SQL Basic format of the select command select distinct target list from tuple variable list where qualification order by target list subset Simple query examples use this relational schema sailors sid sname rating boats bid bname colour reserve sid bid date SQL target list is an abbreviation for all attributes in the from list select from sailors s where order by s rating Each item in the target list can be as general as attribute name expression where the expression is any arithmetic or string expression over indexed tuple variables and constants It can also contain some built in functions like sqrt sin mod etc as well as aggregates coming up later SQL target list expression example With rating an integer from 1 to 10 this query gives a rating bonus to sailors who sailed two different boats on the same day select s sid s sname rating s rating 2 from sailors s reserve r reserve r2 where s sid r sid and s sid r2 sid and r date r2 date and r bid r2 bid What s wrong with the above What happens if s rating 9 before this query Domain constraints might take care of this but we need to be careful SQL Qualifications each item in a qualification where clause can be as general as expression expression Example select from where name1 s1 sname name2 s2 sname sailors s1 sailors s2 2 s1 rating s2 rating 1 SQL Further elaboration tuple variables can be implicit if the system can figure out which relation each attribute belongs to table names can be used as tuple variables Example find names ages and departments of employees who are over 40 and work on the first floor select ename age emp dname from emp dept where age 40 and floor 1 and emp dname dept dname SQL SQL provides set operators union intersect and minus Example find the names of employees who work in the toy department and make at most 60K select ename from emp where dname toy minus select ename from emp where sal 60K SQL Note that it is usually possible to phrase a single query in multiple ways The previous query could have also been written select from where intersect select from where ename emp dname toy ename emp sal 60K SQL Or also even simpler select from where ename emp dname toy and sal 60K Writing a query in different ways will usually change how efficient the query is the above query is very likely to be faster than the example using intersects and that one is likely to be faster than the one using minus SQL SQL also provides set operators contains a set being a superset of another and exists a set not being empty Both return Boolean results so may be negated using not SQL Example find the names of employees who manage all the departments on the first floor select mgr from dept d1 where select d2 dname from dept d2 where d1 mgr d2 mgr contains select dname from dept where floor 1 SQL SQL allows nested queries using the keyword in Example find the names of employees who work on the first floor select ename from emp wheredname in select dname from dept where floor 1 The same query in flat form is select dname from emp dept where emp dname dept dname and floor 1 SQL The connective in tests for set membership Similar connectives are not in set non membership op any op relationship with some tuple in the set op all op relationship with all tuples in the set where op is one of Example find the names of employees who make more than everybody on the first floor select ename from emp wheresal all select sal from emp dept where emp dname dept dname and floor 1 SQL Scoping of variables works exactly as in Pascal or C Example find the names of students who take a course from their advisor select sname from student wheres in select s from enroll where c in select c from class where prof student advisor Recap SQL Four basic commands select insert delete update SQL Insert Insert command format insert into relation name values value list or insert into relation name select statement Semantics of insert format one add the tuple corresponding to value list into relation name format two execute the select statement then add all the resulting tuples into relation name Example insert into student values 1 Carey CS Stonebraker SQL Insert Example relation register S name paid in which registered students are recorded After the end of registration week we execute insert into student select r s r name from register r where r paid yes SQL Delete Delete command format delete relation name where qualification Semantics of delete execute the corresponding select command select from where full target list relation name qualification or and then remove the resulting tuples from relation name SQL Delete Example with the following schema student s name major advisor enroll s c grade course c dept The following command expels CS majors who received a grade of less than 2 5 in a CS course delete where student major CS and s in select s from enroll course where enroll s student s and grade 2 5 and enroll c course c and dept CS SQL Update Update format update relation name settarget list where qualification Semantics of update it is equivalent to executing insert into del temp select from relation name where qualification SQL Update Semantics of update cont then executing insert into app temp select ext target list from relation name where qualification Ext target list is identical to target list in the original update command but augmented with tuple variable attribute name for all attributes of the range of tuple variable that don t appear in target list delete the tuples in del temp from relation name add the tuples in app temp to relation name SQL Update Example give a 10 grade raise to every CS major in CS564 update set where enroll grade 1 1 grade c CS564 and s in select s from student where major CS SQL Update Which is equivalent to insert into del temp select s c grade from enroll where c CS564 and s in select s from student where major CS insert into app temp select s c grade 1 1 grade from enroll where c CS564 and s in select s from student where major CS SQL Aggregates Aggregate functions are functions that take a collection of values as input and return a single value SQL supports five built in aggregate functions average avg minimum min maximum max total sum cardinality count using distinct to aggregate only unique values is often important with avg sum and count SQL Aggregates Example find the number of students select num of students count s from student why do we not need to use distinct in this example Example find the number of employee
View Full Document
Unlocking...