SQL Interview Questions — Flashcards | inGrade

Question 1

What is SQL and why is it important?

Accepted Answer

SQL (Structured Query Language) is a standard programming language used to manage and manipulate relational databases. It is important because it allows us to interact with databases to store, retrieve, and modify data. SQL is used for data querying, updating, and managing database structures efficiently.

Question 2

What are the different types of SQL statements?

Accepted Answer

SQL statements are divided into: DDL (Data Definition Language) for database structure (CREATE, ALTER, DROP), DML (Data Manipulation Language) for data within the database (SELECT, INSERT, UPDATE, DELETE), DCL (Data Control Language) for permissions (GRANT, REVOKE), and TCL (Transaction Control Language) for transactions (COMMIT, ROLLBACK).

Question 3

What is a JOIN in SQL? Explain different types.

Accepted Answer

A JOIN combines records from two or more tables based on a related column. Types include: INNER JOIN (returns only rows with matching values in both tables), LEFT JOIN (returns all rows from the left table and matching rows from the right), RIGHT JOIN (returns all rows from the right table), and FULL JOIN (returns all rows when there is a match in either table).

Question 4

What is the difference between DELETE and TRUNCATE?

Accepted Answer

DELETE removes rows from a table based on a condition and can be rolled back if wrapped in a transaction. TRUNCATE removes all rows from a table, cannot be rolled back, and is generally faster than DELETE. TRUNCATE also resets identity columns, while DELETE does not.

Question 5

What are aggregate functions in SQL?

Accepted Answer

Aggregate functions perform a calculation on a set of values and return a single value. Common aggregate functions include COUNT() (counts rows), SUM() (adds values), AVG() (returns average), MAX() (returns highest value), and MIN() (returns lowest value). They are often used with GROUP BY to perform calculations on each group.

Question 6

What is a subquery in SQL?

Accepted Answer

A subquery is a query nested inside another query. It can be placed in SELECT, WHERE, or FROM clauses. For example: SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees) retrieves all employees whose salary is greater than the average. Correlated subqueries reference the outer query and are executed for each row.

Question 7

What are the ACID properties of a SQL transaction?

Accepted Answer

ACID properties ensure database consistency: Atomicity (all operations in a transaction complete or none do), Consistency (a transaction moves the database from one valid state to another), Isolation (transactions are isolated from each other), and Durability (once committed, changes are permanent and survive system failures).

Question 8

What is an index in SQL and when should you use it?

Accepted Answer

An index is a database object that improves the speed of data retrieval operations. Indexes are best used when a column is frequently used in WHERE clauses, join conditions, or ORDER BY. However, they slow down INSERT, UPDATE, and DELETE operations and consume additional disk space, so they should be used judiciously.

Question 9

What is the difference between CHAR and VARCHAR data types?

Accepted Answer

CHAR is a fixed-length string — if the stored string is shorter than the defined length, the remaining space is padded with spaces. VARCHAR is a variable-length string that only takes up as much space as needed, making it more efficient when storing strings of varying lengths.

Question 10

What is the HAVING clause and how does it differ from WHERE?

Accepted Answer

The HAVING clause filters the results of an aggregation, similar to how WHERE filters rows before aggregation. WHERE cannot be used with aggregate functions, while HAVING can. For example: SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 50000 returns departments where the average salary exceeds 50,000.

Start your learning journey today