SQL Practice Prompts

Employee Database

• Create a database schema for an employee management system including tables for employees, departments, and positions.
• Populate the tables with sample data.
• Write SQL queries to:
  • Retrieve the list of all employees.
  • Find the employees who belong to a specific department.
  • Get the highest-paid employee.
  • Calculate the average salary in the company.

Online Store

• Design a database schema for an online store with tables for products, categories, customers, and orders.
• Populate the tables with sample data.
• Write SQL queries to:
  • Retrieve the list of products in a specific category.
  • Find the total revenue generated from orders.
  • Get the top-selling products.
  • Calculate the average order value.

Library Management System

• Create a database schema for a library management system with tables for books, authors, customers, and loans.
• Populate the tables with sample data.
• Write SQL queries to:
  • Retrieve the list of books by a specific author.
  • Find the books that are currently checked out.
  • Calculate the late fees for overdue books.
  • Get the most frequent library visitors.

Social Media Platform

• Design a database schema for a social media platform with tables for users, posts, comments, and likes.
• Populate the tables with sample data.
• Write SQL queries to:
  • Retrieve the posts made by a specific user.
  • Find the most liked posts.
  • Calculate the average number of comments per post.
  • Get the users who have commented on a particular post.

Financial Management System

• Create a database schema for a financial management system with tables for accounts, transactions, categories, and budgets.
• Populate the tables with sample data.
• Write SQL queries to:
  • Retrieve the transactions within a specific date range.
  • Find the total expenses in each category.
  • Calculate the remaining budget for each category.
  • Get the accounts with the highest balance.