1. What is SQL?
SQL (Structured Query Language) is a language used to store, manage, and retrieve data from a database.
Example:
SELECT * FROM employees;
This gets all records from the employees table.
2. What is a Database?
A database is a place where data is stored in an organized way so it can be easily accessed.
Example:
MySQL database is like a folder that holds tables.
3. What are the types of SQL commands?
- DDL – Data Definition Language (CREATE, ALTER, DROP)
- DML – Data Manipulation Language (INSERT, UPDATE, DELETE)
- DQL – Data Query Language (SELECT)
- TCL – Transaction Control (COMMIT, ROLLBACK)
- DCL – Data Control (GRANT, REVOKE)
4. What is Primary Key?
A Primary Key uniquely identifies each row in a table.
- No duplicates
- Cannot be NULL
Example:
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50)
);
5. What is Foreign Key?
A Foreign Key links two tables together.
It refers to the primary key of another table.
Example:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
emp_id INT,
FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
);
6. What is UNIQUE Key?
UNIQUE ensures all values in a column are different.
But NULL is allowed (MySQL allows multiple NULLs).
Example:
ALTER TABLE employees ADD UNIQUE (email);
7. Difference between Primary Key and UNIQUE Key?
| Primary Key | UNIQUE Key |
|---|---|
| Only one per table | Many UNIQUE allowed |
| No NULL allowed | NULL allowed |
| Identifies a record | Ensures uniqueness only |
8. What is NOT NULL constraint?
NOT NULL ensures a column must always have a value.
Example:
name VARCHAR(50) NOT NULL
9. What is Default Constraint?
It gives a column a default value if no value is provided.
Example:
ALTER TABLE employees
ADD COLUMN status VARCHAR(10) DEFAULT 'Active';
10. Difference between DELETE, TRUNCATE, and DROP?
| Command | Meaning | Rollback | Removes Structure? |
|---|---|---|---|
| DELETE | Removes rows | Yes | No |
| TRUNCATE | Removes all rows faster | No | No |
| DROP | Removes entire table | No | Yes |
Example:
DELETE FROM employees WHERE emp_id=1;
TRUNCATE TABLE employees;
DROP TABLE employees;
11. Difference between WHERE and HAVING?
| WHERE | HAVING |
|---|---|
| Filters rows before grouping | Filters groups after grouping |
| Cannot use aggregate (SUM, COUNT) | Can use aggregate |
Example:
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
12. What are Joins in SQL?
Joins combine data from two or more tables using related columns.
13. What is INNER JOIN?
Returns matching records from both tables.
Example:
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d
ON e.dept_id = d.dept_id;
14. What is LEFT JOIN?
Returns all records from the left table + matching records from the right.
Example:
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d
ON e.dept_id = d.dept_id;
15. What is RIGHT JOIN?
Returns all records from the right table + matching records from the left.
16. What is FULL JOIN?
MySQL does NOT directly support FULL JOIN, but you can simulate it:
SELECT * FROM employees e
LEFT JOIN departments d ON e.dept_id=d.dept_id
UNION
SELECT * FROM employees e
RIGHT JOIN departments d ON e.dept_id=d.dept_id;
17. What is Self Join?
A table joins with itself.
Example:
Find employees and their managers (same table):
SELECT e.name AS Employee, m.name AS Manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.emp_id;
18. What is Cross Join?
Returns all combinations of both tables (Cartesian product).
SELECT *
FROM employees
CROSS JOIN departments;
19. What is UNION and UNION ALL?
- UNION → Removes duplicates
- UNION ALL → Keeps duplicates
Example:
SELECT name FROM employees
UNION
SELECT name FROM managers;
20. Difference between UNION and UNION ALL?
| UNION | UNION ALL |
|---|---|
| Removes duplicates | Keeps duplicates |
| Slower | Faster |
21. What is Normalization?
A process of organizing data to reduce redundancy and improve integrity.
Example: splitting repeated data into separate tables.
22. What is Denormalization?
Adding redundant data to improve read performance.
Example: storing total salary in a summary table.
23. Difference between CHAR and VARCHAR?
| CHAR | VARCHAR |
|---|---|
| Fixed length | Variable length |
| Faster | Saves space |
| Pads with spaces | No padding |
24. Difference between SQL and MySQL?
- SQL → Language
- MySQL → Database management system that uses SQL
25. What is Auto Increment in SQL?
Automatically generates a unique number for each new row.
Example:
CREATE TABLE employees (
emp_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50)
);