SQL-Query

Welcome to the SQL Query Practice Repository! In this repository, I Tushar Agggrwal designed it to help anyone strengthen SQL query skills by providing a collection of common and interview-based SQL queries for practice.

Download my Advanced SQL guide

Introduction

SQL (Structured Query Language) is a powerful programming language used for managing and manipulating relational databases. Proficiency in SQL is highly valued in the tech industry, particularly for data analysis, database management, and software development roles.

This repository serves as a valuable resource for both beginners and experienced SQL users, offering a wide range of query examples to enhance your understanding and problem-solving capabilities.

Features

• Common SQL Queries: Browse through a curated collection of commonly used SQL queries. These queries cover various aspects of database management, including data retrieval, filtering, sorting, aggregation, and more.

• Interview Queries: Prepare for SQL-based job interviews by exploring a set of carefully selected interview-based SQL queries. These queries simulate real-world scenarios and challenge your problem-solving skills.

• Practice: Put your SQL skills to the test! Take advantage of the query files provided in this repository to practice and refine your abilities. Work through the queries, experiment with different approaches, and strive for efficient and elegant solutions.

Naviagte through this REPO

S.no.	Query	Level	Result	DB & Data
1	Data	Easy	Result	DB & Data
2	Data	Easy	Result	DB & Data
3	Data	Easy	Result	DB & Data
4	Data	Easy	Result	DB & Data
5	Data	Easy	Result	DB & Data
6	Data	Easy	Result	DB & Data
7	Data	Easy	Result	DB & Data
8	Data	Easy	Result	DB & Data
9	Data	Easy	Result	DB & Data
10	Data	Easy	Result	DB & Data
11	Data	Easy	Result	DB & Data

Getting Started

To get started with the SQL Query Practice Repository, follow these simple steps:

1. Explore the Queries: Browse through the repository and examine the available SQL query files. Each file is named descriptively to indicate the topic or question it covers.

2. Practice, Practice, Practice!: Open the SQL query files using your preferred text editor or integrated development environment (IDE). Read the instructions and attempt to write the SQL queries to solve the problem statements. Experiment with different approaches and strive for efficient solutions.

3. Validate Your Queries: Compare your solutions with the provided sample queries in the repository. Use these sample queries as a reference to validate your own implementations and to learn alternative approaches.

4. Contribute: If you come up with new SQL queries or have improvements to existing ones, consider contributing to this repository! Fork the repository, make your changes, and submit a pull request. Your contributions will be greatly appreciated by the community.

Author

• ©2023 Tushar Aggarwal. All rights reserved
• LinkedIn
• Medium
• Tushar-Aggarwal.com
• Kaggle

Read or Regret:

---

1) Top 7 most common SQL commands tested during SQL interviews:

• SELECT - used to select specific columns from a table
• FROM - used to specify the table that contains the columns you are SELECT’ing
• WHERE - used to specify which rows to pick
• GROUP BY - used to group rows with similar values together
• HAVING - used to specify which groups to include, that were formed by the GROUP BY clause.
• ORDER BY - used to order the rows in the result set, either in ascending or descending order
• LIMIT - used to limit the number of rows returned

But what else it can do ?

• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
• SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
• SQL can set permissions on tables, procedures, and views

---

2) Some of The Most Important SQL Commands

1. SELECT:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

2. INSERT:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

3. UPDATE:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

4. DELETE:

DELETE FROM table_name
WHERE condition;

5. CREATE TABLE:

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  ...
);

6. ALTER TABLE:

ALTER TABLE table_name
ADD column_name datatype;

7. DROP TABLE:

DROP TABLE table_name;

8. JOIN:

SELECT column1, column2, ...
FROM table1
JOIN table2 ON table1.column = table2.column;

9. GROUP BY:

SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1;

10. ORDER BY:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 ASC/DESC;

11. HAVING:

SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1
HAVING COUNT(*) > 5;

12. DISTINCT:

SELECT DISTINCT column1, column2, ...
FROM table_name;

13. TRUNCATE TABLE:

TRUNCATE TABLE table_name;

14. CREATE INDEX:

CREATE INDEX index_name
ON table_name (column1, column2, ...);

15. UNION:

SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

16. NULL:

SELECT column1, column2, ...
FROM table_name
WHERE column1 IS NULL;

17. CASE:

SELECT column1, 
       CASE
           WHEN condition1 THEN result1
           WHEN condition2 THEN result2
           ELSE result3
       END AS new_column
FROM table_name;

18. VIEW:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

19. GRANT:

GRANT permission_type
ON object_name
TO user_name;

20. REVOKE:

