SQL QUERIES

1
SQL QUERIES
2
Department resources

• https//www.cse.sc.edu OR
• www.cse.sc.edu - local info -
• For CSE Students, Faculty and Staff
• My CSE Account - Oracle account
• Password - Oracle Password
• stoica_at_cse.sc.edu or help_at_cse.sc.edu

3
Database example

• Dog-Owner database
• Dog(name, age, weight, breed)
• Owner(name, ssn, phone)
• Owns(ssn, name)

4
CREATE TABLE

• create table dog(
• name char(20), age int,
• weight float, breed char(20))
• create table owner (name char(20), ssn char(11),
  phone char(12))
• create table owns (
• ssn char(11), name char(20))

5
INSERT VALUES

• insert into dog values ('Pepper',1,68,'G.S')
• insert into dog values ('Rudy-Rose',5,57,'G.S')
• insert into owner values ('Mary Smith',
• '123-45-6789','803-123-4567')
• insert into owner values ('John White',
• '987-65-4321', '803-765-4321')
• insert into owns values (123-45-6789,Pepper)
• insert into owns values (987-65-4321,Rudy-Rose
  )

6
SELECT

• SQL Select query form SELECT A1, A2, ...,
  An FROM r1, r2, ..., rm WHERE C
• Ais represent attributes to be returned
• ris represent relations
• C is a condition
• Equivalent to the relational algebra expression.
• ?A1, A2, ..., An(?C (r1 x r2 x ... x
  rm))

7
SELECT

• List the name of the all
• German shepherd dogs ?

8
SELECT

• select name from dog
• where breed'G.S'

9
SELECT

• select from dog where breed'G.S'
• select name, weight from dog
• where breed'G.S' and age
• select name, weight/2.2 as
• "Weight in kg" from dog
• select name, breed from dog
• where name like P

10
Multi-relational Queries

• Combine data from more
• than one relation
• List relations in the FROM clause
• Attributes with the same name
• must be distinguished

11
Multi-relational Queries

• select owner.name, owns.name
• from owner, owns

?
12
Multi-relational Queries
13
Multi-relational Queries

• List the name of the owner
• and the name of his/her dog ?

14
Multi-relational Queries

• select owner.name, owns.name
• from owner, owns
• where owner.ssnowns.ssn

15
Renaming Relations

• When a query needs to use two or more tuples from
  the same relation
• Distinguish copies of the same relation with
  different tuple variable names in the FROM clause
• You can also create shortcut to the relation
  names this way

16
Renaming relations

• Find German shepherd dogs that differ in less
  than 15 lb in weight?

17
Renaming Relations

• select d1.name, d2.name
• from dog d1, dog d2
• where d1.weight(d2.weight-d1.weight)
• d1.name!d2.name

18
Subqueries

• Parenthesized SELECT-FROM-WHERE can be used in
  FROM and WHERE clauses
• In FROM replaces relations with the result
  (relation) of a query
• In WHERE when the answer to the subquery is a
  single tuple otherwise error may occur

19
Subqueries

• Find the name and breed of the dogs that weight
  the same as Pepper?

20
Subqueries

• select name, breed
• from dog where weight (
• select weight from dog
• where name'Pepper')