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

Answers

1) problem

1)INSERT INTO Product (Productid,manufactureid,quantity,name) VALUES (844,112,20,'plastic');

2) INSERT INTO Product (Productid,manufactureid,quantity,name) VALUES (555,22,20,'steel');

3)INSERT INTO Product (Productid,manufactureid,quantity,name) VALUES (645,100,10,'carbon');

4)INSERT INTO stock (productid,quantity) VALUES(844,20);

2 problem

a) SELECT * FROM Product;

b) SELECT * FROM Manufacturer;

c) SELECT * FROM Stock;

3) problem

a) SELECT Productid from product WHERE manufactureid=1;

b) SELECT Productid from product WHERE quantity=300;

c) SELECT name from product WHERE quantity<=300;

4)problem

a) SELECT Productid from product WHERE manufactureid=4;

b) SELECT Productid from product WHERE manufactureid=1;

c) SELECT * from product WHERE name='keyboard';

5)problem

a) SELECT name,manufactureid from product;

b) SELECT DISTINCT name,manufacturerid from product;

c) As you can see above results, The distinct modifier will remove repeating tuples.