SQL

1
SQL
2
Query Language

• SQL or SEQUEL - (Structured English Query
  Language)
• High-level DB language created at IBM  in 1970's
          DB system products using SQL released in
  early 80's
• Provides DML and DDL
• Standard SQL
• SQL-86, SQL-89 - minimal standard, still
  incompatibility product features
• ANSI/ISO - (SQL-92) SQL2 - tried to avoid
  incompatibility problems
• X/Open standard - extensions plus portability
• SQL-3, SQL1999, SQL-99, SQL2003, SQL200n  
• Current standard SQL2008    
•                           

3
SQL

• Relation is not a set of tuples - a multiset or
  bag of tuples
• Therefore, 2 or more tuples may be identical
• Basic building block of SQL is the Select
  Statement
• SELECT ltattribute listgt
• FROM lttable list gt
• WHERE ltsearch conditionsgt

4
Select Statement

• Select - chooses columns (project operation p in
  relational algebra)
• From - combines tables if gt 1 table (join
  operation X in relational algebra)
• Where - chooses rows (select operation s in
  relational algebra)
• Result of a query is a relation
• Results may contain duplicate tuples

5
Queries

• Retrieve the birthdate and address of the
  employee whose name is 'Smith
• Select bdate, address
• From Employee
• Where lname 'Smith'
• To retrieve all the attribute values of the
  selected tuples, a is used
• Select
• From Employee
• Where lname 'Smith'

6
Queries

• To select all rows and columns of a relation 
• Select
• From Employee
• To select some of the rows
• Select
• From Employee
• Where dno 5
• To select specified columns for all rows
• Select SSN
• From Employee

7
Select Clause

• Select ltattribute listgt
• Attribute list can be
• column names
• Constants
• arithmetic expressions involving columns, etc.
• In Oracle, can also be a select statement (but
  select can only return 1 column and 1 row)
• lists all attributes in a table
• To rename an attribute, use the keyword as
• Select lname as last_name
• From employee

8
From clause

• From lttable listgt
• Table list can be
• one or more table names
• a select statement itself

9
Combining tuples in where clause

• To retrieve data that is in more than one table
  can use
• a cartesian product X
• Select
• From Empnames, Dependent
• A join operation X
• List each department and its manger info 
• Select
• From Employee, Department
• Where mgrssnssn
•               

10
Additional characteristics

• Use distinct to eliminate duplicate tuples
• Select distinct salary
• From Employee
• In SQL we can use the same name for 2 or more
  attributes in different relations. Must qualify
  the attributes names
• employee.lname

11
Additional characteristics

• Aliases are used to rename relations
• Select E.lname, D. dname From Employee E,
  Department D
• Where E.dno D.dnumber
• List all employee names and their supervisor
  names
• Select E.fname, E.lname, S.fname, S.lname
• From Employee E, Employee S
• Where E.superssn S.ssn

12
Where clause

• Where ltsearch conditionsgt (s in relational
  algebra)
• Search conditions can be
• Comparison predicate expr expr2
• where is lt, gt, lt, etc.
• in, between, like, etc.
• expr is constant, col, qual.col,
• aexpr op aexpr, fn(aexpr),
  set_fn(aexpr)
• expr2 is expr select statement
• Note expr can be a select statement

13
Expr as a select statement

• You need to be careful using this. Result must
  be a single value
• Select lname, dno
• From employee
• Where dno (select dnumber
• from department
• where dname Research)

14
Predicates

• Predicates evaluate to either T or F. Many of
  the previous queries can be specified in an
  alternative form using nesting.

15
In predicate

• The in predicate tests set membership for a
  single value at a time.
• In predicate expr not in (select val ,
  val)
• Select From Agents
• Where city in ('Atlanta', 'Dallas')

16
In predicate

• Select employees in research dept.
• Select
• From Employee
• Where dno in (Select dnumber
• From Department
• where dname
  'Research')
