MSc IT UFIE 8 K15 M Data Management

MSc IT UFIE 8 K-15 -M Data Management Prakash Chatterjee Room 3 P 16 prakash. chatterjee@uwe. ac. uk http: //www. cems. uwe. ac. uk/~pchatter/2010/dm Lecture 3 : Structured Query Language (SQL) UFIE 8 K-15 -M Data Management 2011

Origins & history n Early 1970’s – IBM develops Sequel as part of the System R project at n n n its San Hose Research Lab; 1986 - ANSI & ISO publish the standard SQL-86; 1987 – IBM publishes its own “standard” SQL called Systems Architecture Database Interface (SAA-SQL); 1989 – SQL-89 published by ANSI (extended version of SQL-86); 1992 – SQL-92 published with better support for algebraic operations; 1999 – SQL-1999 published with support for typing, stored procedures, triggers, BLOBs etc. SQL-92 remains the most widely implemented standard – and most database vendors also provide their own (proprietary) extensions. UFIE 8 K-15 -M Data Management 2011 2

Components of SQL The SQL language has several parts: n Data-definition language (DDL). The SQL DDL provides commands for defining relation schemas, deleting relations, and modifying relation schemas. n Interactive data-manipulation language (DML). The SQL DML includes a query language based on both the relational algebra and the tuple relational calculus. It includes also commands to insert tuples into, delete tuples from, and modify tuples in the database. n View definition. The SQL DDL includes commands for defining views. n Transaction control. SQL includes commands for specifying the beginning and ending of transactions. n Embedded SQL and dynamic SQL. Embedded and dynamic SQL define how SQL statements can be embedded within general-purpose programming languages, such as C, C++, Java, PL/I, Cobol, Pascal, and Fortran. n Integrity. The SQL DDL includes commands for specifying integrity constraints that the data stored in the database must satisfy. Updates that violate integrity constraints are disallowed. n Authorization. The SQL DDL includes commands for specifying access rights to relations. UFIE 8 K-15 -M and views. Data Management 2011 3

SQL Example (1) n The Supplier-and-Parts Database s p sno sname status sp city sno pno qty s 1 Smith 20 London s 1 p 1 300 s 2 Jones 10 Paris s 1 p 2 200 s 3 Blake 30 Paris s 1 p 3 400 s 4 Clark 20 London s 1 p 4 200 s 5 Adams 30 Athens s 1 p 5 100 s 1 p 6 100 s 2 p 1 300 s 2 p 2 400 s 3 p 2 200 s 4 p 4 300 s 4 p 5 400 pno pname colour weight city p 1 Nut Red 12. 0 London p 2 Bolt Green 17. 0 Paris p 3 Screw Blue 17. 0 Oslo p 4 Screw Red 14. 0 London p 5 Cam Blue 12. 0 Paris p 6 Cog Red 19. 0 London UFIE 8 K-15 -M Data Management 2011 4

SQL Example (2) n Project the columns renamed columns: SELECT sname FROM s snam e Smith SELECT sname AS Supplier, status * 5 AS 'Status times Five' computed columns: FROM s SELECT sname, status * 5 FROM s Supplier Status times Five Jones sname status * 5 Blake Smith 100 Jones 50 Blake 150 Clark 100 Adams 150 Clark Adam s UFIE 8 K-15 -M Data Management 2011 Smith 100 Jones 50 Blake 150 Clark 100 Adams 150 5

SQL Example (3) n Restrict the rows SELECT * FROM s WHERE city=‘London’ sno sname status complex condition: SELECT * FROM s WHERE city=‘London’ OR status = 30 city s 1 Smith 20 London s 4 Clark 20 London sno sname s 1 Smith 20 London s 3 Blake 30 Paris s 4 Clark 20 London s 5 Adams 30 Athens UFIE 8 K-15 -M Data Management 2011 status city 6

SQL Example (4) n Restrict & Project SELECT city FROM s WHERE sname='smith' OR status='20' city London remove duplicate rows: SELECT DISTINCT city FROM s WHERE sname='smith' OR status='20' city London UFIE 8 K-15 -M Data Management 2011 7

