Question

For this assignment you have to create tables as reviewed in class Manufacturer table, Product table and Stock table. The code is in Week 7 PPT lecture. You are to add update as needed to make the tables the same as below and match Product, Manufacturer and Stock as below.

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 and make sure the contents of the tables match below.  

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).  

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 ProductID of Products that have a quantity of 300.

c. Select ProductID of Products that have quantity of 300 and less.

4. Write and execute the SQL select statements that select attributes (not all) based on a condition.

a. Select ProductID for items with ManufacturerID of 4.

b. Select ProductName for items with ManufacturerID of 1.

c. Select Products with the productName 'Keyboard'

5. Write and execute the SQL select statements that SELECT with and without DISTINCT modifier

a. Select ProductName and ManufacturerID from Product

b. Select Product Name and ManufacturerID from Product with the Distinct modifier.

c. Explain the difference in the results from a and b.

Answer 1

Please rate the answer. Feel free to contact me in case of any more clarifications required.

Note: I don't have the stock table structure. So I have not given the insert sql for the stock table.

Please comment me the stock table columns. I will add the insert statement.

Question_1. First insert tuples into two of the tables and make sure the contents of the tables match below.  

        Write and execute the SQL to insert three records ProductID 844, 555 and 645 to the Product table (see below)
        Answer => INSERT INTO Product (Productid,manufactureid,quantity,name) VALUES (844,112,20,'Monitor');  
                          INSERT INTO Product (Productid,manufactureid,quantity,name) VALUES (555,122,25,'Mouse');
                          INSERT INTO Product (Productid,manufactureid,quantity,name) VALUES (645,132,30,'Keyboard');

        Write and execute the SQL to insert the one record to the Stock table (see below).  
        Answer => 
 

Question_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
        Answer => SELECT * FROM PRODUCT;
        
        b. Select all (attributes) tuples in Manufacturer
        Answer => SELECT * FROM MANUFACTURER;

        c. Select all (attributes) tuples in Stock
        Answer => SELECT * FROM STOCK;

Question_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.
        Answer => SELECT * FROM PRODUCT WHERE MANUFACTUREID = 1;

        b. Select ProductID of Products that have a quantity of 300.
        Answer => SELECT ProductID FROM PRODUCT WHERE QUANTITY = 300;

        c. Select ProductID of Products that have quantity of 300 and less.
        Answer => SELECT ProductID FROM PRODUCT WHERE QUANTITY <= 300;

Question_4. Write and execute the SQL select statements that select attributes (not all) based on a condition.
        a. Select ProductID for items with manufactureid of 4.
        Answer => SELECT ProductID FROM PRODUCT WHERE MANUFACTUREID = 4;

        b. Select ProductName for items with manufactureid of 1.
        Answer => SELECT ProductID FROM PRODUCT WHERE MANUFACTUREID = 1;

        c. Select Products with the productName 'Keyboard'
        Answer => SELECT * FROM PRODUCT WHERE name = 'Keyboard';

Question_5. Write and execute the SQL select statements that SELECT with and without DISTINCT modifier
        a. Select ProductName and manufactureid from Product
        Answer => SELECT Name as "Product Name", MANUFACTUREID  FROM PRODUCT;

        b. Select Product Name and manufactureid from Product with the Distinct modifier.
        Answer => SELECT distinct Name as "Product Name", MANUFACTUREID  FROM PRODUCT;       

        c. Explain the difference in the results from a and b.
           DISTINCT will remove those rows where all the selected fields are identical. 
           So in Question_5 a, the result will show all the records from the table PRODUCT
           Question_5 b, will only show the distinct records from the product i.e. the records are not identical..

The explanation for Question_5.