Complete ANSI SQL Reference with Examples
(CompanyDB)
We will use a sample database named CompanyDB with the following tables: Employees(emp_id,
first_name, last_name, department_id, salary, hire_date)
Departments(department_id, dept_name, location)
Projects(project_id, project_name, department_id, budget)
EmployeeProjects(emp_id, project_id, role)
1. CREATE DATABASE
Query:
CREATE DATABASE CompanyDB;
Answer/Explanation:
Creates a new database named CompanyDB.
2. CREATE TABLE
Query:
CREATE TABLE Departments ( department_id INT PRIMARY KEY, dept_name VARCHAR(50),
location VARCHAR(50) );
Answer/Explanation:
Creates Departments table.
3. INSERT INTO
Query:
INSERT INTO Departments VALUES (1, 'IT', 'New York');
Answer/Explanation:
Adds a new row into Departments.
4. UPDATE
Query:
UPDATE Departments SET location = 'Chicago' WHERE department_id = 1;
Answer/Explanation:
Updates location of IT department to Chicago.
5. DELETE
Query:
DELETE FROM Departments WHERE department_id = 1;
Answer/Explanation:
Deletes the IT department row.
6. SELECT
Query:
SELECT first_name, last_name FROM Employees;
Answer/Explanation:
Returns list of employees' names.
7. WHERE
Query:
SELECT * FROM Employees WHERE salary > 50000;
Answer/Explanation:
Returns employees with salary above 50000.
8. ORDER BY
Query:
SELECT first_name, salary FROM Employees ORDER BY salary DESC;
Answer/Explanation:
Lists employees ordered by salary descending.
9. DISTINCT
Query:
SELECT DISTINCT department_id FROM Employees;
Answer/Explanation:
Returns unique department IDs.
10. LIKE
Query:
SELECT * FROM Employees WHERE first_name LIKE 'J%';
Answer/Explanation:
Finds employees whose first name starts with J.
11. BETWEEN
Query:
SELECT * FROM Employees WHERE salary BETWEEN 40000 AND 60000;
Answer/Explanation:
Finds employees with salary between 40k–60k.
12. IN
Query:
SELECT * FROM Employees WHERE department_id IN (1,2,3);
Answer/Explanation:
Finds employees in departments 1,2,3.
13. INNER JOIN
Query:
SELECT e.first_name, d.dept_name FROM Employees e INNER JOIN Departments d ON
e.department_id = d.department_id;
Answer/Explanation:
Shows employees with their department names.
14. LEFT JOIN
Query:
SELECT e.first_name, d.dept_name FROM Employees e LEFT JOIN Departments d ON
e.department_id = d.department_id;
Answer/Explanation:
Shows all employees and their department (null if none).
15. GROUP BY
Query:
SELECT department_id, AVG(salary) FROM Employees GROUP BY department_id;
Answer/Explanation:
Shows average salary per department.
16. HAVING
Query:
SELECT department_id, AVG(salary) AS avg_salary FROM Employees GROUP BY
department_id HAVING AVG(salary) > 60000;
Answer/Explanation:
Returns departments with avg salary above 60k.
17. COUNT
Query:
SELECT COUNT(*) FROM Employees;
Answer/Explanation:
Counts total employees.
18. MAX/MIN
Query:
SELECT MAX(salary), MIN(salary) FROM Employees;
Answer/Explanation:
Shows highest and lowest salary.
19. SUM
Query:
SELECT SUM(salary) FROM Employees WHERE department_id=1;
Answer/Explanation:
Total salary paid in department 1.
20. Subquery (scalar)
Query:
SELECT first_name FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees);
Answer/Explanation:
Finds employees with salary above avg salary.
21. Subquery (correlated)
Query:
SELECT e1.first_name, [Link] FROM Employees e1 WHERE salary > (SELECT
AVG([Link]) FROM Employees e2 WHERE e1.department_id = e2.department_id);
Answer/Explanation:
Employees earning above their department's avg salary.
22. EXISTS
Query:
SELECT dept_name FROM Departments d WHERE EXISTS (SELECT * FROM Employees e WHERE
e.department_id = d.department_id);
Answer/Explanation:
Departments that have employees.
23. UNION
Query:
SELECT dept_name FROM Departments UNION SELECT location FROM Departments;
Answer/Explanation:
Combines dept_name and location results (unique).
24. INTERSECT (not all DBMS support)
Query:
SELECT dept_name FROM Departments INTERSECT SELECT location FROM Departments;
Answer/Explanation:
Shows values common in both columns.
25. EXCEPT
Query:
SELECT dept_name FROM Departments EXCEPT SELECT location FROM Departments;
Answer/Explanation:
Shows dept_names not listed as locations.
26. CREATE VIEW
Query:
CREATE VIEW HighSalary AS SELECT first_name, salary FROM Employees WHERE salary >
70000;
Answer/Explanation:
Creates a view of high salary employees.
27. ALTER TABLE
Query:
ALTER TABLE Employees ADD email VARCHAR(100);
Answer/Explanation:
Adds email column to Employees table.
28. DROP TABLE
Query:
DROP TABLE EmployeeProjects;
Answer/Explanation:
Deletes the EmployeeProjects table.
29. TRUNCATE
Query:
TRUNCATE TABLE Employees;
Answer/Explanation:
Removes all rows but keeps structure.
30. Transaction
Query:
BEGIN; UPDATE Employees SET salary=salary*1.1; COMMIT;
Answer/Explanation:
Increases salaries by 10% in a transaction.
