/*-----------------BASIC QUERIES ------------------------------
---------------------------------------------------------------*/
SHOW DATABASES;
SHOW TABLES FROM tcs;

USE tcs;
DESCRIBE Person; -- returns the table structure

SELECT * FROM contactdetails
ORDER BY ID DESC -- use order by before LIMIT
LIMIT 5;

SELECT * FROM contactdetails WHERE forenames LIKE 'Ashley%';

-- Shows the lines of sql statements used to create the table
SHOW CREATE TABLE Person;

-- Creating a table like an existing table without the data
CREATE TABLE PersonCopy like Person;

-- Showing the indexes used in a table
SHOW INDEX FROM Person;

-- Date/time functions
SELECT NOW() + INTERVAL 6 DAY; -- now plus 6 days in the future
SELECT NOW() + INTERVAL 3 MONTH; -- now plus 3 months in the future
SELECT NOW() + INTERVAL 13 WEEK; -- now plus 13 weeks in the future
SELECT Date_Format (NOW(), '%d/%m/%y'); -- returning the date in a particular format


/*-----------------JOINS AND SUB-QUERIES-------------------------
---------------------------------------------------------------*/

-- Using sub-query to return the count of trainees without a qualifcation (7875)
SELECT COUNT(id) FROM contactdetails WHERE id NOT IN 
	(SELECT id FROM qualification);
    
-- Using LEFT/LEFT OUTER JOIN with exclusion to return the equivalent of above (7875)
-- Nulls are returned for Persons without qualifications, i.e all persons with unmatched rows from qualifications
SELECT COUNT(c.id) FROM contactdetails as c
-- SELECT c.forenames, c.Surname, q.id as QualificationId FROM contactdetails as c
LEFT JOIN qualification as q
ON c.id = q.id 
WHERE q.id is null;

-- Using JOIN/INNER JOIN to return a count of all Posts with Placements (180452)
-- Nulls are not returned, only matched rows from both tables
SELECT count(ps.id) from post as ps
INNER JOIN placement as pl
ON ps.id = pl.id;

-- Using LEFT/LEFT OUTER JOIN to return a count of Posts with or without Placement (92893)
-- Nulls PlacementIDs are returned for Posts with no placements 
SELECT ps.id as PostID, pl.id as PlacementID, ps.nationalpostnumber from post as ps
-- SELECT COUNT(ps.id) FROM post as ps
LEFT JOIN placement as pl
ON ps.id = pl.id;


-- Using LEFT/LEFT OUTER JOIN to return a count of Posts without Placement (92893)
-- Nulls PlacementIDs are returned for Posts with no placements 
SELECT ps.id as PostID, pl.id as PlacementID, ps.nationalpostnumber from post as ps
-- SELECT COUNT(ps.id) FROM post as ps
LEFT JOIN placement as pl
ON ps.id = pl.id
WHERE pl.id is NULL;


-- Using a RIGHT/RIGHT OUTER JOIN to return a count of orphaned Placements, i.e. placements without a post (check bad data)
-- Looking for Null post ids in placements
SELECT count(pl.id) from placement as pl
RIGHT JOIN post as ps 
ON pl.postId = ps.id
WHERE ps.id is NULL;

-- Using a RIGHT/RIGHT OUTER JOIN to return a count of orphaned programme memberships,
-- i.e. programme memberships where the programme does not exist (check bad data)
-- Nulls in programme ids
SELECT count(p.id) from programmemembership as pm
RIGHT JOIN programme as p
ON pm.programmeId = p.id
WHERE p.id is NULL;


-- Using a RIGHT / RIGHT OUTER join to return posts from the ProgrammePost table that no longers exist as Posts (check bad data)
-- If a post or programme does not exist anymore, there shouldn't be any records with those IDs in ProgrammePost
SELECT pg.programmeId, pg.postID as Prg_PostID, ps.id as Post_ID FROM Post as ps
RIGHT JOIN programmepost pg
ON ps.id = pg.postId
WHERE ps.id is NULL;

