Structured Query Language (SQL) revision notes

Purpose of SQL

Structured Query Language (SQL) is used to work with data in relational databases. For this topic, the key skills are defining a database table and using SQL to retrieve, insert, update and delete data. These operations can involve one table or multiple tables.

Defining a table

A table can be defined with CREATE TABLE. The definition gives the table a name and specifies its columns and suitable data types.

sql CREATE TABLE Student ( StudentID INTEGER, StudentName VARCHAR(50), YearGroup INTEGER );

This statement defines a table called Student with three columns. The semicolon marks the end of the SQL statement.

Retrieving data

SELECT retrieves data. FROM identifies the table, and WHERE restricts the rows considered by the condition.

sql SELECT StudentName, YearGroup FROM Student WHERE YearGroup = 12;

To retrieve data using multiple tables, include the tables in the query and specify how related rows should be matched. For example:

sql SELECT Student.StudentName, Course.CourseName FROM Student JOIN Course ON Student.CourseID = Course.CourseID;

The selected columns identify the required data, while the ON condition gives the relationship used to match rows from the two tables.

Inserting data

INSERT INTO adds a row to a table. The listed columns correspond to the values in the VALUES list.

sql INSERT INTO Student (StudentID, StudentName, YearGroup) VALUES (101, 'Aisha Khan', 12);

The order and meaning of the values must match the listed columns.

Updating data

UPDATE changes existing data. SET gives the new value, and WHERE identifies which rows are changed.

sql UPDATE Student SET YearGroup = 13 WHERE StudentID = 101;

The WHERE condition is important because it limits the update to the intended row or rows.

Deleting data

DELETE FROM removes rows from a table.

sql DELETE FROM Student WHERE StudentID = 101;

Again, WHERE identifies the rows to remove. Omitting or incorrectly writing the condition can cause more rows than intended to be deleted.

Common errors

• Confusing UPDATE, which changes existing rows, with INSERT, which adds a new row.
• Using DELETE when the task requires changing a value.
• Forgetting the WHERE condition when only particular rows should be changed or removed.
• Selecting columns from multiple tables without specifying how the tables are matched.
• Giving values in an INSERT statement in an order that does not match the listed columns.