Basic Level Questions

SQL Practice Exercises



SELECT Queries



  1. Select all columns from the employees table.
  2. Select only the first_name and last_name from customers.
  3. Select unique department values from employees.
  4. Select all orders placed in 2024.
  5. Select the top 10 customers by customer_id.

WHERE Clause



  1. Select users older than 30 years.
  2. Find products with price less than 100.
  3. Get employees in the "Marketing" department.
  4. Select customers from 'New York'.
  5. Find orders placed between '2023-01-01' and '2023-06-30'.

ORDER BY



  1. Get all products ordered by price descending.
  2. List employees ordered by join date.
  3. Show customers alphabetically.
  4. Order orders by order amount, highest first.
  5. List top 5 highest-paid employees.

LIMIT/TOP



  1. Get the first 5 records from employees.
  2. Show the last 10 orders placed.
  3. Show top 3 products by rating.
  4. Limit the number of customers to 100.
  5. Get any 1 random row from products.

Intermediate Level



INSERT, UPDATE, DELETE



  1. Insert a new product.
  2. Update the salary of an employee.
  3. Delete a customer by ID.
  4. Insert 3 records into the departments table.
  5. Increase price of all products by 10%.

AGGREGATE FUNCTIONS



  1. Count total number of employees.
  2. Find the average product price.
  3. Get the max and min salary.
  4. Count how many orders a user has made.
  5. Find total sales for April.

GROUP BY



  1. Group employees by department.
  2. Get total number of customers per city.
  3. Show average price per product category.
  4. Find number of orders per day.
  5. Count products grouped by brand.

HAVING



  1. Get departments with more than 5 employees.
  2. Show cities with more than 10 customers.
  3. Find products with average rating above 4.
  4. Count employees in departments with over 3 managers.
  5. Filter categories with more than 20 products.

JOINS



  1. Get employee names with their department names.
  2. Join orders and customers to show order details.
  3. List all products with their categories.
  4. Show comments with user info (post + user join).
  5. Show all students and their attendance records.

Advanced Level



Subqueries



  1. Get employees earning more than the average salary.
  2. List customers who made more than 5 orders.
  3. Find users who haven't placed any orders.
  4. Get the latest order for each customer.
  5. Select the highest-paid employee in each department.

CASE Statements



  1. Classify employees as 'Junior', 'Mid', 'Senior' based on salary.
  2. Show customer status based on total order amount.
  3. Label products as 'Cheap', 'Moderate', or 'Expensive'.
  4. Determine if students passed or failed based on marks.
  5. Use CASE to calculate bonus for employees.

String Functions



  1. Get the first 5 characters of a product name.
  2. Convert customer names to uppercase.
  3. Concatenate first and last name into full name.
  4. Replace 'Inc' with 'Ltd' in company names.
  5. Count length of product descriptions.

Date Functions



  1. Get today's orders.
  2. Extract month from order_date.
  3. Find users who joined last year.
  4. List events happening this week.
  5. Show age of users using birthdate.

Window Functions (Advanced SQL)



  1. Rank products by rating within category.
  2. Show running total of sales by day.
  3. Use LAG to get previous day's order count.
  4. Use LEAD to show next salary value.
  5. Find highest-paid employee in each department using RANK.

Schema-Based Challenges



Students System

  1. Get average marks per student.
  2. Show students who failed any subject.
  3. Find top 3 scorers in each class.
  4. List classes with no students.
  5. Rank students in class 10 by average score.

Blogging Platform



  1. List all posts with author names.
  2. Count comments per post.
  3. Get top 5 most commented posts.
  4. Show posts with no comments.
  5. Find users who posted in the last 7 days.

E-Commerce Store



  1. Show all orders with customer and product info.
  2. Find best-selling product.
  3. Get total sales per category.
  4. List customers who placed multiple orders.
  5. Find products that were never sold.

Social Media



  1. Count likes per post.
  2. Find most followed user.
  3. Show posts from people the user follows.
  4. List users who haven't posted anything.
  5. Show recent activity in the last 24 hours.

Bonus: Practice Questions for Constraints and Admin



Constraints & Permissions



  1. Create a table with PRIMARY KEY and NOT NULL.
  2. Add a FOREIGN KEY constraint.
  3. Update a table to add a UNIQUE constraint.
  4. Revoke permissions from a user.
  5. Grant SELECT and INSERT to a role.

Views, Indexes, Procedures



  1. Create a view to show active employees.
  2. Write a stored procedure to calculate tax.
  3. Create an index on the order_date column.
  4. Use a trigger to prevent deleting a VIP user.
  5. Create a materialized view for monthly sales.