Unit 08 · Practice

Practice

The boxes on this page run on a MySQL server of your own, separate from the one in this unit's lessons. It starts with no databases. Each part of the questions works in a database of its own, which the part's first question has you create.

Every box on the page runs on the same server, and whatever you run stays, even after you reload the page. There's no reset button. If something goes wrong, fix it with SQL, the way you would in Workbench: DELETE rows that shouldn't be there, UPDATE the ones that are wrong, or DROP a table you need to make again.

The Your databases panel lists your databases, and the one in use is open to show its tables and their columns. Clicking another database uses it, the same as running USE. To see the rows in a table, run a SELECT in any box.

Run runs everything in the box. If you select part of the script first, the button changes to Run selection and runs only that part. Under the box, each statement gets a line saying what happened to it. A script stops at its first error.

Part 1 · The shelter database
1

You keep the records for a small animal shelter. Create a database named shelter, and USE it. Then create a table in it named Animals, with these columns:

Column Holds
AnimalID a whole number
Name text, up to 50 characters. Every animal has one.
Species text, up to 30 characters. Every animal has one.
Age a whole number
⌘/Ctrl + Enter
2

Put these three animals into Animals.

AnimalID Name Species Age
1 Biscuit Dog 3
2 Pepper Cat 5
3 Juniper Rabbit 1
⌘/Ctrl + Enter
Part 2 · The dining database
3

A restaurant booking service keeps its records in a database named dining. Create it, and USE it. Then create a table in it named Guests, for rows like these:

ID FirstName LastName Phone
1 Nathaniel Mandrake (111) 222 3333
2 David Martinez (333) 444 5555
3 Valentine Wiggin (666) 777 8888

Choose the data types yourself, and use constraints for these rules from the booking service:

  • Guests are looked up by their ID, and no two guests share one.
  • Every guest has a first name and a last name.
  • A guest doesn't have to give a phone number.
⌘/Ctrl + Enter
4

Put these three guests into Guests.

ID FirstName LastName Phone
1 Nathaniel Mandrake (111) 222 3333
2 David Martinez (333) 444 5555
3 Valentine Wiggin (666) 777 8888
⌘/Ctrl + Enter
5

The booking service also keeps its restaurants, and the reservations guests make at them. Create a table named Restaurants and a table named Reservations in dining, for rows like these:

Restaurants

ID Name
1 Bob's Gourmet Macaroni
2 Cheese Louise Dinner

Reservations

ID GuestID RestaurantID ReservationDateTime
1 3 2 2024-01-02 19:00:00
2 1 1 2024-01-03 09:30:00

Choose the data types yourself, and use constraints for these rules from the booking service:

  • Restaurants and reservations are each looked up by their ID, and no two share one.
  • Every restaurant has a name.
  • A reservation is for one guest at one restaurant. The database has to refuse a reservation for a guest or a restaurant it doesn't have.
⌘/Ctrl + Enter
6

Put these rows into Restaurants and Reservations.

Restaurants

ID Name
1 Bob's Gourmet Macaroni
2 Cheese Louise Dinner

Reservations

ID GuestID RestaurantID ReservationDateTime
1 3 2 2024-01-02 19:00:00
2 1 1 2024-01-03 09:30:00
3 2 1 2024-01-03 10:00:00
4 3 2 2024-01-04 17:30:00
⌘/Ctrl + Enter

To put an apostrophe inside text, type it twice: 'Bob''s Gourmet Macaroni'.

7

A coworker suggests making Guests.Phone UNIQUE, so that no two guests can have the same phone number. Which of these would that stop?

Part 3 · The dealership database

A car dealership keeps its customers, and the cars it sells, in a database of its own. Questions 9 to 14 are things that happen at the dealership once that database is set up. Handle each one without CREATE or INSERT, by changing the tables and rows that are already there.

8

The script below creates the dealership's two tables and puts in their rows. Add two lines at the top that create a database named dealership and start using it, then run the script.

⌘/Ctrl + Enter
9

The dealership has decided to start tracking the color of each car. Name the new column Color.

⌘/Ctrl + Enter
10

There was a mistake when processing a recent order. The Honda Civic was actually sold to John Smith.

⌘/Ctrl + Enter
11

Hannah Brooks has decided to return her Tesla Model 3. The dealership has agreed to take it back.

⌘/Ctrl + Enter
12

Aisha Patel has changed her email address. Her new email is aishap@gmail.com.

⌘/Ctrl + Enter
13

Due to a special promotion, the dealership has decided to reduce the price of the Ford Mustang by $2000.

⌘/Ctrl + Enter
14

Kwame has decided to close their account. Data privacy laws require the company to permanently remove their information.

⌘/Ctrl + Enter
15

Once question 11 is correct, Hannah Brooks asks the dealership to close her account too. Run the DELETE in the box. Why did it fail?

⌘/Ctrl + Enter
Submit

When you're done, download your submission file and upload it to this unit's practice assignment in Canvas. It carries a summary of your progress and the record of every attempt you made on this page. You can submit as many times as you like; the last one counts.

Attempts are saved in this browser. To keep going on another computer, download your submission here and resume from it there.