SQL

1
SQL

• DDL
• DML

2
Data Definition in SQL

• CREATE
• DROP
• ALTER

3
CREATE TABLE

• Specifies a new relation by giving it a name, and
  specifying each of its attributes and their data
  types (INTEGER, FLOAT, DECIMAL(i,j), CHAR(n),
  VARCHAR(n), DATE)
• A constraint NOT NULL may be specified on an
  attribute
• Used for specifying the primary key attributes,
  secondary keys, and referential integrity
  constraints (foreign keys)
• Key attributes can be specified via the PRIMARY
  KEY and UNIQUE phrases

4
CREATE TABLE

• create table department
• (dname varchar(10) not null,
• dnumber integer not null,
• mgrssn char(9),
• mgrstartdate date,
• primary key (dnumber),
• unique (dname),
• foreign key (mgrssn) references employee)
  

5
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
• EXAMPLE
• drop table dependent

6
ALTER TABLE

• Used to add an attribute to one of the base
  relations
• The new attribute will have NULLs in all the
  tuples of the relation right after the command is
  executed hence the NOT NULL constraint is not
  allowed for such an attribute
• EXAMPLE
• alter table employee add job varchar(12)
• alter table employee drop job

7
Referential Integrity Options

• We can specify CASCADE or SET NULL or SET DEFAULT
  on referential integrity constraints (foreign
  keys)
• Create table employee
• ( ename varchar(30) not null,
• essn char(9),
• bdate date,
• dno integer default 1,
• superssn char(9),
• primary key (essn),
• foreign key (dno) references department
• on delete set default on update cascade,
• foreign key(superssn) references employee
• on delete set null on update cascade)

8
What is an index?

• Schema object
• Used to speed up the retrieval of rows by using a
  pointer
• Reduce disk I/O by using path access method to
  locate the data quickly

9
Index Operations

• Create an index on a column
• create index lname_index on employee(lname)
• Removing an index
• drop index lname_index on employee

10
Column Aliasing

• For each employee, retrieve the employees first
  and last name and the first and last name of his
  or her immediate supervisor
• select e.lname employee_lname,
• e.fname employee_fname,
• s.lname supervisor_lname,
• s.fname supervisor_fname
• from employee e, employee s
• where e.superssns.ssn

11
Basic Queries in SQL

• DML commands for manipulating the database
• SELECT ltattribute listgt
• FROM lttable_listgt
• WHERE ltconditiongt
• ltattribute listgt is a list of attribute names
  whose values are to be retrieved by the query
• lttable_listgt is a list of the relation names to
  process the query
• ltconditiongt is a conditional (Boolean) expression
  that identifies the tuples to be retrieved by the
  query.

12
Multiple Table Join

• For every project located in Stafford, list the
  project number, the controlling department
  number, and the department managers last name,
  address and birthdate
• select pnumber, dnum, lname, address, bdate
• from project, department, employee
• where dnumdnumber and mgrssnssn and
  plocationStafford

13
Rename (Aliasing) Names

• For each employee, retrieve the same employees
  name, and the name of his or her immediate
  supervisor
• select e.fname, e.lname, s.fname, s.lname
• from employee e, employee s
• where e.superssn s.ssn
• Alternate relation names e and s are called
  aliases for the employee relation

14
Letter represents all

• To retrieve all the attribute values of the
  selected tuples, a is used, which stands for
  all the attributes
• Get all employees who work in Department 5
• select
• from employee
• where dno 5
• Get all employees who work for the Research
  Department
• select
• from employee, department
• where dnameResearch and dnodnumber

15
Unspecified WHERE-clause

• A missing where-clause indicates no condition
  hence all tuples of the relations in the
  from-clause are selected
• Retrieve the SSN values for all employees
• select ssn
• from employee

16
Use of distinct and order by

• Duplicate tuples can appear in an SQL query
  result
• To eliminate duplicate tuples in a query result,
  the keyword distinct is used
• The order by clause is used to sort the tuples in
  a query result based on the values of some
  attribute(s).
• List all supervisors social security numbers
• select distinct superssn
• from employee
• List all salary scales in the company
• select distinct salary
• from employee
• order by salary desc

17
Operator Like

• Substring comparison
• The Like comparison operator is used to compare
  partial strings
• represents any number of characters
• - or ? replaces a single arbitary character
• List all employees whose address is in Houston,
  Texas
• select fname, lname
• from employee
• where address like Houston,TX

18
Operator Like

• Retrieve all employees who were born during the
  1950s
• select fname, lname
• from employee
• where bdate like _______5_

19
Operator Between and

• List the employees who work for department 5 and
  whose salary is higher than or equal to 30000,
  but lower than or equal to 40000
• select fname, lname, salary
• from employee
• where (salary between 30000 and 40000)
• and dno5
• order by salary

20
Arithmetic Operators , -, , /

• Show the effect of giving all employees who work
  on the ProductX project a 10 raise
• select fname, lname, 1.1salary
• from employee, works_on, project
• where ssnessn and pnopnumber and
  pnameProductX

21
Relational Operators , !, lt gt, gt, gt, lt, lt

• List employees who do not work for department 5
• select from employee
• where dno ! 5

22
Logical operators and, or

• Retrieve the employees who work on at least one
  of the projects with project number 1, 2 and 3.
• select
• from works_on
• where pno1 or pno2 or pno3

23
Operator IN

• Retrieve the employees who work on at least one
  of the projects with project number 1, 2 and 3.
• select
• from works_on
• where pno in (1, 2, 3)

24
Operator NOT

• Retrieve the employees who do not work on any of
  the projects with project number 1, 2 and 3
