Instructions: Create (compose) five queries according to their requirements (see
ID: 3598987 • Letter: I
Question
Instructions: Create (compose) five queries according to their requirements (see below).
5. Compose a query to insert a new customer with the following values: Customer Name: Lakeshore Furniture; Customer Address: 300 East Lakeshore Drive; City: Chicago; State: IL; Postal Code: 60602.
6. Compose a query to update the postal code of Lakeshore Furniture from 60602 to 60607. Assume that you know the customer name is Lakeshore Furniture but you do not know its Customer ID.
7. Compose a query to delete all customers who are located in Illinois (IL). B. Multiple Table Queries
8. Compose an SQL statement that will show all customers who purchased an Entertainment Center or a Dining Table in 2011. In the result table, show the customer name, city, state, order ID, order date, and product name.
9. Compose a query to calculate the total cost of raw materials for each finish product. In the result table, we would like to see Product ID, Product Name, and its total cost of raw materials. Sort the results by the total cost of raw materials in descending order.
10. Compose an SQL statement to generate a list of two least expensive vendors (suppliers) for each raw material. In the result table, show the following columns: material ID, material description, vendor ID, vendor name, and the supplier's unit price. Sort the result table by material ID and supplier’s unit price in ascending order. Note: If a raw material has only one vendor (supplier), that supplier and its unit price for the raw material should also be in the result (output) table [hint: use a correlated subquery].
Explanation / Answer
Ans 5)
INSERT INTO table_name (Customer Name,Customer Address,City,State,Postal Code)
VALUES ('Lakeshore Furniture', '300 East Lakeshore Drive', 'Chicago', 'IL', '60602'); // Assuming the Column names as the above mentioned
INSERT INTO Customer
VALUES ('Lakeshore Furniture','300 East Lakeshore Drive','Chicago','IL', '60602'); // Inserting Directly
Ans 6)
UPDATE Customer
SET Postal Code= '60607'
WHERE Customer Name='Lakeshore Furniture'; //Updating where Customer Name is 'Lakeshore Furniture'
Ans 7)
DELETE FROM Customer
WHERE State='Illinois'; //delete all customers who are located in Illinois
Ans 8)
Need more information about table. I think this is not the same table as you have mentioned in Question 5.