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.

1. 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.
2. 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.)
3. 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.)
4. 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.
5. 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.