There is a table called USER_TABLES that stores the information about each user'stable. Use the select statement
Fantastic news! We've Found the answer you've been seeking!
Question:
- 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).
- 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)?
- What is the length of values defined for city column. Modify the length of "city" in the table "suppliers"(5 marks).
- 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.
- 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.
- How many different kinds of parts do we have
Part 2: More Queries (using the S-SP-P tables)(each 6 marks)
- Give the details of suppliers in Lodon
- Give details of shipments where the quantity shipped is less than200
- Give details of partsstored in London
- Give the names and numbers of London based suppliers
- Give the names of "blue" Parts
- Give the part number for parts weighting less than 17
- Give details of parts supplied by S1
- Give the color of parts supplied by S2
- Where are the suppliers of P2 located
- 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 |
Related Book For
Modern Systems Analysis And Design
ISBN: 9780134204925
8th Edition
Authors: Joseph Valacich, Joey George
Posted Date: