Basic SQL Queries
In this topic we will learn about basic SQL queries in relational database.
What Is The SQL?#
-
SQLisStructured Query Language, which is a computer language for storing, manipulating and retrieving data stored in arelational database. -
SQLis the standard language for Relational Database System. All the Relational Database Management Systems (RDMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their standard database language.
Setup Environment#
-
In this example we will
MySQLdatabase, this database is very common and many people usually use it. -
First you should download
MySQL Installeron mysql page. If you don't know how to install MySQL byMySQL Installeryou can view the instruction here. However, I recommend we should use MySQL Docker you can view Docker With Databases for more details. -
After the installation you should install
MySQL Workbenchon mysql page orDBeaverfollowing DBeaver page. These are database tools which will support you very much to interact with database. -
So we need some sample data and you can find it in this website by click
Download MySQL Sample Data. -
Then import/execute your downloaded
SQL Sample DatabyMySQL WorkbenchorDBeaver -
So this is the relationships between tables in your
SQL Sample Data
Basic SQL Queries#
- To pratise and executes sample queries you can use
MySQL WorkBenchorDBeavertools.
Query With Custom Column And Limit Records#
-- Get customerName and phone from customer table with 20 records
SELECT customerName, phone FROM customers LIMIT 20
-- Get some columns with additional columns is
-- `postalCodeEdited` with values is added 100 and limit 20 records
SELECT customerName, addressLine1, postalCode, postalCode + 100 AS postalCodeEdited
FROM customers
LIMIT 20
-- Get all columns with additional columns is
-- `postalCodeEdited` with values is added 100 and limit 30 records
SELECT *, postalCode + 100 AS 'postalCodeEdited'
FROM customers
ORDER BY customerName
LIMIT 30
-- Get all columns which is not `country` column, limit 30 records
SELECT DISTINCT country
FROM customers
ORDER BY country
LIMIT 30
-- Get columns productName, buyPrice and new_buyPrice with values = buyPrice * 1.1
SELECT productName, buyPrice, buyPrice * 1.1 AS 'new_buyPrice'
FROM products
Query With Conditions#
-- Get all customers with creditLimit > 100000
SELECT *
FROM customers
WHERE creditLimit > 100000
-- Get all order with orderDate > '2005-01-01'
SELECT *
FROM order s
WHERE orderDate > '2005-01-01'
SELECT *
FROM orders
WHERE orderDate > '2005-01-01' AND customerNumber > 400 AND (orders.status = 'On Hold' OR orders.status = 'Disputed')
-- Get all orders with statuses in list: ['On Hold', 'Disputed']
SELECT *
FROM orders
WHERE orders.status IN ('On Hold', 'Disputed')
-- Get all orders BETWEEN 200 AND 400
SELECT *
FROM orders
WHERE customerNumber BETWEEN 200 AND 400
-- Get all orders BETWEEN '2005-01-01' AND '2005-04-30'
SELECT *
FROM orders
WHERE orderDate BETWEEN '2005-01-01' AND '2005-04-30'
Query With Like#
-- Get all customers with customerName begin with `Au...`
SELECT *
FROM customers
WHERE customerName LIKE 'Au%'
-- Get all customers with contactLastName has last character 'y'
SELECT *
FROM customers
WHERE contactLastName LIKE '____y'
-- Get all customers with contactLastName doesn't have last character 'y'
SELECT *
FROM customers
WHERE contactLastName NOT LIKE '____y'
-- Get all customers with customerName that is not begin with `Au`
SELECT *
FROM customers
WHERE customerName REGEXP '^Au'
-- Get all customers with customerName end with
SELECT *
FROM customers
WHERE customerName REGEXP 'Co$|^Au'
-- Get all customers with customerName contain [a-h] and end with `e`.
SELECT *
FROM customers
WHERE customerName REGEXP '[a-h]e'
Query With Order#
-- Get all customers with state is not null
SELECT *
FROM customers
WHERE state IS NOT NULL
-- Get all customers with state is null
SELECT *
FROM customers
WHERE state IS NULL
-- Get all customers with contactFirstName is desc
SELECT *
FROM customers
ORDER BY contactFirstName DESC
-- Get city, contactFirstName with ordered by city , contactFirstName
SELECT city, contactFirstName
FROM customers
ORDER BY city , contactFirstName
-- try to avoid this use
SELECT city, contactFirstName, 10 AS points
FROM customers
ORDER BY 1 , 2
SELECT city, contactFirstName
FROM customers
WHERE city LIKE 'a%'
ORDER BY city, contactFirstName
Query With Pagination#
-- get 2 continuos records (record 5 and record 6), ignone 4 frist records
SELECT *
FROM customers
LIMIT 4, 2
Query With Join Tables#
SELECT orders.customerNumber, orders.orderNumber, customers.customerNumber AS 'id_of_customer_table'
FROM orders
JOIN customers
ON orders.customerNumber = customers.customerNumber
-- column that contain in two table had to use "o.customerName"
SELECT o.customerNumber, o.orderNumber, c.customerNumber AS 'id_of_customer_table'
FROM orders o
JOIN customers c
ON o.customerNumber = c.customerNumber
-- join table from 2 different database
SELECT *
FROM customers c
JOIN secondclassicmodels.orders o
ON c.customerNumber = o.customerNumber
-- join table from 2 different database
USE classicmodels;
SELECT *
FROM secondclassicmodels.customers c
JOIN orders o
ON o.customerNumber = c.customerNumber
SELECT c.customerNumber, phone, amount, p.customerNumber AS 'payment'
FROM customers c
JOIN payments p
ON c.customerNumber = p.customerNumber
-- Join multiple tables together
SELECT customerName,
c.customerNumber,
o.customerNumber AS 'orders_customer_numb',
o.orderNumber
comments,
productCode,
quantityOrdered
FROM customers c
JOIN orders o
ON c.customerNumber = o.customerNumber
JOIN orderdetails od
ON o.orderNumber = od.orderNumber
SELECT *
FROM orderdetails ord
JOIN products prd
ON prd.productCode = ord.productCode
JOIN orders o
ON o.orderNumber = ord.orderNumber
-- Implpicit Join Syntax
SELECT *
FROM orderdetails ord, orders o
WHERE ord.orderNumber = o.orderNumber
OUTER JOIN, we will you left join or right join
-- for left join, all the customer from left table (customer) will be return whether the join condition is true or not
-- for right join, all the order from right table (orders) will be return whether the join condition is true or not
SELECT
c.customerNumber,
c.customerName,
o.orderNumber
FROM customers c
LEFT JOIN orders o
ON c.customerNumber = o.customerNumber
ORDER BY c.customerNumber
-- description, we will get all the customer whether contains order or not
-- then we will get all the order whether contains orderdetails or not
SELECT
c.customerNumber,
c.customerName,
o.orderNumber,
d.productCode
FROM customers c
LEFT JOIN orders o
ON c.customerNumber = o.customerNumber
LEFT JOIN orderdetails d
ON o.orderNumber = d.orderNumber
ORDER BY c.customerNumber