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 encounters.* ,encounterItemsUsed.* FROM encounters INNER JOIN encounterItemsUsed ON encounterItemsUsed.encounterId = encounters.encounterId WHERE encounters.patientId = 1 ; -- Write a query to delete all the encounters with a comment that contain the number '4'. -- Use SQL transaction BEGIN TRANSACTION test04 DELETE FROM encounters WHERE comments LIKE '%4%'; SELECT * FROM encounters ; --ROLLBACK --COMMIT