Question: Write a SQL query that will utilize the two datasets in this spreadsheet and result in a single table as described below: A list of


Write a SQL query that will utilize the two datasets in this spreadsheet and result in a single table as described below:

A list of doctors, sorted by Doctor ID (from largest to smallest) with at least one call in the following date range - 1/1/2020 - 12/31/2020.

Columns in the final table should be: Doctor ID, Doctor First Name, Doctor Last Name, Number of Blue Calls, Number of Green Calls, Total Calls

Number of Blue Calls is defined as the Number of Calls in the aforementioned date range where Blue is the Call Type

Number of Green Calls is defined as the Number of Calls in the aforementioned date range where Green is the Call Type

Number of Total Calls is defined as the Number of Calls in the aforementioned date range, regardless of Call Type.

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock

To achieve this in SQL assuming you have two tables doctors and calls and calls table ha... View full answer

blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related Databases Questions!

Q:

Let A, B be sets. Define: (a) the Cartesian product (A B) (b) the set of relations R between A and B (c) the identity relation A on the set A [3 marks] Suppose S, T are relations between A and B, and...

Q:

Write Create Table commands to create the three tables above in your database and create a primary key for each table. Add any foreign keys as appropriate. Include in your answer the table creation...

Q:

Design a Java class that represents a cache with a fixed size. It should support operations like add, retrieve, and remove, and it should evict the least recently used item when it reaches capacity.

Q:

Write SQL commands for the following requirements. ( 1 ) Write a query to list visit number, visit date, doctor ID , patient number, patient name, and pat balance from those visit records in the...

Q:

SIT103 Data and Information Management T2, 2023 Summative assessment task-2: A real-world database design and implementation project Due dates: Part-1: Project proposal, due date: Wednesday 10th May...

Q:

Question 3 Practical computer work: The answer to this question should be submitted electronically. ( Marks: 6 5 ) Using MySQL , create a single Structured Query Language ( SQL ) script that answers...

Q:

CIS 205 Final Project Summer 2018 The final assignment consists of separating a database project into the phases that were outline during the semester. The project should be completed with the...

Q:

In this question assume that p and q are atomic formulae. (a) Compare and contrast path formulae and state formulae in temporal logic. [4 marks] (b) Describe and contrast the meanings of F(G p) and...

Q:

Developments in Technology Light is incident from air on the end face of a multimode optical fibre at angle of incidence as shown below. n n 1 2 The refractive indices of the core and cladding are...

Q:

this is my assessment which are am going to send you and i need some things about my assessment : Adding some more detail and diving into the case study a bit deeper would really make your points...

Q:

What are the basic differences between wireless WANs and WLANs, and what are the common features? Consider mode of operation, administration, frequencies, capabilities of nodes, services,...

Q:

The heat transfer coefficient on the inside of a copper tube (1.9 cm ID and 2.3 cm OD) is 500 W/(m2 K) and 120 W/(m2 K) on the outside, but a deposit with a fouling factor of 0.009 (m2 K)/W (based on...

Q:

AP7.2 A magnetically levitated high-speed train flies on an air gap above its rail system, as shown in Figure AP7.2 (a) [24]. The air gap control system has a unity feedback system with a loop...

Q:

What is the effect of an increase in the central banks nominal interest rate target in the basic New Keynesian model?

Q:

Why is money nonneutral in the New Keynesian open-economy model?

Q:

What happens when the money supply increases in a liquidity trap?

Q:

Compare the ISO/IEC 27001 outline with the NIST documents discussed in this chapter. Which areas, if any, are missing from the NIST documents? Identify the strengths and weaknesses of the NIST...

Q:

Are capital controls a good idea? Why or why not?

Q:

56. _gives lower-level managers and non-managerial employees the right to make important decisions about using organizational resources. A. Decentralize authority B. Minimize span of control C....

Q:

When a student has not chosen a major at a university, the university often enters a value of "Undecided" for the major field. Is "Undecided" a way to represent the null value? Should it be used as a...

Q:

Indicate whether data administration or database administration is typically responsible for each of the following functions: a. Managing the data repository b. Installing and upgrading the DBMS c....

Q:

Summarize six important properties of relations.

Q:

Consider the following events that change prices. For each one, say whether the opportunity cost of consuming the affected good increases or decreases.

Q:

You manage two employees at a pet salon. Your employees perform two tasks: giving flea baths and grooming animals. If you constructed a single production possibilities frontier for flea baths and...

Q:

Consider the following examples. For each one, say whether the incentive is positive or negative. a. Bosses who offer time-and-a-half for working on national holidays. b. Mandatory minimum sentencing...