SQL Your notes SQL 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 Order data Sum data Count data
Selecting data commands
Command Description Example
SELECT Retrieves data from a SELECT * FROM users;
database table (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 SELECT name, age FROM users;
retrieve data from (retrieves names and ages from the 'users' table)
WHERE Filters the data based on a SELECT * FROM users
specified condition WHERE age > 30; (Retrieves users older than 30)
AND Combines multiple SELECT * FROM users
conditions in a WHERE WHERE age > 18 AND city = 'New York'; clause (retrieves users older than 18 and from New York)
least one of the WHERE age < 18 OR city = 'New York'; Your notes conditions is true (retrieves users younger than 18 or from New York)
WILDCARDS '*' and '%' symbols are SELECT * FROM users;
used for searching and (retrieves all columns for the 'users' table) matching data '*' used to select all SELECT * FROM users WHERE name LIKE 'J%'; columns in a table (retrieves users whose names start with 'J') '%' used as a wildcard character in the LIKE operator
ORDER BY How data is organised SELECT Forename, Lastname FROM Students
(sorted) when it is WHERE StudentID < 10 retrieved 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)
SUM Adds up and outputs the SELECT SUM(Salary) FROM tbl_people;
sum of a field
COUNT Counts the number of SELECT COUNT(*)
files which match the set criteria FROM tbl_people WHERE Salary > 50000;
Examples Select all the fields from the Customers table Command:
Complete this SQL statement to display all of the animal breeds that are currently breeding and there was only one young born this year [3] SELECT ................................................. FROM ................................................. WHERE ................................................. Answer SELECT Animal [1] FROM tbl_animals [1] WHERE Number of Young == 1 [1]
