# Structured Query Language (SELECT, FROM, WHERE) - **Track:** Cambridge IGCSE™ Computer Science (0478 / 0984) (CAMBRIDGE IGCSE 0478) - **Module:** Section 9: Relational Databases & SQL - **Estimated Duration:** 15 minutes - **Access:** Open Theory (All-Access for Interactive IDE & Autotests) --- ## Theory & Conceptual Background **Structured Query Language (SQL)** is the standard declarative programming language used worldwide to retrieve, manipulate, and manage data within relational database management systems. --- ## 1. The Basic SQL Query Structure To retrieve specific data from a database table, a query uses three fundamental clauses: ```sql SELECT , , ... FROM WHERE ; ``` ### The Roles of the Three Clauses: 1. **`SELECT` (What columns to display)**: - Lists the fields (columns) you want returned in the final result table. - To retrieve **all** fields from a table, use the asterisk wildcard symbol `*` (e.g., `SELECT * FROM Products`). 1. **`FROM` (Where the data is stored)**: - Specifies the exact table name containing the requested records. 1. **`WHERE` (Which rows to include)**: - Specifies the logical filter criteria. Only records for which the condition evaluates to `TRUE` are extracted. - The `WHERE` clause is optional; omitting it returns all records in the table. --- ## 2. SQL Comparison & Logical Operators The `WHERE` clause uses standard mathematical comparison operators: | Operator | Meaning | Example | | --- | --- | --- | | `=` | Equal to | `WHERE Status = 'Active'` | | `<>` | Not equal to | `WHERE Grade <> 'U'` | | `<` | Less than | `WHERE Age < 18` | | `<=` | Less than or equal to | `WHERE QuantityOnHand <= 10` | | `>` | Greater than | `WHERE Score > 75` | | `>=` | Greater than or equal to | `WHERE FinalMark >= 50` | ### Combining Conditions with Logical Operators: - **`AND`**: Returns records only if **all** conditions separated by `AND` are `TRUE`. ```sql WHERE ClassGrade = 10 AND FeesPaid = TRUE; ``` - **`OR`**: Returns records if **at least one** condition is `TRUE`. ```sql WHERE Subject = 'Physics' OR Subject = 'Chemistry'; ``` - **`NOT`**: Inverts the condition. ```sql WHERE NOT (Status = 'Graduated'); ``` --- ## 3. Worked Example: Tracing Query Execution Suppose we have a table named `ExamResults`: ```text +------------+--------------+-----------+-------+ | CandidateID| Subject | MarkScore | Passed| +------------+--------------+-----------+-------+ | C101 | CS | 88 | TRUE | | C102 | Maths | 92 | TRUE | | C103 | CS | 44 | FALSE | | C104 | CS | 76 | TRUE | | C105 | Physics | 81 | TRUE | +------------+--------------+-----------+-------+ ``` ### Query: ```sql SELECT CandidateID, MarkScore FROM ExamResults WHERE Subject = 'CS' AND MarkScore >= 50; ``` ### Step-by-Step Row Evaluation: 1. **Row 1 (`C101`)**: `Subject = 'CS'` (TRUE) AND `88 >= 50` (TRUE) $\rightarrow$ **MATCH** (Included). 2. **Row 2 (`C102`)**: `Subject = 'Maths'` (FALSE) $\rightarrow$ **NO MATCH** (Skipped). 3. **Row 3 (`C103`)**: `Subject = 'CS'` (TRUE) AND `44 >= 50` (FALSE) $\rightarrow$ **NO MATCH** (Skipped). 4. **Row 4 (`C104`)**: `Subject = 'CS'` (TRUE) AND `76 >= 50` (TRUE) $\rightarrow$ **MATCH** (Included). 5. **Row 5 (`C105`)**: `Subject = 'Physics'` (FALSE) $\rightarrow$ **NO MATCH** (Skipped). ### Result Table: ```text +------------+-----------+ | CandidateID| MarkScore | +------------+-----------+ | C101 | 88 | | C104 | 76 | +------------+-----------+ ``` --- ## 4. Key Syntax Rules & Exam Pitfalls 1. **Text Literals**: In SQL, text strings must always be enclosed in single quotation marks (e.g., `'CS'`, `'London'`). Numbers are written without quotes (e.g., `50`). 2. **Clauses Order**: The order `SELECT ... FROM ... WHERE ...` is fixed. Putting `FROM` before `SELECT` causes a syntax error. 3. **Punctuation**: SQL statements conventionally conclude with a semicolon (`;`). ## Selecting every field The asterisk below means all fields. It is not multiplication here. ```sql SELECT * FROM Results; ``` Choose only the required fields when a question specifies them. --- ## Interactive Exercises & Autotest Specifications ### Exercise 1: Exam-Style Check: Evaluating SQL WHERE Conditions with Logical Operators (Multiple choice, 25 XP) **Instructions:** A bookstore database contains an `Inventory` table with fields `Title`, `Author`, `Price`, and `Hardcover`. Consider the following SQL query: ```sql SELECT Title, Price FROM Inventory WHERE Price < 20.00 AND Hardcover = TRUE; ``` Which of the following book records will be returned by this query? **Options:** - **A**: A book with Price 24.00 and Hardcover TRUE - **B**: A book with Price 12.00 and Hardcover FALSE - **C**: A book with Price 29.99 and Hardcover FALSE - **D**: A book with Price 15.50 and Hardcover TRUE **Autotest Expectations:** - **Correct matching record identified**: `selection_match` -> Expected: `opt_correct_both` ### Exercise 2: Exam-Style Question: Constructing a Standard SQL Query (Fill in blank, 35 XP) **Instructions:** Complete the SQL query that retrieves the `StudentName` and `FinalMark` from the `Students` table for all students who scored at least 70 marks: ```sql [blank_1] StudentName, FinalMark [blank_2] Students [blank_3] FinalMark >= 70; ``` **Starter Code:** ```text {"template":"1. [blank_1]\n2. [blank_2]\n3. [blank_3]","word_bank":["SELECT","FROM","WHERE","ORDER BY","GROUP BY","HAVING"],"prompt":"Exam-Style Question: Constructing a Standard SQL Query","blanks":[{"id":"blank_1"},{"id":"blank_2"},{"id":"blank_3"}]} ``` **Autotest Expectations:** - **Column projection clause SELECT**: `text_exact` -> Expected: `SELECT` - **Table source clause FROM**: `text_exact` -> Expected: `FROM` - **Filtering criteria clause WHERE**: `text_exact` -> Expected: `WHERE` ### Exercise 3: Exam-Style Question: Construct SQL Query for Filtered Results (Sql, 40 XP) **Instructions:** A school database contains a table named `Results` with the following fields: `StudentID (INTEGER), StudentName (TEXT), Subject (TEXT), Mark (INTEGER), Grade (TEXT)` Write a single SQL query to display the `StudentName` and `Mark` of all students who achieved a `Mark` of 50 or greater. **Starter Code:** ```text {"schema":{"table_name":"Results","columns":[{"name":"StudentID","type":"INTEGER"},{"name":"StudentName","type":"TEXT"},{"name":"Subject","type":"TEXT"},{"name":"Mark","type":"INTEGER"},{"name":"Grade","type":"TEXT"}]},"dataset":[[101,"Alice Smith","Computer Science",78,"A"],[102,"Bob Jones","Computer Science",42,"U"],[103,"Charlie Brown","Computer Science",65,"B"],[104,"Daisy Miller","Computer Science",50,"C"],[105,"Ethan Davis","Computer Science",39,"U"]],"template":"-- Write your SQL query below\nSELECT "} ``` **Autotest Expectations:** - **Returns the correct matching student names and marks for scores >= 50**: `sql_match` -> Expected: `` - **Projects the expected columns StudentName and Mark**: `sql_columns` -> Expected: `StudentName, Mark` - **Returns exactly 3 records matching the criteria**: `row_count` -> Expected: `3` ### Exercise 4: Independent Practice: Combined condition (Sql, 30 XP) **Instructions:** Return Name and Score where Team is Red OR Score is below 40, ordered by Name ascending. Use the Results table: Name (TEXT), Team (TEXT), Score (INTEGER). **Starter Code:** ```text {"schema":{"table_name":"Results","columns":[{"name":"Name","type":"TEXT"},{"name":"Team","type":"TEXT"},{"name":"Score","type":"INTEGER"}]},"dataset":[["Uma","Blue",38],["Tao","Red",50],["Nia","Blue",45],["Inez","Blue",72],["Zed","Red",80],["Bea","Blue",40],["Omar","Red",49],["Suri","Blue",90],["Ava","Blue",39]],"template":"-- Write your query\n"} ``` ### Exercise 5: Independent Practice: Select all fields (Sql, 30 XP) **Instructions:** Return every field for records with Score at least 50, ordered by Score ascending. Use the Results table: Name (TEXT), Team (TEXT), Score (INTEGER). **Starter Code:** ```text {"schema": {"table_name": "Results", "columns": [{"name": "Name", "type": "TEXT"}, {"name": "Team", "type": "TEXT"}, {"name": "Score", "type": "INTEGER"}]}, "dataset": [["Inez", "Blue", 72], ["Omar", "Red", 49], ["Suri", "Blue", 90], ["Tao", "Red", 50], ["Uma", "Blue", 38]], "template": "-- Write your query\n"} ```