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. TUTOR (TutorID, CertDate, Status)MATCH HISTORY (MatchID, TutorID, StudentID,StartDate, EndDate)TutorIDCertDateStatus1001/05/2018 ActiveMatchIDTutorIDStudentIDStartDateEndDate1/05/2018 Temp Stop1013000 1/10/201811001021/05/2018 Dropped1035/22/2018 Active3001 1/15/20183002 2/10/20183003 5/28/20181015/15/20181045/22/2018 Active33/01/20181021055/22/2018 Temp Stop1061065/22/2018 Active1033004 6/01/20186/15/2018STUDENT (StudentID, Group, Read)3005 6/01/20183006 6/01/20186.1046/28/2018StudentID104GroupRead7300032.3300125.6300231.3TUTOR REPORT (MatchID, Month, Hours, Lessons)300313.3MatchIDHoursMonth1 6/18Lessons300422.7300544.84 6/1886300637.85 6/184300741.54 7/181017/182

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

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.

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.

Np Ms Office 365/Excel 2016 I Ntermed

1st Edition

ISBN:9781337508841

Author:Carey

Publisher:Carey

Chapter4: Analyzing And Charting Financial Data

Section: Chapter Questions

Problem 2.5CP

See similar textbooks

Related questions

Q: Please explore the database and then write the appropriate SQL command. 1.List all drugs whose…

A: The given is the requirement to be solved in SQL where three tables are already created with some…

Q: Write SQL queries DDL Queries to implement the following Relational Model i.e. create the Nurse…

A: DDL SQL Query: Data Definition Language DDL Commands: Create Drop Alter Truncate Comment Rename

Q: b. Write SQL. queries for the following staterments. i Display all the employee's details in the…

A: As the data is not provided, I used the dummy data, and queries are solved using the MySQL database.…

Q: Write following SQL Queries:A simple database composed of the following tables: PATIENT, DOCTOR,…

A: Note since your question contain multiple sub part but we can answer only three subpart at a time…

Q: he figure below shows a sample database containing information about: · Students and their…

A: Answer is given below .

Q: Create a database that contains four tables department, employee, address and contact info, then…

A: SQL (Structured Query Language) is a standardized programming language that's used to manage…

Q: Use the Pine Valley Furniture (PVF) database for this question. Write a SQL query to display the ID…

A: Select Customer.Customer ID, Customer.Customer Name from Customer Inner join on Order.Customer ID =…

Q: Hi I hope you can help me. I will rate the answer. Use MYSQL do it in a .sql format. Also please…

A: Given :- Hi I hope you can help me. I will rate the answer. Use MYSQL do it in a .sql format. Also…

