Lecture6:Data Manipulation in SQL , Simple SQL queries

1. Lecture6:Data Manipulation in SQL , Simple SQL queries Lecture6 Ref. Chapter5 Prepared by L. NoufAlmujally

2. The Process of Database Design Lecture6

3. Tables in the Examples Customer(custNo, custName, custSt, custCity, age) Product(prodNo, prodName, prodDes, price) Orders(ordNo, ordDate, custNo, prodNo, quantity) Where custName, custSt, custCity, prodName, prodDes are strings ordDate is date Others are numbers Lecture6

4. Sample Data in Customer Table Lecture6

5. Sample Data in Product Table Lecture6

6. Sample Data in Orders Table Lecture6

7. Data Manipulation (DML) • DML is used to retrieve and modify data in the tables • Four basic statements • Insert Into • Select • Update • delete From Lecture6

8. Insert Statement • The INSERT statement adds one or more new rows of data to a database table. • Syntax • Note: • value list must correspond to column list • If column list is omitted, then a value for every attribute is required • The data types must be correct INSERT INTO table_name VALUES (value1,value2,value3,...); INSERT INTO table_name (column1,column2,column3,...)VALUES (value1,value2,value3,...); Lecture6

9. Insert Statement Example • Example: for table Customer, Insert into Customer(custNo, custName) values ('6', 'John'); Output: 1 row inserted Insert into Customer values ('7', 'David ', 'St1','City1', 20); Output: 1 row inserted Lecture6

10. Simple SELECT Queries • The SELECT command is used for submitting queries to the DBMS. • Syntax • SELECT expression_list • FROM table_list • [WHERE condition] • [ORDER BY expression_list]; Lecture6

11. Simple SELECT Queries Expression in SELECT statement : Condition in WHERE statement: • an expression that can be evaluated to TRUEorFALSE. • Only rows satisfying the condition will be chosen. • Condition can be simple comparison or compound expression Lecture6

12. Conditions in the WHERE Clause WHERE clause consists of five basic search conditions: • Comparison: Compare the value of one expression to the value of another expression (= , <, >, <=, >=, <>). • Range: Test whether the value of an expression falls within a specified range of values (BETWEEN/ NOT BETWEEN). • Set membership: Test whether the value of an expression equals one of a set of values (IN/ NOT IN). • Pattern match: Test whether a string matches a specified pattern (LIKE/ NOT LIKE). • NULL: Test whether a column has null value (IS NULL/ IS NOT NULL). Note: Basic comparisons can be compounded by AND, OR, NOT • Eg, prodNo=100 andordDate='01-jan-2003'

13. Simple Queries : Comparison search condition Comparison operators: = , < , > , <= , >= , <> Example 1: List all products (by prodNo and price) which are priced more than 100. Select prodNo, price From Product Where price >100; Example 2: What is the name of the customer whose custNo is 1? Select custName From customer Where custNo=1; Lecture6

14. Listing All Data in a Table • If WHEREclause is omitted, all rows will be listed. Example: List all data in the customer table SELECT custNo, custName, custSt, custCity FROM customer; OR (use * for all columns) SELECT * FROM CUSTOMER; Lecture6

15. Simple Queries : Compound comparison search condition • Compound comparison operators: AND , OR , NOT , ( ) • Order of evaluation: • Expression is evaluated left to right • Between brackets • NOT • AND • OR Lecture6

16. Examples Example: Find all orders of product 100 before 02/01/03. SELECT * FROM orders WHERE prodNo = 100 AND ordDate <'02-jan-2003'; Example:Find all products priced less than 200 or greater than 300 SELECT * FROM product WHERE price < 200 OR price >300; Lecture6

17. More Examples Example: Find the customer with name C1 and live in Riyadh or Jeddah SELECT * FROM customers WHEREcustName='C1‘ AND(custCity='Jeddah' ORcustCity='Riyadh'); Lecture6

18. Simple Queries : BETWEEN / NOT BETWEEN • The BETWEEN operator is used to select values within a range. • The NOT BETWEEN checks if a value is outside a range. • Syntax: SELECT column_name(s) FROM table_nameWHERE column_nameBETWEEN|NOT BETWEEN value1ANDvalue2; Lecture6

19. BETWEEN Example Example: List products priced between 200 and 300. SELECT * FROM product WHERE price >=200 and price <=300; or equivalently SELECT * FROM product WHERE price between 200 and300; Lecture6

20. Simple Queries : IN / NOT IN • IN tests whether a data value matches one of a list values. • NOT IN checks for data values that do not lie in a specific list of values • Syntax SELECT column_name(s) FROM table_name WHERE column_nameIN| NOT IN(value1,value2,...); Lecture6

