本学习笔记中所使用的数据库DVDrental（本数据库可上网搜索或私信me），该数据库创建程序在本文末尾，数据表包括addrress、city、customer、film、film_actor、film_category、inventory、payment、rental、staff和store，如下图：
SELECT p.*,c.*,r.*
FROM payment p
LEFT JOIN customer c ON p.customer_id = c.customer_id
LEFT JOIN rental r ON p.rental_id = r.rental_id
select generate_series(1, 10);
-- column with 1 - 10
SELECT val.val, set.set FROM generate_series(1,10)val
join generate_series(5,15)set ON val.val = set.set
SELECT val.val, set.set FROM generate_series(1,10)val
LEFT JOIN generate_series(5,15)set on val.val = set.set
--放在左边的列
SELECT val.val, set.set FROM generate_series(1,10)val
Right join generate_series(5,15)set ON val.val = set.set
--放在右边的列
-- column with 5 - 15
/* LEFT JOIN 左连接，左边的表和右边的表建立连接，返回左边的表和右边的表中匹配的内容 */
--LEFT JOIN generate_series(5,16)set on val.val = set.set
/* RIGHT JOIN 右连接，右边的表和左边的表建立连接，返回右边的表和左边的表中匹配的内容 */
--Right join generate_series(5,15)set ON val.val = set.set
-- 例1，
SELECT p.customer_id, c.first_name,
SUM(p.amount)::money, COUNT(*),
SUM(p.amount)/COUNT(*) as AVG_RENTAL_VALUE
FROM payment p LEFT JOIN customer c ON p.customer_id = c.customer_id
WHERE p.customer_id > 300 AND c.first_name like '%b%'
GROUP BY 1,2
HAVING COUNT(*) > 20
--having is when you filter on aggregates, is a where clause for aggregates
ORDER BY 4 DESC
结果如下图：
-- 例2
SELECT p.*, payment_date::date,
extract(year from p.payment_date),
extract(month from p.payment_date),
extract(day from p.payment_date)
FROM payment p
where extract(month from p.payment_date) in (1,2)
-- 例3
select to_char(p.payment_date,'yyyy-mm'),SUM(p.amount),MAX(p.amount),MIN(p.amount)
from payment p group by 1
-- -- 按照月份合计，by 1 指查询结果的第一列
--例4
select SUM(p.amount),MAX(p.amount),MIN(p.amount), to_char(p.payment_date,'yyyy-mm')
from payment p
group by 4 order by 1
select extract(dow from p.payment_date) from payment p;
-- 星期的第几天，sunday is zero
--- 查询每周各天的出租收入各为多少？
select to_char(p.payment_date,'dy') as dow,sum(p.amount)
from payment p
group by 1
order by 2 desc;
---- 查询星期四和星期5这两天的出租收入各为多少？
select * from (
select t.*,
CASE
WHEN t.dow = 0 THEN 'Sunday'
WHEN t.dow = 1 THEN 'Monday'
WHEN t.dow = 2 THEN 'Tuesday'
WHEN t.dow = 3 THEN 'Wednesday'
WHEN t.dow = 4 THEN 'Thurs'
WHEN t.dow = 5 THEN 'Fri'
WHEN t.dow = 6 THEN 'Saturday'
END as day_of_week
from(
select extract(dow from p.payment_date) as dow,sum(p.amount)::money
--::money 表示货币数据, as dow 字段标识为dow，替代payment_date
from payment p
group by 1
order by 1
)t
)t2
where t2.day_of_week = 'Fri' or t2.day_of_week = 'Thurs';
select p.*, to_char(p.payment_date,'dy') as dow -- dy 表示星期几的英文缩写
from payment p;
-- 星期几的收入最多？
select to_char(p.payment_date,'dy') as dow,sum(p.amount)
from payment p
group by 1
order by 2 desc
limit 3;
WITH base_table AS (
select p.rental_id,p.amount,r.inventory_id,f.title,f.rating,c.name as genre
from payment p
left join rental r on p.rental_id = r.rental_id
left join inventory i on i.inventory_id = r.inventory_id
left join film f on f.film_id = i.film_id
left join film_category fc on fc.film_id = i.film_id
left join category c on c.category_id = fc.category_id
)
SELECT * from (
select t.*,
ROW_number() over (partition by t.rating order by t.sales desc)
from(
select bt.rating, bt.genre, SUM(bt.amount) as sales
from base_table bt
group by 1,2
order by 3 desc
)t
)radical
where radical.row_number = 1;
-- 生成日期序列
select gs::date from generate_series(
'2007-01-01',
'2007-12-31',
interval '1 day'
) gs;
-- 查询出租收入为空的记录
With base_table as (
--上句为后套入
select gs::date,SUM(p.amount) as sales from generate_series(
'2007-01-01',
'2007-12-31',
interval '1 day'
) gs LEFT JOIN payment p on gs=p.payment_date::date
group by 1
order by 1
)
--下句为后套入
select extract(week from bt.gs),SUM(bt.sales) --按照星期合计查询
from base_table bt
group by 1
order by 1
和第7课的查询目的相同，
With base_table as (
--上句为后套入
select gs::date,SUM(p.amount) as sales from generate_series(
'2007-01-01',
'2007-12-31',
interval '1 day'
) gs LEFT JOIN payment p on gs=p.payment_date::date --去掉Left，则查询sum数据不为0的记录
group by 1
order by 1
)
--下句为后套入
select extract(week from bt.gs),SUM(bt.sales) --按照星期合计查询
from base_table bt
group by 1
order by 1
把DVD分为两类：美国和其他国家，再附加其他查询条件
例1：
select
case
when t.country = 'United States' THEN 'USA'
else 'Rest of world' END as geo_type,
count(*) from (
select c.customer_id,c.first_name,c.last_name,c.email,c.address_id,
split_part(c.email,'@',2),c.last_name ||', '|| c.first_name as last_first,ci.city,
ci.country_id, co.country,
-- outer table, c.customer_id, inner table p.customer_id
(
select min(p.payment_date)
from payment p where p.customer_id = c.customer_id
)::date as cust_min_order_date,
(
select max(p.payment_date)
from payment p where p.customer_id = c.customer_id
)::date as cust_max_order_date,
current_date - (
select max(p.payment_date)
from payment p where p.customer_id = c.customer_id
)::date as days_since_recent_order
From customer c
left join address a on a.address_id = c.address_id
left join city ci on ci.city_id = a.city_id
left join country co on ci.country_id = co.country_id
)t
group by 1 order by 2 desc;
例2：
select
case
when t.country = 'United States' THEN 'USA'
else 'Rest of world' END as geo_type,
count(*) from (
select c.customer_id,c.first_name,c.last_name,c.email,c.address_id,
split_part(c.email,'@',2),c.last_name ||', '|| c.first_name as last_first,ci.city,
ci.country_id, co.country,
-- outer table, c.customer_id, inner table p.customer_id
(
select min(p.payment_date)
from payment p where p.customer_id = c.customer_id
)::date as cust_min_order_date,
(
select max(p.payment_date)
from payment p where p.customer_id = c.customer_id
)::date as cust_max_order_date,
current_date - (
select max(p.payment_date)
from payment p where p.customer_id = c.customer_id
)::date as days_since_recent_order
From customer c
left join address a on a.address_id = c.address_id
left join city ci on ci.city_id = a.city_id
left join country co on ci.country_id = co.country_id
--Inner Join on Outside of the DB data
where c.email IN (
'sonia.gregory@sakilacustomer',
'miriam.mckinney@sakilacustomer',
'velma.lucas@sakilacustomer',
'becky.miles@sakilacustomer',
'bobbie.craig@sakilacustomer',
'violet.rodriquez@sakilacustomer',
'kristina.chambers@sakilacustomer',
'toni.holt@sakilacustomer',
'misty.lambert@sakilacustomer',
'mae.fletcher@sakilacustomer',
'shelly.watts@sakilacustomer',
'daisy.bates@sakilacustomer',
'ramona.hale@sakilacustomer'
)
)t
group by 1 order by 2 desc
Select f.description,
--regexp_replace(f.description,'of|a|in','xxx','g')
--regexp_replace(f.description,'of|\ma\M|in','xxx','g')
--regexp_replace(f.description,'\mof\M|\ma\M|\min\M','xxx','g')
--regexp_replace(f.description,'\mof\M|\ma\M|\min\M','','g')
--regexp_replace(f.description,'\mof\M|\ma\M|\min\M|\s\s ','','g')
lower(
regexp_replace(
regexp_replace(f.description,'\mof\M|\ma\M|\min\M','','g'),
'\s ',' ','g')),
--left(lower(
-- regexp_replace(
-- regexp_replace(f.description,'\mof\M|\ma\M|\min\M','','g'),
-- '\s ',' ','g')),3)
trim(lower(
regexp_replace(
regexp_replace(f.description,'\mof\M|\ma\M|\min\M','','g'),
'\s ',' ','g')))
from film f;
select * from(
select date::date, sum(p.amount), count(*) from generate_series(
'2007-01-01'::date,
'2007-12-31'::date,
'1 day'::interval
)date left join payment p on p.payment_date::date = date.date
--测试去掉left 或使用INNER 的查询结果
group by 1
order by 1
-- nulls last -- 查询sum为null的记录
)t
where t.count > 1
select f.film_id,count(*)
from film f
left join film_actor fa on f.film_id = fa.film_id
group by 1
order by 1;
/**/
select f.film_id,fa.*
from film f
left join film_actor fa on f.film_id = fa.film_id
order by 1,2;
/*****/
select f.film_id,count(*),
array_agg(fa.actor_id),
(array_agg(fa.actor_id))[1:3], -- 数组中为前3个演员的ID
(select sum(x) from unnest(array_agg(fa.actor_id)) x)
from film f
left join film_actor fa on f.film_id = fa.film_id
group by 1
order by 1,2
结果如下图：
select t2.customer_id,count(distinct t2.genre) from (
select t.customer_id,t.genre,count(*) from(
select r.rental_id,r.customer_id,r.inventory_id,f.title,fc.category_id,c.name as genre
from rental r
left join inventory i on r.inventory_id = i.inventory_id
left join film f on i.film_id = f.film_id
left join film_category fc on fc.film_id = f.film_id
left join category c on c.category_id = fc.category_id
)t
group by 1,2
order by 1,3 desc
)t2
group by 1
having count(*) > 10
order by 2;
select t.actor_id, count(distinct t.film_id) from (
select f.film_id,f.title,f.release_year,fa.actor_id,a.first_name,a.last_name
from film f
left join film_actor fa on f.film_id = fa.film_id
left join actor a on fa.actor_id = a.actor_id
order by 1,4
)t
group by 1
having count(distinct t.film_id) > 34
order by 2 desc;
结果如下图：
with base_table as (
select p.rental_id, p.amount,r.inventory_id,i.film_id,f.title,f.rating
from payment p
left join rental r on p.rental_id = r.rental_id
left join inventory i on i.inventory_id = r.inventory_id
left join film f on f.film_id = i.film_id
),next_table as(
select bt.title,bt.rating, sum(bt.amount)
from base_table bt
group by 1,2
order by 3 desc
)
select * from(
select nt.*,
row_number() over (partition by nt.rating order by nt.sum desc) as rating_rank
from next_table nt
)t
where t.rating_rank = 1;
结果如下图：
CREATE TABLE "actor" (
"actor_id" int4 NOT NULL DEFAULT nextval('actor_actor_id_seq'::regclass),
"first_name" varchar(45) COLLATE "default" NOT NULL,
"last_name" varchar(45) COLLATE "default" NOT NULL,
"last_update" timestamp(6) NOT NULL DEFAULT now(),
CONSTRAINT "actor_pkey" PRIMARY KEY ("actor_id")
)
WITHOUT OIDS;
CREATE TRIGGER "last_updated" BEFORE UPDATE ON "actor" FOR EACH ROW EXECUTE PROCEDURE "public"."last_updated"();
CREATE INDEX "idx_actor_last_name" ON "actor" USING btree ("last_name" "pg_catalog"."text_ops" ASC NULLS LAST);
ALTER TABLE "actor" OWNER TO "postgres";
CREATE TABLE "address" (
"address_id" int4 NOT NULL DEFAULT nextval('address_address_id_seq'::regclass),
"address" varchar(50) COLLATE "default" NOT NULL,
"address2" varchar(50) COLLATE "default",
"district" varchar(20) COLLATE "default" NOT NULL,
"city_id" int2 NOT NULL,
"postal_code" varchar(10) COLLATE "default",
"phone" varchar(20) COLLATE "default" NOT NULL,
"last_update" timestamp(6) NOT NULL DEFAULT now(),
CONSTRAINT "address_pkey" PRIMARY KEY ("address_id")
)
WITHOUT OIDS;
CREATE TRIGGER "last_updated" BEFORE UPDATE ON "address" FOR EACH ROW EXECUTE PROCEDURE "public"."last_updated"();
CREATE INDEX "idx_fk_city_id" ON "address" USING btree ("city_id" "pg_catalog"."int2_ops" ASC NULLS LAST);
ALTER TABLE "address" OWNER TO "postgres";
CREATE TABLE "category" (
"category_id" int4 NOT NULL DEFAULT nextval('category_category_id_seq'::regclass),
"name" varchar(25) COLLATE "default" NOT NULL,
"last_update" timestamp(6) NOT NULL DEFAULT now(),
CONSTRAINT "category_pkey" PRIMARY KEY ("category_id")
)
WITHOUT OIDS;
CREATE TRIGGER "last_updated" BEFORE UPDATE ON "category" FOR EACH ROW EXECUTE PROCEDURE "public"."last_updated"();
ALTER TABLE "category" OWNER TO "postgres";
CREATE TABLE "city" (
"city_id" int4 NOT NULL DEFAULT nextval('city_city_id_seq'::regclass),
"city" varchar(50) COLLATE "default" NOT NULL,
"country_id" int2 NOT NULL,
"last_update" timestamp(6) NOT NULL DEFAULT now(),
CONSTRAINT "city_pkey" PRIMARY KEY ("city_id")
)
WITHOUT OIDS;
CREATE TRIGGER "last_updated" BEFORE UPDATE ON "city" FOR EACH ROW EXECUTE PROCEDURE "public"."last_updated"();
CREATE INDEX "idx_fk_country_id" ON "city" USING btree ("country_id" "pg_catalog"."int2_ops" ASC NULLS LAST);
ALTER TABLE "city" OWNER TO "postgres";
CREATE TABLE "country" (
"country_id" int4 NOT NULL DEFAULT nextval('country_country_id_seq'::regclass),
"country" varchar(50) COLLATE "default" NOT NULL,
"last_update" timestamp(6) NOT NULL DEFAULT now(),
CONSTRAINT "country_pkey" PRIMARY KEY ("country_id")
)
WITHOUT OIDS;
CREATE TRIGGER "last_updated" BEFORE UPDATE ON "country" FOR EACH ROW EXECUTE PROCEDURE "public"."last_updated"();
ALTER TABLE "country" OWNER TO "postgres";
CREATE TABLE "customer" (
"customer_id" int4 NOT NULL DEFAULT nextval('customer_customer_id_seq'::regclass),
"store_id" int2 NOT NULL,
"first_name" varchar(45) COLLATE "default" NOT NULL,
"last_name" varchar(45) COLLATE "default" NOT NULL,
"email" varchar(50) COLLATE "default",
"address_id" int2 NOT NULL,
"activebool" bool NOT NULL DEFAULT true,
"create_date" date NOT NULL DEFAULT ('now'::text)::date,
"last_update" timestamp(6) DEFAULT now(),
"active" int4,
CONSTRAINT "customer_pkey" PRIMARY KEY ("customer_id")
)
WITHOUT OIDS;
CREATE TRIGGER "last_updated" BEFORE UPDATE ON "customer" FOR EACH ROW EXECUTE PROCEDURE "public"."last_updated"();
CREATE INDEX "idx_fk_address_id" ON "customer" USING btree ("address_id" "pg_catalog"."int2_ops" ASC NULLS LAST);
CREATE INDEX "idx_fk_store_id" ON "customer" USING btree ("store_id" "pg_catalog"."int2_ops" ASC NULLS LAST);
CREATE INDEX "idx_last_name" ON "customer" USING btree ("last_name" "pg_catalog"."text_ops" ASC NULLS LAST);
ALTER TABLE "customer" OWNER TO "postgres";
CREATE TABLE "film" (
"film_id" int4 NOT NULL DEFAULT nextval('film_film_id_seq'::regclass),
"title" varchar(255) COLLATE "default" NOT NULL,
"description" text COLLATE "default",
"release_year" "public"."year",
"language_id" int2 NOT NULL,
"rental_duration" int2 NOT NULL DEFAULT 3,
"rental_rate" numeric(4,2) NOT NULL DEFAULT 4.99,
"length" int2,
"replacement_cost" numeric(5,2) NOT NULL DEFAULT 19.99,
"rating" "public"."mpaa_rating" DEFAULT 'G'::mpaa_rating,
"last_update" timestamp(6) NOT NULL DEFAULT now(),
"special_features" text[] COLLATE "default",
"fulltext" tsvector NOT NULL,
CONSTRAINT "film_pkey" PRIMARY KEY ("film_id")
)
WITHOUT OIDS;
CREATE TRIGGER "film_fulltext_trigger" BEFORE INSERT OR UPDATE ON "film" FOR EACH ROW EXECUTE PROCEDURE "pg_catalog"."tsvector_update_trigger"('fulltext', 'pg_catalog.english', 'title', 'description');
CREATE TRIGGER "last_updated" BEFORE UPDATE ON "film" FOR EACH ROW EXECUTE PROCEDURE "public"."last_updated"();
CREATE INDEX "film_fulltext_idx" ON "film" USING gist ("fulltext" "pg_catalog"."tsvector_ops");
CREATE INDEX "idx_fk_language_id" ON "film" USING btree ("language_id" "pg_catalog"."int2_ops" ASC NULLS LAST);
CREATE INDEX "idx_title" ON "film" USING btree ("title" "pg_catalog"."text_ops" ASC NULLS LAST);
ALTER TABLE "film" OWNER TO "postgres";
CREATE TABLE "film_actor" (
"actor_id" int2 NOT NULL,
"film_id" int2 NOT NULL,
"last_update" timestamp(6) NOT NULL DEFAULT now(),
CONSTRAINT "film_actor_pkey" PRIMARY KEY ("actor_id", "film_id")
)
WITHOUT OIDS;
CREATE TRIGGER "last_updated" BEFORE UPDATE ON "film_actor" FOR EACH ROW EXECUTE PROCEDURE "public"."last_updated"();
CREATE INDEX "idx_fk_film_id" ON "film_actor" USING btree ("film_id" "pg_catalog"."int2_ops" ASC NULLS LAST);
ALTER TABLE "film_actor" OWNER TO "postgres";
CREATE TABLE "film_category" (
"film_id" int2 NOT NULL,
"category_id" int2 NOT NULL,
"last_update" timestamp(6) NOT NULL DEFAULT now(),
CONSTRAINT "film_category_pkey" PRIMARY KEY ("film_id", "category_id")
)
WITHOUT OIDS;
CREATE TRIGGER "last_updated" BEFORE UPDATE ON "film_category" FOR EACH ROW EXECUTE PROCEDURE "public"."last_updated"();
ALTER TABLE "film_category" OWNER TO "postgres";
CREATE TABLE "inventory" (
"inventory_id" int4 NOT NULL DEFAULT nextval('inventory_inventory_id_seq'::regclass),
"film_id" int2 NOT NULL,
"store_id" int2 NOT NULL,
"last_update" timestamp(6) NOT NULL DEFAULT now(),
CONSTRAINT "inventory_pkey" PRIMARY KEY ("inventory_id")
)
WITHOUT OIDS;
CREATE TRIGGER "last_updated" BEFORE UPDATE ON "inventory" FOR EACH ROW EXECUTE PROCEDURE "public"."last_updated"();
CREATE INDEX "idx_store_id_film_id" ON "inventory" USING btree ("store_id" "pg_catalog"."int2_ops" ASC NULLS LAST, "film_id" "pg_catalog"."int2_ops" ASC NULLS LAST);
ALTER TABLE "inventory" OWNER TO "postgres";
CREATE TABLE "language" (
"language_id" int4 NOT NULL DEFAULT nextval('language_language_id_seq'::regclass),
"name" char(20) COLLATE "default" NOT NULL,
"last_update" timestamp(6) NOT NULL DEFAULT now(),
CONSTRAINT "language_pkey" PRIMARY KEY ("language_id")
)
WITHOUT OIDS;
CREATE TRIGGER "last_updated" BEFORE UPDATE ON "language" FOR EACH ROW EXECUTE PROCEDURE "public"."last_updated"();
ALTER TABLE "language" OWNER TO "postgres";
CREATE TABLE "payment" (
"payment_id" int4 NOT NULL DEFAULT nextval('payment_payment_id_seq'::regclass),
"customer_id" int2 NOT NULL,
"staff_id" int2 NOT NULL,
"rental_id" int4 NOT NULL,
"amount" numeric(5,2) NOT NULL,
"payment_date" timestamp(6) NOT NULL,
CONSTRAINT "payment_pkey" PRIMARY KEY ("payment_id")
)
WITHOUT OIDS;
CREATE INDEX "idx_fk_customer_id" ON "payment" USING btree ("customer_id" "pg_catalog"."int2_ops" ASC NULLS LAST);
CREATE INDEX "idx_fk_rental_id" ON "payment" USING btree ("rental_id" "pg_catalog"."int4_ops" ASC NULLS LAST);
CREATE INDEX "idx_fk_staff_id" ON "payment" USING btree ("staff_id" "pg_catalog"."int2_ops" ASC NULLS LAST);
ALTER TABLE "payment" OWNER TO "postgres";
CREATE TABLE "rental" (
"rental_id" int4 NOT NULL DEFAULT nextval('rental_rental_id_seq'::regclass),
"rental_date" timestamp(6) NOT NULL,
"inventory_id" int4 NOT NULL,
"customer_id" int2 NOT NULL,
"return_date" timestamp(6),
"staff_id" int2 NOT NULL,
"last_update" timestamp(6) NOT NULL DEFAULT now(),
CONSTRAINT "rental_pkey" PRIMARY KEY ("rental_id")
)
WITHOUT OIDS;
CREATE TRIGGER "last_updated" BEFORE UPDATE ON "rental" FOR EACH ROW EXECUTE PROCEDURE "public"."last_updated"();
CREATE INDEX "idx_fk_inventory_id" ON "rental" USING btree ("inventory_id" "pg_catalog"."int4_ops" ASC NULLS LAST);
CREATE UNIQUE INDEX "idx_unq_rental_rental_date_inventory_id_customer_id" ON "rental" USING btree ("rental_date" "pg_catalog"."timestamp_ops" ASC NULLS LAST, "inventory_id" "pg_catalog"."int4_ops" ASC NULLS LAST, "customer_id" "pg_catalog"."int2_ops" ASC NULLS LAST);
ALTER TABLE "rental" OWNER TO "postgres";
CREATE TABLE "staff" (
"staff_id" int4 NOT NULL DEFAULT nextval('staff_staff_id_seq'::regclass),
"first_name" varchar(45) COLLATE "default" NOT NULL,
"last_name" varchar(45) COLLATE "default" NOT NULL,
"address_id" int2 NOT NULL,
"email" varchar(50) COLLATE "default",
"store_id" int2 NOT NULL,
"active" bool NOT NULL DEFAULT true,
"username" varchar(16) COLLATE "default" NOT NULL,
"password" varchar(40) COLLATE "default",
"last_update" timestamp(6) NOT NULL DEFAULT now(),
"picture" bytea,
CONSTRAINT "staff_pkey" PRIMARY KEY ("staff_id")
)
WITHOUT OIDS;
CREATE TRIGGER "last_updated" BEFORE UPDATE ON "staff" FOR EACH ROW EXECUTE PROCEDURE "public"."last_updated"();
ALTER TABLE "staff" OWNER TO "postgres";
CREATE TABLE "store" (
"store_id" int4 NOT NULL DEFAULT nextval('store_store_id_seq'::regclass),
"manager_staff_id" int2 NOT NULL,
"address_id" int2 NOT NULL,
"last_update" timestamp(6) NOT NULL DEFAULT now(),
CONSTRAINT "store_pkey" PRIMARY KEY ("store_id")
)
WITHOUT OIDS;
CREATE TRIGGER "last_updated" BEFORE UPDATE ON "store" FOR EACH ROW EXECUTE PROCEDURE "public"."last_updated"();
CREATE UNIQUE INDEX "idx_unq_manager_staff_id" ON "store" USING btree ("manager_staff_id" "pg_catalog"."int2_ops" ASC NULLS LAST);
ALTER TABLE "store" OWNER TO "postgres";
ALTER TABLE "address" ADD CONSTRAINT "fk_address_city" FOREIGN KEY ("city_id") REFERENCES "city" ("city_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "city" ADD CONSTRAINT "fk_city" FOREIGN KEY ("country_id") REFERENCES "country" ("country_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "customer" ADD CONSTRAINT "customer_address_id_fkey" FOREIGN KEY ("address_id") REFERENCES "address" ("address_id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "film" ADD CONSTRAINT "film_language_id_fkey" FOREIGN KEY ("language_id") REFERENCES "language" ("language_id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "film_actor" ADD CONSTRAINT "film_actor_actor_id_fkey" FOREIGN KEY ("actor_id") REFERENCES "actor" ("actor_id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "film_actor" ADD CONSTRAINT "film_actor_film_id_fkey" FOREIGN KEY ("film_id") REFERENCES "film" ("film_id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "film_category" ADD CONSTRAINT "film_category_category_id_fkey" FOREIGN KEY ("category_id") REFERENCES "category" ("category_id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "film_category" ADD CONSTRAINT "film_category_film_id_fkey" FOREIGN KEY ("film_id") REFERENCES "film" ("film_id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "inventory" ADD CONSTRAINT "inventory_film_id_fkey" FOREIGN KEY ("film_id") REFERENCES "film" ("film_id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "payment" ADD CONSTRAINT "payment_customer_id_fkey" FOREIGN KEY ("customer_id") REFERENCES "customer" ("customer_id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "payment" ADD CONSTRAINT "payment_rental_id_fkey" FOREIGN KEY ("rental_id") REFERENCES "rental" ("rental_id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "payment" ADD CONSTRAINT "payment_staff_id_fkey" FOREIGN KEY ("staff_id") REFERENCES "staff" ("staff_id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "rental" ADD CONSTRAINT "rental_customer_id_fkey" FOREIGN KEY ("customer_id") REFERENCES "customer" ("customer_id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "rental" ADD CONSTRAINT "rental_inventory_id_fkey" FOREIGN KEY ("inventory_id") REFERENCES "inventory" ("inventory_id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "rental" ADD CONSTRAINT "rental_staff_id_key" FOREIGN KEY ("staff_id") REFERENCES "staff" ("staff_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "staff" ADD CONSTRAINT "staff_address_id_fkey" FOREIGN KEY ("address_id") REFERENCES "address" ("address_id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "store" ADD CONSTRAINT "store_address_id_fkey" FOREIGN KEY ("address_id") REFERENCES "address" ("address_id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "store" ADD CONSTRAINT "store_manager_staff_id_fkey" FOREIGN KEY ("manager_staff_id") REFERENCES "staff" ("staff_id") ON DELETE RESTRICT ON UPDATE CASCADE;
