1. What is SQL?
Answer:
SQL (Structured Query Language) is used to communicate with relational databases. It helps retrieve, insert, update, and delete data.
SELECT * FROM Employees;
2. What is the difference between SQL and MySQL?
SQL : A language
MySQL : A database system
SQL : Used to write queries
MySQL : Executes SQL queries
SQL : Standard language
MySQL : Software product
3. What are Primary Keys and Foreign Keys?
Primary Key: Uniquely identifies each row in a table.
Foreign Key: Creates a relationship between two tables.
Example:
โข EmployeeID โ Primary Key
โข DepartmentID โ Foreign Key
4. What is Normalization?
Answer:
Normalization organizes data into multiple related tables to reduce redundancy and improve data integrity.
Benefits:
โ Reduces duplicate data
โ Improves consistency
โ Saves storage
5. What is Denormalization?
Answer:
Denormalization combines tables to improve query performance.
Benefits:
โ Faster reporting
โ Faster data retrieval
Drawback:
โ More redundancy
6. Difference Between WHERE and HAVING?
WHERE: Filters rows before aggregation.
HAVING: Filters groups after aggregation.
SELECT Department, COUNT(*)
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 10;
7. Difference Between DELETE, DROP, and TRUNCATE?
DELETE: Removes selected rows.
DELETE FROM Employees
WHERE EmployeeID = 101;
TRUNCATE: Removes all rows.
TRUNCATE TABLE Employees;
DROP: Deletes entire table structure.
DROP TABLE Employees;
8. Difference Between INNER JOIN and LEFT JOIN?
INNER JOIN: Returns matching records only.
LEFT JOIN: Returns all records from left table and matching records from right table.
SELECT *
FROM Employees E
LEFT JOIN Departments D
ON E.DepartmentID = D.DepartmentID;
9. What is RIGHT JOIN?
Returns all rows from the right table and matching rows from the left table.
10. What is FULL OUTER JOIN?
Returns all matching and non-matching rows from both tables.
11. What is SELF JOIN?
A table joined with itself.
Example: Employee and Manager stored in same table.
12. What is CROSS JOIN?
Returns every possible combination of rows.
If:
โข Table A = 5 rows
โข Table B = 4 rows
Result = 20 rows
13. What are Aggregate Functions?
Used to perform calculations.
Examples: COUNT(), SUM(), AVG(), MIN(), MAX()
14. Difference Between COUNT and COUNT DISTINCT?
COUNT(EmployeeID): Counts all values.
COUNT(DISTINCT DepartmentID): Counts unique values only.
15. What is GROUP BY?
Groups rows with similar values.
SELECT Department, COUNT(*)
FROM Employees
GROUP BY Department;
16. Difference Between GROUP BY and ORDER BY?
GROUP BY: Groups data.
ORDER BY: Sorts data.
17. What is a Subquery?
A query inside another query.
SELECT *
FROM Employees
WHERE Salary >
(
SELECT AVG(Salary)
FROM Employees
);
18. What are CTEs?
Common Table Expressions create temporary result sets.
WITH SalesCTE AS
(
SELECT *
FROM Sales
)
SELECT *
FROM SalesCTE;
Benefits:
โ Readability
โ Reusability
19. What are Window Functions?
Perform calculations without collapsing rows.
Examples: ROW_NUMBER(), RANK(), DENSE_RANK()
20. Explain ROW_NUMBER()
Assigns unique numbers.