SQL Syntax

Question 1

What does the SELECT statement do in SQL?

Answer 1

It specifies which columns to display in the result set.

Question 2

What clause specifies which table to retrieve data from?

Answer 2

FROM clause.

Question 3

Which clause filters rows based on conditions?

Answer 3

WHERE clause.

Question 4

How do you sort query results?

Answer 4

Using ORDER BY followed by column name(s) and ASC or DESC.

Question 5

What is the purpose of GROUP BY?

Answer 5

To aggregate data across rows that share a common value in one or more columns.

Question 6

What is the difference between WHERE and HAVING?

Answer 6

WHERE filters rows before aggregation; HAVING filters groups after aggregation.

Question 7

What does COUNT(*) do?

Answer 7

Counts all rows in the result set including duplicates and NULLs.

Question 8

How do you count distinct values in a column?

Answer 8

COUNT(DISTINCT column_name).

Question 9

Which SQL function returns the average of a numeric column?

Answer 9

AVG().

Question 10

Which function returns the sum of all values in a column?

Answer 10

SUM().

Question 11

Which function returns the largest value in a column?

Answer 11

MAX().

Question 12

Which function returns the smallest value in a column?

Answer 12

MIN().

Question 13

What keyword removes duplicate rows from a query result?

Answer 13

DISTINCT.

Question 14

What is the purpose of the AS keyword?

Answer 14

To rename a column or table with an alias.

Question 15

What are the main types of JOINs in SQL?

Answer 15

INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN

Question 16

What does an INNER JOIN return?

Answer 16

Rows with matching values in both tables.

Question 17

What does a LEFT JOIN return?

Answer 17

All rows from the left table and matched rows from the right; unmatched right values become NULL.

Question 18

What does a RIGHT JOIN return?

Answer 18

All rows from the right table and matched rows from the left; unmatched left values become NULL.

Question 19

What does a FULL OUTER JOIN return?

Answer 19

All rows when there is a match in either left or right table

Question 20

How do you combine results of two queries?

Answer 20

Using UNION or UNION ALL.

Question 21

What is the difference between UNION and UNION ALL?

Answer 21

UNION removes duplicates

Question 22

What is a subquery?

Answer 22

A query nested inside another query.

Question 23

What does the IN operator do?

Answer 23

Tests whether a value matches any value in a list or subquery.

Question 24

What does BETWEEN do?

Answer 24

Filters values within a given range (inclusive).

Question 25

What does LIKE do?

Answer 25

Performs pattern matching using % (wildcard for any characters) or _ (single character).

Question 26

How do you replace NULL with a default value?

Answer 26

COALESCE(column, default_value)

Question 27

What is a CASE statement?

Answer 27

Conditional logic in SQL for returning different values based on conditions.

Question 28

What is the purpose of LIMIT (or TOP in SQL Server)?

Answer 28

Restricts the number of rows returned.

Question 29

What are window functions?

Answer 29

Functions that perform calculations across sets of rows related to the current row without collapsing them.

Question 30

What does ROW_NUMBER() do?

Answer 30

Assigns a unique sequential integer to rows within a partition.

Question 31

What does RANK() do?

Answer 31

Gives ranks with gaps for ties in ordered results.

Question 32

What does DENSE_RANK() do?

Answer 32

Gives ranks without gaps for ties.

Question 33

What clause defines partitions for window functions?

Answer 33

PARTITION BY.

Question 34

What clause defines order for window functions?

Answer 34

ORDER BY inside the OVER() clause.

Question 35

What is the difference between GROUP BY and window functions?

Answer 35

GROUP BY collapses rows into one per group

Question 36

What is a primary key?

Answer 36

A column (or set of columns) that uniquely identifies each row in a table.

Question 37

What is a foreign key?

Answer 37

A column that links to the primary key in another table to create a relationship.

Question 38

What does SELECT DISTINCT city FROM customers do?

Answer 38

Returns a list of unique city names from the customers table.

Question 39

How can you find the total sales per customer?

Answer 39

SELECT customer_id, SUM(amount) FROM sales GROUP BY customer_id

Question 40

How can you filter groups (e.g. customers) with total sales > 1000

Answer 40

Use HAVING SUM(amount) > 1000. SELECT customer, SUM(amount) AS total_sales FROM sales GROUP BY customer HAVING SUM(amount) > 1000;

Question 41

What is the execution order of SQL clauses?

Answer 41

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

Question 42

How do you calculate cumulative sums in SQL?

Answer 42

Using SUM(amount) OVER(ORDER BY date).

Question 43

How do you find the previous row’s value?

Answer 43

Using LAG(column) OVER(ORDER BY some_column).

Question 44

How do you find the next row’s value based on some_column ordering?

Answer 44

Using LEAD(column) OVER(ORDER BY some_column).

Question 45

How can you count the number of unique users per day?

Answer 45

SELECT date, COUNT(DISTINCT user_id) FROM table GROUP BY date.

Question 46

What is a common table expression (CTE)?

Answer 46

A temporary result set defined using WITH that can be referenced within the main query.

Question 47

What is the difference between CTE and subquery?

Answer 47

CTEs can be referenced multiple times and improve readability; subqueries cannot.