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') 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 = 'New' and why? -- Write a query to update the comment to 'Test case 4' -- for all the encounters with a comment that contain the number '3' -- Write a query to select the encounter along with its encounterItemsUsed records -- For patient Id = 1? update encounters set comments = 'Test case 4' where comments like '%3%'