Scribd
Upload
9 views2 pages

11MARCH Assignment

The document contains SQL queries for various tasks including calculating average prices for product categories, counting user actions from log data, and finding average movie ratings. It also discusses the correctness of a query related to employees in departments with a single employee and explains the behavior of different count functions in SQL. Additionally, it lists upcoming topics such as subqueries, set operators, joins, and window functions.

Uploaded by

harishkumarji.b
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
9 views2 pages

11MARCH Assignment

The document contains SQL queries for various tasks including calculating average prices for product categories, counting user actions from log data, and finding average movie ratings. It also discusses the correctness of a query related to employees in departments with a single employee and explains the behavior of different count functions in SQL. Additionally, it lists upcoming topics such as subqueries, set operators, joins, and window functions.

Uploaded by

harishkumarji.b
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

Product Categories:

Consider a table named products with columns:


product_id, category_id, and price.
Write a query to find the average price for each product category.

select category_id,avg(price) from table group by category_id;

====================================
Log Analysis:
You have a table named log_data with columns:
user_id, action_type, and timestamp.
u01, login, 20241217 11.15:000
u01 changed pwd
u01 loogeout

Write a query to count the number of actions performed by each user,


and display the results in descending order of action count.

uid count_of_action
u02 5
u01 3
u03 1

select user_id, count(action type) from log_data


group by user_id
order by count(action type) desc ;

where count(action type)=1 X wrong


having count(action type)=1; correct

grouped by user_id,
and display the results in descending order of action count.

select user_id,count(action_type) from table group by user_id


order by count(action_type) desc
===============
q>Movie Ratings:
Given a table named movie_ratings with columns:
movie_id, user_id, and rating,movie_title.
m01,u01,4, 'Dr. Strange'
m01,u02,5, 'Dr. Strange'
m01,u03,2, 'Dr. Strange'

Write a query to find the average rating for each movie,


along with the movie title

op
movie_id avg_rating
m01 3.6

sel movie_title,movie_id,avg(rating) from movie_ratings


group by movie_title, movie_id
===========================

Q> detail of those employee who belongs to


those departments in which single employee also working..

select employee_id,employee_name from employess where department_id in


(select department_id from department group by deparment_id having count(1)=1)is
this qurey correct mam...

one more thing will count(1) consider nulls


select
count(*), count(1), count(country), count(distinct country)
from agents_71;

o/p

12 12 0 0
bcz country column is null so count is 0
===================================================
Upcoming topics
subquery
set operators
joins
window functions

You might also like