I can provide you with 200 frequently asked SQL interview questions and answers for

freshers right here. However, I can't generate PDF files directly — but you can easily copy
this content and paste it into a Word document or use a free online tool to convert it to PDF.

Let me give you all 200 questions organized by topic.

200 SQL Interview Questions for Data

Analyst Freshers
Section 1: SQL Basics & Introduction (Questions 1-20)
Q1. What is SQL?

Answer: SQL stands for Structured Query Language. It is a standard programming language
used to manage and manipulate relational databases. SQL is used to create, read, update, and
delete data in databases.

Q2. What is a database?

Answer: A database is an organized collection of structured data stored electronically. It

allows users to easily access, manage, and update information. Examples include MySQL,
PostgreSQL, Oracle, and SQL Server.

Q3. What is a relational database?

Answer: A relational database stores data in tables (relations) consisting of rows and
columns. Tables can be linked using keys, allowing relationships between different data sets.
Examples include MySQL, PostgreSQL, and Oracle.

Q4. What is a table in SQL?

Answer: A table is a collection of related data organized in rows (records) and columns
(fields). Each table has a unique name and represents a specific entity like employees,
products, or orders.

Q5. What is a row in SQL?

Answer: A row (also called a record or tuple) is a single horizontal entry in a table that
contains data for all columns. Each row represents one instance of the entity.

Q6. What is a column in SQL?

Answer: A column (also called a field or attribute) is a vertical entity in a table that contains
specific information about every record. For example, "Name" or "Age" columns.
Q7. What is a field in SQL?

Answer: A field is the intersection of a row and column, containing a single data value. For
example, the name "John" in the Name column of a specific row.

Q8. What are the different types of SQL commands?

Answer:

 DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE

 DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
 DCL (Data Control Language): GRANT, REVOKE
 TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT
Q9. What is DDL?

Answer: DDL stands for Data Definition Language. It includes commands used to define and
modify database structure like CREATE (create objects), ALTER (modify objects), DROP
(delete objects), and TRUNCATE (remove all records).

Q10. What is DML?

Answer: DML stands for Data Manipulation Language. It includes commands used to
manipulate data within tables: SELECT (retrieve data), INSERT (add data), UPDATE
(modify data), and DELETE (remove data).

Q11. What is DCL?

Answer: DCL stands for Data Control Language. It includes commands that control access to
data: GRANT (give permissions) and REVOKE (remove permissions).

Q12. What is TCL?

Answer: TCL stands for Transaction Control Language. It manages transactions in

databases: COMMIT (save changes), ROLLBACK (undo changes), and SAVEPOINT (set
checkpoint).

Q13. What is a query?

Answer: A query is a request for data or information from a database. The SELECT
statement is the most common query used to retrieve data from one or more tables.

Q14. What is the syntax of a basic SELECT statement?

Answer:

SELECT column1, column2

FROM table_name;

To select all columns: SELECT * FROM table_name;

Q15. What is the difference between SQL and MySQL?

Answer:

 SQL is a standard query language used to communicate with databases

 MySQL is a relational database management system (RDBMS) that uses SQL as its query
language
Q16. What are SQL data types?

Answer: Common SQL data types include:

 Numeric: INT, FLOAT, DECIMAL

 String: VARCHAR, CHAR, TEXT
 Date/Time: DATE, TIME, DATETIME, TIMESTAMP
 Boolean: BOOLEAN or BIT
Q17. What is VARCHAR?

Answer: VARCHAR (Variable Character) is a data type that stores variable-length character
strings. You specify a maximum length, but it only uses storage for actual characters.
Example: VARCHAR(50) can store up to 50 characters.

Q18. What is the difference between CHAR and VARCHAR?

Answer:

 CHAR is fixed-length; it always uses the specified storage space

 VARCHAR is variable-length; it only uses space needed for actual data
 CHAR is faster for fixed-length data; VARCHAR is more space-efficient for variable data
Q19. What is INT data type?

Answer: INT (Integer) is a numeric data type that stores whole numbers without decimal
points. It typically stores values from -2,147,483,648 to 2,147,483,647.

Q20. What is the DATE data type?

Answer: DATE stores date values in YYYY-MM-DD format. It includes year, month, and
day but no time component. Example: '2024-01-15'.

Section 2: Basic SELECT Queries (Questions 21-40)

Q21. How do you select all columns from a table?

Answer:

SELECT * FROM table_name;

The asterisk (*) is a wildcard that represents all columns.

Q22. How do you select specific columns from a table?

Answer:

SELECT column1, column2, column3

FROM table_name;

List the column names separated by commas.

Q23. What is the WHERE clause?

Answer: The WHERE clause filters records based on specified conditions. Only rows that
meet the condition are returned.

SELECT * FROM employees

WHERE department = 'Sales';
Q24. How do you use multiple conditions in WHERE clause?

Answer: Use AND, OR operators to combine conditions:

SELECT * FROM employees

WHERE department = 'Sales' AND salary > 50000;
SELECT * FROM employees
WHERE department = 'Sales' OR department = 'Marketing';
Q25. What is the AND operator?

Answer: AND combines multiple conditions where ALL conditions must be true for a row to
be selected.

SELECT * FROM products

WHERE price > 100 AND category = 'Electronics';
Q26. What is the OR operator?

Answer: OR combines multiple conditions where AT LEAST ONE condition must be true
for a row to be selected.

SELECT * FROM products

WHERE category = 'Electronics' OR category = 'Appliances';
Q27. What is the NOT operator?

