DATA ASIGNMENT DISD2

AKIL AZIZ - 19004979 GROUP 1

Table - ALBUMs

TABLE - TRACKS

TABLE – ALUM-TRACKS

TABLE ARTISTS
TABLE TRACK_ARTISTS

QUESTION 3

QUESTION 4

QUESTION 5
QUESTION 6

QUESTION 7

QUESTION 8

QUESTION 9
CODE
--DATA ASSIGNMENT

--AKIL AZIZ 19004979

--DISD2

--Question 1

CREATE DATABASE MUSIC_COLLECTION;

--CREATING ALL TABLES

CREATE TABLE ALBUMS(

ALBUM_ID VARCHAR(5) NOT NULL,

ALBUM_TITLE VARCHAR(40) NOT NULL,

CONSTRAINT PK_ALBUMS PRIMARY KEY(ALBUM_ID)

);

CREATE TABLE TRACKS(

TRACK_ID VARCHAR(5) NOT NULL,

TRACK_NAME VARCHAR(40) NOT NULL,

TRACK_DURATION NUMERIC(10,1) NOT NULL,

CONSTRAINT PK_TRACKS PRIMARY KEY(TRACK_ID)

);

CREATE TABLE ALBUM_TRACKS(

TRACK_ID VARCHAR(5) NOT NULL,

ALBUM_ID VARCHAR(5) NOT NULL,

--REFERENCING FOREIGN KEYS

CONSTRAINT FK_TRACKS_ALBUM_TRACKS FOREIGN KEY (TRACK_ID) REFERENCES

CONSTRAINT FK_ALBUMS_ALBUM_TRACKS FOREIGN KEY (ALBUM_ID) REFERENCES

);
CREATE TABLE ARTISTS(

ARTIST_ID VARCHAR(5) NOT NULL,

ARTIST_NAME VARCHAR(40) NOT NULL,

CONSTRAINT PK_ARTISTS PRIMARY KEY(ARTIST_ID)

);

--REFERENCING FOREIGN KEYS

CREATE TABLE TRACK_ARTISTS(

TRACK_ID VARCHAR(5) NOT NULL,

ARTIST_ID VARCHAR(5) NOT NULL,

CONSTRAINT FK_TRACKS_TRACK_ARTISTS FOREIGN KEY (TRACK_ID) REFERENCES

CONSTRAINT FK_ARTISTS_TRACK_ARTISTS FOREIGN KEY (ARTIST_ID) REFERENCES

);

--Question

--Add data to tables IN SINGLE ROWS

INSERT INTO ALBUMS VALUES ('AL001', 'Now Thats What I Call Music');

INSERT INTO ALBUMS VALUES ('AL002', 'Now Dance 11');

INSERT INTO TRACKS VALUES ('TR001', 'Silence', 3.5);

INSERT INTO TRACKS VALUES ('TR002', 'Rain', 4);

INSERT INTO TRACKS VALUES ('TR003', 'Too Good At Goodbyes', 4.5);

INSERT INTO TRACKS VALUES ('TR004', 'Find You', 3.5);

INSERT INTO TRACKS VALUES ('TR005', 'If I Could Find', 2.5);

INSERT INTO TRACKS VALUES ('TR006', 'Let Me Go Now', 3);

INSERT INTO TRACKS VALUES ('TR007', 'Friends', 3.5);

INSERT INTO TRACKS VALUES ('TR008', 'What About Us', 4);

INSERT INTO ALBUM_TRACKS VALUES ('TR001', 'AL001');

INSERT INTO ALBUM_TRACKS VALUES ('TR001', 'AL002');

INSERT INTO ALBUM_TRACKS VALUES ('TR002', 'AL001');

INSERT INTO ALBUM_TRACKS VALUES ('TR004', 'AL001');

INSERT INTO ALBUM_TRACKS VALUES ('TR005', 'AL001');

INSERT INTO ALBUM_TRACKS VALUES ('TR005', 'AL002');

INSERT INTO ALBUM_TRACKS VALUES ('TR006', 'AL002');

INSERT INTO ALBUM_TRACKS VALUES ('TR007', 'AL002');

INSERT INTO ALBUM_TRACKS VALUES ('TR008', 'AL002');

INSERT INTO ARTISTS VALUES ('AR001', 'Marshmallow');

INSERT INTO ARTISTS VALUES ('AR002', 'Khalid');

INSERT INTO ARTISTS VALUES ('AR003', 'The Script');

INSERT INTO ARTISTS VALUES ('AR004', 'Sam Smith');

INSERT INTO ARTISTS VALUES ('AR005', 'Nick Jonas');

INSERT INTO ARTISTS VALUES ('AR006', 'Goldfish');

INSERT INTO ARTISTS VALUES ('AR007', 'Method');

