3 of 16

Databases

Databases holds data in tables. The table is made up of many unique components that separate it from other data types.

Hotels
ID	hotel_name	rooms	rating	room_price	pets
1	Water Lodge	50	3.5	42	Y
2	Fire Inn	64	4.4	46	N
3	Windy Hotel	215	2.2	66	N
4	River Vale	65	3.7	80	Y
5	City Lodge	180	1.9	55	N

4 of 16

Table name

Each table has its own unique name (entity). When searching for data, the table name allow the program to know what table you want to search in.

Hotels
ID	hotel_name	rooms	rating	room_price	pets
1	Water Lodge	50	3.5	42	Y
2	Fire Inn	64	4.4	46	N
3	Windy Hotel	215	2.2	66	N
4	River Vale	65	3.7	80	Y
5	City Lodge	180	1.9	55	N

5 of 16

Columns

Each column (or field) has a name and a datatype.

When searching data we can choose which columns we wish to retrieve (we do not need to always retrieve them all)

Hotels
ID	hotel_name	rooms	rating	room_price	pets
1	Water Lodge	50	3.5	42	Y
2	Fire Inn	64	4.4	46	N
3	Windy Hotel	215	2.2	66	N
4	River Vale	65	3.7	80	Y
5	City Lodge	180	1.9	55	N

6 of 16

Rows

A row of data (or record) is a single line of data that has a single value for each column.

Each row of data starts with a unique ID.

Hotels
ID	hotel_name	rooms	rating	room_price	pets
1	Water Lodge	50	3.5	42	Y
2	Fire Inn	64	4.4	46	N
3	Windy Hotel	215	2.2	66	N
4	River Vale	65	3.7	80	Y
5	City Lodge	180	1.9	55	N

7 of 16

An ID is a unique identifier for the row. Every table must have one.

This ID is not just useful for searching but links tables together to avoid repetition

Hotels
ID	hotel_name	rooms	rating	room_price	pets
1	Water Lodge	50	3.5	42	Y
2	Fire Inn	64	4.4	46	N
3	Windy Hotel	215	2.2	66	N
4	River Vale	65	3.7	80	Y
5	City Lodge	180	1.9	55	N

Order
ID	Customer_ID	Hotel_ID	Date
1	4	2	12/04/21

8 of 16

SQL

SQL stands for structured Query Language and acts as the main language for databases.

It allow the creation and deletion of databases and tables. As well allow for creation, reading, updating and deleting of records.

Keywords should be written in all caps but is not vital.

9 of 16

Selecting all data

SELECT * FROM hotels

Original data
Data returned

Function =

Read data

*(Get all columns)

Table name

10 of 16

Selecting certain columns

SELECT hotel_name, rooms, pets FROM hotels

Original data
Data returned

Column names (separated by a comma)

11 of 16

WHERE (filter results)

SELECT * FROM hotels

WHERE rating > 3

Original data
Data returned

Only returns the rows that meet the condition

12 of 16

AND and OR

SELECT * FROM hotels

WHERE rating > 3

AND pets = “Y”

Original data
Data returned

OR can be used here as well. OR will increase results returned, while AND will lower it.

13 of 16

SELECT * FROM hotels

WHERE hotel_name LIKE “%Lodge”

Original data
Data returned

Will look for hotels ending in LODGE.
% at the end will be hotels starting with lodge.
% before and after is hotel containing the word lodge.

14 of 16

ORDER

SELECT hotel_name FROM hotels

ORDER hotel_name ASC

Original data
Data returned

DESC can be used for descending order.

You can use order and where on a field even you are not displaying it.

15 of 16

ORDER

SELECT * FROM hotels

WHERE price < 70

ORDER price DESC

Original data
Data returned

ORDER can be used with WHERE, this happens after a WHERE clause.

16 of 16

Features	Databases	Text/CSV files	2D Arrays
Uses	Storing large organised data	General storage of data	Storing data during a program
Advantages
Quick to create	Very Slow	Quick	Very Quick
Can be quickly added to	Slow unless automated	Quick	Very Quick
Duplicate data is easy to avoid	Yes	No	No
Suited for large data sets	Yes	Yes	No
Suited for small data sets	No	Yes	Yes
Easy to retrieve precise data	Very Easy	Very Hard	Hard
Disadvantages
Requires a server	Nearly always	No	No
Requires a second programming language	Yes	No language required	No