21. IN Example Example: List all customers living in Riyadh, or Dammam, or Jeddah. SELECT * FROM Customer WHERE custCity = 'Jeddah' OR custCity = 'Riyadh' OR custCity = 'Dammam'; or equivalently SELECT * FROM Customer WHERE custCityIN(‘Jeddah', ‘Riyadh', ‘Dammam'); Lecture6

22. Simple Queries : LIKE / NOT LOKE • LIKE is used to search for a specified pattern in a column. • NOT LIKE allows you to select records that does NOT match the pattern. • Syntax • SQL has special pattern matching symbol • % represents any sequence of zero or more characters • _ represents any single character SELECT column_name(s) FROM table_name WHERE column_nameLIKE | NOT LIKE ‘pattern’; Lecture6

23. LIKE Example Example: List all products whose description contain the string 'Food'. SELECT * FROM product WHERE prodDesLIKE'%Food%'; Lecture6

24. More Examples of LIKE | NOT LIKE LIKE 'H_' : any string beginning with H and exactly 2 characters long. NOT LIKE 'H%': any string not beginning with H LIKE '%y': any string ending with 'y' Lecture6

25. Simple Queries : IS NULL and IS NOT NULL • It is not possible to test for NULL values with comparison operators, such as =, <, or <>. • To test for null values in a query, use IS NULL or IS NOT NULL in the WHERE clause. • Comparisons between a NULL and any other value, return unknown and the result will not be included in the final results • Syntax Lecture6 SELECT column_name(s) FROM table_name WHERE column_nameIS NULL;

26. IS NULL and IS NOT NULL Examples Example: List all products with a product description. SELECT * FROM product WHERE prodDes IS NOT NULL; Similarly, to list products without description, use SELECT * FROM product WHERE prodDes IS NULL; Lecture6

27. Lecture6

28. Simple Queries : Use of DISTINCT • Use Distinct in the select statement To remove duplicate values • Syntax SELECT DISTINCTcolumn_name,column_name FROM table_name; Lecture6

29. Use of DISTINCT Example Example: List all customer cities. SELECT custCity FROM customer; • A city will be repeated if there are more than one customer in that city. To eliminate the duplicates, use: SELECT DISTINCTcustCity FROM customer; Lecture6

30. Simple Queries : Ordering of Rows • Rows can be put in ascending or descending order of some columns. To do this, use ORDER BY • Syntax • Default order (ie, if desc is not used) is ascending SELECT column_name,column_name FROM table_name WHERE ConditionORDER BY column_name,column_nameASC|DESC Lecture6

31. Ordering of Rows Example • Example: list all products in descending order of price SELECT * FROM product ORDER BY price desc; • Can also order by several attributes, eg. ORDER BY price desc, prodName; Lecture6

32. Ordering of Rows Example SELECT * FROM Individual ORDER BY FirstName, LastName Lecture6

33. Operators summary Lecture6

34. Lecture6

35. Update Statement • The UPDATEstatement is used to update records in a table • Syntax Notice the WHERE clause in the SQL UPDATE statement!The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated! UPDATEtable_nameSETcolumn1=value1, column2=value2,...WHEREsome_column=some_value Lecture6

36. Update Statement Example • Example : Customer C1 has changed his city to Riyadh. UPDATE Customer SET custCity=‘Riyadh‘ , custSt='12 Mains Rd' WHERE CustName=C1'; Output: 1 row updated Select * From Customer ; Lecture6

37. Update Statement Example Be careful when updating records. If we had omitted the WHERE clause, in the example before, like this: UPDATE Customer SET custCity=‘Riyadh‘ , custSt='12 Mains Rd' ; Output: 5 rows updated Select * From Customer ; Lecture6

38. Delete Statement • The DELETEstatement is used to delete records in a table. • Syntax Notice the WHERE clause in the SQL DELETE statement!The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted! DELETE does not delete the table itself, only rows in the table. DELETE FROM table_nameWHEREsome_column=some_value; Lecture6

39. Delete Statement Example • Example : Delete Customer C1 DELETE FROM Customer WHERE CustName=C1'; Output: 1 row deleted Select * From Customer ; Lecture6

40. Delete Statement Example • Example : Delete all Customers DELETE FROM Customer Output: 5 row deleted Select * From Customer ; Lecture6

41. Truncate Statement • TRUNCATE deletes all data in a table and frees storage space for the table rows ( deletes data faster but you cannot rollback) • Syntax TRUNCATE get rid of the data but not the table itself (DROP) Trancatetable_name; Lecture6

42. Truncate Example • Example : Delete all Products TRUNCATE TABLE product; Lecture6

43. Extra Example EMPLOYEE DEPARTMENT Lecture6

44. Basic SQL SELECT Queries SELECT firstName, lastName FROM Employee WHERE employeeNo = ‘E1’; Lecture6

45. Compound Comparison SELECT deptNumber FROM EMPLOYEE WHERE lastName = ‘Smith’ OR lastName = ‘Hodges’; Duplicate Removal Lecture6 SELECTDISTINCTdeptNumber FROM EMPLOYEE;

46. Set Membership Search ( IN) SELECT deptNumber, mailNumber FROM DEPARTMENT WHERE deptName IN ( ‘Computer Science’, ‘Physics’); Pattern Match Search ( LIKE) Lecture6 SELECT employeeNo, deptNumber FROM EMPLOYEE WHERE firstName LIKE ‘%an%’;

47. Sorting Output from Queries SELECT employeeNo , lastName FROM EMPLOYEE ORDER BY lastName; Lecture6

48. Lecture6

49. References • “Database Systems: A Practical Approach to Design, Implementation and Management.” Thomas Connolly, Carolyn Begg. 5thEdition, Addison-Wesley, 2009. Lecture6