SQL:

1
SQL ??????
2
????? ?? ??? ??????

• ??? ?????? ?? ??????

• Ordered
• Cust_Id
• Book_Name
• Order_Date

• Books
• Book_Id
• Book_Name
• Year
• Max_Time
• Faculty
• Pages

• Customers
• Cust_Id
• Cust_Name
• Faculty

• Borrowed
• Cust_Id
• Book_Id
• From_Date
• To_Date

3
????? ???????

• Customers(Cust_Id, Cust_Name, Faculty)
• Cust_Id (?????? ??? ????) ??' ???? ?? ????
• Cust_Name?? ????
• Faculty ?? ??????

4
????? Customers
Faculty Cust_Name Cust_Id
CS Moshe Cohen 12345
EE Avi Barak 23456
MED Avi Barak 34567
EE Lior Edri 45678
EE Moshe Cohen 56789
EE Moshe Cohen 67890
5
????? ??????? (????...)

• Books(Book_Id, Book_Name, Year, Max_Time,
  Faculty, Pages)
• Book_Id ??' ???? (?????? ??? ????)
• Book_Name ?? ?????
• Year ??? ?????
• Max_Time ??? ????? ???' ????? (?????)
• Faculty ?? ??????
• Pages ??' ??????

6
????? Books
Faculty Pages Max_Time Year Book_Name Book_Id
CS 348 7 1998 Database Systems 1111
CS 348 14 1998 Database Systems 1112
CS 424 7 2001 Database Systems 1113
CS 390 1 1998 Database And Knowledge 2222
EE 390 7 1998 Database And Knowledge 2223
EE 180 21 1998 Electronic Circuits 3333
MED 580 7 1985 Genes 7 4444
MED 450 7 1988 Anatomy 5555
7
????? ??????? (????...)

• Ordered(Cust_Id, Book_Name, Order_Date)
• Cust_Id ??' ???? ?? ????
• Book_Name ?? ?????
• Order_Date ????? ????? ?????

8
???? Ordered
Order_Date Book_Name Cust_Id
14-Oct-2002 Database Systems 12345
24-Oct-2002 Anatomy 45678
30-Oct-2002 Database And Knowledge 12345
12-Oct-2002 Electronic Circuits 45678
9
????? ??????? (????...)

• Borrowed(Book_Id, Cust_Id, From_Date, To_Date)
• Book_Id ??' ???? ?? ???
• Cust_Id ??' ???? ?? ????
• From_Date ????? ????? ????
• To_Date ????? ????? ????

10
???? Borrowed
To_Date From_Date Cust_Id Book_Id
13-Oct-2002 56789 5555
11
??????? ?-SQL SELECT

• SELECT ALL DISTINCT table. expr
  alias, exp alias,
• FROM table alias, table alias,
• WHERE condition
• GROUP BY expr, expr, HAVING condition
• INTERSECT MINUS UNION UNION ALL SELECT
  
• ORDER BY expr ASC DESC , expr ASC
  DESC,

12
??????? ?-SQL SELECT

• ??????? ?????? ????? ????? ???? ??????? ???
  ??????? ????? ???.
• SELECT column, column,
• FROM table

• ????? ????? ?? ?????? ????? ??????? ??? ???
• SELECT Book_Name, Pages FROM Books

13
????? - ????
Books
Faculty Pages Max_Time Year Book_Name Book_Id
CS 348 7 1998 Database Systems 1111
CS 348 14 1998 Database Systems 1112
CS 424 7 2001 Database Systems 1113
CS 390 1 1998 Database And Knowledge 2222
EE 390 7 1998 Database And Knowledge 2223
EE 180 21 1998 Electronic Circuits 3333
MED 580 7 1985 Genes 7 4444
MED 450 7 1988 Anatomy 5555
14
????? - ?????
SELECT Book_Name, Pages FROM Books
Pages Book_Name
348 Database Systems
348 Database Systems
424 Database Systems
390 Database And Knowledge
390 Database And Knowledge
180 Electronic Circuits
580 Genes 7
450 Anatomy
15
????? ?? ?????

• ?????? ?? ?????, ??????? ??? "" (??????).
• ????? ????? ?? ????? ?? ?? ??????
• SELECT FROM Books

