SQL: Data Manipulation Language
CSC 343
Winter 2021
MICHAEL LIUT (MICHAEL.LIUT@UTORONTO.CA) ILIR DEMA (ILIR.DEMA@UTORONTO.CA)
DEPARTMENT OF MATHEMATICAL AND COMPUTATIONAL SCIENCES UNIVERSITY OF TORONTO MISSISSAUGA
Data Manipulation Language (DML)
● DDL is used to manipulate the schema of our DB (creating/updating/deleting tables and adding constraints)
● DML is used to manipulate the data in our DB.
○ INSERT (Adding data)
○ UPDATE (Changing existing data)
○ SELECT (Querying for data)
○ DELETE (Removing data)
DML – INSERT
● Used to insert data into tables
● General form:
INSERT INTO TABLE_NAME[(k1, k2, .., kn)] VALUES(v1, v2, .., vn);
● Notice that the set of attribute names after table_name are optional. This is because not specifying
them is the same as specifying all of the attribute names in the table.
● Example:
○ INSERT INTO Teacher(tid, name, deptId) VALUES(1, ‘Ilir’, 5);
○ INSERT INTO Teacher VALUES(1, ‘Ilir’, 5);
●
●
DML – INSERT
The two examples above do the same thing, so when is specifying the keys useful?
○ Not inserting data for some columns (which allow NULL)
○ SERIAL columns (which take care of the value for you) – note: it is not unique, you must specify this!
○ Different order of inserting data
○ Depending on reader’s knowledge of the table, it might be more clear to specify it
You cannot insert data which violates integrity constraints, some cases:
○ Inserting the wrong type of data
■ INSERT INTO Teacher VALUES(‘1’, 321, true);
○ Inserting a row with a primary key which already exists
■ INSERT INTO Teacher VALUES(1, ‘Ilir’, 5);
■ INSERT INTO Teacher VALUES(1, ‘Michael, 5);
○ Inserting a row with a NULL attribute in the primary key
■ INSERT INTO Teacher VALUES(NULL, ‘Naaz’, 5);
■ INSERT INTO Teacher(Name, DeptId) VALUES( ‘Naaz’, 5);
○ Inserting data into a column which is a foreign key, but where the value you specify does not already exist
■ INSERT INTO Teacher VALUES(1, ‘Ilir’, 0); (Assume that we do not have a row in Department with DeptId 0)
DML – UPDATE
● Used to update data which already exists in a table
● General form:
UPDATE TABLE_NAME SET k1=new_v1,..kn=new_vn [WHERE CONSTRAINTS];
● You can update 1 or more values at a time
● Constraints (and the WHERE keyword) are used to target your update
○ Constraints can be used to target 1 specific row, how would you do this? What concept would you use?
● Update statements will fail if you violate constraints (same ones explained on the DML – INSERT slides)
● Example:
○ UPDATE Teacher SET DeptId = 7 WHERE Name = ‘Ilir’;
○ UPDATE Teacher SET DeptId = 9 WHERE DeptId = 5;
DML – SELECT
– Used for data querying
SELECT ATTR_NAMES FROM TABLE_NAMES [WHERE CONDITIONS];
– If there is more than one attribute with the same name a table should be specified: SELECT t.name FROM Teacher t WHERE DeptId = 1;
– Using * in ATTR_NAMES selects all attributes
– It is sometimes helpful to rename attributes in your result:
SELECT t.tid AS ‘Personnel Number’ FROM Teacher t WHERE DeptId = 1;
DML – WHERE
Condition inside the WHERE clause:
– Boolean: AND, OR, NOT
– Comparison: =, <>, <, >, <=, >=
– Pattern matching: attribute LIKE/NOT LIKE pattern
– Any string: %
– Any character: __
– NULL values return UNKNOWN on conditions (and are not selected)
– Looking at range: BETWEEN value AND value
– Looking at the list: ANY/ALL/ IN/NOT IN
– NOT EXIST
– UNION/UNION ALL
– SELECT statements can be used inside WHERE clauses
DML – DELETE
– Used to delete tuples
DELETE FROM TABLE_NAME WHERE
– To delete all tuples from the relation:
– Just like dropping tables, you can not break foreign key constraints by removing rows.
(Unless ON DELETE CASCADE is specified in DDL)
DELETE FROM TABLE_NAME;
NOTE: When using DELETE, it doesn’t start deleting until all the tuples are checked (checks the condition on all the tuples in relation and then deletes the ones that satisfy the condition all at once).
Questions?
Q
THANKS FOR LISTENING
I’LL BE ANSWERING QUESTIONS NOW
&
A
9