Answer: NOT negates a condition, returning rows where the condition is false.

SELECT * FROM employees

WHERE NOT department = 'HR';
Q28. What is the ORDER BY clause?

Answer: ORDER BY sorts the result set in ascending (ASC) or descending (DESC) order.

SELECT * FROM employees

ORDER BY salary DESC;
SELECT * FROM employees
ORDER BY name ASC;
Q29. What is the default sort order in ORDER BY?

Answer: The default sort order is ascending (ASC). If you don't specify ASC or DESC,
results are sorted in ascending order.

Q30. How do you sort by multiple columns?

Answer:

SELECT * FROM employees

ORDER BY department ASC, salary DESC;

This sorts first by department (ascending), then by salary (descending) within each
department.

Q31. What is the DISTINCT keyword?

Answer: DISTINCT removes duplicate values from the result set.

SELECT DISTINCT department FROM employees;

This returns each unique department name only once.

Q32. What is the LIMIT clause?

Answer: LIMIT restricts the number of rows returned.

SELECT * FROM employees

LIMIT 10;

This returns only the first 10 rows.

Q33. What is the OFFSET clause?

Answer: OFFSET skips a specified number of rows before returning results.

SELECT * FROM employees

LIMIT 10 OFFSET 20;

This skips 20 rows and returns the next 10 rows.

Q34. What is an alias in SQL?

Answer: An alias gives a table or column a temporary name for readability.

SELECT first_name AS fname, last_name AS lname

FROM employees AS emp;
Q35. What is the LIKE operator?

Answer: LIKE is used for pattern matching with wildcards:

 % matches any sequence of characters

 _ matches any single character
SELECT * FROM employees
WHERE name LIKE 'J%'; -- Names starting with J
Q36. What are wildcards in SQL?

Answer: Wildcards are special characters used with LIKE:

 % - Represents zero or more characters

 _ - Represents exactly one character
WHERE name LIKE 'A%' -- Starts with A
WHERE name LIKE '%son' -- Ends with son
WHERE name LIKE '_ohn' -- 4-letter name ending in ohn
Q37. What is the IN operator?

Answer: IN checks if a value matches any value in a list.

SELECT * FROM employees

WHERE department IN ('Sales', 'Marketing', 'HR');

This is equivalent to multiple OR conditions.

Q38. What is the BETWEEN operator?

Answer: BETWEEN selects values within a given range (inclusive).

SELECT * FROM employees

WHERE salary BETWEEN 40000 AND 60000;

This includes both 40000 and 60000.

Q39. What is the IS NULL operator?

Answer: IS NULL checks for NULL (missing/unknown) values.

SELECT * FROM employees

WHERE phone_number IS NULL;

Note: You cannot use = NULL; you must use IS NULL.

Q40. What is the IS NOT NULL operator?

Answer: IS NOT NULL returns rows where the value is not NULL.

SELECT * FROM employees

WHERE phone_number IS NOT NULL;
Section 3: SQL Functions (Questions 41-65)
Q41. What are aggregate functions?

Answer: Aggregate functions perform calculations on multiple rows and return a single
value:

 COUNT() - counts rows

 SUM() - adds values
 AVG() - calculates average
 MIN() - finds minimum
 MAX() - finds maximum
Q42. What is the COUNT() function?

Answer: COUNT() returns the number of rows.

SELECT COUNT(*) FROM employees; -- All rows

SELECT COUNT(department) FROM employees; -- Non-NULL values
SELECT COUNT(DISTINCT department) FROM employees; -- Unique values
Q43. What is the SUM() function?

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

SELECT SUM(salary) FROM employees;

SELECT SUM(salary) FROM employees WHERE department = 'Sales';
Q44. What is the AVG() function?

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

SELECT AVG(salary) FROM employees;

SELECT AVG(salary) AS average_salary FROM employees;
Q45. What is the MIN() function?

Answer: MIN() returns the smallest value in a column.

SELECT MIN(salary) FROM employees;

SELECT MIN(hire_date) FROM employees; -- Earliest date
Q46. What is the MAX() function?

Answer: MAX() returns the largest value in a column.

SELECT MAX(salary) FROM employees;

SELECT MAX(hire_date) FROM employees; -- Latest date
Q47. What is the GROUP BY clause?

Answer: GROUP BY groups rows with the same values and is often used with aggregate
functions.

SELECT department, COUNT(*) as employee_count

FROM employees
GROUP BY department;
Q48. What is the HAVING clause?

Answer: HAVING filters groups created by GROUP BY (like WHERE but for groups).

SELECT department, AVG(salary) as avg_salary

FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;
Q49. What is the difference between WHERE and HAVING?

Answer:

 WHERE filters individual rows before grouping

 HAVING filters groups after GROUP BY is applied
 WHERE cannot use aggregate functions; HAVING can
SELECT department, COUNT(*)
FROM employees
WHERE hire_date > '2020-01-01' -- Filter rows first
GROUP BY department
HAVING COUNT(*) > 5; -- Filter groups after
Q50. What is the UPPER() function?

Answer: UPPER() converts a string to uppercase.

SELECT UPPER(name) FROM employees;

-- 'john' becomes 'JOHN'
Q51. What is the LOWER() function?

Answer: LOWER() converts a string to lowercase.

SELECT LOWER(name) FROM employees;

-- 'JOHN' becomes 'john'
Q52. What is the LENGTH() function?

Answer: LENGTH() returns the number of characters in a string.

SELECT name, LENGTH(name) as name_length

