SQL Expression Cheat Sheet in Common Concepts

 Expressions in Querying

We can write expressions in various SQL clauses. Expressions can comprise of various data types like integers, floats, strings, datetime, etc.

Let's learn more about expressions using the following database.

Database

The IMDb stores various movies, actors and cast information.

Using Expressions in SELECT Clause

1. Get profits of all movies.

Note: Consider profit as the difference between collection and budget.

1

2

3

4

SELECT

id, name, (collection_in_cr-budget_in_cr) as profit

FROM

movie;

SQL

Output

id	name	profit
1	The matrix	40.31
2	Inception	67.68
3	The Dark Knight	82.5
...	...	...

Note

We use "||" operator to concatenate strings in sqlite3

2. Get the movie
   name
   and
   genre
   in the following format:
   movie_name - genre
   .

1

2

3

4

SELECT

name || " - " || genre AS movie_genre

FROM

movie;

SQL

Output

movie_genre
The Matrix - Sci-fi
Inception - Action
The Dark Knight - Drama
Toy Story 3 - Animation
...

Using Expressions in WHERE Clause

1. Get all the movies with a profit of at least 50 crores.

1

2

3

4

5

6

SELECT

*

FROM

movie

WHERE

(collection_in_cr - budget_in_cr) >= 50;

SQL

Output

id	name	genre	budget_in_cr	collection_in_cr	rating	release_date
2	Inception	Action	16.0	83.68	8.8	2010-07-14
3	The Dark Knight	Action	18.0	100.5	9.0	2008-07-16
4	Toy Story 3	Animation	20.0	106.7	8.5	2010-06-25
...	...	...	...	...	...	...

Using Expressions in UPDATE Clause

1. Scale down ratings from 10 to 5 in movie table.

1

2

UPDATE movie

SET rating = rating/2;

SQL

You can check the updation of movie ratings by retriving the data from the table.

Expressions in HAVING Clause

Get all the genres that have average profit greater than 100 crores.

1

2

3

4

5

6

7

8

SELECT

genre

FROM

movie

GROUP BY

genre

HAVING

AVG(collection_in_cr - budget_in_cr) >= 100;

SQL

Output

genre
Action
Animation
Mystery
...

Try it Yourself!

Question 1 Get the profit of every movie with

rating

greater than 8.0

Expected Output

id	name	genre	budget_in_cr	collection_in_cr	rating	release_date	profit
1	The Matrix	Sci-fi	6.3	46.43	8.7	1999-04-31	40.13
2	Inception	Action	16	83.68	8.8	2010-07-16	67.68
3	The Dark Knight	Drama	18	100.5	9	2008-07-18	82.5
...	...	...	...	...	...	...	...

Question 2 Get all the movies having a

profit

of at least 30 crores, and belong to "Action", "Animation" or "Drama" genres.

Expected Result

id	name	genre	budget_in_cr	collection_in_cr	rating	release_date
2	Inception	Action	16	83.68	8.8	2010-07-16
3	The Dark Knight	Drama	18	100.5	9	2008-07-18
4	Toy Story 3	Animation	20	106.7	8.5	2010-06-25
...	...	...	...	...	...	...

Question 3 Scale up the ratings from 5 to 100 in the movie table.

MOVIE

id	name	genre	budget_in_cr	collection_in_cr	rating	release_date
1	The Matrix	Sci-fi	6.3	46.43	8.7	1999-04-31
2	Inception	Action	16	83.68	8.8	2010-07-16
3	The Dark Knight	Drama	18	100.5	9	2008-07-18
4	Toy Story 3	Animation	20	106.7	8.5	2010-06-25
5	Shutter Island	Mystery	100	722.5	7.5	2003-05-27
6	Sherlock Holmes	Mystery	52	266	8	2010-11-25
7	Iron Man	Action	75	600	9.2	2008-06-14
8	Deadpool	Action	100	600	9.1	2016-05-17
9	Deadpool 2	Action	120	200	8.3	2018-11-20
10	Iron Man 2	Action	200	800	8.5	2010-05-24
11	Thor	Action	150	250	7.8	2010-03-24
12	Avengers: Age of Ultron	Action	9.5	10	6.5	2015-06-17
13	Spider-Man: Homecoming	Action	10	12	2.5	2010-06-04
14	Black Panther	Action	25	50	5.6	2016-05-19
15	Avengers: Endgame	Action	125	600	8.8	2018-04-15
16	Avengers: Infinity War	Action	205	300	8.6	2017-03-06
17	How to Train Your Dragon	Animation	120	325	9.5	2010-06-13
18	The God Father	Drama	25	62	7.1	1972-06-24
19	The God Father 2	Drama	30	45	7.5	1974-11-23
20	Interstellar	Sci-fi	300	600	8.8	2010-03-18

ACTOR

actor_id	name	age
1	Keanu Reeves	46
2	Carrie-Anne Moss	53
3	Christopher Nolan	60
4	Leonardo DiCaprio	50
5	Lee Unkrich	60
6	Robert Downey Jr	55
7	Ryan Reynolds	45
8	Mark Ruffalo	35
9	Tom Holland	30
10	Smith	54
11	Marion Cotillard	45
12	Tom Hardy Eames	54
13	Joaquin Phoenix	60
14	Maggie Gyllenhaal	55
15	Michelle Williams	42
16	Mark Strong	34
17	Rachel McAdams	40
18	Morena Baccarin	36
19	Josh Brolin	35
20	James Caan	30
21	Jude Law	30
22	Chris Evans	34

CAST

actor_id	movie_id	role
1	1	Lead Actor
2	2	Lead Actress
4	2	Lead Actor
3	3	Lead Actor
4	5	Lead Actor
6	6	Lead Actor
7	8	Lead Actor
7	9	Lead Actor
7	10	Lead Actor
9	13	Lead Actor
6	10	Lead Actor
6	15	Lead Actor
8	15	Lead Actor
10	1	Supporting Actor
11	2	Lead Actress
12	2	Supporting Actor
13	3	Supporting Actor
14	3	Lead Actress
16	6	Supporting Actor
15	5	Lead Actress
18	9	Lead Actress
19	9	Supporting Actor
20	18	Supporting Actor
21	6	Lead Actor
22	15	Lead Actor