Oracle

Objective:

You are required to address a set of Data Query Language (DQL) tasks for a Facebook Application System using the tables provided. Each task focuses on different aspects of querying data from the system using SELECT statements with various conditions, functions, and joins.

1. SELECT Command

• Select all columns from the USER table.
• Select the first_name, last_name, and email of all users.
• Select the content and created_at from the POST table.
• Select the status and friendship_id from the FRIEND table.

2. WHERE Command

• Select all users where date_of_birth is before '1990-01-01'.
• Select all posts where content contains the word 'announcement'.
• Select all friends where status is 'Accepted'.
• Select all comments where created_at is after '2023-01-01'.

3. ORDER BY Command

• Select all users and order them by last_name in ascending order.
• Select all posts and order them by created_at in descending order.
• Select all events and order them by event_date in ascending order.
• Select all advertisements and order them by posted_at in descending order.

4. LIMIT and ROWNUM Command

• Select the first 10 users from the USER table using ROWNUM.
• Select the first 5 posts from the POST table.
• Select the top 3 most recent events ordered by event_date.
• Select the first 5 friends, ordered by friendship_id.

5. DISTINCT Command

• Select distinct status from the FRIEND table.
• Select distinct reaction_type from the REACTION table.
• Select distinct page_name from the PAGE table.
• Select distinct subscription_type from the SUBSCRIPTION table.

6. GROUP BY Command

• Group by status from the FRIEND table and count the number of friendships per status.
• Group by reaction_type from the REACTION table and count the number of reactions per type.
• Group by subscription_type from the SUBSCRIPTION table and calculate the number of users per subscription type.
• Group by user_id from the POST table and calculate the total number of posts per user.

7. HAVING Command

• Select reaction_type from the REACTION table and group by it, having more than 5 reactions per type.
• Group by user_id from the POST table and calculate the total posts per user, having more than 5 posts per user.
• Group by status from the FRIEND table, having more than 5 friends with 'Accepted' status.
• Group by subscription_type from the SUBSCRIPTION table, having more than 3 users in the 'Premium' category.

8. INNER JOIN Command

• Select all posts along with the user details using INNER JOIN between the POST and USER tables.
• Select all comments along with the post and user details using INNER JOIN between COMMENT, POST, and USER tables.
• Select all reactions and their corresponding posts and users using INNER JOIN between REACTION, POST, and USER.
• Select all advertisements and the corresponding page details using INNER JOIN between ADVERTISEMENT and PAGE.

9. LEFT JOIN Command

• Select all users and their posts using LEFT JOIN, including users without any posts.
• Select all users and their comments using LEFT JOIN, including users without any comments.
• Select all pages and their corresponding advertisements using LEFT JOIN, including pages without advertisements.
• Select all events and their corresponding users using LEFT JOIN, including events without users.

10. COUNT, SUM, AVG Command

• Count the total number of users in the USER table.
• Count the number of distinct reaction_type in the REACTION table.
• Calculate the sum of all posts for each user in the POST table.
• Calculate the average number of friends per user in the FRIEND table.

11. CASE Command

• Select all posts and use a CASE statement to categorize them as 'Recent' if the created_at is within the last 30 days, otherwise 'Old'.
• Select all users and categorize them as 'Active' if they have posted within the last 7 days, otherwise 'Inactive'.
• Use a CASE statement to show the friendship status as 'Friends' if status is 'Accepted', otherwise 'Not Friends'.
• Use a CASE statement in the REACTION table to categorize reactions as 'Positive' if reaction_type is 'Like' or 'Love', otherwise 'Neutral'.

12. EXISTS and NOT EXISTS Command

• Select all users where a post exists in the POST table using EXISTS.
• Select all users where no post exists in the POST table using NOT EXISTS.
• Select all posts where comments exist using EXISTS.
• Select all events where no advertisements exist using NOT EXISTS.

13. SUBQUERY Command

• Select all users whose user_id is in the result of a subquery selecting user_id from the POST table where created_at is within the last 30 days.
• Select all posts where the post_id is in the result of a subquery selecting post_id from the REACTION table where reaction_type is 'Like'.
• Select all friends where the friend_id is in the result of a subquery selecting friend_user_id from the FRIEND table where status is 'Pending'.
• Select all comments where the comment_id is in the result of a subquery selecting comment_id from the COMMENT table where content contains 'great'.

14. RANK and DENSE_RANK Command

• Rank users based on the number of posts they have made using RANK().
• Use DENSE_RANK() to rank users based on the number of friends they have.
• Rank posts by their created_at date using RANK().
• Use DENSE_RANK() to rank reactions based on the number of times they appear.

