SQL - Theory

Question 1

What does the SELECT statement do in SQL?

Answer 1

It retrieves data from one or more tables in a database.

Question 2

How do you filter records in a SQL query?

Answer 2

By using the WHERE clause.

Question 3

What is the purpose of the JOIN clause?

Answer 3

The JOIN clause combines rows from two or more tables based on a related column between them.

Question 4

What is the difference between INNER JOIN and LEFT JOIN?

Answer 4

INNER JOIN returns only matching records from both tables, while LEFT JOIN returns all records from the left table and matched records from the right table.

Question 5

How do you select distinct values in a column?

Answer 5

By using the SELECT DISTINCT statement.

Question 6

What does the GROUP BY clause do?

Answer 6

The GROUP BY clause groups rows that have the same values in specified columns into summary rows.

Question 7

How do you count the number of rows in a table?

Answer 7

Using the COUNT(*) function.

Question 8

How can you calculate the total sum of a column?

Answer 8

By using the SUM(column_name) function.

Question 9

What is the purpose of the HAVING clause?

Answer 9

The purpose of the HAVING clause is to filter groups created by the GROUP BY clause based on a condition.

Question 10

How do you order the results of a query?

Answer 10

By using the ORDER BY clause followed by the column name and sort direction (ASC or DESC).

Question 11

What is a subquery?

Answer 11

A query nested inside another SQL query.

Question 12

How do you create an index on a table?

Answer 12

Using the CREATE INDEX statement, e.g., CREATE INDEX idx_name ON table_name(column_name);

Question 13

What is the purpose of an index in a database?

Answer 13

The purpose of an index is to improve the speed of data retrieval operations on a table.

Question 14

Explain the difference between clustered and non-clustered indexes.

Answer 14

A clustered index determines the physical order of data in a table, while a non-clustered index creates a separate structure pointing to the data.

Question 15

What is a primary key?

Answer 15

A unique identifier for each record in a table that cannot contain NULL values.

Question 16

How do you perform a case-insensitive search in SQL?

Answer 16

By using functions like LOWER() or UPPER() on both the column and the search string, e.g., WHERE LOWER(column_name) = ‘value’.

Question 17

What is normalization?

Answer 17

Normalization is the process of organizing a database to reduce redundancy and improve data integrity.

Question 18

What are aggregate functions? Name three examples.

Answer 18

Functions that perform a calculation on a set of values and return a single value. Examples: COUNT(), SUM(), AVG().

Question 19

What does the EXPLAIN statement do?

Answer 19

The EXPLAIN statement provides the execution plan for a SQL query, helping to analyze and optimize performance.

Question 20

How can you remove duplicate rows from a result set?

Answer 20

By using the SELECT DISTINCT statement.

Question 21

What is a foreign key?

Answer 21

A foreign key is a field in one table that uniquely identifies a row of another table, establishing a relationship between the two tables.

Question 22

How do you update data in a table?

Answer 22

UPDATE statement with the SET clause, e.g., UPDATE table_name SET column1 = value1 WHERE condition;

Question 23

How do you delete records from a table?

Answer 23

Using the DELETE FROM statement with a WHERE clause, e.g., DELETE FROM table_name WHERE condition;

Question 24

What is the difference between WHERE and HAVING clauses?

Answer 24

WHERE filters rows before grouping, while HAVING filters groups after GROUP BY.

Question 25

How do you find the average value of a column?

Answer 25

Using the **AVG(column_name)** function.

Question 26

What is a view in SQL?

Answer 26

A virtual table based on the result-set of a SQL statement.

Question 27

How do you create a view?

Answer 27

Using the **CREATE VIEW** statement, e.g., **CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;**

Question 28

What is a stored procedure?

Answer 28

A prepared SQL code that you can save and reuse, allowing for modular and efficient code execution.

Question 29

How do you create an index on multiple columns?

Answer 29

By listing multiple columns in the **CREATE INDEX** statement, e.g., **CREATE INDEX idx_name ON table_name(column1, column2);**

Question 30

What is a composite key?

Answer 30

A primary key composed of two or more columns to uniquely identify a record.

Question 31

Explain the **UNION** operator.

Answer 31

It combines the result sets of two or more **SELECT** statements, removing duplicate rows.

Question 32

How does **UNION ALL** differ from **UNION**?

Answer 32

**UNION ALL** includes all duplicate rows, while **UNION** removes duplicates.

Question 33

What is a transaction in SQL?

Answer 33

A sequence of one or more SQL operations treated as a single unit, ensuring data integrity.

Question 34

How do you start a transaction?

Answer 34

Using the **BEGIN TRANSACTION** or **START TRANSACTION** statement.

Question 35

What is the purpose of the **COMMIT** statement?

Answer 35

To save all changes made during the current transaction.

Question 36

How do you revert changes in a transaction?

Answer 36

Using the **ROLLBACK** statement.

Question 37

What is a window function?

Answer 37

A function that performs calculations across a set of table rows related to the current row, without collapsing the result set.

Question 38

Give an example of a window function.

Answer 38

**ROW_NUMBER() OVER (PARTITION BY column ORDER BY column)**

Question 39

How can you improve query performance?

Answer 39

By optimizing indexes, avoiding unnecessary columns in SELECT, using efficient joins, and analyzing execution plans.

Question 40

What does the **DISTINCT** keyword do?

Answer 40

It removes duplicate rows from the result set.

Question 41

How do you concatenate two columns in SQL?

Answer 41

Using the **CONCAT()** function or the **||** operator, e.g., **SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM table_name;**

Question 42

What is the purpose of the **LIKE** operator?

Answer 42

To search for a specified pattern in a column.

Question 43

How do you perform a pattern match where a column starts with 'A'?

Answer 43

**WHERE column_name LIKE 'A%'**

Question 44

What is a CTE (Common Table Expression)?

Answer 44

A temporary result set defined within the execution scope of a single **SELECT**, **INSERT**, **UPDATE**, or **DELETE** statement.

Question 45

What is the **NULL** value in SQL?

Answer 45

It represents the absence of a value or an unknown value.

Question 46

How do you check for **NULL** values in a column?

Answer 46

Using the **IS NULL** or **IS NOT NULL** operators, e.g., **WHERE column_name IS NULL**.

Question 47

What is the purpose of the **CASE** statement?

Answer 47

To provide conditional logic in SQL queries, allowing different outputs based on conditions.