ITS232 Introduction To Database Management Systems

ITS232Introduction To Database Management Systems SitiNurbaya Ismail Faculty of Computer Science & Mathematics, Universiti Teknologi MARA (UiTM), Kedah | A2-3039 | ext:2561 | sitinurbaya@kedah.uitm.edu.my | 012-7760562 | CHAPTER 7 An Introduction To SQL Part 2: DDL & DML

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.

Data Defination Language (DDL) Defining database structure/schema

Chapter 7: An Introduction To SQL7.3 Data Definition Languages (DDL) Data Definition SQL commands describe the database structure or schema:

Create table ‘printer’ with printerNo as a primary key and staffNo as a foreign key; Staff(staffNO,staffNAME,city,postcode) Printer(printerNO, desc, color, staffNO*) CREATE TABLE printer (printerNOVARCHAR(5) NOT NULL PRIMARY KEY, desc VARCHAR(16), colorVARCHAR(8), FOREIGN KEY (staffNO) REFERENCES staff(staffNO)); An empty table ‘printer’ is created with printerNO as primary key and staffNO as a foreign key: Chapter 7: An Introduction To SQL7.3 Data Definition Languages (DDL): Create Table

Chapter 7: An Introduction To SQL7.3 Data Definition Languages (DDL): Alter Table ALTER command is used if there is a change on the table’s attribute. The following SQL statement will add an attribute to table ‘staff’. ALTER TABLE staff ADD telno char(11); The following SQL statement will rename the column color in table ‘printer’. ALTER TABLE printer RENAME COLUMN color TO printerColor; The following SQL statement will change the width of desc’s colum to 25 digits in table ‘printer’. ALTER TABLE printer MODIFY (desc VARCHAR(25));

Chapter 7: An Introduction To SQL7.3 Data Definition Languages (DDL): In Brief

Data Manipulation Language (DML) Manipulate data

Data Manipulation Language(DML) • Data Manipulation in SQL is an ability for manipulating the datato provide information needs by users. • Manipulating the data referees to process of: • selecting the data • do some operation at the data • user view it or save it in the database SELECT * FROM printer WHERE color = ‘red’ OR color = ‘blue’;

DML: Queries: SELECT Statement SELECT Statement • The SELECTstatement allows you to find,retrieve, and display data. • To execute the SELECT statement on a table, you must be the table owner, have DBA or SYSADM security privileges, or have theSELECTprivilege for that table. • The result of SELECTstatement is a set of rows known as the result set, which meets the conditions specified in the SELECT statement SQL SELECT Syntax Note SQL is not case sensitive { SELECT is the same as select } SELECT column_name(s) FROM table_name; SELECT * FROM table_name;

Select all data from table printer. Select printerNO and price from table printer. DML: Queries: SELECT Statement SELECT * FROM printer; SELECT printerNO, price FROM printer;

DML: Queries: SELECT DISTINCT Statement SELECT DISTINCT Statement • In a table, some of the columns may contain duplicate values. • The DISTINCT keyword can be used to return only distinct (different) values. SQL SELECT DISTINCT Syntax SELECT DISTINCT column_name(s) FROM table_name;

DML: Queries: SELECT DISTINCT Statement The following statement only will select the distinct values from the column named city from table staff. SELECT DISTINCT city FROM staff;

SQL LIKE Operator The LIKE operator is used in a WHERE statement to search for a specified pattern in a column. SQL LIKE Syntax Note The LIKE operator is commonly used with SQL Wildcards SELECT column_name(s) FROM table_name WHERE column_name LIKE patern;

SQL LIKE Operator The following select staffs living in a city start with “K” from table ‘staff’. SELECT * FROM staff WHERE city LIKE “K%”;

SQL Wildcards • SQL wildcards can be used when searching for data in a database. • SQL wildcards can substitute for one or more characters when searching for data in a database. • SQL wildcards must be used with the SQL LIKE operator. • With SQL, the following wildcards can be used:

Wildcards: Using The % Wildcard Select staff living in the city that start with ‘La’ from staff table. Select staff living in the city that contain pattern ‘wi’ from staff table. SELECT * FROM staff WHERE city LIKE "La%"; SELECT * FROM staff WHERE city LIKE "%wi%";

