Scribd
Upload
16 views2 pages

MySQL Assignment

The assignment focuses on practicing SQL commands for data manipulation in Microsoft SQL Server using a sample Sales database. Tasks include filtering records by category and price, sorting sales data, using logical operators for specific queries, and aggregating data to calculate total quantities sold. A bonus challenge involves modifying the aggregation to include total revenue for products exceeding a specified threshold.

Uploaded by

alansiju010
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
16 views2 pages

MySQL Assignment

The assignment focuses on practicing SQL commands for data manipulation in Microsoft SQL Server using a sample Sales database. Tasks include filtering records by category and price, sorting sales data, using logical operators for specific queries, and aggregating data to calculate total quantities sold. A bonus challenge involves modifying the aggregation to include total revenue for products exceeding a specified threshold.

Uploaded by

alansiju010
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Assignment: SQL Querying and Data Manipulation

Objective:

The purpose of this assignment is to practice using SQL commands to filter, sort, and aggregate
data efficiently in Microsoft SQL Server. Complete the following tasks using a sample database.

Database Schema:

Consider the following table Sales:

SaleID CustomerName Product Category SaleDate Quantity Price

1 John Doe Laptop Electronics 2024-02-10 2 1200

2 Jane Smith Smartphone Electronics 2024-02-15 1 800

3 Alice Brown Chair Furniture 2024-03-05 4 150

4 Bob White Laptop Electronics 2024-03-10 1 1100

5 Charlie Lee Table Furniture 2024-03-15 2 500

6 Eve Adams Printer Electronics 2024-04-01 1 300

Tasks:

1. Filtering Data with WHERE

Write a SQL query to retrieve all records from the Sales table where:

• The category is 'Electronics'.

• The sale price is greater than 500.

2. Sorting Data with ORDER BY

Write a SQL query to retrieve all sales records sorted by:

• SaleDate in descending order.

• If two sales have the same date, sort them by Price in ascending order.

3. Using SQL Operators: AND, OR, NOT

Write a SQL query to:

• Retrieve records where Category is either 'Electronics' OR 'Furniture' but NOT 'Chair'.

• The sale price should be greater than 300 AND quantity should be greater than 1.

4. Aggregating Data with GROUP BY and HAVING

Write a SQL query to calculate:

• The total quantity sold per Product.


• Display only those products where the total quantity sold is greater than 2.

Submission Guidelines:

• Submit your SQL queries in a text file or SQL script.

• Provide comments in your queries to explain the logic used.

• Test your queries before submission to ensure correctness.

Bonus Challenge (Optional):

• Modify the GROUP BY query to include total revenue for each product (Quantity * Price)
and filter products with revenue greater than 1000.

You might also like