xddd

--show occupied hours at which there was no visit(management wants to check if the number is not too high)
SELECT Work_shift.Id as workShiftID, Work_shift.Day_of_the_week as dayOfTheWeek,
	(SELECT COUNT(*) FROM Occupied_hours WHERE Occupied_hours.Id not in(SELECT Visits.Occupied_hours_id FROM Visits)) as nrOfHoursWithNoVisits
	FROM Work_shift, Occupied_hours, Visits
	WHERE Occupied_hours.Id not in (SELECT Visits.Occupied_hours_id FROM Visits)
	GROUP BY Work_shift.Id, Work_shift.Day_of_the_week
	ORDER BY 1

--show all services with amount amount of money that was made from them in total(want to check which one is the most profitable)
SELECT Service_made_AND_Price_Catalog.Name as serviceName,
	SUM(Service_made_AND_Price_Catalog.Price) as sumOf
	FROM Service_made_AND_Price_Catalog JOIN Service_made
	ON Service_made_AND_Price_Catalog.Id = Service_made.Id
	GROUP BY Service_made_AND_Price_Catalog.Name
	ORDER BY sumOf

--show all endodonists with nr of their workshifts, there is need for job cuts and one with the least workshifts needs to go
SELECT Dentists.License_number as dentists, Specialization.Type_of_specialization as spec,
	(SELECT COUNT(*) FROM Work_shift WHERE Work_shift.License_number = Dentists.License_number) as nrOfH
	FROM Dentists, Specialization
	WHERE Specialization.License_number = Dentists.License_number and Specialization.Type_of_specialization IN( 'Endodontist')


--show occupied hours of all dental assistants during certain work shift

SELECT Dental_assistant_id, Occupied_hours.Start_time, Occupied_hours.Finish_time, Work_shift.Day_of_the_week
	FROM Occupied_hours JOIN Work_shift ON Occupied_hours.Work_shift_id = Work_shift.Id
	WHERE Day_of_the_week IN('Monday')


--
--DROP VIEW IF EXISTS CD
--GO
----CREATE VIEW CD AS
----	SELECT People.Name, People.Surname, Diseases.Id as diseaseID
----	FROM People, Diseases
----	WHERE Diseases.Personal_identity_number = People.Personal_identity_number;

----GO

----SELECT CD.Name, CD.Surname, Diseases_Catalog.Name, Diseases_Catalog.What_it_is
----	FROM CD, Diseases_Catalog
----	WHERE Diseases_Catalog.Id = CD.diseaseID


--show how many times each dentist worked with each assistant

SELECT Dentists.Personal_identity_number, Dental_Assistant.Personal_identity_number, COUNT(Work_hours.Id) as nrOfTimes
	FROM Work_hours, Dental_Assistant, Dentists
	WHERE Work_hours.Dental_assistant_id = Dental_Assistant.Id and Work_hours.License_number = Dentists.License_number
	GROUP BY Dentists.Personal_identity_number, Dental_Assistant.Personal_identity_number
	ORDER BY nrOfTimes



-- show dentists who arent likely to treat children who actually treated children
DROP VIEW IF EXISTS CV
GO
CREATE VIEW CV AS
	SELECT People.Name +' '+ People.Surname as cvName, People.Age as cvAge, Visits.Work_shift_id as cvWK
	FROM People, Visits
	WHERE People.Personal_identity_number = Visits.Personal_identity_number
GO

DROP VIEW IF EXISTS WCV
GO
CREATE VIEW WCV AS
	SELECT cvName as customerName, cvAge as customerAge, Work_hours.License_number as dentistLicense
	FROM CV, Work_hours
	WHERE cvWK = Work_hours.Id
GO

SELECT * FROM WCV
	WHERE customerAge < 18