SQLize Online / PHPize Online  /  SQLtest Online

/* 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. Search for all patients with at least 20 notes 4. 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 5. Insert a new patient and display the patient 6. Insert a new visit and display the visit 7. Update a record of your choice and display the results */ /* 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