Maharashtra HSC IT Commerce: Database Management and SQL

Database Management and SQL tests relational concepts and the ability to write correct queries. You need key definitions, command classification and clause order, plus the effect of NULL and the rules for aggregates.

Core rules and concepts

• A DBMS stores and manages data; an RDBMS stores data in related tables of rows and columns.
• Primary key: unique and not NULL, identifies each row. Foreign key: references a primary key in another table and enforces referential integrity. Candidate key: any column that could serve as primary key.
• DDL defines structure: CREATE, ALTER, DROP. DML manipulates data: SELECT, INSERT, UPDATE, DELETE.
• Query order: SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ....
• WHERE filters rows before grouping; HAVING filters groups after aggregation.
• Aggregate functions: COUNT(), SUM(), AVG(), MAX(), MIN().

Worked example

Table student(name, marks, city). List names of students with marks at least 60, highest first: SELECT name FROM student WHERE marks >= 60 ORDER BY marks DESC; Reasoning: WHERE filters individual rows, ORDER BY marks DESC sorts from highest to lowest, and SELECT name returns only the requested column. Adding an aggregate such as COUNT(*) would require grouping, which this question does not ask for.

Traps that cost marks

• Writing WHERE marks = NULL; NULL needs IS NULL or IS NOT NULL.
• Confusing DELETE (removes rows, keeps the table) with DROP (removes the table) and TRUNCATE (removes all rows quickly).
• Putting an aggregate condition in WHERE instead of HAVING.
• Omitting quotes around text values such as 'Pune'.
• Assuming COUNT(column) counts NULLs; it does not, while COUNT(*) counts rows.