Structured Query Language (SQL) (AQA GCSE Computer Science)

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 (flat file)
  • Select data (relational)
  • Order data
  • Insert data
  • Update data
  • Delete records

Selecting data commands (flat file)

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)
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')
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)
ORDER BY	How data is organised (sorted) when it is retrieved	SELECT Forename, Lastname FROM Students WHERE StudentID < 10 ORDER BY Lastname, Forename ASC (retrieves only the forename and lastname of all students from the students table who have a studentID of less than 10 and displays in ascending order by lastname and forename)

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

Nested select

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:

SELECT * FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees);

Output:

ID	Name	Salary	Department	City
2	Zarmeen Azra	52000	Sales	Manchester

Selecting data commands (relational)

Command	Description	Example
JOIN (INNER JOIN)	Combines data from two or more tables based on a related column	SELECT users.name, orders.order_id FROM users INNER JOIN orders ON users.user_id = orders.user_id; (retrieves user names and their corresponding order IDs)

Example

Table: Employees

EmployID	Name	Salary	City
1	Fynn Roberts	45000	London
2	Zarmeen Azra	52000	Manchester
3	Ella Stanley	39500	Birmingham

Table: Departments

DepartID	EmployID	Department	Manager	Email
1	1	HR	Sally Jones	[email protected]
2	2	Sales	Peter Evans	[email protected]
3	3	Marketing	Stuart Davies	[email protected]

• Select the name, manager and email address of employees in the sales department

Command:

SELECT Name, Manager, Email FROM Employees INNER JOIN Departments ON Employees.EmployID = Departments.EmployID WHERE Department = 'Sales';

Output:

Name	Manager	Email
Zarmeen Azra	Peter Evans	[email protected]

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:

INSERT INTO Employees (Name, Salary, Department, City) VALUES ('George Rope', 47250, 'Sales', 'Leeds') ;

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

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

• Update employee ID 3 to a salary of 47500 and city to London

Command:

UPDATE Employees SET Salary = 47500, City = 'London' WHERE ID = 3;

Output

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

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:

DELETE FROM Employees WHERE Department = 'Marketing' ;

Output:

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