SQL활용(음악정보 데이터베이스 구축 및 활용)

1. 음악정보 데이터 베이스 구축

-1. 음악정보 새로운 접속 만들기

• 사용자 : MUSIC
• 암호 : 1234
• 기본 tablepace : USER
• 임시 tablepace : TEMP
• Nation role : connect, resource, dba
• 접속명 : local-MUSIC

-2. 음악정보 DB 테이블 생성

CREATE TABLE album (
    albumNumber             varchar2(3)     not null    primary key,
    albumTitle              varchar2(30)    not null,
    artist                  varchar2(30),
    price                   number,
    releaseDate             date,
    distributionCompany     varchar2(30),
    rating                  number(3,1)
);

CREATE TABLE song (
    albumNumber     varchar2(3)     not null,
    disc            varchar2(3)      not null,
    tracNumber      varchar2(3)      not null,
    musicTitle      varchar2(50),
    sortation       varchar2(10),
    FOREIGN KEY(albumNumber) REFERENCES album(albumNumber)
);

-3. 음악정보 DB 데이터 입력

INSERT INTO album(albumNumber, albumTitle, artist, price, releaseDate, distributionCompany,rating)
VALUES ( '1', '조용필', '조용필', 11500, '1984-10-01', '벅스', 9.8);
INSERT INTO album(albumNumber, albumTitle, artist, price, releaseDate, distributionCompany,rating)
VALUES ( '2', '사랑하기 때문에', '유재하', 10400, '1987-08-20', 'King Pin', 9.7);
INSERT INTO album(albumNumber, albumTitle, artist, price, releaseDate, distributionCompany,rating)
VALUES ('3', 'Break Up 2 Make Up', '지코(ZICO)', 700, '2016-01-25', 'CJ E&M MUSIC', 7.6);
INSERT INTO album(albumNumber, albumTitle, artist, price, releaseDate, distributionCompany,rating)
VALUES ( '4', '도깨비 OST Part 8', '정준일', 500, '2017-01-01', 'CJ E&M MUSIC', 9.5);
INSERT INTO album(albumNumber, albumTitle, artist, price, releaseDate, distributionCompany,rating)
VALUES ( '5', '도깨비 OST Part 9', '에일리', 600, '2017-01-07', 'CJ E&M MUSIC', 8);

SELECT * FROM album;

INSERT INTO song(albumNumber, disc, tracNumber, musicTitle, sortation)
VALUES ('1', '1', '1', '돌아와요 부산항에', '');
INSERT INTO song(albumNumber, disc, tracNumber, musicTitle, sortation)
VALUES ('1', '2', '9', '해변의 여인', '');
INSERT INTO song(albumNumber, disc, tracNumber, musicTitle, sortation)
VALUES ('1', '1', '11', '옛 일', '');
INSERT INTO song(albumNumber, disc, tracNumber, musicTitle, sortation)
VALUES ('1', '1', '12', '서러워 말아요', '');
INSERT INTO song(albumNumber, disc, tracNumber, musicTitle, sortation)
VALUES ('1', '1', '15', '생각이 나네', '');
INSERT INTO song(albumNumber, disc, tracNumber, musicTitle, sortation)
VALUES ('2', '1', '1', '우리들의 사랑', '');
INSERT INTO song(albumNumber, disc, tracNumber, musicTitle, sortation)
VALUES ('2', '1', '2', '그대 내 품에', '');
INSERT INTO song(albumNumber, disc, tracNumber, musicTitle, sortation)
VALUES ('2', '1', '9', '사랑하기 때문에', '');
INSERT INTO song(albumNumber, disc, tracNumber, musicTitle, sortation)
VALUES ('3', '1', '1', '너는 나 나는 너', '');
INSERT INTO song(albumNumber, disc, tracNumber, musicTitle, sortation)
VALUES ('3', '1', '2', '사라잉었다(Feat. 루나 of f(x)', '');
INSERT INTO song(albumNumber, disc, tracNumber, musicTitle, sortation)
VALUES ('4', '1', '1', '첫 눈', '');
INSERT INTO song(albumNumber, disc, tracNumber, musicTitle, sortation)
VALUES ('4', '1', '2', '첫 눈', 'Inst');
INSERT INTO song(albumNumber, disc, tracNumber, musicTitle, sortation)
VALUES ('5', '1', '1', '첫눈처럼 너에게 가겠다', '원곡');
INSERT INTO song(albumNumber, disc, tracNumber, musicTitle, sortation)
VALUES ('5', '1', '2', '첫눈처럼 너에게 가겠다', 'Inst');

SELECT * FROM song;

2. 음악정보 데이터베이스 활용

-1. '해변의 여인' 이라는 노래를 담고 있는 타이틀과 아티스트를 검색하라

SELECT albumTitle AS "타이틀", artist AS "아티스트" FROM album, song
WHERE album.albumNumber = song.albumNumber
AND musicTitle = '해변의 여인';

-2. '그대 내 품에'라는 노래를 부른 아티스트를 검색하라

SELECT artist AS "아티스트" FROM album, song
WHERE album.albumNumber = song.albumNumber
AND musicTitle = '그대 내 품에';

-3. 'Break Up 2 Make Up'이라는 이름을 가지고 있는 앨범에 수록된 노래를 모두 검색

SELECT musicTitle FROM album, song
WHERE album.albumNumber = song.albumNumber
AND albumTitle = 'Break Up 2 Make Up';

-4. 각 앨범에 수록된 타이틀별 수록곡의 개수를 검색하라

SELECT albumTitle AS "타이틀", COUNT(song.albumNumber) AS "수록곡 수"
FROM album, song
WHERE album.albumNumber = song.albumNumber
GROUP BY albumTitle;

-5. '사랑' 이라는 단어가 포함된 곡명을 가진 앨범의 타이틀별 수록곡의 개수를 검색

SELECT albumTitle AS "타이틀", COUNT(song.albumNumber) AS "수록곡 수"
FROM album, song
WHERE album.albumNumber = song.albumNumber
AND musicTITLE LIKE '%사랑%'
GROUP BY albumTitle;

-6. 타이틀과 곡명이 동일한 앨범의 노래 이름을 검색하라

SELECT musicTitle AS "곡명" FROM album, song
WHERE album.albumNumber = song.albumNumber
AND album.albumTitle = song.musicTitle;

-7. 예를 들어 그룹 'Blur'가 'Blur'라는 이름의 앨범을 발매할 수 있다. 이와 같이 아티스트와 타이틀이 동일한 앨범의 타이틀을 검색하라

SELECT albumTitle AS "타이틀" FROM album
WHERE albumTitle = artist;

-8. 동일한 곡명이 2개 이상 앨범에 존재하는 경우,해당 곡명과 수록 곡의 개수를 검색

SELECT musicTitle AS "곡명", COUNT(albumNumber) AS "수록곡 수"
FROM song
GROUP BY musicTitle HAVING COUNT(musicTitle) >= 2;