Database Concepts and Basic Querying
Database Concepts and Basic Querying tests tables, keys, relationships, data types, and simple SQL. Expect both definition and query-writing.
Core ideas
- A database is an organised collection of related data; a DBMS manages it. A table stores rows (records) and columns (fields).
- A primary key uniquely identifies each row and cannot be NULL. A foreign key refers to a primary key in another table and creates a relationship.
- Data types: INTEGER, VARCHAR/TEXT, DATE, DECIMAL, BOOLEAN. Choose types to prevent invalid data.
- Relationships: one-to-one, one-to-many, many-to-many (resolved with a junction table).
- SQL statements: SELECT columns FROM table WHERE condition ORDER BY column; INSERT INTO table VALUES (...); UPDATE table SET column=value WHERE condition; DELETE FROM table WHERE condition.
- WHERE filters rows; ORDER BY sorts (ASC/DESC); DISTINCT removes duplicates; COUNT/SUM/AVG aggregate.
- NULL means unknown, not zero or blank. Use IS NULL / IS NOT NULL, not = NULL.
Worked example
Table Students(StudentID, Name, Marks). To list names of students with marks above 60, highest first: SELECT Name FROM Students WHERE Marks > 60 ORDER BY Marks DESC; Reasoning: SELECT chooses the column, WHERE filters rows before sorting, ORDER BY sorts the result. If you write WHERE Marks > '60', some DBMSs convert the text, but numeric comparison is correct. To count them: SELECT COUNT(*) FROM Students WHERE Marks > 60.
Traps
Do not use = NULL. Do not forget quotes around text values. Do not put WHERE after ORDER BY. DELETE removes rows; DROP removes the table. UPDATE without WHERE changes every row. A primary key can be composite; do not assume every table needs an “ID” column if another unique field exists.