FROM employees;
Q53. What is the SUBSTRING() function?

Answer: SUBSTRING() extracts a portion of a string.

SELECT SUBSTRING(name, 1, 3) FROM employees;

-- Extracts first 3 characters
Q54. What is the CONCAT() function?

Answer: CONCAT() combines two or more strings.

SELECT CONCAT(first_name, ' ', last_name) as full_name

FROM employees;
Q55. What is the TRIM() function?

Answer: TRIM() removes leading and trailing spaces from a string.

SELECT TRIM(name) FROM employees;

-- ' John ' becomes 'John'
Q56. What is the ROUND() function?

Answer: ROUND() rounds a number to a specified number of decimal places.

SELECT ROUND(salary, 2) FROM employees;

SELECT ROUND(15.789, 1); -- Returns 15.8
Q57. What is the CEIL() or CEILING() function?

Answer: CEIL() rounds a number up to the nearest integer.

SELECT CEIL(4.2); -- Returns 5

SELECT CEIL(4.9); -- Returns 5
Q58. What is the FLOOR() function?

Answer: FLOOR() rounds a number down to the nearest integer.

SELECT FLOOR(4.9); -- Returns 4

SELECT FLOOR(4.2); -- Returns 4
Q59. What is the NOW() function?

Answer: NOW() returns the current date and time.

SELECT NOW(); -- Returns '2024-01-15 14:30:00'

Q60. What is the CURDATE() function?

Answer: CURDATE() returns the current date only.

SELECT CURDATE(); -- Returns '2024-01-15'

Q61. What is the YEAR() function?

Answer: YEAR() extracts the year from a date.

SELECT YEAR(hire_date) FROM employees;

SELECT YEAR('2024-01-15'); -- Returns 2024
Q62. What is the MONTH() function?

Answer: MONTH() extracts the month from a date.

SELECT MONTH(hire_date) FROM employees;

SELECT MONTH('2024-01-15'); -- Returns 1
Q63. What is the DAY() function?

Answer: DAY() extracts the day from a date.

SELECT DAY(hire_date) FROM employees;
SELECT DAY('2024-01-15'); -- Returns 15
Q64. What is the DATEDIFF() function?

Answer: DATEDIFF() returns the difference between two dates.

SELECT DATEDIFF('2024-01-15', '2024-01-01'); -- Returns 14 days

SELECT DATEDIFF(NOW(), hire_date) FROM employees;
Q65. What is the COALESCE() function?

Answer: COALESCE() returns the first non-NULL value from a list.

SELECT COALESCE(phone, mobile, 'No Number') FROM employees;

If phone is NULL, it checks mobile; if both are NULL, returns 'No Number'.

Section 4: Joins (Questions 66-90)

Q66. What is a JOIN in SQL?

Answer: A JOIN combines rows from two or more tables based on a related column. It
allows you to retrieve data spread across multiple tables in a single query.

Q67. What are the types of JOINs?

Answer:

 INNER JOIN - Returns matching rows from both tables

 LEFT JOIN - Returns all rows from left table + matching from right
 RIGHT JOIN - Returns all rows from right table + matching from left
 FULL OUTER JOIN - Returns all rows from both tables
 CROSS JOIN - Returns Cartesian product of both tables
 SELF JOIN - Joins a table with itself
Q68. What is INNER JOIN?

Answer: INNER JOIN returns only rows where there is a match in both tables.

SELECT [Link], departments.dept_name

FROM employees
INNER JOIN departments ON employees.dept_id = [Link];
Q69. What is LEFT JOIN?

Answer: LEFT JOIN returns all rows from the left table and matched rows from the right
table. Unmatched rows from the right table show NULL.

SELECT [Link], departments.dept_name

FROM employees
LEFT JOIN departments ON employees.dept_id = [Link];
Q70. What is RIGHT JOIN?

Answer: RIGHT JOIN returns all rows from the right table and matched rows from the left
table. Unmatched rows from the left table show NULL.

SELECT [Link], departments.dept_name

FROM employees
RIGHT JOIN departments ON employees.dept_id = [Link];
Q71. What is FULL OUTER JOIN?

Answer: FULL OUTER JOIN returns all rows from both tables. Where there's no match,
NULL is shown.

SELECT [Link], departments.dept_name

FROM employees
FULL OUTER JOIN departments ON employees.dept_id = [Link];
Q72. What is CROSS JOIN?

Answer: CROSS JOIN returns the Cartesian product — every row from the first table
combined with every row from the second table.

SELECT * FROM colors CROSS JOIN sizes;

If colors has 3 rows and sizes has 4 rows, result has 12 rows.

Q73. What is SELF JOIN?

Answer: A SELF JOIN joins a table with itself. Useful for hierarchical data or comparing
rows within the same table.

SELECT [Link] as employee, [Link] as manager

FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id;
Q74. What is the difference between INNER JOIN and LEFT JOIN?

Answer:

 INNER JOIN returns only matching rows from both tables

 LEFT JOIN returns ALL rows from the left table, plus matching rows from the right table
(NULL for non-matches)
Q75. What is the difference between JOIN and UNION?

Answer:

 JOIN combines columns from multiple tables horizontally (adds more columns)
 UNION combines rows from multiple queries vertically (adds more rows)
Q76. What is UNION?

Answer: UNION combines results from two or more SELECT statements, removing
duplicates.
SELECT name FROM employees
UNION
SELECT name FROM contractors;

Both queries must have the same number of columns with compatible data types.

Q77. What is UNION ALL?

