SQL Revision

1. SQL (sequel) is used on a most DBMS systems (e.g. Access, Oracle, Ingress, IBM DB2, MySQL) This course uses MySQL asthe DBMS SQL Revision

2. RDBMS Tables • Database consists of a set of tables • Each table has vertical columns (attributes) and horizontal rows (tuples or records) • Each row (or record) contains fields, one for each column, which can contain data values • A heading specifies the name and type of the values for each of the columns • A table can have 0, 1, or more rows • Only one data value (possibly composite) is held in any one field • Unless NOT NULL is specified in the heading, NULL values (i.e. unknown) are allowed

3. SNo SNAME STATUS CITY S1 Pink 20 London S2 White 10 Detroit S3 Blue 30 Detroit S4 Blonde 20 London S5 Brown 30 Yate Suppliers Table • Suppliers Table S, with sample values

4. SQL Tables (cont.) • The rows have no particular order unless an order is imposed upon them • The columns are strictly ordered from left to right (rarely significant – unlike 3GL record structures) • Base Table : Table with a name that exists in its own right • View: Table derived from other tables • Looks like another table to the user, but it is generated at the point of use rather than stored

5. PRIMARY and FOREIGN KEYS • A CANDIDATE KEY is one or more attributes the value of which is unique for every row • A PRIMARY KEY for any table is chosen from one of its candidate keys • A table may have one and only one primary key • A FOREIGN KEY is an attribute in a table whose values must exactly match those of a primary key of the same or a different table in order to identify the existence of a relationship between rows

6. SQL Indexes • Speed up retrieval at expense of: • space • time for updates • They are a stored file, with each entry containing : • data value • pointer to row containing that value in that field • Can be created by the user • System can choose whether or not to use it

7. SQL Data Definition Language (DDL) • CREATE TABLE • Creates an initially empty Base Table • Data values added by other commands • Any column can contain NULL values for one or more rows unless NOT NULL is specified in its type • If a column is a Primary Key it must not contain NULL values

8. SQL Definition of Supplier/Parts Database CREATE TABLE P ( PNo CHAR(6), PNAME CHAR(20), SIZE CHAR(6), WEIGHT NUMERIC(5,1), CITY CHAR(15), PRIMARY KEY ( PNo) ); CREATE TABLE S ( SNo CHAR(5), SNAME CHAR(20), STATUS NUMERIC(5), CITY CHAR(15), PRIMARY KEY (SNo) ); CREATE TABLE SP ( SNo CHAR(5), PNo CHAR(6), QTY NUMERIC(9), PRIMARY KEY (SNo, PNo), CONSTRAINT SP_P FOREIGN KEY ( SNo) REFERENCES S(SNo), CONSTRAINT SP_S FOREIGN KEY (PNo) REFERENCES P(PNo) );

9. SNo SNAME STATUS CITY SNo PNo QTY S1 Pink 20 London S1 P1 300 S1 P2 200 S2 White 10 Detroit S1 P3 400 S3 Blue 30 Detroit S1 P4 200 S4 Blonde 20 London S1 P5 100 S5 Brown 30 Yate S1 P6 100 S2 P1 300 PNo PNAME SIZE WEIGHT CITY S2 P2 400 P1 Nut BIG 12 London S3 P2 200 P2 Bolt SMALL 17 Detroit S4 P2 200 P3 Screw MEDIUM 17 Rome S4 P4 300 P4 Screw BIG 14 London S4 P5 400 P5 Cam MEDIUM 12 Detroit P6 Cog BIG 19 London Example Database: Supplier/Parts S SP P

10. SQL DDL – Built-in Types • Common types built into SQL • CHAR(n) • VARCHAR(n) • BIT(n) • NUMERIC(p,q) • DECIMAL(p,q) • INTEGER • SMALLINT • FLOAT(p) • DATE • TIME • TIMESTAMP • INTERVAL

11. SQL – Keys • At the end of a CREATE TABLE command, specify the Primary Key for that table and any Foreign Keys e.g. CREATE TABLE SP ( SNo CHAR(5) PNo CHAR(6) QTY NUMERIC(9) PRIMARY KEY (SNo,PNo) CONSTRAINT SP_P FOREIGN KEY ( SNo) REFERENCES S(SNo), CONSTRAINT SP_S FOREIGN KEY (PNo) REFERENCES P(PNo));