16
????? - ?????
SELECT FROM Books
Faculty Pages Max_Time Year Book_Name Book_Id
CS 348 7 1998 Database Systems 1111
CS 348 14 1998 Database Systems 1112
CS 424 7 2001 Database Systems 1113
CS 390 1 1998 Database And Knowledge 2222
EE 390 7 1998 Database And Knowledge 2223
EE 180 21 1998 Electronic Circuits 3333
MED 580 7 1985 Genes 7 4444
MED 450 7 1988 Anatomy 5555
17
??????? ?????????

• ???? ????? ??????? ????????? ???????? ?????
  ?????.
• ????? ????? ???? ?????? ????? ?? ?? ?????? ????
  ?????? ???????.
• SELECT Book_Id, Book_Name, Max_Time/7
• FROM Books

18
????? - ????
Books
Faculty Pages Max_Time Year Book_Name Book_Id
CS 348 7 1998 Database Systems 1111
CS 348 14 1998 Database Systems 1112
CS 424 7 2001 Database Systems 1113
CS 390 1 1998 Database And Knowledge 2222
EE 390 7 1998 Database And Knowledge 2223
EE 180 21 1998 Electronic Circuits 3333
MED 580 7 1985 Genes 7 4444
MED 450 7 1988 Anatomy 5555
19
????? - ?????
SELECT Book_Id, Book_Name, Max_Time/7 FROM Books
Max_Time/7 Book_Name Book_Id
1 Database Systems 1111
2 Database Systems 1112
1 Database Systems 1113
0 Database And Knowledge 2222
1 Database And Knowledge 2223
3 Electronic Circuits 3333
1 Genes 7 4444
1 Anatomy 5555
20
????? ??? ???????? Where -

• ??????? WHERE condition ?????? ????? ?? ???
  ????????.
• ????? ????? ???? ?? ?????? ?????? ???? ????
  1990
• SELECT Book_Name
• FROM Books
• WHERE Year gt 1990

21
????? - ????
Books
Faculty Pages Max_Time Year Book_Name Book_Id
CS 348 7 1998 Database Systems 1111
CS 348 14 1998 Database Systems 1112
CS 424 7 2001 Database Systems 1113
CS 390 1 1998 Database And Knowledge 2222
EE 390 7 1998 Database And Knowledge 2223
EE 180 21 1998 Electronic Circuits 3333
MED 580 7 1985 Genes 7 4444
MED 450 7 1988 Anatomy 5555
22
????? - ?????
SELECT Book_Name FROM Books WHERE Year gt 1990
Book_Name
Database Systems
Database Systems
Database Systems
Database And Knowledge
Database And Knowledge
Electronic Circuits
23
WHERE (????...)

• ????? ???? ???????
• ????? ?????? ???? gt, , lt, ltgt, lt ...
• ????? ?????? ?????? AND, OR, NOT.
• ????? ????? ???? ?? ?????? ?????? ??? ????? 1990
  ?- 2000
• SELECT Book_Name
• FROM Books
• WHERE Year gt 1990 AND Year lt 2000

24
????? - ????
Books
Faculty Pages Max_Time Year Book_Name Book_Id
CS 348 7 1998 Database Systems 1111
CS 348 14 1998 Database Systems 1112
CS 424 7 2001 Database Systems 1113
CS 390 1 1998 Database And Knowledge 2222
EE 390 7 1998 Database And Knowledge 2223
EE 180 21 1998 Electronic Circuits 3333
MED 580 7 1985 Genes 7 4444
MED 450 7 1988 Anatomy 5555
25
????? - ?????
SELECT Book_Name FROM Books WHERE Year gt 1990
AND Year lt 2000
Book_Name
Database Systems
Database Systems
Database And Knowledge
Database And Knowledge
Electronic Circuits
26
??????? BETWEEN

• ????? ???????? BETWEEN ???? ????? ????? ?? ?????
  ???? ?????
• SELECT Book_Name
• FROM Books
• WHERE Year BETWEEN 1990 AND 2000

27
??? ?????

• ????? ????? ???? ?? ?????? ?????? ?? ??? ?????
  1990 ? 2000
• SELECT Book_Name
• FROM Books
• WHERE NOT (Year BETWEEN 1990 AND 2000)
• ??
• WHERE Year NOT BETWEEN 1990 AND 2000

28
????? ?????? ?? ?????

• ????? ????? ?? ?????? ?????? ????? 1992, 1995 ??
  1999