Answer: UNION ALL combines results from multiple SELECT statements including
duplicates (faster than UNION).

SELECT name FROM employees

UNION ALL
SELECT name FROM contractors;
Q78. What is the difference between UNION and UNION ALL?

Answer:

 UNION removes duplicate rows (slower)

 UNION ALL keeps all rows including duplicates (faster)
Q79. Can you JOIN more than two tables?

Answer: Yes, you can join multiple tables:

SELECT [Link], d.dept_name, [Link]

FROM employees e
JOIN departments d ON e.dept_id = [Link]
JOIN locations l ON d.location_id = [Link];
Q80. What is a natural join?

Answer: A NATURAL JOIN automatically joins tables based on columns with the same
name.

SELECT * FROM employees

NATURAL JOIN departments;

Not recommended as it can produce unexpected results if column names change.

Q81. What is the ON clause in JOIN?

Answer: The ON clause specifies the join condition — which columns to use for matching
rows between tables.

SELECT * FROM orders

JOIN customers ON orders.customer_id = [Link];
Q82. What is the USING clause in JOIN?

Answer: USING is a shorthand when join columns have the same name in both tables.

SELECT * FROM orders

JOIN customers USING (customer_id);
Equivalent to: ON orders.customer_id = customers.customer_id

Q83. How do you find employees without a department using JOIN?

Answer:

SELECT [Link]
FROM employees
LEFT JOIN departments ON employees.dept_id = [Link]
WHERE [Link] IS NULL;
Q84. How do you find common records between two tables?

Answer: Use INNER JOIN or INTERSECT:

-- Using INNER JOIN

SELECT a.* FROM table1 a
INNER JOIN table2 b ON [Link] = [Link];
-- Using INTERSECT
SELECT * FROM table1
INTERSECT
SELECT * FROM table2;
Q85. What is INTERSECT?

Answer: INTERSECT returns only rows that appear in both result sets.

SELECT name FROM employees

INTERSECT
SELECT name FROM managers;
Q86. What is EXCEPT or MINUS?

Answer: EXCEPT (or MINUS in Oracle) returns rows from the first query that are not in the
second query.

SELECT name FROM all_employees

EXCEPT
SELECT name FROM terminated_employees;
Q87. Write a query to find duplicate records using JOIN.

Answer:

SELECT a.*
FROM employees a
JOIN employees b ON [Link] = [Link] AND [Link] != [Link];
Q88. What happens when JOIN condition is missing?

Answer: Without a join condition, you get a CROSS JOIN (Cartesian product), which
returns every combination of rows from both tables.

Q89. Can you use multiple conditions in JOIN?

Answer: Yes, use AND or OR:

SELECT * FROM orders o
JOIN products p ON o.product_id = [Link] AND o.order_date > '2024-01-01';
Q90. How do you join tables with different column names?

Answer: Use the ON clause specifying the different column names:

SELECT * FROM employees e

JOIN departments d ON e.department_id = d.dept_id;

Section 5: Keys and Constraints (Questions 91-115)

Q91. What is a Primary Key?

Answer: A Primary Key uniquely identifies each record in a table. It cannot contain NULL
values and must be unique. Each table can have only one primary key.

CREATE TABLE employees (

id INT PRIMARY KEY,
name VARCHAR(50)
);
Q92. What is a Foreign Key?

Answer: A Foreign Key is a column that references the Primary Key in another table. It
creates a link between tables and enforces referential integrity.

CREATE TABLE orders (

order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
Q93. What is a Unique Key?

Answer: A Unique Key ensures all values in a column are different. Unlike Primary Key, it
can contain ONE NULL value and a table can have multiple unique keys.

CREATE TABLE employees (

id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE
);
Q94. What is the difference between Primary Key and Unique Key?

Answer:

Primary Key Unique Key

Only one per table Multiple allowed per table

Cannot be NULL Can have one NULL value

Creates clustered index Creates non-clustered index

 Primary Key Unique Key

Uniquely identifies records Just ensures uniqueness

Q95. What is a Composite Key?

Answer: A Composite Key uses two or more columns together to uniquely identify records.

CREATE TABLE order_items (

order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id)
);
Q96. What is a Candidate Key?

Answer: A Candidate Key is any column or combination of columns that could serve as a
primary key (unique and not null). A table may have multiple candidate keys, but only one is
chosen as the primary key.

Q97. What is an Alternate Key?

Answer: An Alternate Key is a candidate key that was not selected as the primary key. It can
still uniquely identify records.

Q98. What is a Super Key?

Answer: A Super Key is any set of columns that uniquely identifies rows. It may include
additional columns beyond what's necessary. All candidate keys are super keys, but not vice
versa.

Q99. What are constraints in SQL?

Answer: Constraints are rules enforced on data columns to maintain data integrity:

 NOT NULL
 UNIQUE
 PRIMARY KEY
 FOREIGN KEY
 CHECK
 DEFAULT
Q100. What is the NOT NULL constraint?

Answer: NOT NULL ensures a column cannot have NULL values.

