SQL – part 1

1. SQL – part 1 SQL: DDL and DML NOEA/IT - FEN: Databases/SQL

2. Realisation of the Relational Model: SQL-based DBMSs(SQL = Structured Query Language) • SQL is much more than merely queries – it includes • DDL • Data Definition Language • DML • Data Manipulation Language • DCL • Data Control Language NOEA/IT - FEN: Databases/SQL

3. SQL-Versions • SQL has been implemented by many different DBMS-manufactures • SQL is to a large extend the same for most DBMSs – close to a de facto standard • Standards:SQL86 (SQL1), SQL89 (SQL1½), SQL92 (SQL2), SQL3 (SQL9x/SQL2000? - eventually SQL-99) • SQL2 is still the most common standard. • SQL-99 is the newest standard (released in 2002) • Most manufactures have their own extensions (and omissions) to the standard NOEA/IT - FEN: Databases/SQL

4. SQL2 - DDL Supports domains: • Basic types, INT, CHAR, strings etc. • Date-Time • Programmer defined types: CREATE DOMAIN cprType AS CHAR(10); CREATE DOMAIN postalType AS CHAR(4); Types allows the compiler/interpreter to check for some logical errors Not in MS SQL Server 2000 NOEA/IT - FEN: Databases/SQL

5. SQL2 - DDL CREATE TABLE Client ( cprno cprType NOT NULL, …. postCode postalType, …, CONSTRAINT ClientPK PRIMARY KEY(cprno), CONSTRAINT PCodeCityFK FOREIGN KEY(postCode) REFERENCES PCodeCity(pCode) ON UPDATE CASCADE ON DELETE SET NULL); Should make NOT NULL unnecessary NOEA/IT - FEN: Databases/SQL

6. SQL2 - DDL CREATE TABLE PCodeCity ( pCode postalType NOT NULL, city VARCHAR(30) NOT NULL, CONSTRAINT PCodeCityPK PRIMARY KEY(pCode)); Also see Elmasri figure 8.1 and 8.2 NOEA/IT - FEN: Databases/SQL

7. SQL2 - DDL • DROP SCHEMA • DROP TABLE • ALTER TABLE • ALTER TABLE • DROP CONSTRAINT • ADD CONSTRAINT NOEA/IT - FEN: Databases/SQL

8. SQL2 - DML SELECT UPDATE INSERT DELETE NOEA/IT - FEN: Databases/SQL

9. SQL2 – DML: SELECT Queries: SELECT <attribute-list> FROM <tables> [WHERE <condition>] [GROUP BY <attribute-list>] [HAVING <condition>] [ORDER BY <attribute-list>] [...]: WHERE, GROUP BY, HAVING and ORDER BY may be omitted. NOEA/IT - FEN: Databases/SQL

10. The Company Database NOEA/IT - FEN: Databases/SQL

11. Company on SQL Server • Script: NOEA/IT - FEN: Databases/SQL

12. Relational Algebra - Overview NOEA/IT - FEN: Databases/SQL

13. SQL2 - DML (Q0): Row and coulomb selection: SELECT BDATE, ADDRESS FROM EMPLOYEE WHERE FNAME = ’John’ AND MINIT = ’B’ AND LNAME = ’Smith’ All attributes: SELECT * ---- NOEA/IT - FEN: Databases/SQL

14. SQL2 - DML (Q1): JOIN: SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE, DEPARTMENT WHERE DNAME = ’Research’ AND DNO = DNUMBER Last term in the WHERE-clause is the join-condition. If omitted the result will be the Cartesian product. NOEA/IT - FEN: Databases/SQL

15. SQL2 - DML (Q2): JOIN several tables: SELECT PNUMBER, DNUM, LNAME, ADDRESS FROM PROJECT, EMPLOYEE, DEPARTMENT WHERE PLOCATION = ’Stafford’ AND DNUM = DNUMBER AND SSN = MGRSSN Note: Two join-conditions in the WHERE-clause. NOEA/IT - FEN: Databases/SQL

16. SQL2 - DML (Q8): Ambiguous attribute names and aliases: SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM EMPLOYEE E, EMPLOYEE S WHERE E.SUPERSNN = S.SSN EMPLOYEE is joined with itself using the aliases E and S. ’.’ (”dot”)-notation may also be used to resolve ambiguous attribute names. NOEA/IT - FEN: Databases/SQL

17. SQL2 - DML SQL-tables are NOT sets (in the math sense of the word set): (Q11): SELECT SALARY FROM EMPLOYEE (Q11A): SELECT DISTINCT SALARY FROM EMPLOYEE NOEA/IT - FEN: Databases/SQL

18. SQL2 - DML (Q4): Set operations: (SELECT PNUMBER FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE LNAME = ’Smith’ AND DNUM = DNUMBER AND MGRSSN = SSN) UNION (SELECT PNUMBER FROM PROJECT, WORKS_ON, EMPLOYEE WHERE LNAME = ’Smith’ AND PNO = PNUMBER AND ESSN = SSN) Returns a set!!! (also INTERSECT and EXCEPT) NOEA/IT - FEN: Databases/SQL

19. SQL2 - DML Updates: • Inserting rows: INSERT • Deleting rows: DELETE • Updating row values: UPDATE As SELECT they work on tables. NOEA/IT - FEN: Databases/SQL

20. SQL2 - DML Inserting a single row: INSERT INTO EMPLOYEE VALUES (’Richard’,’K’,’Marini’,’653298653’, ’30-DEC-52’,’98 Oak Forest, Katy, ’TX’,’M’,37000,’987654321’,4) Inserting a single row, only selected attributes: INSERT INTO EMPLOYEE(FNAME,LNAME,SSN) VALUES (’Richard’,’Marini’,’653298653’) Is rejected if any of the other attributes is defined NOT NULL and doesn’t have defined a default value. NOEA/IT - FEN: Databases/SQL

21. SQL2 - DML Inserting using sub SELECTs: CREATE TABLE DEPTS_INFO (DEPT_NAME VARCHAR(15), NO_OF_EMPS INTEGER, TOTAL_SAL INTEGER); INSERT INTO DEPTS_INFO SELECT DNAME, COUNT(*), SUM(SALARY) FROM DEPARTMENT, EMPLOYEE WHERE DNUMBER = DNO GROUP BY DNAME; Note DEPTS_INFO is not automatically updated if changes are made in the othe tables. It is a base table. NOEA/IT - FEN: Databases/SQL

22. SQL2 - DML Deleting rows: DELETE FROM EMPLOYEE WHERE LNAME =’Brown’ DELETE FROM EMPLOYEE WHERE SSN = ’123456789’ DELETE FROM EMPLOYEE WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME = ’Research’) DELETE FROM EMPLOYEE (Not equivalent to: ´DROP TABLE EMPLOYEE’. Why not?) NOEA/IT - FEN: Databases/SQL

23. SQL2 - DML Updating rows: UPDATE PROJECT SET PLOCATION = ’Bellaire’, DNUM = 5 WHERE PNUMBER = 10 UPDATE EMPLOYEE SET SALARY = SALARY*1.1 WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME = ’Research’) Note, that it is only possible to affect one table in one UPDATE statement. NOEA/IT - FEN: Databases/SQL

24. Exercises • The Company Database: • Run the CREATE-scripts on MS SQL Server • Do some of examples in Elmasri chapter 8 • To which extend does SQL Server obey to the standard? Can you find any divergences? • The VW Database: • Run the CREATE-scripts • Implement the queries from vwDatabase.pdf in SQL NOEA/IT - FEN: Databases/SQL