CIS275 - Chapter 11: SQL zyLabs

Question 1

The Horse table has the following columns:

ID - integer, primary key

RegisteredName - variable-length string

Breed - variable-length string

Height - decimal number

BirthDate - date

Write a SELECT statement to select the registered name, height, and birth date for only horses that have a height between 15.0 and 16.0 (inclusive) or have a birth date on or after January 1, 2020.

Answer 1

SELECT RegisteredName, Height, BirthDate
FROM Horse
WHERE Height BETWEEN 15.0 AND 16.0
OR BirthDate >= ‘2020-01-01’;

Question 2

The Movie table has the following columns:

ID - integer, primary key

Title - variable-length string

Genre - variable-length string

RatingCode - variable-length string

Year - integer

The Rating table has the following columns:

Code - variable-length string, primary key

Description - variable-length string

Write a SELECT statement to select the Title, Year, and rating Description. Display all movies, whether or not a RatingCode is available.

Hint: Perform a LEFT JOIN on the Movie and Rating tables, matching the RatingCode and Code columns.

Answer 2

SELECT Title, Year, Description
FROM Movie
LEFT JOIN Rating
ON RatingCode = Code;

Question 3

The database has three tables for tracking horse-riding lessons:

Horse with columns:

ID - primary key

RegisteredName

Breed

Height

BirthDate

Student with columns:

ID - primary key

FirstName

LastName

Street

City

State

Zip

Phone

EmailAddress

LessonSchedule with columns:

HorseID - partial primary key, foreign key references Horse(ID)

StudentID - foreign key references Student(ID)

LessonDateTime - partial primary key

Write a SELECT statement to create a lesson schedule with the lesson date/time, horse ID, and the student’s first and last names. Order the results in ascending order by lesson date/time, then by horse ID. Unassigned lesson times (student ID is NULL) should not appear in the schedule.

Hint: Perform a join on the Student and LessonSchedule tables, matching the student IDs.

Answer 3

SELECT LessonDateTime, HorseID, FirstName, LastName
FROM LessonSchedule
JOIN Student
ON LessonSchedule.StudentID = Student.ID
ORDER BY LessonDateTime ASC, HorseID;

Question 4

The Employee table has the following columns:

ID - integer, primary key

FirstName - variable-length string

LastName - variable-length string

ManagerID - integer

Write a SELECT statement to show a list of all employees’ first names and their managers’ first names. List only employees that have a manager. Order the results by Employee first name. Use aliases to give the result columns distinctly different names, like “Employee” and “Manager”.

Hint: Use INNER JOIN.

Answer 4

SELECT A.FirstName AS ‘Employee’,
B.FirstName AS ‘Manager’
FROM Employee A
INNER JOIN Employee B
ON B.ID = A.ManagerID
ORDER BY A.FirstName ASC;

Question 5

Answer 5

Question 6

Answer 6

Question 7

Answer 7

Question 8

Answer 8

Question 9

Answer 9

Question 10

Standard numerical operators

Answer 10

=, !=, < <=, >, >=

col_name != 4

Question 11

Number is within range of two values (inclusive)

Answer 11

BETWEEN … AND …

col_name BETWEEN 1.5 AND 10.5

Question 12

Number is not within range of two values (inclusive)

Answer 12

NOT BETWEEN … AND …

col_name NOT BETWEEN 1 AND 10

Question 13

Number exists in a list

Answer 13

IN (…)

col_name IN (2, 4, 6)

Question 14

Number does not exist in a list

Answer 14

NOT IN (…)

col_name NOT IN (1, 3, 5)

Question 15

Answer 15

Question 16

Answer 16

Question 17

Answer 17

Question 18

Answer 18

Question 19

Case sensitive exact string comparison (notice the single equals)

Answer 19

=

col_name = “abc”

Question 20

Case sensitive exact string inequality comparison

Answer 20

!= or <>

col_name != “abcd”

Question 21

Case insensitive exact string comparison

Answer 21

LIKE

col_name LIKE “ABC”

Question 22

Case insensitive exact string inequality comparison

Answer 22

NOT LIKE

col_name NOT LIKE “ABCD”

Question 23

Used anywhere in a string to match a sequence of zero or more characters (only with LIKE or NOT LIKE)

Answer 23

%

col_name LIKE “%AT%”
(matches “AT”, “ATTIC”, “CAT” or even “BATS”)

Question 24

Used anywhere in a string to match a single character (only with LIKE or NOT LIKE)

Answer 24

_ (underscore)

col_name LIKE “AN_”
(matches “AND”, but not “AN”)

Question 25

String exists in a list

Answer 25

IN (…)

col_name IN (“A”, “B”, “C”)

Question 26

String does not exist in a list

Answer 26

NOT IN (…)

col_name NOT IN (“D”, “E”, “F”)

Question 27

Answer 27