CREATE TABLE employees (

id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
Q101. What is the CHECK constraint?

Answer: CHECK limits the values that can be placed in a column based on a condition.

CREATE TABLE employees (

id INT PRIMARY KEY,
age INT CHECK (age >= 18)
);
Q102. What is the DEFAULT constraint?

Answer: DEFAULT sets a default value for a column when no value is specified.

CREATE TABLE orders (

id INT PRIMARY KEY,
order_date DATE DEFAULT CURDATE(),
status VARCHAR(20) DEFAULT 'Pending'
);
Q103. What is referential integrity?

Answer: Referential integrity ensures that relationships between tables remain consistent. A
foreign key value must match an existing primary key value or be NULL.

Q104. What happens when you delete a row referenced by a Foreign Key?

Answer: Depending on the defined action:

 RESTRICT/NO ACTION - Prevents deletion

 CASCADE - Deletes related rows
 SET NULL - Sets foreign key to NULL
 SET DEFAULT - Sets foreign key to default value
Q105. What is ON DELETE CASCADE?

Answer: When a parent record is deleted, all related child records are automatically deleted.

FOREIGN KEY (dept_id) REFERENCES departments(id) ON DELETE CASCADE

Q106. What is ON UPDATE CASCADE?

Answer: When a parent key is updated, all related foreign keys are automatically updated.

FOREIGN KEY (dept_id) REFERENCES departments(id) ON UPDATE CASCADE

Q107. Can a table have multiple Primary Keys?

Answer: No, a table can have only ONE primary key. However, that primary key can be a
composite key made up of multiple columns.

Q108. Can a Primary Key be NULL?

Answer: No, Primary Key columns cannot contain NULL values. This is a fundamental
property of primary keys.
Q109. Can a Foreign Key be NULL?

Answer: Yes, a foreign key can be NULL (unless NOT NULL constraint is added). NULL
indicates no relationship for that record.

Q110. Can a Foreign Key reference a non-Primary Key?

Answer: Yes, a foreign key can reference any column with a UNIQUE constraint, not just
primary keys.

Q111. How do you add a Primary Key to an existing table?

Answer:

ALTER TABLE employees

ADD PRIMARY KEY (id);
Q112. How do you add a Foreign Key to an existing table?

Answer:

ALTER TABLE orders

ADD FOREIGN KEY (customer_id) REFERENCES customers(id);
Q113. How do you drop a constraint?

Answer:

ALTER TABLE employees

DROP CONSTRAINT constraint_name;
-- For Primary Key
ALTER TABLE employees
DROP PRIMARY KEY;
Q114. What is a surrogate key?

Answer: A surrogate key is an artificial key (usually auto-generated number) created to serve
as a primary key. It has no business meaning, unlike natural keys.

Q115. What is a natural key?

Answer: A natural key is a key that has business meaning in the real world, like Social
Security Number or Email. It exists naturally in the data.

Section 6: Data Manipulation - INSERT, UPDATE,

DELETE (Questions 116-135)
Q116. What is the INSERT statement?

Answer: INSERT adds new records to a table.

INSERT INTO employees (name, department, salary)

VALUES ('John Smith', 'Sales', 50000);
Q117. How do you insert multiple rows at once?

Answer:

INSERT INTO employees (name, department)

VALUES
('John', 'Sales'),
('Jane', 'Marketing'),
('Bob', 'HR');
Q118. How do you insert data from another table?

Answer:

INSERT INTO employees_backup

SELECT * FROM employees WHERE department = 'Sales';
Q119. What is the UPDATE statement?

Answer: UPDATE modifies existing records in a table.

UPDATE employees
SET salary = 55000
WHERE name = 'John Smith';
Q120. How do you update multiple columns?

Answer:

UPDATE employees
SET salary = 60000, department = 'Marketing'
WHERE id = 101;
Q121. What happens if you forget WHERE in UPDATE?

Answer: ALL rows in the table will be updated. This is a critical mistake:

UPDATE employees SET salary = 0; -- Updates EVERY employee!

Always use WHERE to target specific rows.

Q122. What is the DELETE statement?

Answer: DELETE removes records from a table.

DELETE FROM employees

WHERE id = 101;
Q123. What happens if you forget WHERE in DELETE?

Answer: ALL rows in the table will be deleted:

DELETE FROM employees; -- Deletes ALL records!

Always use WHERE to target specific rows.

Q124. What is the difference between DELETE and TRUNCATE?

Answer:

DELETE TRUNCATE

DML command DDL command

Can use WHERE clause Cannot use WHERE

Slower (row by row) Faster (deallocates pages)

Can be rolled back Cannot be rolled back

Fires triggers Does not fire triggers

Keeps identity value Resets identity value

Q125. What is TRUNCATE?

Answer: TRUNCATE removes all rows from a table quickly without logging individual row
deletions.

TRUNCATE TABLE employees;

Q126. What is the difference between TRUNCATE and DROP?

Answer:

 TRUNCATE removes all rows but keeps table structure

 DROP removes the entire table (structure and data)
Q127. How do you delete duplicate rows keeping one?

Answer:

DELETE FROM employees

WHERE id NOT IN (
SELECT MIN(id)
FROM employees
GROUP BY name, department
);
Q128. How do you update using values from another table?

Answer:

UPDATE employees e
SET salary = (SELECT avg_salary FROM salary_grades WHERE grade = [Link]);
-- Or using JOIN
UPDATE employees e
JOIN salary_grades s ON [Link] = [Link]
SET [Link] = s.avg_salary;
Q129. Can you use ORDER BY with UPDATE?

Answer: In some databases like MySQL, yes:

UPDATE employees
SET bonus = 1000
ORDER BY hire_date
LIMIT 5;

This updates the 5 earliest hired employees.

Q130. Can you use LIMIT with DELETE?

Answer: In MySQL, yes:

DELETE FROM logs

ORDER BY created_at
LIMIT 100;

This deletes the 100 oldest log entries.

Q131. What is INSERT IGNORE?

Answer: INSERT IGNORE (MySQL) inserts rows and ignores errors like duplicate keys:

INSERT IGNORE INTO employees (id, name)

VALUES (1, 'John');

If id=1 exists, the row is silently skipped.

Q132. What is REPLACE INTO?

Answer: REPLACE (MySQL) inserts a new row or replaces an existing row if a duplicate
key is found:

REPLACE INTO employees (id, name, salary)

VALUES (1, 'John', 55000);
Q133. What is UPSERT?

Answer: UPSERT is INSERT or UPDATE — insert if new, update if exists. Different

databases use different syntax:

-- MySQL
INSERT INTO employees (id, name, salary)
VALUES (1, 'John', 50000)
ON DUPLICATE KEY UPDATE salary = 50000;
Q134. How do you copy a table with data?

Answer:

CREATE TABLE employees_copy AS

SELECT * FROM employees;
Q135. How do you copy a table structure without data?

Answer:

CREATE TABLE employees_copy AS

SELECT * FROM employees WHERE 1=0;
-- Or in some databases
CREATE TABLE employees_copy LIKE employees;

Section 7: Table Creation and Modification (Questions

136-155)
Q136. What is the CREATE TABLE statement?

Answer: CREATE TABLE creates a new table in the database.

CREATE TABLE employees (

id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
department VARCHAR(30),
salary DECIMAL(10,2),
hire_date DATE
);
Q137. What is the ALTER TABLE statement?

Answer: ALTER TABLE modifies an existing table structure.

ALTER TABLE employees ADD email VARCHAR(100);

ALTER TABLE employees MODIFY salary DECIMAL(12,2);
ALTER TABLE employees DROP COLUMN email;
Q138. How do you add a column to a table?

Answer:

ALTER TABLE employees

ADD email VARCHAR(100);
Q139. How do you delete a column from a table?

Answer:

ALTER TABLE employees

DROP COLUMN email;
Q140. How do you modify a column's data type?

Answer:

-- MySQL
ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(12,2);
-- SQL Server
ALTER TABLE employees
ALTER COLUMN salary DECIMAL(12,2);
Q141. How do you rename a column?

Answer:

-- MySQL
ALTER TABLE employees
CHANGE old_name new_name VARCHAR(50);
-- SQL Server
EXEC sp_rename 'employees.old_name', 'new_name', 'COLUMN';
Q142. How do you rename a table?

Answer:

-- MySQL
RENAME TABLE old_table TO new_table;
-- SQL Server
EXEC sp_rename 'old_table', 'new_table';
-- Standard SQL
ALTER TABLE old_table RENAME TO new_table;
Q143. What is the DROP TABLE statement?

Answer: DROP TABLE permanently deletes a table and all its data.

DROP TABLE employees;

DROP TABLE IF EXISTS employees; -- Prevents error if table doesn't exist
Q144. What is the difference between DROP and DELETE?

Answer:

 DROP removes the entire table (structure + data)

 DELETE removes rows only, keeps table structure
Q145. What is AUTO_INCREMENT?

Answer: AUTO_INCREMENT automatically generates a unique number for each new row.

CREATE TABLE employees (

id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50)
);
Q146. What is IDENTITY in SQL Server?

