COSC 2307 Assignment 3   
Start early—this programming assignment requires a fair amount of work to use ISQLPLUS.  
Part 1: Queries  
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).  
2. You are asked to create 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)?  
3. What is the length of values defined for city column. Modify the length of “city” in the table “suppliers”(5 marks).  
4. 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.  
5. 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 larger than 0.   
Part 2: More Queries (using the S-SP-P tables) (each 6 marks)  
1. How many different kinds of parts do we have 2. Give the details of suppliers in Paris 3. Give details of shipments where the quantity shipped is less than 300 4. Give details of parts stored in London 5. Give the names and numbers of London based suppliers 6. Give the names of red Parts 7. Give the part number for parts weighting less than 17 8. Give details of parts supplied by S1
9. Give the color of parts supplied by S2 10. Where are the suppliers of P2 located 11. 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 script should be sufficient to convince us that your commands run successfully.    
Submission Guideline:  1. Upload your submission on CMS

    • 7 years ago
    Complete A++ Solution

    Purchase the answer to view it

    • attachment