SQL Language

SQL stands for Structured Query Language. It is the standard language used to communicate with relational databases. SQL was developed by IBM researchers Donald Chamberlin and Raymond Boyce in the early 1970s and was first commercially released by Oracle in 1979. SQL is used to create, read, update, and delete data stored in a relational database management system (RDBMS).

SQL is used by popular database systems like MySQL, PostgreSQL, Microsoft SQL Server, Oracle Database, SQLite, and MariaDB. While each system has its own dialect, core SQL syntax is standardized by ANSI/ISO. SQL is not just for developers — data analysts, business intelligence professionals, and data scientists all rely heavily on SQL to query and transform data.

DDL (Data Definition Language): Defines and modifies the structure of database objects like tables, indexes, views, and schemas. Changes are auto-committed and cannot be rolled back.

DML (Data Manipulation Language): Works with the actual data inside tables — inserting, updating, deleting, and retrieving records. These operations are part of transactions and can be rolled back.

DCL (Data Control Language): Controls user access and permissions on database objects. GRANT gives privileges, REVOKE takes them away.

TCL (Transaction Control Language): Manages database transactions to ensure data integrity. COMMIT saves all changes, ROLLBACK undoes them, SAVEPOINT sets a partial rollback point.

The CREATE TABLE statement defines a new table in the database. You specify the column names, their data types, and any constraints. Choosing the right data type is critical for data integrity and storage efficiency.

ALTER TABLE: Modify existing table structure. ALTER TABLE employees ADD COLUMN phone VARCHAR(15); — add column. ALTER TABLE employees DROP COLUMN phone; — remove column. ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12,2); — change data type. ALTER TABLE employees RENAME TO staff; — rename table.

DROP TABLE: Permanently deletes the table and all its data. DROP TABLE employees; — cannot be rolled back. DROP TABLE IF EXISTS employees; — no error if table doesn't exist.

TRUNCATE TABLE: Removes all rows from a table but keeps the table structure. Faster than DELETE (no row-by-row logging). TRUNCATE TABLE employees; Cannot be rolled back in most databases.

Constraints are rules enforced on columns to ensure data accuracy, integrity, and reliability. They prevent invalid data from being entered into the database. Constraints can be applied at the column level (inline) or table level (after all columns are defined).

Composite Primary Key: A primary key made of two or more columns together. Defined at table level: PRIMARY KEY (order_id, product_id). Used in junction/bridge tables for many-to-many relationships.

ON DELETE / ON UPDATE actions for Foreign Keys: CASCADE (automatically delete/update child rows), SET NULL (set FK column to NULL), SET DEFAULT (set FK to default value), RESTRICT (prevent delete/update if child exists), NO ACTION (similar to RESTRICT).

AUTO_INCREMENT / IDENTITY: MySQL: emp_id INT AUTO_INCREMENT PRIMARY KEY — automatically generates unique integer IDs. PostgreSQL uses SERIAL or GENERATED ALWAYS AS IDENTITY. SQL Server uses IDENTITY(1,1).

The SELECT statement is the most frequently used SQL command. It retrieves data from one or more tables. A SELECT query can filter rows (WHERE), sort results (ORDER BY), group data (GROUP BY), limit output (LIMIT), and combine tables (JOIN). The order of clauses matters — they must follow a specific sequence.

SELECT examples:
SELECT * FROM employees; — all columns, all rows.
SELECT first_name, salary FROM employees; — specific columns.
SELECT DISTINCT department FROM employees; — unique values only.
SELECT first_name AS "First Name", salary * 12 AS annual_salary FROM employees; — aliases and expressions.

WHERE clause operators: = (equal), != or <> (not equal), >, <, >=, <= (comparison), BETWEEN x AND y (range inclusive), IN (value1, value2) (matches any in list), NOT IN, LIKE 'pattern' (% = wildcard, _ = single char), IS NULL, IS NOT NULL, AND, OR, NOT.

ORDER BY: ORDER BY salary DESC (highest first). ORDER BY last_name ASC, first_name ASC (multiple columns). Default is ASC (ascending). NULL values are sorted first in ASC in most databases.

LIMIT and OFFSET: LIMIT 10 — return first 10 rows. LIMIT 10 OFFSET 20 — skip 20 rows, return next 10. Used for pagination. SQL Server uses: SELECT TOP 10. Oracle uses: FETCH FIRST 10 ROWS ONLY or ROWNUM.