• SELECT Book_Name
• FROM Books
• WHERE Year1992 OR Year1998 OR Year2001

29
????? ?????? ?? ????? - IN

• ????? ???????? IN
• SELECT Book_Name
• FROM Books
• WHERE Year IN (1992, 1998, 2001)
• ????? ???????? NOT IN
• SELECT Book_Name
• FROM Books
• WHERE Year NOT IN (1992, 1998, 2001)

30
???????? LIKE

• ???????? LIKE ???? ????? ?? ?????? ?????? ?????
  (wildcards).
• ??? "_" ????? ??? ????.
• ??? "" ????? ????? ????? ?????, ????? 0 ?? ????.
• ????? ????? ?? ?????? ???? ???? ?? ???????
  Database ????? ???? ?????? ??? m
• SELECT Book_Name FROM Books
• WHERE Book_Name LIKE Databasem_

31
????? - ????
Books
Faculty Pages Max_Time Year Book_Name Book_Id
CS 348 7 1998 Database Systems 1111
CS 348 14 1998 Database Systems 1112
CS 424 7 2001 Database Systems 1113
CS 390 1 1998 Database And Knowledge 2222
EE 390 7 1998 Database And Knowledge 2223
EE 180 21 1998 Electronic Circuits 3333
MED 580 7 1985 Genes 7 4444
MED 450 7 1988 Anatomy 5555
32
????? - ?????
SELECT Book_Name FROM Books WHERE Book_Name
LIKE Databasem_
Book_Name
Database Systems
Database Systems
Database Systems
33
????? ????? NULL

• NULL ??? ????? ?????? ??? ??? ("????? ????")
• ???????
• ????? ????? ?? ??? ?????? ?? ?????
• ?????? ?? ???? ??????
• ?????? ?-NULL
• expr IS NULL ????? true ?? expr ??? ??? NULL
• expr IS NOT NULL
• ????? ????? ?? ?????? ???? ?? ??????.
• SELECT Book_Id FROM Borrowed
• WHERE To_Date IS NULL

34
???? NULL (????)

• ???? ??? ?? ????? ??????? ?? ????? ???? ?????
  NULL ???? ???? NULL.
• ????? ????? "????? ????" ???? ??? ????? ?????
  NULL
• COALESCE(value, default)
• ????? ???? ??? ?????? ??????? ?? ?? ?????? (????
  ????? ?????? ?? ?????? ?? ????? 0).
• SELECT Book_Id , COALESCE(To_Date From_Date, 0)
  / 7
• FROM Borrowed
• ????? ????? COALESCE(expr1,expr2,) ????? ??
  ???? ?????? ????? ?- NULL

35
????? ???????? - DISTINCT

• ??????? DISTINCT ?????? ?? ??????? ???????
  ?????? ???????.
• ????? ????? ?? ???? ?????? ????? ??????, ???
  ?????.
• SELECT DISTINCT Book_Name, Year
• FROM Books

36
????? - ????
Books
Faculty Pages Max_Time Year Book_Name Book_Id
CS 348 7 1998 Database Systems 1111
CS 348 14 1998 Database Systems 1112
CS 424 7 2001 Database Systems 1113
CS 390 1 1998 Database And Knowledge 2222
EE 390 7 1998 Database And Knowledge 2223
EE 180 21 1998 Electronic Circuits 3333
MED 580 7 1985 Genes 7 4444
MED 450 7 1988 Anatomy 5555
37
????? - ?????
??? DISTINCT
???? DISTINCT
Year Book_Name
1998 Database Systems
1998 Database Systems
2001 Database Systems
1998 Database And Knowledge
1998 Database And Knowledge
1998 Electronic Circuits
1985 Genes 7
1988 Anatomy
Year Book_Name
1998 Database Systems
2001 Database Systems
1998 Database And Knowledge
1998 Electronic Circuits
1985 Genes 7
1988 Anatomy
38
???? - ORDER BY

• ORDER BY ???? ?? ??????? ??????? ?? ?? ??????
  ?? ???? ?? ??????? ??????.
• ????? ????? ?? ?????? ??????? ??? ??? ??????
• SELECT FROM Books
• ORDER BY Year

