SQL - Day 1

Today, we'll learn the backbone of data - Structured Query Language. It is declarative language, i.e. you tell it what to do not how to do.

Code with me!

Steps:

1. Download MySQL and don't forget the admin password (you can't recover it!)

2. Download imdb movie dataset and get started.

Things I learnt today:

USE imdb; //imdb is the database name

SHOW TABLES; //will show all the tables in this database

DESCRIBE movies; //will show all fields in the movies table

Use semicolon after every command and capitalize all the SQL keywords as a best practice.

SELECT Command

SELECT * FROM <table name>

There will be around 38k records, if you want to abort - use Ctrl+C

SELECT * FROM <table name> LIMIT 20 //will only show top 20 rows

SELECT * FROM <table name> LIMIT 20 OFFSET 40 //will only show 20 rows after 40 rows, i.e. rows from 40-60

SELECT DISTINCT genre FROM movie_genres //will show distinct genres (otherwise your list would be flooded - try it and you'll understand)

ORDER BY

SELECT name, year from movies ORDER BY year DESC LIMIT 20 ; //ascending order is a default option

WHERE

SELECT name, year FROM movies WHERE rankscore>9

Alternatively can use: <> (not), >=, <=, =

"=" DOES NOT work with NULL. Use "IS NULL" or "IS NOT NULL" for this.

LOGICAL OPERATORS

SELECT name, year FROM movies WHERE year>2000 AND rankscore>9; //movies with year >2000 and rankscore is >9 both the conditions should be fulfilled

Pro tip: Use "BETWEEN" if you want data between a specific range (both the numbers are inclusive)

SELECT name, year FROM movies WHERE year BETWEEN 2000 AND 2001; //else you would have to use multiple ">" and "<" condition

SELECT * FROM movies_genres WHERE genre IN ('Comedy', 'Horror') LIMIT 10; //effective coding!

SELECT name FROM movies WHERE name LIKE 'Tis%'; // % is wild card character returns all strings with 0 or more after 'Tis'

SELECT name FROM movies WHERE name LIKE 'Tis_'; // _ is wild card character returns all strings with exactly 1 character after 'Tis', can use double underscore to match 2 characters

Another example (not from imdb)

If you want to find all the students who scored more than 96%, how would you write?

SELECT * from students_data where marks>'96\%' // '\' is an escape character telling the interpreter that treat % as a symbol and not wildcard character

Day 1 completed, yay! :D