INSERT INTO ... SELECT: Copy data from one table to another. INSERT INTO archive_employees SELECT * FROM employees WHERE hire_date < '2020-01-01'; — inserts results of a SELECT query directly into another table.

UPDATE with JOIN: Update a table based on values from another table. UPDATE e SET e.dept_name = d.name FROM employees e JOIN departments d ON e.dept_id = d.id; (SQL Server syntax). MySQL uses: UPDATE employees e JOIN departments d ON e.dept_id = d.id SET e.dept_name = d.name;

UPSERT (INSERT OR UPDATE): MySQL: INSERT INTO table ... ON DUPLICATE KEY UPDATE col=val; PostgreSQL: INSERT INTO table ... ON CONFLICT(id) DO UPDATE SET col=val; Inserts a new row if key doesn't exist, updates if it does.

JOINs combine rows from two or more tables based on a related column (usually a foreign key). JOINs are one of the most powerful and frequently used features of SQL, allowing you to query data spread across multiple tables in a normalized database.

INNER JOIN: Returns only rows where the join condition is met in both tables. Most commonly used join. Rows without a match in either table are excluded.

LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table and matching rows from the right. If no match, right table columns return NULL. Use to find records in left table that may or may not have related records in right table.

RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the right table and matching rows from the left. Mirror of LEFT JOIN. Less commonly used — most queries can be rewritten as LEFT JOIN by swapping table order.

FULL OUTER JOIN: Returns all rows from both tables. Unmatched rows from either side show NULL for the other's columns. Not supported in MySQL — simulate with UNION of LEFT and RIGHT JOIN.

Multiple JOINs: SELECT e.name, d.dept_name, l.city FROM employees e JOIN departments d ON e.dept_id = d.id JOIN locations l ON d.location_id = l.id; — chain JOINs to connect multiple tables.

Aggregate functions perform calculations on a set of rows and return a single value. They are used with SELECT to summarize data. When combined with GROUP BY, they calculate summaries for each group. HAVING filters these groups (like WHERE but for aggregated results).

COUNT DISTINCT: SELECT COUNT(DISTINCT dept_id) FROM employees; — counts unique department IDs only.

ROLLUP and CUBE: GROUP BY dept_id, job_role WITH ROLLUP — generates subtotals and grand total rows. GROUP BY CUBE(dept_id, job_role) — generates all possible grouping combinations. Available in MySQL, SQL Server, Oracle.

Window functions (SQL advanced): Unlike GROUP BY which collapses rows, window functions compute aggregate values while keeping all rows. OVER() clause defines the window. ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY salary DESC) — rank within department. Also: RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER(), AVG() OVER().

A subquery (also called an inner query or nested query) is a SELECT statement embedded inside another SQL statement. The inner query executes first and its result is used by the outer query. Subqueries can appear in WHERE, FROM, SELECT, or HAVING clauses.

Subquery in WHERE: SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); — finds all employees earning above the average salary. The subquery returns a single value (scalar subquery).

Subquery with IN: SELECT * FROM employees WHERE dept_id IN (SELECT dept_id FROM departments WHERE location = 'Mumbai'); — find employees in Mumbai departments.

Subquery with EXISTS: SELECT * FROM employees e WHERE EXISTS (SELECT 1 FROM orders o WHERE o.emp_id = e.emp_id); — find employees who have at least one order. EXISTS returns TRUE/FALSE. Often faster than IN for large datasets.

Correlated subquery: A subquery that references columns from the outer query. Executed once per row of the outer query. SELECT e.name, e.salary FROM employees e WHERE e.salary = (SELECT MAX(salary) FROM employees WHERE dept_id = e.dept_id); — finds the highest-paid employee in each department.

Subquery in FROM (Derived table): SELECT avg_data.dept_id, avg_data.avg_salary FROM (SELECT dept_id, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id) AS avg_data WHERE avg_data.avg_salary > 60000; — treats subquery result as a temporary table.

CTE (Common Table Expression) with WITH: More readable alternative to subqueries. WITH high_earners AS (SELECT * FROM employees WHERE salary > 80000) SELECT * FROM high_earners WHERE dept_id = 3; CTEs can be referenced multiple times and can be recursive.

Index types: Single-column index (most common), Composite index (multiple columns — order matters), Unique index (enforces uniqueness), Full-text index (for text search — MATCH AGAINST), Clustered index (physical order of rows, one per table — Primary Key is clustered by default), Non-clustered index (separate structure).

