My SQL: Data Manipulation

1. Lecture 4 CS157B My SQL: Data Manipulation Prof. Sin Min Lee Deparment of Computer Science San Jose State University

2. Introduction

3. Writing an SQL Command • Retrieving Data • Building SQL Statements • Performing Database Updates

4. SQL • SQL (Structured Query Language) is the standard language for commercial DBMSs • SEQUEL (Structured English QUEry Language)was originally defined by IBM for System R • standardization of SQL began in the 80s • current standard is SQL-99 • SQL is more than a query language it includes a DDL, DML and administration commands • SQL is an example of a transform-oriented language. • A language designed to use relations to transform inputs into required outputs.

5. 2 Basic structure of an SQL query

6. 2 The Situation:Student Particulars • fieldtypewidthcontents • id numeric 4 student id number • name character 10 name • dob date 8 date of birth • sex character 1 sex: M / F • class character 2 class • hcode character 1 house code: R, Y, B, G • dcode character 3 district code • remission logical 1 fee remission • mtest numeric 2 Math test score

7. I General Structure SELECT ...... FROM ...... WHERE ...... SELECT[ALL / DISTINCT] expr1 [AScol1], expr2 [AScol2] ; FROMtablenameWHEREcondition

8. I General Structure SELECT[ALL / DISTINCT] expr1 [AScol1], expr2 [AScol2] ; FROMtablenameWHEREcondition • The query will select rows from the source tablename and output the result in table form. • Expressions expr1, expr2 can be : • (1) a column, or • (2) an expression of functions and fields. • And col1, col2 are their corresponding column names in the output table.

9. I General Structure SELECT[ALL / DISTINCT] expr1 [AScol1], expr2 [AScol2] ; FROMtablenameWHEREcondition • DISTINCT will eliminate duplication in the output while ALL will keep all duplicated rows. • condition can be : • (1) an inequality, or • (2) a string comparison • using logical operators AND, OR, NOT.

10. I Result General Structure Before using SQL, open the student file: USE student eg. 1List all the student records. SELECT * FROM student

11. I Class Class  1A 1A  1A 1A class="1A"  1A 1A  1B 1B  1B 1B : : General Structure eg. 2List the names and house code of 1A students. SELECT name, hcode, class FROM student ; WHERE class="1A"

12. I Result General Structure eg. 2List the names and house code of 1A students.

13. I Result General Structure eg. 3List the residential district of the Red House members. SELECT DISTINCT dcode FROM student ; WHERE hcode="R"

14. What is SQL? • ISO SQL has two major components: • Data Definition Language (DDL) • Data Manipulation Language (DML) Source: Database Systems Connolly/Begg

15. Data Definition Language (DDL) • Defining the database structure • Tables • Controlling access to the data • What a user can legally access Source: Database Systems Connolly/Begg

16. Data Manipulation Language (DML) • Retrieving Data • Query tables • Updating Data • Populate tables Source: Database Systems Connolly/Begg

17. Writing SQL Commands • SQL statement consists of reserved words and user-defined words • Reservedwords are a fixed part of the SQL language and have a fixed meaning • User-defined words are made up by the user (according to syntax rules) Source: Database Systems Connolly/Begg

18. Reserved Words • Are fixed part of the SQL language • Have a fixed meaning • Require exact spelling • Kept on the same line Source: Database Systems Connolly/Begg

19. User-defined Words • Are made up by the user • Governed by a set of syntax rules • Represent names of database objects such as: • Tables • Columns • Views • Indexes Source: Database Systems Connolly/Begg

20. Data Manipulation • Select: query data in the database • Insert: insert data into a table • Update: updates data in a table • Delete: delete data from a table Source: Database Systems Connolly/Begg

21. Literals • Non-numeric data values must be enclosed in single quotes: • ’16 Holland Drive’ • ‘CS157B’ • Numeric data values must NOT be enclosed in single quotes: • 6 • 600.00 Source: Database Systems Connolly/Begg

22. Writing SQL Commands Most components of an SQL statement are case insensitive, but one exception is that literal character data must be typed exactly as it appears in the database. Source: Database Systems Connolly/Begg

23. Simple Query • Select specifies which columns are to appear in the output. • From specifies the table(s) to be used. • Where filters the rows subject to some condition(s). Source: Database Systems Connolly/Begg

24. Simple Query • Group By forms groups of rows with the same column value. • Having filters the groups subject to some condition. • Order By specifies the order of the output. Source: Database Systems Connolly/Begg

25. Retrieve all columns and all rows SELECT firstColumn,…,lastColumn FROM tableName; SELECT* FROM tableName;

26. Use of Distinct SELECT DISTINCT columnName FROM tableName;

27. Calculated fields SELECT columnName/2 FROM tableName

