Lesson Objective
- Know what SQL is and its purpose.
- Use SQL statements to search for data using the SELECT, FROM, WHERE, ORDER BY statements.
- Use the wildcard *
- Formulate criteria involving AND, OR and LIKE.
mrahmedcomputing
KS3, GCSE, A-Level Computing Resources
Lesson 3. SQL (Basic)
Lesson Notes
Databases
Databases support a range of operations which are together known as SCRUD:
- S Search
- C Create
- R Read
- U Update
- D Delete
SQL
SQL stands for Structured Query Language. It is a programming language which allows you to create, query and manipulate databases. SQL is a text based querying system. It is used by all major relational database management systems. SQL is a declarative language (Gen 4): we state what we want, and not how it should be obtained.
Basic SQL Syntax:
| Keyword | Purpose in SQL Statments |
|---|---|
| SELECT | This keyword starts an SQL statement. It is followed by the names of the fields that are selected from the table or tables SELECT is a required keyword. |
| FROM | FROM is followed by the name(s) of the table(s) containing the fields specified in the SELECT command. |
| WHERE | The WHERE keyword specifies conditions used to filter (limit) the records that are returned by the SELECT (Search criteria). The WHERE keyword is optional. |
| ORDER BY | BY ORDER BY specifies the order in which you want the selected records to be sorted. The ORDER BY clause is optional. |
Example:
| MemberID | FirstName | Surname | Gender | Town |
|---|---|---|---|---|
| 1 | David | Johnson | M | Ipswich |
| 2 | Christine | Bates | F | Woodbridge |
| 3 | Jasmine | Hamid | F | Ipswich |
| 4 | Peter | Okello | M | Colchester |
| 5 | Stephen | Hines | M | Woodbridge |
SELECT and FROM
To display all fields and records:
SELECT MemberID,FirstName,Surname,Gender,Town FROM members_table
To display all fields and records (using the wild card):
SELECT * FROM members_table
To display certain fields and all records:
SELECT FirstName,Surname FROM members_table
WHERE
To display specific records:
SELECT * FROM members_table WHERE Town = "Ipswich"
To display specific records by defining additional conditions:
SELECT * FROM members_table WHERE Town = "Ipswich" OR Town = "Colchester"
LIKE
To display records where a value BEGINS with a certain letter:
SELECT * FROM members_table WHERE Surname LIKE "H%"
To display records where a value ENDS with a certain letter:
SELECT * FROM members_table WHERE Surname LIKE "%H"
To display records where a value CONTAINS a certain letter:
SELECT * FROM members_table WHERE Surname LIKE "%H%"
ORDER BY
To sort data in ascending order:
SELECT * FROM members_table ORDER BY Surname ASC
To sort data in descending order:
SELECT * FROM members_table ORDER BY Surname DESC