Oracle

Objective:

You are required to address a set of Data Manipulation Language (DML) tasks for a Facebook Application System. Each query focuses on distinct DML tasks, including inserting, updating, deleting, using subqueries, joins, and working with relationships between tables. The tables and relationships have already been defined based on the DDL from your chapter 5 assignments.

Requirements:

You will need to complete at least 26 different DML tasks, covering inserting, updating, deleting, working with joins, subqueries, JSON, and complex data manipulations.

Question 1: Insert Data into all Tables

Write SQL queries to populate each table created in the Chapter 5 assignment with at least 10 rows of data.

Question 2: Insert a New User

Write a DML SQL statement to insert a new record into the USER table with the following details:

• first_name: 'John'
• last_name: 'Doe'
• email: 'john.doe@example.com'
• password: 'password123'
• date_of_birth: '1990-05-15'

Question 3: Insert Multiple Posts

Insert two new records into the POST table with the following details, using single sql statement:

• First post: content: 'My first post on Facebook!', user_id: 1
• Second post: content: 'Loving this new platform!', user_id: 1

Question 4: Insert a Record into the PAGE Table with Default Values

Insert a record into the PAGE table using default value for created_at.

Question 5: Insert a Friendship

Write a DML SQL statement to insert a friendship between two users (user_id = 1 and friend_id = 2) into the FRIEND table with the status set to "Accepted".

Question 6: Select Into Backup Table

Write a SQL statement to create a backup of all rows in the POST table into a new table called POST_BACKUP.

Question 7: Insert Data Using Subquery

Write a SQL statement to insert a new comment into the COMMENT table. Use a subquery to get the user_id for the user whose email is 'john.doe@example.com' and post_id for the post with content 'My first post on Facebook!'.

Question 8: Update User Information

Write a SQL statement to update the email and password for the user with user_id = 1.

Question 9: Update with Subquery

Write a SQL statement to update the status of all friendships where the user has a post with the content 'Loving this new platform!' to "Accepted". Use a subquery to find the user_id.

Question 10: Update with JOIN

Write a SQL statement to update the content field of all posts where the user_id = 1 and last_name = 'Doe'. Use a join between the USER and POST tables.

Question 11: Perform an UPSERT on USER Table

Write a SQL statement to insert a new user or update the email if the user_id already exists in the USER table.

Question 12: Delete a Post

Write a SQL statement to delete a post where post_id = 3.

Question 13: Delete with JOIN

Write a SQL statement to delete all comments where the post was created by the user with user_id = 1. Use a JOIN between the POST and COMMENT tables.

Question 14: Truncate the AUDIT_LOG Table

Write a SQL statement to truncate the ADVERTISEMENT table, removing all rows without generating individual delete triggers.

Question 15: Add JSON Column to POST Table

Write a SQL statement to add a meta_data column of type JSON to the PAGE table.

Question 16: Insert into JSON Column

Write a SQL statement to insert JSON data into the meta_data column of the PAGE table. The JSON should contain keys like "likes_count", "comments_count", and "shares_count".

Question 17: Update JSON Column

Write a SQL statement to update the meta_data JSON column of the PAGE table by incrementing the "likes_count" for a specific post.

Question 18: Delete JSON Data

Write a SQL statement to remove the "shares_count" field from the meta_data JSON column for a specific PAGE.

Question 19: Insert into REACTION Table

Write a SQL statement to insert a reaction into the REACTION table, where reaction_type = 'Like' for the post with post_id = 1 and user_id = 1.

Question 20: Update a Friendship Status

Write a SQL statement to update the status of a friendship where user_id = 1 and friend_id = 2 to "Blocked".

Question 21: Insert a New Page

Write a SQL statement to insert a new page into the PAGE table:

• page_name: 'Tech Innovators'
• owner_id: 1

Question 22: Insert a Message

Write a SQL statement to insert a new message into the MESSAGE table between two users:

• sender_id: 1
• receiver_id: 2
• message_content: 'Hello, how are you?'

Question 23: Insert into EVENT Table

Write a SQL statement to insert a new event into the EVENT table:

• event_name: 'Facebook Conference'
• event_date: '2024-07-01'
• created_by: 1

Question 24: Update Page Name

Write a SQL statement to update the page_name for the page with page_id = 1 to 'Innovative Tech'.

Question 25: Insert into ADVERTISEMENT Table

Write a SQL statement to insert a new advertisement into the ADVERTISEMENT table:

• ad_content: 'Check out our latest product!'
• page_id: 1

Question 26: Insert Multiple Users

Insert two new records into the USER table with the following details:

• First user: first_name: 'Alice', last_name: 'Smith', email: 'alice.smith@example.com', password: 'alice123', date_of_birth: '1985-09-12'
• Second user: first_name: 'Bob', last_name: 'Johnson', email: 'bob.johnson@example.com', password: 'bob123', date_of_birth: '1988-11-22'

Question 27: Delete with INNER JOIN

Write a SQL statement to delete all reactions for posts created by the user with user_id = 1, using an INNER JOIN between the POST and REACTION tables.

Question 28: SUPER Complex INSERT.

• Create a DATE_DIM table with columns: date_id, calendar_date, year, month, day_of_the_month, week_day_number, week_day_name, yearly_week_number, month_start_date_flag, month_end_date_flag, year_start_date_flag, year_end_date_flag, holiday_flag
• The date_id should be populated in the YYYYMM format (e.g., 202401).
• Populate the DATE_DIM table with data for current year and the next year.

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.

Sample ERD Data Model for Facebook Application

To gain complete access, login with gmail or outlook, no need of signup. click here

DML ASSIGNMENT2 - Facebook Application System