Wildcards: Using The _ Wildcard Select staff with a name that starts with any character, followed by ‘da’ from staff table. Select staff with a name that starts with "S", followed by any character, followed by "ar",followed by any character, followed by "s" from staff table. SELECT * FROM staff WHERE staffNANE LIKE "_da"; SELECT * FROM staff WHERE staffNANE LIKE "S_ar_s";

Wildcards: Using The [charlist] Wildcard Select staff with a name that starts with "a" or "s" or "p" from staff table. Select staff name that do not starts with “"a" or "s" or "p" from staff table. SELECT * FROM staff WHERE staffNANE LIKE "[asp]%"; SELECT * FROM staff WHERE staffNANE LIKE "[!asp]%";

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.

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,…);

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

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.

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

DML: Deleting Table Rows • DELETE record as specified in WHERE clause. • The following example deletes all staff whose name begins with “Nisa" 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 staffNAMELIKE “Nisa%”; DELETE FROM staff WHERE staffNO =“ABC125”;

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.

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

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”;

DML: COMMIT TheCOMMITstatement terminates the current transaction and makes all changes under the transaction persistent. • COMMIT is used for saving the data that has been changed permanently because whenever you perform any DML like UPDATE, INSERT or DELETE then you are required to write COMMIT at the end of all or every DML operation in order to save it permanently. • If you do not write COMMIT and you program crashes then your data will be restored into its previous condition. The COMMIT statement has the following general format: UPDATE printer SET indate = ‘15/01/2007' WHERE printerNO = 'F380'; COMMIT;

DML: ROLLBACK The ROLLBACK statement terminates the current transaction and cancel all changes made under the transaction. • ROLLBACK is used if you want to restore your data into its previous condition. • ROLLBACK can be write at any time after the DML queries has been written but remember once COMMIT has been written then you cannot rollback the data. • You can only rollback the DML queries that have been written after the last commit statement. The ROLLBACK statement has the following general format: INSERT INTO staff VALUES (“ABC990”, “Shafiza”, “Johor”) ROLLBACK;

DML: COMMIT & ROLLBACK The concept of COMMIT and ROLLBACK is designed for data consistency because many uses manipulate data of the same table, using the same database so the user must get updated data. That is why COMMIT and ROLLBACK are used for. COMMIT to save whatever has been done. It is used to permanently store it in memory. ROLLBACK to undo something. If we use roll-back, the particular changes made are undone.

AND to combine two search conditions which must be both true. OR to combine two search conditions when one or the other (or both) must be true DML: Compound Statement with SELECT You can combine simple conditions with the logical operators AND, OR, and NOT to form compound conditions.

DML: Compound Statement with SELECT Select only the staff live in Merbok AND age greater then or equal to 40 years old from table staff. Select only the staff live in PenangOR age greater then or equal to 40 years old from table staff. SELECT * FROM staff WHERE city=“Merbok” AND age >= 40; SELECT * FROM staff WHERE city=“Penang” AND age >= 40;

DML: Compound Statement with SELECT Select only the staff live in Merbok OR Penang AND age greater then or equal to 40 years old from table staff. SELECT * FROM staff WHERE (city=“Merbok” OR city=“Penang”) AND age >= 40;

Example Delete all biscuits with chocolate flavor from tblbiscuit. Delete all order from customerC001 that have status cancel from tblorder. tblcustomer(custNO, custNAME, custEMAIL) tblbiscuit(bisNO, bisNAME, bisFLAVOUR, bisPRICE) tblorder(orderNO,*custNO,*bisNO, orderadate, qty, status)

Example Change all status to confirm for biscuit BIS101 for customer C135. Increase biscuit price 10% for biscuit with strawberry flavor. tblcustomer(custNO, custNAME, custEMAIL) tblbiscuit(bisNO, bisNAME, bisFLAVOUR, bisPRICE) tblorder(orderNO,*custNO,*bisNO, orderadate, qty, status)

Example List down all customer name and email who order biscuit cornflakes. List down all biscuit details being order by customer C010. tblcustomer(custNO, custNAME, custEMAIL) tblbiscuit(bisNO, bisNAME, bisFLAVOUR, bisPRICE) tblorder(orderNO,*custNO,*bisNO, orderadate, qty, status)

