The Ultimate Guide to Interview Questions SQL

1. What is SQL?

SQL stands for Structured Query Language, It allows you to connect with relational databases by writing queries that retrieve, insert, update, or delete data. SQL is a standard language for managing relational databases which is widely used in a number of industries.

2. What is MySQL?

MySQL is a free and open-source relational database management system (RDBMS) that employs SQL to manage and interact with data, It is frequently utilized for its reliability, ease of use, and sturdy features.

4. DBMS vs. RDBMS

DBMS (Database Management System) is a general term for software that manages databases, while RDBMS (Relational Database Management System) specifically manages data in a tabular form with relationships between tables, RDBMS systems support SQL and ensure data integrity through ACID properties.

5. What is an IDE?

An IDE (Integrated Development Environment) is a software application that provides comprehensive facilities for software development, For SQL, IDEs like MySQL Workbench, SQL Server Management Studio (SSMS), and others offer tools for writing, testing, and managing SQL queries and databases.

6. MySQL vs. PostgreSQL

MySQL and PostgreSQL are both popular relational database systems. Key differences include:

Compliance: PostgreSQL is known for its adherence to SQL standards and support for advanced features like JSONB, whereas MySQL has more relaxed standards compliance.
Extensibility: PostgreSQL supports custom data types and functions, while MySQL is more limited in extensibility.
Performance: MySQL is generally faster for read-heavy workloads, while PostgreSQL excels in write-heavy operations and complex queries.

7. MySQL vs. MongoDB

MySQL is a relational database system, while MongoDB is a NoSQL database. Differences include:

Data Model: MySQL uses tables and rows, while MongoDB uses collections and documents.
Schema: MySQL requires a predefined schema, whereas MongoDB allows schema-less data storage.
Query Language: MySQL uses SQL, while MongoDB uses its own query language.

8. SQL vs. NoSQL

SQL databases are relational and use structured query language for data manipulation, while NoSQL databases are non-relational and can store data in various formats like key-value pairs, documents, or graphs, NoSQL databases are typically used for unstructured or semi-structured data.

9. What is a Schema?

A schema in a database defines the structure of the data, including tables, column's, relationships, and constraints,. It provides a blueprint for how data is organized and ensure's the data integrity.

10. Database Structure

A typical database structure includes:

Database Name: The name of the database.
Tables: Collections of rows (records) and columns (fields).
Rows: Individual records within a table.
Columns: Attributes or fields within a table.

11. MySQL Constraints

MySQL supports various constraints to enforce data integrity, including:

PRIMARY KEY: Uniquely identifies each record in a table.
FOREIGN KEY: Ensures referential integrity between tables.
UNIQUE: Ensures all values in a column are distinct.
NOT NULL: Ensures a column cannot have NULL values.
CHECK: Ensures values in a column meet a specified condition.
DEFAULT: Assigns a default value to a column if none is provided.

12. Primary Key vs. Foreign Key

Primary Key: A column or set of columns that uniquely identifies each row in a table. Each table can have only one primary key.

Foreign Key: A column or set of columns that establishes a link between data in two tables. It references the primary key of another table.

13. DROP vs. TRUNCATE

DROP: Deletes the table and all of its data. The table structure is removed from the database.

TRUNCATE: Deletes all rows from a table but keeps the table structure intact. It is faster than DELETE without a WHERE clause.

14. UNION vs. UNION ALL

UNION: Combines the results of two queries and removes duplicate rows.

UNION ALL: Combines the results of two queries and includes all rows, including duplicates.

15. ORDER BY Execution

The ORDER BY clause sorts the result set of a query based on one or more columns, It can sort in ascending (ASC) or descending (DESC) order.

16. GROUP BY vs. HAVING

GROUP BY: Groups rows that have the same values into summary rows.

HAVING: Filters groups based on a condition, similar to the WHERE clause but for grouped data.

17. What are Views?

Views are virtual tables created by querying one or more tables, They do not store data themselves but provide a way to simplify complex queries and present data in a specific format.

18. Indexes

Indexes are used to speed up the retrieval of rows from a table by creating a data structure that improves query performance, They can be created on one or more columns of a table.

19. Triggers

Triggers are special procedures that are automatically executed in response to certain events on a table, such as INSERT, UPDATE, or DELETE operations.

20. Common Table Expressions (CTEs)

CTEs are temporary result sets that are defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement, They are useful for creating recursive queries and simplifying complex queries.

21. Stored Procedures

Stored procedures are precompiled collections of one or more SQL statements that can be executed as a single unit, They help to encapsulate logic and improve performance.

22. Subqueries

Subqueries are queries nested inside another query, They can be used to retrieve data that will be used in the main query.

23. Normalization

Normalization is the process of organizing data to reduce redundancy and improve data integrity, It involves dividing a database into two or more tables and defining relationships between them.

24. ER Model

The Entity-Relationship (ER) model is a diagrammatic representation of entities and their relationships in a database, It helps in designing a database schema.

25. Wildcards

Wildcards are used in SQL queries to search for patterns in data. The most common wildcards are % (any sequence of characters) and _ (a single character).

26. Joins

Joins are used to combine rows from two or more tables based on a related column. Types of joins include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

27. CASE Expression

The CASE expression adds conditional logic to a SQL query. allowing for varied responses depending on specified conditions.

28. Window Functions

Window functions perform calculations across a set of table rows related to the current row. Examples include RANK(), DENSE_RANK(), and ROW_NUMBER().

29. Deleting Duplicate Rows

To delete duplicate rows from a table, you can use a combination of ROW_NUMBER() and a DELETE statement.

               
WITH cte AS (
SELECT *,
    ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY (SELECT NULL)) AS rn
FROM table_name
)
DELETE FROM cte WHERE rn > 1;

30. Find Top 3 Highest Salaries from Each Department

              
SELECT department, salary
FROM (
  SELECT department, salary,
      ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
) AS ranked
WHERE rn <= 3;

31. Display Nth Row in a Table

              
SELECT *
FROM (
    SELECT *,
        ROW_NUMBER() OVER (ORDER BY column_name) AS rn
    FROM table_name
) AS ranked
WHERE rn = 2;  -- Change to 1, 2, 3, etc., to get different rows

32. Combine Two Tables and Find Unique Records

              
SELECT DISTINCT *
FROM table1
UNION
SELECT DISTINCT *
FROM table2;

33. Show Top N Salaries of Employees

              
SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 5;  -- Change to the number of top salaries you want

Conclusion

SQLs is the most important and widely used language to query a database in relational databases. This guide explained, Key terms and differences between SQL and NoSQL databases as well as some practical advice to start running SQL queries on real examples. Understanding these concepts will make you better in managing things related to the database and perform well on interviews.