12. SQL DDL (cont) • Additional Commands • DROP TABLE • Removes a Base Table from the Database • ALTER TABLE • Allows changes to a base table • adding types • adding/removing columns • Must be followed by an SQL table update command e.g. ALTER TABLE S ADD DISCOUNT SMALLINT;

13. SNo SNAME STATUS CITY DISCOUNT S1 Pink 20 London S2 White 10 Detroit S3 Blue 30 Detroit S4 Blonde 20 London S5 Brown 30 Yate Suppliers Table extended Suppliers Table S, with sample values

14. SQL DDL - Indexes • CREATE INDEX and DROP INDEX • Used to create and remove indexes • The only commands that relate to indexes • Creates index on column(s) specified of named base table • If UNIQUE is specified, no two rows are allowed the same value for indexed field • e.g. CREATE UNIQUE INDEX IXS ON S (SNo); CREATE INDEX IXSP ON SP (SNo, PNo); DROP INDEX IXSP; • N.B. Indexes result in extra disc space being used and a slowing down of updates

15. SQL – DDL VIEWS • CREATE VIEW and DROP VIEW • Allows the creation and removal of views • CREATE VIEW viewname AS sql-dml • DROP viewname • e.g. CREATE VIEW GOODSUPPLIERS AS SELECT SNo,SNAME,CITY FROM S WHERE STATUS > 15; • DROP VIEW GOODSUPPLIERS;

16. SNo SNAME City S1 Pink London S3 Blue Detroit S4 Blonde London S5 Brown Yate View: GOODSUPPLIERS • N.B. Cannot access STATUS information or information, including existence, of White from this view

17. SQL – Data Manipulation Language (DML) • Four main types of DML statements in SQL • SELECT • Retrieve part of one or more tables • Most frequently used SQL statement • UPDATE • Updates the data values in an existing table • INSERT • One method of putting data values into a table • DELETE • Remove values from a table

18. SQL – SELECT • General form:- SELECT [ DISTINCT ] fields(s) FROM tables(s) [ WHERE predicate] [ ORDER BY field(s) [ GROUP BY field(s) [ HAVING predicate ] ]

19. SNo SNAME STATUS CITY SNo PNo QTY S1 Pink 20 London S1 P1 300 S1 P2 200 S2 White 10 Detroit S1 P3 400 S3 Blue 30 Detroit S1 P4 200 S4 Blonde 20 London S1 P5 100 S5 Brown 30 Yate S1 P6 100 S2 P1 300 PNo PNAME SIZE WEIGHT CITY S2 P2 400 P1 Nut BIG 12 London S3 P2 200 P2 Bolt SMALL 17 Detroit S4 P2 200 P3 Screw MEDIUM 17 Rome S4 P4 300 P4 Screw BIG 14 London S4 P5 400 P5 Cam MEDIUM 12 Detroit P6 Cog BIG 19 London Example Database: Supplier/Parts S SP P

20. SNo STATUS S1 20 S2 10 S3 30 S4 20 S5 30 SELECT Examples • SELECT SNo, STATUS FROM S • SELECT * FROM S • Selects all the columns from S, i.e. result is whole table

21. SNo STATUS S3 30 SELECT Examples (cont.) • SELECT SNo , STATUS FROM S WHERE CITY=`Detroit` and STATUS > 20 • Predicate can use any of the following: • Operators = ,( != or <>), >, >= ,< , <= • BETWEEN value1 AND value 2 • IN ( list of values ) • IS NULL • LIKE characterpattern) • Use AND and OR to combine predicates • Use NOT to negate predicates • Use parentheses where necessary

22. SNo STATUS S2 20 S1 20 S4 20 S3 30 S5 30 SELECT Examples (cont.) • Ordering of rows displayed • SELECT SNo , STATUS FROM S ORDER BY STATUS ASC • Opposite order is given by DESC

23. SNAME QTY Pink 300 White 300 SELECT EXAMPLES (cont.) • The join process produces one table from more than one other table • e.g. Get supplier names and quantities for supply of part P1 SELECT SNAME,QTY FROM S,SP WHERE (S.SNo = SP.SNo AND PNo = P1 ) ;