39
????? - ?????
SELECT FROM Books ORDER BY Year
Faculty Pages Max_Time Year Book_Name Book_Id
MED 580 7 1985 Genes 7 4444
MED 450 7 1988 Anatomy 5555
CS 348 7 1998 Database Systems 1111
CS 348 14 1998 Database Systems 1112
CS 390 1 1998 Database And Knowledge 2222
EE 180 21 1998 Electronic Circuits 3333
EE 390 7 1998 Database And Knowledge 2223
CS 424 7 2001 Database Systems 1113
40
???? ???? ???? -DESC

• SELECT FROM Books ORDER BY Year DESC

Faculty Pages Max_Time Year Book_Name Book_Id
CS 424 7 2001 Database Systems 1113
MED 450 7 1988 Anatomy 5555
CS 348 7 1998 Database Systems 1111
CS 348 14 1998 Database Systems 1112
CS 390 1 1998 Database And Knowledge 2222
EE 180 21 1998 Electronic Circuits 3333
EE 390 7 1998 Database And Knowledge 2223
MED 580 7 1985 Genes 7 4444
41
???? ??? ??? ???????

• ???? ?????? ?????.
• ????? ????? ???? ?????? ??????? ??? ??? ?????? ,
  ???? ???? ??????? (??? ?????? ?????? ????? ???).
• SELECT FROM Books
• ORDER BY Year, Pages

42
???? (????...)

• ???? ????? ?? ??' ???? ????? ?? ?????.
• ????? ????? ???? ?????? ??????? ??? ???? ??????
• SELECT FROM Books
• ORDER BY 3

43
???????? ?????????

• ?- SQL ???? ????????? ?????????? ?????
• MIN ???????
• MAX ???????
• AVG ?????
• SUM ????
• COUNT ???? ???????
• ?? ??? ????????? ??? ????? ?? ????? ????? ???????
  ??? ???.

44
?????

• ????? ??? ?? ???? ??????? ?????? ????????? ???
  ?? ??????
• ?????
• SELECT AVG(Pages), MAX(Pages)
• FROM Books

45
????? ????

• Books

Faculty Pages Max_Time Year Book_Name Book_Id
CS 348 7 1998 Database Systems 1111
CS 348 14 1998 Database Systems 1112
CS 424 7 2001 Database Systems 1113
CS 390 1 1998 Database And Knowledge 2222
EE 390 7 1998 Database And Knowledge 2223
EE 180 21 1998 Electronic Circuits 3333
MED 580 7 1985 Genes 7 4444
MED 450 7 1988 Anatomy 5555
46
????? ????
Pages
348
348
424
390
390
180
580
450
AVG(Pages) MAX(Pages)
389 580
47
???????? ????????? ????

• ???? ???? ???????? ????????? ?? ?? ??? ???????
  ????? ?"? ????? ??????? WHERE .
• ??????
• SELECT COUNT (Book_Name)
• FROM Books
• WHERE Year 1998

48
???????? ????????? ????

• Books

Faculty Pages Max_Time Year Book_Name Book_Id
CS 348 7 1998 Database Systems 1111
CS 348 14 1998 Database Systems 1112
CS 424 7 2001 Database Systems 1113
CS 390 1 1998 Database And Knowledge 2222
EE 390 7 1998 Database And Knowledge 2223
EE 180 21 1998 Electronic Circuits 3333
MED 580 7 1985 Genes 7 4444
MED 450 7 1988 Anatomy 5555
49
????? ????
Book_Name
Database Systems
Database Systems
Database And Knowledge
Database And Knowledge
Electronic Circuits

• Book_Name

Book_Name
Database Systems
Database Systems
Database Systems
Database And Knowledge
Database And Knowledge
Electronic Circuits
Genes 7
Anatomy
WHERE
COUNT
COUNT(Book_Name)
5
50
???????? ????????? -????? ????????

• ????? ???????? ???? ????? ???????? ?"? ?????
  ?????DISTINCT ???? ??????.
• ?????
• SELECT COUNT (DISTINCT Book_Name)
• FROM Books
• WHERE Year 1998

51
????? ???? (DISTINCT)
DISTINCT Book_Name
Database Systems
Database And Knowledge
Electronic Circuits
Book_Name
Database Systems
Database Systems
Database And Knowledge
Database And Knowledge
Electronic Circuits
COUNT(DISTINCT Book_Name)
3
52
?????? ????????? ?? ???? NULL

