INFO 340 Study Guide - Quiz Guide: Atlantic Records Group
INFO 340 Quiz 5 Code Review
-- CHECK CONSTRAINT (BUSINESS RULE)
/*
No recording of the son ‘Tears in Heaven’ performed in HipHop may last longer than 3:45
- manage w user defined function
- issue an alter table statement to cement function output
*/
CREATE FUNCTION fnNoTearsHippyHop()
RETURNS INT
AS
BEGIN
DECLARE @Ret INT = 0
IF EXISTS (
SELECT *
FROM SONG
JOIN RECORDING ON S.SongID = R.SongID
JOIN GENERE G ON R.GenreID = G.GenreID
WHERE S.SongName = 'Tears in Heaven'
AND G.GenreName = 'HipHop'
AND R.LENGTH > 3.25
)
SET @Ret = 1
RETURN @Ret
END
GO
ALTER TABLE RECORDING
ADD CONSTRAINT CK_NoTearsLongerThan345
CHECK (dbo.fnNoTearsHippyHop() = 0)
GO
/*
No more than 3 percussionists allowed on any recording of genre 'Hard Rock'
after May 30, 2018
*/
CREATE FUNCTION fnNoMoreThan3Drummers()
RETURNS INT
AS
BEGIN
DECLARE @Ret INT = 0
IF EXISTS (
SELECT *
FROM INSTRUMENT_TYPE IT
JOIN INSTRUMENT I ON IT.InstrumentTypeID = I.InstrumentTypeID
