Question

Write an SQL query to return the users who posted the most number of jokes on 1/1/2019.

Database:

create table User(
userid varchar(30) not null,
password varchar(30),
firstname varchar(30),
lastname varchar(50),
email varchar(50),
gender char(1),
age integer,
banned boolean,
primary key(userid),
unique(email));

create table MyFriends(
userid varchar(30),
friendid varchar(30),
primary key(userid,friendid),
foreign key(userid) references User,
foreign key(friendid) references User(userid));

Create table Jokes(
Jokeid integer,
title varchar(100),
description varchar(255),
authorid varchar(30) not null,
primary key(jokeid),
posttime Datetime,
foreign key(authorid) references User(userid));

create table JokeTag(
jokeid integer,
tag varchar(50),
primary key(jokeid,tag),
foreign key(jokeid) references Jokes);

create table MyFavoriteJokes(
userid varchar(30),
jokeid integer,
primary key(userid, jokeid),
foreign key(userid) references User,
foreign key(jokeid) references Jokes);

create table Reviews(
reviewid integer,
score varchar(50),
remark varchar(255),
SUBMITtIME datetime,
userid varchar(30) not null,
jokeid integer not null,
primary key(reviewid),
foreign key (userid) references User,
foreign key (jokeid) references Jokes);

Answer 1

SQL: create table Reviews(
reviewid integer,
score varchar(50),
remark varchar(255),
SUBMITtIME datetime,
userid varchar(30) not null,
jokeid integer not null,
primary key(reviewid),
foreign key (userid) references User,
foreign key (jokeid) references Jokes);
Trigger
create trigger review
on Reviews
for
insert,update
as
print'a user cannot provide more than five reviews per day'
rollback;