Database Concepts and SQL
Database Concepts and SQL
This domain tests relational database terms and basic SQL. A DBMS manages data; an RDBMS stores data in tables (relations) of rows (tuples) and columns (attributes). A primary key uniquely identifies each row and cannot be null; a candidate key is any minimal unique key; a foreign key references a primary key in another table and enforces referential integrity.
SQL rules
- DDL defines structures: CREATE TABLE, ALTER TABLE, DROP TABLE.
- DML manipulates data: INSERT, SELECT, UPDATE, DELETE.
- SELECT columns FROM table WHERE condition ORDER BY column;
- DISTINCT removes duplicate rows; aggregate functions include COUNT, SUM, AVG, MIN and MAX.
- String values use single quotes; NULL needs IS NULL, not = NULL.
Worked example
SELECT name FROM student WHERE marks > 75 ORDER BY marks DESC; returns names of students scoring above 75, highest marks first. The WHERE clause filters rows before ordering; ORDER BY DESC sorts descending. If two students have the same marks, their relative order is unspecified unless a tie-breaker is added.
Traps
DELETE removes rows; DROP removes the table. UPDATE without WHERE changes every row. NULL is not zero or an empty string. A primary key rejects duplicates and nulls. COUNT(*) counts rows, including those with nulls; COUNT(column) ignores nulls.