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
- Aggregates: COUNT, SUM, AVG, MIN, MAX.
COUNT(*)counts rows;COUNT(col)ignores NULLs. - NULL means unknown: use
IS NULL, never= NULL. DELETEremoves chosen rows (can roll back);TRUNCATEremoves all rows quickly;DROPremoves the table itself.UNIONremoves duplicates;UNION ALLkeeps them.LIKE 'A%'– starts with A;_matches one character.- A view is a saved query (virtual table). A subquery is a query inside another query.
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