Question 28

Answer 28

Question 29

Answer 29

Question 30

Answer 30

Question 31

The Movie table has the following columns:

ID - integer, primary key

Title - variable-length string

Genre - variable-length string

RatingCode - variable-length string

Year - integer

Write a SELECT statement to select the year and the total number of movies for that year.

Hint: Use the COUNT() function and GROUP BY clause.

Answer 31

SELECT YEAR, COUNT(*)
FROM Movie
GROUP BY Year;

Question 32

List all directors of Pixar movies (alphabetically), without duplicates

Answer 32

SELECT DISTINCT Director
FROM movies
ORDER BY Director ASC;

Question 33

List the first five Pixar movies sorted alphabetically

Answer 33

SELECT *
FROM movies
ORDER BY Title
LIMIT 5;

Question 34

List the last four Pixar movies released (ordered from most recent to least)

Answer 34

SELECT *
FROM movies
ORDER BY Year DESC
LIMIT 4;

Question 35

List the second set of five Pixar movies sorted alphabetically

Answer 35

SELECT *
FROM movies
ORDER BY Title
LIMIT 5 OFFSET 5;

Question 36

The database has three tables for tracking horse-riding lessons:

Horse with columns:

ID - primary key

RegisteredName

Breed

Height

BirthDate

Student with columns:

ID - primary key

FirstName

LastName

Street

City

State

Zip

Phone

EmailAddress

LessonSchedule with columns:

HorseID - partial primary key, foreign key references Horse(ID)

StudentID - foreign key references Student(ID)

LessonDateTime - partial primary key

Write a SELECT statement to create a lesson schedule for Feb 1, 2020 with the lesson date/time, student’s first and last names, and the horse’s registered name. Order the results in ascending order by lesson date/time, then by the horse’s registered name. Make sure unassigned lesson times (student ID is NULL) appear in the results.

Hint: Perform a join on the LessonSchedule, Student, and Horse tables, matching the student IDs and horse IDs.

Answer 36

SELECT LessonDateTime, FirstName, LastName, RegisteredName
FROM LessonSchedule
LEFT JOIN Student
ON Student.ID = StudentID
INNER JOIN Horse
ON Horse.ID = HorseID
WHERE LessonDateTime LIKE “%2020-02-01%”
ORDER BY LessonDateTime ASC, RegisteredName;

Question 37

List all the Canadian cities and their populations

Answer 37

SELECT *

FROM north_american_cities

WHERE Country LIKE “%can%”;

Question 38

Order all the cities in the United States by their latitude from north to south

Answer 38

SELECT *
FROM north_american_cities
WHERE Country LIKE “%un%”
ORDER BY latitude DESC;

Question 39

List all the cities west of Chicago, ordered from west to east

Answer 39

SELECT *

FROM north_american_cities

WHERE Longitude < ‘-87.629798’

ORDER BY Longitude ASC;

Question 40

List the two largest cities in Mexico (by population)

Answer 40

SELECT *

FROM north_american_cities

WHERE Country LIKE “%mex%”

ORDER BY Population DESC LIMIT 2;

Question 41

List the third and fourth largest cities (by population) in the United States and their population

Answer 41

SELECT * FROM north_american_cities

WHERE Country LIKE “%un%”

ORDER BY Population DESC LIMIT 2 OFFSET 2;

Question 42

Find the domestic and international sales for each movie

Answer 42

SELECT title, domestic_sales, international_sales
FROM movies
JOIN boxoffice
ON movies.id = boxoffice.movie_id;

Question 43

Show the sales numbers for each movie that did better internationally rather than domestically

Answer 43

SELECT title, domestic_sales, international_sales
FROM movies
JOIN boxoffice
ON movies.id = boxoffice.movie_id
WHERE international_sales > domestic_sales;

Question 44

List all the movies by their ratings in descending order

Answer 44

SELECT title, rating
FROM movies
JOIN boxoffice
ON movies.id = boxoffice.movie_id
ORDER BY rating DESC;

Question 47

When joining table A to table B, a _____ simply includes rows from A regardless of whether a matching row is found in B.

Answer 47

LEFT JOIN

Question 48

When joining table A to table B, a RIGHT JOIN simply includes rows from B regardless of whether a matching row is found in A.

Answer 48

RIGHT JOIN

Question 49

a _____ simply means that rows from both tables are kept, regardless of whether a matching row exists in the other table.

Question 50

Find the list of all buildings that have employees

Answer 50

SELECT DISTINCT building

FROM employees

LEFT JOIN Buildings

ON employees.building = buildings.building_name;

Question 51

Find the list of all buildings and their capacity

Answer 51

SELECT building_name, capacity FROM Buildings;

Question 52

List all buildings and the distinct employee roles in each building (including empty buildings)

Answer 52

