Databases | SQL | Oracle | MS Access Answers

Polly Pipe is a water sports provider and installer. Polly Pipe is focused in placing aquariums at business customers. Customers can request several installations, but each installation is tailor-made for a specific customer. Facilities are classified by type. One or more employees are assigned to each facility. The facilities use equipment. Below are examples of paper records.

Staff Management Record

Staff Number| Name| Type|

SHA1| Dave Clark| Plumber

Equipment Type Table

Type| Equipment|

Tanks| 20 gallon tank, 50 gallon tank

Instillation Management Form

Installation ID| Installation Type| Installation Name and Address| Customer| Equipment| Types of Staff Required| Period of Staff assignment|

234| Freshwater Tropical| Oak House| 17 Wroxton Road, Hertfordshire| John Carter| 2 air pumps, 200 gallons fish tank| 1 x Carpenter,1 x Aquatics installer| From 2012/12/10|

ERD Please!!

Polly Pipe is a water sports provider and installer. These necessities are defined in this scenario and below are samples of the paper records that the Polly Pipe preserves. Polly Pipe is focused in placing aquariums at business customers. Customers can request several installations, but each installation is tailor-made for a specific customer. Facilities are classified by type. One or more employees are assigned to each facility. Because these facilities are often very large. The facilities use equipment such as aquariums, air pumps and thermostats.

Below are examples of paper records that Polly Pipe currently maintains.

Staff Management Record

Staff Number| Name| Type|

Equipment Type Table

Type| Equipment|

Instillation Management Form

Installation ID| Installation Type| Installation Name and Address| Customer| Equipment| Types of Staff Required| Period of Staff assignment|

You have a VMware virtual machine on a ESX host with a production SQL database server. During a maintenance you cloned this virtual machine. Users started complaining that their saved work results in the database randomly disappear after they relog to the database, then sometimes appear back. Why does it happen?

Getting all the channel_ids that uploaded at least one video in "AI/ML" or "Robotics" technologies between 2018 and 2021.

Output Table:

channel_id
    351
    353
    364
    365
    377

We need to count the users who has the premium membership, but you are trying to sum instead of count(). And also in the WHERE clause, we need to check premium users have premium_membership value as 1 , and the value 0 is considered as non-premium user.

Get the number of male and female premium users in the platform.

Expected Output Format

gender total_users

F...

M...

18. Get the top 3 genre_ids that are liked by the users in India in the year 2018.

Note:

. Consider the name of the country as

"INDIA"

Consider reaction_type LIKE as liked. If a video belongs to 3 genres, then the like is counted in all the 3 genres.

• Sort the output in the descending order of no_of_likes, and then in the ascending order of the genre_id.

Expected Output Format

genre id

no of Likes

Get top 5 genres in the platform with most positive user activity, i.e., the genres with videos having more number of likes.

Note:

Consider the reaction_type LIKE as liked.

• If a video belongs to 3 genres, then the likes of the video is counted in all the 3 genres.

• Sort the output in the descending order of no_of_likes, and then in the ascending order of the genre_type.

Expected Output Format

genre id

genre typa

no of likes

Get all the potential users. Fetch the user_ids who liked at least 2 videos published by "Disney" channel, and who did not subscribe to the channel (channel_id = 352).

Note:

. Consider reaction_type LIKE as liked.

• Sort the output in the descending order of no_of_likes, and then in the ascending order of potential_user_id.

Expected Output Format

potential_user_id, no_of_likes

15. Get all the user_ids who liked at least 5 videos published by "Tedx" channel.

Note:

. Consider reaction_type LIKE as liked.

• Sort the output in the descending order of no_of_likes, and then in the ascending order of active_user_id.

Expected Output Format

active_user_id

no_of_likes

14. Get the active users of the platform. Consider the users who liked at least fifty videos are considered active users.

Note:

. Consider reaction_type LIKE as liked.

• Sort the output in the ascending order of user_id.

Expected Output Format

active_user_id

no_of_likes