3.7 Relational Databases and SQL

Question 1

What is a database?

Answer 1

A database is a persistent collection of organised storage that can be retrieved through queries

Question 2

What is SQL?

Answer 2

Structured Query Language

Question 3

What is data redundancy?

Answer 3

Duplicate pieces of data

Question 4

What is data inconsistency?

Answer 4

Storing repeated data in the same table can lead to inconsistencies in the data

Question 5

Range check

Answer 5

A number or date is within a sensible/allowed range i.e. Month between 1 and 12

Question 6

Type check

Answer 6

Data is the right type

Question 7

Length check

Answer 7

Text entered is not too long or too short - for example a password is between 8 and 15 characters

Question 8

Presence check

Answer 8

Checks that the data has been entered, i.e. The field has been left blank

Question 9

Format check

Answer 9

Checks the format is correct, for example, a postcode or email is correct

Question 10

List check

Answer 10

Making sure the user enters the right thing from a list. They can only select an option from a drop-down menu.

Question 11

What wildcard is used to represent everything?

Answer 11

*

Question 12

SQL SELECT

Answer 12

Select *
FROM table1

Question 13

SQL WHERE

Answer 13

Select *
FROM table1
WHERE Forename = ‘Bob’

• If the value is a string (or varchar, text etc.) then it requires quotation marks.

Question 14

SQL SELECT SORTING

Answer 14

Select Forename, Surname
FROM table1
ORDER BY Surname ASC

• ASC = Ascending
• DESC = Descending

Question 15

SQL INSERT

Answer 15

INSERT INTO table1 (userId, Forename, Surname, Age, Height, MobileNumber)
VALUES (1, ‘Bob’, ‘Jones’, 47, 1.87, ‘07834 577780’)

• If the value is a string (or varchar, text etc.) then it requires quotation marks.

Question 16

SQL UPDATE

Answer 16

UPDATE table1
Surname = ‘Rich’
WHERE userId = 1

It is the safest option to use the primary key as if you use another field, then you might change someone else’s details as well.

Question 17

SQL DELETE

Answer 17

DELETE FROM table1
WHERE userId = 1

Question 18

SQL Joining two tables

Answer 18

SELECT table1.Forename, table1.Surname, table2.Address
FROM table1
INNER JOIN ON table1.userId = table2.userId
WHERE userId = 1

Question 19

Primary key

Answer 19

A primary key is a field in a database table and acts as a unique identifier for each record.

Question 20

Foreign key

Answer 20

It is the reference to the primary key of another table.
It is used to create a link/relationship between two tables.

Question 21

What is an integer?

Answer 21

A whole number

Question 22

What is a float?

Answer 22

A number with a fractional component (a decimal)

Question 23

What is datetime

Answer 23

Date and Time

Question 24

What is a char?
In databases

Answer 24

A string of fixed length (up to 8,000 characters long)

Question 25

What is a varchar?

Answer 25

A string of variable length (up to 8,000 characters long)

Question 26

What is text?

Answer 26

A string of variable length (up to 2GB of data)