DDL DATA DEFINITION LANGUGE Create Schema Command Owner

ﻟﻐﺔ ﺗﻌﺮﻳﻒ ﺍﻟﺒﻴﺎﻧﺎﺕ -: ﺍﻭﻻ -: (DDL) DATA DEFINITION LANGUGE Create Schema Command : Owner ﺍﺳﻢ ﻗﺎﻋﺪﺓ Owner ﺍﻻﻣﺮ EX: ﺍﻟﺒﻴﺎﻧﺎﺕ Create SCHEMA Company Authorization Hassan In SQL Server DATABASE TABLE Creation : Crate Table-name ( column-name type , olumn-name type, column-name type, ); M-H

EX : Create Table Employees ( ID Char (5), NAME char (30), TEL Char (15), Salary Decimal (5, 2) ); M-H ID NAME TEL SALARY 1 Ali 010 1000

Some DATA TYPES 1. 2. 3. 4. 5. 6. 7. Char (x)=10 Varchar (x) =10 Small Integer Date Time Decimal (x , y) M-H ﺃﻨﻮﺍﻉ ﺍﻟﺒﻴﺎﻧﺎﺕ X X Length (x)=2

EX : Create Table 6; ( EMP-NO smallint, Name Char(30), Salary Decimal (5, 2), Primary key (Emp-no), Forign key (name ) References table 7 (EMP-name) ); M-H

To cancel table : - ﻻﻟﻐﺎﺀ ﺟﺪﻭﻝ ﻣﺎ ﺍﺳﻢ ﺍﻟﺠﺪﻭﻝ ﺍﻟﻤﺮﺍﺩ ﺣﺬﻓﻪ DROP Table-Name; To modify table description : Alter table Table-Name; ﻟﺘﻌﺪﻳﻞ ﺗﻮﺻﻴﻒ ﺟﺪﻭﻝ ﻣﺎ : ﻹﺿﺎﻓﺔ ﺃﻌﻤﺪﺓ ﺟﺪﻳﺪﺓ Add (filed name type 1 , filed name type 2); : ﻟﺘﻌﺪﻳﻞ ﻭﺻﻒ ﻋﻤﻮﺩ Alter table- name modify (filed -name type); : ﻟﺘﻐﻴﺮ ﺍﺳﻢ ﺍﻟﺠﺪﻭﻝ Alter table old-name rename to new-name ; M-H

Data manipulation language (DML) Select Insert Update Delete Select statement Select <colm 1, colum 2, ……. . > from <table 1, table 2, ………. > Select < ﺍﻻﻋﻤﺪﺓ >ﻗﺎﺋﻤﺔ from < >ﻗﺎﺋﻤﺔ ﺍﻟﺠﺪﺍﻭﻝ M-H

ITEMS-Table Ex 1 : ID Item-Name Q 1 Select Item-Name, Qty From ITEMS-Table; Results of Q 1 Book 500 Note-book 1500 Ruler 100 Stapler ﺩﺑﺎﺳﺔ 50 pen 5000 M-H O/p Qty 1 Book 500 2 Note-book 1500 3 Ruler 100 4 Stapler ﺩﺑﺎﺳﺔ 50 5 pen 5000

Student – score – table Q 2 Select St ID, Subject 1 score From St – sc – table ; Results of Q 2 50 70 60 50 70 80 80 95 90 65 200 90 250 85 M-H O/p St – sc – table St ID St Name Subject 1 score Subject 2 score 50 ﻣﺤﻤﺪ ﻋﻠﻲ 70 80 60 ﺣﺴﻦ ﻣﺤﻤﺪ 50 60 70 ﻋﻠﻲ ﻋﻠﻴﻮﺓ 80 15 80 ﻣﺤﻤﺪ ﺣﺴﻴﻦ 95 40 90 ﺍﻧﺲ ﺍﻟﻤﺎﻟﻜﻲ 65 20 200 ﻣﺤﻤﺪ ﺯﻛﺮﻳﺎ 90 90 250 ﺳﻤﻴﺮ ﺍﻧﺴﻲ 85 100

Q 3 Select * From ITEMS-Table; Results of Q 3 1 Book 500 2 Note-book 1500 3 Ruler 100 4 Stapler ﺩﺑﺎﺳﺔ 50 5 pen 5000 M-H ITEMS-Table O/p ID Item-Name Qty 1 Book 500 2 Note-book 1500 3 Ruler 100 4 Stapler ﺩﺑﺎﺳﺔ 50 5 pen 5000

ITEMS-Table ID Item-Name Ex 1 : Q 1 Select Item-Name, Qty From ITEMS-Table Where Qty<=100; Results of Q 1 M-H Ruler 100 Stapler ﺩﺑﺎﺳﺔ 50 O/p Qty 1 Book 500 2 Note-book 1500 3 Ruler 100 4 Stapler ﺩﺑﺎﺳﺔ 50 5 pen 5000

