SQL Basics (DQL + DML)

Question 1

What are the main categories of SQL?

Answer 1

DQL (Data Query Language), DML (Data Manipulation Language), DDL (Data Definition Language), DCL (Data Control Language), TCL (Transaction Control Language).

Question 2

What is DQL in SQL?

Answer 2

DQL is used to query data from the database, primarily using the SELECT statement.

Question 3

What is DML in SQL?

Answer 3

DML includes commands like INSERT, UPDATE, DELETE to modify the data in the database.

Question 4

What is DDL in SQL?

Answer 4

DDL is used to define and modify database structures, such as CREATE, ALTER, DROP.

Question 5

What is DCL in SQL?

Answer 5

DCL includes commands like GRANT and REVOKE to control access to data.

Question 6

What is TCL in SQL?

Answer 6

TCL includes commands like COMMIT, ROLLBACK, SAVEPOINT to manage transactions.

Question 7

What does the SELECT statement do?

Answer 7

SELECT retrieves data from one or more tables.

Question 8

What does the WHERE clause do in SQL?

Answer 8

WHERE filters rows based on specified conditions.

Question 9

What does ORDER BY do?

Answer 9

ORDER BY sorts the result set based on one or more columns.

Question 10

What does the LIMIT clause do?

Answer 10

LIMIT restricts the number of rows returned in a result set.

Question 11

What does INSERT do in SQL?

Answer 11

INSERT adds new rows to a table.

Question 12

What does UPDATE do in SQL?

Answer 12

UPDATE modifies existing rows in a table.

Question 13

What does DELETE do in SQL?

Answer 13

DELETE removes rows from a table.

Question 14

What does COUNT() do in SQL?

Answer 14

COUNT() returns the number of rows matching a condition.

Question 15

What does SUM() do in SQL?

Answer 15

SUM() returns the total sum of a numeric column.

Question 16

What does AVG() do in SQL?

Answer 16

AVG() returns the average value of a numeric column.

Question 17

What does MIN() do in SQL?

Answer 17

MIN() returns the smallest value in a column.

Question 18

What does MAX() do in SQL?

Answer 18

MAX() returns the largest value in a column.

Question 19

What does GROUP BY do?

Answer 19

GROUP BY groups rows that have the same values into summary rows.

Question 20

What does HAVING do?

Answer 20

HAVING filters groups based on aggregate conditions.

Question 21

What is an INNER JOIN?

Answer 21

Returns rows with matching values in both tables.

Question 22

What is a LEFT JOIN?

Answer 22

Returns all rows from the left table and matched rows from the right table.

Question 23

What is a RIGHT JOIN?

Answer 23

Returns all rows from the right table and matched rows from the left table.

Question 24

What is a FULL OUTER JOIN?

Answer 24

Returns all rows when there is a match in either left or right table.

Question 25

What are SQL aliases?

Answer 25

Temporary names for columns or tables using AS keyword for readability.

Question 26

What does DISTINCT do in SQL?

Answer 26

DISTINCT returns only unique values, removing duplicates from the result set.

Question 27

What’s the difference between IS NULL and = NULL?

Answer 27

IS NULL checks for NULL values; = NULL does not work because NULL is not equal to anything.

Question 28

What is a best practice when using JOINs?

Answer 28

Always specify join conditions to avoid Cartesian products.

Question 29

What is a best practice for writing queries?

Answer 29

Use explicit column names instead of SELECT * for better performance and clarity.

Question 30

What are common use cases for aggregate functions?

Answer 30

Analytics, reporting, and summary tables.

Question 31

What is the impact of poorly written queries on system design?

Answer 31

Can lead to high CPU usage, slow response times, and inefficient indexes.

Question 32

How do JOINs affect performance?

Answer 32

JOINs can be expensive on large tables if not indexed properly or filtered early.

Question 33

What tools help monitor SQL query performance?

Answer 33

EXPLAIN plans, SQL profiler tools, slow query logs, and APM tools.

Question 34

What’s a tradeoff of using GROUP BY with large datasets?

Answer 34

High memory/CPU usage versus pre-aggregating data in summary tables.

Question 35

What is a common interview question on JOINs?

Answer 35

Explain the difference between INNER JOIN and LEFT JOIN with an example.

Question 36

What is a potential gotcha with UPDATE statements?

Answer 36

Forgetting the WHERE clause can update all rows in a table.

Question 37

What is a potential gotcha with DELETE statements?

Answer 37

Running DELETE without WHERE deletes all data from the table.

Question 38

What’s a performance tip for aggregate queries?

Answer 38

Use indexes on grouped and filtered columns where possible.

Question 39

What is a common real-world tradeoff in query design?

Answer 39

Read performance vs. flexibility and maintainability of the SQL code.

Question 40

When should you use DISTINCT cautiously?

Answer 40

DISTINCT may be expensive and should be avoided unless necessary to remove duplicates.

Question 41

What does IS NOT NULL do?

Answer 41

Checks whether a value is not NULL.

Question 42

What is a scalar subquery in SELECT?

Answer 42

A subquery that returns a single value, used in SELECT or WHERE clauses.

Question 43

What’s a use case for ORDER BY with LIMIT?

Answer 43

Fetching top N results like 'Top 10 best-selling products'.