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 * from encounters en join encounterItemsUsed eni on en.encounterId = eni.encounterId where en.patientId = 1 -- Write a query to delete all the encounters with a comment that contain the number '4'. -- Use SQL transaction Begin Try Begin Transaction Delete from encounters where comments like '%4%' Commit; End Begin catch Rollback; End