Structured query language (SQL)

Question 1

What is SQL used for?

Answer 1

creating, maintaining and accessing databases

it allows users to search for records that contain a particular item or items of data

Question 2

data

Answer 2

units of information

Question 3

database

Answer 3

data store designed in an organised way, making it easier to search for the information you need

Question 4

records

Answer 4

all of the data related to one entity in a database

Question 5

What is data in a database stored as?

Answer 5

records

Question 6

What are records stored in?

Answer 6

files

Question 7

attribute

Answer 7

a single database component

Question 8

List 6 SQL commands

Answer 8

SELECT
FROM
WHERE
LIKE
AND
OR

Question 9

How are databases formatted/what do they look like?

Answer 9

a table, for example:

StudentNo FirstName Surname DOB TutorGroup
001123 Fred Smith 28/01 10X
001142 Angela Smith 13/10 10Y
002567 Mary Green 15/2 10X

Question 10

WHERE keyword

Answer 10

used for searching

e.g.
SELECT FirstName, Surname (the fields to display)
FROM Students (the name of the database)
WHERE TutorGroup = “10X” AND DOB > 31/1/2001; (the search criteria)

Question 11

formatting an SQL command (5)

Answer 11

• write commands on separate lines to make them easier to understand
• every command (not line) ends with a ;
• commands are not case sensitive
• fields are separated with a comma
• the fields may also be shown in quotations

Question 12

SELECT keyword

Answer 12

used to display records

e.g.
SELECT * FROM Students; (Displays all the records and shows all the fields, the * symbol means ‘show all fields’)

SELECT FirstName, Surname FROM Students;

Question 13

wildcards

Answer 13

• the % symbol is used as a substitute for one or many characters
• the * symbol is used to represent ‘all fields’

WHERE TutorGroup LIKE “%X”;

…returns all fields with a tutor group ending in an X, and any characters before that

Question 14

LIKE command

Answer 14

finds matches for an incomplete word:

SELECT * FROM Students WHERE TutorGroup LIKE “%X”;

the = command can be used to find complete words

Question 15

AND and OR commands (Boolean operators)

Answer 15

can also be used to retrieve data

Question 16

FROM command

Answer 16

specifies the name of the database to search

Question 17

Details of the employees of a multinational company are stored in a database table named ‘Employees’ using these fields:

• EmployeeNo
• FirstName
• Surname
• DOB
• Country
• JobFunction

Write the SQL commands that would return the following records:

a) All employees showing only the Surname and Country fields. [2]
b) Employees with the word ‘computer’ in their job titles. All fields should be displayed. [2]

Answer 17

a) SELECT Surname, Country FROM Employees;

b) SELECT * FROM Employees
WHERE JobFunction LIKE “%computer%”;

Question 18

Details of a school’s students are stored in a database named ‘Students’ containing the following fields:

• FirstName
• Surname
• DOB
• Gender
• Address1
• Address2
• Town
• PostCode
• SchoolYear
• TutorGroup

Write the SQL commands that would return the following records:

a) all students showing the FirstName, Surname and PostCode fields
b) Students who are in the year 10 tutor group named East showing all the fields. [5]

Answer 18

a) SELECT FirstName, Surname, Postcode FROM Students;

b) SELECT * FROM Students
WHERE TutorGroup = “EAST” AND SchoolYear = 10;