1. There is a table called USER_TABLES that stores the information about each user's table. Use the select statement to make sure your tables are all created from assignment 2 (5 marks).

 

1. I want a new table called "part2" which has the same column definitions as the table "part" you created in assignment2. However, you should not copy the data contained in the course. How do you create a copy of the "part" table without copying its data contents (6 marks)?

 

1. What is the length of values defined for city column. Modify the length of "city" in the table "suppliers"(5 marks).

 

1. Are there any constraints defined for the above tables (table suppliers (S), supplier-part (SP), parts? (P) In order to find out, try to search through the USER_CONSTRAINTS table.

 

1. Add the following constraints for the tables (each 3 marks, total 18 marks):

• s# is the primary key for table"S"
• s#, p# is the primary key for the table "SP"

-p# in table "P" should be unique

• the primary key of the "S" table is posted as foreign key in the tables "SP"
• SNAME in table "S" may not be NULL
• the columns "qty" in table"SP", "weight" in table"P" should be largerthan 0.
  1. How many different kinds of parts do we have

 

 

Part 2: More Queries (using the S-SP-P tables)(each 6 marks)

 

1. Give the details of suppliers in Lodon
2. Give details of shipments where the quantity shipped is less than200
3. Give details of partsstored in London
4. Give the names and numbers of London based suppliers
5. Give the names of "blue" Parts
6. Give the part number for parts weighting less than 17

 

1. Give details of parts supplied by S1
2. Give the color of parts supplied by S2
3. Where are the suppliers of P2 located
4. Get supplier numbers for suppliers who supply parts located in either Paris or London

 

 

Turn in a copy of all of your SQL commands, along with a script illustrating their execution (by using paste your terminal output and save it in a file or any other way). Your scripts should be sufficient to convince us that your commands run successfully.

 

 

S#	SNAME	STATUS	CITY
S1	SMITH	20	LONDON
S2	JONES	10	PARIS
S3	BLAKE	30	PARIS
S4	CLARKE	20	LONDON
S5	ADAMS	30	ATHENS

Supplier (S)

 

 

 

S#	P#	Qty
S1	P1	300
S1	P2	200
S1	P3	400
S1	P4	200
S1	P5	100
S1	P6	100
S2	P1	300
S2	P2	400
S3	P2	200
S4	P2	200
S4	P4	300
S4	P5	400

SP

 

 

 

P#	Pname	Colour	Weight	City
P1	BOLT	blue	10	London
P2	NUT	black	5	Paris
P3	WASHER	black	2	London
P4	NUT	red	6	Athens
P5	CAM	red	8	Paris
P6	NUT	blue	5	London