Goal Although NOSQL seems to be all the rage, particularly MongoDB, traditional

Question

Goal

Although NOSQL seems to be all the rage, particularly MongoDB, traditional RDBMS will never go away. They are too useful. Therefore, to be a full stack developer, you have to know SQL at the journeyman level.

Details

You will be given typical business questions that can be answered with an SQL query.

Treat them like real business concerns you were asked to handle.

Just because the business didn't spell out a requirement doesn't mean you aren't responsible. For example, just because #1 specified sorting doesn't mean you're off the hook for sorting on #3.

In addition, don't give out too much information to the business. The CEO doesn't give a @#$%! about the item id#. For example, #4 should be only one row with the item name and the volume. Again, don't whine about "it doesn't say to include the volume"--that's a huge disfavor you get in school. Ambiguity is the rule in real life. Deal with it. You have to make business decisions in what to report and how to format it.

Make column headers human readable in the result table. In fact, even if they are perfectly human readable, such as "name", change it to "Customer Name" or "Top Grossing Customer"

1)

a. Are any vendors also customers? If so, who?

b. Get me a list of our items by total revenue.

c. Which vendors grossed the most?

d. Which item is in the most orders?

e. Which customers have the most orders?

f. What are our cheapest and most expensive items? What's their volume? What's the price? What's the total revenue?

Setting Up MySQL and Populating the Data

1. The tables are already created and populated at http://sqlfiddle.com/#!9/284793 (Links to an external site.)Links to an external site..

2. Write your queries to answer the questions above.

Explanation / Answer

I have created a stored procedure in SQL based on the requirement given in the question

All the table and data creation scripts executed and stored procedure created based on the data provided

Below is the SQL script which creates the stored procedure, i have given comments to explain its functionality

SQL CODE

--Procedure to check the available appointment of doctor

CREATE PROCEDURE [dbo].[GetDoctorAppointment]

@DocName varchar(100), --Doctor's Name

@AppointDate DateTime, -- Appointment datetime

@interval int -- interval time

AS

BEGIN

--The below If condition checks whether the given data

--in parameter conflicts with any start and end time of doctor's appointment

--If exists then return 0, else return 1

If Exists(Select * from appointment as a

inner join doctor as d on d.did = a.did

inner join appointment_service as asr on asr.aid = a.aid

inner join service as s on s.sid = asr.sid

inner join patient as p on p.pid = a.pid

where d.dname = @DocName and

--In below statement added the interval time to the required appintment datetime

(a.start_time between CONVERT(DATETIME, @AppointDate) AND DATEADD(mi, @interval, @AppointDate))

OR (a.end_time between CONVERT(DATETIME, @AppointDate) AND DATEADD(mi, @interval, @AppointDate)))

Begin

PRINT 0;

Return 0;

END

Else

Begin

PRINT 1;

Return 1;

End

END

[Procedure to check the available appointment of doctor CREATE PROCEDURE [dbo] [GetDoctorAppointment] @DocName varchar (100),?Doctors Name @AppointDate DateTime ,?Appointment datetime interval int -- interval time AS BEGIN -The below If condition checks whether the given data --in parameter conflicts with any start and end time of doctors appointment -If exists then return 0, else return 1 If Exists (Select from appointment as a inner join doctor as d on d.dida.did inner join appointmentservice as asr on asr.aid = a.aid inner join service as 3 on 3.31d = a3r.31d inner join patient as p on p.pida.pid where d.dname -DocName and --In below statement added the interval time to the required appintment datetime (a·start time between CONVERT (DATETIME , @AppointDate) AND DATEADD(mi, @interval , @AppointDate)) OR (a.endtime between CONVERT (DATETIME, EAppointDate) AND DATEADD (mi, Einterval, CAppointDate))) Begin - PRINT 0 Return 0 END Else Begin PRINT 1: Return 1; End END]

Below screen shows the output of the above given stored procedure when passing the doctor's name and the appointment date and mins, here i have used the same sample data that given in the question

Output Screen

When passing time inbetween 9:15 to 9:45

When passing time inbetween 10:00 to 10:30

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.