Understanding SQL: A Historical Perspective and Comprehensive Overview

SQL Naveen Ashish Calit2 & ICS UC Irvine

SQL -- historical Perspective • Dr. Edgar Codd (IBM) • “A Relational Model of Data for Large Shared Data Banks" • CACM June 1970 • Standardized • 1986 by ANSI --- SQL1 • Revised in 1992 ---SQL2. • Approx 580 page document describing syntax and semantics • 1999: SQL3 • 2003: 2003 SQL3 • Players • IBM, Relational Software (Oracle), …. • Every vendor has a slightly different version of SQL • Simple and declarative

Portability • Many different implementations • Need not completely conform to standard • Own specialized versions • PL/SQL, SQL PL, Transact-SQL, ….. • Portability (of application) in one SQL implementation to another is usually messy • Standard implementation left to vendor • Not break backward compatibility for user base • “Vendor Lock-in”

SQL in Different Roles • Data Definition language: • allows users to describe the relations and constraints. • Constraint specification language: • commands to specify constraints ensured by DBMS • Query language: • relationally complete, supports aggregation and grouping • declarative -- you specify what you want and not how to retrieve, easy to learn and program • Updates in SQL: • allows users to insert, delete and modify tables • View definition language: • commands to define rules • updates through views generally not supported

SQL in Different Roles • Embedded SQL: • has been embedded in a variety of host languages--C, C++, PERL, Smalltalk, Java (vendor dependent) • Impedance mismatch: SQL manipulates relations that are sets--- programming languages do not handle sets as efficiently. • Transaction Control: • commands to specify beginning and end of transactions.

SQL as Data Definition Language • Allows users to • specify the relation schemas • domain of each attribute • integrity constraints • set of indices to be maintained on the relation • we will learn what indices are later • security and authorization information for each relation • the physical storage structure for each relation on disk.

Domain Types • char(n): fixed length char string • varchar(n): variable length char string • int or integer • smallint • numeric(p,d): fixed-point number of given precision • real, double precision • float(n): floats with a given precision • date: containing year,month, date • time: in hours, minutes and seconds • Null value is part of each domain • Define new domains: • create domainperson-name char(20)

Schema Definition Create table r ( A1 D1 [not null] [default V1] A2 D2 [not null] [default V2] … An Dn [not null] [default Vn] <integrity constraint 1> <integrity constraint 2> … <integrity constraint k> ) Integrity constraints 1 … k could be: • primary key • candidate key • foreign key • check(predicate) specifies predicate that must be satisfied by each tuple

