tvs.sql

CREATE DATABASE TV;

use TV;

--1
CREATE TABLE TV_CHANNELS
(
	TV_NO char(3) not null primary key,
	NAME VARCHAR(20) not null
)

CREATE TABLE MOVIES
(
	MOVIE_ID int not null primary key,
	NAME VARCHAR(30) not null
)

CREATE TABLE DAILY_PROGRAM
(
	TV_NO char(3) not null,
	MOVIE_ID int not null,
	constraint PK_DAILY_PROGRAM primary key(TV_NO, MOVIE_ID),
	constraint FK_DAILY_PROGRAM_TV_CHANNEL foreign key (TV_NO) references TV_CHANNELS(TV_NO),
	constraint FK_DAILY_PROGRAM_MOVIES foreign key (MOVIE_ID) references MOVIES(MOVIE_ID) on delete cascade
)

--2
alter table DAILY_PROGRAM
add RATING int null check (RATING between 1 and 5);

--3
INSERT INTO MOVIES(MOVIE_ID, "NAME")
values (1, 'Die hard'),
(2, 'Revolver'),
(3, 'Reservoir dogs'),
(4, 'Once upon a time in Holly wood'),
(5, 'Pulp fiction');

INSERT INTO MOVIES(MOVIE_ID, "NAME")
values (6, 'Opasen char');

INSERT INTO TV_CHANNELS(TV_NO, "NAME")
values (1, 'bTv'),
(2, 'Nova'),
(3, 'Diema'),
(4, 'BNT'),
(5, 'BNT 2');

INSERT INTO DAILY_PROGRAM(TV_NO, MOVIE_ID, RATING)
VALUES(1, 1, 5),
(1, 2, 1),
(2, 2, 2),
(2, 3, 3),
(3, 3, 4),
(3, 4, 4),
(4, 4, 5),
(4, 5, 5),
(4, 2, 5),
(5, 1, 2),
(5, 5, 3),
(5, 3, 5);

INSERT INTO DAILY_PROGRAM(TV_NO, MOVIE_ID, RATING)
VALUES (5, 1, 2),(1, 1, 5);

--4
DELETE FROM MOVIES WHERE MOVIE_ID = 1;

-- check if the movie with ID is also deleted in the movie column
SELECT * FROM DAILY_PROGRAM;

--5
SELECT m.NAME, dp.RATING FROM MOVIES m join DAILY_PROGRAM dp ON m.MOVIE_ID = dp.MOVIE_ID ORDER BY m.NAME ASC;

--6
SELECT m.NAME, count(dp.MOVIE_ID) FROM MOVIES m left join DAILY_PROGRAM dp ON m.MOVIE_ID = dp.MOVIE_ID
GROUP BY m.NAME;

--7
SELECT tvs.NAME, AVG(dp.RATING) FROM TV_CHANNELS tvs join DAILY_PROGRAM dp ON tvs.TV_NO = dp.TV_NO
GROUP BY tvs.NAME
HAVING  AVG(dp.RATING) >= 3;