SQL> CREATE TABLE album (
2 aNumber NUMBER(6) NOT NULL PRIMARY KEY,
3 title VARCHAR2(30) NOT NULL,
4 artist VARCHAR2(30),
5 price number(6),
6 aData date,
7 dist varchar2(30),
8 score float(6)
9 );
SQL> create table song (
2 aNumber NUMBER(6) NOT NULL,
3 disk number(6) not null,
4 sNumber number(6),
5 songname varchar2(50),
6 div varchar2(10),
7 foreign key(aNumber) references album(aNumber),
8 constraint songPK primary key(aNumber, disk, sNumber)
9 );
SQL> insert into album values (numberSQE.nextVal, 'true', 'avicii', 10400, '1987-08-20', '아일랜드레코드', 10.0);
1 row created.
SQL> insert into album values (numberSQE.nextVal, 'faded', 'Alan walker', 700, '2015-12-03', 'mer musikk', 9.5);
1 row created.
SQL> insert into album values (numberSQE.nextVal, 'Pray for the wicked', 'Panic! At the disco', 500, '2018-06-22', 'DCD2', 9.4);
1 row created.
SQL> insert into album values (numberSQE.nextVal, 'Get your heart on', 'Simple plan', 600, '2011-06-21', 'atlantic', 9.0);
1 row created.
SQL> insert into song values(1, 1, 1, 'Wainting for love',NULL);
1 row created.
SQL> insert into song values(1, 1, 5, 'For a better day',NULL);
1 row created.
SQL> insert into song values(2, 1, 1, 'Wake me up',NULL);
1 row created.
SQL> insert into song values(2, 1, 3, 'Hey brother',NULL);
1 row created.
SQL> insert into song values(3, 1, 1, 'Faded',NULL);
1 row created.
SQL> insert into song values(3, 1, 4, 'The Spectre',NULL);
1 row created.
SQL> insert into song values(4, 1, 3, 'Hey look ma, i made it',NULL);
1 row created.
SQL> insert into song values(4, 1, 4, 'High hopes',NULL);
1 row created.
SQL> insert into song values(5, 1, 1, 'You suck at love',NULL);
1 row created.
-- 1.
select a.title, a.artist from song s inner join album a on a.aNumber = s.aNumber where s.songname = 'Wainting for love';
-- 2.
select a.artist from song s inner join album a on a.aNumber = s.aNumber where s.songname = 'Faded';
-- 3.
select s.songname from album a inner join song s on a.aNumber = s.aNumber where a.title = 'Pray for the wicked';
--4.
select s.songname from album a inner join song s on a.aNumber = s.aNumber where dist = 'atlantic';
--5.
select a.title, count(s.aNumber) from album a inner join song s on s.aNumber = a.aNumber group by a.title;
--6.
select a.title, count(s.aNumber) from song s inner join album a on a.aNumber = s.aNumber where s.songname like '%y%' group by a.title;
--7.
select songname from album a inner join song s on a.aNumber = s.aNumber where upper(a.title) = upper(s.songname);SQL> insert into song values(5, 1, 3, 'Jet lag',NULL);
-- 8.
select s.songname from album a inner join song s on a.aNumber = s.aNumber where a.price >= 10000;