Answer to Question #185017 in Databases | SQL | Oracle | MS Access for bhadra

Question

Get all the distinct user_ids who liked at least one video uploaded by Android Authority Channel (channel__id = 364) but didn't like the video uploaded by Tech savvy channel with video_id = 1005.

Note:

• Consider reaction_type LIKE as liked
• Sort the output in the ascending order of user_id.

Expected Output Format

user_id

sir plz explain sql query

Answer 1

/*

Assuming there are three related tables in your database. 

Table 1: VIDEOS - which includes the video_ids and other details of videos stored by channels. 

Table 2: CHANNELS - which includes the channel__ids and other details about the channel providers. 

Table 3: USERS - which includes the user_ids, reaction_type, and other details about users watching with the videos. 

To get all distinct users who liked at least one video uploaded by Android Authority Channel (channel__id = 364)

but didn't like the video uploaded by Tech savvy channel with video_id = 1005

Your query will be: 

SELECT DISTINCT user_id

FROM USERS, CHANNEL, VIDEOS

WHERE CHANNEL.channel__id = 364 AND USERS.reaction_type = 'LIKE' AND VIDEOS.video_id != 1005

ORDER BY user_id ASC

We are using the function DISTINCT to return unique users.

SELECT function is used to select the column user_id

FROM command specifies the tables to select data from; USERS, CHANNEL, VIDEOS

WHERE command allows us to specify the condition, in this case we want to specify users who liked at least one video uploaded by Android Authority Channel (channel__id = 364)

but didn't like the video uploaded by Tech savvy channel with video_id = 1005

Notice; I am using the dot notation to specify which table the column came from.

I use ORDER BY to specify the column to use when sorting the result

ASC command specifies Ascending order.

*/

SELECT DISTINCT user_id

FROM USERS, CHANNEL, VIDEOS

WHERE CHANNEL.channel__id = 364 AND USERS.reaction_type = 'LIKE' AND VIDEOS.video_id != 1005

ORDER BY user_id ASC