T3 - DBMS and SQL
Adapted from
L&T/lt-prep-library/topics/T3-dbms-and-sql.md; verified in that library's QA.
1. Why this topic matters here
Expect roughly 4.12 questions (3.2% of the paper) drawn from DBMS and SQL on the L&T PGET online test. Technical/domain is the single largest block at about 40 of the ~130 marks, and the sub-syllabus for that block is set by the discipline you register under — for a Software Engineering / CSE registration, DBMS and SQL sit inside that block alongside C, Java, OS and Networks.
The evidence for this specific topic is weak and must be treated as such. No directly observed L&T PGET question is behind this file. The topic is carried by aggregator syllabus listings and candidate-named topic lists that consistently place "DBMS basics, SQL queries, normalization" in CSE-flavoured PGET papers. Confidence is Medium, so study it for recall of definitions, keys, normal forms and the standard SQL result of a given query — not for design skill.
2. Exactly what gets asked
| Sub-pattern | How it appears | Roughly how often |
|---|---|---|
| SQL query output | Given a table and a SELECT with WHERE, GROUP BY, HAVING, ORDER BY — pick the result set | Very common |
| Joins | INNER vs LEFT vs RIGHT vs FULL; "how many rows does this join return?" | Very common |
| Keys and constraints | Candidate / primary / foreign / super key identification from a given set of attributes | Common |
| Normalization | "Which normal form is this table in?" / "Which FD violates 2NF?" | Common |
| Aggregate functions | COUNT(*) vs COUNT(col) vs COUNT(DISTINCT col) with NULLs | Common |
| ACID and transactions | Which property is violated by a given scenario | Occasional |
| Indexes and views | What an index speeds up; what a view is | Occasional |
| ER model | Cardinality of a relationship; weak entity | Occasional |
| Concurrency | Dirty read, lost update, deadlock definition | Occasional |
3. Concept notes
3.1 Keys — the definition chain
A super key is any attribute set that uniquely identifies a row. A candidate key is a minimal super key (remove any attribute and uniqueness is lost). The primary key is the one candidate key chosen by the designer; it cannot be NULL. An alternate key is a candidate key not chosen as primary. A foreign key in table R references the primary key of table S and may be NULL unless declared NOT NULL.
Example: for Student(RollNo, Aadhaar, Name), both RollNo and Aadhaar are candidate keys; {RollNo, Name} is a super key but not candidate (Name is redundant).
Count of super keys shortcut: if a table has n attributes and the candidate key has k attributes, the number of super keys is 2^(n−k) when there is exactly one candidate key.
3.2 Functional dependencies and Armstrong's axioms
X → Y means: any two tuples agreeing on X must agree on Y. Three inference rules:
- Reflexivity: if Y ⊆ X then X → Y.
- Augmentation: if X → Y then XZ → YZ.
- Transitivity: if X → Y and Y → Z then X → Z.
Closure of X: start with X, repeatedly add right-hand sides of FDs whose left side is already in the set. X is a super key if its closure contains every attribute.
3.3 Normal forms — decide in this order
| Form | Condition | Fails when |
|---|---|---|
| 1NF | All attribute values are atomic | A cell holds a list or a set |
| 2NF | 1NF and no partial dependency (no non-prime attribute depends on part of a candidate key) | Composite key, and a non-key column depends on only one part |
| 3NF | 2NF and no transitive dependency (no non-prime attribute depends on another non-prime attribute) | A → B and B → C with B non-prime |
| BCNF | For every non-trivial FD X → Y, X is a super key | A non-key attribute determines part of the key |
| 4NF | BCNF and no non-trivial multivalued dependency | Independent multivalued facts in one table |
Memory hook for the break points: 2NF breaks on part of a key, 3NF breaks on a non-key attribute, BCNF breaks on a key attribute.
3.4 SQL clause execution order
Write order is SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. Execution order is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. This single fact answers most "what is the output" questions, because WHERE cannot filter on an aggregate and HAVING can.
3.5 Aggregates and NULL — the highest-yield rule
Every aggregate function except COUNT(*) ignores NULLs.
Given a column with values 10, 20, NULL, 30:
COUNT(*)= 4COUNT(col)= 3SUM(col)= 60AVG(col)= 60/3 = 20 (not 60/4 = 15)
NULL comparisons use IS NULL / IS NOT NULL; col = NULL evaluates to UNKNOWN and returns no rows.
3.6 Joins — row counts
| Join | Returns |
|---|---|
| INNER | Only matching rows from both sides |
| LEFT OUTER | All left rows; NULLs where right has no match |
| RIGHT OUTER | All right rows; NULLs where left has no match |
| FULL OUTER | All rows from both sides, NULL-padded |
| CROSS | Cartesian product, m × n rows |
If A has m rows and B has n rows, an INNER join on a foreign key where every B row matches exactly one A row returns n rows, not m × n. A LEFT join between A (m) and B (n) returns at least m rows.
3.7 ACID
- Atomicity — all or nothing; a failed transfer rolls back both debits.
- Consistency — the database moves between valid states.
- Isolation — concurrent transactions do not see each other's intermediate states.
- Durability — committed data survives a crash.
Concurrency faults: dirty read (reading uncommitted data), lost update (two writers overwrite), phantom read (new rows appear in a repeated range query).
3.8 Indexes, views, and DDL/DML split
An index speeds up SELECT with a WHERE on the indexed column and slows down INSERT/UPDATE/DELETE because the index must be maintained. A primary key creates a unique index automatically. A view is a stored query, not stored data (unless materialised).
- DDL: CREATE, ALTER, DROP, TRUNCATE.
- DML: SELECT, INSERT, UPDATE, DELETE.
- DCL: GRANT, REVOKE.
- TCL: COMMIT, ROLLBACK, SAVEPOINT.
DELETE is DML, is logged, can be rolled back, and can have a WHERE. TRUNCATE is DDL, is faster, resets the table, and cannot have a WHERE.
3.9 ER model
Cardinality is one-to-one, one-to-many, many-to-many. A weak entity has no key of its own and uses a partial key plus the owner's key; it is drawn with a double rectangle. A total participation constraint is drawn with a double line.
4. Must-memorise block
- Super key count with one candidate key of size k in an n-attribute table: 2^(n−k).
- Normal-form break points: 1NF = atomic; 2NF = no partial dependency; 3NF = no transitive dependency; BCNF = every determinant is a super key; 4NF = no non-trivial MVD.
- All aggregates except COUNT(*) ignore NULL.
- AVG ignores NULL rows in the denominator.
- Execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.
- WHERE cannot contain an aggregate; HAVING can.
- COUNT(*) counts rows; COUNT(col) counts non-NULL values.
- CROSS JOIN returns m × n rows; LEFT JOIN returns at least m rows.
- ACID = Atomicity, Consistency, Isolation, Durability.
- Primary key ⇒ unique + NOT NULL + automatic unique index.
- DELETE = DML and logged; TRUNCATE = DDL, fast, no WHERE.
- DCL = GRANT/REVOKE; TCL = COMMIT/ROLLBACK/SAVEPOINT.
col = NULLreturns nothing; useIS NULL.- Armstrong: Reflexivity, Augmentation, Transitivity.
5. Worked examples
Example 1. Table Emp(id, name, dept, salary) has 5 rows. Give the output of SELECT dept, COUNT(*) FROM Emp GROUP BY dept HAVING COUNT(*) > 1.
Fast method: GROUP BY makes one row per distinct dept. HAVING keeps only groups with more than one member. Suppose rows are (1,A,HR,100), (2,B,HR,200), (3,C,IT,150), (4,D,IT,160), (5,E,IT,170). HR has 2, IT has 3. Both survive. Answer: two rows — HR|2 and IT|3. A slow method would rewrite the query mentally into a nested loop; unnecessary, since HAVING filters groups, not rows.
Example 2. Column marks holds 50, NULL, 70, NULL, 80. Give COUNT(*) and AVG(marks).
Fast method: COUNT() = 5 rows. AVG ignores the two NULLs: sum = 200 over 3 values. AVG = 66.67, COUNT() = 5.** The slow method — writing out the division as 200/5 — gives 40 and is the classic wrong answer.
Example 3. R(A,B,C,D) with FDs A → B, B → C, C → D. Find the highest normal form of R.
Fast method: Closure of A = {A,B,C,D}, so A is the only candidate key. Check 2NF: no partial dependency, since the key is a single attribute. Check 3NF: A → B is fine (A is key), but B → C has B non-prime and C non-prime — a transitive dependency. So R is in 2NF but not 3NF. Answer: 2NF. The slow method is a full attribute-closure table; the pattern key → nonkey → nonkey is enough.
Example 4. Table X has 4 rows, table Y has 3 rows, and they share column id with all 3 Y rows matching distinct X rows. How many rows does X INNER JOIN Y ON X.id = Y.id return, and how many does X LEFT JOIN Y return?
Fast method: INNER keeps only matched pairs — one per matching Y row. INNER = 3 rows. LEFT keeps every X row; the unmatched X row appears NULL-padded. LEFT = 4 rows. The trap answer 12 is the CROSS JOIN count.
Example 5. Given SELECT COUNT(DISTINCT dept) FROM Emp on a table where dept values are HR, HR, NULL, IT, NULL. What is returned?
Fast method: DISTINCT removes duplicates, and NULL is not a value that COUNT counts. Distinct non-NULL values are HR and IT. Answer: 2. The trap answer 3 comes from counting NULL as a distinct value.
6. Traps and gotchas
| Trap | Why it catches people | How to avoid it |
|---|---|---|
| Using AVG as sum/count(*) | Averaging with NULLs in the denominator | Remember aggregates drop NULL; divide by COUNT(col) |
| Believing WHERE can filter aggregates | Ordinary English suggests it should | WHERE runs before GROUP BY; use HAVING |
| Calling a super key a candidate key | Both identify rows uniquely | Candidate key must be minimal |
| Thinking 3NF implies BCNF | Both sound like "no redundancy" | BCNF requires every determinant to be a super key; 3NF allows a non-super-key determinant if the dependent is prime |
| Treating TRUNCATE as DML | It looks like DELETE without WHERE | TRUNCATE is DDL, no WHERE, not row-logged |
| Assuming INNER join count = m × n | Multiplication feels natural | m × n is CROSS JOIN; INNER matches only |
| Reading a join as returning left rows | Query looks symmetric | LEFT keeps all left rows plus NULL padding |
Writing col = NULL to find missing values |
Normal equality habit | Use IS NULL; equality with NULL is UNKNOWN |
| Forgetting that a primary key implies uniqueness | Focus on the NULL rule | Primary key = unique + NOT NULL + index |
| Confusing weak entity with a table lacking a PK | Definition sounds similar | Weak entity borrows the owner's key; it has a partial key |
7. Practice questions
1. Which key is a minimal super key? a) Primary key b) Foreign key c) Candidate key d) Alternate key
2. A table has 6 attributes and one candidate key of 2 attributes. How many super keys exist? a) 12 b) 32 c) 64 d) 16
3. Which SQL clause filters groups rather than rows? a) WHERE b) ORDER BY c) HAVING d) DISTINCT
4. Column x has values 5, NULL, 15, NULL, 25. What is AVG(x)?
a) 15 b) 9 c) 11.25 d) NULL
5. Which of these is DDL? a) INSERT b) UPDATE c) TRUNCATE d) COMMIT
6. The FD chain A → B, B → C shows which dependency type when A is the key?
a) Partial b) Join c) Multivalued d) Transitive
7. Table P has 3 rows, Q has 5 rows. SELECT * FROM P CROSS JOIN Q; returns how many rows?
a) 15 b) 8 c) 5 d) 3
8. Which property guarantees a committed transaction survives a power failure? a) Atomicity b) Durability c) Isolation d) Consistency
9. SELECT COUNT(*), COUNT(city) FROM T; where T has 10 rows and city is NULL in 3 rows. What is returned?
a) 10, 7 b) 7, 10 c) 10, 10 d) 7, 7
10. A relation is in 3NF. Which condition must hold? a) Every determinant is a super key b) All attributes are atomic only c) No multivalued dependency exists d) No non-prime attribute transitively depends on a key
11. Table R(A,B,C) with FDs AB → C, C → A. Which is a candidate key?
a) A b) AB c) B d) C
12. Two transactions both read a value, both add 10, both write. The final value loses one update. Which isolation error occurred? a) Dirty read b) Lost update c) Phantom read d) Non-repeatable read
ANSWER KEY
1. c) A candidate key is by definition a minimal super key; the primary key is one such candidate chosen by the designer.
2. d) 2^(n−k) = 2^(6−2) = 2^4 = 16.
3. c) HAVING runs after GROUP BY and filters groups; WHERE filters rows before grouping.
4. a) Aggregates ignore NULLs, so AVG = (5+15+25)/3 = 45/3 = 15.
5. c) TRUNCATE is DDL; INSERT and UPDATE are DML and COMMIT is TCL.
6. d) B is a non-prime attribute determining another non-prime attribute C, giving a transitive dependency.
7. a) CROSS JOIN is the Cartesian product: 3 × 5 = 15 rows.
8. b) Durability is the ACID property that guarantees committed data survives a crash.
9. a) COUNT(*) counts all 10 rows; COUNT(city) counts the 7 non-NULL values.
10. d) 3NF forbids a non-prime attribute transitively depending on a key; option (a) is the stricter BCNF condition.
11. b) Closure of AB = {A,B,C}, and closure of C = {C,A} which misses B; so AB is the minimal key.
12. b) Two writers overwriting each other's value is the classic lost update, distinct from dirty and phantom reads.
8. Resources
- https://www.geeksforgeeks.org/dbms/dbms/ — timed DBMS MCQ sets matching the placement-paper phrasing.
- https://www.geeksforgeeks.org/normal-forms-in-dbms/ — the normal-form ladder with worked FD examples.
- https://www.geeksforgeeks.org/sql-join-set-1-inner-left-right-and-full-joins/ — join types with output tables, ideal for row-count questions.
- https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html — official reference confirming that AVG and SUM ignore NULLs.
- https://www.postgresql.org/docs/current/sql-select.html — authoritative statement of clause evaluation order in SELECT.
9. Self-check
- Can you state the difference between a super key, a candidate key and a primary key in one sentence each?
- Can you name the exact condition that breaks 2NF, 3NF and BCNF without hesitating?
- Can you compute AVG and COUNT(col) on a column containing NULLs in under fifteen seconds?
- Can you state the SQL execution order from FROM to ORDER BY?
- Can you give the row count of INNER, LEFT and CROSS joins for two small tables on demand?
- Can you list the ACID properties and name the concurrency fault behind a lost update?
If any answer is "no", revise section 4 before moving on.
10. Sources
[INFERRED] This topic has no directly observed L&T PGET question behind it. It is carried by aggregator syllabus listings that place DBMS and SQL inside the CSE technical block, and by candidate-named topic lists for L&T campus papers. The 4.12-question estimate is a proportional allocation (3.2%) of the ~130-question paper, not a counted figure. The ACID, normal-form and join definitions above are standard, stable textbook content and are safe to memorise; the specific weighting and difficulty rating are inference, not evidence.