SQL
What is SQL?
- SQL (Structured Query Language) is a programming language used to interact with a DBMS.
- SQL allows users to locate specific information in a database table using these basic SQL commands:
- Select - what field(s) do you want to retrieve?
- From - which table(s) do you want to search?
- Where - what condition is there?
Selecting Data Commands
|
|
Example |
SELECT | Retrieves data from a database table |
SELECT * SELECT name, age |
FROM | Specifies the tables to retrieve data from |
SELECT * SELECT name, age |
WHERE | Filters the data based on a specified condition |
SELECT * |
- The '*' symbol is called a 'wildcard', it selects all fields in the table
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 |
Worked example
The database table Stock
stores the current stock levels of products currently on sale.
ProdName | Quantity | Price |
Biscuits | 143 | 0.99 |
Bread | 87 | 1.49 |
Milk | 34 | 1.10 |
Pasta | 421 | 0.89 |
Ketchup | 287 | 2.99 |
Complete the SQL query to return the product name and quantity of all products that's price is less than £1 [3]
SELECT | |
FROM | |
WHERE |
Answer
SELECT | ProdName, Quantity |
FROM | Stock |
WHERE | Price < 1 |
Guidance
- Spelling of field names and table name must be exact
- Capitalisation must match field and table names to be awarded marks
- Table name is always in the question in different format