ITS232 Introduction To Database Management Systems

1. ITS232Introduction To Database Management Systems CHAPTER 7 An Introduction To SQLPart 2: DDL & DML SitiNurbaya Ismail Faculty of Computer Science & Mathematics, UniversitiTeknologi MARA (UiTM), Kedah | sitinurbaya@kedah.uitm.edu.my | http://www.sitinur151.wordpress.com | | A2-3039 | ext:2561 | 012-7760562 |

2. Objectives To be able to apply SQL command. • How to use DML in SQL 2 major components: • Data Definition Language (DDL) • defining database structure. • allows database objects such as schemas, domains, tables, views and indexes to be created and destroyed. • Data Manipulation Language (DML) • retrieving and updating data. • used to populate and query the tables. • data manipulation.

3. Data Manipulation Language (DML) Manipulate data

4. DML: Data Entry • TheINSERT INTOcommand inserts new rows into a table. • When you insert data into a child table that has a foreign key linking it to a parent table, you must obey referential integrity rules. • This means you cannot insert a value into a child key that does not exist in the parent key unless it is a NULL value. • You must insert a new row into the parent key first. • To insert a string that contains a single quote, you must replace the single quote in the string with two consecutive single quotes.

5. DML: Data Entry SQL INSERT INTO Syntax • It is possible to write the INSERT INTO statement in two forms. • The first form doesn't specify the column names where the data will be inserted, only their values: • The second form specifies both the column names and the values to be inserted: INSERT INTO table_name VALUES (value1, value2,…); INSERT INTO table_name (column1,column2,…) VALUES (value1, value2,…);

6. The following insert values into table ‘staff’. DML: Data Entry INSERT INTO staff VALUES (‘ABC123’, ‘Norain', ‘Ipoh’);

7. DML: Deleting Table Rows • The DELETE command deletes all rows matching the search condition from a table. • You can only delete rows from a single table, and you cannot delete rows from the system tables. • To execute the DELETEcommand, you must be the table owner, have delete privilege on the table, or have DBA or SYSADM security privileges.

8. DML: Deleting Table Rows SQL DELETE Syntax Note Notice the WHERE clause in the DELETE syntax. The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted! DELETE FROM table_name WHERE some column=some value

9. DML: Deleting Table Rows • DELETE record as specified in WHERE clause. • The following example deletes all staff whose name begins with “Muhammad" from the staff table. • The following example deletes staff number ABC125 from the staff table. DELETE FROM staff WHERE staffNO=“ABC123” AND city=“Ipoh”; DELETE FROM staff WHERE staffNAME LIKE “Muhammad%”; DELETE FROM staff WHERE staffNO =“ABC125”;

10. DML: Making Changes to Data Items The UPDATEcommand updates existing rows in a table. When you update a column, the new column values must satisfy the column constraints and referential integrity. If the column has a DEFAULT value defined, you can use the DEFAULT keyword to set the value of the column to the default value.

11. DML: Making Changes to Data Items SQL UPDATESyntax Note Notice the WHERE clause in the UPDATE syntax. The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated! UPDATE table_name SET column1=value,column2=value2,… WHERE some column=some value

12. DML: Making Changes to Data Items • Shows how to update the staff table and change the city value for staff named “Rosliza". • Shows how to give a salary raise of 5% to staff named “Wafiy". • Update description for printer with printerNO F380. UPDATE staff SET city=“Bangi” WHERE staffNAME = “Rosliza”; UPDATE staff SET salary=salary*1.05 WHERE staffNAME = “Wafiy”; UPDATE printer SET description=“KO” WHERE printerNO = “F380”;