CSCI 370 Spring 2024 - Assignment 5
Due: 11:59pm, 23 February 2024, Friday
Assignment Information
The following relational schema (primary keys are underlined) are
used by the Forum Management
as described in Assignment 1.
Users(username, nickname, contactInfo, signupDate, status)
Hosts(username)
Channels(channelName, description, creator, createTime)
ActiveSubscriptions(username, channel, subscribeTime)
Messages(messageID, messageBody, poster, channel, postTime,
replyToMessage)
SubscribeHistory(username, channel, subscribeTime,
unsubscribeTime)
This assignment is based on the above relational schema.
Many more details, including constraints, data types, etc,
are captured in the SQL definitions
of these tables.
You may use the supplied table definitions to create empty tables.
You will then need to populate the tables (insert sample data)
yourself in order to test your SQL queries.
Your Tasks:
Express each of the following queries in a single SQL query
against the relational schema defined above.
- The forum wants to know how active this forum is at this moment.
Write a SQL query that returns a table with exactly two tuples:
one tuple displays the total number of active users
(those users whose status is "Active"), and the other tuple displays
the total number of channels that are active in year 2023
(i.e., there is at least one message posted in those channels
in year 2023).
The result table should have exactly two columns: one column
displays the numerical result as described in the previous paragraph,
and the other column displays
the meaning of the numerical result.
Because both tuples consist a string and a number,
they are union compatible.
- List the name and contact information of each user
who is actively subscribed to the most number of channels
compared with other users. (Note that there could be
multiple users who equally subscribe to the most number
of channels currently, and all of these users and only these users
should be in the result.)
- For each user who posted more than 20 reply messages (i.e.,
the replyToMessage is not null)
in a channel, list the username and nickname of the user,
the name and description of such a channel, and
the exact number of all (original and reply) messages posted by this user
in this channel.
Order the query result in ascending order of the usernames first,
then in descending order of the total number of posted messages.
(Hint: count(col) will ignore the null values in col,
while count(*) counts rows thus will count the null values in col,
where col is a column name.)
- For each channel created by a host with the username "hijkstra",
list the channel's name and create time, and the
number of messages posted in each such channel in the year 2023.
Even if there is no messages posted in such a channel in the year 2023,
the qualified channel should still appear in the result with the number
of posted messages shown as 0.
- List the username, nickname and status of the newest user (or users
if they joined the forum equally late), and the name of each channel
in which such a user posted at least one message.
The "newest user" is defined
as the user whose signed up date is the largest.
(There shouldn't be any duplicates in the result.)
How to Submit
You are expected to include all queries in a TEXT file.
Make your queries logically correct, easy to read
and easy to understand.
Make sure that your SQL queries can be successfully executed
by Oracle database engine.
You can submit your assignment solutions using one of the following two ways:
- Submit a hard copy before the due date/time.
- Login to VIU Learn, upload all your solution files to A5 under
CSCI 370's assessment and assignments tab.
Last updated: February 14, 2024