SQL
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].*";