Database Concepts and Basic Querying
The vocabulary
A database is an organised collection of related data managed by a DBMS. Data sits in tables (relations) made of rows (records/tuples) and columns (fields/attributes). A primary key uniquely identifies each row and can be neither null nor duplicated. A foreign key in one table points to a primary key in another and enforces referential integrity. Candidate keys are all columns that could serve as the primary key; one is chosen.
SQL basics
SELECT columns FROM table WHERE condition ORDER BY column ASC|DESC; Conditional grouping uses GROUP BY with HAVING. INSERT adds rows, UPDATE changes values, DELETE removes rows - all three change data, while DROP removes the whole table structure.
Worked example
Table students(id, name, marks, city). List names of students from Lucknow scoring 60 or more, highest first. SELECT name FROM students WHERE city = 'Lucknow' AND marks >= 60 ORDER BY marks DESC; Reasoning: WHERE filters rows before sorting; AND requires both conditions to hold; ORDER BY marks DESC puts the highest mark first. Adding LIMIT 10 would cap the list at ten rows.
Traps
- Writing marks = NULL; NULL means unknown and must be tested with IS NULL or IS NOT NULL.
- Using HAVING without GROUP BY to filter ordinary rows - WHERE is the right clause.
- Confusing DELETE (removes rows) with DROP (removes the table).
- Forgetting that text values need single quotes in standard SQL.
- Assuming a table can carry two primary keys; it can have a composite key, but only one primary key constraint.