SQL (Structured Query Language) is the standard for managing and manipulating relational data. It includes commands for querying, updating, and defining database structures. This guide is divided into logical sections for easy navigation, with full theory explanations, code examples, diagrams (described in text for readability), and best practices. At the end of relevant sections, you’ll find an enhanced Q&A bank (questions 1-114) drawn from common interview topics, expanded with clearer explanations, additional SQL snippets, and edge-case insights.
Key Features of This Guide:
Tips for Interview Success:
Let’s dive in!
SQL is a declarative language for interacting with RDBMS. It was developed in the 1970s by IBM and standardized by ANSI. SQL handles data definition (DDL), manipulation (DML), control (DCL), and querying (DQL). Key commands include SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, and DROP.
Unlike procedural languages (e.g., PL/SQL), SQL focuses on what to do, not how. It supports standards but has vendor-specific extensions (e.g., MySQL’s LIMIT vs. SQL Server’s TOP).
Best Practices:
Basic SELECT:
-- Select all columns from a table
SELECT * FROM customers;
Handling NULLs:
-- Use IS NULL to check for missing values
SELECT * FROM products WHERE price IS NULL;
SELECT * FROM employees WHERE salary > 50000;
What does SQL stand for?
SQL stands for Structured Query Language. It accesses and manipulates databases and is an ANSI standard.
SQL is (referring to it as a Programming Language)
SQL is declarative: you specify the desired result without detailing steps (e.g., no need to manage indexes manually). It’s non-procedural, unlike languages like C++ that require explicit operations. This abstraction makes SQL efficient for data tasks.
Which of the following is NOT a SQL command? (SELECT, REMOVE, UPDATE, INSERT)
REMOVE is not a valid SQL command. Use DELETE instead. Key commands: SELECT (extract), UPDATE (modify), DELETE (remove), INSERT INTO (add), CREATE/ALTER/DROP (for structures), and INDEX operations.
What is MySQL?
MySQL is an open-source RDBMS by Oracle, using SQL as its language. Difference between SQL and MySQL: SQL is the language; MySQL is the system implementing it.
Best Practice: Use MySQL for web apps due to its speed; consider PostgreSQL for complex transactions.
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, PL/SQL!');
END;
CREATE TABLE users (is_active TINYINT(1));
INSERT INTO users VALUES (1); -- True
What data type would you choose if you wanted to store the distance (rounded to the nearest mile)?
INTEGER (or INT) for whole numbers. Use DECIMAL for precision if needed.
Best Practice: Choose data types based on range and precision to optimize storage.
Which are valid SQL keywords (statements & clauses)
Valid: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, UPDATE, DELETE, INSERT INTO, CREATE/ALTER DATABASE/TABLE, DROP TABLE, CREATE/DROP INDEX.
-- Comment. Multi-line: /* Comment */. Comments explain code or prevent execution.-- Single-line
SELECT * FROM customers; /* Multi-line comment */
Querying uses SELECT to retrieve data, with clauses like FROM (source), WHERE (filter), ORDER BY (sort), LIMIT (paginate). Operators include =, >, LIKE (patterns), BETWEEN (ranges), IN (lists). Handle duplicates with DISTINCT; NULLs with IS NULL/IFNULL.
Best Practices:
Text-based JOIN visualization (later in Joins section).
Pattern matching:
SELECT * FROM products WHERE name LIKE 'A%'; -- Starts with A
SELECT name AS full_name FROM customers WHERE age > 30 ORDER BY age DESC;
SELECT * FROM products;
Best Practice: Avoid * in production; list columns explicitly.
SELECT first_name AS fname FROM customers;
SELECT FirstName FROM Customers;
What does the SQL FROM clause do?
Specifies tables/joins for data extraction.
SELECT DISTINCT city FROM addresses;
SELECT DISTINCT city FROM addresses;
SELECT * FROM Customers WHERE FirstName = 'John';
Use single quotes for strings.
SELECT * FROM customers WHERE age > 18 AND city = 'NY' OR status = 'active';
SELECT * FROM Table1 WHERE Column1 >= 100;
Comparison operators: =, >, <, >=, <=, !=.
SELECT * FROM Customers WHERE FirstName = 'John' AND LastName = 'Jackson';
SELECT * FROM tbl ORDER BY RAND() LIMIT 10;
Performance note: Inefficient for large tables; consider alternatives like random IDs.
SELECT name, COALESCE(price, 50) FROM products;
Which operator is used to search for a specified text pattern in a column?
LIKE with % (multi-char wildcard) or _ (single-char).
Examples: ‘a%’ (starts with a), ‘%or%’ (contains or).
SELECT * FROM Students WHERE FirstName LIKE 'K%';
SELECT * FROM sales WHERE date BETWEEN '2023-01-01' AND '2023-12-31';
SELECT * FROM Sales WHERE Date BETWEEN '01/12/2017' AND '01/01/2018';
SELECT * FROM Customers WHERE LastName BETWEEN 'Brooks' AND 'Gray';
SELECT * FROM products WHERE category IN ('Electronics', 'Books');
SELECT UPPER(name) FROM customers;
SELECT CEILING(25.3); -- 26
SELECT CURDATE(); -- e.g., 2025-08-20
SELECT MAX(salary) FROM employees;
SELECT COUNT(id) FROM orders WHERE status = 'shipped';
Which of the following are Aggregate Functions?
COUNT, SUM, MIN, MAX, AVG. Used for summaries.
SELECT COUNT(*) FROM Customers;
SELECT * FROM products ORDER BY price DESC, name ASC;
If you don’t specify ASC or DESC for an ORDER BY clause, the following is used by default:
ASC.
SELECT name, mark FROM Students ORDER BY mark DESC LIMIT 3;
Use TOP in SQL Server, ROWNUM in Oracle.
SELECT * FROM Customers ORDER BY FirstName DESC;
SELECT name, COUNT(name) FROM customers GROUP BY name;
GROUP BY aggregates subsets.
SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 60000;
Best Practice: Use WHERE for individual row filters to reduce data early.
Joins combine tables based on related columns. Types: INNER (matching), LEFT (all left + matching right), RIGHT (all right + matching left), FULL OUTER (all from both), SELF (table with itself), CROSS (Cartesian product). Set operations: UNION (combine, distinct), UNION ALL (with duplicates), INTERSECT (common), MINUS (first minus second).
Best Practices:
Simple JOIN diagram (ASCII):
Table A: ID | Name
Table B: ID | City
INNER JOIN: Matching IDs only
LEFT JOIN: All A + matching B (NULL for non-matches)
INNER JOIN:
SELECT customers.name, orders.amount
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;
What is JOIN used for?
Combines rows from multiple tables on related columns.
What is the most common type of join?
INNER JOIN (returns matching rows).
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;
Which of the following is NOT TRUE about the ON clause?
ON specifies join conditions/columns, eases readability, supports multi-way joins. All are true except if the question implies something false—clarify in interview.
Assume that Table A is joined to Table B. An inner join:
Displays rows where keys match.
SELECT * FROM faculty f
INNER JOIN division d ON f.division_id = d.id
INNER JOIN country c ON f.country_id = c.id;
SELECT a.name, b.name FROM employees a, employees b WHERE a.manager_id = b.id;
SELECT * FROM table1 CROSS JOIN table2;
SELECT * FROM set1 INTERSECT SELECT * FROM set2;
In relational algebra the UNION of two sets (set A and Set B) corresponds to
A OR B (combined, distinct). SELECTs must match columns/types/order.
What is the difference between UNION and UNION ALL?
UNION: distinct rows; UNION ALL: all rows (duplicates included). UNION ALL is faster.
SELECT name FROM searchers MINUS SELECT name FROM buyers;
Subqueries are nested queries in SELECT/FROM/WHERE. Types: scalar (single value), row (multiple), correlated (depends on outer). Use for filtering, calculations. CASE for conditional logic. Functions like UPPER, CEILING for data transformation.
Best Practices:
Subquery in WHERE:
SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);
CASE:
SELECT name,
CASE WHEN age < 18 THEN 'Minor' ELSE 'Adult' END AS status
FROM users;
One (or more) select statement whose return values are used in filtering conditions of the main query is called
Subquery (inner/nested query).
Subqueries can be nested in…
SELECT, INSERT, UPDATE, DELETE, or another subquery.
What row comparison operators can be used with a subquery?
IN, ANY, ALL, >, <, =.
A subquery that is used inside a subquery is called a
Nested subquery.
SELECT * FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e.department);
DDL: Defines structures (CREATE, ALTER, DROP). DML: Manipulates data (INSERT, UPDATE, DELETE, SELECT—sometimes DQL). DCL: Controls access (GRANT, REVOKE). TRUNCATE (DDL) removes all rows without logging.
Best Practices:
CREATE TABLE:
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL
);
INSERT:
INSERT INTO customers (name) VALUES ('John Doe');
What are different types of statements supported by SQL?
DDL (CREATE/ALTER/DROP), DML (INSERT/UPDATE/DELETE/SELECT), DCL (GRANT/REVOKE). SELECT sometimes in DQL.
Which of the following SQL statements are DDL
ALTER, CREATE, DROP, TRUNCATE TABLE.
DML includes the following SQL statements
SELECT, INSERT, UPDATE, DELETE.
Grant and Revoke commands are under
DCL.
Which of the following is not a DML statement?
COMMIT (transaction control, TCL).
Which SQL statement is used to insert new data in a database?
INSERT INTO.
When inserting data in a table do you have to specify the list of columns you are inserting values for?
No, if providing values for all columns in order. But best to specify for clarity.
INSERT INTO Customers VALUES (1, 'John', 'Doe');
INSERT INTO Customers (LastName) VALUES ('Hawkins');
CREATE TEMPORARY TABLE temp_customers SELECT * FROM customers LIMIT 10;
Drops at session end.
Which SQL statement is used to update data in a database?
UPDATE.
What is the keyword is used in an UPDATE query to modify the existing value?
SET.
UPDATE Customers SET LastName = 'Hawkins' WHERE LastName = 'Jackson';
Which SQL statement is used to delete data from a database?
DELETE.
DELETE FROM Customers WHERE FirstName = 'John';
The FROM SQL keyword is used to
Specify source table(s) in SELECT/UPDATE/DELETE/INSERT.
What is the difference between DELETE and TRUNCATE?
DELETE (DML): Row-by-row, loggable, WHERE clause, rollback possible. TRUNCATE (DDL): Bulk remove all rows, faster, no WHERE, resets auto-increment.
Which SQL statement is used to create a table in a database?
CREATE TABLE.
What is Collation in SQL?
Rules for comparing/sorting strings (e.g., case-sensitive). Character sets define symbols; collations define comparisons.
Example: Binary (simple encoding), case-insensitive.
CREATE TABLE persons (id INT AUTO_INCREMENT PRIMARY KEY);
What are valid constraints in MySQL?
NOT NULL, UNIQUE, PRIMARY KEY (UNIQUE + NOT NULL), FOREIGN KEY, CHECK, DEFAULT, INDEX.
Which of the following is NOT TRUE about constraints?
All are true: Constraints enforce data integrity.
The NOT NULL constraint enforces a column to not accept null values.
True.
An unique (non-key) field
UNIQUE constraint (allows multiples per table, unlike PRIMARY KEY).
CREATE TABLE persons (age INT CHECK (age >= 18));
What is the difference between UNIQUE and PRIMARY KEY constraints?
PRIMARY KEY: UNIQUE + NOT NULL, one per table. UNIQUE: Multiple per table, allows NULLs (in some DBMS).
DROP TABLE table_name;
What is the difference between DROP and TRUNCATE?
DROP removes table structure; TRUNCATE removes data only.
ALTER TABLE `order` ADD order_date DATE;
ALTER TABLE Customer CHANGE Address Addr VARCHAR(50);
ALTER TABLE addresses DROP COLUMN city;
Indexes speed queries like a book index. Clustered (reorders table), non-clustered (separate). Privileges: GRANT/REVOKE for access. SQL Injection: Malicious input exploiting queries.
Best Practices:
Index:
CREATE INDEX idx_name ON customers (name);
GRANT:
GRANT SELECT ON database.* TO 'user'@'localhost';
What are Indexes in SQL?
Speed retrieval. Duplicate allowed unless UNIQUE.
Best Practice: Update sparingly on insert-heavy tables.
What is the difference between clustered and non-clustered indexes? Which of the following statements are true?
Clustered: One per table, reorders data. Non-clustered: Multiple, separate structure. Clustered faster for reads; both true statements apply.
The statement for assigning access privileges is
GRANT/REVOKE.
How many types of Privileges are available in SQL?
System (e.g., CREATE TABLE), Object (e.g., SELECT on specific table).
List the various privileges that a user can grant to another user?
SELECT, INSERT, UPDATE, DELETE, EXECUTE, etc.
What is SQL Injection?
Code injection via input. Prevent with parameterized queries.
Example Vulnerability:
-- Bad: SELECT * FROM users WHERE id = '$input'; -- Input: ' OR '1'='1
Safe: Use placeholders.
Transactions ensure ACID: Atomicity (all or nothing), Consistency (valid states), Isolation (concurrent independence), Durability (persists after commit). Controls: COMMIT, ROLLBACK, SAVEPOINT. Locking prevents conflicts. Isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ (MySQL default), SERIALIZABLE.
Best Practices:
ACID Properties (Text):
| Isolation: Tx1 | Tx2 (no interference). |
Transaction:
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
What does the term ‘locking’ refer to?
Prevents concurrent changes/reads on data.
SAVEPOINT sp1;
-- Operations
ROLLBACK TO sp1;
A transaction completes its execution is said to be
Committed.
Consider the following code: START TRANSACTION /transaction body/ COMMIT; ROLLBACK; What does Rollback do?
Nothing—post-COMMIT changes are permanent.
What are valid properties of the transaction?
ACID.
What happens if autocommit is enabled?
Each statement auto-commits (default in MySQL).
What is the default isolation level used in MySQL?
REPEATABLE READ (prevents non-repeatable reads).
Views: Virtual tables from queries. Updatable if simple. Cursors: Row-by-row processing in procedures. Triggers: Auto-execute on events (INSERT/UPDATE/DELETE). Optimization: Efficient plans via indexes, stats.
Best Practices:
View:
CREATE VIEW active_customers AS SELECT * FROM customers WHERE status = 'active';
Trigger:
CREATE TRIGGER after_insert AFTER INSERT ON orders FOR EACH ROW
INSERT INTO audit_log VALUES (NEW.id, 'Inserted');
Which of these is also known as a virtual table in MySQL?
View.
Are views updatable using INSERT, DELETE or UPDATE?
Some are (simple, no aggregates/DISTINCT/GROUP BY). Conditions: One base table, includes PK, no subqueries.
What are the advantages of Views?
Simplify queries, limit access, hide complexity. Changes commit only after base table ops.
Does a View contain data?
No (normal view: query only). Yes for materialized (stored data, needs refresh).
DROP VIEW view_name;
Can a View based on another View?
Yes.
Inside a stored procedure you to iterate over a set of rows returned by a query using a
CURSOR. Steps: DECLARE, OPEN, FETCH, CLOSE, DEALLOCATE.
How do you call the process of finding a good strategy for processing a query?
Query optimization (considers plans, stats).
What is a trigger? or There are triggers for… or A trigger is applied to
Stored procedure auto-executing on DML/DDL events (INSERT/UPDATE/DELETE/CREATE/etc.). Components: Event, Action.
Example above.
Resources