Tag: Data Management

SQL

Ted

The most common data access method is structure query language (SQL). This language is designed to allow easy access to relational data. If you are familiar when set theory or Venn diagrams SQL will seem very intuitive. SQL allows you to quickly and simply combine relational data. Below is an example of a database containing two tables, customers and purchase order.  The Customer table has customerid (the unique identifier for the customer), name (The customer’s name) and age (the customer’s age).  The PurchaseOrder table has POID (the unique id for the purchase order), customerid (which refers back to the customer who made the purchase) and Purchase (what the purchase was).

Example:
Customer
CustomerID Name Age
1 Joe 23
2 Mika 45
3 Lin 34
4 Sara 56
5 Susan 18
PurchaseOrder
POID CustomerID Purchase
1 3 Fiction
2 1 Biography
3 1 Fiction
4 2 Biography
5 3 Fiction
6 4 Fiction
 
     
SELECT
SELECT * FROM MyDataBase.Customer

CustomerID Name Age
1 Joe 23
2 Mika 45
3 Lin 34
4 Sara 56
5 Susan 18
ORDER BY
SELECT * FROM MyDataBase.Customer ORDER BY by age
CustomerID Name Age
5 Susan 18
1 Joe 23
3 Lin 34
2 Mika 45
4 Sara 56
WHERE
SELECT * FROM MyDataBase.Customer WHERE age = 18
CustomerID Name Age
5 Susan 18
INNER JOIN
SELECT * FROM MyDataBase.Customer INNER JOIN MyDataBase.PurchaseOrder ON Customer.CustomerID = PurchaseOrder.CustomerID
CustomerID Name Age POID Purchase
1 Joe 23 2 Biography
1 Joe 23 3 Fiction
2 Mika 45 4 Biography
3 Lin 34 1 Fiction
3 Lin 34 5 Fiction
4 Sara 56 6 Fiction
LEFT OUTER JOIN
SELECT * FROM MyDataBase.Customer LEFT OUTER JOIN MyDataBase.PurchaseOrder ON Customer.CustomerID = PurchaseOrder.CustomerID
CustomerID Name Age POID Purchase
1 Joe 23 2 Biography
1 Joe 23 3 Fiction
2 Mika 45 4 Biography
3 Lin 34 1 Fiction
3 Lin 34 5 Fiction
4 Sara 56 6 Fiction
5 Susan 18 NULL NULL
GROUP BY
SELECT Name, count(*) as Orders FROM Customer INNER JOIN PurchaseOrder ON Customer.CustomerID = PurchaseOrder.CustomerID GROUP BY Name
Name Age Orders
Joe 23 1
Mika 45 2
Lin 34 2
Sara 56 1
UPDATE
UPDATE Customer SET Age = 26 WHERE CustomerID = 1
Customer
CustomerID Name Age
1 Joe 26
2 Mika 45
3 Lin 34
4 Sara 56
5 Susan 18
INSERT
INSERT INTO Customer Values (6, Terry , 50)
Customer
CustomerID Name Age
1 Joe 23
2 Mika 45
3 Lin 34
4 Sara 56
5 Susan 18
6 Terry 50
DELETE
DELETE FROM Customer WHERE CustomerID = 1
Customer
CustomerID Name Age
2 Mika 45
3 Lin 34
4 Sara 56
5 Susan 18