Pacific-Design.com

    
Home Index

1. SQL Language

+ 01 CREATE TABLE

+ 02 GROUP BY

+ 03 SUBQUERIES

+ 04 CASE Statement

+ 05 WITH Statement

+ 06 UNION

+ 07 PROCEDURE

+ 08 DELETE Duplicates

+ DATE SUB

+ Date Delta

+ ON DUPLICATE KEY

+ Query Optimization

+ Row Number

+ SHA256

+ Temporary Table

SQL Language /

Aggregate Functions

AVG()           Return the average value of the argument
BIT_AND()       Return bitwise and
BIT_OR()        Return bitwise or
BIT_XOR()       Return bitwise xor
COUNT(DISTINCT) Return the count of a number of different values
COUNT()         Return a count of the number of rows returned
GROUP_CONCAT()  Return a concatenated string
MAX()           Return the maximum value
MIN()           Return the minimum value
STD()           Return the population standard deviation
STDDEV_POP()    Return the population standard deviation
STDDEV_SAMP()   Return the sample standard deviation
STDDEV()        Return the population standard deviation
SUM()           Return the sum
VAR_POP()       Return the population standard variance
VAR_SAMP()      Return the sample variance
VARIANCE()      Return the population standard variance 

SQL - INNER JOIN

SELECT a.order_id, a.order_date, b.customer_id, b.customer_name
FROM Orders a 
JOIN Customer b ON a.customer_id = b.customer_id;


SQL - LEFT JOIN

SQL will return all customers, and any orders they have placed
SELECT a.customer_name, b.order_id
FROM Customer a
LEFT JOIN Orders b ON a.customer_id = b.customer_id 
ORDER BY a.customer_name;


SQL - RIGHT JOIN

SQL returns all customers, and any orders they have placed
SELECT a.order_id, b.customer_name
FROM Orders a
RIGHT JOIN Customer b ON a.customer_id = b.customer_id
ORDER BY a.customer_name;


SQL - SELF JOIN

To query the data and get information for both people in one row
SELECT e1.EmployeeID, 
    e1.FirstName, 
    e1.LastName,
    e1.SupervisorID, 
    e2.FirstName as SupervisorFirstName, 
    e2.LastName as SupervisorLastName
FROM Employee e1
LEFT OUTER JOIN Employee e2 on e1.SupervisorID = e2.EmployeeID

References: