Introduction to SQL
by K. Yue
1. Introduction
2. SQL and MySQL
MySQL Setup
We will use MySQL. In this course, you may want to run two versions of MySQL.
You may also connect to the MySQL servers at dcm.uhcl.edu (or dcmweb) at port 3306 (version 5.x) or port 3308 (version 8.x).
You may use the command line program 'mysql' as MySQL client from MariaDB or MySQL.
mysql –h host -u user -p
or
mysql –h host -u user -p -P port_number
to start your mysql session. See: https://dev.mysql.com/doc/refman/8.0/en/mysql-command-options.html
(In order to do so, assuming you are using Windows, it needs to know where to find mysql. Thus, you will need to include the location of the mysql in your PATH environment variable. For example, PATH may include this: "c:\xampp\mysql\bin" for MariaDB or "C:\Program Files\MySQL\MySQL Server 8.0\bin" for MySQL 8.xm or both)
You may also use your favorite SQL clients, such as MySQL Workbench, HeidiSQL, etc.: http://www.slant.co/topics/53/~mysql-client-applications-for-windows.
Exercise on the supply database:
All versions of MySQL servers already have the database "supply" and "sakila" installed. No need to install them when using DCM server.
DDL:
Examples: the supply database
-- Barebone:
--
Drop tables before creating them again.
-- Note that Supply needs to be dropped first.
-- Otherwise referential integrity may be violated.
DROP TABLE SUPPLY;
DROP TABLE PART;
DROP TABLE SUPPLIER;
-- Create tables
CREATE OR REPLACE TABLE SUPPLIER (
SNUM varchar(9),
SNAME varchar(15),
SCITY varchar(15),
STATUS integer(3),
primary key (SNUM)
);
CREATE OR REPLACE TABLE PART (
PNUM varchar(9),
PNAME varchar(15),
COLOR varchar(12),
WEIGHT integer(5),
primary key (PNUM)
);
CREATE OR REPLACE TABLE SUPPLY (
SNUM varchar(9),
PNUM varchar(9),
QUANTITY integer(6),
primary key (SNUM, PNUM),
foreign key (SNUM) references SUPPLIER(SNUM),
foreign key (PNUM) references PART(PNUM)
);
Columns may include special characters but you will need special syntax. You can not use the name 'first name' directly as column name as spaces are not allowed. Instead:
Example:
SQL provides many options. Study them when necessary. For example, one may create a table from the result of a query.
CREATE TABLE t1
AS
SELECT s.snum, s.sname, s.status, p.pnum, p.pname, su.quantity
FROM supplier s INNER JOIN supply su USING (snum)
INNER JOIN part p USING (pnum);
SELECT * FROM t1 WHERE Status > 15;
DROP TABLE t1;
-- Note that a temporary table exists only in the session of
-- a SQL client connected with the MySQL server.
CREATE TEMPORARY TABLE t2
AS
SELECT s.snum, s.sname, s.status, p.pnum, p.pname, su.quantity
FROM supplier s INNER JOIN supply su USING (snum)
INNER JOIN part p USING (pnum);
SELECT * FROM t2 WHERE Status > 15;
Study the Sakila database that comes with MySQL: sakila-schema.sql.txt and sakila-data.sql.txt. The file sakila.mwb contains the relational schema in MySQL workbench format.
Examples:
CREATE VIEW Houston_Supplier AS
SELECT *
FROM SUPPLIER
WHERE SCity = 'Houston';
mysql> SELECT *
-> from Houston_Supplier;
+------+-------+---------+--------+
| SNUM | SNAME | SCITY | STATUS |
+------+-------+---------+--------+
| S2 | DEF | Houston | 20 |
| S3 | Go go | Houston | 12 |
+------+-------+---------+--------+
2 rows in set (0.01 sec)
DML
Example:
INSERT INTO <<table>> [<<columns>>]
VALUES <<expression>>
INSERT INTO <<table>> [<<columns>>]
<<select statement>>
DELETE FROM <<table>>
WHERE <<condition>>;
UPDATE <<table>>
SET <<update assignments>>
[WHERE <<update condition>>]
Example:
UPDATE supplier
SET Scity = 'Beaumont'
WHERE SNUM = 'S1';
Example:
Execute the following SQL statements one by one. Before executing it, predict the result.
-- Insert/update/delete
SELECT * FROM Supplier;
INSERT INTO Supplier(snum, sname, scity, status)
VALUES ('S111', 'Bill''s Supplies', 'Houston', 1);
INSERT INTO Supplier(snum, sname, scity, status)
VALUES ('S112', 'Carol''s Supplies', 'Houston', 20);
INSERT INTO Supplier(snum, sname, status)
VALUES ('S112', 'Nancy''s Supplies', NULL);
INSERT INTO Supplier(snum, sname, status)
VALUES ('S113', 'Tom''s Supplies', NULL);
INSERT INTO Supplier(snum, sname, status)
VALUES ('S114', 'Arnold''s Supplies', NULL);
SELECT * FROM Supplier;
DELETE FROM Supplier;
-- will get an error message.
DELETE FROM Supplier
WHERE snum = 'S111';
SELECT * FROM Supplier;
DELETE FROM Supplier
WHERE status IS NULL;
SELECT * FROM Supplier;
mysql> CREATE TEMPORARY TABLE temp(
-> SNUM varchar(9),
-> SNAME varchar(30),
-> numParts integer(4),
-> primary key (SNUM));
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> SELECT * FROM temp;
Empty set (0.00 sec)
mysql>
mysql> INSERT INTO temp
-> SELECT s.SNUM, s.SNAME, COUNT(u.PNUM) AS numParts
-> FROM supplier AS s NATURAL JOIN supply AS u
-> GROUP BY s.SNUM, s.SNAME;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT * FROM temp;
+------+-------+----------+
| SNUM | SNAME | numParts |
+------+-------+----------+
| S1 | ABC | 2 |
| S2 | DEF | 3 |
| S3 | Go go | 4 |
| S4 | P&G | 4 |
| S5 | Yap | 4 |
+------+-------+----------+
5 rows in set (0.00 sec)
Example:
-- All suppliers move to Beaumont.
UPDATE supplier
SET Scity = 'Beaumont';
The Select Statement
SELECT DISTINCT <<result_columns>> -- [3] construct result columns
FROM
<<source_tables>> -- [1] conceptually join to form a large table
WHERE
<<conditions_for_inclusion>> -- [2] Filter rows
Example:
Problem: Show the supplier names, supplier cities, and the quantities and names of red parts they supply.
Expected Result:
+-------+---------+----------+----------+
| sname | scity | quantity | pname |
+-------+---------+----------+----------+
| DEF | Houston | 6 | Micropod |
| Go go | Houston | 1 | Micropod |
| Yap | Phoenix | 5 | Minipod |
| Yap | Phoenix | 10 | Micropod |
+-------+---------+----------+----------+
Analysis:
Query:
SELECT DISTINCT s.sname, s.scity, su.quantity, p.pname
FROM supplier s, supply su, part p
WHERE s.snum = su.snum
AND su.pnum = p.pnum
AND p.color = 'Red';
Class Exercise
Problem. Show all information of suppliers with a status of greater than five and supplies a part of weight greater than five. Q25
New template:
Problem: Show the supplier identities, supplier cities, and the quantities and names of red parts they supply. The supplier identity is in the form of snum:sname.
Expected Result:
+----------+---------------+----------+----------+
| supplier | supplier city | quantity | name |
+----------+---------------+----------+----------+
| S2:DEF | Houston | 6 | Micropod |
| S3:Go go | Houston | 1 | Micropod |
| S5:Yap | Phoenix | 5 | Minipod |
| S5:Yap | Phoenix | 10 | Micropod |
+----------+---------------+----------+----------+
Analysis:
Query:
SELECT DISTINCT CONCAT(s.snum, ':', s.sname) AS supplier,
s.scity AS `supplier city`,
su.quantity AS quantity,
p.pname AS name
FROM supplier s, supply su, part p
WHERE s.snum = su.snum
AND su.pnum = p.pnum
AND p.color = 'Red';
New template:
SELECT DISTINCT <columns> -- 3: 3a
FROM <relations> -- 1
WHERE <condition> -- 2
ORDER BY <columns>; -- 4: 3b
(conceptually steps 3 and 4 may be performed together)
Example:
mysql> SELECT DISTINCT CONCAT(s.snum, ':', s.sname) AS supplier,
-> s.scity AS `supplier city`,
-> su.quantity AS quantity,
-> p.pname AS name
-> FROM supplier s, supply su, part p
-> WHERE s.snum = su.snum
-> AND su.pnum = p.pnum
-> AND p.color = 'Red'
-> ORDER BY supplier;
+----------+---------------+----------+----------+
| supplier | supplier city | quantity | name |
+----------+---------------+----------+----------+
| S2:DEF | Houston | 6 | Micropod |
| S3:Go go | Houston | 1 | Micropod |
| S5:Yap | Phoenix | 5 | Minipod |
| S5:Yap | Phoenix | 10 | Micropod |
+----------+---------------+----------+----------+
4 rows in set (0.00 sec)
mysql>
mysql> SELECT DISTINCT CONCAT(s.snum, ':', s.sname) AS supplier,
-> s.scity AS `supplier city`,
-> su.quantity AS quantity,
-> p.pname AS name
-> FROM supplier s, supply su, part p
-> WHERE s.snum = su.snum
-> AND su.pnum = p.pnum
-> AND p.color = 'Red'
-> ORDER BY s.status;
+----------+---------------+----------+----------+
| supplier | supplier city | quantity | name |
+----------+---------------+----------+----------+
| S5:Yap | Phoenix | 5 | Minipod |
| S5:Yap | Phoenix | 10 | Micropod |
| S3:Go go | Houston | 1 | Micropod |
| S2:DEF | Houston | 6 | Micropod |
+----------+---------------+----------+----------+
4 rows in set (0.00 sec)
Note the difference in the order of the results. Also, in the second SQL statement, s.status is not in the result.
Example:
-- All suppliers with Status between 5 to 15.
SELECT *
FROM Supplier
WHERE Status BETWEEN 5 AND 15;
-- All suppliers with status of 1, 5, 10 or 20.
-- Order by status.
SELECT *
FROM Supplier
WHERE Status IN (1, 5, 10, 20)
ORDER BY Status;
-- All suppliers with the character 'o' in the City.
SELECT *
FROM Supplier
WHERE SCity LIKE '%o%';
Notes:
Join
New template:
SELECT DISTINCT <columns> -- 3: 3a
FROM <sources> -- 1
WHERE <condition> -- 2
ORDER BY <columns>; -- 4: 3b
Types of Joins
Example:
SELECT * FROM Supplier;
SELECT * FROM Supply;
-- Unlike the natural join in RA, in SQL, it uses the
-- equality of the foreign key (even if the foreign key has
-- a different name)
SELECT *
FROM supplier NATURAL JOIN supply;
SELECT *
FROM supplier INNER JOIN supply;
-- JOIN, INNER JOIN and CROSS JOIN are the same in MySQL
SELECT *
FROM supplier CROSS JOIN supply;
-- May specify common attribute (common name)
SELECT *
FROM supplier INNER JOIN supply USING (snum);
-- May use a general join condition, not only equality
-- of common attribute.
SELECT *
FROM supplier s INNER JOIN supply su
ON (s.snum = su.snum);
-- Left join will include every row from the left table,
-- even if there is no qualifying row in the right table.
SELECT *
FROM supplier LEFT JOIN supply USING (snum);
SELECT *
FROM supplier s LEFT JOIN supply su
ON (s.snum = su.snum);
-- Right join is a mirror image of left join.
SELECT *
FROM supplier right JOIN supply USING (snum);
SELECT *
FROM supplier s right JOIN supply su
on (s.snum = su.snum);
-- OUTER JOIN is supposed to be LEFT JOIN + RIGHT JOIN
-- but it is not implemented in MySQL.
SELECT *
FROM supplier OUTER JOIN supply USING (snum);
-- LEFT OUTER JOIN is the same as LEFT JOIN
SELECT *
FROM supplier LEFT OUTER JOIN supply USING (snum);
SELECT *
FROM supplier s LEFT OUTER JOIN supply su
ON (s.snum = su.snum);
Subqueries
Example:
Problem: Show all suppliers with a status within 10 of the maximum status any supplier has.
Analysis: algorithm
[1] max <- the maximum status.
[2] show all suppliers with status >= max - 10
Since we are using only declarative aspect of SELECT (except for JOIN), we may use a temporary table to store max. A temporary table will be removed at the end of a session.
-- Use of temporary table.
CREATE TEMPORARY TABLE temp -- temporary table
AS SELECT max(status) AS MAX FROM supplier;
-- List all supplier with status within 10 that of the largest status
-- any supplier has.
SELECT s.*
FROM supplier AS s, temp AS t -- no join condition; no common attribute
WHERE s.status >= t.max - 10
ORDER BY s.status DESC;
DROP TABLE temp; -- house cleaning: good style. May not be needed for temp table.
Alternatively, we can use a subquery, which can appear in the WHERE clause.
SELECT s.*
FROM supplier s
WHERE s.status >=
(SELECT MAX(status) FROM supplier) - 10
ORDER BY s.status DESC;
Note:
Subqueries can also appear in other places in the SELECT statement, such as the FROM clause.
New template:
SELECT DISTINCT <columns> -- 3: 3a
FROM <sources> -- 1
WHERE <condition> -- 2
ORDER BY <columns>; -- 4: 3b
Example:
SELECT DISTINCT s.*
FROM supplier s,
(SELECT MAX(status) AS MAX FROM supplier) AS temp
-- alias needed. otherwise cannot refer to it.
WHERE s.status >= temp.max - 10
ORDER BY s.status DESC;
What happens if the keyword 'distinct' is removed?
Example:
Problem: List all suppliers that supplies a green part and with a status within 10 of the maximum status any supplier has.
Analysis:
[1] max <- the maximum status.
[2] gpnum <- pnum of a green part.
[3] gsnum <- snum that supplies gpnum
[4] return snum with snum = gsnum and status >= max - 10
Query:
SELECT s.*
FROM supplier s
WHERE s.status >=
(SELECT MAX(status) FROM supplier) - 10
ORDER BY s.status DESC;
-- alternatively
SELECT DISTINCT s.*
FROM supplier s,
(SELECT MAX(status) AS MAX FROM supplier) AS temp
-- alias needed. otherwise cannot refer to it.
WHERE s.status >= temp.max - 10
ORDER BY s.status DESC;
Common Table Expressions (CTE)
Example:
Reconsider the previous problem.
Problem: List all suppliers that supplies a green part and with a status within 10 of the maximum status any supplier has.
Analysis:
[1] max <- the maximum status.
[2] gpnum <- pnum of a green part.
[3] gsnum <- snum that supplies gpnum
[4] return snum with snum = gsnum and status >= max - 10
Query:
SELECT DISCTINCT s.* -- [4]
FROM supplier s,
(SELECT max(status) AS max FROM supplier) AS t1, -- [1] max
(SELECT snum AS gsnum FROM supply -- [3] gpsum
WHERE pnum IN
(SELECT pnum FROM part WHERE color = 'green') -- [2] gpnum
) AS t2
WHERE s.status >= t1.max - 10
AND s.snum = t2.gsnum
ORDER BY s.status DESC;
-- The order of execution does not seem to be 'natural'.
-- Can we use something like below?
-- No, t2 cannot be used immediately within the FROM clause.
SELECT DISTINCT s.* -- [4]
FROM supplier s,
(SELECT max(status) AS max FROM supplier) AS t1, -- [1] max
(SELECT pnum FROM part WHERE color = 'green') AS t2, -- [2] gpnum
(SELECT DISTINCT snum FROM supply JOIN t2 usINg (pnum)) AS t3 -- [3] gpsum
WHERE s.status >= t1.max - 10
AND s.snum = t3.snum
ORDER BY s.status DESC;
The answer is no.
ERROR 1146 (42S02): Table 'supply.t2' doesn't exist
This is because the scope of t2 does not include following sources in the FROM clause.
To follow the order of the algorithm, we may use temporary tables.
-- using temporary tables;
-- [1]
CREATE TEMPORARY TABLE t1
AS SELECT max(status) AS max FROM supplier;
-- [2]
CREATE TEMPORARY TABLE t2
AS SELECT pnum FROM part WHERE color = 'green';
-- [3]
CREATE TEMPORARY TABLE t3
AS SELECT snum FROM supply JOIN t2 USING (pnum);
-- [4]
SELECT DISTINCT s.*
FROM supplier s, t1, t3
WHERE s.status >= t1.max - 10
AND s.snum = t3.snum;
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
CTE solution:
WITH
t1 AS (SELECT max(status) AS max FROM supplier), -- [1]
t2 AS (SELECT pnum FROM part WHERE color = 'green'), -- [2]
t3 AS (SELECT DISTINCT snum FROM supply INNER JOIN t2 USING (pnum)) -- [3]
SELECT distinct s.* -- [4]
FROM supplier s, t1, t3
WHERE s.status >= t1.max - 10
AND s.snum = t3.snum;
Note:
CTE is useful and better than temporary table solutions in many use cases.
Group by and having:
Example:
-- GROUP BY Example
SELECT s.sname, p.color, COUNT(DISTINCT u.pnum) AS `COUNT`
FROM supplier s natural join supply u natural join part p
GROUP BY s.sname, p.color;
EXPLAIN SELECT s.sname, p.color, COUNT(DISTINCT u.pnum) AS count
FROM supplier s, supply u, part p
WHERE s.snum = u.snum
AND u.pnum = p.pnum
GROUP BY s.sname, p.color;
Example on having:
-- Average status of suppliers grouped by city.
SELECT SCITY, 'Number of suppliers =', COUNT(*),
'Average Status = ', AVG(STATUS)
FROM Supplier
GROUP BY SCity;
-- Average weight of parts, grouped by color, Counting only parts with weight more than 3
-- AND showing only results of color with average weight more than 5.
SELECT Color, COUNT(*) AS count,
AVG(Weight) AS 'Average Weight'
FROM part
WHERE weight > 3
GROUP BY Color
HAVING Avg(Weight) > 5;
Examples with Sakila
(1) List the name of all active customers in the following format.
+-----------------------+
| Active Customer |
+-----------------------+
| MARY SMITH |
| PATRICIA JOHNSON |
| LINDA WILLIAMS |
| BARBARA JONES |
...
| FREDDIE DUGGAN |
| WADE DELVALLE |
| AUSTIN CINTRON |
+-----------------------+
584 rows in set (0.04 sec)
SELECT DISTINCT CONCAT(first_name, ' ', last_name) AS "Active Customer"
FROM CUSTOMER
WHERE active = TRUE;
(2) List the city "Ambattur" and "London" and the customers living in them in the following format:
+----------+----------------+
| city | Customer |
+----------+----------------+
| Ambattur | ARLENE HARVEY |
| London | MATTIE HOFFMAN |
| London | CECIL VINES |
+----------+----------------+
3 rows in set (0.00 sec)
SELECT DISTINCT CI.city, CONCAT(C.first_name, ' ', C.last_name) AS "Customer"
FROM CUSTOMER C, ADDRESS A, CITY CI
WHERE C.address_id = A.address_id
AND A.city_id = CI.city_id
AND (CI.city = 'Ambattur' OR CI.city = 'London');
(3) List all customers and their number of rentals in the following format. Note that it is listed in ascending order of customer's last name and then their first name. For example, "RAFAEL ABNEY" have rented 21 films.
+-----------------------+-------------------+
| Customer | Number of rentals |
+-----------------------+-------------------+
| RAFAEL ABNEY | 21 |
| NATHANIEL ADAM | 28 |
| KATHLEEN ADAMS | 27 |
| DIANA ALEXANDER | 27 |
| GORDON ALLARD | 32 |
| SHIRLEY ALLEN | 31 |
...
| LUIS YANEZ | 20 |
| MARVIN YEE | 21 |
| CYNTHIA YOUNG | 32 |
+-----------------------+-------------------+
599 rows in set (0.10 sec)
SELECT DISTINCT CONCAT(C.first_name, ' ', C.last_name) AS "Customer",
COUNT(R.rental_id) AS "Number of rentals"
FROM CUSTOMER C, RENTAL R
WHERE C.customer_id = R.customer_id
GROUP BY C.customer_id, C.first_name, C.last_name
ORDER BY C.last_name, C.first_name;
(4) List all actors who have appeared in an action film in the following format. Note that the result is sorted in ascending order of the first name and then last name of the actors.
+----------------------+
| ACTOR |
+----------------------+
| ADAM GRANT |
| ADAM HOPPER |
| AL GARLAND |
| ALAN DREYFUSS |
| ALBERT NOLTE |
| ALEC WAYNE |
| ANGELA HUDSON |
...
| WILLIAM HACKMAN |
| WOODY HOFFMAN |
| WOODY JOLIE |
| ZERO CAGE |
+----------------------+
165 rows in set (0.01 sec)
SELECT DISTINCT CONCAT(A.first_name, ' ', A.last_name) AS "ACTOR"
FROM ACTOR A, FILM_ACTOR FA, FILM F, FILM_CATEGORY FC, CATEGORY C
WHERE A.actor_id = FA.actor_id
AND FA.film_id = F.film_id
AND F.film_id = FC.film_id
AND FC.category_id = C.category_id
AND C.name = "Action"
ORDER BY A.first_name, A.last_name;
(5) List all actor names who have appeared in a film rented by the customer "MARY SMITH" in the following format exactly. Note that the result is sorted in ascending order of the first name and then last name of the actors.
+---------------------+
| ACTOR |
+---------------------+
| ADAM GRANT |
| AL GARLAND |
| ALBERT NOLTE |
| ANGELA WITHERSPOON |
| ANGELINA ASTAIRE |
| ANNE CRONYN |
...
| SYLVESTER DERN |
| VAL BOLGER |
| VIVIEN BERGEN |
| WOODY JOLIE |
| ZERO CAGE |
+---------------------+
108 rows in set (0.03 sec)
SELECT DISTINCT CONCAT(A.first_name, ' ', A.last_name) AS "ACTOR"
FROM ACTOR A, FILM_ACTOR FA, FILM F, INVENTORY I, RENTAL R, CUSTOMER C
WHERE A.actor_id = FA.actor_id
AND FA.film_id = F.film_id
AND F.film_id = I.film_id
AND I.Inventory_id = R.inventory_id
AND R.customer_id = C.customer_id
AND C.last_name = "SMITH"
AND C.first_name = "MARY"
ORDER BY A.first_name, A.last_name;
(6) List all films with 'Behind the Scenes' as a special feature in ascending order of title in the following format.
+-----------------------------+
| title |
+-----------------------------+
| ACADEMY DINOSAUR |
| AFFAIR PREJUDICE |
| ALAMO VIDEOTAPE |
| ALI FOREVER |
| ALICE FANTASIA |
| ALIEN CENTER |
...
| WRONG BEHAVIOR |
| YOUNG LANGUAGE |
| YOUTH KICK |
| ZORRO ARK |
+-----------------------------+
538 rows in set (0.01 sec)
SELECT DISTINCT title
FROM FILM
WHERE FIND_IN_SET('Behind the Scenes',special_features)
ORDER BY title;
-- or
SELECT DISTINCT title
FROM FILM
WHERE LOCATE('Behind the Scenes',special_features)
ORDER BY title;
-- Note that the following does not work.
-- This is because (SPECIAL_FEATURES) builds a set of sets as
-- SPECIAL_FEATURES is a set.
SELECT FILM.FILM_ID
FROM FILM
WHERE 'Behind the Scenes' IN (SPECIAL_FEATURES);
(7) List the actor name and the total count of rentals of all films he/she appears in the the descending order of the count in the following format.
+----------------------+------------------------+
| ACTOR | Number of rented films |
+----------------------+------------------------+
| GINA DEGENERES | 753 |
| MATTHEW CARREY | 678 |
| MARY KEITEL | 674 |
| ANGELA WITHERSPOON | 654 |
| WALTER TORN | 640 |
| HENRY BERRY | 612 |
| JAYNE NOLTE | 611 |
| VAL BOLGER | 605 |
...
| JULIA FAWCETT | 255 |
| SISSY SOBIESKI | 235 |
| JULIA ZELLWEGER | 221 |
| EMILY DEE | 216 |
+----------------------+------------------------+
200 rows in set (1.89 sec)
SELECT DISTINCT CONCAT(A.first_name, ' ', A.last_name) AS "ACTOR",
COUNT(R.rental_id) AS "Number of rented films"
FROM ACTOR A, FILM_ACTOR FA, FILM F, INVENTORY I, RENTAL R
WHERE A.actor_id = FA.actor_id
AND FA.film_id = F.film_id
AND F.film_id = I.film_id
AND I.inventory_id = R.inventory_id
GROUP BY A.actor_id, A.first_name, A.last_name
ORDER BY COUNT(R.rental_id) DESC;
(8) List all films and their special features in the following format exactly. There should be a column for one of the four predefined special features. The value of 'YES' or 'NO' should be displayed according to whether the special feature is present.
+-----------------------------+--------------+---------------+----------------+----------+
| title | Commentaries | Behind Scenes | Deleted Scenes | Trailers |
+-----------------------------+--------------+---------------+----------------+----------+
| ACADEMY DINOSAUR | NO | YES | YES | NO |
| ACE GOLDFINGER | NO | NO | YES | YES |
| ADAPTATION HOLES | NO | NO | YES | YES |
| ZORRO ARK | YES | YES | NO | YES |
+-----------------------------+--------------+---------------+----------------+----------+
1000 rows in set (0.07 sec)
SELECT DISTINCT title,
IF (FIND_IN_SET('Commentaries',special_features), 'YES', 'NO') AS 'Commentaries',
IF (FIND_IN_SET('Behind the Scenes',special_features), 'YES', 'NO') AS 'Behind Scenes',
IF (FIND_IN_SET('Deleted Scenes',special_features), 'YES', 'NO') AS 'Deleted Scenes',
IF (FIND_IN_SET('Trailers',special_features), 'YES', 'NO') AS 'Trailers'
FROM FILM
ORDER BY title;
(9) List the name of all actors who have appeared in four or more films in the category Comedy in the following format. Note the listing order.
+----------+------------------+-----------------+-------------------------------------------------------------------------------------------------+
| actor_id | name | number of films | comedy films |
+----------+------------------+-----------------+-------------------------------------------------------------------------------------------------+
| 196 | BELA WALKEN | 6 | ELEMENT FREDDY, FLINTSTONES HAPPINESS, LION UNCUT, STAGE WORLD, TRAMP OTHERS, VERTIGO NORTHWEST |
| 143 | RIVER DEAN | 5 | HATE HANDICAP, MYSTIC TRUMAN, PARTY KNOCK, PINOCCHIO SIMON, TRAMP OTHERS |
| 149 | RUSSELL TEMPLE | 5 | CLOSER BANG, CONNECTION MICROCOSMOS, CRAZY HOME, GOLD RIVER, TRAINSPOTTING STRANGERS |
| 24 | CAMERON STREEP | 4 | CONNECTION MICROCOSMOS, DOOM DANCING, FREEDOM CLEOPATRA, PURE RUNNER |
| 37 | VAL BOLGER | 4 | CAPER MOTIONS, FIREBALL PHILADELPHIA, LONELY ELEPHANT, MALLRATS UNITED |
| 58 | CHRISTIAN AKROYD | 4 | CAPER MOTIONS, DOOM DANCING, OPERATION OPERATION, SUBMARINE BED |
| 76 | ANGELINA ASTAIRE | 4 | HUSTLER PARTY, MEMENTO ZOOLANDER, MULAN MOON, SATURN NAME |
| 82 | WOODY JOLIE | 4 | CRAZY HOME, DOOM DANCING, DOWNHILL ENOUGH, WISDOM WORKER |
| 83 | BEN WILLIS | 4 | DOWNHILL ENOUGH, LONELY ELEPHANT, PERFECT GROOVE, VALLEY PACKER |
| 101 | SUSAN DAVIS | 4 | MEMENTO ZOOLANDER, SEARCHERS WAIT, VALLEY PACKER, WISDOM WORKER |
| 127 | KEVIN GARLAND | 4 | CAPER MOTIONS, DADDY PITTSBURGH, GUNFIGHT MOON, MYSTIC TRUMAN |
| 129 | DARYL CRAWFORD | 4 | DADDY PITTSBURGH, DOWNHILL ENOUGH, STRICTLY SCARFACE, VELVET TERMINATOR |
| 198 | MARY KEITEL | 4 | MALLRATS UNITED, MEMENTO ZOOLANDER, SUBMARINE BED, SWEDEN SHINING |
+----------+------------------+-----------------+-------------------------------------------------------------------------------------------------+
13 rows in set (0.01 sec)
SELECT a.actor_id AS actor_id,
CONCAT(a.first_name, ' ', a.lASt_name) AS name,
COUNT(DISTINCT f.film_id) AS `number of films`,
GROUP_CONCAT(f.title ORDER BY f.title ASc separator ', ') AS `comedy films`
FROM actor a, film_actor fa, film f, film_category fc, category c
WHERE a.actor_id = fa.actor_id
AND fa.film_id = f.film_id
AND f.film_id = fc.film_id
AND fc.category_id = c.category_id
AND c.name = 'Comedy'
GROUP BY a.actor_id, a.first_name, a.lASt_name
HAVING `number of films` >=4
ORDER BY `number of films` desc, actor_id;
(10) List the name of all actors who have appeared in films in not more than 9 categories in the following format.
+----------+---------------+----------------------+-----------------------------------------------------------------------------+
| actor_id | name | number of categories | categories |
+----------+---------------+----------------------+-----------------------------------------------------------------------------+
| 88 | KENNETH PESCI | 9 | Action, Animation, Children, Foreign, Horror, Music, Sci-Fi, Sports, Travel |
| 148 | EMILY DEE | 9 | Children, Classics, Drama, Foreign, Music, New, Sci-Fi, Sports, Travel |
| 35 | JUDY DEAN | 8 | Action, Animation, Documentary, Foreign, Horror, Sci-Fi, Sports, Travel |
| 199 | JULIA FAWCETT | 8 | Action, Animation, Classics, Comedy, Family, Foreign, New, Travel |
+----------+---------------+----------------------+-----------------------------------------------------------------------------+
4 rows in set (0.09 sec)
SELECT a.actor_id AS actor_id,
CONCAT(a.first_name, ' ', a.lASt_name) AS name,
COUNT(DISTINCT c.category_id) AS `number of categories`,
GROUP_CONCAT(DISTINCT c.name ORDER BY c.name ASC SEPARATOR ', ') AS `categories`
FROM actor a, film_actor fa, film f, film_category fc, category c
WHERE a.actor_id = fa.actor_id
AND fa.film_id = f.film_id
AND f.film_id = fc.film_id
AND fc.category_id = c.category_id
GROUP BY a.actor_id, a.first_name, a.lASt_name
HAVING `number of categories` <= 9
ORDER BY `number of categories` DESC, a.actor_id;
Classroom exercises:
(1) List all customers and their total payment on rentals in the following format. Note that it is listed in ascending order of customer's last name and then their first name. For example, "RAFAEL ABNEY" has paid $97.79.
+-----------------------+---------------+
| Customer | Total Payment |
+-----------------------+---------------+
| RAFAEL ABNEY | 97.79 |
| NATHANIEL ADAM | 133.72 |
| KATHLEEN ADAMS | 92.73 |
| DIANA ALEXANDER | 105.73 |
| GORDON ALLARD | 160.68 |
...
| BRIAN WYMAN | 52.88 |
| LUIS YANEZ | 79.80 |
| MARVIN YEE | 75.79 |
| CYNTHIA YOUNG | 111.68 |
+-----------------------+---------------+
599 rows in set (0.38 sec)
(2) List the customer id of customers rented film 1 but not film 21.
+-------------+
| customer_id |
+-------------+
| 431 |
| 518 |
| 279 |
| 411 |
| 170 |
| 161 |
| 581 |
| 359 |
| 39 |
| 541 |
| 301 |
| 344 |
| 587 |
| 597 |
| 252 |
| 554 |
| 345 |
| 406 |
| 487 |
| 92 |
| 8 |
| 34 |
+-------------+
22 rows in set (0.01 sec)
(3) List all films starring all actors appeared in the film "PHANTOM GLORY".
+---------+
| FILM_ID |
+---------+
| 108 |
| 675 |
+---------+
2 rows in set (0.03 sec)
(4) List all actors who appear in a film rented by "MARIA MILLER".
+----------------------+
| ACTOR |
+----------------------+
| JOHNNY LOLLOBRIGIDA |
| JULIANNE DENCH |
| WHOOPI HURT |
| MICHAEL BOLGER |
...
| CUBA BIRCH |
| REESE WEST |
| BOB FAWCETT |
| DAN HARRIS |
| WOODY JOLIE |
+----------------------+
116 rows in set (0.01 sec)
(5) List the customer names with the top twenty numbers of rentals in the exact format below.
+------------------+------------------+---------------+
| Customer | Number of Rental | Total Payment |
+------------------+------------------+---------------+
| ELEANOR HUNT | 46 | $216.54 |
| KARL SEAL | 45 | $221.55 |
| MARCIA DEAN | 42 | $175.58 |
| CLARA SHAW | 42 | $195.58 |
| TAMMY SANDERS | 41 | $155.59 |
| SUE PETERS | 40 | $154.60 |
| WESLEY BULL | 40 | $177.60 |
| TIM CARY | 39 | $175.61 |
| MARION SNYDER | 39 | $194.61 |
| RHONDA KENNEDY | 39 | $194.61 |
| DAISY BATES | 38 | $162.62 |
| ELIZABETH BROWN | 38 | $144.62 |
| TOMMY COLLAZO | 38 | $186.62 |
| CURTIS IRBY | 38 | $167.62 |
| BRANDON HUEY | 37 | $152.63 |
| ELSIE KELLEY | 37 | $141.63 |
| MARSHA DOUGLAS | 37 | $151.63 |
| JUNE CARROLL | 37 | $173.63 |
| ANGELA HERNANDEZ | 36 | $140.64 |
| JUSTIN NGO | 36 | $129.64 |
+------------------+------------------+---------------+
20 rows in set (0.31 sec)