Q: Fetch all records from a table:
A: SELECT * FROM employees;
Q: Retrieve specific columns:
A: SELECT name, salary FROM employees;
Q: Use WHERE clause:
A: SELECT * FROM employees WHERE department = 'IT';
Q: Use of BETWEEN, IN, LIKE:
A: SELECT * FROM products WHERE price BETWEEN 100 AND 200;
SELECT * FROM students WHERE grade IN ('A', 'B');
SELECT * FROM customer WHERE name LIKE 'A%';
Q: Count employees in each department:
A: SELECT department, COUNT(*) FROM employees GROUP BY department;
Q: Get max, min, avg salary:
A: SELECT MAX(salary), MIN(salary), AVG(salary) FROM employees;
Q: GROUP BY with HAVING:
A: SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*)
> 5;
Q: Inner Join:
A: SELECT e.name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_id =
d.id;
Q: Left and Right Join:
A: LEFT JOIN:
SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.id;
RIGHT JOIN:
SELECT e.name, d.dept_name FROM employees e RIGHT JOIN departments d ON e.dept_id =
d.id;
Q: Self Join:
A: SELECT e1.name AS employee, e2.name AS manager FROM employees e1 JOIN employees
e2 ON e1.manager_id = e2.id;
Q: More than average salary:
A: SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
Q: Correlated Subquery Example:
A: SELECT name FROM employees e1 WHERE EXISTS (
SELECT * FROM employees e2
WHERE e2.salary = e1.salary AND e2.id != e1.id
);
Q: Set Operators - UNION/INTERSECT/EXCEPT:
A: UNION:
SELECT name FROM employees UNION SELECT name FROM managers;
INTERSECT:
SELECT name FROM employees INTERSECT SELECT name FROM managers;
EXCEPT:
SELECT name FROM employees EXCEPT SELECT name FROM managers;
Q: Insert a record:
A: INSERT INTO employees (id, name, dept, salary) VALUES (1, 'Rahul', 'IT', 50000);
Q: Update a record:
A: UPDATE employees SET salary = salary + 1000 WHERE dept = 'HR';
Q: Delete a record:
A: DELETE FROM employees WHERE id = 101;
Q: Primary and Foreign Key:
A: CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(id)
);
Q: UNIQUE vs PRIMARY KEY:
A: - PRIMARY KEY: Only one per table, cannot be NULL.
- UNIQUE: Multiple allowed, can be NULL.
Q: Create Table with Constraints:
A: CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
dept_id INT,
salary DECIMAL(10,2),
FOREIGN KEY (dept_id) REFERENCES departments(id)
);
Q: Normalization (1NF, 2NF, 3NF):
A: - 1NF: Atomic values
- 2NF: No partial dependency
- 3NF: No transitive dependency
Q: DBMS vs RDBMS:
A: - DBMS: Non-relational (e.g., file systems)
- RDBMS: Uses tables, supports relationships (e.g., MySQL)
Q: ACID Properties:
A: - Atomicity: All or nothing
- Consistency: Data integrity
- Isolation: Transactions dont interfere
- Durability: Permanent changes
Q: Create a View:
A: CREATE VIEW high_salary AS SELECT name, salary FROM employees WHERE salary >
60000;
Q: Advantages of Views:
A: - Simplify queries
- Enhance security
- Modularize logic
Q: Clustered vs Non-clustered Index:
A: - Clustered: Affects data storage, only one per table
- Non-clustered: Separate from data, many possible
Q: Second Highest Salary:
A: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM
employees);
Q: Find Duplicate Records:
A: SELECT name, COUNT(*) FROM employees GROUP BY name HAVING COUNT(*) > 1;
Q: Delete Duplicates but Keep One:
A: DELETE FROM employees WHERE id NOT IN (
SELECT MIN(id)
FROM employees
GROUP BY name, salary, dept_id
);
