MySQL Assignment – 1

Basic Questions

  1. Write SQL to create a database named shop.
  2. Create a table products with columns: id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), price DECIMAL(8,2).
  3. Insert 5 sample records into products.
  4. Write SQL to update the price of product with id = 2 to 450.75.
  5. Delete the product with id = 3.
  6. Select all products where price is greater than 200.
  7. Create a table customers with fields cust_id INT AUTO_INCREMENT PRIMARY KEY, cust_name VARCHAR(50), city VARCHAR(50).
  8. Insert 3 sample customers into customers.
  9. Write SQL to display only distinct city names from customers.
  10. Write SQL to count total number of products in the products table.
  11. Write SQL to find all books whose author name ends with “n”.
  12. Select books ordered by price in ascending order.
  13. Select the top 3 most expensive books.
  14. Count how many members are in the members
  15. Write SQL to display only unique author names from books.
  16. Write SQL to find the maximum price of a book.
  17. Write SQL to find the minimum price of a book.
  18. Write SQL to calculate the average price of books.
  19. Add a new column email VARCHAR(100) to the members
  20. Update the email of the member with member_id = 1 to ram@example.com.

Intermediate Questions

  1. Create a table orders with fields: order_id INT AUTO_INCREMENT PRIMARY KEY, cust_id INT, prod_id INT, order_date DATE.
  2. Insert 3 sample orders (use cust_id and prod_id from above tables).
  3. Write SQL to join customers and orders to show customer name with their order_id.
  4. Write SQL to join orders and products to show product name and price with order_id.
  5. Write SQL to show total amount spent by each customer (use SUM).
  6. Write SQL to find the most expensive product.
  7. Add a new column email VARCHAR(100) to customers.
  8. Update the email of a specific customer.
  9. Write SQL to delete all orders placed before 2024-01-01.
  10. Write SQL to show top 2 highest priced products.
  11. Write SQL to show the list of customers who placed more than 2 orders.
  12. Write SQL to find the product with the second highest price.
  13. Write SQL to calculate the total number of orders placed in each month of 2024.
  14. Write SQL to display customer names along with the total number of distinct products they purchased.
  15. Write SQL to find customers who never placed an order.
  16. Write SQL to fetch all products that were ordered at least once (INNER JOIN with orders).
  17. Write SQL to find the average order amount for each customer.
  18. Write SQL to get the names of customers who ordered products priced above 500.
  19. Write SQL to display the highest order amount placed by each customer.
  20. Write SQL to select all orders along with product names, sorted by order_date descending.

Advanced Questions

  1. Write SQL to create a table employees with columns: emp_id INT AUTO_INCREMENT PRIMARY KEY, emp_name VARCHAR(100), dept_id INT, salary DECIMAL(10,2).
  2. Insert 4 employees into employees table.
  3. Create a departments table with dept_id INT PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(100) UNIQUE.
  4. Insert 3 sample departments.
  5. Add a foreign key dept_id in employees table referencing departments table.
  6. Write SQL to display employees with their department names.
  7. Write SQL to increase salary of employees in “Sales” department by 10%.
  8. Write SQL to delete all employees whose salary is less than 20000.
  9. Create a table payments with pay_id INT AUTO_INCREMENT PRIMARY KEY, emp_id INT, amount DECIMAL(10,2), pay_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP.
  10. Write SQL query to show total salary paid to each employee using GROUP BY.