Important queries in SQL

In SQL you can make queries in number of ways ,though we can break complex codes into small readable and calculated parts.

There are a number of ways you can make queries in SQL, though we are suggesting a few ways through which complex codes can be broken into small, readable, and calculated parts.

First Case for “WITH” clause

If a hypothetical table is given like this, where top ratings are given to Hindi movies and their number of views worldwide. Read the following table carefully, where we list movies with the top 10 ratings and views.

MoviesRatingViews
1. Ramayana: The Legend of Prince Rama (1993)8.515432
2. Rocketry: The Nambi Effect (2022)8.413783
3. 777 Charlie (2022)8.413129
4. Hanky Panky (1979)8.412845
5. Nayakan (1987)8.412275
6. Anbe Sivam (2003)8.411961
7. Jai Bhim (2021)8.410032
8. Pariyerum Perumal (2018)8.48954
9. 3 Idiots (2009)8.48231
10. Manichithrathazhu (1993)8.47530

Business Question 1

Give us the table, where we want a list of movies with more than average number of views.

Explanation

The average number of views for all movies is 11417. Means we need a table with only those movies which are having views greater than 11417.

Code

Python
Python
Python
WITH temporary_frame as (select avg(Views) as avg_view FROM MOVIES) 
select * FROM MOVIES, 
temporary_frame where MOVIES.Views > temporary_frame.avg_view
MoviesRatingViews
1. Ramayana: The Legend of Prince Rama (1993)8.515432
2. Rocketry: The Nambi Effect (2022)8.413783
3. 777 Charlie (2022)8.413129
4. Hanky Panky (1979)8.412845
5. Nayakan (1987)8.412275
6. Anbe Sivam (2003)8.411961

Second case with clause

Let’s take another example where we are taking hypothetical data from a film to understand another use of the ”WITH” clause. We have a city-by-city collection of top hit movies from Indian cinema, as well as national collections.

MovieCityCity_Wise_CollectionCountrywide_CollectionBudgetVerdict
Baahubali 2 The Conclusion (2017)Mumbai1251.6421788.06250All Time Blockbuster
KGF Chapter 2 (2022)Mumbai845.61208100All Time Blockbuster
RRR (2022)Mumbai7981140550Blockbuster
Dangal (2016)Mumbai1449.212070.370All Time Blockbuster
Avengers End Game (2019)Mumbai13279189702500All Time Blockbuster
Sanju (2018)Mumbai411.95588.5100All Time Blockbuster
Tiger Zinda Hai (2017)Mumbai390.6558210Blockbuster
Baahubali 2 The Conclusion (2017)Delhi357.6121788.06250All Time Blockbuster
KGF Chapter 2 (2022)Delhi241.61208100All Time Blockbuster
RRR (2022)Delhi2281140550Blockbuster
Dangal (2016)Delhi414.062070.370All Time Blockbuster
Avengers End Game (2019)Delhi3794189702500All Time Blockbuster
Sanju (2018)Delhi117.7588.5100All Time Blockbuster
Tiger Zinda Hai (2017)Delhi111.6558210Blockbuster
Baahubali 2 The Conclusion (2017)Chennai178.8061788.06250All Time Blockbuster
KGF Chapter 2 (2022)Chennai120.81208100All Time Blockbuster
RRR (2022)Chennai1141140550Blockbuster
Dangal (2016)Chennai207.032070.370All Time Blockbuster
Avengers End Game (2019)Chennai1897189702500All Time Blockbuster
Sanju (2018)Chennai58.85588.5100All Time Blockbuster
Tiger Zinda Hai (2017)Chennai55.8558210Blockbuster

Let’s follow the given steps to study the business problem, code, and output of the table.

Business Question 2

Give us a table with minimum and maximum collections for each film, as well as cities.

Code

Create two custom views with City Wise Collection min and City Wise Collection max via using with nested “clause” statements.

SQL
SQL
SQL

WITH minimum_collection AS
  (SELECT Movie, MIN(City_Wise_Collection) AS min_cwc
   FROM movie_business
   GROUP BY Movie),

maximum_collection AS
 (SELECT Movie, MAX(City_Wise_Collection) AS max_cwc
  FROM movie_business
  GROUP BY Movie)