• select
• from works_on
• where pno not in (1, 2, 3)

25
Is null

• List employee(s) who dont have any supervisors
• select fname, lname
• from employee
• where superssn is null

26
Is not null

• List employee(s) who have supervisors
• select fname, lname
• from employee
• where superssn is not null

27
Sub-queries (Nested Queries)

• A Select statement embedded within another select
  statement
• The result of the inner select (inner query) is
  used in the WHERE-clause of the outer query
• Operators used for the sub-queries
• gt, gt, lt, lt, ltgt,
• all, any/some, in, exists/not exists

28
Examples

• Any returns true if the value is greater than any
  value in the set. Any combines with gt, gt, lt,
  lt, ltgt
• Get employee whose salary is higher than any of
  the employees in Department 5.
• select ssn, fname, lname, salary
• from employee
• where salary gt any
• (select salary
• from employee
• where dno 5)

29
IN in Subqueries

• In compares a value with a set of values, and
  evaluates to TRUE if the value is one of the
  elements in the set.
• Get the name of each employee who has dependent
  with the same first name as the employee
• select e.fname, e.lname
• from employee e
• where e.ssn in
• (select essn
• from dependent
• where essne.ssn and e.fnamedependent_nam
  e)

30
EXISTS and NOT EXISTS

• Designed for use only with subqueries
• Produces a simple true/false result
• EXISTS is
• true if and only if there exists at least one row
  in the result table returned by the inner query
  (sub-query)
• False if the sub-query returns an empty result
• The sub-query can contain any number of columns
• The sub-query is usually of the form
• select from

31

• Retrieve the name of each employee who has a
  dependent with the same first name as the
  employee
• select fname, lname
• from employee
• where exists (select
• from dependent
• where ssnessn and
• fname dependent_name)

32

• Retrieve the names of employees who have no
  dependents
• select fname, lname
• from employee
• where not exists (select
• from dependent
• where ssnessn)

33

• List the name of managers who have at least one
  dependent
• select fname, lname
• from employee
• where EXISTS ( select
• from dependent
• where ssnessn)
• and EXISTS (select
• from department
• where ssnmgrssn)

34
Aggregate Functions

• COUNT
• SUM
• MAX
• MIN
• AVG

35
MAX, MIN, AVG

• Find the maximum salary, the minimum salary, and
  the average salary among employees who work for
  the Research Department
• select max(salary), min(salary), avg(salary)
• from employee, department
• where dnameResearch and dnodnumber

36
COUNT

• Count returns the number of rows in the result
  query
• Examples
• select count() from employee
• select count() from employee where dno5
• select count(salary) from employee
• select count(distinct salary) from employee

37

• List employees in Department 5 whose salary is
  higher than the average company salary
• select ssn, fname, lname, salary
• from employee
• where dno 5 and salary gt
• (select avg(salary) from employee)

38
Group by - Clause

• It is used in subtotal report
• The grouping attributes specified in a group-by
  clause must also appear in the SELECT-clause
• For each department, retrieve the department
  number, the number of employees in the department
  and their average salary
• select dno, count(), avg(salary)
• from employee
• group by dno

39
Join condition with 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

40
Join condition with grouping

• For each project, retrieve the project number,
  project name and the number of employees in
  Department 5 who work on that project
• select pnumber, pname, count()
• from project, works_on
• where pnumberpno and dno5
• group by pnumber, pname

41
Group by .. Having

• To retrieve the values of the aggregate functions
  for only those groups that satisfy certain
  conditions
• Used for specifying selection condition on groups
• 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 pnumberpno
• group by pnumber, pname
• having count() gt 2

42
Summary of SQL Queries

• SELECT ltattribute listgt
• FROM lttable listgt
• WHERE ltconditiongt
• GROUP BY ltgrouping attributesgt
• HAVING ltgroup conditiongt
• ORDER BY ltattribute listgt

43
Specifying Updates in SQL

• INSERT
• UPDATE
• DELETE

44
INSERT

• Used to add one or more tuples to a relation
• Attribute values should be tested in the same
  order as the attributes were specified in the
  CREATE TABLE command
• Example
• insert into employee values
• (Richard,K,Marini,653298653,30-DEC-52
  ,98 Oak Forest, Kaly, TX,M,37000,987654321,4
  )

45
INSERT

• Alternate Form
• Specifies attribute names and its values
  explicitly
• Attributes with null values can be left out
• Example
• insert into employee (fname, lname, ssn)
• values (Richard,Marini,653298653)

46
INSERT

• insert into depts_info (dept_name, no_of_emps,
  total_sal)
• select dname, count(), sum(salary)
• from department, employee
• where dnumberdno
• group by dname

47
DELETE

• Removes tuples from a relation
• Includes WHERE-clause
• Tuples deleted one at a time (unless CASCADE is
  specified on a referential integrity constraint)
• Referential integrity should be enforced

48
DELETE

• delete from employee where lnameBrown
• delete from employee
• delete from employee
• where dno in
• (select dnumber from department
• where dnameResearch)

49
UPDATE

• Used to modify one or more tuples
• A WHERE-clause selects tuples to be modified
• SET-clause specifies the attributes to be
  modified and their new values
• Referential integrity should be enforced

50
UPDATE

• Change the location and controlling department
  number foro project 10 to Bellaire and 5
  respectively
• update project
• set plocationBellaire,dnum5
• where pnumber10

51
UPDATE

• Give all employees in Research department a 10
  raise
• update employee
• set salary salary1.1
• where dno in
• (select dnumber from department
• where dnameResearch)