In this lecture we go over a review of the last sql lecture and start going into table relationships.
The schema
is the bluepring for how our tableis structured. This is what sets the guidelines for how the data is stored inside of our database.
A schema for our table above would look similar to this
CREATE TABLE racers (
racerId SERIAL PRIMARY KEY,
name TEXT,
age INTEGER,
country TEXT
)
Above, we are determining exactly what datatypes are allowed to be inserted into each coloumn for the row that is being added to our racers table in our database.
When setting up our schema we will determine what data type
is allowed in our columns. There are many types out there, but we will cover the main ones.
Null
- This data type means an empty value.
Integer
- This data type will define a round number.
Decimal
- This data type can have unlimited decimal values.
Float
- This data type can have values up to 15 decimal places.
Serial
- This data type is an incrementing integer, usally used with primary keys to establish uniqueness.
Text
- This data type declares unlimited characters in a string.
Varchar(n)
- This data type will allow you to determine how many characters are allowed.
Boolean
- This data type is either true or false
SQL statements are queries that we make to our database.
A few things to note about SQL Statements:
-
We use a semicolon to delcare the end of a statement.
-
We can not have a trailing comma on our scripts.
-
We need to use single quotes, not double.
-
It's common to use all caps, but it's not needed. It's best to stick with one way and be consistant and not use lowercase and capital characters.
We can use insert statements
to add data into our tables. The syntax for an insert statement looks like the following.
INSERT INTO racers
(name, age, country)
VALUES
('Tayte', 22, 'US')
Above, we are adding myself into the database. We first delcare a insert statement with the INSERT INTO
keyword followed by the name of the table we are inserting data into. Then in parenthesis, we select the columns to add to. We then use the VALUES
keyword followed by another pair of parenthesis to include the data to insert into the columns.
Note: It's import that the data lines up with the column we want to insert that data into
Select
statements are used to query our database for information. The structure for a select
statement looks like the following.
SELECT *
FROM racers
Above, we delcare it's a select statement using the SELECT
keyword. It is followed by the column name that we want to selectv data from. In this case, we used an asterik to say 'all' the data from every column. Then we declare what table to select from using the FROM
keyword.
We can use a where clause
to add a filter to our select statements.
SELECT *
FROM racers
WHERE name = 'Tayte'
>
- Greater than.
SELECT *
FROM racers
WHERE id > 2
<
- Less than.
SELECT *
FROM racers
WHERE id < 2
>=
- Greater than or equal to.
SELECT *
FROM racers
WHERE id >= 2
<=
- Less than or equal to.
SELECT *
FROM racers
WHERE id <= 2
!=
- Does not equate.
SELECT *
FROM racers
WHERE id != 2
AND
- Check for multiple conditions to be true.
SELECT *
FROM racers
WHERE id != 2
AND id < 3
OR
- Check for only one of multiple conditions to be true.
SELECT *
FROM racers
WHERE name = 'Tayte'
OR id = 2
IS NULL
- Checks for a value to be null.
SELECT *
FROM racers
WHERE name IS NULL
IS NOT NULL
- Checks for a value NOT to be null.
SELECT *
FROM racers
WHERE name IS NOT NULL
IN
- This is used as shorthand syntax to act as multiple OR
conditions.
SELECT *
FROM racers
WHERE name IN ('Tayte', 'Samual', 'Kevin');
If we didn't use the IN
key word, our statement would look like this
SELECT *
FROM racers
WHERE name = 'Tayte'
OR name = 'Samual'
OR name = 'Kevin';
BETWEEN
- This is used to retrieve values in between a range.
SELECT *
FROM racers
WHERE id BETWEEN 1 AND 3;
The LIMIT
clause will allow you to return a certain amount of rows from a select statement.
SELECT *
FROM racers
LIMIT 2
This would return the first two rows of data from the racers table.
ORDER BY
- This clause will sort the records in our result. This clause can only be used in select statements.
SELECT *
FROM racers
ORDER BY id
This will organize how result in ascending order by default.
ASC
is the keyword we can use with order by to sort our records in the result in ascending order.
SELECT *
FROM racers
ORDER BY id ASC
DESC
is the keyword we can use with order by to sort our records in the result in descending order.
SELECT *
FROM racers
ORDER BY id DESC
There are "functions" that we can use with PostgreSQL. This will usually handle some sort of mathematical logic for us.
count()
- This returns the count of an expression.
SELECT count(id)
FROM racers
It's important to note that count does not count null values. Pass in the
*
to account for all rows.
min()
- This returns the minimum value of an expression.
SELECT min(age)
FROM racers
Above, we are finding the youngest racer.
max()
- This returns the maximum value of an expression.
SELECT max(age)
FROM racers
Above, we are returning the oldest racer.
sum()
- This returns the summed value of an expression.
SELECT sum(age)
FROM racers
Above, we now have the sum of the ages for all the racers.
avg()
- This function returns the average value of an expression.
SELECT avg(age)
FROM racers
Above, we have found the average age of the racers.
We can update our data inside of a database by using an update statement
.
UPDATE racers
SET name = 'Chris'
WHERE id = 2
Above, we have just updated the name for the user with the id of 2. We first use the UPDATE
keyword followed by the name of the table we are updating. Then we SET
the name to "Chris" where the user id equals 2.
We can remove or destroy data from our databased by using a delete statement
.
DELETE FROM racers
WHERE id = 2
We first use the DELETE FROM
keyword, then declare the table to remove from. We then choose the id of the racer we want to remove.
Make sure to add the where clause or you would remove everything in the table!
We can use alter statements
to alter our tables in our database. We generally use alter statements to modify the schema
for our tables.
ALTER TABLE racers
ADD COLUMN team TEXT
ALTER TABLE racers
ALTER COLUMN team
SET DATA TYPE VARCHAR(100);
We can also makew this shorter by using shorthand syntax
. This way is shorter from the previous example.
ALTER TABLE racers
ALTER team
SET DATA TYPE VARCHAR(100);
This way is the shortest syntax we can use.
ALTER TABLE racers
ALTER team
TYPE VARCHAR(100);
ALTER TABLE racers
RENAME COLUMN team
TO "group";
ALTER TABLE racers
DROP COLUMN group;
ALTER TABLE racers
RENAME TO bike_racers;
We can drop our tables, if we feel the need to remove them. Be careful doing this, because we can not restore tables that have been dropped. This could lead to a lot of problems, so just keep that in mind!
DROP TABLE racers
A relational database is built around a model of having relationships
between different tables to establish a way for the tables to communicate with each other.
There are a three main designs that we can establish for creating a relationship
between tables.
This deisgn is where one entity can only talk to one other entity. I like to think about this like a social security number. Only one person will be associated with one SSN, it will never represent more than one person.
This design is where one entity can have multple relationships with other entities. I like to think about this like comments on a website. A comment is only owned by one person, this creates a one to many
relationship. One user can have many comments.
This design is where multiple entities can have a relationship with multiple entities. I like to think about this as books and authors. A book can have many authors and an author can have many books.
Now that we have talked about some of the relational design patterns, you are probably wondering how we actually set this relationship up? This is where foreign keys
come into play.
A foreign key
will set a constraint to the data that is inserted into the row. The only way it data can be inserted is if a row has a column value that matches with another specific value from another table.
A foreign key
will typically reference a primary key
from another table.
CREATE TABLE bikes (
bikeId SERIAL PRIMARY KEY,
racerId INTEGER REFERENCES racers(racerId),
type TEXT,
color TEXT
)
Above is thew syntax we would use to setup a foreign key in our schema for a table. We are saying that the racer_id
column needs to have a matching value with a row's id from the racers table. This has now created that relationship
between the two tables.
Joins
are one of the essential elements that help make up a relational database.
They will allow us to retrieve data from multiple tables at one time.
SELECT *
FROM racers
JOIN bikes ON racers.racerId = bikes.bikeId;
Above is the basic syntax for creating a join
statement. We first will use a select
statement to choose what columns we want to select data for from both of the tables, then we select the first table and the second table. We use the ON
keyword to connect both of the tables on their primary and foreign keys.
Aliasing can make our queries and statements a little more organized and easier to understand.
AS
is the keyword we will use to alias how we refer to something. The alias name will follow the keyword.
SELECT name AS firstName
FROM racers
Above, we alias the name
column as firstName
. This can become handy when we have multiple tables with the same name for some columns.
A subquery
is a query that is nested inside of another larger query.
a subquery can be nested inside a SELECT
, INSERT
, UPDATE
, DELETE
, or SET
statement or inside another subquery.
A subqeury is usually added within the where
clause of a statement.
It's important to keep in mind that the most inner query will be executed before the outer queries.
SELECT *
FROM racers
WHERE racerid IN (
SELECT racerid
FROM bikes
WHERE type = 'fezzari'
AND age >= 25
)
DISTINCT
is the keyword we can use when we don't want any duplications of datasets.
SELECT DISTINCT country FROM racers;