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?
-- Ans: No
-- Write a query to select the encounter along with its encounterItemsUsed records
-- For patient Id = 1?
SELECT
E.encounterId,
E.code,
E.comments,
U.encounterItemsUsedId,
U.lotNumber,
U.type
FROM
encounters E
INNER JOIN
encounterItemsUsed U
ON
E.encounterId = U.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 Test103;
BEGIN TRY
SELECT * FROM encounters;
DELETE FROM
encounters
WHERE
comments LIKE '%4%'
SELECT * FROM encounters;
COMMIT TRANSACTION Test103;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION Test103;
THROW;
END CATCH
Telegram: DB developers channel