SQL Example (4) Group By and Having n Use the ‘GROUP BY’ clause to aggregate related rows SELECT city, SUM(status) AS 'Total Status' FROM s GROUP BY city Total Status Athens 30 London 40 Paris 40 n Use the ‘HAVING’ clause to restrict rows aggregated with ‘GROUP BY’ SELECT city, SUM(status) AS 'Total Status' FROM s GROUP BY city HAVING SUM(status) > 30 city Total Status London 40 Paris 40 UFIE 8 K-15 -M Data Management 2011 8

SQL Functions n SQL provides a wide range of predefined functions to perform manipulation of data. n Four types of functions arithmetic (sqrt(), log(), mod(), round() …) date (sysdate(), month(), dayname() …) character (length(), lower(), upper()…) aggregate (min(), max(), avg(), sum() …) UFIE 8 K-15 -M Data Management 2011 9

Joins (1) n The m-f Database m id name age f id name age 1 tom 23 1 mary 23 2 dick 20 2 anne 30 3 harry 30 3 sue 34 UFIE 8 K-15 -M Data Management 2011 10

Joins (3) n Product (or Cartesian Product) SELECT * FROM m, f id name age 1 tom 23 1 mary 23 2 dick 20 1 mary 23 3 harry 30 1 mary 23 1 tom 23 2 anne 30 2 dick 20 2 anne 30 3 harry 30 2 anne 30 1 tom 23 3 sue 34 2 dick 20 3 sue 34 3 harry 30 3 sue 34 Synonymous with the CROSS JOIN, hence: SELECT * FROM m CROSS JOIN f; would return the same result. This is not very useful but is the basis for all other joins. UFIE 8 K-15 -M Data Management 2011 11

Joins (4) n Natural join Joins tables using some shared characteristic – usually (but not necessarily) a foreign key. SELECT * FROM m, f WHERE m. age = f. age id name age 1 tom 23 1 mary 23 3 harry 30 2 anne 30 UFIE 8 K-15 -M Data Management 2011 12

Joins (5) n Inner joins The previous example, besides being a natural join, is also an example of an inner join. An inner join retrieves data only from those rows where the join condition is met. SELECT * FROM m, f WHERE m. age > f. age id 3 name harry age 30 id 1 name mary age 23 UFIE 8 K-15 -M Data Management 2011 13

Joins (6) n Outer joins Unmatched rows can be included in the output using as outer join. Left outer join: SELECT * FROM m LEFT OUTER JOIN f ON m. age = f. age id name 1 tom 23 1 2 dick 20 NULL 3 harry 30 2 mary NULL anne age 23 NULL 30 Right outer join: SELECT * FROM m RIGHT OUTER JOIN f ON m. age = f. age id name age 1 tom 23 1 mary 23 3 harry 30 2 anne 30 NULL 3 sue 34 UFIE 8 K-15 -M Data Management 2011 14

Joins (7) n Self Join Special case of the inner join – here the table employee shows employees and their managers. Ruth manages Joe who manages Tom, Dick and Harry. emp_id emp_name mgr_id 1 Tom 4 2 Dick 4 3 Harry 4 4 Joe 5 5 Ruth NULL Show who manages who by name: SELECT E 1. emp_name AS Employee, E 2. emp_name AS Manager FROM employee AS E 1 INNER JOIN employee AS E 2 ON E 1. mgr_id = E 2. emp_id UFIE 8 K-15 -M Data Management 2011 Employee Manager Tom Joe Dick Joe Harry Joe Ruth 15

Bibliography / Readings / Home based activities Bibliography - An Introduction to Database Systems (8 th ed. ), C J Date, Addison Wesley 2004 Database Management Systems, P Ward & G Defoulas, Thomson 2006 Database Systems Concepts (4 th ed. ), A Silberschatz, H F Korth & S Sudarshan, Mc. Graw-Hill 2002 Readings - Introduction to SQL’ Mc. Graw-Hill/Osbourne (handout) Home based activities - Ensure you download xampp and install on home PC or laptop (if you have a slow home internet connection – download to data key or CD here at UWE) Copy the SQL Workbook onto your data key or CD. Import the tables from the SQL Workbook into your home My. SQL DB. Begin working through some of the query examples in the workbook using PHPMy. Admin. UFIE 8 K-15 -M Data Management 2011 16