UVA ACADEMIC COMPUTING CENTERLanguage-1Query Languages in PracticeDeviation from theory- for implementation restrictions and convenienceconsiderations, languages used in commercial productsto not confirm precisely to relational algebra andtuple/domain calculusThree famous ones- SQL (structured query language) developed atIBM Almaden for Systems R (later DB2), originallycalled SEQUEL (structured English query language)- QUEL (query lanuage) developed at UC Berkeleyfor INGRES (university as well as commercial)- QBE (query by example) developed at IBM Watson Lab,available as QMF (query management facility) of DB2UVA ACADEMIC COMPUTING CENTERLanguage-2SQLStandard relational database language- ANSI (American national standards institute) SQL1: 1986- ANSI-ISO SQL2: 1992a revised and expanded standard, also called SQL92- plans are underway for SQL3: extend SQL withobject-oriented and other recent database conceptsA comprehensive database language- DDL: defining and modifying schema- DML: based on relational algebra and tuple calculus- embedded language: embedded in general-purposelanguages such as PL/I, Pascal, Fortran, and C.- view definition and access authorization- integrity constaints- transaction control: begin/end transactions, lockingUVA ACADEMIC COMPUTING CENTERLanguage-3Basic Syntax of SQLConsisting of select from where blockselect <attribute list> … projectionfrom <relations> … Cartesian productwhere <conditions> … selectionselect A1, ..., Anfrom r1, ..., rmwhere P=A1. . .An(P(r1.. rm))Result is a relation- formal query languages are based on the notion thatrelation is a set: no duplicates- SQL allows duplicate tuples in the resulting relationbecause it is time-consuming.- for duplicate elimination, use unique or distinctafter selectselect distinct branch-namefrom accountUVA ACADEMIC COMPUTING CENTERLanguage-4Features of SQLAllows general where conditions- unspecified where clause: all tuples are selectedselect distinct SSN select SSN, Dept-Namefrom Employee from Employee, Dept- membership testselect distinct C-namefrom Depositwhere B-name in ("Perry", "Thornton")Nested select structure- select can be included in the where clause<ex> Find branches that have a customer having an accountan loan over $200 from the same branch.select B-namefrom Depositwhere C-name in (select C-namefrom Borrowwhere Amount > 200)UVA ACADEMIC COMPUTING CENTERLanguage-5Features of SQLRelational algebra operations , ,<ex> Find all customers having a loan, an account, or bothat Thornton branch(select C-namefrom Depositwhere B-name = "Thornton")union(select C-namefrom Borrowwhere B-name = "Thornton")Membership test for a tuple<ex> Find all customers who have both a loan and an accountat Thornton branchselect C-namefrom Borrowwhere B-name = "Thornton" and<B-name, C-name> in(select B-name, C-namefrom Deposit)UVA ACADEMIC COMPUTING CENTERLanguage-6Features of SQLNo direct representation of natural join in original SQL- relatively simple to express using SQL<ex> Find all pubs which serve some beer someone likesP(S L)select Pub (can be select S.Pub if ambiguous)from Serves, Likeswhere S.Beer = L.Beer- allowed in SQL2 (natural & outer join with conditions)select Pubfrom (Serves join Likes on Serves.Beer=Likes.Beer)Tuple variables- useful when the same relation is referred twice<ex> Find all cusmoters who have an account at the samebranch at which Jones has oneselect T.C-namefrom Deposit S, Deposit T (or Deposit as S, Deposit as T)where S.C-name = "Jones" and S.B-name = T.B-nameUVA ACADEMIC COMPUTING CENTERLanguage-7Features of SQLSet comparisons: some, any, all, contains<ex> Find all branches having greater assets than allbranches in Cheville.select B-namefrom Branchwhere Assets > all(select assetsfrom Branchwhere B-city ="Cheville")<ex> Find all customers who have an account at allbranches in Cheville.select S.C-namefrom Deposit Swhere (select T.B-namefrom Deposit Twhere S.C-name = T.C-name)contains(select B-namefrom Branchwhere B-city = "Cheville")UVA ACADEMIC COMPUTING CENTERLanguage-8Features of SQLOrdering and empty relation checking- default is ascending order (asc)<ex> Find all customers in reverse alphabetic order havinga loan at Perry branchselect distinct C-namefrom Borrowwhere B-name = "Perry"order by C-name desc<ex> Find all customers who have an account at Perry branch.select distinct C-namefrom Customerwhere exists (select *from depositwhere Deposit.C-name = Customer.C-nameand B-name = "Perry")UVA ACADEMIC COMPUTING CENTERLanguage-9Features of SQLAggregate functions and grouping- built-in functions: count, sum, max, min, avg- aggregate functions can be applied to subgroups oftuples in a relation, based on some attribute values<ex> Find average account balance at each branch.select B-name, avg(Balance)from Depositgroup by B-Name- conditions to apply to groups: having clause<ex> Find only the branches with average balance > $1,000select B-name, avg(Balance)from Depositgroup by B-Namehaving avg(Balance) > 1000UVA ACADEMIC COMPUTING CENTERLanguage-10Update Statements in SQLInsert, delete, updateinsert into Depositvalues ("Perry", 1234, "Smith", 1200)insert into Deposit(A#, C-name, B-name, Balance)values (1234, "Smith, "Perry", 1200)insert into Deposit(A#, C-name, Balance)values (1234, "Smith, 1200)insert into Deposit(A#, C-name, B-name, Balance)values (1234, "Smith, null, 1200)delete (from) depositdelete (from) depositwhere C-name = "Smith"update Depositset Balance = Balance * 1.05where Balance > 1000UVA ACADEMIC COMPUTING CENTERLanguage-11Data Definition and Changes in SQLData definition language allows specification- database schema- domain of values associated with each attribute- integrity constraints, security, indexcreate table r (A1D1, A2D2, ..., AnDn,<integrity-constraints>)Example:create table branch(branch-name char(20) not null,branch-city char(30),assets integer,primary key (branch-name),check (assets >= 0))UVA ACADEMIC COMPUTING CENTERLanguage-12Data Definition and Changes in SQLTwo delete (drop) behavior options- restricted: dropped only if it not referenced- cascade: all referring constraints are dropped, toodelete from r … delete all the tuplesdrop table r … delete all the tuples as well as schemaSchema change- adding/dropping attribute, changing an attributedefinition and relation constraintsalter table Customer add SSN intalter table Customer drop secret-addressUVA ACADEMIC COMPUTING CENTERLanguage-13Views in SQLA view in SQL terminology- a single table that is derived