SQL

Introduction
Links
mySQL using Docker
Video
Commands
Curated Questions

Introduction

Structured Query Language. Keywords are case insensitive.
DBMS notes

Links

mySQL using Docker

A basic working SQL server.

docker pull mysql
docker create network work
docker run --name testmysql --network work -e MYSQL_ROOT_PASSWORD=password -d mysql:latest
docker run -it --network work --rm mysql mysql -htestmysql -uroot -p
#(Enter 'password' as your password)

Pull docker image. Create a network. Start a server in a container. Create a client container and access the server.

Video

Commands

show databases;
create database test;
use test;
create table t ( col1 char , col2 int);
insert into t values ('a',1),('b',2);
SELECT * from t;
select col1 from t WHERE col2 = 1;
select col2 from t where col1 LIKE 'a%';
select distinct city FROM station WHERE city REGEXP "^[aeiou].*";

REGEX

AGGREGATION

MIN, MAX, COUNT, AVG

GROUP BY

select col1 , count(col1) from t group by col1;

JOINS

select t1.col1 , t2.col1 from t1 INNER JOIN t2 ON t1.col2 = t2.col2;
select t1.col1 , t2.col1 from t1 LEFT JOIN t2 ON t1.col2 = t2.col2;
select t1.col1 , t2.col1 from t1 RIGHT JOIN t2 ON t1.col2 = t2.col2;

UNION => DISTINCT
UNION ALL => DUPLICATES allowed

select * from t1 UNION select * from t2;

Curated Questions

1. Query even ID numbers in table STATION.

select * from station where MOD(id,2)=0;

2. Query to find the difference between the total number of cities and the unique number of cities in the table STATION.

select count(city)-count(distinct city) from station;

3. Query city with the longest name from table STATION

select city from station orderby length(city) desc, city limit 1;

4. Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Result cannot contain duplicates.

select distinct city FROM station WHERE city REGEXP "^[aeiou].*";