SQL Intermediate

Question 1

What is a subquery in SQL?

Answer 1

A query nested inside another SQL query

Question 2

How do you use a subquery in a WHERE clause?

Answer 2

SELECT * FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = ‘Sales’);

Question 3

What is a correlated subquery?

Answer 3

A subquery that uses values from the outer query and runs once per row

Question 4

What is a common table expression (CTE)?

Answer 4

A temporary named result set defined using WITH

Question 5

How do you define a CTE?

Answer 5

WITH temp AS (SELECT * FROM employees) SELECT * FROM temp;

Question 6

What is the difference between a CTE and a subquery?

Answer 6

CTEs improve readability and can be reused; subqueries are embedded and more limited

Question 7

What is a window function?

Answer 7

A function that performs calculations across a set of table rows related to the current row

Question 8

What does ROW_NUMBER() do?

Answer 8

Assigns a unique sequential number to each row in the result set

Question 9

What is PARTITION BY in window functions?

Answer 9

Divides rows into groups before applying the function

Question 10

What does RANK() do?

Answer 10

Assigns a rank to rows within a partition

Question 11

How do you find the second highest salary using RANK?

Answer 11

SELECT * FROM (SELECT salary

Question 12

What is the purpose of COALESCE()?

Answer 12

Returns the first non-NULL value in a list

Question 13

What does NVL() do in SQL?

Answer 13

Replaces NULL with a specified value (Oracle-specific; COALESCE is standard)

Question 14

What is a self-join?

Answer 14

A table joined to itself

Question 15

How do you perform a self-join?

Answer 15

Use table aliases: SELECT a.name

Question 16

What does the IN operator do?

Answer 16

Checks if a value exists in a list or result of a subquery

Question 17

What is the difference between IN and EXISTS?

Answer 17

IN compares values; EXISTS checks for row existence and can be faster for correlated subqueries

Question 18

How do you enforce uniqueness in a column?

Answer 18

Use a UNIQUE constraint

Question 19

What is a composite primary key?

Answer 19

A primary key made of more than one column

Question 20

What are indexes in SQL?

Answer 20

Data structures that improve the speed of queries on a database table

Question 21

How do you create an index on the name column?

Answer 21

CREATE INDEX idx_name ON employees(name);

Question 22

What are the downsides of indexes?

Answer 22

They speed up reads but slow down writes and increase storage

Question 23

What is normalization?

Answer 23

The process of organizing data to reduce redundancy and improve integrity

Question 24

What is the goal of first normal form (1NF)?

Answer 24

Ensure each column contains atomic values and each row is unique

Question 25

What is the goal of second normal form (2NF)?

Answer 25

Remove partial dependencies; every non-key attribute must depend on the whole key

Question 26

What is the goal of third normal form (3NF)?

Answer 26

Remove transitive dependencies; non-key attributes must depend only on the key

Question 27

What is denormalization?

Answer 27

The process of introducing redundancy to improve read performance

Question 28

What is a transaction in SQL?

Answer 28

A sequence of operations performed as a single logical unit of work

Question 29

What are the ACID properties?

Answer 29

Atomicity

Question 30

What is the COMMIT statement?

Answer 30

Saves the changes made during the transaction permanently

Question 31

What does the ROLLBACK statement do?

Answer 31

Reverts changes made during the transaction

Question 32

What is the difference between TRUNCATE and DELETE?

Answer 32

TRUNCATE removes all rows quickly without logging; DELETE can be selective and is logged

Question 33

What is a view in SQL?

Answer 33

A virtual table created by a SELECT query

Question 34

How do you create a view?

Answer 34

CREATE VIEW view_name AS SELECT column1 FROM table_name;

Question 35

What are materialized views?

Answer 35

Views that store the query result physically and can be refreshed

Question 36

What is a UNION in SQL?

Answer 36

Combines results of two SELECTs and removes duplicates

Question 37

What is UNION ALL?

Answer 37

Combines results of two SELECTs and includes duplicates

Question 38

How do you change a column’s data type?

Answer 38

ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type;

Question 39

What is a constraint in SQL?

Answer 39

A rule enforced on data to ensure integrity (e.g.

Question 40

How do you add a NOT NULL constraint to a column?

Answer 40

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;