1.3.2 d + e – Databases - DONE

Question 1

How do you select data in SQL?

Answer 1

Using
SELECT field FROM table WHERE condition

Question 2

How do you use SQL to find an item that starts with, ends with, or contains a specific character

Answer 2

[WHERE condition] becomes:

WHERE condition LIKE character% when searching for items that start with the character
WHERE condition LIKE %character when searching for items that end with with the character
WHERE condition LIKE %character% when searching for items that contain the character

Question 3

How do you select the items in all fields of a record?

Answer 3

Using SELECT *

Question 4

How do you implement multiple conditions?

Answer 4

Using AND and/or OR

Question 5

How do you add a record into a database using SQL?

Answer 5

By inserting the record’s data using:

INSERT INTO table (field1, field2, field3)
VALUES(value1, value2, value3)

E.g. INSERT INTO world(name, capital, population)
VALUES(“Oz”, “Emerald City”, 1000000)

Question 6

How do you delete a record/records from a table?

Answer 6

Using:
DELETE FROM table
WHERE condition

Question 7

How do you join tables?

Answer 7

Using:
SELECT table.field
FROM table
JOIN table2,
ON table1.field = table2.field
WHERE condition

The specified field is the foreign key.

E.g.
SELECT Student.Surname, Tutor_Group.Tutor room
FROM Student
JOIN Tutor_Group
ON Student.Tutor Group = Tutor_Group.Tutor Group
WHERE Student.Tutor Group = “10B”

Question 8

How do you delete a table?

Answer 8

Using:
DROP TABLE table

Question 9

Describe referential integrity

Answer 9

When relationships between databases are consistent, such that each foreign key links to an existing primary key.

Question 10

Why is referential integrity desirable?

Answer 10

It ensures that information is not removed if it is required elsewhere in a linked database, in order to prevent the occurrence of missing data.

Question 11

How can referential integrity be broken?

Answer 11

If a primary key is deleted or updated or if a foreign key is no longer valid.