GCSE Link: 7.02 (SELECT Statements)
On this page we will learn how to create tables using SQL, and also about more complex SQL queries with joins.
Example 1 shows a SQL statement to CREATE a TABLE called Students.
Example 1
CREATE TABLE Students (
StudentID INTEGER PRIMARY KEY,
FirstName TEXT NOT NULL,
LastName TEXT NOT NULL,
DateOfBirth DATETIME
)
In general, you must write the field identifier, then the data type, then any constraints (such as "primary key" or "not null") for each field.
Now we have an empty table, so we can put some data into it!
Example 2 shows a SQL statement to INSERT a row INTO the Students table.
Example 2
INSERT INTO Students VALUES (
1225, "William",
"Smith", 15/04/2009
)
At GCSE, we learnt how to use the WHERE keyword to join tables together.
However, we will now learn about the JOIN keyword, which provides an
alternative way to combine tables in relational databases.
Example 3 (WHERE) and Example 4 (JOIN...ON) are equivalent.
Example 3
SELECT * FROM
Table1, Table2
WHERE Table1.PrimaryKey
= Table2.ForeignKey
Example 4
SELECT * FROM
Table1 JOIN Table2
ON Table1.PrimaryKey
= Table2.ForeignKey
Table 1 shows part of a new Grades table in the same database as the Students table we just created.
Table 1
| StudentID | Subject | Percentage | Grade |
|---|---|---|---|
| 1225 | Maths | 78 | 8 |
| 1227 | Computer Science | 61 | 6 |
| 1226 | Chemistry | 94 | 9 |
| 1227 | Chemistry | 81 | 8 |
| 1228 | Geography | 69 | 7 |
Write a SQL query using the JOIN keyword to find the subject, grade, and
student's full name for all records in the Grades table where the grade is
below 8, ordered by the grade from highest to lowest.
SELECT Subject, Grade,
FirstName, LastName
FROM Grades JOIN
Students
ON
Grades.StudentID =
Students.StudentID
WHERE Grade < 8
ORDER BY Grade DESC