24. SNo SNAME SIZE QTY S1 Pink MEDIUM 400 S1 Pink BIG 200 S4 Blonde BIG 300 SELECT EXAMPLES (cont.) • e.g. Get supplier number, supplier name , quantities and size for screws SELECT SNo, SNAME, SIZE, QTY FROM S,SP,P WHERE (S.SNo = SP.SNo AND SP. PNo = P.PNo AND PNAME= `Screw`);

25. SNo SNAME STATUS CITY S1 Pink 20 London S2 White 10 Detroit S3 Blue 30 Detroit S4 Blonde 20 London S5 Brown 30 Yate P Add a preferred supplier `PrefSNo` to table P S

26. Basic Equi Joins return matched rows SELECT PNo, PNAME, SIZE, WEIGHT, SNAME, STATUS FROM P,S WHERE (P.PrefSNo = S.SNo);

27. Inner Equi Joins return matched rows SELECT PNo, PNAME, SIZE, WEIGHT, SNAME, STATUS FROM P NATURAL JOIN S; Implicit match on common attributes by DBMS using the System Catalogue Or SELECT PNo, PNAME, SIZE, WEIGHT, SNAME, STATUS FROM P INNER JOIN S ON P.PrefSNo=S.SNo;

28. Outer Joins return all rows in selected table SELECT PNo, PNAME, SIZE, WEIGHT, SNAME, STATUS FROM P LEFT JOIN S ON P.PrefSNo=S.SNo;

29. Outer Joins return all rows in selected table SELECT PNo, PNAME, SIZE, WEIGHT, SNAME, STATUS FROM P RIGHT JOIN S ON P.PrefSNo=S.SNo;

30. Join Types

31. SELECT EXAMPLES (cont.) • Aggregate functions • Enhance the power of SELECT • calculation based on the values in the column • Main ones are: • COUNT The number of values • SUM The sum of the values • AVG The average of the values • MAX and MIN The largest and smallest of the values • If argument preceded by DISTINCT, duplicate values are eliminated before applying function

32. STATUS SNAME 20 Pink 20 Blonde 10 White 30 Blue 30 Brown SELECT EXAMPLES(cont.) • DISTINCT must be specified for COUNT since COUNT(*) is used to count number of rows • e.g. SELECT COUNT(DISTINCT SNo) FROM SP returns 4 • SELECT COUNT(*) FROM SP returns 12 • GROUP BY rearranges the table represented by the FROM Clause into groups • e.g. SELECT SNAME,STATUS FROM S GROUP BY STATUS

33. PNo SUM(QTY) P1 600 P2 1000 P3 400 P4 500 P5 500 P6 100 SELECT Examples (cont.) • The clauses in the SELECT are then applied to each group • e.g. SELECT PNo, SUM(QTY) FROM SP GROUP BY PNo;

34. PNo SUM(QTY) P1 600 P2 1000 P4 500 P5 500 SELECT Examples (cont.) • The HAVING clause can be used to select groups that meet a given condition • e.g. SELECT PNo, SUM(QTY) FROM SP GROUP BY PNo HAVING COUNT(*) > 1;

35. SQL: UPDATE • Used to update existing information in a table • General form: UPDATE table SET field(s) = expression {, expression } [ WHERE predicate ] ; e.g. Double the status of all London based suppliers UPDATE P SET STATUS = 2 * STATUS WHERE CITY = ` London` ;

36. SQL: INSERT • One method for adding new values into a table • General forms: To insert a row of specific values:- INSERT INTO table [ ( field {, field } ) ] VALUES ( constant {, constant} ) ; or to insert a copy of the table produced by a SELECT command:- INSERT INTO table [ ( field { ,field } ) ] SELECT … FROM ... WHERE …; e.g. INSERT INTO P (PNo, CITY, WEIGHT) VALUES (`P7`, `Yate`, 24);

37. SQL: DELETE • To remove values from a table DELETE FROM table [ WHERE predicate ] ; e.g. DELETE FROM S WHERE SNo = `S1`;

38. More complex SQL queries • Because of the power of SQL it is possible to nest queries • 1) The result of any SELECT query is a logical table and thus a SELECT clause can be used instead of a tablename • e.g. SELECT … FROM ( SELECT … FROM … WHERE …) WHERE … ; • 2) If the result of a SELECT is a single attribute (column), then this can also behave as a set of values in a nested WHERE section • e.g. SELECT … FROM … WHERE … IN ( SELECT … FROM … WHERE … )