Answer: IDENTITY is SQL Server's version of auto-increment.

CREATE TABLE employees (

id INT IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(50)
);

IDENTITY(1,1) means start at 1, increment by 1.

Q147. How do you create a table from another table?

Answer:

-- With data
CREATE TABLE new_table AS
SELECT * FROM existing_table;
-- Without data
CREATE TABLE new_table AS
SELECT * FROM existing_table WHERE 1=0;
Q148. What is a temporary table?

Answer: A temporary table exists only for the duration of a session or transaction.

CREATE TEMPORARY TABLE temp_results (

id INT,
value VARCHAR(50)
);

It's automatically deleted when the session ends.

Q149. How do you check if a table exists?

Answer:

-- MySQL
SHOW TABLES LIKE 'employees';
-- SQL Server
IF OBJECT_ID('employees', 'U') IS NOT NULL
PRINT 'Table exists';
-- Using information_schema
SELECT * FROM information_schema.tables
WHERE table_name = 'employees';
Q150. What is information_schema?

Answer: Information_schema is a metadata database containing information about all tables,

columns, constraints, and other database objects.

SELECT column_name, data_type

FROM information_schema.columns
WHERE table_name = 'employees';
Q151. How do you see the structure of a table?

Answer:

-- MySQL
DESCRIBE employees;
-- or
SHOW COLUMNS FROM employees;
-- SQL Server
EXEC sp_columns employees;
Q152. What is a schema in SQL?

Answer: A schema is a container that holds database objects like tables, views, and
procedures. It helps organize and manage objects.

CREATE SCHEMA sales;

CREATE TABLE [Link] (...);
Q153. How do you add a default value to an existing column?

Answer:

ALTER TABLE employees

ALTER COLUMN status SET DEFAULT 'Active';
Q154. How do you remove a default value from a column?

Answer:

ALTER TABLE employees

ALTER COLUMN status DROP DEFAULT;
Q155. What is the difference between CHAR(10) and VARCHAR(10)?

Answer:

 CHAR(10) always stores 10 characters (pads with spaces)

 VARCHAR(10) stores up to 10 characters (uses actual length)

CHAR is faster for fixed-length data; VARCHAR saves space for variable data.

Section 8: Subqueries (Questions 156-175)

Q156. What is a subquery?

