SQL Assignments
Assignment 1: Product Table Operations
1. Create Product Table
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
category VARCHAR(100),
price DECIMAL(10, 2),
stock_quantity INT
);
Steps performed: Created a table named products with columns for product ID (primary
key), name, category, price, and stock quantity.
2. Insert Product Data
INSERT INTO products (product_id, product_name, category, price, stock_quantity)
VALUES
(1, 'Laptop Pro X', 'Electronics', 12000.00, 50),
(2, 'Mechanical Keyboard', 'Electronics', 1500.00, 150),
(3, 'Wireless Mouse', 'Electronics', 750.00, 200),
(4, 'Desk Chair Ergonomic', 'Furniture', 4500.00, 30),
(5, 'Coffee Table Glass', 'Furniture', 3000.00, 20),
(6, 'Smartphone Z', 'Electronics', 8500.00, 70),
(7, 'Bluetooth Speaker', 'Electronics', 2500.00, 100),
(8, 'Dining Table Set', 'Furniture', 15000.00, 15),
(9, 'External SSD 1TB', 'Electronics', 6000.00, 80),
(10, 'Bookcase Modern', 'Furniture', 7000.00, 25);
Steps performed: Inserted ten rows of sample product data into the products table.
3. Retrieve All Product Details
SELECT * FROM products;
Steps performed: Selected all columns and all rows from the products table.
4. Retrieve ID and Product Name
SELECT product_id, product_name FROM products;
Steps performed: Selected only the product_id and product_name columns from the
products table.
5. Update a Product
UPDATE products
SET price = 11500.00, stock_quantity = 45
WHERE product_id = 1;
Steps performed: Updated the price and stock_quantity for the product with product_id
1.
6. Delete a Product
DELETE FROM products
WHERE product_id = 5;
Steps performed: Deleted the row for the product with product_id 5 from the products
table.
Assignment 2: Product Data Analysis
1. Find Products Whose Category is Electronics
SELECT * FROM products
WHERE category = 'Electronics';
Steps performed: Selected all products where the category is 'Electronics'.
2. Find All Products Whose Price Range is Between 5000 and 10000
SELECT * FROM products
WHERE price BETWEEN 5000 AND 10000;
Steps performed: Selected all products where the price is between 5000 and 10000
(inclusive).
3. Display Name and Price From Product Table
SELECT product_name, price FROM products;
Steps performed: Selected the product_name and price columns for all products.
4. Display All Electronic Category Items Which Has Price More Than 2000
SELECT * FROM products
WHERE category = 'Electronics' AND price > 2000;
Steps performed: Selected all products that are in the 'Electronics' category and have a
price greater than 2000.
5. Sort the Product Based on Price in Descending Order
SELECT * FROM products
ORDER BY price DESC;
Steps performed: Selected all products and ordered them by price in descending order.
6. Sort the Product Based on Name
SELECT * FROM products
ORDER BY product_name ASC;
Steps performed: Selected all products and ordered them by product_name in
ascending order (default).
7. Count of Product Based on Category
SELECT category, COUNT(product_id) AS product_count
FROM products
GROUP BY category;
Steps performed: Counted the number of products for each unique category and
displayed the category and its count.
8. Display All Products Except Electronics Category
SELECT * FROM products
WHERE category != 'Electronics';
Steps performed: Selected all products where the category is not 'Electronics'.
Assignment 3: Customer Table Operations
1. Create Customers Table
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE,
city VARCHAR(100),
region VARCHAR(100)
);
Steps performed: Created a table named customers with columns for customer ID
(primary key), name, email (unique), city, and region.
2. Insert Customer Data
INSERT INTO customers (customer_id, customer_name, email, city, region) VALUES
(101, 'Alice Smith', 'alice.s@[Link]', 'New York', 'North East'),
(102, 'Bob Johnson', 'bob.j@[Link]', 'Los Angeles', 'West'),
(103, 'Charlie Brown', 'charlie.b@[Link]', 'Chicago', 'Midwest'),
(104, 'Diana Prince', 'diana.p@[Link]', 'Houston', 'South'),
(105, 'Eve Adams', 'eve.a@[Link]', 'New York', 'North East'),
(106, 'Frank White', 'frank.w@[Link]', 'Miami', 'South'),
(107, 'Grace Lee', 'grace.l@[Link]', 'Seattle', 'West');
Steps performed: Inserted seven rows of sample customer data into the customers
table.
3. Retrieve All Columns from 'customers' Table
SELECT * FROM customers;
Steps performed: Selected all columns and all rows from the customers table.
4. Retrieve Only Customer Name and Email Address for Customers in a Specific
City
SELECT customer_name, email FROM customers
WHERE city = 'New York';
Steps performed: Selected the customer_name and email for customers whose city is
'New York'.
Assignment 4: Join Queries
1. Create Orders Table
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
total_amount DECIMAL(10, 2),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
Steps performed: Created a table named orders with columns for order ID (primary
key), customer ID (foreign key referencing customers table), order date, and total
amount.
2. Insert Order Data
INSERT INTO orders (order_id, customer_id, order_date, total_amount) VALUES
(1, 101, '2023-01-15', 250.00),
(2, 103, '2023-01-20', 500.00),
(3, 101, '2023-02-10', 120.50),
(4, 104, '2023-02-18', 75.00),
(5, 102, '2023-03-01', 300.00),
(6, 103, '2023-03-05', 150.00);
Steps performed: Inserted six rows of sample order data into the orders table.
3. Craft a Query Using an INNER JOIN to Combine 'orders' and 'customers' Tables
for Customers in a Specified Region
SELECT
c.customer_name,
[Link],
o.order_id,
o.order_date,
o.total_amount
FROM
customers c
INNER JOIN
orders o ON c.customer_id = o.customer_id
WHERE
[Link] = 'North East';
Steps performed: Performed an INNER JOIN between customers (aliased as c) and
orders (aliased as o) tables on customer_id. Filtered the results to show only customers
from the 'North East' region.
4. LEFT JOIN to Display All Customers Including Those Without Orders
SELECT
c.customer_name,
[Link],
o.order_id,
o.order_date,
o.total_amount
FROM
customers c
LEFT JOIN
orders o ON c.customer_id = o.customer_id;
Steps performed: Performed a LEFT JOIN between customers (aliased as c) and orders
(aliased as o) tables on customer_id. This query returns all customers, and their order
details if they have any; otherwise, order-related columns will be NULL.
Assignment 5: Subquery and UNION
1. Utilize a Subquery to Find Customers Who Have Placed Orders Above the
Average Order Value
SELECT
c.customer_name,
[Link]
FROM
customers c
INNER JOIN
orders o ON c.customer_id = o.customer_id
WHERE
o.total_amount > (SELECT AVG(total_amount) FROM orders);
Steps performed: Used an INNER JOIN to combine customers and orders. A subquery
was used in the WHERE clause to calculate the average total_amount from the orders
table. The outer query then selected customers whose individual order total_amount
was greater than this average.
2. Write a UNION Query to Combine Two SELECT Statements With the Same
Number of Columns
SELECT customer_id, customer_name, city FROM customers WHERE region = 'North
East'
UNION
SELECT customer_id, customer_name, city FROM customers WHERE city = 'Los
Angeles';
Steps performed: Combined two SELECT statements using the UNION operator. The
first SELECT retrieves customer_id, customer_name, and city for customers in the
'North East' region. The second SELECT retrieves the same columns for customers in
'Los Angeles'. UNION combines the distinct results from both queries.