4 - 18 Introduction to SQL

Question 1

What does SQL stand for?

Answer 1

Structured Query Language

Question 2

What type of language is SQL?

Answer 2

SQL is a declarative language

Question 3

What is SQL used for?

Answer 3

SQL is used for querying and updating tables in a relational database. It can also be used to create tables.

Question 4

Give the basic syntax for the SELECT statement.

Answer 4

SELECT list the fields to be displayed
FROM list the table or tables the data will come from
WHERE list the search criteria
ORDER BY list the fields that the results are to be sorted on (default is Ascending order)

Question 5

Give the meaning, example and any notes that may be included with the operator symbol being ‘=’

Answer 5

Equal to e.g. CDTitle = “Autumn”. Different implementations use single or double quotes

Question 6

Give the meaning, example and any notes that may be included with the operator symbol being ‘>’

Answer 6

Greater than, eg.g. DatePublished > #01/01/2015#. The date is enclosed in quote marks or, in Access, # symbols

Question 7

Give the meaning, example and any notes that may be included with the operator symbol being ‘

Answer 7

Less than, e.g. Date Published < #01/01/2015#

Question 8

Give the meaning, example and any notes that may be included with the operator symbol being ‘!=’

Answer 8

Not equal to, e.g. RecordCompany != “ABC”

Question 9

Give the meaning, example and any notes that may be included with the operator symbol being ‘>=’

Answer 9

Greater than or equal to, e.g. DatePublished >= #01/01/2015#

Question 10

Give the meaning, example and any notes that may be included with the operator symbol being ‘<=’

Answer 10

Less than or equal to, e.g. DatePublished <= #01/01/2015#

Question 11

Give the meaning, example and any notes that may be included with the operator symbol being ‘IN’

Answer 11

Equal to the value within a set o values, RecordCompany IN (“ABC”, “DEF”)

Question 12

Give the meaning, example and any notes that may be included with the operator symbol being ‘LIKE’

Answer 12

Similar to, e.g. CDTitle LIKE “S%”. Finds shadows (wildcard operator varies and can be *)

Question 13

Give the meaning, example and any notes that may be included with the operator symbol being ‘BETWEEN… AND’

Answer 13

Within a range, including the two values which define the limits, e.g. DatePublished BETWEEN #01/01/2015# AND #31/12/2015#

Question 14

Give the meaning, example and any notes that may be included with the operator symbol being ‘IS NULL’

Answer 14

Field does not contain a value, e.g. RecordCompany is NULL

Question 15

Give the meaning, example and any notes that may be included with the operator symbol being ‘AND’

Answer 15

Both expressions must be true for the entire expression to be judged true, e.g. DatePubished > #01/01/2015# AND RecordCompany = “ABC”

Question 16

Give the meaning, example and any notes that may be included with the operator symbol being ‘OR’

Answer 16

If either or both of the expressions are true, the entire expression is judged true, e.g. RecordCompany = “ABC” OR RecordCompany = “DEF”. Equilvalent to RecordCompany IN (“ABC”, “DEF”)

Question 17

Give the meaning, example and any notes that may be included with the operator symbol being ‘NOT’

Answer 17

Inverts truth, ReordCompany NOT IN (“ABC, “DEF”)

Question 18

What does the SQL JOIN do?

Answer 18

JOIN provides an alternative method of combining rows from two or more tables, based on a common field between them.

Question 19

An example of SQL JOIN

Answer 19

SELECT Song.SongTitle, Artist.ArtistName, Song.MusicType
FROM Song
JOIN Artist
ON Song.ArtistID = Artist.ArtistID
WHERE Song.MusicType = "Art Pop"

Question 20

What is the correct SQL syntax to select data where more than one table is involved?

Answer 20

tablename.fieldname (The table name is optional unless the field name appears in more than one table.)