Question

Define an SQL view JokesNum that gives the number of jokes each user posts on each day.

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

Create view JokesNum as Select userid, day(cast(posttime as date)) ,count(Jokeid) from Jokes inner join User on Jokes.authorid = User.userid group by userid, day(cast(posttime as date)) ;

Do ask if any doubt. Please upvote.