40. List all the products that are supplied by the supplier of our most expensive product. SELECT DISTINCTROW Stock_item.Supplier_code, Supplier.Supplier_name, Stock_item.Stock_id, Stock_item.Stock_description, Stock_item.Sales_price_per_unit FROM Supplier INNER JOIN Stock_item ON Supplier.Supplier id = Stock_item.Supplier_code WHERE Stock_item.Supplier_code=(SELECT Supplier_code FROM Stock_item WHERE Stock_item.Sales_price_per_unit=(SELECT Max(Sales_price_per_unit) FROM Stock_item)) ORDER BY Stock_item.Sales_price_per_unit;

42. DBMS has to deal with things like: • Data Integrity • Transaction Handling • Record Locking • Commit & Rollback • Before/After Imaging • Backup and Recovery • Running Stored Procedures & Triggers

44. create table EMPLOYEE ( EMP_ID NUMERIC(12) not null, DEPT_ID NUMERIC(8) not null, EMP_SSN CHAR(9) not null, EMP_FIRST_NAME VARCHAR(20) not null, EMP_LAST_NAME VARCHAR(30) not null, EMP_BIRTH_DATE DATE not null, EMP_GENDER CHAR(1) not null, EMP_HIRE_DATE DATE not null, EMP_STREET VARCHAR(80), EMP_CITY VARCHAR(40), EMP_STATE CHAR(2), EMP_ZIP CHAR(5), constraint PK_EMPLOYEE primary key (EMP_ID), constraint AK_EMP_UID2_EMPLOYEE unique (EMP_SSN), constraint AK_EMP_UID3_EMPLOYEE unique (EMP_FIRST_NAME, EMP_LAST_NAME, EMP_BIRTH_DATE, EMP_GENDER), constraint FK_EMPLOYEE_RELATIONS_DEPARTME foreign key (DEPT_ID) references DEPARTMENT (DEPT_ID))

45. create table EXEMPT_EMPLOYEE ( EMP_ID NUMERIC(12) not null, EMP_MONTHLY_SALARY NUMERIC(8,2) not null, EMP_VACATION_WEEKS SMALLINT not null, constraint PK_EXEMPT_EMPLOYEE primary key (EMP_ID), constraint FK_EXEMPT_E_INHERITAN_EMPLOYEE foreign key (EMP_ID) references EMPLOYEE (EMP_ID)) create table NON_EXEMPT_EMPLOYEE ( EMP_ID NUMERIC(12) not null, UNION_ID NUMERIC(8) not null, EMP_HOURLY_RATE NUMERIC(5,2) not null, EMP_OVERTIME_RATE NUMERIC(5,2) not null, constraint PK_NON_EXEMPT_EMPLOYEE primary key (EMP_ID), constraint FK_NON_EXEM_RELATIONS_UNION foreign key (UNION_ID) references "UNION" (UNION_ID), constraint FK_NON_EXEM_INHERITAN_EMPLOYEE foreign key (EMP_ID) references EMPLOYEE (EMP_ID)

46. Trigger1 to prevent the same emp_id value appearing in both employee subtype tables CREATE TRIGGER exempt_employee_checkBEFORE INSERT OR UPDATE OF emp_idON exempt_employeeFOR EACH ROWDECLARE dummy INTEGER := 0;BEGIN IF ( INSERTING OR (UPDATING AND :new.emp_id <> :old.emp_id)) THEN SELECT COUNT(*) INTO dummy FROM nonexempt_employee WHERE emp_id = :new.emp_id IF (dummy <> 0) THEN RAISE DUP_VAL_ON_INDEX; END IF; END IF;END; 

47. Trigger2 to prevent the same emp_id value appearing in both employee subtype tables CREATE TRIGGER non_exempt_employee_checkBEFORE INSERT OR UPDATE OF emp_idON non_exempt_employeeFOR EACH ROWDECLARE dummy INTEGER := 0;BEGIN IF ( INSERTING OR (UPDATING AND :new.emp_id <> :old.emp_id)) THEN SELECT COUNT(*) INTO dummy FROM exempt_employee WHERE emp_id = :new.emp_id IF (dummy <> 0) THEN RAISE DUP_VAL_ON_INDEX; END IF; END IF;END;

48. MySQL Doesn’t Support: • Views • Subselects • Triggers • Multi-Table Transaction Control • See Chris’ paper for MySQL limitations and an overview of SQL1-SQL4 features