UK K12 GCSE A-Level Year 9 KS3Pre-GCSE Computer Science Databases SQL Fundamentals

Learning Objectives

By the end of this topic, students will be able to:

• Define what a database is and its purpose in computer systems.
• Explain the basic concepts of Structured Query Language (SQL).
• Write simple SQL queries to retrieve and manipulate data.
• Identify and correct common errors in SQL syntax.
• Apply SQL fundamentals to real-world scenarios.

Core Concepts

A database is a collection of organized data that can be efficiently accessed, managed, and updated. SQL (Structured Query Language) is a standard language for managing relational databases. It allows users to store, manipulate, and retrieve data.

Database Structure

A database consists of:

• Tables: A table is a collection of related data, similar to an Excel spreadsheet. Each table has rows (records) and columns (fields).
• Fields: A field is a single piece of data within a table, such as a name or address.
• Records: A record is a single row in a table, containing data for each field.

SQL Basics

SQL has several key concepts:

• SELECT: Retrieves data from a database table.
• FROM: Specifies the table(s) to retrieve data from.
• WHERE: Filters data based on conditions.
• ORDER BY: Sorts data in ascending or descending order.

SQL Syntax

SQL syntax is case-insensitive, but it's common to use uppercase for keywords and lowercase for field names.

Data Types

SQL supports various data types, including:

• INT: Whole numbers (e.g., 1, 2, 3).
• VARCHAR: Character strings (e.g., 'hello', 'world').
• DATE: Dates in the format 'YYYY-MM-DD'.

Worked Examples

Example 1: Simple SELECT Query

Suppose we have a table called 'students' with fields 'name', 'age', and 'grade'. We want to retrieve the names of all students with a grade above 80.

SELECT name
FROM students
WHERE grade > 80;

Example 2: Filtering Data

We want to retrieve the names of all students who live in 'London' and have an age above 16.

SELECT name
FROM students
WHERE city = 'London' AND age > 16;

Example 3: Sorting Data

We want to retrieve the names of all students sorted in ascending order by age.

SELECT name
FROM students
ORDER BY age ASC;

Common Misconceptions

• SQL is case-sensitive. (Incorrect - SQL is case-insensitive.)
• You can use any field name as a table name. (Incorrect - table and field names must be unique within a database.)
• You can use a SELECT statement without a FROM clause. (Incorrect - a SELECT statement requires a FROM clause.)

Exam Tips

• Read the question carefully and make sure you understand what is being asked.
• Use the correct SQL syntax and data types.
• Test your queries on sample data before submitting them.
• Pay attention to the order of operations and use parentheses to clarify complex queries.

MCQs

Question 1 [F]

What is the purpose of the SELECT statement in SQL? A) To insert new data into a table B) To delete data from a table C) To retrieve data from a table D) To update data in a table

Correct answer: C) To retrieve data from a table Why the distractors fail: A and B are incorrect because SELECT is used for retrieval, not insertion or deletion. D is incorrect because UPDATE is used for modifying existing data.

Question 2 [H]

What is the difference between the AND and OR operators in SQL? A) AND returns true if both conditions are true, while OR returns true if either condition is true B) AND returns true if either condition is true, while OR returns true if both conditions are true C) AND returns false if either condition is false, while OR returns false if both conditions are false D) AND returns true if both conditions are false, while OR returns true if either condition is false

Correct answer: A) AND returns true if both conditions are true, while OR returns true if either condition is true Why the distractors fail: B and C are incorrect because AND returns true if both conditions are true, while OR returns true if either condition is true. D is incorrect because AND returns true if both conditions are true, while OR returns true if either condition is true.

Question 3 [F]

What is the correct syntax for sorting data in ascending order using the ORDER BY clause? A) ORDER BY age DESC B) ORDER BY age ASC C) ORDER BY age D) ORDER BY age IN ASC

Correct answer: B) ORDER BY age ASC Why the distractors fail: A is incorrect because DESC is used for descending order, not ascending. C is incorrect because the ORDER BY clause requires a direction (ASC or DESC). D is incorrect because IN is not a valid direction for the ORDER BY clause.

Question 4 [H]

What is the purpose of the WHERE clause in SQL? A) To specify the table(s) to retrieve data from B) To filter data based on conditions C) To sort data in ascending or descending order D) To insert new data into a table

Correct answer: B) To filter data based on conditions Why the distractors fail: A is incorrect because FROM is used to specify the table(s) to retrieve data from. C is incorrect because ORDER BY is used to sort data. D is incorrect because INSERT is used to insert new data into a table.

Question 5 [F]

What is the correct syntax for retrieving the names of all students with a grade above 80? A) SELECT name FROM students WHERE grade > 80 B) SELECT name FROM students WHERE grade = 80 C) SELECT name FROM students WHERE grade < 80 D) SELECT name FROM students WHERE grade >= 80

Correct answer: A) SELECT name FROM students WHERE grade > 80 Why the distractors fail: B is incorrect because it retrieves students with a grade of exactly 80, not above 80. C is incorrect because it retrieves students with a grade below 80, not above 80. D is incorrect because it retrieves students with a grade of 80 or above, but the question asks for students with a grade above 80.

Short-answer questions

Question 1

Explain the difference between the AND and OR operators in SQL. Provide an example of each operator in use.

Question 2

Write a SQL query to retrieve the names of all students who live in 'London' and have an age above 16.

Question 3

What is the purpose of the ORDER BY clause in SQL? Provide an example of using the ORDER BY clause to sort data in ascending order.

Question 4

Explain the concept of a database and its purpose in computer systems. Provide an example of a simple database schema.

Question 5

Write a SQL query to retrieve the names of all students sorted in descending order by age.