SELECT
  mb.City,
  mb.Movie,
  mb.City_Wise_Collection,
  mb.Countrywide_Collection,
  mb.budget,
  min.min_cwc,
  max.max_cwc

FROM movie_business mb
 JOIN minimum_collection min
 ON mb.Movie = min.Movie
 JOIN maximum_collection max
 ON mb.Movie = max.Movie;
CITYMOVIECITY_WISE_COLLECTIONCOUNTRYWIDE_COLLECTIONBUDGETMIN_CWCMAX_CWC
MumbaiDangal (2016)1449.212070.370207.031449.21
DelhiDangal (2016)414.062070.370207.031449.21
ChennaiDangal (2016)207.032070.370207.031449.21
MumbaiSanju (2018)411.95588.510058.85411.95
DelhiSanju (2018)117.7588.510058.85411.95
ChennaiSanju (2018)58.85588.510058.85411.95
MumbaiTiger Zinda Hai (2017)390.655821055.8390.6
DelhiTiger Zinda Hai (2017)111.655821055.8390.6
ChennaiTiger Zinda Hai (2017)55.855821055.8390.6
MumbaiBaahubali 2 The Conclusion (2017)1251.6421788.06250178.8061251.642
DelhiBaahubali 2 The Conclusion (2017)357.6121788.06250178.8061251.642
ChennaiBaahubali 2 The Conclusion (2017)178.8061788.06250178.8061251.642
MumbaiKGF Chapter 2 (2022)845.61208100120.8845.6
DelhiKGF Chapter 2 (2022)241.61208100120.8845.6
ChennaiKGF Chapter 2 (2022)120.81208100120.8845.6
MumbaiRRR (2022)7981140550114798
DelhiRRR (2022)2281140550114798
ChennaiRRR (2022)1141140550114798
MumbaiAvengers End Game (2019)13279189702500189713279
DelhiAvengers End Game (2019)3794189702500189713279
ChennaiAvengers End Game (2019)1897189702500189713279

How useful was this post?

Click on a star to rate it!

  • ANCOVA: Analysis of Covariance with python

    ANCOVA is an extension of ANOVA (Analysis of Variance) that combines blocks of regression analysis and ANOVA. Which makes it Analysis of Covariance.

  • Learn Python The Fun Way

    What if we learn topics in a desirable way!! What if we learn to write Python codes from gamers data !!

  • Meet the most efficient and intelligent AI assistant : NotebookLM

    Start using NotebookLM today and embark on a smarter, more efficient learning journey!

  • Break the ice

    This can be a super guide for you to start and excel in your data science career.

  • Tourism Trend Prediction

    After tourism was established as a motivator of local economies (country, state), many governments stepped up to the plate.

  • Sentiment Analysis Polarity Detection using pos tag

    Sentiment analysis can determine the polarity of sentiments from given sentences. We can classify them into certain categories.

  • For loop with Dictionary

    Traverse a dictionary with for loop Accessing keys and values in dictionary. Use Dict.values() and Dict.keys() to generate keys and values as iterable. Nested Dictionaries with for loop Access Nested values of Nested Dictionaries How useful was this post? Click on a star to rate it! Submit Rating

  • For Loops with python

    For loop is one of the most useful methods to reuse a code for repetitive execution.

  • Metrics and terminologies of digital analytics

    These all metrics are revolving around visits and hits which we are getting on websites. Single page visits, Bounce, Cart Additions, Bounce Rate, Exit rate,

  • Hypothesis Testing

    Hypothesis testing is a statistical method for determining whether or not a given hypothesis is true. A hypothesis can be any assumption based on data.

  • A/B testing

    A/B tests are randomly controlled experiments. In A/B testing, you get user response on various versions of the product, and users are split within multiple versions of the product to figure out the “winner” of the version.

  • For Loop With Tuples

    This article covers ‘for’ loops and how they are used with tuples. Even if the tuples are immutable, the accessibility of the tuples is similar to that of the list.

  • Multivariate ANOVA (MANOVA) with python

    MANOVA is an update of ANOVA, where we use a minimum of two dependent variables.

  • Two-Way ANOVA

    You only need to understand two or three concepts if you have read the one-way ANOVA article. We use two factors instead of one in a two-way ANOVA.

Instagram
WhatsApp
error: Content is protected !!