-- Using RIGHT/ RIGHT OUTER join to return the Programmes from the ProgrammePost table that no longer exist in Programme table
SELECT pp.programmeId, pp.postID as Prg_PostID, pg.id as Prog_ID FROM Programme as pg
RIGHT JOIN programmepost pp
ON pp.programmeId = pg.id
WHERE pg.id is NULL; 

-- Check if those post actually exist in Post table using a Sub-query and Join
-- They don't!
Select * from Post where id in (
Select pg.postID from Post as ps
RIGHT JOIN programmepost pg
ON ps.id = pg.postId
WHERE ps.id is NULL);

-- Check if they exist in the PostSpecialty table
-- Although those no longer exist they still have some records in PostSpecialty table (bad data)
SELECT * FROM PostSpecialty WHERE postId IN (
	SELECT pg.postID from Post as ps
	RIGHT JOIN programmepost pg
	ON ps.id = pg.postId
	WHERE ps.id is NULL);
 
-- JOINING MULTIPLE TABLES
-- This query was written for the Placement Planning tool output
-- The requirement was to return all posts with or without placements for a specific Programme, Specialty and date range.
SELECT 
ProgrammePost.Programmeid,
PostSpecialty.specialtyId, 
ProgrammePost.postId, 
Post.nationalPostNumber,
Placement.id AS Placement_Id,
Placement.dateFrom,
Placement.dateTo,
Placement.placementWholeTimeEquivalent,
Placement.intrepidId,
Placement.traineeId,
Placement.siteId AS Placement_Main_Site,
Placement.postId AS Placement_PostID,
PostSite.siteId AS Post_Primary_site,
ContactDetails.Forenames,
ContactDetails.surname,
GmcDetails.gmcNumber,
GdcDetails.gdcNumber
FROM Post
LEFT JOIN ProgrammePost ON Post.Id = ProgrammePost.postId
LEFT JOIN Placement ON Post.Id = Placement.postId
LEFT JOIN PostSpecialty ON Post.Id = PostSpecialty.postId
LEFT JOIN ContactDetails ON Placement.traineeId = ContactDetails.Id
LEFT JOIN GmcDetails ON Placement.traineeId = GmcDetails.Id
LEFT JOIN GdcDetails ON Placement.traineeId = GdcDetails.Id
LEFT JOIN PostSite ON PostSite.Postid = Post.Id AND PostSite.postSiteType='PRIMARY'
-- input your Programme
WHERE ProgrammePost.Programmeid = 18053
AND Post.status = 'CURRENT'
-- input your Specialty
AND PostSpecialty.specialtyId = 210
-- input your date range
AND (Placement.dateFrom IS NULL OR CONVERT(Placement.dateFrom,DATE) <= '2021-03-07')
AND (Placement.dateTo IS NULL OR CONVERT(Placement.dateTo,DATE) >= '2019-03-07')
ORDER BY Placement_Id asc;

/*------------UPDATE AND DELETE SAFELY USING IDs/ USE ROLLBACK AND COMMIT TRANSACTION-------------
---------------------------------------------------------------------------------------------------*/
START TRANSACTION;
UPDATE contactdetails
SET email = 'ashley.ransoo@hee.nhs.uk' 
WHERE id = 2815;
ROLLBACK;
-- COMMIT;
Select * from contactdetails where id = 2815;


/*-----------------------------SLOW RUNNING QUERIES LOG ------------------------------------------
--------------------------------------------------------------------------------------------------*/
-- Has a minimum and a default value  for the 'long query time'
USE mysql;
SHOW Status;

SHOW CREATE TABLE mysql.general_log;
SHOW CREATE TABLE mysql.slow_log;

Select * from mysql.slow_log;
Select * from mysql.general_log;

SHOW global Status;