Rain Hu's Workspace
Go back

[IT] SQL

Rain Hu

Introduction

What is SQL?

RDBMS

Syntax

SELECT * FROM Customers;

Most Important SQL Commands

SELECT

SELECT column1, column2, ...
FROM table_name;

SELECT DISTINCT

SELECT DISTINCT column1, column2, ...
FROM table_name;

WHERE

SELECT column1, column2, ...
FROM table_name
WHERE condition;

AND, OR, NOT

SELECT column1, column2, ...
FROM table_name;
WHERE NOT condition1 AND condition2 OR condition3 ...;

ORDER BY

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

INSERT INTO

  1. Specify both the column names and the values to be inserted:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
  1. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows:
INSERT INTO table_name
VALUES (value1, value2, value3, ...);

IS NULL, IS NOT NULL

SELECT column_names
FROM table_name
WHERE column_name IS NULL|IS NOT NULL;

UPDATE

UPDATE table_name
SET columns1 = value1, column2 = value2, ...
WHERE condition;

DELETE

DELETE FROM table_name 
WHERE condition;

TOP, LIMIT, FETCH FIRST, ROWNUM

Note: Not all database systems support the SELECT TOP clause.
MySQL supports the LIMIT clause to select a limited number of records,
while Oracle uses FETCH FIRST n ROWS ONLY and ROWNUM.

SELECT TOP number|percent column_names(s)
FROM table_name
WHERE condition;
SELECT column_names(s)
FROM table_name
WHERE condition
LIMIT number;
SELECT column_names(s)
FROM table_name
ORDER BY column_name(s)
FETCH FIRST number ROWS only;
SELECT column_names(s)
FROM table_name
WHERE ROWNUM <= number;
SELECT *
FROM (
    SELECT column_name(s)
    FROM table_name
    ORDER BY column_name(s)
    )
WHERE ROWNUM <= number;

MIN()

SELECT MIN(column_name)
FROM table_name
WHERE condition;

MAX()

SELECT MAX(column_name)
FROM table_name
WHERE condition;

COUNT()

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

AVG()

SELECT AVG(column_name)
FROM table_name
WHERE condition;

SUM()

SELECT SUM(column_name)
FROM table_name
WHERE condition;

LIKE Operator

Note: Ms Access uses an asterisk(*) instead of the percent sign(%), and a question mark(?) instead of the underscore(_).

SELECT column1, column2
FROM table_name
WHERE columnN LIKE '_a%';

Wildcards

IN

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

or

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

BETWEEN

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Aliases

SELECT column_name AS alias_name
FROM table_name;
SELECT column_name(s)
FROM table_name AS alias_name;

Joins

Orderes

OrderIdCustomerIDOrderDate
1030821996-09-18
10309371996-09-19
10310771996-09-20

Customers

CustomerIDCustomerNameContactNameCountry
1Alfreds FutterkisteMaria AndersGermany
2Ana Trujillo Emparedados y heladosAna TrujilloMexico
3Antonio Moreno TaqueriaAntonio MorenoMexico
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

Result

OrderIDCustomerNameOrderDate
10308Ana Trujillo Emparedados y helados9/18/1996
10365Antonio Moreno Taquería11/27/1996
10383Around the Horn12/16/1996
10355Around the Horn11/15/1996
10278Berglunds snabbköp8/12/1996

inner join left join right join full outer join

INNER JOIN

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

LEFT JOIN

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

RIGHT JOIN (RIGHT OUTER JOIN)

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

FULL JOIN (FULL OUTER JOIN)

SELECT column_name(s)
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

Self Join

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

Customers

CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
1Alfreds FutterkisteMaria AndersObere Str. 57Berlin12209Germany
2Ana Trujillo Emparedados y heladosAna TrujilloAvda. de la Constitución 2222México D.F.05021Mexico
3Antonio Moreno TaqueríaAntonio MorenoMataderos 2312México D.F.05023Mexico
SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;

UNION

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2

Note: The column names in the result-set are usually equal to the column names in the first SELECT statement.

GROUP BY

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

HAVING

SELECT column_name(s)
FROM table_name
WHERE conditon
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

EXISTS

SELECT column_name(s)
FROM table_name
WHERE EXISTS
    (SELECT column_name(s) 
    FROM table_name
    WHERE condition
    );

ANY

SELECT column_name(s)
FROM table_name
WHERE column_name(s) opeartor ANY
    (SELECT column_name
     FROM table_name
     WHERE condition);

ALL

SELECT ALL column_name(s)
FROM table_name
WHERE conditon
SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
    (SELECT column_name
     FROM table_name
     WHERE condition);

SELECT INTO

SELECT *
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition
SELECT column1, column2, column3, ...
INTO new table [IN externaldb]
FROM oldtable
WHERE condition;

INSERT INTO SELECT

Note: The existing records in the target table are unaffected.

INSERT INTO table2
SELECT * FROM table1
WHERE condition
INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition

CASE

CASE
    WHEN conditon1 THEN result1
    WHEN conditon2 THEN result2
    WHEN conditonN THEN resultN
    ELSE resuklt
END

NULL Functions

Products |P_Id|ProductName|UnitPrice|UnitsInStock|UnitsOnOrder| |1|Jarlsberg|10.45|16|15| |2|Mascarpone|32.56|23| | |3|Gorgonzola|15.67|9|20|

  1. INFULL()
SELECT ProductName, UnitPrice * (UnitsInStock + 
IFNULL(UnitsOnOrder, 0))
FROM Products;
  1. COALESCE()
SELECT ProductName, UnitPrice * (UnitsInStock + 
COALESCE(UnitsOnOrder, 0))
FROM Products;
  1. ISNULL()
SELECT ProductName, UnitPrice * (UnitsInStock + 
ISNULL(UnitsOnOrder, 0))
FROM Products;
  1. COALESCE()
SELECT ProductName, UnitPrice * (UnitsInStock + 
COALESCE(UnitsOnOrder, 0))
FROM Products;
  1. IsNULL()
SELECT ProductName, UnitPrice * (UnitsInStock + 
IIF(IsNull(UnitsOnOrder), 0, UnitsOnOrder))
FROM Products;
  1. NVL()
SELECT ProductName, UnitPrice * (UnitsInStock + 
NVL(UnitsOnOrder, 0))
FROM Products;
  1. COALESCE()
SELECT SELECT ProductName, UnitPrice * (UnitsInStock +
COALESCE(UnitsOnOrder, 0))
FROM Products;

Sotred Precedures

Stored Procedure Syntax

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

** Execute as Stored Procedure

EXEC procedure_name;

Comments

Share this post on:
Share this post via WhatsApp Share this post on Facebook Share this post on X Share this post via Telegram Share this post on Pinterest Share this post via email
Previous
[IT] MySQL Functions
Next
[Leetcode] 347. Top K Frequent Elements