INSERT INTO ARTISTS VALUES ('AR008', 'Justin Bieber');

INSERT INTO ARTISTS VALUES ('AR009', 'BloodPop');

INSERT INTO ARTISTS VALUES ('AR010', 'Pink');

INSERT INTO ARTISTS VALUES ('AR011', 'Elvis Presley');

INSERT INTO TRACK_ARTISTS VALUES ('TR001', 'AR001');

INSERT INTO TRACK_ARTISTS VALUES ('TR001', 'AR002');

INSERT INTO TRACK_ARTISTS VALUES ('TR002', 'AR003');

INSERT INTO TRACK_ARTISTS VALUES ('TR003', 'AR004');

INSERT INTO TRACK_ARTISTS VALUES ('TR004', 'AR005');

INSERT INTO TRACK_ARTISTS VALUES ('TR005', 'AR006');

INSERT INTO TRACK_ARTISTS VALUES ('TR006', 'AR007');

INSERT INTO TRACK_ARTISTS VALUES ('TR007', 'AR008');

INSERT INTO TRACK_ARTISTS VALUES ('TR007', 'AR009');

INSERT INTO TRACK_ARTISTS VALUES ('TR008', 'AR010');

SELECT * FROM ALBUMS;

SELECT * FROM TRACKS;

SELECT * FROM ALBUM_TRACKS;

SELECT * FROM ARTISTS;

SELECT * FROM TRACK_ARTISTS;

--STATEMENTS TO DELETE A TABLE

DROP TABLE TRACKS;

DROP TABLE ALBUMS;

DROP TABLE ALBUM_TRACKS;

DROP TABLE ARTISTS;

DROP TABLE TRACK_ARTISTS;

--Question 3

--USING THE UPDATE STATEMENT (Atkinson, 2012)

UPDATE ARTISTS

SET ARTIST_NAME = 'Marshmallow'

WHERE ARTIST_ID = 'AR001';

--Qyestion 4

--using the WHERE statement to join the data in two tables

SELECT ARTIST_NAME, TRACK_NAME

FROM ARTISTS, TRACKS, TRACK_ARTISTS

WHERE ARTISTS.ARTIST_ID = TRACK_ARTISTS.ARTIST_ID

AND TRACKS.TRACK_ID = TRACK_ARTISTS.TRACK_ID

--ascending order

ORDER BY ARTIST_NAME ASC;

--using the INNER JOIN statement

--naming the new collum heading

SELECT ALBUMS.ALBUM_TITLE, SUM(TRACK_DURATION) AS 'ALBUM LENGHT (MINUTES)'

FROM ALBUMS

INNER JOIN ALBUM_TRACKS ON ALBUMS.ALBUM_ID = ALBUM_TRACKS.ALBUM_ID

INNER JOIN TRACKS ON TRACKS.TRACK_ID = ALBUM_TRACKS.TRACK_ID

GROUP BY ALBUM_TITLE

ORDER BY 'ALBUM LENGHT (MINUTES)' DESC;

--Question 6

--comparing all data from ARTIST table to check if it

--matches with the ARTIST_ID on the TRACK_ARTIST table

--and show the one that gives a NULL value

SELECT a.ARTIST_ID, a.ARTIST_NAME

FROM ARTISTS a

LEFT JOIN TRACK_ARTISTS ta ON ta.ARTIST_ID = a.ARTIST_ID

WHERE ta.ARTIST_ID IS NULL;

--QUESTION 7

--statement to delete a record

DELETE ARTISTS

WHERE ARTIST_ID = 'AR011';

--Question 8

--selecting the top two which has a count of 2

--comparing the TRACK_ID (Atkinson, 2012)

SELECT TOP 2 TRACK_NAME, COUNT(*) AS 'NUMBER OF ALBUMS'

FROM TRACKS, ALBUM_TRACKS

WHERE TRACKS.TRACK_ID = ALBUM_TRACKS.TRACK_ID

GROUP BY TRACK_NAME
ORDER BY 'NUMBER OF ALBUMS' DESC;

--Question 9

--create two separate collums holding the TRACK_NAME and

--comparing if they have the same duration lengh

--the results give duplicate data

SELECT A.TRACK_NAME AS 'TRACK_NAME', B.TRACK_NAME AS 'TRACK_NAME', A.TRACK_DURATION

FROM TRACKS A, TRACKS B

WHERE A.TRACK_NAME <> B.TRACK_NAME

AND A.TRACK_DURATION = B.TRACK_DURATION

ORDER BY A.TRACK_DURATION;

--REFERENCE LIST

--Atkinson, P. and Viera, R. 2012. Beginning Microsoft SQL Server 2012 Programming. Indianapolis,
Ind.: Wiley