206 lines
6.0 KiB
SQL
206 lines
6.0 KiB
SQL
---------------------------------------------------------------------------
|
|
-- Practical SQL: A Beginner's Guide to Storytelling with Data, 2nd Edition
|
|
-- by Anthony DeBarros
|
|
|
|
-- Chapter 8 Code Examples
|
|
----------------------------------------------------------------------------
|
|
|
|
-- Listing 8-1: Declaring a single-column natural key as primary key
|
|
|
|
-- As a column constraint
|
|
CREATE TABLE natural_key_example (
|
|
license_id text CONSTRAINT license_key PRIMARY KEY,
|
|
first_name text,
|
|
last_name text
|
|
);
|
|
|
|
-- Drop the table before trying again
|
|
DROP TABLE natural_key_example;
|
|
|
|
-- As a table constraint
|
|
CREATE TABLE natural_key_example (
|
|
license_id text,
|
|
first_name text,
|
|
last_name text,
|
|
CONSTRAINT license_key PRIMARY KEY (license_id)
|
|
);
|
|
|
|
-- Listing 8-2: Example of a primary key violation
|
|
-- Note: You will need to create the natural_key_example table
|
|
-- using either of the two statements in Listing 8-1.
|
|
|
|
INSERT INTO natural_key_example (license_id, first_name, last_name)
|
|
VALUES ('T229901', 'Gem', 'Godfrey');
|
|
|
|
INSERT INTO natural_key_example (license_id, first_name, last_name)
|
|
VALUES ('T229901', 'John', 'Mitchell');
|
|
|
|
-- Listing 8-3: Declaring a composite primary key as a natural key
|
|
|
|
CREATE TABLE natural_key_composite_example (
|
|
student_id text,
|
|
school_day date,
|
|
present boolean,
|
|
CONSTRAINT student_key PRIMARY KEY (student_id, school_day)
|
|
);
|
|
|
|
-- Listing 8-4: Example of a composite primary key violation
|
|
INSERT INTO natural_key_composite_example (student_id, school_day, present)
|
|
VALUES(775, '2022-01-22', 'Y');
|
|
|
|
INSERT INTO natural_key_composite_example (student_id, school_day, present)
|
|
VALUES(775, '2022-01-23', 'Y');
|
|
|
|
INSERT INTO natural_key_composite_example (student_id, school_day, present)
|
|
VALUES(775, '2022-01-23', 'N');
|
|
|
|
-- Listing 8-5: Declaring a bigint column as a surrogate key using IDENTITY
|
|
|
|
CREATE TABLE surrogate_key_example (
|
|
order_number bigint GENERATED ALWAYS AS IDENTITY,
|
|
product_name text,
|
|
order_time timestamp with time zone,
|
|
CONSTRAINT order_number_key PRIMARY KEY (order_number)
|
|
);
|
|
|
|
INSERT INTO surrogate_key_example (product_name, order_time)
|
|
VALUES ('Beachball Polish', '2020-03-15 09:21-07'),
|
|
('Wrinkle De-Atomizer', '2017-05-22 14:00-07'),
|
|
('Flux Capacitor', '1985-10-26 01:18:00-07');
|
|
|
|
SELECT * FROM surrogate_key_example;
|
|
|
|
-- Listing 8-6: Restarting an IDENTITY sequence
|
|
|
|
INSERT INTO surrogate_key_example
|
|
OVERRIDING SYSTEM VALUE
|
|
VALUES (4, 'Chicken Coop', '2021-09-03 10:33-07');
|
|
|
|
ALTER TABLE surrogate_key_example ALTER COLUMN order_number RESTART WITH 5;
|
|
|
|
INSERT INTO surrogate_key_example (product_name, order_time)
|
|
VALUES ('Aloe Plant', '2020-03-15 10:09-07');
|
|
|
|
SELECT * FROM surrogate_key_example;
|
|
|
|
-- Listing 8-7: A foreign key example
|
|
|
|
CREATE TABLE licenses (
|
|
license_id text,
|
|
first_name text,
|
|
last_name text,
|
|
CONSTRAINT licenses_key PRIMARY KEY (license_id)
|
|
);
|
|
|
|
CREATE TABLE registrations (
|
|
registration_id text,
|
|
registration_date timestamp with time zone,
|
|
license_id text REFERENCES licenses (license_id),
|
|
CONSTRAINT registration_key PRIMARY KEY (registration_id, license_id)
|
|
);
|
|
|
|
INSERT INTO licenses (license_id, first_name, last_name)
|
|
VALUES ('T229901', 'Steve', 'Rothery');
|
|
|
|
INSERT INTO registrations (registration_id, registration_date, license_id)
|
|
VALUES ('A203391', '2022-03-17', 'T229901');
|
|
|
|
INSERT INTO registrations (registration_id, registration_date, license_id)
|
|
VALUES ('A75772', '2022-03-17', 'T000001');
|
|
|
|
-- Listing 8-8: Examples of CHECK constraints
|
|
|
|
CREATE TABLE check_constraint_example (
|
|
user_id bigint GENERATED ALWAYS AS IDENTITY,
|
|
user_role text,
|
|
salary numeric(10,2),
|
|
CONSTRAINT user_id_key PRIMARY KEY (user_id),
|
|
CONSTRAINT check_role_in_list CHECK (user_role IN('Admin', 'Staff')),
|
|
CONSTRAINT check_salary_not_below_zero CHECK (salary >= 0)
|
|
);
|
|
|
|
-- Both of these will fail:
|
|
INSERT INTO check_constraint_example (user_role)
|
|
VALUES ('admin');
|
|
|
|
INSERT INTO check_constraint_example (salary)
|
|
VALUES (-10000);
|
|
|
|
-- Listing 8-9: A UNIQUE constraint example
|
|
|
|
CREATE TABLE unique_constraint_example (
|
|
contact_id bigint GENERATED ALWAYS AS IDENTITY,
|
|
first_name text,
|
|
last_name text,
|
|
email text,
|
|
CONSTRAINT contact_id_key PRIMARY KEY (contact_id),
|
|
CONSTRAINT email_unique UNIQUE (email)
|
|
);
|
|
|
|
INSERT INTO unique_constraint_example (first_name, last_name, email)
|
|
VALUES ('Samantha', 'Lee', 'slee@example.org');
|
|
|
|
INSERT INTO unique_constraint_example (first_name, last_name, email)
|
|
VALUES ('Betty', 'Diaz', 'bdiaz@example.org');
|
|
|
|
INSERT INTO unique_constraint_example (first_name, last_name, email)
|
|
VALUES ('Sasha', 'Lee', 'slee@example.org');
|
|
|
|
-- Listing 8-10: A NOT NULL constraint example
|
|
|
|
CREATE TABLE not_null_example (
|
|
student_id bigint GENERATED ALWAYS AS IDENTITY,
|
|
first_name text NOT NULL,
|
|
last_name text NOT NULL,
|
|
CONSTRAINT student_id_key PRIMARY KEY (student_id)
|
|
);
|
|
|
|
-- This will fail:
|
|
INSERT INTO not_null_example (first_name, last_name)
|
|
VALUES ('Sting', NULL);
|
|
|
|
-- Listing 8-11: Dropping and adding a primary key and a NOT NULL constraint
|
|
|
|
-- Drop
|
|
ALTER TABLE not_null_example DROP CONSTRAINT student_id_key;
|
|
|
|
-- Add
|
|
ALTER TABLE not_null_example ADD CONSTRAINT student_id_key PRIMARY KEY (student_id);
|
|
|
|
-- Drop
|
|
ALTER TABLE not_null_example ALTER COLUMN first_name DROP NOT NULL;
|
|
|
|
-- Add
|
|
ALTER TABLE not_null_example ALTER COLUMN first_name SET NOT NULL;
|
|
|
|
-- Listing 8-12: Importing New York City address data
|
|
|
|
CREATE TABLE new_york_addresses (
|
|
longitude numeric(9,6),
|
|
latitude numeric(9,6),
|
|
street_number text,
|
|
street text,
|
|
unit text,
|
|
postcode text,
|
|
id integer CONSTRAINT new_york_key PRIMARY KEY
|
|
);
|
|
|
|
COPY new_york_addresses
|
|
FROM 'C:\YourDirectory\city_of_new_york.csv'
|
|
WITH (FORMAT CSV, HEADER);
|
|
|
|
-- Listing 8-13: Benchmark queries for index performance
|
|
|
|
EXPLAIN ANALYZE SELECT * FROM new_york_addresses
|
|
WHERE street = 'BROADWAY';
|
|
|
|
EXPLAIN ANALYZE SELECT * FROM new_york_addresses
|
|
WHERE street = '52 STREET';
|
|
|
|
EXPLAIN ANALYZE SELECT * FROM new_york_addresses
|
|
WHERE street = 'ZWICKY AVENUE';
|
|
|
|
-- Listing 8-14: Creating a B-tree index on the new_york_addresses table
|
|
|
|
CREATE INDEX street_idx ON new_york_addresses (street);
|