SQL Reminder of SQL Bogdan Shishedjiev SQL 1

Subsets of language • Data definition language (DDL) – – – Domain definition Schema definition Constraint definition View definition Access right definition • Data manipulation language (DML) – – Queries Tuple insertion Tuple deletion Tuple modification Bogdan Shishedjiev SQL 2

DDL • Domain definition – Simple domains • • • Chars Numbers Date/Time Raw BLOB Logical – Named domain – only in some DBMS Bogdan Shishedjiev SQL 3

DDL • Create Table CREATE TABLE име ( Attribut 1 type 1, Attribut 2 type 2, . . . ); – Definition by Query CREATE TABLE nom AS SELECT. . – Default Value Definition default(expression) – Constraints • UNIQUE • NOT NULL • Primary Key CONSTRAINT Keyname PRIMARY KEY (Attrname 1, Attrname 2, . . . ) • Foreign Key CONSTRAINT Keyname FOREIGN KEY(Attrname, . . . ) REFERENCES Tablename (Attrname, . . . ) • CHECK(expression) Bogdan Shishedjiev SQL 4

DDL • Table elimination DROP TABLE name; • Table modification ALTER TABLE name ADD COLUMN(name_of_column type, . . . ); - adds an attribute/ attributs ALTER TABLE name ADD CONSTRAINT. . . ; ALTER TABLE name MODIFY(column type. . ); changes the attribute type ALTER TABLE name DROP column , . . ; removes attribute Bogdan Shishedjiev SQL 5

DDL • Indexes – Creation CREATE [UNIQUE] INDEX name_index ON name_table (attribut [ASCIDESC], . . . ); – Deletion DROP INDEX name_index; • Views – Creation CREATE VIEW name [(names of columns)] AS SELECT. . ; – Deletion DROP VIEW name ; Bogdan Shishedjiev SQL 6

Example CREATE TABLE DEPT ( DEPTNOINTEGER NOT NULL, DNAME VARCHAR(14) CHARACTER SET ISO 8859_1, LOC VARCHAR(13) CHARACTER SET ISO 8859_1, CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO) ); CREATE TABLE EMP ( EMPNO INTEGER NOT NULL, ENAME VARCHAR(10) CHARACTER SET ISO 8859_1, JOB VARCHAR(9) CHARACTER SET ISO 8859_1, MGR INTEGER CHECK (MGR is. NULL or Dept. No = (select Dept. No from Employee E where E. Reg. No = Superior) , HIREDATE TIMESTAMP, SAL NUMERIC(9, 2), COMM NUMERIC(9, 2), DEPTNOINTEGER, CONSTRAINT PK_EMP PRIMARY KEY (EMPNO) ); ALTER TABLE EMP ADD CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO); Bogdan Shishedjiev SQL 7

Example DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON EMP NO ENAME JOB MGR HIREDATE SAL 7369 SMITH CLERK 7902 17. 12. 1980 800 7499 ALLEN SALESMAN 7698 20. 2. 1981 1600 7521 WARD SALESMAN 7698 22. 2. 1981 1250 7566 JONES MANAGER 7839 02. 4. 1981 2975 7654 MARTIN SALESMAN 7698 28. 9. 1981 1250 7698 BLAKE MANAGER 7839 01. 5. 1981 2850 30 7782 CLARK MANAGER 7839 09. 6. 1981 2450 10 7788 SCOTT ANALYST 7566 19. 4. 1987 3000 20 7839 KING PRESIDENT 17. 11. 1981 5000 10 7844 TURNER SALESMAN 7698 08. 9. 1981 1500 30 7876 ADAMS CLERK 7788 23. 5. 1987 1100 20 7900 JAMES CLERK 7698 03. 12. 1981 950 30 7902 FORD ANALYST 7566 03. 12. 1981 3000 20 7934 MILLER CLERK 7782 23. 1. 1982 1300 10 Bogdan Shishedjiev SQL COMM DEPTNO 20 300 500 30 30 20 1400 30 8

Queries • SELECT Statement SELECT [DISTINCT | ALL ]{* | expression| attribute }, . . . FROM <table [alias]>, . . . [WHERE Logical expression – condition for selection or join] [GROUP BY list of attributes] [HAVING Logical expression – condition for group selection] [UNION | INTERSECT | MINUS SELECT. . . ] [ORDER BY list of attributes [ASC | DESC] ]; Bogdan Shishedjiev SQL 9

