Songs: each song has a title, duration, and genre. Title and duration are requir

Question

Songs: each song has a title, duration, and genre. Title and duration are required; genre is not.

Publish date has to be before today, and after the first day of the current year. Genre must be at

least 3 characters.

Albums: each album has a title, artist, release date, price, format, and genre. All fields except

genre are required. Price can't be negative. Format must be record, cd, or eight track. Release date

can be any date after 1/1/1931 if the format is record; any date after 1/1/1968 if the format is eight

track; and any date after 1/1/1979 if it's a cd.

*/

-- create and use database

CREATE DATABASE music

GO

USE music

-- create songs

CREATE TABLE songs

(

songTitle nvarchar(50) NOT NULL,

songDuration time NOT NULL,

genre varchar(10)

CHECK (Len(genre) >=3),

publishDate date

CHECK (publishDate < getdate() AND

(publishdate > datefromparts(datepart(yyyy,getdate()),1,1) AND

publishdate < getdate())

)

CREATE TABLE albums

(

albumTitle nvarchar(50) NOT NULL,

artist nvarchar(100) NOT NULL,

releaseDate date NOT NULL,

price smallmoney NOT NULL,

albumFormat varchar(10) NOT NULL

CHECK (albumFormat IN ('cd','record','eight track')),

genre varchar(10),

CHECK ((releaseDate > '1/1/1931' AND albumFormat = 'record') OR

(releaseDate > '1/1/1968' AND albumFormat = 'eight track') OR

releaseDate > '1/1/1979' AND albumFormat = 'cd'))

)

SQL ( EXERCISE): USE THE SOLUTION ABOVE AND MAKE THE FOLLOWING CHANGES:

Each song belongs to a single album (one album has many songs). Modify the database to implement this relationship using identity columns for any primary key(s) Add the following entries to the database: Album # itle st Release Date Price Format enre anges in pimmy 6/1/1987 95 8 track country Latitude, Buffet anges in titude 1/1995 8.95 cd Barometer immy Buffett pop oup Black Ice D/DC 11/1/2008 6.95 cd 1/1991 8.95 Pocket Fu Spin of Kryptonite cd Doctors

Explanation / Answer

SQL Quries Solutions:

1) For any song that doesn't have a genere, make genre "unknown"

UPDATE songs SET genre='unknown' WHERE IFNULL(unknown,'')='';

2) Correct AD/DC to AC/DC (Black Ice)

UPDATE albums SET artist ='AC/DC (Black Ice)' WHERE artist='AD/DC';

3) Change the format of "Changes in Latitudes..." to record

UPDATE albums SET TITLE =REPLACE('Changes in Latitudes','RECORD') WHERE TITLE LIKE '%Changes in Latitudes%';

4) Change artist "Jimmy Buffet" to "Jimmy Buffet and the Coral Reefer Band"

UPDATE albums SET artist ='Jimmy Buffet and the Coral Reefer Band' WHERE artist='Jimmy Buffet';

5) Delete any song that has genre of country or time more than 4 minutes.

DELETE FROM songs WHERE DURATION > '4' OR genre ='country';

6) Delete any album released as an 8 track

DELETE FROM albums WHERE album=(Select DISTINCT album FROM SONGS WHERE 8<(Select COUNT(1) AS CNT FROM SONGS GROUP BY album) ;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.