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 select * from Patient where LastName='Smith' GO select * from Patient p inner join Notes n on p.id=n.id inner join Visit v on v.visitid=n.visitid where v.status=1 GO INSERT INTO PATIENT(FirstName, MiddleName, LastName) VALUES ('Fred', 'S', 'Evens') GO INSERT INTO Visit(PatientID, AdmitDate,[status]) select ID, getdate(), '1' GO UPDATE PATIENT SET MiddleName = 'Frank' where LastName='Evens' select * from PATIENT where MiddleName = 'Frank' GO select p.id from Patient p inner join Notes n where n.id=p.id group by p.id having count(n.id) >=20 GO /* 7. */ GO
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear