Practice Exercises SQL (2) CREATE TABLE/CREATE VIEW/ALTER TABLE For the fo o!
!i"# exercises the co ectio" "a$e is% AC&E A"' here is the ER( of the 'ata)ase%
*+
Create a CAR ta) e i" the co ectio" AC&E !hich has% ,CarNo (PK) - &a.e- &o'e - /ear- /ears0 '- Co or- 1i o$eters 2 A fie 's are $a"'ator3 exce4t Co or+ The o" 3 acce4ta) e 5a 6es for &a.e are 7809(A:- 7T0/0TA:- 79ISSA9: The 'ata)ase $6st re;ect a"3 ro! !here the 1i o$eters is #reater tha" 2<=== > /ears0 ' Create a" 0W9ER Ta) e i" co ectio" AC&E !hich has , OwnerID (PK)- 0"a$e- 04ho"e- (ri5ersLice"se 2 04ho"e is a" o4tio"a fie '+ Create a" 0W9ER@CAR ta) e !itho6t the Forei#" 1e3s !hich has% , OwnerID(PK), CarNo (PK)- Price 2 A'' the F0REIB9 1E/ that !i re ate the 0W9ER@CAR ta) e to the 0W9ER ta) e+ A'' the F0REIB9 1E/ that !i re ate the 0W9ER@CAR ta) e to the CAR ta) e+
2+
?+
A+ <+
C+ D+ E+
&a.e s6re that the 'ata)ase !i "ot a o! the 5a 6e for (ri5ersLice"se to )e i"serte' i"to a "e! ro! if that 5a 6e has a rea'3 )ee) 6se' i" a"other ro!+ A'' a" 0a''ress attri)6te to the 0W9ER ta) e+ It is ?= characters !i'e+ Create a VIEW ca e' 0W9ERS8IP that !i a44ear to the 6ser as the fo o!i"# ta) e% , 0!"erI(- Car9o- &a.e- /ear- Price 2 Re$o5e the Co or co 6$" fro$ the CAR ta) e+ Re$o5e a the 'ata a"' ta) es fro$ the AC&E co ectio"+
F+ *=+
ANSWERS using the Navigator Run an SQ S!ri"t# ($he answers are s%ight%& 'i((erent on the green s!reen#) *+ Create a CAR ta) e i" the co ectio" AC&E !hich has% ,CarNo (PK) - &a.e- &o'e - /ear- /ears0 '- Co or- 1i o$eters 2 A fie 's are $a"'ator3 exce4t Co or+ The o" 3 acce4ta) e 5a 6es for &a.e are 7809(A:- 7T0/0TA:- 79ISSA9: The 'ata)ase $6st re;ect a"3 ro! !here the 1i o$eters is #reater tha" 2<=== > /ears0 ' CREATE TABLE [Link] (CarNo CHAR(5), Make CHAR(15) NOT NULL, Model CHAR(15) NOT NULL, Year DECIMAL(2,0) NOT NULL, Year Old DECIMAL(2,0) NOT NULL, Color CHAR(15), !"lo#e$er DECIMAL(%,1) NOT NULL, CON&TRAINT [Link]'No'(! (RIMARY !EY (CarNo), CON&TRAINT [Link]'C! CHEC! (Make IN ()HONDA),)TOYOTA),)NI&&AN))), CON&TRAINT ACME.!"lo#e$er 'C! CHEC! (!"lo#e$er *+ 2500 , Year Old) )-
2+ Create a" 0W9ER Ta) e i" co ectio" AC&E !hich has , OwnerID (PK)- 0"a$e- 04ho"e- (ri5ersLice"se 2 04ho"e is a" o4tio"a fie '+ CREATE TABLE [Link] (O/0erID CHAR(5), ONa#e CHAR(15) NOT NULL, O(1o0e CHAR(15) , Dr"2er L"3e0 e DECIMAL(2,0) NOT NULL, CON&TRAINT ACME.O/0erID'(! (RIMARY !EY (O/0erID) )?+ Create a" 0W9ER@CAR ta) e !itho6t the Forei#" 1e3s !hich has% , OwnerID(PK), CarNo (PK)- Price 2 CREATE TABLE [Link]'CAR (O/0erID CHAR(5), CarNo CHAR(5), (r"3e DECIMAL(%,1) NOT NULL, CON&TRAINT [Link]'CAR'(! (RIMARY !EY (O/0erID, CarNo) )A+ A'' the F0REIB9 1E/ that !i re ate the 0W9ER@CAR ta) e to the 0W9ER ta) e+ ALTER TABLE [Link]'CAR ADD CON&TRAINT [Link]'CAR'TO'[Link]'4! 4OREI5N !EY (O/0erID) RE4ERENCE& [Link].O/0erID<+ A'' the F0REIB9 1E/ that !i re ate the 0W9ER@CAR ta) e to the CAR ta) e+ ALTER TABLE [Link]'CAR ADD CON&TRAINT [Link]'CAR'TO'CAR'4! 4OREI5N !EY (CarNo) RE4ERENCE& [Link](CarNo)-
C+ &a.e s6re that the 'ata)ase !i "ot a o! the 5a 6e for (ri5ersLice"se to )e i"serte' i"to a "e! ro! if that 5a 6e has a rea'3 )ee" 6se' i" a"other ro!+ ALTER TABLE [Link] ADD CON&TRAINT [Link]"2er L"3e0 e'UN UNI6UE (Dr"2er L"3e0 e)D+ A'' a" 0a''ress attri)6te to the 0W9ER ta) e+ It is ?= characters !i'e+ ALTER TABLE [Link] ADD COLUMN Oaddre CHAR(70)E+ Create a VIEW ca e' 0W9ERS8IP that !i a44ear to the 6ser as the fo o!i"# ta) e% , 0!"erI(- Car9o- &a.e- /ear- Price 2 CREATE 8IE. [Link]&HI( a (&ELECT o.O/0erID, [Link], [Link], [Link], o3.(r"3e 4ROM [Link] o, [Link] 3, [Link]'CAR o3 .HERE [Link] + [Link] AND o3.O/0erID + o.O/0erID)F+ Re$o5e the Co or co 6$" fro$ the CAR ta) e+ ALTER TABLE [Link] DRO( COLUMN Color*=+ Re$o5e a the 'ata a"' ta) es fro$ the AC&E co ectio"+ No$e9 : e $/o $a$e#e0$ 9 DRO( COLLECTION ACMECREATE COLLECTION ACME-