SQLize Online / PHPize Online

A A A
Share   Donate   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. */ GO /* 2. */ GO /* 3. */ GO /* 4. */ GO /* 5. */ GO /* 6. */ GO /* 7. */ GO
Stuck with a problem? Need help? Ask professionals on our Telegram channel!
Join the channel
Your quersion is posted.
Join our Telegram channel and get answers.
Send
Copy