• The outer query selects an Employee tuple if its
  dno value is in the result of the nested query.

17
Quantified predicate

• Quantified predicate compares a single value with
  a set according to the predicate.
• Quantified predicate expr all any
  (select)
• Select
• From Employee
• Where dno any (Select Dnumber
• From Department
• Where dname
  'Research')

18
Quantified predicate

• What does the following query?
• Select
• From Employee
• Where salary gt all (Select salary
• From Employee
• Where sex 'F')
• any equivalent to in
• not in equivalent to ltgt all

19
Exists predicate

• The exists predicate tests if a set of rows is
  non-empty
• Exists predicate not exists (select)
• Select
• From Employee
• Where exists (Select
• From Department
• Where dname 'Research' and dno
  dnumber)

20
Exists predicate

• Exists is used to check whether the result of the
  inner query is empty or not. If the result is
  NOT empty, then the tuple in the outer query is
  in the result.
• Exists is used to implement difference (not in
  used) and intersection.

21
Exists predicate

• Retrieve all the names of employees who have no
  dependents.
• Select fname, lname
• From Employee
• Where not exists (Select
• From Dependent
• Where ssn essn)
• All of the Dependent tuples related to one
  Employee tuple are retrieved. If none exist (not
  exists is true and the inner query is empty) the
  Employee tuple is in the result.

22
Nested queries

• In general we can have several levels of nested
  queries.
• A reference to an unqualified attribute refers to
  the relation declared in the inner most nested
  query.
• An outer query cannot reference an attribute in
  an inner query (like scope rules in higher level
  languages).
• A reference to an attribute must be qualified if
  its name is ambiguous. 

23
Will this work?

• Suppose you want the ssn and dname
• Select ssn, dname
• from employee
• where dno in (select dnumber
• from department)

24
Correlated Nested Queries

• Correlated Nested Queries
• If a condition in the where-clause of a nested
  query references an attribute of a relation
  declared in an outer query, the two queries are
  said to be correlated.
• The result of a correlated nested query is
  different for each tuple (or combination of
  tuples) of the relation in the outer query.
• Which takes longer to execute? a correlated
  nested query or a non-correlated nested query?

25
Correlated queries

• List the name of employees who have dependents
  with the same birthday as they do.
• Select E.fname, E.lname
• From Employee E
• Where E.ssn in (Select essn
• From
  Dependent D
• Where essn
  E.ssn
• and
  E.bdate D.bdate)
• Can this be written as uncorrelated nested?

26
Single block queries

• An Expression written using or IN may almost
  always be expressed as a single block query
• Select E.fname, E.lname
• From Employee E, Dependent D
• Where E.ssn E.essn and E.bdate
  D.bdate

27
Outer Joins

• Notation specific to DBMS
• OracleYou can use a to indicate an outer
  join       The following example indicates a
  left outer join                Select fname,
  minit, lname, dname                From
  Employee, Department                Where
  ssnmgrssn()

28
Outer Joins

• You can use the keywords left, right, full
   Again the following is a left outer
  join                Select fname, minit, lname,
  dname                From Employee Left Outer
  Join Department on ssnmgrssn
•  The keyword Outer is optional

29
Alternative form of join

• Select fname, lname
• From (Employee join Department on dnodnumber)
• Where dnameResearch
• Select fname, lname
• From employee, department
• Whre dnodnumber and dnameResearch

30
Select statement

• Multiple levels of select nesting are allowed.
• Like predicate, Between predicate and Null
  predicate
• Can apply arithmetic operations to numeric values
  in SQL
• Select fname, lname, 1.1salary
• From Employee
• Select discount_rateprice
• From products

31
Aggregate functions

•  Aggregate Functions (set functions, aggregates)
• Include COUNT, SUM, MAX, MIN and AVG
• aggr (col)
• Find the maximum salary, the minimum salary and
  the average salary among all employees.
• Select MAX(salary), MIN(salary), AVG(salary)
• From Employee

