Data Manipulation Language (DML)

In an INSERT command, string data types must have...

(') quotation marks around them. This signifies that the data being entered is a string. Date data types require quotation marks around them when using most databases except Microsoft Access, which requires pound (#) sign around the date data being used.

Variables are declared using the _______ sign before the variable name.

@. you should also declare the data type for that variable just as you would the data type for an attribute.

This stored procedure begins by declaring the variables we will need and the data type for each variable. Next, we declare the curAlumni cursor that will store the data from the query that follows. The query is selecting five attributes from our student table where the alumni attribute is equal to 'Y'. Our cursor will contain the records of all students who are alumni. Next, we use the OPEN command to open the cursor so that we can read the data. After opening the cursor we use the FETCH NEXT command to fetch the next available record in a recordset. Since we have not fetched any records yet, this command will move to the first record of our data set. The FETCH command also stores the values of each attribute into variables for that record. In our example above, we are selecting five attributes so we must have five variables that will store the data as we progress through each record. The FETCH command also produces its own variable called @@FETCH_STATUS. If the FETCH NEXT is successful, the command moves to the next record. If there is not another record to move to then the @@FETCH_STATUS will equal -1, meaning that we have moved beyond the final record of our recordset. The WHILE command creates a loop that will progress through the cursor until @@FETCH_STATUS is no longer equal to zero. At this point we have finished going through all the data in our cursor and can exit the loop. Inside of the WHILE loop we need to process the data from our query one record at a time and then insert the results into the alumni table. Using the YEAR( ) function, we can extract the year from our @DOB and @graduationDate variables. The @age variable is then SET to equal the difference between these two years. (NOTE: This could vary depending on the month a person was born. For simplicity, we will not worry about that at this time). Now that we have captured the age for the record being processed, we can use our INSERT command to insert the data into the alumni table. After the INSERT has finished, we call the FETCH NEXT command (inside the loop) to move to the next record of our data set. This process will repeat until we have reached the end of the data set and the @@FETCH_STATUS is no longer equal to zero. Once we have exited the loop we may now close the cursor. The Deallocate command will remove the reference of the cursor from memory.

CREATE PROCEDURE [dbo].[sp_insertAlumni] AS Declare @SID varchar(9), @fName varchar(20), @lName varchar(20), @DOB DATE, @AGE INT, @graduationDate Date, @year1 INT, @year2 INT Declare curAlumni cursor For SELECT SID, fname, lName, DOB, graduationDate FROM student WHERE alumni = 'Y'; OPEN curAlumni Fetch Next From curAlumni Into @SID, @fName, @lName, @DOB, @graduationDate While @@Fetch_Status = 0 Begin @year1 = Year(@DOB) @year2 = Year(@graduationDate) @age = @year2 - @year1 INSERT INTO alumni(alumniID, fName, lName, Age) VALUES (@SID, @fName, @lName, @Age); Fetch Next From curAlumni Into @SID, @fName, @lName, @DOB, @graduationDate End Close curAlumni Deallocate curAlumni

The query above should be used to return a single value. If you wish to return multiple values then you would need to create a _____ that would loop through the results one record at a time.

CURSOR

Components needed for a successful cursor are

Cursor - Declare a cursor variable that will store the results of a query. OPEN Command - This opens the cursor to be used FETCH NEXT Command - This is used to store the results of the query into variables Do WHILE Loop - This is used to loop through the results, one record at a time. CLOSE Command - This closes the query once you have exited the loop

To delete records of deceased alumni from our alumni table we would need to check the flag in our student table. The following syntax would achieve this goal.

DELETE A FROM student S, alumni, A WHERE S.SID = A.alumniID and S.deceased = 'Y';

The syntax for writing a DELETE statement is as follows.

DELETE FROM table WHERE condition = true; DELETE FROM student WHERE SID = 208799811;

how to declare a variable, set its value, and use the variable in the UPDATE command.

Declare @name varchar(20), @SID varchar(9) SET @name = 'Aaron' SET @SID = '222222222' UPDATE student SET lName = @name WHERE SID = @SID;

The syntax for selecting data from a table and storing it into variables is

Declare @var1 int,@var2 int,@var3 int; SELECT @var1 = attribute1, @var2 = attribute2, @var3 = attribute3 TABLE tableName WHERE condition=TRUE

Using a subquery to insert values into one table that are selected from another table

INSERT INTO alumni (alumniID, fName, lName, email) SELECT SID, fName, lName, email FROM student WHERE alumni = 'Y'; The number of attributes we are inserting data into must match the number of values being selected in our SELECT

Syntax for the insert command

INSERT INTO student (SID, fName, lName, DOB, Email) VALUES (208799811, "Ashley", "Judd", "04/19/1968", "[email protected]");

when would you use variables to hold data and use the variables in your UPDATE command.

If you are using a stored procedure in SQL Server

The ______ command allows you to store a value into the variable.

SET

There is another statement that also deletes data from the database. This statement is the ______ command.

TRUNCATE By writing "TRUNCATE table;" all of the data in that table will be deleted.

In order for an INSERT statement to be successfull

The primary key and any attributes designated as NOT NULL must have a value entered in order for the INSERT statement to be successful.

Why would it be useful to declare variables

This is useful because you can perform calculations and manipulate the data stored in the variable before executing your query. More advanced uses for this would be to select data from one table and store it into a variable, then perform calculations on the variable before writing the changes back to the database.

How to UPDATE all records of a table given a condition

UPDATE student SET city='Daegu' WHERE city = 'Taegu';

How to UPDATE a single record given a condition

UPDATE student SET lName='Smith' WHERE SID='524356102'

Syntax for writing an update command

UPDATE table SET column1 = value1, Column2 = value2, .... WHERE condition = true; UPDATE student SET email = "[email protected]" WHERE SID = 208799811;

The DELETE command also requires a _____ clause to ensure only the intended records are deleted from the database.

WHERE. If a WHERE clause is not present, the use of a DELETE command would delete all records from the table.

What is the difference between DELETE, and TRUNCATE.

When using the DELETE command, transaction records are written that can be used to recover data that needs to be recovered in case of a mistake or accident. These transaction records are backed up during the transactional backups that occur daily. When the TRUNCATE command is used, no transaction records are created. Therefore, the data is gone without the ability to recover at the point of deletion. TRUNCATE also executes faster since it does not require the creation of transaction records.

What is the use of Data Manipulation Language?

allow the user to insert, update/modify, and delete data from the database

INSERT

insert records into the database. The keywords INSERT INTO are used followed by the name of the table to designate the entity you are inserting data into.

If no quotation marks are used then the database will assume it is a ______ data type.

number. you cannot perform mathematical functions on string data types.

If you wish to delete the matching records from both tables based on the condition identified in the WHERE clause

then you would add the alias for both tables to the DELETE command (i.e. DELETE A, S FROM student S, alumni, A WHERE ......).

In the DELETE statement, the WHERE clause is used...

to join these two tables together and filter the results based on the S.deceased attribute being equal to 'Y'.

UPDATE

used to modify existing records within the database.

how can be sure that only a single record will be updated in our UPDATE command?

using the primary key in our WHERE clause