Learning SQL

I read the book 📖Learning SQL to refresh my knowledge of the SQL language and practise certain concepts for data analysis, such as 'Windows Functions'. It's a great, easy-to-read book ♥️.

Revised concepts

• 🔍Simple querying to refresh
• Filters
• Joins
• Subquery & CTE
• Group & Aggregation
• CASE-Expression
• View
• Window Functions

🛠️What I've used

Name	Description	Download/Installation Link
Sakila Database	It is an example db provided by MySQl	https://dev.mysql.com/doc/index-other.html
MySQL Workbench	MySQL Client to visualize and query tables	https://dev.mysql.com/doc/workbench/en/wb-installing.html
Docker	Used to launch mysql server without having to install it on your local machine	https://www.docker.com/get-started/

⚙️Setup

🧑‍🔧Installation

I use docker to launch a SQL server. So check out their documentation to install docker.

# download MySQL image
docker pull mysql:latest

# run the container
docker run --name mysql-server -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=sakiladb -e MYSQL_USER=sakilauser -e MYSQL_PASSWORD=sakilapass -p 3306:3306 mysql

Add Sakila schema and data to MySQL server

# copy schema
docker cp .\sakila-db\sakila-schema.sql mysql-server:/sakila/sakila-schema.sql

# copy data
docker cp .\sakila-db\sakila-data.sql mysql-server:/sakila/sakila-data.sql

Define schema and insert data into the db

First, we need to connect to out MySQL server. As we launch our server with docker, we can only connect to the server via the container client. Therefore we open mysql server container in a bash terminal:

# open the terminal (bash) of mysql server
docker exec -it mysql-server bash

# connect to the mysql server from within the container
mysql -u <username> -p # username (-u) -> MYSQL_USER=sakilauser, and password (-p) -> MYSQL_PASSWORD=sakilapass

Now as we are logged into mysql client, we can define our db schema and insert data:

# define the schema
source sakila/sakila-schema.sql

# insert data into the table
source sakila/sakila-data.sql

ℹ️INFO

source is a command allowing us to run sql script in mysql client.

You might encounter access rights error, in that case, just grant that user the sufficient rights. See https://dev.mysql.com/doc/refman/8.4/en/grant.html, Or switch the user and use the root user (username = root, password = root).