Databases and SQL

**1. Why Databases? — From Paper Registers to NADRA**

Imagine keeping records for an entire country on paper — that's why modern organisations use computerised databases. In Pakistan, NADRA maintains a database linking a citizen's CNIC number to identity information. Databases make large-scale record keeping possible.

A database is an organised collection of structured data stored electronically. A Database Management System (DBMS) is the software that manages the database (e.g., MySQL, PostgreSQL, SQLite).

Why databases over spreadsheets?

• Handle millions of records efficiently
• Multiple users can access simultaneously
• Built-in security and access control
• Reduce data redundancy (avoid duplicate data)
• Maintain data integrity and consistency

**2. Database Structure — Tables, Records, and Fields**

A relational database stores data in tables (also called relations).

| StudentID | Name | City | Board | Subject |

|---|---|---|---|---|

| 1001 | Ahmed Khan | Karachi | EdExcel | Computer Science |

| 1002 | Fatima Ali | Lahore | Cambridge | Physics |

| 1003 | Hassan Raza | Islamabad | AKU-EB | Mathematics |

• Table: a collection of related data (e.g., Students table)
• Record (row): one complete entry (e.g., Ahmed Khan's data)
• Field (column): one category of data (e.g., City)
• Primary key: a unique identifier for each record (e.g., StudentID — no two students share the same ID)
• Foreign key: a field in one table that links to the primary key of another table

Data types:

• INTEGER: whole numbers (StudentID: 1001)
• TEXT/VARCHAR: strings ("Ahmed Khan")
• REAL/FLOAT: decimal numbers (89.5)
• BOOLEAN: TRUE/FALSE
• DATE: dates (2025-03-15)

**3. SQL — Structured Query Language**

SQL is the standard language for interacting with relational databases. The exam tests four key commands:

SELECT — retrieve data:

```sql

SELECT Name, City FROM Students WHERE Board = 'EdExcel';

```

Returns names and cities of all EdExcel students.

INSERT — add new records:

```sql

INSERT INTO Students (StudentID, Name, City, Board, Subject)

VALUES (1004, 'Zainab Malik', 'Peshawar', 'FBISE', 'Biology');

```

UPDATE — modify existing data:

```sql

UPDATE Students SET City = 'Faisalabad' WHERE StudentID = 1003;

```

DELETE — remove records:

```sql

DELETE FROM Students WHERE StudentID = 1002;

```

Important SQL clauses:

• WHERE: filter rows by condition
• ORDER BY: sort results (ASC for ascending, DESC for descending)
• AND / OR: combine conditions
• LIKE: pattern matching (`WHERE Name LIKE 'A%'` finds names starting with A)

Example: "Find all students in Karachi studying Computer Science, sorted by name":

```sql

SELECT * FROM Students

WHERE City = 'Karachi' AND Subject = 'Computer Science'

ORDER BY Name ASC;

```

**4. Database Design and Validation**

Validation checks that data entered is reasonable:

• Range check: marks must be between 0 and 100
• Length check: CNIC must be exactly 13 digits
• Type check: age must be a number, not text
• Presence check: name field cannot be empty
• Format check: email must contain @ symbol

Verification checks that data entered matches the source:

• Double entry: type data twice and compare
• Visual check: human reads and confirms

Normalisation reduces redundancy by splitting data into related tables linked by keys. Instead of repeating "Karachi" 1000 times in one table, create a Cities table and link by CityID.

**5. Exam Strategy**

• SQL syntax must be exact — capitalise keywords (SELECT, FROM, WHERE) for clarity.
• String values in SQL use single quotes: `WHERE City = 'Karachi'` not double quotes.
• Primary key question: choose the field that is UNIQUE for every record.
• When asked to write a query, read the question carefully — does it say "all fields" (use *) or "specific fields" (list them)?
• Validation vs verification: validation = is the data reasonable? Verification = is it what was intended?
• ORDER BY defaults to ASC — only specify DESC when needed.