SQL 2 -Assignment

PRACTICE QUESTIONS FOR SUB-MODULE 2

customersDownload
ordersDownload
productsDownload

Instructions for Practice Questions

To effectively practice SQL joins, use the provided dataset, which includes the following tables:

  • Customers (customer details)
  • Orders (order transactions)
  • Products (electronic product details)

Your Task:

  1. Use SQL queries to answer each practice question.
  2. Write and execute your SQL code using Microsoft SQL Server or any preferred SQL environment.
  3. Ensure your queries retrieve the correct data based on the given scenarios.
  4. If needed, modify or expand your queries to explore additional insights.

Reminder: Always reference the dataset structure to correctly use column names in your queries.

Happy Querying! 

QUESTIONS

1. Write an SQL query to create the Orders table with the following columns:

  • Order_ID (Primary Key, Integer)
  • Product_ID (Integer, Foreign Key referencing Products table)
  • Customer_ID (Integer, Foreign Key referencing Customers table)
  • Order_Date (Date)
  • Total_Amount (Decimal)
  • Quantity (Integer)
  • Status (VARCHAR, should allow values like ‘Shipped’, ‘Processing’, ‘Delivered’, ‘Cancelled’)

2. Insert the following new customer into the Customers table:

  • First Name: Sarah
  • Last Name: Williams
  • Email: sarah.w@email.com
  • Phone: 555-123-4567
  • City: Boston
  • State: MA
  • Country: USA

3. John Doe ordered a Smartphone, but he later changed his mind and wants a Laptop instead. Update the Orders table to reflect this change.

4. A customer cancelled their order. Remove the order with Order_ID = 105 from the Orders table.

5.  Why is it important to always use the WHERE clause when deleting or updating records? What happens if you execute:

DELETE FROM Orders;

EXTRACT THE SCRIPT OF YOUR QUERY AND UPLOAD.