Answer: A subquery is a query nested inside another query. It can return single values,
multiple values, or tables.

SELECT name FROM employees

WHERE salary > (SELECT AVG(salary) FROM employees);
Q157. What is the difference between a subquery and a JOIN?

Answer:

 Subquery is a nested query that executes first

 JOIN combines tables horizontally based on conditions
 JOINs are generally more efficient for combining related data
 Subqueries are better for comparisons and filtering
Q158. What is a correlated subquery?

Answer: A correlated subquery references columns from the outer query and executes once
for each row of the outer query.
SELECT name, salary FROM employees e1
WHERE salary > (
SELECT AVG(salary) FROM employees e2
WHERE [Link] = [Link]
);
Q159. What is the difference between correlated and non-correlated subquery?

Answer:

 Non-correlated: Executes independently once, then outer query uses result

 Correlated: Executes once for each row of outer query, references outer columns
Q160. Where can subqueries be used?

Answer: Subqueries can appear in:

 WHERE clause
 FROM clause (derived table)
 SELECT clause (scalar subquery)
 HAVING clause
Q161. What is a scalar subquery?

Answer: A scalar subquery returns exactly one value (one row, one column).

SELECT name,
(SELECT dept_name FROM departments WHERE id = employees.dept_id) as
department
FROM employees;
Q162. How do you use subquery in WHERE clause?

Answer:

SELECT * FROM employees

WHERE department_id IN (
SELECT id FROM departments WHERE location = 'New York'
);
Q163. How do you use subquery in FROM clause?

Answer:

SELECT dept_name, avg_salary

FROM (
SELECT department_id, AVG(salary) as avg_salary
FROM employees
GROUP BY department_id
) AS dept_averages
JOIN departments ON dept_averages.department_id = [Link];
Q164. What is the EXISTS operator?

Answer: EXISTS returns TRUE if the subquery returns any rows.

SELECT * FROM departments d

WHERE EXISTS (
 SELECT 1 FROM employees e WHERE e.dept_id = [Link]
);

Returns departments that have at least one employee.

Q165. What is NOT EXISTS?

Answer: NOT EXISTS returns TRUE if the subquery returns no rows.

SELECT * FROM departments d

WHERE NOT EXISTS (
SELECT 1 FROM employees e WHERE e.dept_id = [Link]
);

Returns departments with no employees.

Q166. What is the difference between IN and EXISTS?

Answer:

 IN compares a value against a list; better for small subquery results

 EXISTS checks for existence; better for large tables and correlated queries
 EXISTS stops at first match; IN evaluates entire list
Q167. How do you find the second highest salary using subquery?

Answer:

SELECT MAX(salary) FROM employees

WHERE salary < (SELECT MAX(salary) FROM employees);
-- Alternative
SELECT salary FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
Q168. How do you find the nth highest salary?

Answer:

SELECT DISTINCT salary FROM employees

ORDER BY salary DESC
LIMIT 1 OFFSET n-1;
-- Using subquery
SELECT salary FROM employees e1
WHERE n-1 = (
SELECT COUNT(DISTINCT salary) FROM employees e2
WHERE [Link] > [Link]
);
Q169. How do you find employees earning more than department average?

Answer:

SELECT * FROM employees e

WHERE salary > (
SELECT AVG(salary) FROM employees
 WHERE department = [Link]
);
Q170. What is the ANY operator?

Answer: ANY compares a value to any value returned by subquery.

SELECT * FROM employees

WHERE salary > ANY (SELECT salary FROM employees WHERE department =
'Sales');

Returns employees earning more than the lowest Sales salary.

Q171. What is the ALL operator?

Answer: ALL compares a value to all values returned by subquery.

SELECT * FROM employees

WHERE salary > ALL (SELECT salary FROM employees WHERE department =
'Sales');

Returns employees earning more than the highest Sales salary.

Q172. Can you use ORDER BY in a subquery?

Answer: Generally, ORDER BY in subqueries is not meaningful unless paired with LIMIT
(in some databases) because subquery results are sets without inherent order.

Q173. How do you use subquery with INSERT?

Answer:

INSERT INTO employees_backup

SELECT * FROM employees WHERE department = 'Sales';
Q174. How do you use subquery with UPDATE?

Answer:

UPDATE employees
SET salary = (SELECT AVG(salary) FROM salary_grades WHERE grade = 'A')
WHERE performance_rating = 'Excellent';
Q175. How do you use subquery with DELETE?

Answer:

DELETE FROM employees

WHERE department_id IN (
SELECT id FROM departments WHERE status = 'Closed'
);

Section 9: Views (Questions 176-185)

Q176. What is a VIEW?

Answer: A view is a virtual table based on a SELECT query. It doesn't store data but
displays data from underlying tables.

CREATE VIEW sales_employees AS

SELECT id, name, salary FROM employees
WHERE department = 'Sales';
Q177. What are the advantages of views?

Answer:

 Security: Hide sensitive columns from users

 Simplicity: Simplify complex queries
 Consistency: Provide consistent data representation
 Abstraction: Hide underlying table structure changes
Q178. How do you create a view?

Answer:

CREATE VIEW view_name AS

SELECT column1, column2
FROM table_name
WHERE condition;
Q179. How do you drop a view?

Answer:

DROP VIEW view_name;

DROP VIEW IF EXISTS view_name;
Q180. Can you update data through a view?

Answer: Yes, if the view is updatable (based on a single table, no aggregate functions, no
DISTINCT, GROUP BY, or HAVING). The underlying table gets updated.

