SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE [patients]( [patientId] [int] IDENTITY(1,1) NOT NULL, [hospitalId] [int] NOT NULL, [lastname] [varchar](10) NOT NULL, [firstname] [varchar](10) NOT NULL, [dateOfBirth] [datetime] NULL, [lastModifiedDate] [datetime] NOT NULL, PRIMARY KEY (patientId) ) ALTER TABLE [patients] ADD DEFAULT (getdate()) FOR [lastModifiedDate] GO CREATE TABLE [encounters]( [encounterId] [int] IDENTITY(1,1) NOT NULL, [code] [varchar](50) NULL, [hospitalId] [int] NULL, [patientId] [int] NOT NULL, [comments] [varchar](1024) NULL, [lastModifiedDate] [datetime] NOT NULL, PRIMARY KEY (encounterId), FOREIGN KEY([patientId]) REFERENCES [patients] ([patientId]) ) GO ALTER TABLE [encounters] ADD DEFAULT (getdate()) FOR [lastModifiedDate] GO CREATE TABLE [encounterItemsUsed]( [encounterItemsUsedId] [int] IDENTITY(1,1) NOT NULL, [encounterId] [int] NOT NULL, [lotNumber] [varchar](50) NULL, [type] [varchar](10) NULL, [QTY] [int] NULL, [price] [money] NULL, [isBillable] [int] NOT NULL, [lastModifiedDate] [datetime] NOT NULL, PRIMARY KEY (encounterItemsUsedId), CONSTRAINT CHK_Type CHECK([type] = 'Add' OR [type] = 'Remove' OR [type] = 'Alter') ) ALTER TABLE [encounterItemsUsed] ADD DEFAULT (getdate()) FOR [lastModifiedDate] GO ALTER TABLE [encounterItemsUsed] ADD CONSTRAINT [FK_encounterItemsUsed_encounters] FOREIGN KEY([encounterId]) REFERENCES [encounters] ([encounterId]) GO ALTER TABLE [encounterItemsUsed] CHECK CONSTRAINT [FK_encounterItemsUsed_encounters] GO INSERT INTO patients (hospitalId, lastname, firstname, dateOfBirth) VALUES (1,'test1', 'test1', getdate()), (1,'test2', 'test2', '2010-05-18 12:00:00'), (2,'test3', 'test3', '2000-07-01 09:00:00') INSERT INTO encounters ([code], hospitalId, patientId, comments) VALUES ('code1', 1, 1,'Test case 1'), ('codeTS', 2, 3,'Test case 2'), ('codeRS', 2, 3,'Test case 3'), ('codeRS', 2, 3,'Test case 4') INSERT INTO encounterItemsUsed (encounterId, lotNumber, type, QTY, price, isBillable) VALUES (1, 'LOT#1', 'Add', 5, 1.1, 1), (1, 'LOT#1', 'Add', 7, 1.2, 1), (1, 'LOT#1', 'Remove', 1, 0.5, 0), (2, 'LOT#3', 'ADD', 1, 5.65, 1), (2, 'LOT#3', 'ADD', 1, 11.75, 1), (3, 'LOT#5', 'ADD', 1, 13.5, 1) GO -- Can we insert a new encounterItemsUsed record with a type = 'Used' and why? -- Write a query to select the encounter along with its encounterItemsUsed records -- For patient Id = 1? SELECT E.*, EI.* FROM encounters E JOIN encounterItemsUsed EI ON E.encounterId = EI.encounterId WHERE E.patientId = 1 -- Write a query to delete all the encounters with a comment that contain the number '4'. -- Use SQL transaction BEGIN TRANSACTION DECLARE @cnt INT SET @cnt = (select count(*) FROM encounters WHERE Comments like '%4%') DELETE FROm encounters WHERE Comments like '%4%' IF @cnt = @@rowcount BEGIN COMMIT END ELSE BEGIN ROLLBACK END