Five introductory-level SQL assignments that cover various basic concepts:
**Assignment 1: Select Statements**
Write SQL queries to retrieve information from the "employees" table. Assume the table has columns: `employee_id`, `first_name`, `last_name`, `job_title`, and `salary`.
CREATE TABLE employees (
employee_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
job_title VARCHAR(50),
salary INT
);
INSERT INTO employees VALUES
(1, 'Rahul', 'Kumar', 'Manager', 60000),
(2, 'Priya', 'Sharma', 'Developer', 50000),
(3, 'Amit', 'Singh', 'Analyst', 45000),
(4, 'Neha', 'Patel', 'Designer', 55000),
(5, 'Raj', 'Verma', 'Developer', 52000);
a. Retrieve all columns for all employees.
b. Retrieve only the `first_name` and `last_name` columns for all employees.
c. Retrieve unique job titles from the table.
d. Retrieve employees with a salary greater than 50000.
**Assignment 2: Filtering and Sorting**
Consider a "products" table with columns: `product_id`, `product_name`, `price`, and `category`.
CREATE TABLE products (
product_id INT,
product_name VARCHAR(50),
price DECIMAL(10, 2),
category VARCHAR(50)
);
INSERT INTO products VALUES
(1, 'Laptop', 1200.00, 'Electronics'),
(2, 'Smartphone', 500.50, 'Electronics'),
(3, 'Desk Chair', 89.99, 'Furniture'),
(4, 'Coffee Maker', 45.00, 'Appliances'),
(5, 'Headphones', 79.99, 'Electronics'),
(6, 'Bookshelf', 120.00, 'Furniture'),
(7, 'Toaster', 29.99, 'Appliances');
a. Retrieve all columns for products in the "Electronics" category.
b. Retrieve the product names and prices for products with a price less than 100, sorted by price in ascending order.
c. Retrieve the three most expensive products.
d. Retrieve products with a price between 50 and 200.
**Assignment 3: Aggregation Functions**
CREATE TABLE orders (
order_id INT,
customer_id INT,
order_date DATE,
total_amount DECIMAL(10, 2)
);
INSERT INTO orders VALUES
(1, 101, '01-Oct-2024', 150.00),
(2, 102, '12-Jan-2024', 220.50),
(3, 103, '15-Jan-2024', 100.00),
(4, 101, '18-Jan-2024', 75.99),
(5, 104, '20-Jan-2024', 300.00);
Assume a "orders" table with columns: `order_id`, `customer_id`, `order_date`, and `total_amount`.
a. Calculate the total number of orders.
b. Calculate the average total amount of orders.
c. Find the maximum total amount of an order.
d. Determine the number of orders placed by each customer.
**Assignment 4: Joins**
Consider two tables: "employees" with columns `employee_id`, `first_name`, `last_name`, and `department_id`, and "departments" with columns `department_id` and `department_name`.
CREATE TABLE employees (
employee_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
department_id INT
);
INSERT INTO employees VALUES
(1, 'Rahul', 'Kumar', 1),
(2, 'Priya', 'Sharma', 2),
(3, 'Amit', 'Singh', 1),
(4, 'Neha', 'Patel', 2),
(5, 'Raj', 'Verma', 3);
CREATE TABLE departments (
department_id INT,
department_name VARCHAR(50)
);
INSERT INTO departments VALUES
(1, 'IT'),
(2, 'Finance'),
(3, 'Marketing');
a. Retrieve a list of employees with their department names.
b. Find employees who do not belong to any department.
c. Retrieve the total number of employees in each department.
d. List department names along with the number of employees in each department.
**Assignment 5: Subqueries**
Assume a "orders" table with columns: `order_id`, `customer_id`, and `order_date`, and a "customers" table with columns: `customer_id` and `country`.
a. Retrieve orders placed by customers from the United States.
b. Find customers who have not placed any orders.
c. Retrieve customers from the same country who placed orders on the same day.
d. Find the order with the highest total amount.
These assignments cover a range of SQL concepts, including basic SELECT statements, filtering, sorting, aggregation functions, joins, and subqueries. Students can use these assignments to practice and reinforce their understanding of SQL fundamentals.
.png)
0 Comments