Mastering SQL with 100 Essential Questions and Examples

SQL (Structured Query Language) is the cornerstone of database management and manipulation. This guide explores 100 practical and theoretical SQL questions across various categories to help you enhance your database skills.

1. Basic SQL Query Questions

1. Write a query to fetch all records from a table.

   SELECT * FROM table_name;

This query retrieves every record from the specified table.

1. Write a query to find the second highest salary in a table.

   SELECT MAX(salary) AS second_highest_salary  
   FROM employees  
   WHERE salary < (SELECT MAX(salary) FROM employees);

1. Write a query to fetch employees whose names start with 'A'.

   SELECT * FROM employees WHERE name LIKE 'A%';

1. Write a query to calculate the total sales grouped by region.

   SELECT region, SUM(sales) AS total_sales  
   FROM sales_data  
   GROUP BY region;

1. Write a query to fetch all records where the column value is NULL.

   SELECT * FROM table_name WHERE column_name IS NULL;

1. Write a query to remove duplicate rows from a table.

   DELETE FROM table_name  
   WHERE id NOT IN (SELECT MIN(id) FROM table_name GROUP BY column_name);

1. Write a query to display records in descending order.

   SELECT * FROM table_name ORDER BY column_name DESC;

1. Write a query to count the number of rows in a table.

   SELECT COUNT(*) FROM table_name;

1. Write a query to join two tables.

   SELECT employees.name, departments.department_name  
   FROM employees  
   JOIN departments  
   ON employees.department_id = departments.id;

1. Write a query to retrieve the first three rows from a table.
   

   SELECT * FROM table_name LIMIT 3;
   

2. SQL Performance Questions

1. What is query optimization?
   
   Query optimization involves modifying a query to improve its execution time and efficiency.

2. How can you improve the performance of a SQL query?

   • Use indexes.
   • Avoid SELECT *.
   • Optimize joins.
   • Use appropriate data types.
   • Analyze the execution plan.

3. What is the purpose of indexing?
   
   Indexing improves the speed of data retrieval operations on a database table.

4. What are the drawbacks of indexing?

   • Increased storage requirements.
   • Slower data modification operations like INSERT and DELETE.

5. How do you analyze the execution plan of a query?
   
   Use the EXPLAIN keyword to view the execution plan:
   

   EXPLAIN SELECT * FROM table_name;
   

6. What is query caching?
   
   Query caching stores the results of queries for reuse, reducing computation time.

7. What is sharding in databases?
   
   Sharding divides a database into smaller, faster, and more manageable parts called shards.

8. Explain the difference between horizontal and vertical scaling.

   • Horizontal scaling adds more machines to handle more data.
   • Vertical scaling adds resources (CPU, RAM) to a single machine.

9. How does partitioning help in database performance?
   
   Partitioning divides a large table into smaller, more manageable parts, improving query performance.

10. What is database replication?
    
    Replication involves copying and maintaining database copies across multiple servers for reliability and redundancy.

3. SQL Functions Questions

1. What are aggregate functions in SQL?
   
   Aggregate functions perform calculations on multiple rows of data: SUM, AVG, COUNT, etc.

2. Explain the difference between COUNT, SUM, and AVG.

   • COUNT: Counts the number of rows.
   • SUM: Adds values in a column.
   • AVG: Calculates the average.

3. How does the ROUND function work in SQL?
   

   SELECT ROUND(column_name, 2) FROM table_name;
   

4. What is the LENGTH function used for?
   
   It calculates the number of characters in a string:
   

   SELECT LENGTH(column_name) FROM table_name;
   

5. Explain the use of the CASE statement in SQL.
   

   SELECT name,  
          CASE  
              WHEN salary > 5000 THEN 'High'  
              ELSE 'Low'  
          END AS salary_status  
   FROM employees;
   

6. What is the difference between COALESCE and ISNULL?

   • COALESCE: Returns the first non-null value from a list.
   • ISNULL: Checks for null and replaces with a specified value.

7. How do you use string functions like UPPER and LOWER?
   

   SELECT UPPER(column_name) AS upper_case, LOWER(column_name) AS lower_case FROM table_name;
   

8. What is the purpose of the NOW() function?
   
   Returns the current date and time:
   

   SELECT NOW();
   

9. Explain the use of the CONCAT function.
   

   SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
   

10. What is the difference between TRUNCATE and DELETE?

    • TRUNCATE: Removes all rows from a table without logging individual row deletions.
    • DELETE: Removes rows with a condition and logs each deletion.

Hi, I'm Abhay Singh Kathayat!
I am a full-stack developer with expertise in both front-end and back-end technologies. I work with a variety of programming languages and frameworks to build efficient, scalable, and user-friendly applications.
Feel free to reach out to me at my business email: kaashshorts28@gmail.com.