Courses / HEC NSCT – National Skill Competency Test: Complete Preparation for Computing Graduates

4.2 SQL – Queries, Joins and Aggregation

Area 4 – Databases (10%) · 55 min read · 35 MCQs to check yourself

SQL command families

Family Commands
DDL (definition) CREATE, ALTER, DROP, TRUNCATE
DML (manipulation) INSERT, UPDATE, DELETE, (SELECT)
DQL (query) SELECT
DCL (control) GRANT, REVOKE
TCL (transactions) COMMIT, ROLLBACK, SAVEPOINT

Order of a SELECT

SELECT dept, COUNT(*) AS staff
FROM employee
WHERE salary > 50000
GROUP BY dept
HAVING COUNT(*) >= 2
ORDER BY staff DESC
LIMIT 5;

Logical execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. WHERE filters rows before grouping; HAVING filters groups.

Joins

Join Returns
INNER JOIN Only matching rows from both tables
LEFT JOIN All rows from the left table + matches (NULL if none)
RIGHT JOIN All rows from the right table + matches
FULL OUTER JOIN All rows from both sides
CROSS JOIN Every combination (m × n rows)
SELF JOIN A table joined with itself (employee–manager)

Useful facts

Learn SQL joins in 5 minutes (Bro Code)

This lesson ends with a quiz of 35 MCQs. Open the lesson on PrepOasis to take it and save your progress.

Previous: 4.1 Database Basics, ER Model and Keys · Next: 4.3 Normalization – 1NF to BCNF

All lessons in HEC NSCT – National Skill Competency Test: Complete Preparation for Computing Graduates