Master Basic SQL Queries & Operators | Part 1: Fetch, Filter & Count

Показать описание

Welcome to our SQL series! In Part 1, we dive into basic SQL queries and operators to help you master the essentials of SQL.

CREATE TABLE imdb_top_movies
(
Poster_Link VARCHAR(4000),
Series_Title VARCHAR(500),
Released_Year VARCHAR(20),
Certificate VARCHAR(10),
Runtime VARCHAR(20),
Genre VARCHAR(100),
IMDB_Rating DECIMAL(3, 1),
Overview VARCHAR(4000),
Meta_score INT,
Director VARCHAR(200),
Star1 VARCHAR(200),
Star2 VARCHAR(200),
Star3 VARCHAR(200),
Star4 VARCHAR(200),
No_of_Votes INT,
Gross MONEY
);

INSERT INTO imdb_top_movies
(Poster_Link, Series_Title, Released_Year, Certificate, Runtime, Genre, IMDB_Rating, Overview, Meta_score, Director, Star1, Star2, Star3, Star4, No_of_Votes, Gross)
VALUES

INSERT INTO imdb_top_movies
(Poster_Link, Series_Title, Released_Year, Certificate, Runtime, Genre, IMDB_Rating, Overview, Meta_score, Director, Star1, Star2, Star3, Star4, No_of_Votes, Gross)
VALUES

INSERT INTO imdb_top_movies
(Poster_Link, Series_Title, Released_Year, Certificate, Runtime, Genre, IMDB_Rating, Overview, Meta_score, Director, Star1, Star2, Star3, Star4, No_of_Votes, Gross)
VALUES

INSERT INTO imdb_top_movies
(Poster_Link, Series_Title, Released_Year, Certificate, Runtime, Genre, IMDB_Rating, Overview, Meta_score, Director, Star1, Star2, Star3, Star4, No_of_Votes, Gross)
VALUES

select * from imdb_top_movies;
-- Basic SQL Queries
1) Fetch all data from imdb table

select -- mention the column (s) to be fetched
from -- mention the table (s) from where the data needs to be fetched
where -- filter the data

select *
from imdb_top_movies;

2) Fetch only the name and release year for all movies.

select series_title, released_year
from imdb_top_movies;

3) Fetch the name, release year and imdb rating of movies which are R certified.

select series_title, released_year, imdb_rating
from imdb_top_movies
where certificate = 'R';

4) Fetch the name and genre of movies which are R certified and have a Imdb rating of over 8.

select series_title, genre, imdb_rating
from imdb_top_movies
where certificate = 'R'
and imdb_rating 8;

-- Operators in SQL
1) Arithmetic operator -- + - * / %
2) Logical operator -- AND OR LIKE IN NOT IN etc.
3) Comparison operator -- =

5) Find out how many movies are of Drama genre.

select * from imdb_top_movies;

select count(*)
from imdb_top_movies
where genre like '%Drama%' -------'%DRAMA%'

select count(*)
from imdb_top_movies
where upper(genre) like '%DRAMA%'

select count(1)
from imdb_top_movies
where genre like '%Drama%'

select count(series_title) as no_of_movies
from imdb_top_movies
where genre like '%Drama%'

select count(certificate) as no_of_movies
from imdb_top_movies
where genre like '%Drama%'

select count(1) from imdb_top_movies where series_title is null; --
select count(1) from imdb_top_movies where certificate is null; --

6) How many movies are directed by
'Christopher Nolan', 'Ridley Scott', 'David Fincher', 'Hayao Miyazaki'

select count(1)
from imdb_top_movies
where director in ('Christopher Nolan', 'Ridley Scott', 'David Fincher', 'Hayao Miyazaki')

select *
from imdb_top_movies
where director in ('Christopher Nolan', 'Ridley Scott', 'David Fincher', 'Hayao Miyazaki')

🔔 **Subscribe** for more tutorials and tips on SQL and data analysis!

#SQL #BasicSQL #SQLQueries #LearnSQL #SQLTutorial #SQLOperators #Database #DataAnalysis