SELECT DISTINCT building_name, role
FROM buildings
LEFT JOIN employees
ON building_name = building;

Question 53

The Horse table has the following columns:

ID - integer, primary key

RegisteredName - variable-length string

Breed - variable-length string

Height - decimal number

BirthDate - date

Write a SELECT statement to select the registered name and height for only horses that have an above average height. Order the results by height.

Hint: Use a subquery to find the average height.

Answer 53

SELECT RegisteredName, Height
FROM Horse
WHERE Height >
(SELECT AVG (Height)
FROM Horse)
ORDER BY Height;

Question 54

Two tables are created:

1. Horse with columns:
   - ID - integer, primary key
   - RegisteredName - variable-length string
2. Student with columns:
   - ID - integer, primary key
   - FirstName - variable-length string
   - LastName - variable-length string

Create the LessonSchedule table with columns:

• HorseID - integer with range 0 to 65 thousand, not NULL, partial primary key, foreign key references Horse(ID)
• StudentID - integer with range 0 to 65 thousand, foreign key references Student(ID)
• LessonDateTime - date/time, not NULL, partial primary key

If a row is deleted from Horse, the rows with the same horse ID should be deleted from LessonSchedule automatically.

If a row is deleted from Student, the same student IDs should be set to NULL in LessonSchedule automatically.

Answer 54

CREATE TABLE Horse (
ID SMALLINT UNSIGNED AUTO_INCREMENT,
RegisteredName VARCHAR(15),
PRIMARY KEY (ID)
);

CREATE TABLE Student (
ID SMALLINT UNSIGNED AUTO_INCREMENT,
FirstName VARCHAR(20),
LastName VARCHAR(30),
PRIMARY KEY (ID)
);

CREATE TABLE LessonSchedule (
HorseID SMALLINT UNSIGNED NOT NULL,
StudentID SMALLINT UNSIGNED,
LessonDateTime DATETIME NOT NULL,
PRIMARY KEY (HorseID, LessonDateTime),
FOREIGN KEY (HorseID) REFERENCES Horse(ID)
ON DELETE CASCADE,
FOREIGN KEY (StudentID) REFERENCES Student(ID)
ON DELETE SET NULL
);

Question 55

Find the name and role of all employees who have not been assigned to a building

Answer 55

SELECT *
FROM employees
LEFT JOIN buildings
ON building_name = building
WHERE building IS NULL;

Question 56

Find the names of the buildings that hold no employees

Answer 56

SELECT *
FROM buildings
LEFT JOIN employees
ON building_name = building
WHERE Name IS NULL;

Question 57

List all movies and their combined sales in millions of dollars

Answer 57

SELECT title, (domestic_sales + international_sales) / 1000000 AS worldwide_sales
FROM movies
LEFT JOIN boxoffice
ON movies.id = boxoffice.movie_id;

Question 58

List all movies and their ratings in percent

Answer 58

SELECT title, (rating * 10) AS rating_percent
FROM movies
LEFT JOIN boxoffice
ON movies.id = boxoffice.movie_id;

Question 59

List all movies that were released on even number years

Answer 59

SELECT title, year
FROM movies
LEFT JOIN boxoffice
ON movies.id = boxoffice.movie_id
WHERE year % 2 = 0;

Question 60

A common function used to count the number of rows in the group if no column name is specified.

Answer 60

COUNT(*),

COUNT(column)

Otherwise, count the number of rows in the group with non-NULL values in the specified column.

Question 61

Finds the smallest numerical value in the specified column for all rows in the group.

Answer 61

MIN(column)

Question 62

Finds the largest numerical value in the specified column for all rows in the group.

Answer 62

MAX(column)

Question 63

Finds the average numerical value in the specified column for all rows in the group.

Answer 63

AVG(column)

Question 64

Finds the sum of all numerical values in the specified column for the rows in the group.

Answer 64

SUM(column)

Question 65

Find the longest time that an employee has been at the studio

Answer 65

SELECT MAX(years_employed) AS most_years_with_company FROM employees;

Question 66

For each role, find the average number of years employed by employees in that role

Answer 66

SELECT role, AVG(years_employed)

FROM employees

GROUP BY role;

Question 67

Find the total number of employee years worked in each building

Answer 67

SELECT building, SUM(years_employed)

FROM employees

GROUP BY building;

Question 68

Find the number of Artists in the studio (without a HAVING clause)

Answer 68

SELECT role, COUNT(*) as Number_of_artists
FROM employees
WHERE role = “Artist”;

Question 69

Find the number of Employees of each role in the studio

Answer 69

SELECT role, COUNT(*)
FROM employees
GROUP BY role;

Question 70

Find the total number of years employed by all Engineers

Answer 70

SELECT role, SUM(years_employed)
FROM employees
WHERE role = “Engineer”;

OR

SELECT role, SUM(years_employed)
FROM employees
GROUP BY role
HAVING role = “Engineer”;

