Datebase Design a) Write an SQL query to determine the total number of hours and the total number of lessons Tutor 106 taught in June and July 2018.b) Write an SQL query to list the Read scores of students who were ever taught by tutors whose status is Dropped.c) List all active students in June by name. (Make up names and other data if you are actually building a prototype database.) Include the number of hours students received tutoring and how many lessons they completed. # Educational Dataset Overview## TUTOR TableThe "TUTOR" table consists of three columns: TutorID, CertDate, and Status. Each row represents a tutor with their unique identification number, certification date, and current status.| TutorID | CertDate | Status ||---------|-----------|-----------|| 100 | 1/05/2018 | Active || 101 | 1/05/2018 | Temp Stop || 102 | 1/05/2018 | Dropped || 103 | 5/22/2018 | Active || 105 | 5/22/2018 | Temp Stop || 106 | 5/22/2018 | Active |## STUDENT TableThe "STUDENT" table contains three columns: StudentID, Group, and Read. It categorizes each student by their identification number, group number, and reading level.| StudentID | Group | Read ||-----------|-------|------|| 3000 | 3 | 2.3 || 3001 | 2 | 5.6 || 3002 | 3 | 1.3 || 3003 | 1 | 3.3 || 3004 | 2 | 2.7 || 3005 | 3 | 4.8 || 3006 | 3 | 7.8 || 3007 | 4 | 1.5 |## MATCH HISTORY TableThis table provides the history of matches between tutors and students. It includes the MatchID, TutorID, StudentID, StartDate, and EndDate for each session.| MatchID | TutorID | StudentID | StartDate | EndDate ||---------|---------|-----------|------------|------------|| 1 | 100 | 3000 | 1/10/2018 | || 2 | 101 | 3001 | 1/15/2018 | 5/15/2018 || 3 | 102 | 3002 | 3/01/2018 | 3/01/201

Solution : (a) Query :select SUM(Hours) as TotalHrs,sum(Lessons) as Totallsn from TUTOR_REPORT where…

Answered: a) Write an SQL query to determine the…

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Concept explainers

Question

Datebase Design

a) Write an SQL query to determine the total number of hours and the total number of lessons Tutor 106 taught in June and July 2018.

b) Write an SQL query to list the Read scores of students who were ever taught by tutors whose status is Dropped.

c) List all active students in June by name. (Make up names and other data if you are actually building a prototype database.) Include the number of hours students received tutoring and how many lessons they completed.

# Educational Dataset Overview

## TUTOR Table
The "TUTOR" table consists of three columns: TutorID, CertDate, and Status. Each row represents a tutor with their unique identification number, certification date, and current status.

| TutorID | CertDate | Status |
|---------|-----------|-----------|
| 100 | 1/05/2018 | Active |
| 101 | 1/05/2018 | Temp Stop |
| 102 | 1/05/2018 | Dropped |
| 103 | 5/22/2018 | Active |
| 105 | 5/22/2018 | Temp Stop |
| 106 | 5/22/2018 | Active |

## STUDENT Table
The "STUDENT" table contains three columns: StudentID, Group, and Read. It categorizes each student by their identification number, group number, and reading level.

| StudentID | Group | Read |
|-----------|-------|------|
| 3000 | 3 | 2.3 |
| 3001 | 2 | 5.6 |
| 3002 | 3 | 1.3 |
| 3003 | 1 | 3.3 |
| 3004 | 2 | 2.7 |
| 3005 | 3 | 4.8 |
| 3006 | 3 | 7.8 |
| 3007 | 4 | 1.5 |

## MATCH HISTORY Table
This table provides the history of matches between tutors and students. It includes the MatchID, TutorID, StudentID, StartDate, and EndDate for each session.

| MatchID | TutorID | StudentID | StartDate | EndDate |
|---------|---------|-----------|------------|------------|
| 1 | 100 | 3000 | 1/10/2018 | |
| 2 | 101 | 3001 | 1/15/2018 | 5/15/2018 |
| 3 | 102 | 3002 | 3/01/2018 | 3/01/201

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 3 steps with 3 images

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions