SQL Two

Database Relationships, Foreign Keys, Joins, & Sub-Queries

DATA INTENSIFIES

Review Time

*

Select Everything

Review Time

ORDER BY

Choose column which you would like your data ordered by. Has option ASCending or DESCending value.

Review Time

LIMIT

Limit the amount of data you get back from your query.

Review Time

SUM()

Finds the sum of the data. Access it like this: sum(COLUMN_NAME)

Review Time

COUNT()

Finds the count, or total number of items, of the data we query for. Access it like this: count(COLUMN_NAME)

Review Time

AVG()

Finds the average of the data we query for. Access it like this: avg(COLUMN_NAME). Remember average is the total amount divided by the total count

Review Time

MAX()

Finds the max of the data we query for. Access it like this: max(COLUMN_NAME). Returns only one piece of data.

Review Time

MIN()

Finds the minimum of the data we query for. Access it like this: min(COLUMN_NAME).

Returns only one piece of data.

Review Time

LIKE

Underscore to place for single letter

 

WHERE name LIKE '_onathan'

Percent to match any number of letters before or after depending on placement.

 

WHERE name LIKE 'J%'

Review Time

Select Empty Data

SELECT * FROM users

WHERE name IS NULL;

SELECT * FROM users

WHERE name IS NOT NULL

Review Time

Add Rows

INSERT INTO users (

  first_name,

  last_name,

  email

) VALUES (

  'Bryan',

  'Smith',

  'bryan@devmountain.com'

);

Review Time

Delete Rows

DELETE FROM users

WHERE name = 'Bryan';

Be careful with this one. It is always a good practice to do a select first to see what data you will be deleting.

Review Time

Update Rows

UPDATE users

SET name = 'Jase'

WHERE user_id = 4;

Be careful with this one. It is always a good practice to do a select first to see what data you will be updating.

Review Time

DISTINCT

SELECT DISTINCT name FROM users;

Find the unique values. No duplicates.

Altering Tables

In SQL, ALTER TABLE statements can be used to:

Add/Remove Columns

Alter Column Data Types

Rename Columns

Change Table Name

Adding/Removing Columns

To add a column use the following syntax:

ALTER TABLE racers
ADD COLUMN team TEXT;

Table being altered

How table is being altered

Column name and data type

Syntax for removing, or dropping, column is the following:

ALTER TABLE racers
DROP COLUMN team;

Altering Data Types

The syntax for altering data types is the following:

ALTER TABLE racers
ALTER team
SET DATA TYPE VARCHAR(100);

Table being altered

This can also be done in a shorter syntax:

ALTER TABLE racers
ALTER team
TYPE VARCHAR(100);

Column being altered

New data type

Renaming Columns/Table

The syntax for renaming a column is the following:

ALTER TABLE racers
RENAME COLUMN team
TO "group";

The syntax for renaming a table is the following:

ALTER TABLE racers
RENAME TO bike_racers;

Relational Databases and normalization

As your application grows, you will find a need for multiple tables. Each table will have data that another table may find useful.  The process of splitting out our data into multiple tables is called normalization.

Piling all of your data into a single table would be be like stuffing all of your clothes into one dresser drawer. You want a swimsuit? Too bad, here is that shirt you've had since 8th grade

Table Relationships

When working with relational databases, one table's data will often relate to another table's data.

 

There are three main categories of table relationships:

One to One

One to Many

Many to Many

Examples:

1 Husband, 1 Wife

1 Mother, Many Children

Many Books, Many Authors

One to One

One to One relationships mean one row in one table relates to one row in another table.

One to Many

One to Many relationships mean that one row in one table can relate to many rows in another table.

Many to Many

Many to Many means many rows from one table can relate to many rows from another table.

 

Joins and/or junction tables are a common approach to handling many to many relationships.

Foreign Keys

So how do we create these table relationships?

 

Through constraints known as foreign keys.

 

Foreign keys allow us to reference another table's column, typically a column with a primary key.

CREATE TABLE bikes (
  bikeId SERIAL PRIMARY KEY,
  racerId INTEGER REFERENCES racers(racerId),
  type TEXT,
  color TEXT
);

Table being referenced

Column being referenced

Join Statements

Once table relationships have been created, join statements can be used to collect data from tables that relate to each other.

SELECT *
FROM racers
JOIN bikes ON racers.racer_id = bikes.racer_id;

First table to join

How the tables are joining

Second table to join

Subqueries

Subqueries are a queries that rely on an outer query.

 

A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

 

They are created by nesting a query within another query like so:

SELECT *
FROM racers
WHERE racerid IN (
	SELECT racerid
  FROM bikes
  WHERE type = 'fezzari'
  AND age >= 25
)

SQL 2

By matias_perez

Source: awestenskow

SQL 2

Database Relationships, Foreign Keys, Joins, & Sub-Queries

  • 279

More from matias_perez