Data Management Applications D427 Mysql Sql Programing Intermediate Level Expected Questions With Verified Answers

DATA MANAGEMENT APPLICATIONS D427
MYSQL SQL PROGRAMING INTERMEDIATE LEVEL
EXPECTED QUESTIONS WITH VERIFIED ANSWERS

Question 1.
Which data type will store "2022-01-10 14:22:12" as a temporal value without loss of information?

ANSWER
DATETIME


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,
A new column must be added to the Movie table: Column name: Score,
Data type: decimal(3,1),
Write a SQL statement to add the Score column to the Movie table.

ANSWER
ALTER TABLE Movie
ADD Score DECIMAL(3, 1);


Question 3.
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
Select RegisteredName, Height, Birthdate
From Horse
WHERE (Height BETWEEN 15.0 AND 16.0) OR (Birthdate >= '2020-01-01');






,Question 4.
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
SELECT LS.LessonDateTime, LS.HorseID, S.FirstName, S.LastName
From LessonSchedule LS Join Student S on LS.StudentID = S.ID
Where LS.StudentID is not Null
Order BY LS.LessonDateTime ASC, LS.HorseID ASC;


Question 5.
Write the correct SQL statement to create a new database called testDB.

ANSWER
CREATE DATABASE testDB;






,Question 6.
The Movie table has the following columns: ID—integer, primary key, auto-increment, Title—variable-
length string,
Genre—variable-length string, RatingCode—variable-length string,
Year—integer,
The following data needs to be added to the Movie table:
Title Genre RatingCode Year,
Pride and Prejudice Romance G 2005,
Write a SQL statement to insert the indicated data into the Movie table.

ANSWER
INSERT INTO Movie (Title, Genre, RatingCode, Year) VALUES ('Pride and Prejudice',
'Romance', 'G', 2005);


Question 7.
The Book table has the following columns: ID—integer, primary key,
Title—variable-length string,
Genre—variable-length string,
Year—integer,
The YearSales table has the following columns: Year—integer,
TotalSales—bigint unsigned,
Releases—integer,
Write a SQL statement to designate the Year column in the Book table as a foreign key to the Year
column in the TotalSales table.
ANSWER
ALTER TABLE Book
ADD FOREIGN KEY (Year) REFERENCES YearSales (Year);


Question 8.
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
SELECT LS.LessonDateTime, S.FirstName, S.Last-Name, H.RegisteredName
FROM LessonSchedule
LS
LEFT JOIN Student S ON LS.StudentID = S.ID
LEFT JOIN Horse H ON LS.HorseID = H.ID
WHERE LS.LessonDateTime >= '2020-02-01' AND LS.LessonDateTime < '2020-02-02'
ORDER BY LS.LessonDateTime ASC, H.RegisteredName ASC;


Question 9.
Which of the following is a DML command?

ANSWER
INSERT, DELETE, UPDATE


Question 10.
Set the value of the City columns to 'Oslo', but only the ones where the Country column has the value
"Norway".

ANSWER
UPDATE Customers
SET City = 'Oslo'
WHERE Country = 'Norway';