When to create indexes: On columns used in WHERE, JOIN ON, ORDER BY, GROUP BY. On foreign key columns. On high-cardinality columns (many unique values). Avoid over-indexing — indexes slow down INSERT/UPDATE/DELETE because index must be maintained.

Views — advantages: Security (hide sensitive columns — expose only what users need), Simplicity (complex joins written once, queried simply), Consistency (single source of truth for repeated queries), Abstraction (hide underlying table complexity).

Updatable views: Simple views on a single table can allow INSERT/UPDATE/DELETE. Views with GROUP BY, DISTINCT, aggregate functions, UNION, subqueries are not updatable. Use WITH CHECK OPTION to prevent inserts/updates that violate the view's WHERE condition.

Materialized Views: Store the query result physically — not recalculated every time. Must be refreshed manually or on schedule. Dramatically faster for complex aggregations. Supported in PostgreSQL, Oracle — not standard MySQL.

Stored procedures and functions are precompiled SQL code blocks stored in the database. They improve performance (compiled once, run many times), security (users call procedure without direct table access), and reusability. They can accept parameters and contain control flow logic (IF, LOOP, CASE).

Stored Procedure (MySQL):
DELIMITER //
CREATE PROCEDURE get_employees(IN dept INT, OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM employees WHERE dept_id = dept;
    SELECT * FROM employees WHERE dept_id = dept;
END //
DELIMITER ;
-- Call: CALL get_employees(3, @count); SELECT @count;

Stored Function (MySQL):
CREATE FUNCTION calc_bonus(salary DECIMAL(10,2)) RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN salary * 0.15;
END;
-- Use: SELECT first_name, calc_bonus(salary) FROM employees;

Procedure vs Function: Procedures can return multiple result sets and use IN/OUT/INOUT params. Functions must return exactly one value. Functions can be used inside SELECT; procedures cannot. Procedures can modify database state (INSERT/UPDATE/DELETE inside); functions generally should not.

Triggers: Automatically execute before or after INSERT, UPDATE, or DELETE events. CREATE TRIGGER before_insert_employee BEFORE INSERT ON employees FOR EACH ROW BEGIN SET NEW.hire_date = CURDATE(); END; — auto-sets hire date. Used for audit logging, validation, automatic calculations.

A transaction is a sequence of SQL operations executed as a single logical unit. Either all operations succeed and are committed, or all fail and are rolled back. Transactions ensure data integrity especially in concurrent environments. ACID properties define the guarantees a database must provide.

Transaction syntax: START TRANSACTION; (or BEGIN;) — starts a transaction. COMMIT; — saves all changes permanently. ROLLBACK; — undoes all changes since START TRANSACTION. SAVEPOINT sp1; — marks a partial rollback point. ROLLBACK TO SAVEPOINT sp1; — rolls back to savepoint only.

Bank transfer example: START TRANSACTION; UPDATE accounts SET balance = balance - 5000 WHERE acc_no = 'A001'; UPDATE accounts SET balance = balance + 5000 WHERE acc_no = 'A002'; COMMIT; — if any statement fails, ROLLBACK ensures neither account is affected.

Q1. What is the difference between DELETE, TRUNCATE, and DROP?
Ans: DELETE removes specific rows using WHERE — DML, can be rolled back, fires triggers, slow for large tables. TRUNCATE removes all rows — DDL, cannot be rolled back, does not fire triggers, resets AUTO_INCREMENT, faster than DELETE. DROP removes the entire table (structure + data) permanently — DDL, cannot be rolled back.

Q2. What is the difference between WHERE and HAVING?
Ans: WHERE filters individual rows before grouping — cannot use aggregate functions. HAVING filters groups after GROUP BY — can use aggregate functions like COUNT, SUM, AVG. WHERE is faster. You can use both: SELECT dept_id, AVG(salary) FROM employees WHERE is_active=TRUE GROUP BY dept_id HAVING AVG(salary) > 60000;

Q3. What is a primary key vs foreign key?
Ans: Primary key uniquely identifies each row in a table — must be NOT NULL and UNIQUE, only one per table. Foreign key is a column in one table that references the primary key of another table — establishes relationships between tables, enforces referential integrity, allows NULL (meaning no relationship).

Q4. What is normalization? Explain 1NF, 2NF, 3NF.
Ans: Normalization organizes data to reduce redundancy and improve integrity. 1NF — each column has atomic (indivisible) values, no repeating groups, each row is unique. 2NF — 1NF + every non-key column is fully dependent on the entire primary key (no partial dependencies). 3NF — 2NF + no transitive dependencies (non-key columns depend only on primary key, not on other non-key columns).

Q5. What is the difference between INNER JOIN and LEFT JOIN?
Ans: INNER JOIN returns only rows where a match exists in both tables — unmatched rows are excluded. LEFT JOIN returns all rows from the left table and matched rows from the right — if no match, right side columns are NULL. LEFT JOIN is used when you want all records from the main table regardless of whether a related record exists.

Q6. What is a subquery? What are its types?
Ans: A subquery is a SELECT statement inside another SQL statement. Types: Scalar subquery (returns one row, one column — used in SELECT or WHERE), Row subquery (returns one row, multiple columns), Column subquery (returns one column, multiple rows — used with IN/ANY/ALL), Table subquery (returns multiple rows and columns — used in FROM as derived table). Correlated subquery references the outer query and runs once per outer row.

Q7. How do you find duplicate records in SQL?
Ans: SELECT email, COUNT(*) as cnt FROM employees GROUP BY email HAVING COUNT(*) > 1; — finds emails that appear more than once. To delete duplicates keeping one: DELETE FROM employees WHERE emp_id NOT IN (SELECT MIN(emp_id) FROM employees GROUP BY email);

Q8. What is the difference between UNION and UNION ALL?
Ans: UNION combines results of two SELECT statements and removes duplicate rows — slower due to deduplication. UNION ALL combines results and keeps all duplicates — faster. Both require same number of columns and compatible data types. Use UNION ALL unless you specifically need to remove duplicates.

Q9. What is a self join? Give an example.
Ans: A self join joins a table to itself. Used for hierarchical data like employee-manager relationships. SELECT e.first_name AS employee, m.first_name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.emp_id; — lists each employee with their manager's name from the same table.

Q10. How do you find the second highest salary in SQL?
Ans: Method 1 (subquery): SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); Method 2 (LIMIT/OFFSET): SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1; Method 3 (window function): SELECT salary FROM (SELECT salary, DENSE_RANK() OVER(ORDER BY salary DESC) AS rnk FROM employees) t WHERE rnk = 2;

