The following relationships are used to keep an after-school calendar of events for students in a school. An example of this database is given below. The database can be much larger and can contain much more information than these given features, but just use the following data sets for this question. School Events database Student (student_ID, name, surname, address, telephone) Event (event_name, capacity, equipment, fee, instructorID) Location (place_name, equipment) Calendar (event_name, day, start_time, end_time, place) Registration (student_ID, event_name) Instructor (instructor_ID, name, surname, address, telephone, salary) Student studentID - name surname - address - telephone 123 - Sarri Muro - juraha street - 466 77 88 345 - Larkin Kimura - quantitor street - 222 33 44 Activity activity_name - capacity - equipment - fee - instructorID Music - 3 - piano - 100 - 111 Ballet - 15 - wood - floor 50 - 333 Location place_name - equipment Hall1 - piano Hall2 - mat Room1 - wooden floor calendar event_name day - start_time - end_time place Music - Wednesday - 15.30 - 16.30 - Hall1 Ballet - Monday - 16.30 - 18.00 - Room1 Record studentID - event_name 123 - Music 345 - Ballet 123 - Ballet Instructor instructor ID - name surname - address - phone - salary 111 - John Capuno - news street - 463 37 88 - 3000 333 - Laura Elso - old town street- 222 11 44 - 5000 SQL Query Questions: Q1: List the name, surname and telephone number of the students registered in the events of the instructor named John. Q2: Weekday maintenance work will be done for wooden floors. In order to inform the students, create a query that shows the activities and times planned to be held in wooden-floored places on weekdays and the names and phone numbers of the students taking these lessons. Q3: For the school, find the money earned from each activity right now. Q4: Find the busiest event, day and number of enrolled students for each day. Q5: The school will be closed on days when there are no events. Find out if there are any days in the week with no scheduled events. Try to find a way to expand the database to easily perform this query (can a new field or table be added?)
Write your SQL statements required to create a
The following relationships are used to keep an after-school calendar of events for students in a school. An example of this database is given below. The database can be much larger and can contain much more information than these given features, but just use the following data sets for this question.
School Events database
Student (student_ID, name, surname, address, telephone)
Event (event_name, capacity, equipment, fee, instructorID)
Location (place_name, equipment)
Calendar (event_name, day, start_time, end_time, place)
Registration (student_ID, event_name)
Instructor (instructor_ID, name, surname, address, telephone, salary)
Student
studentID - name surname - address - telephone
123 - Sarri Muro - juraha street - 466 77 88
345 - Larkin Kimura - quantitor street - 222 33 44
Activity
activity_name - capacity - equipment - fee - instructorID
Music - 3 - piano - 100 - 111
Ballet - 15 - wood - floor 50 - 333
Location
place_name - equipment
Hall1 - piano
Hall2 - mat
Room1 - wooden floor
calendar
event_name day - start_time - end_time place
Music - Wednesday - 15.30 - 16.30 - Hall1
Ballet - Monday - 16.30 - 18.00 - Room1
Record
studentID - event_name
123 - Music
345 - Ballet
123 - Ballet
Instructor
instructor ID - name surname - address - phone - salary
111 - John Capuno - news street - 463 37 88 - 3000
333 - Laura Elso - old town street- 222 11 44 - 5000
SQL Query Questions:
Q1: List the name, surname and telephone number of the students registered in the events of the instructor named John.
Q2: Weekday maintenance work will be done for wooden floors. In order to inform the students, create a query that shows the activities and times planned to be held in wooden-floored places on weekdays and the names and phone numbers of the students taking these lessons.
Q3: For the school, find the money earned from each activity right now.
Q4: Find the busiest event, day and number of enrolled students for each day.
Q5: The school will be closed on days when there are no events. Find out if there are any days in the week with no scheduled events. Try to find a way to expand the database to easily perform this query (can a new field or table be added?)
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 8 images