Student – score – table Q 2 Select St ID, St Name , Subject 2 score; From St – sc – table Where St ID =80; Results of Q 2 80 ﻣﺤﻤﺪ ﺣﺴﻴﻦ 40 St – sc – table O/p St ID St Name Subject 1 score Subject 2 score 50 ﻣﺤﻤﺪ ﻋﻠﻲ 70 80 60 ﺣﺴﻦ ﻣﺤﻤﺪ 50 60 70 ﻋﻠﻲ ﻋﻠﻴﻮﺓ 80 15 80 ﻣﺤﻤﺪ ﺣﺴﻴﻦ 95 40 90 ﺍﻧﺲ ﺍﻟﻤﺎﻟﻜﻲ 65 20 200 ﻣﺤﻤﺪ ﺯﻛﺮﻳﺎ 90 90 250 ﺳﻤﻴﺮ ﺍﻧﺴﻲ 85 100

ITEMS-Table ID Item-Name Ex 2 : Q 1 Select Item-Name, Qty From ITEMS-Table Where Qty>1000; Results of Q 1 Note-book 1500 pen 5000 M-H O/p Qty 1 Book 500 2 Note-book 1500 3 Ruler 100 4 Stapler ﺩﺑﺎﺳﺔ 50 5 pen 5000

Ex 1 : Q 1 Select Item-Name, Qty From ITEMS-Table Where ( (Qty>1000) AND (P-no=2) ); Results of Q 1 ITEMS-Table Note-book M-H 1500 O/p ID Item-Name Qty 1 Book 500 2 Note-book 1500 3 Ruler 100 4 Stapler ﺩﺑﺎﺳﺔ 50 5 pen 5000

2 -1 ﻻﺧﺘﻴﺎﺭ ﺍﻟﻄﻠﺒﺔ ﺍﻟﺤﺎﺻﻠﻴﻦ ﻋﻠﻲ ﺩﺭﺟﺔ ﺍﻻﻣﺘﻴﺎﺯ ﻓﻲ ﺍﻟﻤﺎﺩﺓ Student – score – table Q 2 Select St ID, St Name From St – sc – table Where ( (Subject 1 score=80) AND (Subject 2 score>=85) ); Results of Q 2 Null Results O/p St – sc – table St ID St Name Subject 1 score Subject 2 score 50 ﻣﺤﻤﺪ ﻋﻠﻲ 70 80 60 ﺣﺴﻦ ﻣﺤﻤﺪ 50 60 70 ﻋﻠﻲ ﻋﻠﻴﻮﺓ 80 15 80 ﻣﺤﻤﺪ ﺣﺴﻴﻦ 95 40 90 ﺍﻧﺲ ﺍﻟﻤﺎﻟﻜﻲ 65 20 200 ﻣﺤﻤﺪ ﺯﻛﺮﻳﺎ 90 90 250 ﺳﻤﻴﺮ ﺍﻧﺴﻲ 85 100

Q 4 Select Item-Name, Qty From ITEMS-Table Where NOT ( (Qty>1000) AND (P-no=2) ); ITEMS-Table ﺍﺷﻮﻑ ﺍﻳﻪ ﺍﻟﻠﻲ ﺑﻴﺤﻘﻘﻪ ﺍﻫﺬﺍ ﺍﻟﺸﺮﻁ ﻭﺍﺟﻴﺐ ﻋﻜﺴﻪ Results of Q 4 2 ﻛﻠﻪ ﻣﺎﻋﺪ ﺭﻗﻢ ID Item-Name Qty 1 Book 500 3 Ruler 100 4 Stapler ﺩﺑﺎﺳﺔ 50 5 pen 5000 M-H O/p 1 Book 500 2 Note-book 1500 3 Ruler 100 4 Stapler ﺩﺑﺎﺳﺔ 50 5 pen 5000

