Naveen Ashish

1
SQL

• Naveen Ashish
• Calit2 ICS
• UC Irvine

2
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

3
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

4
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

5
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.

6
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.

7
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 domain person-name char(20)

8
Schema Definition

• Create table r (
• A1 D1 not null default V1
• A2 D2 not null default V2
• An Dn not null default Vn
• ltintegrity constraint 1gt
• ltintegrity constraint 2gt
• ltintegrity constraint kgt
• )
• Integrity constraints 1 k could be
• primary key
• candidate key
• foreign key
• check(predicate) specifies predicate that must be
  satisfied by each tuple

9
SQL as DDL

• create table Employee
• (
• name char15 not null
• age smallint
• sex (M,F) default M
• ss integer not null
• spouse_ss integer
• dept integer
• )
• Primary Key (ssno)
• Unique(spouse_ss)
• Check (age gt20 and age lt70)
• Foreign key (dept) references Dept(dept)
• on delete cascade
• on update cascade

Default value is Male
Dont 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

10
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

11
Indexes

• REALLY important 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 (its called physical
  database design).

12
Creating Indexes
CREATE INDEX ssnIndex ON Person(social-security-n
umber) Indexes can be created on more than one
attribute CREATE INDEX doubleindex ON Person
(name, social-security-number) Why not create
indexes on everything?
13
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 dont provide all the attributes of R, they
will be filled with NULL.
We can drop the attribute names if were
providing all of them in order.
14
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.
15
SQL as a Query Language

• Basic SQL Query
• select A1, A2, , Anfrom R1 , R2, , Rm
• where lt sel-condgt
• Equivalent RA expression
• ProjA1, A2, An (
• selectsel-cond (
• R1 x R2 x xRm))
• Difference between SQL and RA
• SQL does not automatically remove duplicates.

16
Example

• Relations E(ename, dno, proj) , D(dno, dname,
  mgr)
• keys E ename D dno, mgr dname, mgr
• SQL RA
• Select ename, dname Projename,dname(
• from E, D selectD.dnoE.dno(
• where D.dno E.dno (E x D))
• find employees and the department they work for

17
First Unintuitive SQLism
SELECT R.A FROM R,S,T WHERE R.AS.A OR
R.AT.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?
18
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
• Select distinct 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

19
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 lt 10000
• Who works for Sally and has a salary lt 10K

20
More on Where Clause

• You can use
• attribute names of the relation(s) used in the
  FROM clause
• comparison operators , ltgt, lt, gt, lt, gt
• apply arithmetic operations
• Eg. stockprice2
• apply operations on strings (e.g., for
  concatenation).
• lexicographic order on strings.
• pattern matching s LIKE p
• special stuff for comparing dates and times.

21
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.

22
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 gt E2.sal
• Who makes more than their manager ?

23
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

24
Joins
SELECT name, store
FROM Person, Purchase WHERE
namebuyer AND citySeattle AND
productgizmo Product ( name, price,
category, maker) Purchase (buyer, seller,
store, product) Company (name, stock price,
country) Person( name, phone number, city)

25
Set Operations

• Union
• (select mgr from D
• where dnametoy)
• 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 dnametoy)
• intersect
• (select mgr from D
• where dname marketing)
• select names of people who are managers of both
  the toy and the marketing department

26
Set Operations

• Except
• (select mgr from D
• where dnametoy)
• except
• (select mgr from D
• where dname marketing)
• select names of people who are managers of toy
  department but not of marketing department

27
Conserving Duplicates
The UNION, INTERSECTION and EXCEPT operators
operate as sets, not bags.
(SELECT name FROM Person WHERE
CitySeattle) UNION ALL (SELECT name
FROM Person, Purchase WHERE buyername
AND storeThe Bon)
28
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
• select min(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

29
Duplication and Aggregate Functions

• select count(), 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
• select count( 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.

30
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!

31
Having Clause

• Having clause used along with group by 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
• having count() gt 5
• list the department name and the number of
  employees in the department for all departments
  with more than 5 employees

32
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 group by e1.ename
2 having count() gt 1 3 order by
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.
33
Nesting of Queries

• Who is in Sallys 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 dnos
  returned by nested query.
• Similar to function calls in programming languages

34
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, its a run-time error.
35
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 gt 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 gt ANY R, s gtSOME R -- s is greater than at
  least 1 element in unary relation R.
• EXISTS R -- R is not empty.
• Other operators (lt, , lt, gt, ltgt) could be used
  instead of gt.
• EXISTS, ALL, ANY can be negated.

36
Set Comparison Using Nested Queries

• select enamefrom Ewhere sal gt
  all (select sal from E)
• Who has the highest salary
• ltall, ltall, gtall, all, ltgtall also permitted
  
• select ename
• from E
• where sal gt some
• (select sal
• from E, D
• where E.dno D.dno AND
• D.dname Toy)
• Who makes more than someone in the Toy
  department?
• ltsome, ltsome, gtsome, gtsome some, ltgtsome also
  permitted
• any is a synonym of some in SQL

37
Testing Empty Relations

• select ename
• from E E1
• where exists (select ename
• from E, D
• where
• (E.ename D.mgr)
• and (E1.sal gt 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.

38
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)
39
Revisit to Data Modification Using SQL
DELETE FROM 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)
40
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
41
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
Whats really happening when we query a view??
42
Updating Views

• How can I insert a tuple into a table that
  doesnt 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.

43
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)
44
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.