Question 71

1. FROM and JOINs
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. LIMIT / OFFSET

Answer 71

1. FROM and JOINs
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. LIMIT / OFFSET

Question 72

Find the number of movies each director has directed

Answer 72

SELECT director, COUNT(title)

FROM movies

GROUP BY director;

Question 73

Find the total domestic and international sales that can be attributed to each director

Answer 73

SELECT director, SUM(domestic_sales + international_sales)
FROM movies LEFT JOIN boxoffice
WHERE movies.id = boxoffice.movie_id
GROUP BY director;

Question 74

Create a Horse table with the following columns, data types, and constraints:

ID - integer with range 0 to 65 thousand, auto increment, primary key

RegisteredName - variable-length string with max 15 chars, not NULL

Breed - variable-length string with max 20 chars, must be one of the following: Egyptian Arab, Holsteiner, Quarter Horse, Paint, Saddlebred

Height - number with 3 significant digits and 1 decimal place, must be ≥ 10.0 and ≤ 20.0

BirthDate - date, must be ≥ Jan 1, 2015

Note: Not all constraints can be tested due to current limitations of MySQL.

Answer 74

CREATE TABLE Horse (
ID SMALLINT UNSIGNED AUTO_INCREMENT,
RegisteredName VARCHAR(15) NOT NULL,
Breed VARCHAR(20) CHECK (Breed IN (‘Egyptian Arab’, ‘Quarter Horse’, ‘Holsteiner’, ‘Paint’, ‘Saddlebred’)),
Height DECIMAL(3,1) CHECK (Height >= 10.0 AND Height <= 20.0),
BirthDate DATE CHECK (Birthdate >= ‘2015-01-01’),
PRIMARY KEY (ID)
);

Question 75

Add the studio’s new production, Toy Story 4 to the list of movies (you can use any director)

Answer 75

INSERT INTO movies

VALUES (4, “Toy Story 4”, “John Lasseter”, 2020, 100);

Question 76

Create a Student table with the following column names, data types, and constraints:

ID - integer with range 0 to 65 thousand, auto increment, primary key

FirstName - variable-length string with max 20 chars, not NULL

LastName - variable-length string with max 30 chars, not NULL

Street - variable-length string with max 50 chars, not NULL

City - variable-length string with max 20 chars, not NULL

State - fixed-length string of 2 chars, not NULL, default “TX”

Zip - integer with range 0 to 16 million, not NULL

Phone - fixed-length string of 10 chars, not NULL

Email - variable-length string with max 30 chars, must be unique

Answer 76

CREATE TABLE Student (
ID SMALLINT UNSIGNED AUTO_INCREMENT,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(30) NOT NULL,
Street VARCHAR(50) NOT NULL,
City VARCHAR(20) NOT NULL,
State CHAR(2) NOT NULL DEFAULT “TX”,
Zip MEDIUMINT UNSIGNED NOT NULL,
Phone CHAR(10) NOT NULL,
Email VARCHAR(30) UNIQUE,
PRIMARY KEY (ID)
);

Question 78

Toy Story 4 has been released to critical acclaim! It had a rating of 8.7, and made 340 million domestically and 270 million internationally. Add the record to the BoxOffice table.

Answer 78

INSERT INTO boxoffice

VALUES (4 8.7 340000000 270000000);

Question 79

Answer 79

INSERT INTO Horse
VALUES (1, “Babe”, “Quarter Horse”, 15.3, “2015-02-10”),
(2, “Independence”, “Holsteiner”, 16.0, “2011-03-13”),
(3, “Ellie”, “Saddlebred”, 15.0, “2016-12-22”),
(4, “NULL”, “Egyptian Arab”, 14.9, “2019-10-12”);
SELECT *
FROM Horse;

Question 80

Answer 80

UPDATE Horse
SET Height = 15.6
WHERE ID = 2;

UPDATE Horse
SET RegisteredName = “Lady Luck”, BirthDate = ‘2015-05-01’
WHERE ID = 4;

UPDATE Horse
SET Breed = “NULL”
WHERE BirthDate >= ‘2016-12-22’;

SELECT *
FROM Horse
ORDER BY ID;

Question 81

Answer 81

DELETE FROM Horse
WHERE ID = 5;

DELETE FROM Horse
WHERE Breed = “Holsteiner”;

DELETE FROM Horse
WHERE Breed = “Paint”;

DELETE FROM Horse
WHERE BirthDate < ‘2013-03-13’;

SELECT *
FROM Horse
ORDER BY ID;

Question 82

Answer 82

ALTER TABLE Movie
ADD Producer VARCHAR(50);

ALTER TABLE Movie
DROP Genre;

ALTER TABLE Movie
CHANGE Year ReleaseYear SMALLINT;

SELECT *
FROM Movie;