Structured Query Language (SQL) study guide

Start by identifying the required operation

Read the task and decide whether it asks you to retrieve, insert, update or delete data, or to define a table. The verb in the task should lead to the main SQL command:

• retrieve data: SELECT
• add data: INSERT INTO
• change existing data: UPDATE
• remove data: DELETE FROM
• define a table: CREATE TABLE

This first decision prevents using a command that performs a different operation.

Build the statement from the data requirement

For a retrieval task, identify the columns that must be displayed, the table or tables containing them, and any condition that limits the result. For example, if the required output contains a student's name and a course name stored in different tables, a query must select both columns and use a relationship between the tables:

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

The SELECT list controls which data is retrieved. FROM and JOIN identify the tables. The ON expression explains which values are used to match rows. If a condition is also required, add a WHERE clause, such as:

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

Here, the join combines information from two tables, while WHERE restricts the result to students in the specified year group. Keeping the matching condition and the filtering condition distinct makes the reasoning clearer.

Reason about changes carefully

For INSERT INTO, check that every supplied value corresponds to the intended column. For UPDATE, identify the exact rows to change and place that selection in WHERE. For DELETE FROM, use the same careful approach because the command removes rows rather than merely displaying or changing them.

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

The statement changes the value for the row identified by StudentID. A different condition would change a different set of rows, so an exam answer should explain why its condition selects the intended data.

Defining a table

When asked to define a table, provide CREATE TABLE, a table name, column names and data types. The definition should be read as a description of the structure that later SQL statements will use.

sql CREATE TABLE Course ( CourseID INTEGER, CourseName VARCHAR(60) );

The later SELECT, INSERT, UPDATE and DELETE statements must refer to names that were defined consistently. Check spelling, table qualification and the correspondence between columns and values.

Self-check before submitting an answer

Ask yourself:

1. Does the main command match the requested operation?
2. Are the table and column names consistent throughout?
3. If multiple tables are used, is the matching relationship explicitly stated?
4. If only selected rows should be changed or removed, is there an appropriate WHERE condition?
5. In an insertion, do the values align with the listed columns?
6. In a table definition, are the table name, columns and data types present?

A strong answer is not just syntactically plausible: each clause should have a clear role connected to the task.