Q: Assignment III (5%): Design of the conceptual model (ERD) from the given relational database. (Note:…

A: increase all employee salary by 15% along with department ID, and display all employee data.- Here…

Q: Construct an appropriate SQL query (Create Table) for the following i. Member ii. Make

A: “Since you have asked multiple questions, we will solve the first question for you. If you want any…

Q: Assume the following tables: Student (studid, email, name) Club (clubid, telephone, name,…

A: Assume the following tables: Student (studid, email, name) Club (clubid, telephone, name,…

Q: 1. Write SQL statements to create the tables below. 2. Write SQL statements to insert at least 5…

A: Create table : Tutor table CREATE TABLE TUTOR ( TutorId int NOT NULL, CertDate date, Status…

Q: Review(Reviewer_id, Paper_id, Review_date, Review_Status) Write the Following query into relational…

Q: 1. Write SQL queries to generate the following tables: a. The total number of ongoing tournaments.…

A: SQL query: Structured Query language is used doing operation in database such as insertion, deletion…

Q: This is the problem: (from Database Systems Book, 13th ed) Write a query to display the author last…

A: Create table AUTHORS_OF_BOOKS using the below query: SQL QUERY: /* Create a table called NAMES…

Q: Write and execute a SQL query to list all patient numbers and encounter ids which do not have any…

A: Actually, SQL stands for Structured Query Language.

Q: Write the following queries. 1. Write a query to get the First Name and Last Name of all employees…

A: As per company rules experts are only supposed to Solve one question.So kindly post another…

Q: Create in PL/SQL a stored procedure called 'StudentsActivities' to display the list of students and…

A: The PL/SQL stored procedure is a PL/SQL block which performs one or more specific tasks. The…

Q: a) Consider the following tables: Employee (Emp_no, Name, Emp_city, Company_name, Salary) ni. a)…

A: Given table is, Employee (Emp_no, Name, Emp_city, Company_name, Salary) The name of the table is…

Q: 35. From the product_price table, write an SQL query to find the total and average market price for…

A: We are going to write SQL query for the given statement.

Q: ed all possible SQL queries to perform this relational schem

A: Approach: 1- In this assignment, I have written five different - different SQL queries.2- Where…

Q: Create a database that contains four tables department, employee, address and contact info, then…

A: PL/SQL allows users to mix SQL statements with procedural constructs. It is possible to do PL/SQL…

Q: 4. Write a SQL query to find the name of oldest sailors for each rating level. 5. Find the average…

A: As per our company guidelines we are supposed to answer ?️only first 3️⃣ sub-parts. Kindly repost…

Q: 1. Open SQL Microsoft SQL Management Studio. 2. Create a database named DB_CompanyX. 3. Create…

A: In questions with many questions, we must answer 1.

Q: Given the following relations (the primary keys are underlined): MATCH (CodM, Home Team…

A: SQL Statement of Create and Insert : Create Table Match(CodM int primary key,HomeTeam…

Q: consider the following schema: Product(pid, name, type, manufacturer, price) Buys(cid, pid)…

A: PART 1:- The names of all customers who have purchased all products manufactured by sears in…

Q: Use SQL and TAL Distributors database to complete the following questions: List the number and name…

A: SQL (Structured Query Language) is a database query language. According to ANSI, it is the…

Q: Given the following relations (the primary keys are bold): MATCH (CodM, Home Team VisitingTeam,…

A: SQL Statement of Create and Insert : Create Table Match(CodM int primary key,HomeTeam…

Q: Refer to the Company Database. List the ssn, last name, birth date, and department name of all…

A: In this question, we are asked to write the sql query and algebra expression for the query Two table…

Q: (a) Write the sql syntax to create a table name customer with the following restrictions;…

A: A) create table customer(cid varchar(50) primary key,cname varchar(50) not null,address varchar(100)…

Q: Write the syntax to create a table named Product in SQL with the following restrictions;…

A: According to the question below the Solution:

Q: 5. Write a SQL query to find the maximum, minimum, and average salary of the employees. 6. Write an…

A: Given, Table EmployeeDetails: EmpId FullName ManagerId DateOfJoining City 121 John Snow 321…

Q: b) Write the syntax to create a table named Product in SQL with the following restrictions;…

A: Please find the answer below :

Concept explainers

SQL Query

Structured Query Language is the full form of SQL. It is one of the standard languages to deal with the relational database. It is used to insert, update, delete, and search the records stored in a RDBMS. The relational databases are created and managed by using SQL.

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.

TUTOR (TutorID, CertDate, Status)
MATCH HISTORY (MatchID, TutorID, StudentID,
StartDate, EndDate)
TutorID
CertDate
Status
100
1/05/2018 Active
MatchID
TutorID
StudentID
StartDate
EndDate
1/05/2018 Temp Stop
101
3000 1/10/2018
1
100
102
1/05/2018 Dropped
103
5/22/2018 Active
3001 1/15/2018
3002 2/10/2018
3003 5/28/2018
101
5/15/2018
104
5/22/2018 Active
3
3/01/2018
102
105
5/22/2018 Temp Stop
106
106
5/22/2018 Active
103
3004 6/01/2018
6/15/2018
STUDENT (StudentID, Group, Read)
3005 6/01/2018
3006 6/01/2018
6.
104
6/28/2018
StudentID
104
Group
Read
7
3000
3
2.3
3001
2
5.6
3002
3
1.3
TUTOR REPORT (MatchID, Month, Hours, Lessons)
3003
1
3.3
MatchID
Hours
Month
1 6/18
Lessons
3004
2
2.7
3005
4
4.8
4 6/18
8
6
3006
3
7.8
5 6/18
4
3007
4
1.5
4 7/18
10
1
7/18
2

Organized collection of structured information, or data, typically stored in a computer system.

Expert Solution

This question has been solved!

Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.

This is a popular solution!

SEE SOLUTION Check out a sample Q&A here

Step 1

VIEW

Step 2

VIEW

Step 3

VIEW

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

Create a trigger named trg_line_prod that will automatically update the product quantityon hand for each product sold after a new LINE row is added. Output:After inserting a new row in to LINE: Before running the Trigger the P_QOH was 23: After running the Trigger the P_QOH was 21:
Select the ClientName, ProjectStartDate, and TaskID fields for all records that have a project start date in the year 2020 and have a task ID of MEET00 or MEET01. Sort the records by client name then task ID.
Write down 5 triggers. Triggers should be meaningful.
Scenario and Database Model: InstantRide InstantRide is the new ride sharing application in the city and it has just started its operations. With the help of the InstantRide mobile application, the users request a ride with their location. Drivers and cars are assigned to the request; and then the driver picks up the user to ride their requested location. Information for the users, drivers and cars are stored in the database as well as the travel transactions. In the USERS table, information for the users are stored with their first name, last name and email: In the DRIVERS table, all the drivers in the InstantRide are stored with their name, driving license number and check and rating information: In the CARS table, all the cars in the InstantRide system are kept with the license plate, model and year: Finally, the transactions of the rides are stored in the TRAVELS table. For each travel, start and end time with location are stored. In addition, the involved driver, car and…
Scenario and Database Model: InstantRide InstantRide is the new ride sharing application in the city and it has just started its operations. With the help of the InstantRide mobile application, the users request a ride with their location. Drivers and cars are assigned to the request; and then the driver picks up the user to ride their requested location. Information for the users, drivers and cars are stored in the database as well as the travel transactions. In the USERS table, information for the users are stored with their first name, last name and email: In the DRIVERS table, all the drivers in the InstantRide are stored with their name, driving license number and check and rating information: In the CARS table, all the cars in the InstantRide system are kept with the license plate, model and year: Finally, the transactions of the rides are stored in the TRAVELS table. For each travel, start and end time with location are stored. In addition, the involved driver, car and…
Scenario and Database Model: InstantRide InstantRide is the new ride sharing application in the city and it has just started its operations. With the help of the InstantRide mobile application, the users request a ride with their location. Drivers and cars are assigned to the request; and then the driver picks up the user to ride their requested location. Information for the users, drivers and cars are stored in the database as well as the travel transactions. In the USERS table, information for the users are stored with their first name, last name and email: In the DRIVERS table, all the drivers in the InstantRide are stored with their name, driving license number and check and rating information: In the CARS table, all the cars in the InstantRide system are kept with the license plate, model and year: Finally, the transactions of the rides are stored in the TRAVELS table. For each travel, start and end time with location are stored. In addition, the involved driver, car and…
The SERVICE_REQUEST table uses the CHAR data type for the DESCRIPTION and STATUS fields. Is there an alternate data type that could be used to store the values in these fields? Justify your reason for choosing an alternate data type or for leaving the data type as CHAR.