Question

Homework Assignment #9: Selects On assignment #8 that we started in class and you finished on your own, you created three tables Product Manufacturer and Stock For this assignment you will be working with these same tables Please complete the following tasks and submit in word file or notepad. 1. First insert tuples into two of the tables Write and execute the SQL to insert three records ProductID 844,555 and 645 to the Product table (see below) Write and execute the SQL to insert the one record to the Stock table (see below) ManufacturerlD 1 Products Product Name 101 Keyboard 102 Printer 103 iPad version 2.5 509 Apple Watch 876 MacBook Pro 844 Bluetooth Stereo Turntable 555 Keyboard 645 Printer Product Product Description Ergonomic keyboard with rest pad HP Model 3456 5x7 inch monitor with stylus Series 5 Pink 32G memory 8 core intel processor Turntable system to play vinyls Wireless keyboard Toshiba Model 3456 Manufacturer ManufacturerName Manufacturer Dell Apple Acer Toshiba Stock Product ID Quantity 101 100 400 300 500 6.00 300 2. Write and execute the SQL select statements to do the following selections of ALL TUPLES IN A TABLE a Select all (attributes) tuples in Product b. Select all (attributes) tuples in Manufacturer c. Select all (attributes) tuples in Stock 3. Write and execute the SQL select statements to do the following selections of ONLY SOME TUPLED IN A TABLE a. Select Manufacturer ID - 1 tuples in Product table b. Select Product of Products that have a quantity of 300 4. Write and execute the SQL select statements that select attributes (not all) based on a condition. a Select Products for items with Manufacturer of 4 b. Select Productiame for items with Manufacturer of 1. 5. Write and execute the SOL select statements that SELECT with and without DISTINCT modifier a. Select ProductName and Manufacturer from Product Select Product Name and Manufacturer from Product with the Distinct modifier Explain the difference in the results from a and

Answer 1

1. (a)

Insert into Product values ( 844 ,'Bluetooth Stereo Turntable', 'Turntable system to play vinyls',4)

Insert into Product values ( 555,'Keyboard', 'Wireless keyboard',2)

Insert into Product values ( 645,'Printer', 'Thoshiba model 3456',4)

(b) Insert into Stock values( 844,300)

2.

(a) select * from Product

(b) select * from Manufacturer

(b) select * from Stock

3.

(a) select * from Product where ManufacturerID=1

(b) select productID from Stocks where Quantity=300

4

(a) select ProductID from Product where ManufacturerID=4

(b) select ProductName from Product where ManufacturerID=1

5.

(a) select ProductName, ManufacturerID from Product

(b) Select DISINCT ProductName, ManufacturerID from Product

(c) In the result of (a) all the product names and manufacturer ID are obtained with duplicate values, But in result of (b) the duplicates are eliminated, only distinct values are displayed. But in product table the productName attribute has 6 distinct elements and ManufacturerID has 4 distinct elements, this may give some error in the result.