课 18, 主题 2

In Progress

← Previous Next→

sql

课 Progress

0% Complete

Selecting Data (Single Table)

What is SQL?

In A Level Computer Science, SQL (Structured Query Language) is a programming language used to interact with a DBMS.
The use of SQL allows a user to:
- Select data (single table)
- Select data (multiple tables)
- Insert data
- Delete records
- Delete tables

Selecting data (single table) commands

Command	Description	Example
SELECT	Retrieves data from a database table	SELECT * FROM users; (retrieves all data from the ‘users’ table) SELECT name, age FROM users (retrieves names and ages from the ‘users’ table)
FROM	Specifies the tables to retrieve data from	SELECT name, age FROM users; (retrieves names and ages from the ‘users’ table)
WHERE	Filters the data based on a specified condition	SELECT * FROM users WHERE age > 30; (Retrieves users older than 30)
LIKE	Filters the data based on a specific pattern	SELECT * FROM users WHERE name LIKE ‘J%’; (retrieves users whose names start with ‘J’)
AND	Combines multiple conditions in a WHERE clause	SELECT * FROM users WHERE age > 18 AND city = ‘New York’; (retrieves users older than 18 and from New York)
OR	Retrieves data when at least one of the conditions is true	SELECT * FROM users WHERE age < 18 OR city = ‘New York’; (retrieves users younger than 18 or from New York)
WILDCARDS	‘‘ and ‘%‘ symbols are used for searching and matching data ‘‘ used to select all columns in a table ‘%‘ used as a wildcard character in the LIKE operator	SELECT * FROM users; (retrieves all columns for the ‘users’ table) SELECT * FROM users WHERE name LIKE ‘J%’; (retrieves users whose names start with ‘J’)

Examples

Select all the fields from the Customers table

Command:

code

Output:

ID	Name	Age	City	Country
1	John Doe	30	New York	USA
2	Jane Doe	25	London	UK
3	Peter Lee	40	Paris	France

Select the ID, name & age of customers who are older than 25

Command:

code

Output:

ID	Name	Age
1	John Doe	30
3	Peter Lee	40

Select the name and country of customers who are from a country that begins with ‘U’

Command:

code

Output:

Name	Country
John Doe	USA
Jane Doe	UK

Select all fields of customers who are from ‘London’ or ‘Paris’

Command:

code

Output:

ID	Name	Age	City	Country
2	Jane Doe	25	London	UK
3	Peter Lee	40	Paris	France

Worked Example

Customers’ details are stored in the flat file database table Customer. An extract of the table is shown below

relational-database

Write the S

Responses