Questions
4 questions
Difficulty
Medium
Importance
Core foundation for DBMS paper
Overview
SQL (Structured Query Language) is the standard language for relational database management systems, essential for defining, manipulating, and controlling data. It is a high-yield topic in university exams, focusing on the ability to translate logical business requirements into precise database queries.
DDL, DML, and DCL
These command categories define the scope and authority level of database operations. DDL handles schema structures, DML manages data records, and DCL manages user access and permissions.
- DDL Commands: CREATE, ALTER, DROP, TRUNCATE
- DML Commands: SELECT, INSERT, UPDATE, DELETE
- DCL Commands: GRANT, REVOKE
- TCL Commands (Transaction Control): COMMIT, ROLLBACK, SAVEPOINT
Joins and Subqueries
Joins are used to combine records from two or more tables based on a related column, while subqueries provide a nested approach to retrieving data. Mastery of these is vital for solving complex retrieval problems in exams.
- INNER JOIN returns only matching rows
- LEFT JOIN returns all from the left table and matched from the right
- FULL OUTER JOIN returns all rows from both tables
- Subqueries can be used in SELECT, FROM, or WHERE clauses
- Correlated subqueries execute once for every row processed by the outer query
Aggregate Functions and Grouping
Aggregate functions perform calculations on multiple rows to return a single scalar value. Grouping, paired with the HAVING clause, allows for data summarization based on specific categories.
- Functions: COUNT(), SUM(), AVG(), MIN(), MAX()
- GROUP BY groups rows that have the same values into summary rows
- HAVING is used to filter groups whereas WHERE filters individual rows
- Aggregate functions ignore NULL values except for COUNT(*)
Formula Sheet
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);
Exam Tip
Always draw a small sample table schema before writing complex join queries to avoid logical errors with column aliases.
Common Mistakes
- Confusing the purpose of WHERE (filters records) and HAVING (filters groups created by GROUP BY).
- Using aggregate functions directly in a WHERE clause instead of the HAVING clause.
- Forgetting to include non-aggregated columns in the GROUP BY clause when using aggregate functions in SELECT.
More Revision Notes
Ready to test yourself?
Play topic-wise SQL questions in Aspirant Arcade — gamified MCQ practice.
Download Free