Projection SELECT job, mgr FROM emp; /* Query 12 */ SELECT DISTINCT job, mgr FROM emp; /* Query 13 */ JOB MGR CLERK 7902 SALESMAN 7698 MANAGER 7839 ANALYST 7566 SALESMAN 7698 PRESIDENT MANAGER 7839 CLERK 7788 MANAGER 7839 CLERK 7698 ANALYST 7566 CLERK 7782 PRESIDENT SALESMAN 7698 CLERK 7788 CLERK 7698 ANALYST 7566 CLERK 7782 Bogdan Shishedjiev SQL 10

Operators 'NAME' Code Operator - Inversing the sign *, / Multiplication, division +, -, || (&) Addition, substraction, concatenation SELECT ENAME || '(' || EMPNO || ')' 'NAME', 2 * SAL 'Double. Sal' FROM EMP; (Query 39) 'Double. Sal' SMITH(7369) 1600 ALLEN(7499) 3200 WARD(7521) 2500 JONES(7566) 5950 MARTIN(7654) 2500 BLAKE(7698) 5700 CLARK(7782) 4900 SCOTT(7788) 6000 KING(7839) 10000 TURNER(7844) 3000 ADAMS(7876) 2200 JAMES(7900) 1900 FORD(7902) 6000 MILLER(7934) 2600 Bogdan Shishedjiev SQL 11

Selection SELECT * FROM emp WHERE deptno=10; /* Query 14 */ EMPNO 7782 7839 7934 ENAME CLARK KING MILLER JOB MGR MANAGER 7839 PRESIDENT CLERK 7782 HIREDATE 09. 6. 1981 17. 11. 1981 23. 1. 1982 Bogdan Shishedjiev SQL SAL 2450 5000 1300 COMM DEPTNO 10 10 10 12

Selection & Projection SELECT ename, job, sal FROM emp WHERE job = ‘MANAGER’ AND sal>2500; /* Query 15 */ ENAME JOB JONES MANAGER BLAKE MANAGER SAL 2975 2850 Bogdan Shishedjiev SQL 13

Predicates BETWEEN / NOT BETWEEN SELECT ename FROM emp WHERE hiredate BETWEEN 1. 1. 81 AND 31. 12. 81; /* Query 1 */ IN / NOT IN SELECT ename FROM emp WHERE job In ('ANALYST', 'MANAGER'); /* Query 2 */ ENAME ALLEN WARD JONES MARTIN BLAKE CLARK KING TURNER JAMES FORD ENAME SMITH JONES BLAKE CLARK ADAMS JAMES MILLER Bogdan Shishedjiev SQL 14

Predicates • • LIKE/NOT LIKE compares strings with wildcards “%” substitutes for any 0 or more characters, “_” substitutes for any one character. In Microsoft DBMSs the corresponding examples are “ * “ and “? “. Examples: LIKE 'TARKO%', LIKE '%WSKI', LIKE 'A_C‘ SELECT ename, job FROM Emp WHERE. ename LIKE "b*"; /* Query 4 */. ENAME JOB BLAKE MANAGER • SELECT ename, job, sal FROM emp WHERE ename LIKE '%mi%'; /*Query 16*/ ename job sal SMITH CLERK 800 MILLER CLERK 1300 Bogdan Shishedjiev SQL 15

Aggregate • SELECT count(*) FROM emp WHERE deptno=20; /* Query 10 */ Count 5 • SELECT AVG(sal) average FROM emp WHERE deptno=20; /* Query 11 */ Average 2175 • • SELECT count(Job) as Jobs FROM emp; SELECT count(DISTINCT Job) as Jobs FROM emp; Jobs 14 Jobs 5 Bogdan Shishedjiev SQL 16

Join • Cartesian product SELECT * FROM emp, dept; /* Query 17 */ • Join SELECT * FROM emp, dept WHERE emp. deptno = deptno; /* Query 18 */ SELECT * FROM emp INNER JOIN dept ON emp. deptno = deptno; /* Query 19 */ SELECT * FROM emp E, dept D WHERE E. deptno = D. deptno; /*Query 20 */ SELECT * FROM emp as E INNER JOIN dept as D ON E. deptno = D. deptno; /* Query 21 */ Bogdan Shishedjiev SQL 17