SQL Alias • You can give a table or a column another name by using an alias. This can be a good thing to do if you have very long or complex table names or column names. • An alias name could be anything, but usually it is short. • Usually it is used in multiple table joint. SQL Alias Syntax For Tables SQL Alias Syntax For Columns SELECT column_name(s) FROM table_name AS alias_name SELECT column_name AS alias_name FROM table_name

SQL Alias staff department The following statement list all the staff name that work at IT department. SELECT s.staffNAME FROM staff AS s, department AS d WHERE (d.departNO=s.departNO) AND d.departNAME=“IT”;

SQL JOINs • The JOINkeyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables. • Tables in a database are often related to each other with keys. • A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.

SQL JOINs Different SQL JOINs • JOIN / INNER JOIN Return rows when there is at least one match in both tables • LEFT JOIN Return all rows from the left table, even if there are no matches in the right table • RIGHT JOIN Return all rows from the right table, even if there are no matches in the left table • FULL JOIN Return rows when there is a match in one of the tables

SQL INNER JOIN SQL INNER JOIN Syntax NOTE INNER JOIN is the same as JOIN. SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name = table_name2.column_name

SQL INNER JOIN staff department The following statement list all the staff name that work at IT department. SELECT staff.staffNAME,depart.departNAME FROM staff INNER JOIN department ON department.departNO=staff.departNO;

DML: Two-Table Joins Besides INNER JOIN, you can also use the following statements to combines two tables with join conditions. staff(staffNO, staffNAME, city, salary, departmentNO*) department(departNO, departNAME) SELECT s.staffNAME FROM staff AS s, department AS d WHERE (d.departNO=s.departNO) AND d.departNAME=“IT”; SELECT staff.staffNAME,department.departNAME FROM staff INNER JOIN department ON department.departNO=staff.departNO;

DML: Multiple-Table Joins A multiple table join is a join of more than two tables with join conditions for pairs of table. • A join condition is a comparison (relational operators) on two columns from each table. Following is a three table joins which selects all the customer name that order biscuit Tart NenasGunting. customer(custNO, custNAME, custEMAIL) biscuit(bisNO, bisNAME, bisFLAVOUR, bisPRICE) order(orderNO,*custNO,*bisNO, orderadate, qty, status) SELECT custNAME FROM customer AS c, biscuit AS b, order AS o WHERE c.custNO=o.custNO AND o.bisNO=b.bisNO AND b.bisNAME=“Tart NenasGunting“;

DML: Multiple-Table Joins List down all customer name, biscuit name and order quantity that customer had order with confirm status. tblcustomer(custNO, custNAME, custEMAIL) tblbiscuit(bisNO, bisNAME, bisFLAVOUR, bisPRICE) tblorder(orderNO,*custNO,*bisNO, orderadate, qty, status)

DML: GROUP BY Statement GROUP BYstatement • produce summary data within a group. • a group is a set of rows that have the same values of group by columns. • a single row of aggregate results is produced for each group. • the column you want to group results by is identified by its column name. • restrict what you can enter in the SELECT statement. • SELECTstatement in GROUP BY statement must be one of the following: • An aggregate function, which produces a single value summarizing the rows in the group. • A grouping column which is listed in the GROUP BY statement. • A constant. • An expression involving a combination of the above.

DML: GROUP BY Statement SQL GROUP BY Syntax SELECT column_name(s), aggregate_function(column_name) FROM table_name GROUP BY column_name;

DML: GROUP BY Statement staff The following statement will output staff average salary group by city. SELECT city, MIN(salary) AS minSALARY FROM staff GROUP BY city;

DML: ORDER BY Keyword The ORDER BY keyword • is used to sort the result-set by a specified column. • sort the records in ascending order by default. • By default the records are in ascending order or you can used ASC keyword • If you want to sort the records in a descending order, you can use DESC keyword. • The default order is ascending. NULL values are treated as larger that non-null values for sorting purposes. SQL ORDER BY Syntax SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC

ITS232 Introduction To Database Management Systems