32
Aggregates

• Retrieve the total number of employees in the
  company
• Select COUNT()
• From Employee
• Retrieve the number of employees in the research
  department.
• Select COUNT()
• From Employee, Department
• Where dnodnumber and
  dname'Research'

33
Aggregates

• Note that Select COUNT()
• from Employee
• Will give you the same result as
• Select COUNT(salary) from Employee
• Unless there are nulls - not counted for
  salary
• To count the number of distinct salaries.
• Select COUNT(distinct salary)
• From Employee

34
What does this query do?

• SELECT dno, lname, salary FROM employee x
• WHERE salary gt
• (SELECT AVG(salary)
• FROM employee
• WHERE x.dno dno)
• What would happen if you delete the qualification
  x.?

35
Grouping

• We can apply the aggregate functions to subgroups
  of tuples in a relation.
• Each subgroup of tuples consists of the set of
  tuples that have the same value for the grouping
  attribute(s).
• The aggregate is applied to each subgroup
  independently.
• SQL has a group-by clause for specifying the
  grouping attributes.

36
Grouping

• For each department, retrieve the department
  number, the total number of employees and their
  average salary.
• Select dno, COUNT(), AVG(salary)
• From Employee
• Group By dno
• The tuples are divided into groups with the same
  dno.
• COUNT and AVG are then applied to each group.

37
Grouping

• For each project, retrieve the project number,
  project name and the number of employees who work
  on that project.
• Select pnumber, pname, COUNT()
• From Project, Works_on
• Where pnumberpno
• Group By pnumber, pname
• In the above query, the joining of the two
  relations is done first, then the grouping and
  aggregates are applied.

38
Oracle group by

• Expressions in the GROUP BY clause can contain
  any columns of the tables or views in the FROM
  clause, regardless of whether the columns appear
  in the SELECT clause.
• However, only grouping attribute(s) and aggregate
  functions can be listed in the SELECT clause.

39
Having Clause

• Sometimes we want to retrieve those tuples with
  certain values for the aggregates.
• The having clause is used to specify a selection
  condition on a group (rather than individual
  tuples).
• If a having is specified, you must specify a
  group by.

40
Having

• For each project on which more than two
  employees work, retrieve the project number,
  project name, and the number of employees who
  work on that project.
• Select pnumber, pname, COUNT()
• From Project, Works_on
• Where pnumber pno
• Group By pnumber, pname
• Having COUNT() gt 2

41
Nested or not?

• Select dname, count()
• From department, employee
• Where dnumberdno and salary gt 40000 and dno in
  (select dno
• from employee
• group by dno
• having count() gt 5)
• Group by dname

• Select dname, count()
• From department, employee
• Where dnumberdno
• and salary gt 40000
• Group by dname
• Having count() gt 5

42
Subselect formal definition

• Select called Subselect
• Select expr , expr
• From tablename alias , tablename
  alias
• Where search_condition
• Group By col , col
• Having search_condition

43
Order By

• To sort the tuples in a query result based on the
  values of some attribute
• Order by col_list ascdesc
• Default is ascending order (asc), but can specify
  descending order (desc)

44
Order by

• Retrieve a list of the employees each project
  (s)he works on, ordered by the employee's
  department, and within each department order the
  employees alphabetically by last name.
• Select dname, lname, fname, pname
• From   Department, Employee, Works_on,
  Project
• Where dnumberdno and ssnessn and
• pnopnumber
• Order By  dname, lname

45
Set operations

• Also available are Set Operations, such as
• UNION, MINUS and INTERSECT.
• FULL SELECT
• Subselect Union all subselect    Order By
  col asc desc , col asc desc

46
Set Operations

• The resulting relations are sets of tuples
  duplicate tuples are eliminated.
• Operations apply only to union compatible
  relations.

47
Oracle example