Q1. Which SQL command is used to retrieve data from a database?
a) GET b) FETCH c) SELECT d) EXTRACT
Answer: c) SELECT

Q2. Which clause is used to filter records in SQL?
a) FILTER b) HAVING c) WHERE d) LIMIT
Answer: c) WHERE — HAVING filters groups, WHERE filters rows.

Q3. Which constraint ensures a column cannot have NULL values?
a) UNIQUE b) NOT NULL c) DEFAULT d) CHECK
Answer: b) NOT NULL

Q4. What does the COUNT(*) function return?
a) Sum of all values b) Total number of rows including NULLs c) Average value d) Maximum value
Answer: b) Total number of rows including NULLs

Q5. Which JOIN returns all rows from both tables, with NULLs for unmatched rows?
a) INNER JOIN b) LEFT JOIN c) RIGHT JOIN d) FULL OUTER JOIN
Answer: d) FULL OUTER JOIN

Q6. Which SQL keyword is used to sort the result set?
a) SORT BY b) GROUP BY c) ORDER BY d) ARRANGE BY
Answer: c) ORDER BY — default is ASC (ascending).

Q7. What is the correct syntax for creating a view?
a) MAKE VIEW v AS SELECT ... b) CREATE VIEW v AS SELECT ... c) VIEW v CREATE AS SELECT ... d) NEW VIEW v SELECT ...
Answer: b) CREATE VIEW v AS SELECT ...

Q8. Which command permanently saves a transaction?
a) SAVE b) ROLLBACK c) COMMIT d) END
Answer: c) COMMIT

Q9. What does DISTINCT do in a SELECT statement?
a) Sorts results b) Filters NULL values c) Removes duplicate rows d) Groups rows
Answer: c) Removes duplicate rows — SELECT DISTINCT col FROM table;

Q10. Which aggregate function returns the highest value in a column?
a) TOP() b) HIGHEST() c) MAX() d) UPPER()
Answer: c) MAX()

Q11. What does the LIKE operator use for matching any sequence of characters?
a) * b) _ c) % d) ?
Answer: c) % — % matches zero or more characters. _ matches exactly one character.

Q12. Which of the following is NOT a DDL command?
a) CREATE b) ALTER c) INSERT d) DROP
Answer: c) INSERT — INSERT is a DML command (manipulates data, not structure).