SQL

Selecting Data (Single Table)

What is SQL?

• 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:

SELECT * FROM Customers;

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:

SELECT ID, name, age
FROM Customers
WHERE Age > 25;

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:

SELECT Name, Country
FROM Customers
WHERE COuntry LIKE 'U%';

Output:

Name	Country
John Doe	USA
Jane Doe	UK

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

Command:

SELECT *
FROM Customers
WHERE City = 'London' OR City = 'Paris';

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

Write the SQL statement that would show only the CustomerID and Surname fields for customers with the Title “Miss” or “Mrs”

[4]

Answer: SELECT CustomerID, Surname [1] FROM Customer [1] WHERE Title=“Miss” [1] OR Title = “Mrs” [1]

Selecting Data (Multiple Tables)

Command	Description	Example
Nested SELECT	A select within another select statement (nested). A mini select within the main one	SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users); (retrieves users with an age greater than the average age)
JOIN ** (INNER JOIN)**	Combines data from two or more tables based on a related column	SELECT users.name, orders.order_id FROM users JOIN orders ON users.user_id = orders.user_id; (retrieves user names and their corresponding order IDs)

Examples

Table: Employees

ID	Name	Salary	Department	City
1	Fynn Roberts	45000	HR	London
2	Zarmeen Azra	52000	Sales	Manchester
3	Ella Stanley	39500	Marketing	Birmingham

• Select all fields for employees whose salary is bigger than the average salary of all employees

Command: Output:

ID	Name	Salary	Department	City
2	Zarmeen Azra	52000	Sales	Manchester

Inserting Data

Command	Description	Example
INSERT	Adds new data to a database table	INSERT INTO users (name, age) VALUES (‘John Doe’,25); (inserts a new user with the name ‘John Doe’ and age 25)

Example

Table: Employees

ID	Name	Salary	Department	City
1	Fynn Roberts	45000	HR	London
2	Zarmeen Azra	52000	Sales	Manchester
3	Ella Stanley	39500	Marketing	Birmingham

• Insert a new employee into the Employees table with the ‘Name’, ‘Salary’, ‘Department’ and ‘City’ fields

Command: Output:

ID	Name	Salary	Department	City
1	Fynn Roberts	45000	HR	London
2	Zarmeen Azra	52000	Sales	Manchester
3	Ella Stanley	39500	Marketing	Birmingham
4	George Rope	47250	Sales	Leeds

Deleting Records

Command	Description	Example
DELETE	Removes data from a database table	DELETE FROM users WHERE age < 18; (deletes all users younger than 18 from the ‘users’ table) DELETE FROM users WHERE name=“John”; (deletes a record where the name is John)

Example

Table: Employees

ID	Name	Salary	Department	City
1	Fynn Roberts	45000	HR	London
2	Zarmeen Azra	52000	Sales	Manchester
3	Ella Stanley	39500	Marketing	Birmingham
4	George Rope	47250	Sales	Leeds

• Delete all records from the Employees table whose department is ‘Marketing’

Command: Output:

ID	Name	Salary	Department	City
1	Fynn Roberts	45000	HR	London
2	Zarmeen Azra	52000	Sales	Manchester
3	George Rope	47250	Sales	Leeds

Worked Example

A database stores information about songs on a music streaming service. One of the tables called Song has the fields Title, Artist, Genre and Length

A band called RandomBits removes their permission for their songs to be streamed. The company removes all the songs belonging to RandomBits from their service.

Write an SQL statement that will remove all songs by RandomBits from the table Song

[2]

Answer: DELETE FROM Song [1] WHERE Artist = “RandomBits” [1]

Deleting Tables

Command	Description	Example
DROP	Deletes a table in a database	DROP TABLE users; (deletes the ‘users’ table)

Example

Table: Employees

ID	Name	Salary	Department	City
1	Fynn Roberts	45000	HR	London
2	Zarmeen Azra	52000	Sales	Manchester
3	Ella Stanley	39500	Marketing	Birmingham
4	George Rope	47250	Sales	Leeds

• Delete the Employees table

Command: Output