• SELECT FROM
• (SELECT ENAME FROM EMP
• WHERE JOB 'CLERK'
• UNION
• SELECT ENAME
• FROM EMP
• WHERE JOB 'ANALYST')

48
Example Queries

• Suppose you have created a table QtrSales (ID,
  Q1, Q2, Q3, Q4)
• SQL to compute the total sales for each quarter?
• SQL to compute the total sales for each ID?

49
Evaluation

• Logical order of evaluation
• Apply Cartesian product to tables
• apply search conditions
• Apply group by and having
• Apply the select clause
• order the result for the display.
• Actual order of evaluation?
• More efficient to apply join condition during
  Cartesian product
• How can a DBMS implement a join?

50
Sample SQL queries

• List employee last names for employees who have
  at least 2 dependents.
• Increase the salary of employees working in the
  Administration department by 50.
• Delete all employees who work on project 30.

51
Power/Weakness of SQL

•  SQL summary
• user specifies what is desired rather than how
• (e.g.  no need to write loop statement to
  traverse tuples)
• no implied ordering
• query optimizer decides how
• desired result described in one statement
• Avoids procedural complexities (non-procedural)
• A language with the power of relational algebra
• defined as relationally complete (Codd)

52
Weaknesses of SQL?

• 1) Too many equivalent forms
• e.g.  in, any, exists             not in, ltgt
  all
• 2) No non-procedural language can have Turing
  power (computationally complete), e.g. perform
  any computational procedure that can be specified
  in algorithmic terms
• SQL only relationally complete
• 3) Some capabilities missing
• Selects cannot be arbitrarily nested like
  relational algebra
• loss of expressive power of a relational algebra
  expression
• 4) Can find reports SQL can't create - need
  embedded SQL
• e.g.  categorize total sales based on size of
  sale group by 0-499, 500-999, etc.

53
Expansions to SQL

• 1) Can nest set functions
• e.g. Average of total dollar sales
• 2) More statistical functions (besides avg)
• e.g.  variance, mode
• 7) Transitive closure - arbitrary number
• managers of managers
• 8) Ranking available
• Retrieve top 5 out of 6
• List 20 agents with largest sales

54
Strengths

• While subselects cannot be arbitrarily nested -
  have added power in search (where) condition
• Other strengths of SQL?

55
DBMS market share

• According to Gartner and IDC DB market was 16.6
  B (14B) in 2006 (2005) - 18.8B in 2007
• Oracle 48.6 (47.9) market share
• IBM DB2 21.2 (21.6) market share
• MS SQL Server 18.6 (15)
• NCR Teradata (number 4 in 2007)
• Sybase
• MySQL, PostGreSQL, Ingres 9.9 (10.7)

56
DDL Data Definition in SQL

• Used to CREATE, DROP and ALTER the descriptions
  of the relations of a database
• CREATE TABLE
• Specifies a new base relation by giving it a
  name, and specifying each of its attributes and
  their data types
• CREATE TABLE name (col1 datatype, col2
  datatype, ..)

57

• Data types (ANSI SQL vs. Oracle)
• There are differences between SQL and Oracle,
  but Oracle will convert the SQL types to its own
  internal types
• int, smallint, integer converted to NUMBER
• Character is char(l) or varchar2(l), varchar(l)
  still works
• Float and real converted to number

58
Constraints

• Constraints are used to specify primary keys,
  referential integrity constraints, etc.
• CONSTRAINT constr_name PRIMARY KEY
• CONSTRAINT constr_name REFERENCES
• table (col)
• You can also specify NOT NULL for a column

59
Create table In line constraint definition

• Create table Project2 (pname varchar2(9)
• CONSTRAINT pk PRIMARY KEY,
• pnumber int not null,
• plocation varchar2(15),
• dnum int CONSTRAINT fk
• REFERENCES Department (dnumber),
• phead int)

60
Create Table out of line constraint definition

