CIS275 - Chapter 11: SQL zyLabs Flashcards

1
Q

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.

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

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.

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

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.

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

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.

A

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;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q
A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q
A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q
A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q
A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q
A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Standard numerical operators

A

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

col_name != 4

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Number is within range of two values (inclusive)

A

BETWEEN … AND …

col_name BETWEEN 1.5 AND 10.5

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Number is not within range of two values (inclusive)

A

NOT BETWEEN … AND …

col_name NOT BETWEEN 1 AND 10

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Number exists in a list

A

IN (…)

col_name IN (2, 4, 6)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Number does not exist in a list

A

NOT IN (…)

col_name NOT IN (1, 3, 5)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q
A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q
A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q
A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q
A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Case sensitive exact string comparison (notice the single equals)

A

=

col_name = “abc”

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Case sensitive exact string inequality comparison

A

!= or <>

col_name != “abcd”

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Case insensitive exact string comparison

A

LIKE

col_name LIKE “ABC”

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Case insensitive exact string inequality comparison

A

NOT LIKE

col_name NOT LIKE “ABCD”

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

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

A

%

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

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

A

_ (underscore)

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
String exists in a list
IN (…) col\_name IN ("A", "B", "C")
26
String does not exist in a list
NOT IN (…) col\_name NOT IN ("D", "E", "F")
27
28
29
30
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.
SELECT YEAR, COUNT(\*) FROM Movie GROUP BY Year;
32
List all directors of Pixar movies (alphabetically), without duplicates
SELECT DISTINCT Director FROM movies ORDER BY Director ASC;
33
List the first five Pixar movies sorted alphabetically
SELECT \* FROM movies ORDER BY Title LIMIT 5;
34
List the last four Pixar movies released (ordered from most recent to least)
SELECT \* FROM movies ORDER BY Year DESC LIMIT 4;
35
List the second set of five Pixar movies sorted alphabetically
SELECT \* FROM movies ORDER BY Title LIMIT 5 OFFSET 5;
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.
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;
37
List all the Canadian cities and their populations
SELECT \* FROM north\_american\_cities WHERE Country LIKE "%can%";
38
Order all the cities in the United States by their latitude from north to south
SELECT \* FROM north\_american\_cities WHERE Country LIKE "%un%" ORDER BY latitude DESC;
39
List all the cities west of Chicago, ordered from west to east
SELECT \* FROM north\_american\_cities WHERE Longitude \< '-87.629798' ORDER BY Longitude ASC;
40
List the two largest cities in Mexico (by population)
SELECT \* FROM north\_american\_cities WHERE Country LIKE "%mex%" ORDER BY Population DESC LIMIT 2;
41
List the third and fourth largest cities (by population) in the United States and their population
SELECT \* FROM north\_american\_cities WHERE Country LIKE "%un%" ORDER BY Population DESC LIMIT 2 OFFSET 2;
42
Find the domestic and international sales for each movie
SELECT title, domestic\_sales, international\_sales FROM movies JOIN boxoffice ON movies.id = boxoffice.movie\_id;
43
Show the sales numbers for each movie that did better internationally rather than domestically
SELECT title, domestic\_sales, international\_sales FROM movies JOIN boxoffice ON movies.id = boxoffice.movie\_id WHERE international\_sales \> domestic\_sales;
44
List all the movies by their ratings in descending order
SELECT title, rating FROM movies JOIN boxoffice ON movies.id = boxoffice.movie\_id ORDER BY rating DESC;
45
46
47
When joining table A to table B, a _____ simply includes rows from A regardless of whether a matching row is found in B.
LEFT JOIN
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.
RIGHT JOIN
49
a _____ simply means that rows from both tables are kept, regardless of whether a matching row exists in the other table.
50
Find the list of all buildings that have employees
SELECT DISTINCT building FROM employees LEFT JOIN Buildings ON employees.building = buildings.building\_name;
51
Find the list of all buildings and their capacity
SELECT building\_name, capacity FROM Buildings;
52
List all buildings and the distinct employee roles in each building (including empty buildings)
SELECT DISTINCT building\_name, role FROM buildings LEFT JOIN employees ON building\_name = building;
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.
SELECT RegisteredName, Height FROM Horse WHERE Height \> (SELECT AVG (Height) FROM Horse) ORDER BY Height;
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.
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 );
55
Find the name and role of all employees who have not been assigned to a building
SELECT \* FROM employees LEFT JOIN buildings ON building\_name = building WHERE building IS NULL;
56
Find the names of the buildings that hold no employees
SELECT \* FROM buildings LEFT JOIN employees ON building\_name = building WHERE Name IS NULL;
57
List all movies and their combined sales in millions of dollars
SELECT title, (domestic\_sales + international\_sales) / 1000000 AS worldwide\_sales FROM movies LEFT JOIN boxoffice ON movies.id = boxoffice.movie\_id;
58
List all movies and their ratings in percent
SELECT title, (rating \* 10) AS rating\_percent FROM movies LEFT JOIN boxoffice ON movies.id = boxoffice.movie\_id;
59
List all movies that were released on even number years
SELECT title, year FROM movies LEFT JOIN boxoffice ON movies.id = boxoffice.movie\_id WHERE year % 2 = 0;
60
A common function used to count the number of rows in the group if no column name is specified.
COUNT(\*), COUNT(column) Otherwise, count the number of rows in the group with non-NULL values in the specified column.
61
Finds the smallest numerical value in the specified column for all rows in the group.
MIN(column)
62
Finds the largest numerical value in the specified column for all rows in the group.
MAX(column)
63
Finds the average numerical value in the specified column for all rows in the group.
AVG(column)
64
Finds the sum of all numerical values in the specified column for the rows in the group.
SUM(column)
65
Find the longest time that an employee has been at the studio
SELECT MAX(years\_employed) AS most\_years\_with\_company FROM employees;
66
For each role, find the average number of years employed by employees in that role
SELECT role, AVG(years\_employed) FROM employees GROUP BY role;
67
Find the total number of employee years worked in each building
SELECT building, SUM(years\_employed) FROM employees GROUP BY building;
68
Find the number of Artists in the studio (without a HAVING clause)
SELECT role, COUNT(\*) as Number\_of\_artists FROM employees WHERE role = "Artist";
69
Find the number of Employees of each role in the studio
SELECT role, COUNT(\*) FROM employees GROUP BY role;
70
Find the total number of years employed by all Engineers
SELECT role, SUM(years\_employed) FROM employees WHERE role = "Engineer"; OR SELECT role, SUM(years\_employed) FROM employees GROUP BY role HAVING role = "Engineer";
71
1. FROM and JOINs 2. WHERE 3. GROUP BY 4. HAVING 5. SELECT 6. DISTINCT 7. ORDER BY 8. LIMIT / OFFSET
1. FROM and JOINs 2. WHERE 3. GROUP BY 4. HAVING 5. SELECT 6. DISTINCT 7. ORDER BY 8. LIMIT / OFFSET
72
Find the number of movies each director has directed
SELECT director, COUNT(title) FROM movies GROUP BY director;
73
Find the total domestic and international sales that can be attributed to each director
SELECT director, SUM(domestic\_sales + international\_sales) FROM movies LEFT JOIN boxoffice WHERE movies.id = boxoffice.movie\_id GROUP BY director;
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.
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) );
75
Add the studio's new production, Toy Story 4 to the list of movies (you can use any director)
INSERT INTO movies VALUES (4, "Toy Story 4", "John Lasseter", 2020, 100);
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
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) );
77
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.
INSERT INTO boxoffice VALUES (4 8.7 340000000 270000000);
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;
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;
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;
82
ALTER TABLE Movie ADD Producer VARCHAR(50); ALTER TABLE Movie DROP Genre; ALTER TABLE Movie CHANGE Year ReleaseYear SMALLINT; SELECT \* FROM Movie;