Kerala Plus Two Computer Science (DHSE): Database Concepts and SQL

Database Concepts and SQL

Questions cover relational terminology, keys, integrity rules, and single-table SQL with aggregates.

Relational model

• A relation is a table; a tuple is a row; an attribute is a column.
• Degree = number of attributes; cardinality = number of tuples.
• Primary key: uniquely identifies a row, cannot be NULL.
• Candidate key: any minimal unique key; one becomes the primary key.
• Foreign key: references a primary key in another table and enforces referential integrity.
• Alternate key: candidate key not chosen as primary.

SQL essentials

• DDL: CREATE, ALTER, DROP. DML: INSERT, UPDATE, DELETE, SELECT.
• SELECT columns FROM table WHERE condition ORDER BY column;
• Aggregates: COUNT, SUM, AVG, MIN, MAX; GROUP BY forms groups, HAVING filters groups.
• WHERE filters rows before grouping; HAVING filters after grouping.

Worked example

SELECT dept, COUNT(*) FROM Employee WHERE salary > 20000 GROUP BY dept HAVING COUNT(*) > 2; Reasoning: rows with salary above 20000 are kept first; they are grouped by department; groups with more than two such employees are output. COUNT(*) counts rows per group.

Traps

• Using = NULL instead of IS NULL; NULL is not equal to anything.
• Putting an aggregate condition in WHERE instead of HAVING.
• Assuming a primary key may contain NULL or duplicates.
• Confusing degree with cardinality.
• Forgetting that DELETE removes rows while DROP removes the table structure.