3.7 Relational Databases and SQL

Question 1

L1
1) Explain the concept of a database
2) What is a primary key?
3) What are the relational database data types?

Answer 1

1) A database is a structured way to store data so that it can be retrieved using queries.
2) A primary key is a field that stores unique data for each record in a table
3) - Integer (whole number)
- Real, Float, Decimal (number with a decimal component)
- Date, Time, Datetime (to store a dates and times)
- Char (fixed length string up to 8,000 characters)
- Varchar (variable length string up to 8,000 characters)
- Text (variable length string up to 2 GB of data)

Question 2

L1
1) What is a table?
2) What is a record?
3) What is a field?

Answer 2

1) A table stores records and fields in an organised manner.
2) A record is an individual collection of data for one person/object, a row in a table
3) A field is one item of data, a column.

Question 3

L1
1) What is a flat file database?
2) How are flat file databses stored?
3) What are the disadvantages of flat file databases?

Answer 3

1) A flat file database stores a single table of data inside a single text file.
2) Flat file databases are often stored using a CSV (comma separated values) format. Each record appears on a separate line. Each field is separated by a comma. This format is very easy to set up, however, it is hard to manage for anything but the simplest of data sets.
3) Flat files mean that we often have to repeat data in each record. This leads to inconsistencies in the data – these make it hard to search or sort the data. This also causes redundant data – so the database uses more memory or storage than it needs to, it may also take longer to search. Relational databases solve this problem.

Question 3

L2
1) What is a relational database?
2) What is a foreign key?
3) What are the different relationships that a table can have?

Answer 3

1) A relational database contains multiple tables. These tables will have links known as relationships. Each table is also known as a relation. Relational databases allow us to design tables that reduce inconsistencies and eliminate data redundancy.
2) A foreign key is a field in a table that references the primary key of another table.
3) Relationships between tables can be: one-to-one, many-to-many, one-to-many

Question 3

L2
What are the symbols for the following relationships in tables?
1) One-to-one
2) Many-to-many
3) One-to-many

Answer 3

1) The symbol is a straight line
2) The symbol is ‘crow’s feet’ at both ends of the straight line.
3) The symbol is ‘crow’s feet’ at the right ends of the straight line.

Question 4

L3
1) What is SQL?
2) What is the SQL syntax for querying a database?

Answer 4

1) SQL stands for Structured Query Language.
It is a language which allows you to create, query, update and delete data to and from databases
2) SELECT … (list the fields to be displayed)
FROM … (specify the table name)
WHERE … (list the search criteria)

Question 5

L4
How do you create a table in SQL?

Answer 5

CREATE TABLE tablename
(column1 datatype,
column2 datatype,
column3 datatype);
- The name of the table is ‘tablename’
- The title of each field is ‘column’
The tablename cannot have a space.

Question 6

Explain how the select statement works in SQL

Answer 6

• You can select specific columns in a query:
  SELECT FirstName, Surname
  FROM members
• You can use a “wild card” * to mean “all columns”
  SELECT *
  FROM members

Question 7

L3
1) Explain how the WHERE cause works
2) Explain the BETWEEN SQL queries
3) Explain the LIKE SQL queries

Answer 7

1) - The WHERE clause is used to select only records satisfying a specified condition:
SELECT FirstName, Surname
FROM members
WHERE Town = ‘Ipswich’

• You can use the following operators in SQL queries:
  = != > < >= <= AND, OR, NOT
  eg. SELECT *
  FROM members
  WHERE Town = ‘Colchester’ OR Town = ‘Ipswich’

SELECT FirstName, Surname
FROM members
WHERE (Town = ‘Colchester’ OR Town = ‘Ipswich’) AND Gender = ‘F’

2) BETWEEN - between an inclusive range
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
DateOfBirth BETWEEN #01/01/2018#
AND #31/12/2019#

3) LIKE - search for a pattern
For example:
SELECT *
FROM members
WHERE Surname LIKE ‘H*’
This selects all members whose surname begins with H
Note the wildcard * in the LIKE string which acts as a substitute for zero, one or more characters

Question 8

1) Explain the ORDER BY keyword
2) Explain the UPDATE keyword

Answer 8

1) - ORDER BY allows a query to sort data by ascending or descending order
- For ascending order SELECT * FROM members
ORDER BY Surname ASC
- For descending order SELECT * FROM members ORDER BY Surname DESC
2) - Data stored in records can be changed
- More than one record can be changed at a time
- UPDATE Dogs SET Age = 4 WHERE Age = 3

Question 9

1) Explain how the DELETE keyword works
2) Explain how to select information from multiple tables

Answer 9

1) - The WHERE criteria allow more than one record to be deleted at a time
- It is important if using tables with relationships that these aren’t affected by the deletion
- DELETE FROM Dogs WHERE Breed = ‘Labrador’ AND Colour = ‘Brown’
2) - A query can be made which selects information from two tables
- SELECT Owners.Firstname, Owners.Lastname, Dogs.Age FROM Owners, Dogs
WHERE Owners.DogsID = Dogs.DogsID
AND Dogs.Age = 4

Question 10

Explain how the INSERT keyword works

Answer 10

• We can INSERT a record into a table
• We do not need to provide a value for an Autonumber field as this will be done automatically
• If the value is a foreign key it will have to be provided
• INSERT INTO Dogs (Dogs.Name, Dogs.Breed, Dogs.Colour, Dogs.Gender, Dogs.Age) VALUES (‘Timmy’, ‘Retriever’,’Green’,’M’,2)