-: BETWEEN ﻣﻊ select – statement ﺇﺳﺘﺨﺪﺍﻡ -: ﺭﺍﺑﻌ ﻭ ﺗﺴﺘﺨﺪﻡ ﻟﺘﺴﻬﻴﻞ ﺍﻟﺒﺤﺚ ﻓﻲ ﻧﻄﺎﻕ)ﻣﺪﻱ( ﻣﻌﻴﻦ Ex : from Item-table use select statement to identify items quantity , Qty, which is between 100 and 1000 ID Item-Name Qty Ex 1 : 1 Book 500 2 Note-book 1500 Q 1 3 Ruler Stapler ﺩﺑﺎﺳﺔ 4 Select Item-Name, Qty 5 pen From ITEMS-Table Where Qty Between 100 AND 1000; Results of Q 1 Book 500 O/p M-H 100 50 5000

Q 4 Select Item-Name, Qty From ITEMS-Table Where NOT (Qty Between 100 AND 1000); ID Item-Name Qty Results of Q 4 1 Book 500 2 Note-book 1500 3 Ruler 100 Stapler ﺩﺑﺎﺳﺔ 50 4 Stapler ﺩﺑﺎﺳﺔ 50 pen 5000 5 pen 5000 M-H O/p

-: IN ﻣﻊ select – statement ﺇﺳﺘﺨﺪﺍﻡ -: ﺧﺎﻣﺴ -: ﻭ ﺗﺴﺘﺨﺪﻡ ﻟﺘﺴﻬﻴﻞ ﺍﻟﺒﺤﺚ ﻋﻦ ﻗﻴﻢ ﻣﺘﻔﺮﻗﺔ Ex : from Item-table use select statement to identify items quantity , Qty, and item-name where p-no=2, 4, 5 Ex 1 : Q 1 ITEMS-Table Select Item-Name, Qty ID Item-Name Qty From ITEMS-Table 1 Book 500 Where p-no IN (2, 4, 5); 2 Note-book 1500 Results of Q 1 3 Ruler 100 Note-book 1500 Stapler ﺩﺑﺎﺳﺔ 50 pen 5000 O/p 4 Stapler ﺩﺑﺎﺳﺔ 50 5 pen 5000 M-H

Q 2 Select MIN (Qty) From ITEMS-Table Where p-no IN (2, 4, 5); Results of Q 2 50 M-H O/p ITEMS-Table ID Item-Name Qty 1 Book 500 2 Note-book 1500 3 Ruler 100 4 Stapler ﺩﺑﺎﺳﺔ 50 5 Pen 5000 6 Note-book 10 7 Ruler 500

Q 3 Select AX (Qty) From ITEMS-Table; Results of Q 3 5000 M-H O/p

Q 4 Select Count (Qty) From ITEMS-Table Where Item-Name = ruler; Results of Q 4 2 M-H ITEMS-Table ID Item-Name Qty 1 Book 500 2 Note-book 1500 3 Ruler 100 4 Stapler ﺩﺑﺎﺳﺔ 50 5 Pen 5000 6 Note-book 10 7 Ruler 500

Q 3 select count * From EMP-table Where NOT CITY=‘ CAIRO ’ ; ﺍﻟﻨﺘﻴﺠﺔ ﻳﺸﻮﻑ ﺍﻟﻘﺎﻫﺮﺓ ﻭﻳﺠﻴﺐ ﻋﻜﺴﻬﺎ Results of Q 2 2 EMP-table EMP- ID EMP-Name CITY 1 ﻣﺤﻤﺪ ﻋﻠﻲ CAIRO 2 ﺣﺴﻴﻦ CIZA 3 ﻣﺎﺟﺪ ALEX

Q 4 Select count (DISTINCT SUBJ 1 -SCORE) From ST – SUBJ -TABLE; ST – SUBJ -TABLE Results of Q 4 3 ST- ID Name SUBJ 1– SCORE SUBJ 2– SCORE 1 ﻣﺤﻤﺪ ﻋﻠﻲ E 1 E 2 ﺣﺴﻴﻦ G 2 VG 3 ﻣﺎﺟﺪ VG 3 G 4 ﻋﻠﻲ E G ﻻ ﻣﻜﺮﺭﺓ ﺗﺤﺴﺐ

Q 5 Select Item-Name, Qty From ITEMS-Table Where Qty Between 100 AND 1000; Results of Q 5 ID Item-Name book 500 Ruler 100 M-H O/p Qty 1 Book 500 2 Note-book 1500 3 Ruler 100 4 Stapler ﺩﺑﺎﺳﺔ 50 5 pen 5000

Q 5 Select Item-Name, Qty From ITEMS-Table Where Qty Not Between 100 AND 1000; Results of Q 5 ID Item-Name Note-book 1500 Stapler ﺩﺑﺎﺳﺔ 50 pen 5000 M-H O/p Qty 1 Book 500 2 Note-book 1500 3 Ruler 100 4 Stapler ﺩﺑﺎﺳﺔ 50 5 pen 5000

Q 6 Select From Where Q 7 Select From Where * ITEMS-Table Item-Name like ‘ - - - e r ’ ; 3 Ruler 100 * ITEMS-Table Item-Name like ‘ % book ’ ; 1 Book 500 2 Note-book 1500

Q 15 Select * From ITEMS-Table Order by up DESC; Result : - ﻧﻔﺲ ﺗﺮﺗﻴﺐ ﺍﻟﺠﺪﻭﻝ ﻭﻟﻜﻦ ﻣﻌﻜﻮﺱ

ﺑﻨﺎﺀ ﻋﻠﻲ ﺭﻗﻢ ﺍﻟﻌﻤﻮﺩ ﺍﻟﻨﺴﺒﻲ ﻓﻲ ﺟﻤﻠﺔ 1 2 Q 16 Select ID , Qty From ITEMS-Table Order by 1 , 2 DESC; Result : ID Qty 03 600 02 500 01 100 ﻛﻤﺎ ﻳﻤﻜﻦ ﺗﺮﺗﻴﺐ ﺍﻟﺠﺪﻭﻝ ﺍﻟﻨﺎﺗﺞ Q 16 ﻣﺜﻞ SELECT ID Item-Name up Qty 01 Book 1 15. 5 100 02 Ruler 3. 5 500 03 pen 6. 0 600

Q 17 Select From Where Order ID , Qty ITEMS-Table Qty >= 500 by ID; 02 500 03 600 ID Item-Name up Qty 01 Book 1 15. 5 100 02 Ruler 3. 5 500 03 pen 6. 0 600