Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 1SQL DMLSession 6 (CSCI-585)Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 2Example SchemaEmp (EmpNo, Name, Job, Sal, Comm, HireDate, Mgr, DeptNo) Dept (DeptNo, Name, Location) DeptNo Number(2)Name Varchar2(14)Location Varchar2(13)EmpNo Number(4)Name Varchar2(10)Job Varchar2(9)Sal Number(7,2)Comm Number(7,2)HireDate DateMgr Number(4)DeptNo Number(2)EmpDeptDatabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke 3Example RelationsEMPNO NAME JOB MGR HIREDATE SAL COMM DEPTNO7369 SMITH CLERK 7902 17-DEC-80 800 207499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 307521 WARD SALESMAN 7698 22-FEB-81 1250 500 307566 JONES MANAGER 7839 02-APR-81 2975 207654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 307698 BLAKE MANAGER 7839 01-MAY-81 2850 307782 CLARK MANAGER 7839 09-JUN-81 2450 107788 SCOTT ANALYST 7566 19-APR-87 3000 207839 KING PRESIDENT 17-NOV-81 5000 107844 TURNER SALESMAN 7698 08-SEP-81 1500 0 307876 ADAMS CLERK 7788 23-MAY-87 1100 207900 JAMES CLERK 7698 03-DEC-81 950 307902 FORD ANALYST 7566 03-DEC-81 3000 207934 MILLER CLERK 7782 23-JAN-82 1300 10DEPTNO NAME LOCATION10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO 40 OPERATIONS BOSTONDeptEmpDatabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke 4INSERT StatementTo create a tuple in SQL the following ‘Insert’ command is required:insert into R (attribute1, attribute2, … attributen )values (value1, value2, … valuen) 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)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!Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 5Group INSERT StatementTo 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 ]insert into DepA (staffno, name, job, hiredate)select empno, name, job, hiredatefrom Empwhere deptno = 10;Example: copy details of all employees that work in department 10 from the Emp relation into the DepA relation. corresponding attributes have to be of the same typeEach tuple to be inserted has to be unique!Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 6DELETE Statementdelete from R[where condition-expression]To delete a set of tuples in SQL the following ‘Delete’ command is used:Example: remove details of all employees that work in department 10 from the Emp relation. Delete from Empwhere deptno = 10;If the where clause is omitted then alltuples in the relation will be removed!Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 7UPDATE StatementTo alter a set of tuples in SQL the following ‘Update’ command is used:update Rset attribute1 = datavalue1, attribute2 = datavalue2, ...attributen = datavaluen[where condition-expression]Example: increase the salary of all employees that work in department 10 by 10%. update Empset sal = sal *1.1where deptno = 10;If the where clause is omitted then alltuples in the relation will be altered!Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 8Modification of the Database Modification of the Database ––UpdatesUpdates Increase all accounts with balances over $10,000 by 6%, all other accounts receive 5%. Write two update statements:update accountset balance = balance ∗ 1.06where balance > 10000update accountset balance = balance ∗ 1.05where 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 9Case Statement for Conditional Case Statement for Conditional UpdatesUpdates Same query as before: Increase all accounts with balances over $10,000 by 6%, all other accounts receive 5%.update accountset balance = casewhen balance <= 10000 then balance *1.05else balance * 1.06endDatabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke 10ORDER BYselect emp.name, dept.namefrom Emp, Deptwhere (emp.deptno = dept.deptno)and (emp.deptno = 10 or emp.deptno = 30)order by emp.name asc;Blake SalesKing Accountingselect emp.name, dept.namefrom Emp, Deptwhere (emp.deptno = dept.deptno)and (emp.deptno = 10 or emp.deptno = 30)order by dept.name desc;Blake SalesKing Accountingselect namefrom Deptorder by name;AccountingSalesRemember in relations Remember in relations neither tuples nor attributes neither tuples nor attributes have any intrinsic order!have any intrinsic order!Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 11Views in SQL A SQL view is a virtual table that is derivedfrom 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 queryDatabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke 12ExampleA possible view definitionCREATE VIEW StudOccupied ASSELECT g.StudId, SUM(Hours ) AS OccupiedFROM Grades g, Course pWHERE g.CourId = p.CourId AND Grade IS NULLGROUP BY StudId ;Deleting a viewDROP VIEW StudOccupied ;Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 13Update of a ViewUpdate of a ViewCreate a view of all loan data in loan relation, hiding the amount attributecreate view branch-loan asselect branch-name, loan-numberfrom loan Add a new tuple to branch-loaninsert into branch-loanvalues (‘Perryridge’, ‘L-307’)This insertion must be represented by the insertion of