• ?? ????????? ?????????? ??????? ????? NULL.
• ???? ?? ???? COUNT().
• ?????
• SELECT COUNT ()
• FROM Books
• WHERE Year 1998

53
???????? ????????? ????

• ????? ????? ???? ?????? ??? ???
• ?????? ?????
• SELECT Year, COUNT(Book_Id)
• FROM Books
• ?? ????! (???? ????? ?? Year, ??? ??? ?? COUNT).

54
????? GROUP BY

• ????? ???????? ????????? ?? ?????? ?? ??????.
• ???? ?????? (?????)
• SELECT Year, COUNT(Book_Id)
• FROM Books
• GROUP BY Year
• ??? ??? ?? Year, ?-COUNT ????? ?????.

55
????? ???? (GROUP BY)
Year Book_Id
1998 1111
1998 1112
2001 1113
1998 2222
1998 2223
1998 3333
1985 4444
1988 5555
COUNT(Book_Id) Year
5 1998
1 1988
1 2001
1 1985
56
????? ????

• ????? ?????
• SELECT Faculty, COUNT(Book_Id)
• FROM Books
• GROUP BY Year
• ?? ????! ???? ?????? ??? Year, ??? ????? ??????
  ????? ???? Faculty ?????.
• ??? ????? ??????? ?????????, ???? ????? ?? ????
  ????? ????? ?????? (????????).

57
????? ???? ????? - HAVING

• ??????? HAVING condition ????? ??? ????????
  ???????? ?-GROUP BY .
• ????? condition ???????? ?????????, ???? ?????
  ????? ??????, ???????.

58
????? (HAVING)

• ????? ?? ?????? ??????? ?????
• SELECT Year, COUNT(Book_Id)
• FROM Books
• GROUP BY Year
• HAVING AVG(Pages) gt 400
• ???? ???? ?????? ??? Year, ???? ?? ???????
  ?????? ???? ???? ????? ????? ??????? ???? ?- 400.

59
????? ???? (HAVING)

• Books

Faculty Pages Max_Time Year Book_Name Book_Id
CS 348 7 1998 Database Systems 1111
CS 348 14 1998 Database Systems 1112
CS 424 7 2001 Database Systems 1113
CS 390 1 1998 Database And Knowledge 2222
EE 390 7 1998 Database And Knowledge 2223
EE 180 21 1998 Electronic Circuits 3333
MED 580 7 1985 Genes 7 4444
MED 450 7 1988 Anatomy 5555
60
????? ???? (HAVING)
AVG(Pages) COUNT(Book_Id) Year
331 5 1998
450 1 1988
424 1 2001
580 1 1985
61
????? WHERE ????? HAVING

• WHERE ????? ?????? ???? ??????
• HAVING ????? ?????? ???? ??????.
• ????? ??? ?? ?????? ?? ???? ?-200 ??????, ???
  ??? ??? ?? ??' ?????? ????? ????, ????? ???????
  ???? ??????? ????? ??? ???? ?- 400.

62
????? ????

• ????? ??? ?? ?????? ?? ???? ?-200 ??????, ???
  ??? ??? ?? ??' ?????? ????? ????, ????? ???????
  ???? ??????? ????? ??? ???? ?- 400.
• SELECT Year, COUNT(Book_Id)
• FROM Books
• WHERE Pages gt 200
• GROUP BY Year
• HAVING AVG(Pages) gt 400

63
????? ????

• Books

64
????? ????

• ???? GROUP BY Year

AVG(Pages) COUNT(Book_Id) Year
369 4 1998
450 1 1988
424 1 2001
580 1 1985
65
????? ????

• ???? (?????)
• ????? ??????? ?? ?????? ??????? ???? ?- 200
  ??????.
• ????? ??????? ???????, ?? ???? ????? ?? ???
  ??????? (??????) ???? Year.
• ????? ???? ??????? ?????? ??? ?????, ??????
  ??????? ??? ?????? ??? ????? 400.
• ????? ??' ?????? ??? ?????.

66
??????? ???? ?????? ?? ???? ????? (JOIN)

• ???? "??????" ?????? ???????/?????? ?????
• ?????? FROM table1, table2,
• ?????? ????? ?????? ??? ?? ???????
• ???????? ????? ????? ?????? table1.field
• ????? ????? ?"? ????? ????? ?- WHERE

67
????? (JOIN)

• ????? ????? ???? ?????? ????? ?? ??????? ??? ??
  ???? ?????? ???????.