• Create table Project2 (pname varchar2(9),
• pnumber int not null,
• plocation varchar2(15),
• dnum int, phead int,
• CONSTRAINT pk PRIMAY KEY (pname),
• CONSTRAINT fk FOREIGN KEY (dnum)
• REFERENCES Department (dnumber))

61
Oracle Specifics

• When you specify a foreign key constraint out of
  line, you must specify the FOREIGN KEY keywords
  and one or more columns. When you specify a
  foreign key constraint inline, you need only the
  REFERENCES clause.

62
Integrity constraints in Oracle

• A NOT NULL constraint prohibits a database value
  from being null.
• A unique constraint - allows some values to be
  null.
• A primary key constraint combines a NOT NULL
  constraint and a unique constraint in a single
  declaration.
• A foreign key constraint requires values in one
  table to match values in another table.
• A check constraint requires a value in the
  database to comply with a specified condition
  (e.g. between 0-100)

63
DROP TABLE

• Used to remove a relation and its definition
• The relation can no longer be used in queries,
  updates or any other commands since its
  description no longer exists
• Drop table dependent

64
ALTER TABLE

• To alter the following ways
• to add a column
• to add an integrity constraint
• to redefine a column (datatype, size, default
  value) there are some limits to this
• to enable, disable or drop an integrity
  constraint or trigger
• other changes relate to storage, etc.
• Useful if 2 tables reference each other Oracle
  alter

•      
•                           

65
Updates (DML)

• Insert, delete and update
• INSERT
• Insert into table_name ( (col1 ,
  colj) values (val1 , valj) (col1 ,
  colj) subselect )
• add a single tuple
• attribute values must be in the same order as
  the CREATE table

66
Insert

• Insert into Employee values ('Richard',
  'K', 'Marini', '654298343', '30-DEC-52', '98
  Oak Forest, Katy, TX', 'M', 37000, '987654321,
  4)
• Use null for null values in ORACLE

67
Insert

• To insert multiple tuples from existing table
• Create table Depts_Info (dept_name
• varchar(10), no_of_emps int, total_sal
  int)
• Insert into Depts_Info
• Select dname, count(), sum(salary)
• From Department, Employee
• Where dnumber dno
• Group By dname

68
Delete

• Delete from table_name search_condition
• If include a where clause to select, tuples are
  deleted from table one at a time
• The number of tuples deleted depends on the where
  clause
• If no where clause included all tuples are
  deleted - the table is empty

69
Delete

• Delete From Employee
• Where lname 'Brown
• Delete From Employee
• Where ssn '123456789
• Delete from Employee
• Where dno in (Select dnumber
• From
  Department
• Where
  dname 'Research')
• Delete from Employee

70
Update

• Modifies values of one or more tuples
• Where clause used to select tuples
• Set clause specified the attribute and value
  (new)
• Only modifies tuples in one relation at a time
• Update lttable namegt
• Set attribute value , attribute
  value
• Where ltsearch conditionsgt

71
Update

• Update Project
• Set plocation 'Bellaire', dnum 5
• Where pnumber 10
• Update Employee
• Set salary salary 1.1
• Where dno in (Select dnumber
• From
  department
• Where dname
  'Research')

72
Violation of Integrity Constraints

• Insert, delete or update can violate a
  referential integrity constraint
• SQL allows qualified options to be specified for
  the foreign key
• Set null
• Cascade
• Set default NOT AVAILABLE in ORACLE
• On delete or On update (includes insert)

73
Oracle

• The ON DELETE CASCADE and ON DELETE SET NULL
• Specify CASCADE if you want Oracle to remove all
  tuples with dependent foreign key values.
• Specify SET NULL if you want Oracle to convert
  dependent foreign key values to NULL.

74
SQL

• Subselect - formal definition
•         Select expr , expr         From
  tablename alias , tablename alias        
  Where search_condition         Group By col
  , col         Having search_condition
• Full Select - formal definition
•      Subselect Union all subselect   Order
  By result_col asc desc , result_col asc
  desc