28. Comparison Search Condition = equals < > is not equal to (ISO standard) != “ “ “ “ (allowed in some dialects) < is less than > is greater than <= is less than or equal to >= is greater than or equal to Source: Database Systems Connolly/Begg

29. Comparison Search Condition • An expression is evaluated left to right. • Subexpressions in brackets are evaluated first. • NOTs are evaluated before ANDs and ORs. • ANDs are evaluated before ORs. Source: Database Systems Connolly/Begg

30. Range Search Condition SELECT columnName FROM tableName WHERE columnName BETWEEN 20 AND 30; SELECT columnName FROM tableName WHERE columnName >= 20 AND columnName <= 30;

31. Set membership search condition SELECT columnName FROM tableName WHERE columnName IN (‘name1’, ‘name2’); SELECT columnName FROM tableName WHERE columnName = ‘name1’ OR columnName = ‘name2’;

32. Pattern matching symbols % represents any sequence of zero or more characters (wildcard). _ represents any single character Source: Database Systems Connolly/Begg

33. Pattern match search condition ‘h%’ : begins with the character h . ‘h_ _ _’ : four character string beginning with the character h. ‘%e’ : any sequence of characters, of length at least 1, ending with the character e. ‘%CS157B%’ : any sequence of characters of any length containing CS157B Source: Database Systems Connolly/Begg

34. Pattern match search condition LIKE ‘h%’ begins with the character h . NOT LIKE ‘h%’ does not begin with the character h. Source: Database Systems Connolly/Begg

35. Pattern match search condition To search a string that includes a pattern-matching character ‘15%’ Use an escape character to represent the pattern-matching character. LIKE ‘15#%’ ESCAPE ‘#’ Source: Database Systems Connolly/Begg

36. NULL search condition DOES NOT WORK comment = ‘ ’ comment != ‘ ’ DOES WORK comment IS NULL comment IS NOT NULL

37. Sorting • The ORDER BY clause • consists of list of column identifiers that the result is to be sorted on, separated by commas. • Allows the retrieved rows to be ordered by ascending (ASC) or descending (DESC) order Source: Database Systems Connolly/Begg

38. Sorting • Column identifier may be • A column name • A column number (deprecated) Source: Database Systems Connolly/Begg

39. Sorting SELECT type, rent FROM tableName ORDER BY type, rent ASC; Source: Database Systems Connolly/Begg

40. Aggregate Functions • COUNT returns the number … • SUM returns the sum … • AVG returns the average … • MIN returns the smallest … • MAX returns the largest … value in a specified column. Source: Database Systems Connolly/Begg

41. Use of COUNT( * ) How many students in CS157B? SELECT COUNT( * ) AS my count FROM CS157B

42. GROUP BY clause • When GROUP BY is used, each item in the SELECT list must be single-valued per group. • The SELECT clause may contain only • Column names • Aggregate functions • Constants • An expression involving combinations of the above Source: Database Systems Connolly/Begg

43. Grouping SELECTdept,COUNT(staffNo)AS my count SUM(salary) FROM tableName GROUP BY dept ORDER BY dept

44. Restricting Grouping • HAVING clause • is with the GROUPBY clause. • filters groups into resulting table. • includes at least one aggregate function. • WHERE clause • filters individual rows into resulting table. • Aggregate functions cannot be used. Source: Database Systems Connolly/Begg

45. SELECT dept, COUNT(staffNo) AS my count, SUM(salary) AS my sum FROM Staff GROUP BY dept HAVING COUNT(staffNo) > 1 ORDER BY dept; Source: Database Systems Connolly/Begg

46. Subqueries SELECT columnNameA FROM tableName1 WHERE columnNameB = (SELECT columnNameB FROM tableName2 WHERE condition); result from inner SELECT applied as a condition for the outer SELECT Source: Database Systems Connolly/Begg

47. Subquery with Aggregate Function List all staff whose salary is greater than the average salary, show by how much their salary is greater than the average. SELECT fName, salary – ( SELECTAVG(salary) FROM Staff ) AS salDiff FROM Staff WHERE salary > ( SELECT AVG(salary) FROM Staff ); Source: Database Systems Connolly/Begg

48. Nested Subqueries: Use of IN SELECT property FROM PropertyForRent WHERE staff IN( SELECT staff FROM Staff WHERE branch = ( SELECT branch FROM Branch WHERE street = ‘112 A St’)); Selects branch at 112 A St Source: Database Systems Connolly/Begg

49. Nested Subqueries: Use of IN SELECT property FROM PropertyForRent WHERE staff IN( SELECT staff FROM Staff WHERE branch = (branch ) ); Select staff members who works at branch. Source: Database Systems Connolly/Begg

50. Nested Subqueries: Use of IN SELECT property FROM PropertyForRent WHERE staff IN( staffs who works at branch on ‘112 A St’); Since there are more than one row selected, “=“ cannot be used. Source: Database Systems Connolly/Begg