D427 - Data Management Applications UPDATED ACTUAL Questions and CORRECT Answers

D427 - Data Management Applications UPDATED ACTUAL Questions and
CORRECT Answers

Lab 2.9 The Movie table has the following columns:
ID - integer, primary key
Title - variable-length string
SELECT Year, COUNT(*) AS TNMYear Genre - variable-length string
FROM Movie RatingCode - variable-length string
GROUP BY Year 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.
2.10 Lab - The Movie table has the following columns:
ID - integer, primary key
Title - variable-length string
Genre - variable-length string
RatingCode - variable-length string
SELECT mov.Title, mov.Year, rat.Description; Year - integer
FROM movie AS mov The Rating table has the following columns:
LEFT JOIN Rating AS rat Code - variable-length string, primary key
ON mov.RatingCode=rat.Code 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.

2.11 Lab - Select employees and managers with inner
join
SELECT E.FirstName AS Employee, D.FirstName AS Man- The Employee table has the following columns:
ager ID - integer, primary key
FROM Employee AS L
FirstName - variable-length string
LastName - variable-length string

,ManagerID - integer






, Write a SELECT statement to show a list of all employ-
ees' first names and their managers' first names. List
INNER JOIN Employee AS D
only employees that have a manager. Order the results
ON E.ManagerID=D.ID
by Employee first name. Use aliases to give the result
WHERE E.ManagerID IS NOT NULL
columns distinctly different names, like "Employee" and
ORDER BY E.FirstName
"Manager".

Hint: Join the Employee table to itself using INNER JOIN.
2.12 Lab - Select lesson schedule with inner join
The database has three tables for tracking horse-riding
lessons:
1. Horse with columns:
o ID - primary key
o RegisteredName
o Breed
o Height
SELECT LessonDateTime, HorseID, FirstName, LastName o BirthDate
FROM LessonSchedule 2. Student with columns:
LEFT JOIN Student ON LessonSchedule.StudentID=Stu- o ID - primary key
dent.ID o FirstName
LEFT JOIN Horse ON LessonSchedule.HorseID=HorseID o LastName
WHERE Student.ID IS NOT NULL o Street
ORDER BY LessonDateTime, ASC, HorseID ASC o City
o State
o Zip
o Phone
o EmailAddress
3. LessonSchedule with columns:
o HorseID - partial primary key, foreign key references
Horse(ID)
o StudentID - foreign key references Student(ID)


, o 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.

2.13 Lab - Select lesson schedule with multiple joins
1. Horse with columns:
o ID - primary key
o RegisteredName
o Breed
o Height
o BirthDate
2. Student with columns:
SELECT LessonDateTime, FirstName, LastName, Regis-
o ID - primary key
teredName
o FirstName
FROM LessonSchedule
o LastName
LEFT JOIN Student ON LessonSchedule.StudentID = Stu-
o Street
dent.ID
o City
LEFT JOIN Horse ON LessonSchedule.HorseID=Horse.ID
o State
WHERE DATE(LessonDateTime) = '202-02-01'
o Zip
ORDER BY LessonDateTime ASC, RegisteredName ASC
o Phone
o EmailAddress
3. LessonSchedule with columns:
o HorseID - partial primary key, foreign key references
Horse(ID)
o StudentID - foreign key references Student(ID)
o LessonDateTime - partial primary key