UPDATE sales_employees SET salary = 50000 WHERE id = 101;

Q181. What is a materialized view?

Answer: A materialized view stores query results physically (unlike regular views). It's faster
to query but needs refreshing when source data changes. Not all databases support them.

Q182. How do you modify a view?

Answer:

-- MySQL
CREATE OR REPLACE VIEW view_name AS
SELECT new_columns FROM table;
-- SQL Server
ALTER VIEW view_name AS
SELECT new_columns FROM table;
Q183. Can you create a view based on multiple tables?

Answer: Yes, views can use JOINs:

CREATE VIEW employee_details AS

SELECT [Link], d.dept_name, [Link]
FROM employees e
JOIN departments d ON e.dept_id = [Link];
Q184. What is the WITH CHECK OPTION?

Answer: WITH CHECK OPTION ensures that INSERT/UPDATE through the view only
allows rows that satisfy the view's WHERE condition.

CREATE VIEW sales_employees AS

SELECT * FROM employees WHERE department = 'Sales'
WITH CHECK OPTION;
Q185. Can views improve performance?

Answer: Regular views don't improve performance — they're just stored queries. However,
indexed/materialized views can improve read performance by storing precomputed results.

Section 10: Indexes and Performance (Questions 186-195)

Q186. What is an index?

Answer: An index is a data structure that improves query speed by allowing faster data
retrieval. Like a book index, it helps locate data without scanning every row.

Q187. What are the types of indexes?

Answer:

 Clustered Index: Sorts and stores data rows physically; one per table
 Non-Clustered Index: Separate structure pointing to data; multiple allowed
 Unique Index: Ensures uniqueness of indexed columns
 Composite Index: Index on multiple columns
Q188. How do you create an index?

Answer:

CREATE INDEX idx_name ON employees(name);

CREATE UNIQUE INDEX idx_email ON employees(email);
CREATE INDEX idx_dept_sal ON employees(department, salary); -- Composite
Q189. How do you drop an index?

Answer:

DROP INDEX idx_name ON employees;

-- or
ALTER TABLE employees DROP INDEX idx_name;
Q190. What is a clustered index?

Answer: A clustered index determines the physical order of data in a table. A table can have
only one clustered index. Primary keys typically create clustered indexes.

Q191. What is a non-clustered index?

Answer: A non-clustered index is a separate structure containing index keys and pointers to
data rows. A table can have multiple non-clustered indexes.

Q192. When should you use indexes?

Answer:

 Columns frequently used in WHERE clauses

 Columns used in JOIN conditions
 Columns used in ORDER BY
 Columns with high selectivity (many unique values)
 Foreign key columns
Q193. When should you NOT use indexes?

Answer:

 Small tables
 Columns with few unique values (low selectivity)
 Columns frequently updated
 Tables with heavy INSERT/UPDATE/DELETE operations
Q194. What is the impact of indexes on INSERT/UPDATE/DELETE?

Answer: Indexes slow down write operations because the index must be updated whenever
data changes. There's a trade-off between read performance (faster) and write performance
(slower).

Q195. How do you see indexes on a table?

Answer:

-- MySQL
SHOW INDEX FROM employees;
-- SQL Server
EXEC sp_helpindex 'employees';
-- Using information_schema
SELECT * FROM information_schema.statistics
WHERE table_name = 'employees';

Section 11: NULL Handling and Miscellaneous (Questions

196-200)
Q196. What is NULL in SQL?

Answer: NULL represents missing or unknown data. It is NOT the same as zero, empty
string, or false. NULL requires special handling with IS NULL/IS NOT NULL operators.

Q197. How does NULL behave in comparisons?

Answer: Any comparison with NULL results in UNKNOWN (not TRUE or FALSE):

NULL = NULL -- Returns NULL, not TRUE

NULL != NULL -- Returns NULL, not TRUE
5 > NULL -- Returns NULL

Use IS NULL or IS NOT NULL for comparisons.

Q198. What is IFNULL() or NVL()?

Answer: These functions return an alternative value if the first value is NULL:

-- MySQL
SELECT IFNULL(phone, 'No Phone') FROM employees;
-- Oracle
SELECT NVL(phone, 'No Phone') FROM employees;
-- SQL Server
SELECT ISNULL(phone, 'No Phone') FROM employees;
Q199. What is NULLIF()?

Answer: NULLIF returns NULL if two values are equal; otherwise returns the first value.

SELECT NULLIF(10, 10); -- Returns NULL

SELECT NULLIF(10, 20); -- Returns 10

Useful for avoiding division by zero: salary / NULLIF(hours, 0)

Q200. What is CASE expression in SQL?

Answer: CASE provides conditional logic similar to IF-THEN-ELSE:

SELECT name,
CASE
WHEN salary > 100000 THEN 'High'
WHEN salary > 50000 THEN 'Medium'
ELSE 'Low'
END AS salary_level
FROM employees;

Quick Reference Summary

Most Common SQL Commands
Command Purpose

SELECT Retrieve data

INSERT Add new data

UPDATE Modify existing data

DELETE Remove data

CREATE Create new objects

ALTER Modify objects

DROP Delete objects

Join Types Cheat Sheet

Join Type Returns

INNER JOIN Matching rows only

LEFT JOIN All left + matching right

RIGHT JOIN All right + matching left

FULL OUTER JOIN All rows from both

CROSS JOIN All combinations

Aggregate Functions
Function Purpose

COUNT() Count rows

SUM() Total values

AVG() Average value

MIN() Smallest value

MAX() Largest value