SQL review:
2 months of class in 60 minutes
3 fundamental aspects of databases:
Oddly enough, usually we organize the data first: For instance, you’ll typically build database diagrams before you build select statements, and you’ll usually built select statements before collecting new data.
Organizing Data
1st step in organizing data: Types
Types tell your database - and eventually your users - what type of data you’re dealing with.
Types categorize your data and set what operations can be done with them.
Numeric types
Approximate
Date / Time Types
Other Types
Tables
Organizing data in a relational database is done through tables.
Fortunately, the SQL to do this is rarely used: use the table designer instead.
If you have a unique field - like VINs on cars - you can use that as the primary key, but often you’ll want to make a new id field to ensure uniqueness.
CREATE TABLE tab_car
(
VIN char(17) PRIMARY KEY,
make varchar(50),
model varchar(50),
color varchar(25),
year int
)
Foreign Keys
Tables don’t stand alone.
Almost every table you create will link to other tables in some way.
Whether you explicitly create a Foreign Key constraint or not, the primary_vehicle field is acting as a foreign key to the tab_car table.
CREATE TABLE tab_drivers
(
id INT PRIMARY KEY,
name varchar(100),
insured_since DATE,
insured_until DATE,
primary_vehicle char(17)--Foreign Key
)
--Second example, explicit key
CREATE TABLE tab_drivers2
CONSTRAINT FK_tab_drivers2_car FOREIGN KEY (primary_vehicle) REFERENCES tab_car (VIN)
(
id INT PRIMARY KEY,
name varchar(100),
insured_since DATE,
insured_until DATE,
primary_vehicle char(17)
)
Many-to-Many relationships
If you have a many - to many relationship - for instance, each driver might drive many cars, while each car might have many drivers - you’ll need to build a third table (usually called a bridge table or junction table) to track that relationship.
CREATE TABLE tab_listed_vehicles
(
vehicle_id char(17)--Foreign Key to tab_cars
driver_id int--Foreign Key to tab_drivers
)
Pulling Data
Select Statements
SELECT statements are, most likely, the SQL you will be writing most.
It doesn’t matter what’s in the database if no one can see it.
Filtering is done through Conditional statements: Data is only pulled through the select if the conditions in the WHERE clause are met.
Select statements can have as many conditions as necessary, using AND, and OR.
SELECT
*
FROM
tab_car
WHERE
Color = ‘BLUE’
--Grab all blueish cars
SELECT
*
FROM
tab_car
WHERE
color Like ‘%BLUE%’ OR color Like ‘%Indigo%’
--Grab all Toyota Corollas
SELECT
*
FROM
tab_car
WHERE
Make = ‘Toyota’ AND model = ‘Corolla’
Case Statements
Case Statements can be used in many different ways, but they are a powerful tool for organizing data.
What does the statement on the right do?
SELECT
make,
model,
CASE
WHEN color LIKE ‘%gold%’ THEN ‘Yellow’
WHEN color LIKE ‘%silver%’ THEN ‘White’
WHEN color LIKE ‘%copper%’ THEN ‘Brown’
ELSE color
END AS color,
CASE
WHEN color LIKE ‘%gold%’ OR
color LIKE ‘%silver%’ OR
color LIKE ‘%copper%’
THEN 1
ELSE 0
END AS metallic
FROM
tab_car
Joins
It’s rare that all the information you need will be in a single table.
95% of the joins you do will be inner joins, like this example which gathers owners of Hyundai Elantras that have a defective part that needs a recall.
SELECT
name
FROM
tab_car
JOIN tab_listed_vehicles
ON tab_car.VIN = tab_listed_vehicles.vehicle_id
JOIN tab_drivers
ON tab_listed_vehicles.driver_id = tab_drivers.id
WHERE
make = ‘ELANTRA’
AND model = ‘HYUNDAI’
AND year BETWEEN 2014 AND 2016
Outer Joins
I recommend only using ‘LEFT’ outer joins, but that’s a personal preference.
This SELECT statement will grab all cars that are not anyone’s primary vehicle.
NULLs will show up whenever data is missing: Use COALESCE statements to handle NULL values.
SELECT
*
FROM
tab_cars LEFT JOIN
tab_drivers ON tab_cars.VIN=tab_drivers.primary_vehicle
WHERE
tab_drivers.id IS NULL
Note:
NULL = NULL returns false
NULL <> NULL returns false
1+NULL is NULL
‘A string’+NULL is NULL
COALESCE(NULL, ‘0’) = ‘0’
Adding & Modifying Data
Insert Statements
Insert statements are the standard way of putting new data in tables.
Note that any values that you don’t enter will have a NULL value in the field: This can be good, but you have to be sure you’re doing it on purpose!
INSERT FROM
Lets you insert data from other tables. Here it’s from a single table, but you can use any SELECT you want.
INSERT INTO
tab_cars
(VIN, make, model,year)
VALUES
(
‘1234567890abcdefg’,
‘Toyota’,
‘Corolla’,
1996
)
INSERT INTO
tab_cars
(VIN, make, model)--year will be left NULL
SELECT
(VIN, make, model)
FROM
Other_cars_table
WHERE
Condition = true
Delete Statements
The opposite of insert statements, delete statements remove rows from your table.
If you are writing DELETE statements, remember: THERE IS NO UNDO!
As a precaution, I always write a SELECT statement first, so I know what I’m deleting.
SELECT * FROM
tab_recent_orders
WHERE
Order_date < GETDATE()-7--lists week old orders
DELETE FROM
tab_recent_orders
WHERE
Order_date < GETDATE()-7--removes week old orders
SELECT * FROM --look up the personal information of our users
tab_users_personal_data
WHERE
username IN (SELECT username FROM tab_users_suing_us)
DELETE FROM --then comply with EU regulations
tab_users_personal_data
WHERE
username IN (SELECT username FROM tab_users_suing_us)
Update Statements
Update statements are how you change data, without deleting it.
UPDATE FROM
Lets you update based on data from other tables: you can use those other tables in the conditions or the values.
MERGE is a cool new way of doing that, but you can google that on your own. : )
UPDATE
tab_SQL_class
SET
graduated=1,
awesomeness=awesomeness+5
WHERE
dropped=0 AND project = ‘COMPLETE’
UPDATE
tab_friends
SET
frenemy=true
FROM
tab_friends JOIN tab_enemies ON
tab_friends.person_id = tab_enemies.person_id
SQL Programming
VARIABLES
Variables allow you to store data and use it later.
You can assign them values with a select statement.
DECLARE
@NUM_IDIOTS int,
@MESSAGE varchar(255)
SELECT
@NUM_IDIOTS = COUNT(*)
FROM
tab_users
WHERE
password = ‘password’ OR password = ‘12345’
SELECT
@MESSAGE =
‘We have ’ + @NUM_IDIOTS + ‘ in our database.’
SELECT
@MESSAGE as message --display our message
If Statements
If Statements allows your code to selectively execute statements.
Use ELSE, ELSE IF, or BEGIN/END statements to give yourself even more options.
--this will never be executed
IF 1 < 0
SELECT ‘This will never happen’
--this checks to make sure @AGE is a reasonable number
IF @AGE < 21
SELECT ‘I’’m Sorry, you’ll need to wait another ‘ +(21-@AGE) +’ years to purchase this adult beverage’
ELSE
SELECT ‘Here’’s your La Croix sparkling water’
IF @AGE > 90 BEGIN
SELECT ‘I think you’’re not actually that old.’
INSERT INTO tab_lies(lie, answer) VALUES (‘Age’,@AGE)
END
ELSE IF @AGE < 7 BEGIN
SELECT ‘I think you’’re not actually that young.’
INSERT INTO tab_lies(lie, answer) VALUES (‘Age’,@AGE)
END
ELSE BEGIN
--BEGIN/END not needed, since it’s just one statement
SELECT ‘I guess that’’s reasonable.’
END
LOOPS
Sometimes you’ll want to do the same thing over and over again.
For instance, this loop will create a table containing all numbers up to 100.
DECLARE @COUNTER int = 0
WHILE @COUNTER <= 100
BEGIN
INSERT INTO
tab_numbers
(number)
VALUES
(
@COUNTER
)
SET
@COUNTER = @COUNTER+1
END
SELECT * FROM
tab_numbers
See next lesson for Stored Procedures
I’ll be covering stored procedures in more detail soon!
I want to spend a little more time on them, since they are so important.
Practicing with automated SQL testing