REVOKE permission_type
ON object_name
FROM user_name;

21. Nested SELECT Statement:

SELECT column1, column2, ...
FROM table1
WHERE column1 IN (SELECT column1 FROM table2 WHERE condition);

22. Self-Join:

SELECT e1.employee_name, e2.employee_name
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id;

23. Subquery with EXISTS:

SELECT column1, column2, ...
FROM table1
WHERE EXISTS (SELECT column1 FROM table2 WHERE condition);

24. Common Table Expression (CTE):

WITH cte_name AS (
    SELECT column1, column2, ...
    FROM table1
    WHERE condition
)
SELECT column1, column2, ...
FROM cte_name;

25. Window Functions:

SELECT column1, column2, ..., 
       ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2) AS row_num
FROM table1;

26. Recursive CTE:

WITH recursive cte_name (column1, column2, ...) AS (
    SELECT initial_data
    UNION ALL
    SELECT recursive_data FROM cte_name WHERE condition
)
SELECT column1, column2, ...
FROM cte_name;

27. Pivoting and Unpivoting:

-- Pivoting
SELECT column1, SUM(column2) AS total
FROM table1
GROUP BY column1
PIVOT (SUM(column2) FOR column3 IN ('Value1', 'Value2', 'Value3')) AS pivot_table;

-- Unpivoting
SELECT column1, column3, total
FROM pivot_table
UNPIVOT (total FOR column3 IN (Value1, Value2, Value3)) AS unpivot_table;

---

3) The 5 most common aggregate functions used in SQL interviews are:

• AVG() - Returns the average value
• COUNT() - Returns the number of rows
• MAX() - Returns the largest value
• MIN() - Returns the smallest value
• SUM() - Returns the sum

---

4) What are the 4 different joins tested in SQL assessments?

• INNER JOIN - combines rows from two tables that have matching values
• LEFT JOIN - combines rows from the left table, even if there are no matching values in the right table
• RIGHT JOIN - combines rows from the right table, even if there are no matching values in the left table
• FULL JOIN - combines rows from both tables, regardless of whether there are matching values

---

5) Most Common SQL Join Interview Questions

• What is a self-join, and when would you use it?
• What is an anti-join, and when would you use it?
• What are the performance considerations of SQL join queries?
• How do you optimize a slow join query?
• How do you join more than two tables?
• Does a join always have to be on two rows sharing the same value (non-equi joins)?

---

6) Most Common Date/Time Functions Used in SQL Interviews

• NOW(): returns the current date and time
• CURRENT_DATE(): returns the current date
• INTERVAL: adds a specified time interval to a date
• DATEDIFF: calculates the difference between two dates
• EXTRACT: extracts a specific part of a date (e.g., month, day, year)

---

7) What are the most common window functions for SQL interviews?

• RANK() - gives a rank to each row in a partition based on a specified column or value
• DENSE_RANK() - gives a rank to each row, but DOESN'T skip rank values
• ROW_NUMBER() - gives a unique integer to each row in a partition based on the order of the rows
• NTILE() - divides a partition into a specified number of groups, and gives a group number to each row
• LAG() - retrieves a value from a previous row in a partition based on a specified column or expression
• LEAD() - retrieves a value from a subsequent row in a partition based on a specified column or expression
• NTH_VALUE() - retrieves the nth value in a partition

---

8) Common Database Design Interview Questions

• What is an index, and why does it speed up queries?
• What are the dis-advantages of using indexes?
• How do you troubleshoot a slow SQL query?
• What is a stored procedure, and when do we use them?
• What is normalization? Why might we want to also de-normalize some tables?
• What is ACID, and how does a database enforce atomicity, consistency, isolation, durability?
• What’s the difference between Star schema and Snowflake schema?
• What are the different types of dimensions (e.g. junk dimensions, conformed dimensions, mini dimensions, shrunken dimensions)?
• If you had to make a simple news feed, similar to the Facebook or LinkedIn feed, what are the main tables you’d have? Can you whiteboard a quick ER Diagram for it?
• What is database sharding?
• What are the advantages and disadvantages of relational vs. NoSQL databases?

---

9) How do you approach a SQL interview question?

• a) Understand the question
• b) Identify Relevant information
• c) Break down the problem
• d) Consider Edge Cases
• e) Write queries to answer sub-problems
• f) Test your final query

---

10) Additional resources

• Advanced Giude

---

Feedback

If you have any feedback, suggestions, or questions regarding this repository, please feel free to open an issue. We appreciate your input and will respond to your inquiries as soon as possible. Or Contact at info@tushar-aggarwal.com !