# Common Aggregate FunctionsSELECT count(columName)FROM tableName;SELECT sum(columName)FROM tableName;SELECT avg(columName)FROM tableName;SELECT max(columName)FROM tableName;SELECT min(columName)FROM tableName;# Aggregates Within ClausesSELECT columnName, aggregate_function(columnName)FROM tableNameGROUP BY columnName;SELECT columnName, aggregate_function(columnName)FROM tableNameGROUP BY columnNameHAVING count(*) > 1;SELECT columnName, aggregate_function(columnName)FROM tableNameWHERE columnName operator value (optional)GROUP BY columnName;HAVING aggregate_function(columnName) operator value;SELECT genre, sum(cost)FROM MoviesWHERE cost >= 1000000GROUP BY genre;HAVING COUNT(*) > 1;i.e.SELECT country, sum(salary)From ActorsWHERE role = supportingGROUP BY countryHAVING count(country) > 1;# ConstraintsCREATE TABLE Promotions( id int, name varchar(50), category varchar(15));INSERT INTO Promotions (id, category)VALUES (3, Mechandies);CREATE TABLE Promotions( id int, name varchar(50) NOT NULL, category varchar(15));INSERT INTO Promotions (id, category)VALUES (3, Mechandies);Why use Constraints?Prevent NULL valuesEnsure column values are uniqueProvide Additional validationsCREATE TABLE Promotions( id int, name varchar(50) NOT NULL UNIQUE, category varchar(15));CREATE TABLE Promotions( id int, name varchar(50) NOT NULL, category varchar(15), CONSTRAINT uniqueName UNIQUE (name));# Column ConstraintCREATE TABLE Promotions( id int, name varchar(50) NOT NULL UNIQUE, category varchar(15));# Table ConstraintCREATE TABLE Promotions( id int, name varchar(50) NOT NULL, category varchar(15), CONSTRAINT uniqueName UNIQUE (name));# Both name and category are unique# And cannot be in duplicate in multiple rowsCREATE TABLE Promotions( id int, name varchar(50) NOT NULL, category varchar(15), CONSTRAINT uniqueName UNIQUE (name, category));# Primary Key: NOT NULL and uniqueCREATE TABLE Promotions( id int PRIMARY KEY, name varchar(50), category varchar(15));Q: Difference b/t Primary Key vs NOT NULL and UNIQUEA: The PRIMARY KEY can only be defined once per table, whereas NOT NULL and UNIQUE can be used on multiple columns.#ExampleCREATE TABLE Movies ( name varchar(50) NOT NULL, language varchar(50), CONSTRAINT name_lang UNIQUE (name, language));Common naming conventionSingular version of the table, an underscore followed by the columnName.i.e: movie_idForeign keySELECT idFrom MoviesWHERE title = Gone With the Wind;SELECT name, categoryFrom PromotionsWHERE movie_id = 2;CREATE TABLE Movies ( id int PRIMARY KEY, title varchar(20) NOT NULL UNIQUE);CREATE TABLE Promotions ( id int PRIMARY KEY, movie_id int REFERENCES movies(id), name varchar(50), category varchar(15));CREATE TABLE Promotions ( id int PRIMARY KEY, movie_id int REFERENCES movies, name varchar(50), category varchar(15));When column name is missing from `REFERENCES`, primary key of that table will be used.CREATE TABLE Promotions ( id int PRIMARY KEY, movie_id int, name varchar(50), category varchar(15), FOREIGN KEY (movie_id) REFERENCES movies);i.e.CREATE TABLE Actors ( id int PRIMARY KEY, name varchar(50) NOT NULL UNIQUE, country_id int REFERENCES Countries);i.e.CREATE TABLE Actors ( id int PRIMARY KEY, name varchar(50) NOT NULL UNIQUE, country_id int, FOREIGN KEY (country_id) REFERENCES Countries);# Orphan record1. DELETE FROM Movies WHERE id = 6;2. DROP TABLE Movies;DELETE FROM Promotions WHERE movie_id = 6;DELETE FROM Movies WHERE id = 6;# CHECK ConstraintCREATE TABLE Movies ( id int PRIMARY KEY, title varchar(20) NOT NULL UNIQUE, duration int CHECK (duration > 0));i.e.CREATE TABLE Actors ( id int PRIMARY KEY, name varchar(50) NOT NULL UNIQUE, salary integer CHECK (salary > 500), bonus integer CHECK (bonus < salary), country_id int REFERENCES Countries(id));NormalizationNormalization is the process of reducing duplication in database tablesRules1. Tables must not contain repeating groups of data in 1 column2. Table must not contain redunance (unnecessary repeating information)Join table naming conventioni.e. Movies_GenresSELECT nameFROM GenresWHERE id = 2 or id = 3;SELECT nameFROM GenresWHERE id in (2,3);Join TableCREATE TABLE Actors_Movies ( actor_id int, movie_id int);CREATE TABLE Actors_Movies ( actor_id int REFERENCES Actors movie_id int REFERENCES Movies);CREATE TABLE Actors_Movies ( actor_id int, movie_id int, FOREIGN KEY (actor_id) REFERENCES Actors, FOREIGN KEY (movie_id) REFERENCES Movies);CREATE TABLE Actors_Movies ( actor_id int, movie_id int, FOREIGN KEY (actor_id) REFERENCES Actors, FOREIGN KEY (movie_id) REFERENCES Movies);INSERT INTO Actors_Movies (actor_id, movie_id) VALUES (2, 5);# Relationships* One to One* One to Many* Many to ManyQuestion: In our theatre 1 movie can play in one room at a time. Lets add a constraint that will ensure that no movies are ever marked to play in the same room at the same time.CREATE TABLE Rooms ( id int PRIMARY KEY, seats int, movie_id int UNIQUE, FOREIGN KEY (movie_id) REFERENCES Movies);CREATE TABLE Rooms ( id int PRIMARY KEY, seats int, movie_id int REFERENCES Movies UNIQUE);# Inner JoinSELECT review, movie_idFROM Reviews;SELECT titleFrom MoviesWHERE id IN (1,2,4);We can write above 2 queries into 1 querySELECT *FROM MoviesINNER JOIN ReviewsON Movies.ID ? Watch Movies Online FREE = Reviews.movie_id;SELECT *FROM ReviewsINNER JOIN MoviesON Reviews.movie_id = Movies.ID ? Watch Movies Online FREE;SELECT Movies.title, Reviews.reviewFROM MoviesINNER JOIN ReviewsON Movies.ID ? Watch Movies Online FREE = Reviews.movie_id;SELECT Movies.title, Genres.nameFROM MoviesINNER JOIN Movies_GenresON Movies.ID ? Watch Movies Online FREE = Movies_Genres.movie_idINNER JOIN GenresON Movies_Genres.genre_id = Genres.idWHERE Movies.title = "Peter Pan";Q: Join the Movies table with the Rooms table so that we only fetch movies that have an associated room.SELECT *From MoviesINNER JOIN RoomsON Movies.ID ? Watch Movies Online FREE = Rooms.movie_id;Q: Lets get a little more specific, and only return the movie title, the id for the room, and number of seats in the theatre.SELECT *From MoviesINNER JOIN RoomsON Movies.ID ? Watch Movies Online FREE = Rooms.movie_id;SELECT Movies.title, Rooms.id, Rooms.seatsFrom MoviesINNER JOIN RoomsON Movies.ID ? Watch Movies Online FREE = Rooms.movie_idWHERE Rooms.seats > 75;SELECT Movies.title, Rooms.id, Rooms.seatsFrom MoviesINNER JOIN RoomsON Movies.ID ? Watch Movies Online FREE = Rooms.movie_idWHERE Rooms.seats > 75ORDER BY Rooms.seats DESC;SELECT Actors.name, Movies.titleFROM ActorsINNER JOIN Actors_MoviesON Actors.id = Actors_Movies.actor_idINNER JOIN MoviesON Movies.ID ? Watch Movies Online FREE = Actors_Movies.movie_idORDER BY Movies.title;# AliasesSELECT Movies.title AS films, Reviews.review AS reviewsFROM MoviesINNER JOIN ReviewsON Movies.ID ? Watch Movies Online FREE = Reviews.movie_id;## Without ASSELECT Movies.title films, Reviews.review reviewsFROM MoviesINNER JOIN ReviewsON Movies.ID ? Watch Movies Online FREE = Reviews.movie_id;SELECT Movies.title "Weekly Movies", Reviews.review "Weekly Reviews"FROM MoviesINNER JOIN ReviewsON Movies.ID ? Watch Movies Online FREE = Reviews.movie_id;SELECT m.title "Weekly Movies", Reviews.reviewFROM Movies mINNER JOIN ReviewsON m.id = Reviews.movie_idORDER BY m.title;SELECT m.title, r.reviewFROM Movies mINNER JOIN Reviews rON m.id = r.movie_idORDER BY m.title;# OUTER JOINSELECT *FROM MoviesLEFT OUTER JOIN ReviewsON Movies.ID ? Watch Movies Online FREE = Reviews.movie_id;SELECT m.title, r.reviewFROM Movies mLEFT OUTER JOIN Reviews rON m.id = r.movie_idORDER BY r.id;SELECT *FROM MoviesRIGHT OUTER JOIN ReviewsON Movies.ID ? Watch Movies Online FREE = Reviews.movie_id;SELECT m.title, r.reviewFROM Movies mRIGHT OUTER JOIN Reviews rON m.id = r.movie_idORDER BY r.id;# Sub-queriesInner query or sub-query is surrounded by parathesesesSELECT SUM(sales)FROM MoviesWHERE id IN(SELECT movie_id FROM Promotions WHERE category = Non-cach);Sub-query: easier to readJOIN query: better for performanceSub-query Syntax:WHERE <FIELD> IN (<subquery>)WHERE <FIELD> NOT IN (<subquery>)SELECT *FROM MoviesWHERE duration > (SELECT AVG(duration) From Movies);SELECT r.id, m.titleFROM Rooms rLEFT OUTER JOIN Movies mON r.movie_id = m.idWHERE seats > (SELECT AVG(seats) FROM Rooms);

還有更高級的SQL寫法: TOP