Join EMPN O ENAME JOB MGR HIRED SAL 7782 CLARK MANAGER 7839 09. 6. 1981 7839 KING PRESIDENT 7934 MILLER CLERK 7369 SMITH 7566 COMM E. DEPTNO DNAME LOC 2450 10 10 ACCOUNTING NEW YORK 17. 11. 1981 5000 10 10 ACCOUNTING NEW YORK 7782 23. 1. 1982 1300 10 10 ACCOUNTING NEW YORK CLERK 7902 17. 12. 1980 800 20 20 RESEARCH DALLAS JONES MANAGER 7839 02. 4. 1981 2975 20 20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 19. 4. 1987 3000 20 20 RESEARCH DALLAS 7876 ADAMS CLERK 7788 23. 5. 1987 1100 20 20 RESEARCH DALLAS 7902 FORD ANALYST 7566 03. 12. 1981 3000 20 20 RESEARCH DALLAS 7499 ALLEN SALESMAN 7698 20. 2. 1981 1600 30 30 SALES CHICAGO 7521 WARD SALESMAN 7698 22. 2. 1981 1250 500 30 30 SALES CHICAGO 7654 MARTIN SALESMAN 7698 28. 9. 1981 1250 1400 30 30 SALES CHICAGO 7698 BLAKE MANAGER 7839 01. 5. 1981 2850 30 30 SALES CHICAGO 7844 TURNER SALESMAN 7698 08. 9. 1981 1500 30 30 SALES CHICAGO 7900 JAMES CLERK 7698 03. 12. 1981 950 30 30 SALES CHICAGO Bogdan Shishedjiev SQL 18

All operators SELECT ename, job, deptno, dname FROM emp, dept WHERE emp. deptno = deptno AND job = 'CLERK'; /* Query 22 */ SELECT ename, job, deptno, dname FROM emp INNER JOIN dept ON emp. deptno = deptno WHERE job = 'CLERK'; ename job SMITH CLERK ADAMS CLERK JAMES CLERK MILLER CLERK deptno 20 20 30 10 dname RESEARCH SALES ACCOUNTING Bogdan Shishedjiev SQL 19

Grouping SELECT deptno, MIN(sal), MAX (sal) FROM emp GROUP BY deptno; /* Query 26 */ SELECT deptno, MIN(sal), MAX (sal)FROM emp WHERE job = 'CLERK' GROUP BY deptno; /* Query 27 */ deptno 10 20 30 Minsal 1300 800 950 Maxsal 5000 3000 2850 deptno Minsal 10 1300 20 800 30 950 Bogdan Shishedjiev SQL Maxsal 1300 1100 950 20

Grouping • • GROUP BY groups resulting rows by the identical values of some attributes and aggregate functions to be applied on each group. All attributes that are not parameters of an aggregate function MUST participate in grouping. Order of execution: – WHERE clause removes all rows that don’t satisfy the condition. – The grouping is accomplished and all aggregate values are calculated. – All groups that don’t satisfy the condition of HAVING clause are removed. Bogdan Shishedjiev SQL 21

Grouping • HAVING SELECT deptno, MIN(sal), MAX(sal) FROM emp GROUP BY deptno HAVING MAX(sal) >1200; /* Query 28 */ SELECT deptno, MIN(sal), MAX(sal) FROM emp WHERE job = 'CLERK' GROUP BY deptno HAVING MIN(sal)<1000; /* Query 29 */ deptno Minsal 10 1300 20 800 30 950 Maxsal 5000 3000 2850 deptno Minsal 20 800 30 950 Bogdan Shishedjiev SQL Maxsal 1100 950 22

Ordering • ORDER BY {ASC | DESC} SELECT ename, deptno, sal FROM emp ORDER BY deptno, sal DESC; /* Query 30 */ ename deptno KING 10 CLARK 10 MILLER 10 FORD 20 SCOTT 20 JONES 20 ADAMS 20 SMITH 20 BLAKE 30 ALLEN 30 TURNER 30 MARTIN 30 WARD 30 sal 5000 2450 1300 3000 2975 1100 800 2850 1600 1500 1250 Bogdan Shishedjiev SQL 23

Updating • Insertion INSERT INTO table [(col 1[, col 2…])] VALUES(list of values); or : INSERT INTO table [(col 1[, col 2…])] VALUES SELECT. . . ; – Example : INSERT INTO Emp ( EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO ) SELECT [EMPNO]+20 AS Expr 5, ENAME, "CLERK" AS Expr 2, #9/1/99# AS Expr 4, 800 AS Expr 3, COMM, 40 AS Expr 1 FROM Emp WHERE DEPTNO=10; Bogdan Shishedjiev SQL 24

Updating • Update UPDATE table или view SET {column = expression | (list of columns)=(list of expressions)} [WHERE condition] ; – Example: UPDATE Emp SET Emp. SAL = [Sal]+100 WHERE DEPTNO=40; • Delete DELETE FROM table [WHERE condition]; – Example: DELETE FROM emp WHERE DEPTNO=40; Bogdan Shishedjiev SQL 25