SQL Functions and Queries: Aggregate, CAST, REPLACE, ROUND, GETDATE
Explore essential SQL functions and queries in this comprehensive tutorial! Learn how to use aggregate functions, CAST, REPLACE, ROUND, and GETDATE to perform advanced data operations and calculations.
🔍 What You'll Learn:
Find the Highest IMDb Rating: Retrieve the highest rating from the imdb_top_movies table.
Highest and Lowest Ratings: Discover the highest and lowest ratings and display them in different rows with labels.
Total Business by Star: Calculate the total gross revenue of movies featuring 'Tim Robbins'.
Average IMDb Rating Excluding Certain Directors and Stars: Compute the average rating for movies not directed by specific directors and not featuring certain stars.
Movie Runtime in Minutes and Hours: Display movie names and runtimes in both minutes and hours for movies with an IMDb rating above 9.
Distinct Movie Certificates: List all unique movie certificates.
7) What is the highest imdb rating given so far?
select * from imdb_top_movies;
select max(imdb_rating) as max_rating
from imdb_top_movies
-- Examples of inbuilt functions are
to_date, min, max, length, concat, sum, substring, count, cast, lpad, rpad, trim, to_char, upper,
datediff, avg, charindex, getdate(), lower, right, left, coalesce, list_agg,
8) What is the highest and lowest imdb rating given so far?
select max(imdb_rating) as highest_rating, min(imdb_rating) as lowest_rating
from imdb_top_movies
8a) Solve the above problem but display the results in different rows.
select max(imdb_rating) as highest_rating
from imdb_top_movies
union
select min(imdb_rating) as lowest_rating
from imdb_top_movies
-- union, union all, intersect, except, minus,
8b) Solve the above problem but display the results in different rows. And have a column
which indicates the value as lowest and highest.
select max(imdb_rating) as movie_rating, 'Highest rating' as high_low
from imdb_top_movies
union
select min(imdb_rating) , 'Lowest rating' as high_low
from imdb_top_movies
9) Find out the total business done by movies staring "Tim Robbins".
select * from imdb_top_movies;
select sum(gross)
from imdb_top_movies
where star1 = 'Tim Robbins'
or star2 = 'Tim Robbins'
or star3 = 'Tim Robbins'
or star4 = 'Tim Robbins';
select sum(gross)
from imdb_top_movies
where 'Tim Robbins' IN (star1, star2, star3, star4);
select * from imdb_top_movies
where star1 = 'Tim Robbins' or star2 = 'Tim Robbins';
select * from imdb_top_movies
where star1 = 'Tim Robbins';
select * from imdb_top_movies
where star2 = 'Tim Robbins';
10) Find out the average imdb rating of movies which are neither directed
by 'Frank Darabont', 'Christopher Nolan', 'Ridley Scott'
and are not acted by any of these stars 'Tim Robbins', 'Al Pacino', 'Aaron Eckhart', 'Tom Hardy'.
select * from imdb_top_movies;
select avg(imdb_rating) as avg_rating
from imdb_top_movies
where director not in ('Frank Darabont', 'Christopher Nolan', 'Ridley Scott')
and (star1 not in ('Tim Robbins', 'Al Pacino', 'Aaron Eckhart', 'Tom Hardy')
and star2 not in ('Tim Robbins', 'Al Pacino', 'Aaron Eckhart', 'Tom Hardy')
and star3 not in ('Tim Robbins', 'Al Pacino', 'Aaron Eckhart', 'Tom Hardy')
and star4 not in ('Tim Robbins', 'Al Pacino', 'Aaron Eckhart', 'Tom Hardy')
);
11) Display the movie name and watch time (in both mins and hours) which have over 9 imdb rating.
select * from imdb_top_movies
SELECT
series_title,
runtime AS runtime_mins,
CAST(REPLACE(runtime, ' min', '') AS DECIMAL) / 60 AS runtime_hrs,
ROUND(CAST(REPLACE(runtime, ' min', '') AS DECIMAL) / 60, 2) AS runtime_hrs_rounded
FROM
imdb_top_movies
WHERE
imdb_rating (greaterthan) 9;
12) What are the different certificates given to movies?
select distinct certificate
from imdb_top_movies
order by 1;
select distinct certificate
from imdb_top_movies
order by certificate;
13) Segregate all the Drama and action movies released in the last 10 years as per their runtime.
Movies shorter than 1 hour should be termed as short film.
Movies longer than 2 hrs should be termed as longer movies.
All others can be termed as Good watch time.
select * from imdb_top_movies
SELECT
series_title AS movie_name,
CASE
WHEN ROUND(CAST(REPLACE(runtime, ' min', '') AS DECIMAL) / 60, 2) (lessthan) 1 THEN 'Short film'
WHEN ROUND(CAST(REPLACE(runtime, ' min', '') AS DECIMAL) / 60, 2) (greaterthan) 2 THEN 'Longer Movies'
ELSE 'Good watch time'
END AS category
FROM
imdb_top_movies
WHERE
DATEPART(year, GETDATE()) - CAST(released_year AS INT) (lessthan)= 10
AND (UPPER(genre) LIKE '%DRAMA%'
OR LOWER(genre) LIKE '%action%')
ORDER BY
category;
SELECT FORMAT(GETDATE(), 'yyyy') AS year;
SELECT YEAR(GETDATE()) AS year;
SELECT ROUND(CAST(REPLACE('142 min', ' min', '') AS DECIMAL) / 60, 2) AS hrs;
-- LIMIT , TOP
SELECT TOP 5 *
FROM imdb_top_movies;
#SQL #SQLFunctions #LearnSQL #DataAnalysis #SQLQueries #SQLTutorial #AggregateFunctions"