SQL as DDL create table Employee ( name char[15] not null age smallint sex (M,F) default M ss# integernot null spouse_ss# integer dept# integer ) • Primary Key (ssno) • Unique(spouse_ss#) • Check (age >=20 and age <=70) • Foreign key (dept#) references Dept(dept#) on delete cascade on update cascade Default value is Male Don’t allow null values • if dept# updated/deleted in Dept table, cascade update/delete to Employee • if null/default instead of cascade, null/default value taken by dangling tuples in Employee

SQL as DDL • Attributes in primary key required to be not null • Attributes in candidate key can take null values unless specified otherwise • Unique constraint violated if two tuples have exactly same values for attributes in unique clause provided values are not null • Null also permitted for attributes in foreign key. • Foreign key constraint automatically satisfied if even one attribute in foreign key is null. • Predicate in check clause can be very complex and can include SQL queries inside them • Other DDL Statements: • drop table r • alter table r add A D • alter table r drop A

Indexes • REALLYimportant to speed up query processing time. • Suppose we have a relation • Person (name, social security number, age, city) • An index on “social security number” enables us to fetch a tuple for a given ssn very efficiently (not have to scan the whole relation). • The problem of deciding which indexes to put on the relations is hard (it’s called: physical database design).

Creating Indexes CREATE INDEX ssnIndex ON Person(social-security-number) Indexes can be created on more than one attribute: CREATE INDEX doubleindex ON Person (name, social-security-number) Why not create indexes on everything?

Modifying the Database We have 3 kinds of modifications: insertion, deletion, update. Insertion: general form -- INSERT INTO R(A1,…., An) VALUES (v1,….,vn) Insert a new purchase to the database: INSERT INTO Purchase(buyer, seller, product, store) VALUES (Joe, Fred, wakeup-clock-espresso-machine, “The Sharper Image”) If we don’t provide all the attributes of R, they will be filled with NULL. We can drop the attribute names if we’re providing all of them in order.

Deletions DELETE FROM PURCHASE WHERE seller = “Joe” AND product = “Brooklyn Bridge” Factoid about SQL: there is no way to delete only a single occurrence of a tuple that appears twice in a relation.

SQL as a Query Language Basic SQL Query: select A1, A2, …, Anfrom R1 , R2, … , Rm where < sel-cond> ; Equivalent RA expression: Proj[A1, A2, …An] ( select[sel-cond] ( R1 x R2 x … xRm)) Difference between SQL and RA: SQL does not automatically remove duplicates.

Example Relations: E(ename, dno, proj#) , D(dno, dname, mgr) keys: E ename D dno, mgr dname, mgr SQL RA Select ename, dname Proj[ename,dname]( from E, D select[D.dno=E.dno]( where D.dno = E.dno; (E x D)) find employees and the department they work for

First Unintuitive SQLism SELECT R.A FROM R,S,T WHERE R.A=S.A OR R.A=T.A R, S, T are tables with a single attribute -- A Looking for R intersection (S union T) But what happens if T is empty?

Select Clause -- Projection Select ename, dname from E, D where D.dno = E.dno; • SQL does not automatically eliminate duplicates. • if Sam works for manufacturing department on 2 projects, (sam, manufacturing) will be returned 2 times in SQL. • Keyword distinct used to explicitly remove duplicates Selectdistinct ename, dname from E, D where D.dno = E.dno; • Asterisk * used to denote all attributes Select * from E, D where D.dno = E.dno;

Where Clause • case sensitive constants select * from E where E.location = ‘Jakarta’ list all the information in E about employees in Jakarta. • where clause is optional select * from D List all the information in D. • conjunction and disjunctions select ename from E, D where E.dno = D.dno AND D.mgr = ‘Sally’ AND sal < 10000; Who works for Sally and has a salary < 10K

More on Where Clause • You can use: • attribute names of the relation(s) used in the FROM clause • comparison operators: =, <>, <, >, <=, >= • apply arithmetic operations: • Eg. stockprice*2 • apply operations on strings (e.g., “||” for concatenation). • lexicographic order on strings. • pattern matching: s LIKE p • special stuff for comparing dates and times.

Disambiguating Attribute Names • Relation-name.attribute-name used to disambiguate when attribute appears in multiple relation schemas select ename, dname, D.dno from E, D where E.dno = D.dno; List all employees, their departments name and department number.

Tuple Variables • as clause can be used in the from clause to define tuple variables. Tuple variables used to disambiguate multiple references to the same relation in the from clause. select E1.ename from E as E1, D, E as E2 where E1.dno = D.dno AND D.mgr = E2.ename AND E1.sal > E2.sal; Who makes more than their manager ?

Ordering the Display of Tuples select * from E order by dno, sal desc, ename; Print out E. Order the tuples by dept #. Within each dept, order from highest to lowest salary. For salary ties, use alphabetical order on last name ename dno sal location Susan 1 30K Jakarta Mary 1 20K Jakarta Jane 1 19K Jakarta Jim 2 15K Urbana John 2 15K Urbana

Joins SELECT name, store FROM Person, Purchase WHEREname=buyer AND city=“Seattle” AND product=“gizmo” Product ( name, price, category, maker) Purchase (buyer, seller, store, product) Company (name, stock price, country) Person( name, phone number, city)

Set Operations • Union (select mgr from D where dname=‘toy’) union (select mgr from D where dname = ‘marketing’) select names of people who are managers of either the toy or the marketing department • Intersect (select mgr from D where dname=‘toy’) intersect (select mgr from D where dname = ‘marketing’) select names of people who are managers of both the toy and the marketing department

Set Operations • Except (select mgr from D where dname=‘toy’) except (select mgr from D where dname = ‘marketing’) • select names of people who are managers of toy department but not of marketing department

Conserving Duplicates The UNION, INTERSECTION and EXCEPT operators operate as sets, not bags. (SELECT name FROM Person WHERE City=“Seattle”) UNION ALL (SELECT name FROM Person, Purchase WHERE buyer=name AND store=“The Bon”)

Aggregate Functions • Functions: min, max, sum, count, avg input:collection of numbers/strings (depending on operation) output: relation with a single attribute with single row selectmin(sal), max(sal), avg(sal) from E, D where E.dno = D.dno and D.dname = “Toy”; What is the minimum, maximum, average salary of employees in the toy department ? Except count, all aggregations apply to a single attribute SELECT Count(*) FROM Purchase

Duplication and Aggregate Functions selectcount(*), sum(sal) from E, D where E.dno = D.dno and D.dname = “Toy”; • What is the number of employees and the sum of their salaries in toy department • Could have said count(sal) instead of count(*) • What about • selectcount( sal), sum( DISTINCT sal)from E, Dwhere E.dno = D.dno and D.dname = “Toy”; • This SQL query will not be correct since it removes duplicates salaries.

Group By Clause • Group by used to apply aggregate function to a group of sets of tuples. Aggregate applied to each group separately. • select dname, sum(sal), count(ename)from E, Dwhere E.dno = D.dno group by dname • For each department, list its total number of employees and total salary expenditure • select dname, sum(sal), count(ename)from E, Dwhere E.dno = D.dno • Wrong answer!!! prints each dept name, followed by the sum and count over all depts • Grouped-by attributes exactly the non-aggregated items on the select line!

Having Clause • Having clause used along with groupby clause to select some groups. Predicate in having clause applied after the formation of groups. select dname, count(*) from E, D where E.dno = D.dno group by dname havingcount(*) > 5 list the department name and the number of employees in the department for all departments with more than 5 employees

General SQL Query • select e1.ename, sum(e2.sal) #4 • from E e1, D, E e2 • where e1.dno = D.dno AND e2.ename = D.mgr #1 • groupby e1.ename #2 • havingcount(*) > 1 #3 • orderby ename; #5 • #1: First, tuples are chosen • #2: Then, groups are formed • #3: Then, groups are eliminated • #4: Then, the aggregates are computed for the select line, flattening the groups • #5: Then, last, the tuples in the answer are ordered correctly and printed out. For each employee in two or more depts, print the total salary of his or her managers. Assume each dept has one manager.

Nesting of Queries Who is in Sally’s department? select E1.ename from E E1, E E2 where E2.ename = “Sally” AND E1.dno = E2.dno; select ename from E where E.dno in (select dno from E subquery where ename = “Sally”); names are scoped subquery called nested query it is embedded inside an outer query semantics: nested query returns a relation containing dno for which Sally works for each tuple in E, evaluate nested query and check if E.dno appears in the set of dno’s returned by nested query. Similar to function calls in programming languages

Subqueries Producing One Value Usually subqueries produce a relation as an answer. However, sometimes we expect them to produce single values SELECT Purchase.product FROM Purchase WHERE buyer = (SELECT name FROM Person WHERE social-security-number = “123 – 45 - 6789”); In this case, the subquery returns one value. If it returns more, it’s a run-time error.

Subqueries Returning Relations Find companies who manufacture products bought by Joe Blow. • select ename • from E • where E.dno in • (select dno • from E • where ename = “Sally”); • Conditions involving Relations: • s > ALL R -- s is greater than every value in unary relation R • s IN R -- s is equal to one of the values in R • s > ANY R, s >SOME R -- s is greater than at least 1 element in unary relation R. • EXISTS R -- R is not empty. • Other operators (<, = , <=, >=, <>) could be used instead of >. • EXISTS, ALL, ANY can be negated.

Set Comparison Using Nested Queries select enamefrom Ewhere sal >= all (select sal from E); • Who has the highest salary • <all, <=all, >=all, =all, <>all also permitted select ename from E where sal > some (select sal from E, D where E.dno = D.dno AND D.dname = “Toy”); • Who makes more than someone in the Toy department? • <some, <=some, >=some, >some =some, <>some also permitted • any is a synonym of some in SQL

Testing Empty Relations select ename from E E1 where exists (select ename from E, D where (E.ename = D.mgr) and (E1.sal > E.sal) • Employees who make more money than some manager • nested query uses attributes name of E1 defined in outer query. Such queries called correlated query. • non correlated queries can be executed once and for all and results used in outer query • However, correlated queries need to be executed once for each assignment of a value to some term in the subquery that comes from a tuple variable outside the sunquery • Exist checks for non empty set • similarly, not exist can also be used.

Revisit to Data Modification Using SQL • Recall 3 data modification operartors -- insert, delete and update. • Each of these operators can take relations produced by SQL queries as an input for the operator. • The query replaces the VALUES keyword. • Note the order of querying and inserting. INSERT INTO PRODUCT(name) SELECT DISTINCT product FROM Purchase WHERE product NOT IN (SELECT name FROM Product)

Revisit to Data Modification Using SQL DELETEFROM PURCHASE WHERE seller = “Joe” AND product = “Brooklyn Bridge” UPDATE PRODUCT SET price = price/2 WHERE Product.name IN (SELECT product FROM Sales WHERE Date = today);

Defining Views Views are relations, except that they are not physically stored. They are used mostly in order to simplify complex queries and to define conceptually different views of the database to different classes of users. View: purchases of telephony products: CREATE VIEW telephony-purchases AS SELECT product, buyer, seller, store FROM Purchase, Product WHERE Purchase.product = Product.name AND Product.category = “telephony”

A Different View CREATE VIEW Seattle-view AS SELECT buyer, seller, product, store FROM Person, Purchase WHERE Person.city = “Seattle” AND Person.name = Purchase.buyer We can later use the views: SELECT name, store FROM Seattle-view, Product WHERE Seattle-view.product = Product.name AND Product.category = “shoes” What’s really happening when we query a view??

Updating Views • How can I insert a tuple into a table that doesn’t exist? CREATE VIEW bon-purchase AS SELECT store, seller, product FROM Purchase WHERE store = “The Bon Marche” • If we make the following insertion: INSERT INTO bon-purchase VALUES (“the Bon Marche”, Joe, “Denby Mug”) • We can simply add a tuple (“the Bon Marche”, Joe, NULL, “Denby Mug”)to relation Purchase.

Non-Updatable Views CREATE VIEW Seattle-view AS SELECT seller, product, store FROM Person, Purchase WHERE Person.city = “Seattle” AND Person.name = Purchase.buyer How can we add the following tuple to the view? (Joe, “Shoe Model 12345”, “Nine West”)

Views & Data Independence Old schema E(emp, dept) D (dept, mgr) All applications use the old schema. New schema: E¢(emp, deptno) D¢(deptno, dname, mgr) (save space, allow easy renaming) Old programs do not work with new schema! So create view E(emp, dept) select emp, dname from E¢, D¢ where E¢.deptno = D¢.deptno; create view D ... Then old queries still run, and old updates on E do not.May run on D, depending on the DBMS.

Understanding SQL: A Historical Perspective and Comprehensive Overview