ZY Labs

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 and height for only horses that have an above average height. Order the results by height (ascending).

Hint: Use a subquery to find the average height.

Answer 1

Question 2

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

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.

Answer 2

Question 3

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

Question 4

The Movie table has the following columns:

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

Answer 4

Question 5

San Francisco, CA 94110
USA

How many attributes are present in the address fragment?

Answer 5

1. City: San Francisco
2. State and ZIP Code: CA 94110
3. Country: USA

Question 6

Why wouldn’t DATE be the correct data type to store ‘2022-01-10 14:22:12’ without losing information?

Answer 6

This is incorrect because the DATE data type only stores the date part, not the time.

Question 7

Why is DATETIME the correct data type to store ‘2022-01-10 14:22:12’ without losing information?

Answer 7

This is correct because the DATETIME data type stores both date and time components, preserving all the information.

Question 8

Why wouldn’t DECIMAL be the correct data type to store ‘2022-01-10 14:22:12’ without losing information?

Answer 8

This is incorrect because DECIMAL is a numerical data type used for precise fixed-point or floating-point number storage, not temporal values.

Question 9

Why wouldn’t BIGINT be the correct data type to store ‘2022-01-10 14:22:12’ without losing information?

Answer 9

This is incorrect because BIGINT is a large integer data type and does not store date or time information.

Question 10

What are the common DDL commands?

Answer 10

CREATE, DROP, ALTER, TRUNCATE

Question 11

What are the common DML commands?

Answer 11

INSERT, UPDATE, DELETE

Question 12

What are the common DCL commands?

Answer 12

GRANT, REVOKE

Question 13

What are the common TCL commands?

Answer 13

COMMIT, ROLLBACK, SAVEPOINT

Question 14

What is the common DQL command?

Answer 14

SELECT

Question 15

Why wouldn’t ‘Weight’ be considered the primary key for the Package table?

Answer 15

This is incorrect because weight is not unique for each package; multiple packages can have the same weight.

Question 16

Why wouldn’t ‘Description’ be considered the primary key for the Package table?

Answer 16

This is incorrect because a description is an optional field and may not be unique or present for every package.

Question 17

Why wouldn’t ‘LastChangedDate’ be considered the primary key for the Package table?

Answer 17

This is incorrect because the date a package was last changed is not unique to each package and could be the same for several packages.

Question 18

Why is ‘TrackingNumber’ considered the primary key for the Package table?

Answer 18

This is correct because tracking numbers are unique identifiers assigned to each package, ensuring that each record in the table is uniquely identifiable.

Question 19

Where would you start first.

Write a SQL statement to create the Member table.

Answer 19

Question 20

Where would you start first.

Answer 20

1. Unique RatingCode values: This means you need to group the movies by their RatingCode and avoid duplicates.
2. Count the number of movies: For each RatingCode, count how many movies have that rating.
3. Sort by RatingCode: Arrange the results in ascending order (A–Z) based on the RatingCode.
4. Column order: The output must have RatingCode first, followed by RatingCodeCount.

Question 21

Where would you start first.

• 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 21

To add a new column to an existing table in SQL, you can use the ALTER TABLE statement followed by an ADD clause. You need to specify the table name, the new column name, and its data type.

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

Question 22

Display the following code result

1. A new column must be added to the Movie table:
2. Write a SQL statement to add the Score column to the Movie table

Answer 22

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

Question 23

Where would you start first.

1. The database contains a table named Movie.
2. Write a SQL query to return all data from the Movie table without directly referencing any column names.

Answer 23

• The SQL query SELECT * FROM Movie; accomplishes this by using the * wildcard to select all columns implicitly.
• This approach is efficient and adheres to the constraint of not directly referencing any column names.

Question 24

Display the following code result

Write a SQL query to display all Title values in alphabetical order A–Z.

Answer 24

Question 25

# List the objective and SQL structure Update the Year value to be 2022 for all movies with a Year value of 2020. ## Footnote How do you specify the column to update and the new value in an `UPDATE` clause?

Answer 25

1. Objective: Update the Year column for all movies where the Year is currently 2020 and change it to 2022. 2. SQL Statement Required: You need to write an `UPDATE` statement in SQL to modify the Year column for specific rows in the Movie table. ## Footnote The `SET` clause specifies the column to update and the new value.

Question 26

Display the basic structure for the [SQL update](https://app.milanote.com/1UeQdo1ina668q?p=N4oXWlErVzz) statement:

Answer 26

``` UPDATE table_name SET column_name = new_value WHERE condition; ```

Question 27

# Display the following code result Write a SQL statement to create the Member table.

Answer 27

Question 28

# List the objectives to do the following: Write a SQL statement to delete the row with the ID value of 3 from the Movie table. ## Footnote If we were to omit the `WHERE` clause would could possibly be the result?

Answer 28

1. Since `ID` is the primary key, this ensures that only one row will be deleted (the one with ID = 3). 2. The `DELETE` statement is used to remove rows from a table, and the `WHERE` clause is used to specify which row(s) to delete. ## Footnote If this condition is omitted, all rows in the table would be deleted