SQL (Structured Query Language)

1
SQL (Structured Query Language)

• ?????? ????? ?"? ANSI
• ?????????? ??? ??? ?????? ???????
• ??? ??????? ??? ??? ?????
• ???? ???? ?? ???? ?????
• ???? ???? ?????
• ????? ?????? ????? ????.

2
???? ?????? ? - SQL

• DDL (Data Definition Language)
• Create database , create table , constraints
• DML (Data Manipulating Language)
• select , insert , delete , update
• DCL (Data Control Language)
• Grant , revoke , deny

3
????? ???? select

• Select ????? ???? ??????
• From ??????
• Where ?????????? ?????? ??????
• Group by
  ?????
• Having ?????????? ?????? ??????
• Order by ????
  ??????

4

• Select from ???? ???? (?? ???????)
• Select - ???? ?? ?????
• ? select ???? ??????? ???????
• ??? ????? ????? ???? ?- from

????? Select wo_fname , wo_lname From tbl_workers
5
????? ? where

• ?????????? ?????? ??????.
• ????? ??????? ?? ?????? ?????? ? select
• ???????? ??? ???? ????? ????? ?? ??????? , gt ,
  gt , lt gt ???'
• ???? ?????? ?????????? in, between , like
• ????? ????? ?? and ? or. (And ???? ? Or)

6

• ?????
• ??? ?? ???? ?? ??????? ???????? ??? 5000 ?? ???
  ???????? ?????? 20

Select wo_fname , wo_lname From tbl_workers Where
wo_salary gt 5000 And dep_id lt gt 20
7

• ???????
• ??? ?? ???? ??????? ???????? ???? ?? ???? ??????
  ????? ?????? ????? ? 3
• ??? ?? ???? ?? ??????? ?? ??????? ????? ??????
  ???? 2003 ???? ??? ????.

8
???? ?????? Order by

• ???? ????? ??? ?? ??? (?? ?? ?????? ?- select)
• ???? ??????? ???? ????. (???? ????
• desc ?? ??? ( order by
• ????? ???? gt ???? ??? ???? ????.
• ???? ????? ??? ???? ???? (????? ????? -gt ??????)

9
????? ?- select

• ????? ?????
• Select wo_salary 1.1 as new_sal
• ?????
• Select wo_fname - _ wo_lname as full_name

10
????? ????????

• ????? ????? getdate ( )
• ???? ??? ???????
• Datediff(param , date1 , date2)
• Param yyyy , m, d, hh
• ????? ?? ????? ??????
• Dateadd (param , number , date)

11
?????

• ???? ??? ????? ?? ?? ????
• Select dateadiff(d,vac_start_date , vac_end_date)
  as days
• ???????
• 3. ??? ??? ??????? ??? ??????? ?????? ?????? ????
  ??? ???. ???? ???????? ????? ?????? ???? ?????
  ????? ?????? ??????.
• 4. ??? ?? ????? ?? ??????? ??????? ??? ?????
  ?????. ???? ?? ??????? ??? ???? ????.

12
????? ??? ??????

• ????? ??????
• Select wo_id , dep_name
• From tbl_workers , tbl_dept

Projects 10
Marketing 20
Developing 30
10 127
10 128
20 129
127 Projects 128 Projects
129 Projects 127 Marketing 128
Marketing 129 Marketing 127 Developing
128 Developing 129 Developing
13

• ????? ????? (inner join)
• Select wo_id , dep_name
• From tbl_workers w , tbl_dept d
• Where w.dep_id d.dep_id

Projects 10
Marketing 20
Developing 30
10 127
10 128
20 129
130
127 Projects 128 Projects 129 Marketing
14

• ????? ?????? (????? ?? ????)
• Select wo_id , dep_name
• From tbl_workers w left join tbl_dept d on
• w.dep_id d.dep_id

10 127
10 128
20 129
130
Projects 10
Marketing 20
Developing 30
127 Projects 128 Projects 129 Marketing 130
Null
15

• ????? ?? ???? ???? ??????
• Select wo_id , dep_name
• From ( tbl_workers w left join tbl_dept d on
• w.dep_id d.dep_id ) left join tbl_loans l on
  w.wo_id l.wo_id

??????? 5. ??? ?? ???? ?? ??????? ??????? ?????
?? ??????? ????. 6. ??? ?? ???? ?? ??????? ??
???? ?????? ???.
16
????? ???? ?? ???? ???? - ????? ??? ??? ??????
????? ????? ?????.
????? ???? ???? ?? ??????? ????? ?????? ????
Select work.wo_fname , work.wo_lname ,
boss.wo_fname , boss.wo_lname From tbl_workers
work , tbl_workers boss Where work.wo_boss_id
boss.wo_id
130 Glich Noam 123
130 Cohen Merry 124
Tasa Shuki 130
130 Glich Noam 123
130 Cohen Merry 124
Tasa Shuki 130
17

• ???????
• 7. ???? ?????? ????? ???? ????? ?? ?? ???????
  ??????? ???? ???????? ????? ? 2000 ?? ????.
• 8. ??? ?? ???? ?? ??????? ????? ??? ?????? ?????
  ?? ???? ???? ??? ?? ???.