SQL: Structured Query Language
-
There are some different dialects of SQL. You need
to look at manual first before you start to
run it.
-
Case Sensitive or not?
-
Is there ' ; ' at the end of statement?
-
SQL can do inserting into a table or deleting from a table. This
part is easy. You will learn it by yourself.
-
We will focus on query statements like:
|
Corresponds to Relational Algebra
|
Order of operations
|
| SELECT
<attributes> |
Project on some attributes
|
3
|
| FROM
<tables> |
Cross product on all possible outcomes
|
1
|
| WHERE
<conditions> |
Select on some tuples
|
2
|
Note: You always pick up tuples first, then
project on columns.
That is, check the conditions before you select attributes.
Write SQL to find all people who are at least 30 years old.
SELECT
ssn
FROM
Person
WHERE
age >= 30
or
SELECT
ssn
FROM
Person
WHERE
Person.age >= 30
or
SELECT
ssn
FROM
Person P
P is an alias of Person
for saving typing.
WHERE
P.age >= 30
If there is no WHERE
statement, all tuples are returned.
SELECT
ssn, age
FROM
Person P
Write SQL to find all Memphis people who are at least 30
years old.
SELECT
ssn, age
FROM
Person
WHERE
age >= 30 AND City = "Memphis"
Write SQL to find names of all people who own a restaurant
in Memphis .
SELECT
P.Name
FROM
Person P, Owner O
WHERE
P.ssn = O.ssn AND O.City = "Memphis"
In relational algebra, we join two tables, Person and
Owner, under two conditions, P.ssn = O.ssn and O.City = "Memphis".
Write SQL to find names of all people who own a Chinese restaurant
in Memphis .
SELECT
P.Name
FROM
Person P, Owner O
WHERE
P.ssn = O.ssn AND O.City = "Memphis" AND O.type = "Chinese"
Write SQL to find names of all people who own a restaurant
using his name.
SELECT
P.Name
FROM
Person P, Owner O, Restaurant R
WHERE
P.ssn = O.ssn
AND O.type = R.type
AND O.City = R.City
AND O.State = R.State
AND P.Name = R.Name
Write SQL to find ssn of all people who own a Chinese restaurant.
SELECT
ssn
FROM
Owner
WHERE
type = "Chinese"
If someone owns ten Chinese restaurants, then his ssn
will be repeated 10 times. This is different from relational algebra.
Relational algebra is under set theory operators, so
it removes duplicates. In order to delete the repeating tuples, we
add "DISTINCT" into SELECT statement.
SELECT DISTINCT
ssn
FROM
Owner
WHERE
type = "Chinese"
Write SQL to find ssn of all people who own a Chinese restaurant
or
a French restaurant.
SELECT DISTINCT
ssn
FROM
Owner
WHERE
type = "Chinese" OR type = "French"
Write SQL to find ssn of all people who own a Chinese restaurant
and
a French restaurant.
SELECT DISTINCT
ssn
FROM
Owner O1, Owner O2
WHERE
O1.ssn = O2.ssn
AND O1.type = "Chinese"
AND O2.type = "French"
SELECT
ssn
FROM
Owner
WHERE
type = "Chinese"
INTERSECT
SELECT
ssn
FROM
Owner
WHERE
type = "French"
Notes: You can use UNION, INTERSECT, EXCEPT between
two SQLs.
Union Compatible means
-
Same type
-
Same number of attributes, corresponding must be same type,
not necessary same name.
-
Can you do the following example?
SELECT
ssn
FROM
Person
INTERSECT
SELECT
Name
FROM
Owner
Person
| ssn |
Name |
age |
City |
State |
Income
(Monthly) |
| |
|
|
|
|
|
| |
|
|
|
|
|
Write SQL to find all people who earn less than his age.
SELECT
Name, age - Income
Note: You can return
an expression.
FROM
Person
WHERE
Income < age
Write SQL to find for each person, which year he was born.
SELECT
Name, 2001 - age As birthyear
FROM
Person Note:
You can give a name for the expression.
Nested Query: A
WHERE clause can itself contain an SQL query. The useful
place is negation.
SELECT
.....
FROM
.....
WHERE
..... (SELECT .....
FROM .....
WHERE .....)
Write SQL to find Name of all people who own a Chinese restaurant.
SELECT
Name, ssn (Print key value to distinct same Name and different
persons)
FROM
Person
WHERE
ssn IN ( SELECT ssn
FROM Owner
WHERE type = "Chinese" )
Note:
The inner query (sub query) will return a set of ssn who own a Chinese
restaurant.
If someone owns more than one Chinese restaurant, this
inner query will create some duplicate ssn.
However, the outer query is checking each person's ssn is in inner set
or not.
Thus, the result should not have duplicate situation except some people
having same name.
Write SQL to find Name of all people who do not own a Chinese
restaurant.
SELECT
Name, ssn (Print key value to distinct same Name and different
persons)
FROM
Person
WHERE
ssn NOT IN
( SELECT ssn
FROM Owner
WHERE type = "Chinese" )
Note: The person does
not own a Chinese restaurant, may own other type of restaurant.
Write SQL to find Name of all people who own a Chinese restaurant.
SELECT
Name
FROM
Person
WHERE
EXISTS ( SELECT *
FROM Owner
WHERE Owner.ssn = Person.ssn
AND type = "Chinese" )
Write SQL to find Name of all people who do not own a Chinese
restaurant.
SELECT
Name
FROM
Person
WHERE
NOT EXISTS
( SELECT *
FROM Owner
WHERE Owner.ssn = Person.ssn
AND type = "Chinese" )
Note: EXISTS or NOT EXISTS depends
on whether getting returns or not from sub query, it does not matter what
to return.
Powerful part of nested query: negation
Write SQL to find Name of all people who own all restaurants.
There is no
direct thinking. So, you need to find the negation. That is,
find people who do not own all restaurants.
In another
word, find people, there is a restaurant, who do not own.
SELECT
Name
FROM
Person
WHERE
NOT EXISTS
(
put in "a
query return all restaurants that he does not own"
)
Key: use set difference to
find all restaurants he does not own.
Select all restaurant then take away
the restaurants that he has.
That is, ( SELECT
type, City, State
FROM Restaurant
EXCEPT
SELECT type, City, State
FROM Owner
WHERE ssn = Person.ssn )
Write SQL to find all restaurants which John Doe (ssn ="123456789")
does not own.
SELECT
*
FROM
Restaurant R
WHERE
NOT EXISTS
(SELECT *
FROM OWNER O
WHERE R.type = O.type
AND R.City = O.City
AND R.State = O.State
AND O.ssn = "123456789")
Notes:
-
Using "Join" operator, DBMS will check the condition tuple
by tuple and there is no memory to remember last checking.
-
Using "Nested Query", you will go through all cases.
It is good at
negation.