15. PIVOT and UNPIVOT

• Pivot the REACTION data by reaction_type and post_id.
• Unpivot the comment details to show the content and created_at for each comment by user.
• Pivot the post data by user_id and created_at.
• Unpivot user details by first_name, last_name, and email.

16. UNION and UNION ALL Command

• Select all posts from the POST and REACTION tables using UNION.
• Select all users from different regions using UNION ALL.
• Use UNION to combine active and inactive users.
• Use UNION ALL to select all events and advertisements.

17. COALESCE, NVL, NULLIF Command

• Select all users and use COALESCE to replace null phone_number values with 'No Phone'.
• Use NVL to replace null reaction_type in the REACTION table with 'No Reaction'.
• Use NULLIF to compare user_id and friend_id in the FRIEND table and return NULL if they are the same.
• Select all comments and use COALESCE to replace null content with 'No Comment'.

18. STRING Functions

• Select all users' first_name in uppercase using the UPPER() function.
• Select all posts' content in lowercase using the LOWER() function.
• Use CONCAT() to combine the first_name and last_name of users.
• Select all users' email and find the length of the string using LENGTH().

19. DATE Functions

• Select all users and show their date_of_birth formatted as 'YYYY-MM-DD' using TO_CHAR().
• Add 1 year to all created_at values in the POST table using ADD_MONTHS().
• Subtract 1 month from all event_date values using MONTHS_BETWEEN().
• Select all users and extract the year from their date_of_birth using EXTRACT().

20. NUMERIC Functions

• Select the total number of friends each user has and round it to the nearest integer using ROUND().
• Select all reaction_type from the REACTION table and use CEIL() to round up the count.
• Use FLOOR() to round down the number of posts in the POST table.
• Select the highest reaction_id from the REACTION table using MAX().

21. CAST and CONVERT Command

• Select all users and cast the user_id as a string using CAST().
• Convert reaction_id in the REACTION table to NUMBER.
• Cast post_id in the POST table to VARCHAR.
• Convert the created_at from the POST table into TEXT.

22. JSON Select

• Select specific keys from a JSON column in the PAGE table.
• Parse a JSON column from the USER table to extract profile details.
• Use JSON_VALUE to select a specific key from a JSON column in the PAGE table.

23. CONCAT and CONCAT_WS Functions

• Use CONCAT() to join first_name and last_name with a space in between in the USER table.
• Use CONCAT_WS() to combine the page_name and created_at with a comma in the PAGE table.
• Use CONCAT() to combine the content and created_at from the POST table.
• Use CONCAT_WS() to combine first_name, last_name, and email from the USER table.

24. LIKE and NOT LIKE

• Select users whose email contains 'facebook.com' using LIKE.
• Select posts where content contains 'photo' using LIKE.
• Select users whose last_name does not start with 'A' using NOT LIKE.
• Select pages where page_name contains 'Business' using LIKE.

25. EXISTS and NOT EXISTS

• Select all users where posts exist in the POST table using EXISTS.
• Select all posts where comments exist using EXISTS.
• Select all users where no friends exist using NOT EXISTS.
• Select all pages where no advertisements exist using NOT EXISTS.

26. JOIN Commands

• Select all users and their corresponding posts using INNER JOIN between USER and POST.
• Select all comments and their corresponding posts using INNER JOIN between COMMENT and POST.
• Select all reactions and the corresponding posts using INNER JOIN between REACTION and POST.
• Select all users and their friends using INNER JOIN between USER and FRIEND.

27. BETWEEN Command

• Select all users where the date_of_birth is between '1980-01-01' and '2000-12-31'.
• Select all posts where the created_at is between '2023-01-01' and '2023-12-31'.
• Select all friends where the friendship_id is between 1 and 1000.
• Select all events where the event_date is between '2023-05-01' and '2023-10-31'.

28. IN and NOT IN Command

• Select all users where the user_id is in (1, 2, 3, 4).
• Select all posts where the post_id is in (101, 202, 303).
• Select all reactions where the reaction_type is not in ('Haha', 'Angry').
• Select all friends where the status is in ('Accepted', 'Pending').

29. ARRAY Command

• Select all reaction_type and convert it into an array using COLLECT().
• Use UNNEST() to expand arrays from the REACTION table.
• Convert page names into an array using COLLECT() in the PAGE table.
• Use ARRAY functions to select and manipulate data from the COMMENT table.

GOOD LUCK WITH YOUR ASSIGNMENT!!!

Don't forget to contact us if you need any further assistance with your assignments, and most importantly, for a manual review and approval of your work.