• ?????
• SELECT Customer.Cust_Id, Cust_Name, Book_Name
• FROM Customer, Ordered
• WHERE Customer.Cust_Id Ordered.Cust_Id

68
????? ???? (JOIN)
Order_Date Book_Name Cust_Id
14-Oct-2002 Database Systems 12345
24-Oct-2002 Anatomy 45678
30-Oct-2002 Database And Knowledge 12345
12-Oct-2002 Electronic Circuits 45678
Ordered
Faculty Cust_Name Cust_Id
CS Moshe Cohen 12345
EE Avi Barak 23456
MED Avi Barak 34567
EE Lior Edri 45678
EE Moshe Cohen 56789
EE Moshe Cohen 67890
Customer
69
????? ???? (JOIN)
?????
Book_Name Cust_Name Cust_Id
Database Systems Moshe Cohen 12345
Database And Knowledge Moshe Cohen 12345
Anatomy Lior Edri 45678
Electronic Circuits Lior Edri 45678
70
JOIN ????? ?? ????? ?????

• ????? ?? ????? ?? ?????.
• SELECT Customer., Book_Name
• FROM Customer, Ordered
• WHERE Customer.Cust_Id Ordered.Cust_Id

71
OUTER JOIN ????? ?????? ??? ????? ????? ?????

• ???? ????? ?? ??????? ??? ?????? ?? ???
• ?????
• SELECT Customer.Cust_Id, Cust_Name, Book_Name
• FROM Customer LEFT OUTER JOIN Ordered
• ON ( Customer.Cust_Id Ordered. Cust_Id )
• ?????????? ????? ???? ???? NULL ????? ???????,
  ?"??????" ??? ????
• ???? ?? ON ????? WHERE (???? ??????)

72
????? ???? (OUTER JOIN)
Book_Name Cust_Name Cust_Id
Database Systems Moshe Cohen 12345
Database And Knowledge Moshe Cohen 12345
Anatomy Lior Edri 45678
Electronic Circuits Lior Edri 45678
Avi Barak 23456
Avi Barak 34567
Moshe Cohen 56789
Moshe Cohen 67890
?????
73
????? ?????

• ????? ?? ???? ?????? ???
• SELECT Customer.Cust_Id, Cust_Name,
  COUNT(Book_Name)
