New version page

USC CSCI 585 - Session6

Upgrade to remove ads

This preview shows page 1-2 out of 7 pages.

Save
View Full Document
Premium Document
Do you want full access? Go Premium and unlock all 7 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 7 pages.
Access to all documents
Download any document
Ad free experience

Upgrade to remove ads
Unformatted text preview:

Session 6: SQL DML (CH-4) CSCI-585, Cyrus Shahabi (Some example queries, but you need to go read the book and do more exercise on your own, not everything is covered!) Emp (SS#, name, age, salary, dno) Dept (dno, dname, floor, mgrSS#) • SQL provides commands to change the state of database: insert, delete, and update. • Insert has two different syntax: 1. insert into rel-name values value list 2. insert into rel-name select To illustrate, assume the existence of two relations: register(sid, sname, paid, course#) and CSCI585(sid,sname). If Joe and Bob register for csci585 without having paid: insert into register values(666-66-6666, `Joe', No, 585) (777-77-7777, `Bob', No, 585) To insert all CSCI585 student into CSCI585 relation who have paid: insert into CSCI585 select sid, name from register r where r.paid = `yes' and r.course#=585 Note that the target list of the select command must confirm to the schema of CSCI585 • Delete has the following syntax: delete rel-name where qualification Example: Fire all those employees whose salary is less than average. delete Emp where salary < (select avg(salary) from Emp) Problem: Average changes as we delete! Some versions disallow the above types of delete; some enforce the following semantic: Step1: execute query: select * from rel-name where qualificationStep 2: remove tuples found in Step 1 from rel-name • Update command has the following syntax: update rel-name set target-list where qualification • Example: Give a 10% raise to all employees in the toy department. update Emp set salary = 1.1 * salary where SS# in ( select e.SS# from Emp e, Dept d where e.dno = d.dno and d.dname = `Toy') • What if we wanted to give a 10% raise to all employees who earn less than average (same discussion as delete)? update Emp set salary = 1.1 * salary where salary < (select avg(salary) from Emp)• Hence, the semantic of update is as follows: Step 1: Execute the following two queries: insert into del-temp select full-target-list from rel-name where qualification insert into app-temp select extended target list from rel-name where qualification Extended target list in our example would be: (SS#, name, age, sal * 1.1, dno). Full target list in our example would be: (SS#, name, age, sal, dno). Step 2: Remove tuples in del-temp from rel-name Step 3: Insert tuples in app-temp into rel-name • Order by: To sort the results of a query. Example: List all employees in ascending order by age and descending order by salary (default is ascending) select SS#, name from Emp order by age asc, salary desc • Views: To provide a higher level of abstraction. Syntax: create view v as <query expression> Example: A view of all employees working in toy department create view Toy-employee asselect SS#, name, salary from Emp, Dept where Emp.dno = Dept.dno and dname = ‘Toy’ A view name can appear in any place that a relation name may appear. • Insertion to views: Use of null values! • Updates on views: Works for views based on single relation where a candidate key of the base relation is included in the view attributes. Forbidden (ambiguous) on a view which is defined in terms of more than one relation, or on views with grouping and aggregate functions). create view AvgDeptSal (dno, dname, AvgSalary) as select d.dno, d.dname, avg(salary) from Emp e, Dept d where e.dno = d.dno group by dno • Data definition:1. create schema s Creates a schema! 2. create table r (A1 D1, A2 D2, …, An Dn): create relation or object tables. A is the attribute name and D is its domain data type. (Look into the book and Oracle manual for details. Different products have different syntaxes. You can define primary keys and foreign keys here as well.) 3. drop table r Get rid of the entire relation r 4. delete r Only delete the tuples but keep the relation 5. alter table r add A D Only in some versions (modifies the database


View Full Document
Download Session6
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Session6 and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Session6 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?