Databases and SQL

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

Integer

A whole number

Real/Float/Decimal

A decimal number

Date/Time/Datetime

A date stamp, timestamp or a date and time stamp

Char

Fixed length string up to 8,000 characters

Varchar

Variable string length up to 8,000 characters

Text

Variable string length up to 2GB of data

Boolean

True or False

SQL

SQL stands for Structured Query Language and is the language we use to work with data in a database.

Comparative Operations

Less than

Greater than

Less than or equal to

Greater than or equal to

Equal to

Not equal to

Wildcards

SQL has 3 wildcards you may find useful, wildcards are symbols used to save writing out lots of information. They are:

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

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

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

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 is the command used to get data from the database. We have to:

SELECT fieldname

FROM tablename

WHERE criteria is met

Look at the table of customers:

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

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:

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:

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: