SQL DML Session 6 CSCI 585 Database Management Systems 3ed R Ramakrishnan and J Gehrke 1 Example Schema Dept DeptNo Name Location Emp EmpNo Name Job Sal Comm HireDate Mgr DeptNo Dept DeptNo Name Location Number 2 Varchar2 14 Varchar2 13 Emp Database Management Systems 3ed R Ramakrishnan and J Gehrke EmpNo Name Job Sal Comm HireDate Mgr DeptNo Number 4 Varchar2 10 Varchar2 9 Number 7 2 Number 7 2 Date Number 4 Number 2 2 Example Relations Emp Dept EMPNO 7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 DEPTNO 10 20 30 40 NAME SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER JOB CLERK SALESMAN SALESMAN MANAGER SALESMAN MANAGER MANAGER ANALYST PRESIDENT SALESMAN CLERK CLERK ANALYST CLERK NAME ACCOUNTING RESEARCH SALES OPERATIONS MGR 7902 7698 7698 7839 7698 7839 7839 7566 7698 7788 7698 7566 7782 HIREDATE 17 DEC 80 20 FEB 81 22 FEB 81 02 APR 81 28 SEP 81 01 MAY 81 09 JUN 81 19 APR 87 17 NOV 81 08 SEP 81 23 MAY 87 03 DEC 81 03 DEC 81 23 JAN 82 LOCATION NEW YORK DALLAS CHICAGO BOSTON Database Management Systems 3ed R Ramakrishnan and J Gehrke SAL 800 1600 1250 2975 1250 2850 2450 3000 5000 1500 1100 950 3000 1300 COMM DEPTNO 20 300 30 500 30 20 1400 30 30 10 20 10 0 30 20 30 20 10 3 INSERT Statement To create a tuple in SQL the following Insert command is required insert into R attribute1 attribute2 attributen values value1 value2 valuen insert into Dept deptno name location values 10 Accounting New York insert into Dept deptno name location values 30 Sales Chicago The insert order matters in terms of referential integrity constraints insert into Emp empno name job sal comm hiredate mgr deptno values 7839 King President 5000 NULL 17 Nov 81 NULL 10 insert into Emp empno name job sal comm hiredate mgr deptno values 7698 Blake Manager 1600 NULL 01 May 81 7839 30 Database Management Systems 3ed R Ramakrishnan and J Gehrke 4 Group INSERT Statement To create a set of tuples in SQL the following Insert command can be used insert into R attribute1 attribute2 attributen select attribute1 attribute2 attributen from relation1 relation2 relationn where condition expression group by attribute1 attribute2 attributen having condition expression order by attribute1 attribute2 attributen Example copy details of all employees that work in department 10 from the Emp relation into the DepA relation insert into DepA staffno name job hiredate select empno name job hiredate from Emp where deptno 10 corresponding attributes have to be of the same type Each tuple to be inserted has to be unique Database Management Systems 3ed R Ramakrishnan and J Gehrke 5 DELETE Statement To delete a set of tuples in SQL the following Delete command is used delete from R where condition expression Example remove details of all employees that work in department 10 from the Emp relation Delete from Emp where deptno 10 If the where clause is omitted then all tuples in the relation will be removed Database Management Systems 3ed R Ramakrishnan and J Gehrke 6 UPDATE Statement To alter a set of tuples in SQL the following Update command is used update R set attribute1 datavalue1 attribute2 datavalue2 attributen datavaluen where condition expression Example increase the salary of all employees that work in department 10 by 10 update Emp set sal sal 1 1 where deptno 10 If the where clause is omitted then all tuples in the relation will be altered Database Management Systems 3ed R Ramakrishnan and J Gehrke 7 Modification of the Database Updates Increase all accounts with balances over 10 000 by 6 all other accounts receive 5 Write two update statements update account set balance balance 1 06 where balance 10000 update account set balance balance 1 05 where balance 10000 The order is important Can be done better using the case statement next slide Database Management Systems 3ed R Ramakrishnan and J Gehrke 8 Case Statement for Conditional Updates Same query as before Increase all accounts with balances over 10 000 by 6 all other accounts receive 5 update account set balance case when balance 10000 then balance 1 05 else balance 1 06 end Database Management Systems 3ed R Ramakrishnan and J Gehrke 9 ORDER BY select emp name dept name from Emp Dept where emp deptno dept deptno and emp deptno 10 or emp deptno 30 order by emp name asc select emp name dept name from Emp Dept where emp deptno dept deptno and emp deptno 10 or emp deptno 30 order by dept name desc select name from Dept order by name Accounting Sales Blake King Sales Accounting Blake King Sales Accounting Remember in relations neither tuples nor attributes have any intrinsic order Database Management Systems 3ed R Ramakrishnan and J Gehrke 10 Views in SQL A SQL view is a virtual table that is derived from other base or virtual tables Base tables are defined by CREATE TABLE command and are permanently stored in a database Virtual tables are defined by the CREATE VIEW command to avoid defining complex SQL retrieval expressions repeatedly The definition of a view is stored in the Catalog but it is not stored in the database itself so it is computed every time it is used in a query Database Management Systems 3ed R Ramakrishnan and J Gehrke 11 Example A possible view definition CREATE VIEW StudOccupied AS SELECT g StudId SUM Hours AS Occupied FROM Grades g Course p WHERE g CourId p CourId AND Grade IS NULL GROUP BY StudId Deleting a view DROP VIEW StudOccupied Database Management Systems 3ed R Ramakrishnan and J Gehrke 12 Update of a View Create a view of all loan data in loan relation hiding the amount attribute create view branch loan as select branch name loan number from loan Add a new tuple to branch loan insert into branch loan values Perryridge L 307 This insertion must be represented by the insertion of the tuple L 307 Perryridge null into the loan relation Updates on more complex views are difficult or impossible to translate and hence are disallowed Most SQL implementations allow updates only on simple views without aggregates defined on a single relation Database Management Systems 3ed R Ramakrishnan and J Gehrke 13