Structured Query Language (SQL)

Overview

Recall questions on SELECT, FROM, WHERE and ORDER BY, joining tables on their keys, LIKE and its wildcards, and writing INSERT, UPDATE and DELETE statements.

What you need to know

Answer each one before you reveal it.

Questions from this subtopic

Each one with the model answer underneath.

1. 1

   What is SQL used for?

   Model answer

   SQL is a language for working with the data in a relational database. It is used to retrieve data, to update existing data, to insert new data and to delete data, in each case across one or more tables, and it is also used to define the structure of a database table in the first place. The user states what data is wanted rather than how to find it, and the database management system works out how to carry the request out.

2. 2

   State the parts of a SELECT statement used to retrieve data, and what each part does.

   Model answer

   SELECT lists the attributes to be returned, or an asterisk for all of them. FROM names the table or tables the data comes from. WHERE gives a condition that a record must satisfy to be included, and is also where tables are matched to one another. ORDER BY names the attribute or attributes the results are sorted by, with ASC for ascending — the default — or DESC for descending. A statement ends with a semicolon. Only SELECT and FROM are required.

3. 3

   Write SQL to list the surname and telephone number of every member who joined before 1 January 2020, in alphabetical order of surname, from a table Member(MemberID, Surname, Forename, Telephone, DateJoined).

   Model answer

   SELECT Surname, Telephone FROM Member WHERE DateJoined < '2020-01-01' ORDER BY Surname ASC; The attributes wanted are named after SELECT rather than using an asterisk, so only those two columns are returned; the WHERE condition compares the date attribute with a date literal; and ORDER BY sorts the output, ASC being the default and therefore optional.

4. 4

   How do you retrieve data from more than one table in a single SELECT statement?

   Model answer

   Name every table needed in the FROM clause, and give a condition matching the foreign key in one table against the primary key in the other, so that only rows which genuinely belong together are combined. Where an attribute name appears in more than one table, qualify it with the table name and a full stop. For example: SELECT Member.Surname, Loan.DateDue FROM Member, Loan WHERE Member.MemberID = Loan.MemberID; The same query can be written using the join syntax: SELECT Member.Surname, Loan.DateDue FROM Member INNER JOIN Loan ON Member.MemberID = Loan.MemberID;

5. 5

   What happens if you list two tables in a FROM clause but leave out the condition matching their keys?

   Model answer

   Every row of the first table is combined with every row of the second, producing all possible pairings rather than the meaningful ones. With 500 members and 2000 loans that is a million rows, almost all of them pairing a member with a loan that is not theirs. The result is not merely slow but wrong, so the matching condition is essential, not an optimisation.

6. 6

   Write SQL to list the title of each book currently on loan together with the surname of the borrower, given Book(BookID, Title, AuthorID), Member(MemberID, Surname, Forename) and Loan(LoanID, BookID, MemberID, DateOut, DateReturned).

   Model answer

   SELECT Book.Title, Member.Surname FROM Book, Member, Loan WHERE Loan.BookID = Book.BookID AND Loan.MemberID = Member.MemberID AND Loan.DateReturned IS NULL ORDER BY Member.Surname; Three tables are needed because the link between Book and Member runs through Loan, so two matching conditions are required — one for each relationship. IS NULL selects loans with no return date recorded, which are the ones still out.

Other ways to work this

The same content, four other ways.

What is inside Levo

Six ways to work the same material.