• FROM Customer LEFT OUTER JOIN Ordered
• ON (Customer.Cust_Id Ordered. Cust_Id (
• GROUP BY Customer.Cust_Id, Cust_Name

74
????? ????

• ????? ??????? ?????? ??? ???? ID, ?? ???' ??????
  ??????.
• ?????

COUNT (Book_Name) Cust_Name Cust_Id
2 Moshe Cohen 12345
2 Lior Edri 45678
0 Avi Barak 23456
0 Avi Barak 34567
0 Moshe Cohen 56789
0 Moshe Cohen 67890

• ?? ????? ?????? ??? ?-OUTER JOIN?

75
???? ?????? ALIAS

• ???? ?? ???? (??"? ??? ????) ?????
• ?????
• SELECT C.Cust_Id, Cust_Name, Book_Name
• FROM Customer C, Ordered O
• WHERE C.Cust_Id O. Cust_Id

76
ALIAS ????? ????? ???? ??????

• ????? ???? ???? ?????? ?? ???? ????
• SELECT DISTINCT C1.Cust_Name
• FROM Customer C1, Customer C2
• WHERE C1. Cust_Name C2. Cust_Name
• AND C1.Cust_Id ltgt C2.Cust_Id

• ???? ?? ?????? ????????
• ????? ?? ??????? ?? ??? ??????? ??? ???? ?? ????
  ??.

77
ALIAS ??? ?? ?????? ????

• ????? ???? ??? ?? ?????? ??????? ?? ???????
• ?????

• SELECT Year, COUNT(Book_Id) AS Num_Books
• FROM Books
• GROUP BY Year

78
????????? ????? ???????

• ???????? ?????? ??? ????? ?? n-???.
• ????????? ?-SQL
• ????? INTERSECT
• ?????/???? EXCEPT
• ????? UNION
• ????? ??? ????? ???????? - UNION ALL
• ???? ???????? ??????? ???? ???? ?????? ?????
  ???? ??????, ??????.

79
????????? ????? ??????? ????

• ????? ?? ??????? ??? ?????? ?? ???
• SELECT Cust_Id FROM Customer
• EXCEPT
• SELECT Cust_Id FROM Ordered

• ????? ?????
• ORDER BY ???? ?????? ???? ?? ??????????
• ???? ?????? ????? ???? ??? ???? (????? ????? ????
  ??? ?????????)

80
???-??????? SUBQUERIES

• ???????? ????? ?-WHERE ???? ????? ??? ???? ????,
  ??? ???? ????? ????.
• ????? ????? ?? ?????? ????? ???? ????? ??? ???
  ??? ??' 1112.
• SELECT Book_Name FROM Books
• WHERE Year
• (SELECT Year FROM Books WHERE Book_Id 1112)

81
???-??????? ????

• ??-?????? ????? ????? ??? ???????.
• ???? ?????? ?????? ?? ?? ???????? ???? ?? ?????.
• ????? ????? ?? ?????? ????? ???? ????? ???
  ??????? ????? ?????? ??? ??? ??' 1112.
• SELECT Book_Name FROM Books
• WHERE (Year, Faculty)
• (SELECT Year, Faculty FROM Books
• WHERE Book_Id 1112)

82
???-??????? ????

• ?? ?????? ????? ?????? ???? ? From.
• ?? ??????? ????? ???? Alias.
• ????? ????? ???? ?????? ??????? ???? 20-0ct-98
• ?"? ???? 12345.
• SELECT Book_Name
• FROM (SELECT FROM Ordered
• Where Order_Date gt 20-0ct-98) O
• WHERE O. Cust_Id 1234

83
?????

• ???????? ?????? ??? ???? ?????? ?????
• ?????? ??? x ?????? A, ???? ????
• x ???? ??? ?????? ?-A (?y?Axgty)
• x ??? ???? ????? ?-A (?y?Axlty)
• x?A (?y?Axy)
• ????? ?-SQL
• ANY -- ????
• ALL -- ???

84
????? ????

• ?????? ???? ?"? ????? ????? ANY ?? ALL ???? ????
  ???????
• ????? ????? ????? ???? ???? ?????? ??? ??????
  ??????? ????? ?????.
• SELECT Book_Name, Pages
• FROM Books
• WHERE Pages gtALL
• (SELECT Pages FROM Books WHERE Faculty cs)

85
????? ???? (??????? ??????)

• ???? ???? ??????? ?????? ??????? ?????
• ANY ? IN
• ltgtALL ? NOT IN
• ?????

SELECT Book_Name, Year FROM Books WHERE Year IN
(SELECT Year FROM Books WHERE Faculty
MED)
86
???-??????? ???????

• ???? ????? ???-???????
• ????? ?? ???????? ???????? ????? ?????? ???? ???
  ????? ????

SELECT Faculty FROM Books WHERE Book_Name IN
(SELECT Book_Name FROM Ordered WHERE Cust_Id
IN
(SELECT Cust_Id FROM Customer WHERE
Cust_Name Lior Edri))
87
???-??????? ????

• ???-??????? ????? JOIN
• ????? ???? ???? ?????? ??? ????? ???? ???????

SELECT B.Faculty FROM Books B, Customer C,
Ordered O WHERE B.Book_Name O.Book_Name AND
C.Cust_Id O. Cust_Id AND C.Cust_Name Lior
Edri

• ???? ???? ??? ???? ??????
• ????? ???? ???????????? ????, ??"? ?????? ??
  ????-??????? ???? ????

88
???-??????? ?????? ?????

• ???????? ?? ??? ??-?????? ??? ???????? ??
  ??????? ???????? ????????
• ????? ?? ?????? ????? ?????? ???? ?????? ?????
  ??????? ????? ??????

SELECT Book_Id, Book_Name, Max_Time FROM Books
B WHERE Max_Time gt (SELECT AVG(Max_Time) FROM
Books WHERE Faculty B.Faculty)
89
??????? EXISTS

• EXISTS ??? ??????? ??????? ????? ?? ??-??????.
• ????? True ?? ??-??????? ?????? ??? ?????, False
  ?? ???? ?????? ???.
• ????? ????? ?? ??????? ??????? ??? ?????

SELECT Cust_Name FROM Customer C WHERE EXISTS
(SELECT FROM Ordered WHERE Cust_Id
C.Cust_Id)