SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
/* Solve **transaction base queries, must use go statement between queries as everything rolls back after execution #Visit status 0 = pending 1 = active 2 = not active 1. Search for all patients with last name "smith" 2. Search for all patients that has an active visit 3. Insert a new patient and display the patient 4. Insert a new visit and display the visit 5. Update a record of your choice and display the results 6. Search for all patients with at least 20 notes 7. Display all patients: First name, middle name, last name with a count of their notes, minimum date of service, maximum date of service, visit status as text based on above status values */ /* Table Creation */ CREATE TABLE Patient ( ID INT IDENTITY NOT NULL Primary key, FirstName NVARCHAR(MAX), MiddleName NVARCHAR(MAX), LastName NVARCHAR(MAX) ) CREATE TABLE Visit ( VisitID INT IDENTITY NOT NULL Primary key, PatientID INT NOT NULL, AdmitDate DATETIME, [Status] INT NOT NULL DEFAULT(0), FOREIGN KEY (PatientID) REFERENCES Patient(ID) ) CREATE TABLE Notes ( ID INT IDENTITY NOT NULL Primary key, VisitID INT, DateOfService datetime, [Description] NVARCHAR(max), Deleted BIT NOT NULL DEFAULT(0), FOREIGN KEY (VisitID) REFERENCES Visit(VisitID) ) /* Data Population */ INSERT INTO PATIENT(FirstName, MiddleName, LastName) VALUES ('John', 'S', 'Smith'), ('Jane', 'L', 'Doe'), ('Tom',null,'Jones'),('Mary','M','Smith') INSERT INTO Visit(PatientID, AdmitDate,[status]) select ID, (select DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)), ID%3 from patient order by ID declare @max int = 100, @I int = 0 while @I < @max BEGIN INSERT INTO Notes (VisitID, DateOfService, [Description]) select VisitID, (ABS(CHECKSUM(NEWID())) % 65530), newid() from visit where (select rand()) > .65 and visitID = 1 INSERT INTO Notes (VisitID, DateOfService, [Description]) select VisitID, (ABS(CHECKSUM(NEWID())) % 65530), newid() from visit where (select rand()) > .85 and visitID = 2 INSERT INTO Notes (VisitID, DateOfService, [Description]) select VisitID, (ABS(CHECKSUM(NEWID())) % 65530), newid() from visit where (select rand()) > .52 and visitID = 3 INSERT INTO Notes (VisitID, DateOfService, [Description]) select VisitID, (ABS(CHECKSUM(NEWID())) % 65530), newid() from visit where (select rand()) > .33 and visitID = 4 SET @I = @I+1 end GO /* 1. */ SELECT * FROM Patient WHERE LastName like '%smith%' GO /* 2. */ --SELECT * FROM Visit WHERE Status = 1 --OR SELECT P.id, V.status, P.firstname, P.lastname FROM Patient P JOIN Visit V on V.PatientID = P.ID WHERE V.Status = 1 GO /* 3. */ INSERT INTO Patient(FirstName, MiddleName, LastName) VALUES ('Samuel', 'L', 'Jackson'); SELECT Top 1* FROM Patient ORDER BY ID DESC GO /* 4. */ INSERT INTO Visit(PatientID, AdmitDate, [status]) VALUES (5, '2022-08-15', 1); SELECT Top 1 * FROM Visit ORDER BY PatientID DESC GO /* 5.*/ UPDATE Patient SET MiddleName = 'Leroy' WHERE ID = 5 AND MIddleName = 'L'; SELECT * FROM Patient WHERE ID = 5 GO /* 6. */ SELECT P.ID, N.VisitID, COUNT(N.ID) AS 'NoteCount' FROM Notes N JOIN Visit V ON V.VisitID = N.VisitID JOIN Patient P ON P.ID = V.PatientID GROUP BY P.ID, N.VisitID HAVING COUNT(N.ID) >= 20 GO /* 7. */ --7. Display all patients: First name, middle name, last name with a count of their notes, minimum date of service, --maximum date of service, visit status as text based on above status values SELECT P.FirstName, P.MiddleName, P.LastName, COUNT(DISTINCT N.ID) AS 'NoteCount', MIN (N.DateOfService) AS 'MinDate', MAX(N.DateOfService) AS 'MaxDate', CASE WHEN V.Status = 0 THEN 'pending' WHEN V.Status = 1 THEN 'active' WHEN V.Status = 2 THEN 'not active' END AS StatusText FROM Patient P JOIN Visit V ON V.PatientID = P.ID JOIN Notes N ON N.VisitID = V.VisitID GROUP BY P.FirstName, P.MiddleName, P.LastName, V.VisitID, V.Status ORDER BY V.VisitID GO
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear