Embedded Files
What is a database?
What is a database?
A database is a structured way to store data so that it can be retrieved using queries. Databases store data in tables with rows and columns but when referring to a databse table, they have different names.
Databases use a primary key to idnetify each record.
Primary keys are unique identifiers and are typically numerical or alphanumerical, this is because other data is not unique enough.
Consider a database storing customer names, there may be 2 customers called Jane Smith but they are different people so their name cannot be the primary key. it would be better to have something like 001 and 002 as the primary key.
Database datatypes
Database datatypes
Integer
Integer
A whole number
Real/Float/Decimal
Real/Float/Decimal
A decimal number
Date/Time/Datetime
Date/Time/Datetime
A date stamp, timestamp or a date and time stamp
Char
Char
Fixed length string up to 8,000 characters
Varchar
Varchar
Variable string length up to 8,000 characters
Text
Text
Variable string length up to 2GB of data
Boolean
Boolean
True or False
Relational databases
Relational databases
A relational database contains multiple tables. These tables will have links known as relationships and each table is also known as a relation.
Relational databases allow us to design tables that reduce data inconsistency and eliminate data redundancy.
Data inconsistency is when you have more than one entry of similar data in a table. For example, in your phones contacts, you may have the same person with a different number, now you are unsure of which is the correct number for them. This is data inconsistency.
Data redundancy is when you have more than one entry of the same data in a table. For example, in your phones contacts, you may have the same person saved twice with the same number. This is redundant data.
Relational databases use foreign keys to link the information together.
Foreign keys are the primary key of another table. For example:
We can see here that CustomerID is the primary key of the "Customers" table and MovieID is the primary key of the "Movies" table and the "Watched" table uses these primary keys as foreign keys to link customers to the movies they have watched.
SQL
SQL
SQL stands for Structured Query Language and is the language we use to work with data in a database.
Comparative Operations
Comparative Operations
Less than
Less than
Greater than
Greater than
Less than or equal to
Less than or equal to
Greater than or equal to
Greater than or equal to
Equal to
Equal to
Not equal to
Not equal to
Wildcards
Wildcards
SQL has 3 wildcards you may find useful, wildcards are symbols used to save writing out lots of information. They are:
Asterisk
Asterisk
Found on the 8 key and used by pressing shift & 8, the asterisk whildcard returns all data related to your query, you can imagine it as the "everything" wildcard.
Percentage
Percentage
Found on the 5 key and used by pressing shift & 5, the percentage wildcard means 0 or more characters and is used with the LIKE command to filter data.
Underscore
Underscore
Found on the - key and used by pressing shift & -, the underscore wildcard means exactly 1 character and is used with the LIKE command to filter data.
Commands
Commands
For the purpose of this GCSE, you need to know how to:
Get data from the database
Insert new data into the database
Update data that is already in the database
Delete data that is already in the database
SELECT
SELECT
SELECT is the command used to get data from the database. We have to:
SELECT fieldname
FROM tablename
WHERE criteria is met
consider a table of customers like the one above (in the relational database section)
If i want to get all the entries where the person in the database has a "child" account the SQL would be:
SELECT *
FROM Customers
WHERE Account Type = "Child"
this would return:
>> 4 "Macauley" "Culkin" 31/12/2008 "child"
now lets get the fullname of all the adults and order them oldest to youngest:
SELECT Forename, Surname
FROM Customers
WHERE Account Type = "Adult"
ORDER BY Date of Birth ASC
the ASC here stands for Ascending (smallest to largest), to get the largest to smallest, we would use DESC which stands for Descending.
This SQL would return:
>> "Brad" "Pitt"
>> "Brian" "Cranston"
>> "Matty" "Healy"
>> "Taylor" "Swift"
>> "Tom" "Holland"
INSERT
INSERT
To insert data into a database we use:
INSERT INTO tablename
VALUES (value1, value2, value3,...)
For example, If I wanted to add a new child account for a person called Willow Smith who was born on the 1st November 2014. The SQL would be:
INSERT INTO Customers
VALUES (7, "Willow", "Smith", 01/11/2014, "Child")
The table would now look like this:
UPDATE
UPDATE
To update data in the database we need to use:
UPDATE tablename
SET fieldname = new value
WHERE criteria is met
For example, Macauley Culkin entered his Date of Birth wrong and he was actually born in 2007 which makes him 18 in 2025 and therefore he should have an Adult account. The SQL to do this is:
UPDATE Customers
SET Date of Birth = 31/12/2007 AND Account Type = "Adult"
WHERE Forename = "Macauley" AND Surname = "Culkin"
The table would now look like this:
DELETE
DELETE
To delete data from a database, we use:
DELETE FROM tablename
WHERE criteria is met
For example, If I wanted to delete Brad Pitts account, the SQL would be:
DELETE FROM Customers
WHERE Forename = "Brad" AND Surname = "Pitt"
The table would now look like this:
SELECT from two tables
SELECT from two tables
It is possible to get data from 2 database tables at the same time. Let's look at the Customers table and the Movies table from the relational databases section above.
To select from two tables, we musty format it like this:
SELECT table1.field, table2.field
FROM tablename1, tablename2
WHERE criteria is met
Let's say we want to get all the first names of the customers and all the titles of the movies where the movies age rating is not a U. The SQL is:
SELECT Customers.Forename, Movies.Title
FROM Customers, Movies
WHERE Movies.Age Rating != "U"
Filtering data
Filtering data
It is possible to filter data in a database using the LIKE command and the % wildcard.
Look at the Olympics table below:
We can filter this table to show only countries that start with the letter "G" by using this SQL:
SELECT *
FROM Olympics
WHERE CountryName LIKE "G%"
This would return:
When it comes to filtering data, there are 3 formats you can use for the LIKE command:
WHERE fieldname LIKE "A%" - starts with an A
WHERE fieldname LIKE "%A" - ends with an A
WHERE fieldname LIKE "%A%" - Has an A in it
Page updated
Google Sites
Report abuse