Question

CREATE TABLE youtubevideos(

url VARCHAR(150),

title VARCHAR(50),

description VARCHAR(200),

comid INTEGER NOT NULL,

postuserVARCHAR(50) NOT NULL,

postdate DATE,

PRIMARY KEY (email),

FOREIGN KEY (comid) REFERENCES Comedians(comid),

FOREIGN KEY (postuser) REFERENCES Users(email));

CREATE TABLE Users(

email VARCHAR(50),

password VARCHAR(50),

firstname VARCHAR(50),

lastname VARCHAR(50),

gender CHAR(1),

age INTEGER,

PRIMARY KEY (email));

CREATE TABLE Comedians(

comid INTEGER,

firstname VARCHAR(50),

lastname VARCHAR(50),

birthday DATE,

VARCHAR(50),

PRIMARY KEY(comid));

CREATE TABLE Reviews(

reviewid INTEGER NOT NULL AUTO_INCREMENT,

remark VARCHAR(100),

rating CHAR(1), //P.F.G.E

author VARCHAR(50) NOT NULL,

youtubeid VARCHAR(150) NOT NULL,

PRIMARY KEY (reviewid),

FOREIGN KEY (author) REFERENCES Users(email),

FOREIGN KEY (youtubeid) REFERENCES youtubevides(url),

CONSTRAINT rating,

CHECK rating ['P','F','G','E']

)

CREATE TABLE youtubetags(

url VARCHAR(150),

tag VARCHAR(50),

PRIMARY KEY (url, tag))

CREATE TABLE IsFavorite(

email VARCHAR(50),

comid INTEGER,

PRIMARY KEY (email, comid),

FORIGN KEY (email REFERENCES Users(email),

FOREIGN KEY (comid) REFERENCES Comedians(comid))

1. Return the names of the comedians that have youtubes but no reviews.
2. Return the URLs of the youtubes that have some reviews and each review is excellent.
3. Return the URLs of the youtubes that have some reviews and each review is given by some female user under 20 (not including 20).
4. Return the URLs of the youtubes that have no tags

Answer 1

I have given all the four queries. Pardon for any spelling mistakes. Also do comment if face any issue with any of the queries. I will surely help.

Also upvote if i answered your question.

Return the names of the comedians that have youtubes but no reviews.
select c.firstname, c.lastname from Comedians c inner join youtubevideos y on c.comid = y.comid and y.url not in (select youtubeid from Reviews);

Return the URLs of the youtubes that have some reviews and each review is excellent.
select y.url from youtubevideos y inner join Reviews r on y.url = r.youtubeid and r.rating = 'E';

Return the URLs of the youtubes that have some reviews and each review is given by some female user under 20 (not including 20).
select r.youtubeid from Reviews r inner join Users u on r.author = u.email and lower(u.gender) = 'f' and u.age < 20;

Return the URLs of the youtubes that have no tags
select url from youtubevideos where url not in (select url from youtubetags);