10/16/2018Databases and QueriesPage 1
Databases and Queries
SQL
1.This is used for queries, data definition, and data manipulation. It is most useful when generated from a programming language, as QBE seldom can be.
a.This used to stand for "Structured Query Language" but it no longer stands for anything; it is simply the name of a language, as Pascal is the name of a language. (Some languages are acronyms: BASIC = Beginners All-Purpose Symbolic Instruction Code, Fortran = Formula Translation; LISP = List Processor. The language "C" was just the 3rd major revision of a language developed by Bell Labs: initially called "A", then "B", then "C". (We could now be programming in E and E++ and E#.)
b.Most SQL queries use a subset of these keywords:
SELECT / which columns you want in output or display ("*" means all columns)FROM / which table or tables used to get columns
INNER JOIN (ON) / links between pairs of tables, ON specifies the common columns linking them and how to match them.
There are different JOIN structures:
INNER JOIN (Access' name for the EQUI JOIN) returns only data in which there is a match in both tables; only records which have the exact same value in both tables. (Of course, if the primary key value is 117, there will only be one such value, but its Foreign Key may have many records with 117, and they will all be accessed.
LEFT JOIN returns all records listed in the Left Table. If there is a corresponding match with the Right Table (CID=117 in both tables), then that record from that table is displayed, as well. If there is not a corresponding record in the Right Table, only the column names for that table are displayed and no entries. Summary: The Left Join shows all Left Table records and any matching Right Table records.
RIGHT JOIN returns all records listed in the Right Table. If there is a corresponding match with the Left Table (CID=117 in both tables), then that record from that table is displayed, as well. If there is not a corresponding record in the left Table, only the column names for that table are displayed and no entries. Summary: The Right Join shows all Right Table records and any matching Left Table records.
These "Outer Joins" (Left and Right) are useful in seeing where data integrity problems are occuring, particularly when converting a database.
(Frequently, one can use either an INNER JOIN or a WHERE clause.)
ON / Goes with the INNER JOIN: tells which two common columns match
WHERE / what criteria to use in selecting information to return (which specific values in the column, for instance)
ORDER BY / default is ASC, can also use DESC (but can't spell it out "DESCENDING")
GROUP BY / group like elements: average temperature by city; don't need to know the groups, and don't need to specify the separate groups in advance.
Can be used to modify an aggregate function (SUM, AVG, COUNT, MIN, MAX):
(SEE Example later)
MAIN POINT ABOUT GROUP BY:
You MUST use GROUP BY if both these two things are true:
[You are using an aggregate function]
AND [you wish to display something other than the function result]
Note that WHERE must come before GROUP BY if WHERE is used.
HAVING / a clause of GROUP BY:
e.g. AVG temp GROUP BY city, HAVING temp < 105
LIKE / gives a result similar to your specification
BETWEEN / gives a result between two numbers (or strings)
Summary or Aggregate Functions / COUNT, SUM, MAX, MIN, AVG
DISTINCT / Don't display duplicate records for those columns being displayed
2.IGNORE THESE OTHER SQL STATEMENTS
a.Other SQL statements not directly related to queries: CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, CREATE DOMAIN, CREATE SCHEMA, CREATE VIEW
3.General Comments regarding SQL
a.SQL is standardized, and SQL commands for one DB can usually be used with another DB.
b.SQL is easier to read than QBE
c.SQL is usually harder to use than QBE
d.One can generate SQL from a program like Java, but usually not so for QBE
e.More control with SQL: Can use keyword DISTINCT
f.Most DBMS have some mechanism to perform calculations: some SQL summary or aggregate functions are COUNT, SUM, MAX, MIN, AVG (Know these 5 summary or aggregate functions)
g.If information is to be obtained from 2 or more tables, tables must be joined with the INNER JOIN clause of the FROM statement, based ON a common column for each pair of tables.
SQL EXAMPLES
These examples are related to data in a database supplied in the Prentice Hall Anderson&Post, 3rd. The name of the database is C06Ex15.mdb . Thus you can open this database and try these SQL statements. The tables and columns are shown below, as well as how the tables may be joined (primary key to a foreign key). The diagram also shows that the pair-wise relationships are many-one.
Orders(OrderID,CID,SID,ODate,Amount)Customers(CID,Name,Phone,Street,City,AccountBalance)
Salespeople(SID,Name,YearHired,Phone,Commission)
ItemsSold(OrderID,ItemID,Quantity)
Items(ItemID,Category,Description,Price) /
4. How to execute an SQL command in Access.
Query: show all Customers with AccountBalance > 200, with Name in Ascending order. Display all fields.
a.Under Objects, click Queries, click the button New, click Design View, click OK
b.The window labeled Show Table appears: Close this window without doing anything with it.
c.At the top left below the File menu item is an icon which either shows SQL or it shows an icon called the Design View Button.
1.If you see SQL there, click on the button.
2.If you see the Design View button, click on the arrow to its right and select SQL View.
d.Then you'll be in the SQL window, which will already have the default "SELECT" statement and the ending semi-colon. The semi-colon is the last thing in your SQL statement. Thus you could now type any of the SQL statements shown below.
5.SQL Example: Given this table:
Customers(CID,Name,Phone,City,AccountBalance)
a.Write this query in SQL: show all Customers with AccountBalance > 200, with Name in Descending order. Display all fields.
SELECT *
FROM Customers
WHERE (AccountBalance > 200)
ORDER BY Name DESC;
1.The "*" means display all fields in the Customers table.
2.Note that you must type "DESC" rather than "DESCENDING"
3.To execute after typing the SQL, click the Run button (the red "!") to execute this SQL statement.
4.In the Query window, everyone but Jones from Chicago appears (since Jones has an AccountBalance < 200).
output:
CID / Name / Phone / Street / City / AccountBalance29587 / Smitz / (206) 676-7763 / 523 Oak Street / Seattle / $353.76
44453 / Kolke / (303) 888-8876 / 909 West Avenue / Denver / $863.39
87535 / James / (305) 777-2235 / 374 Main Street / Miami / $255.93
28764 / Adamz / (602) 999-2539 / 938 Main Street / Phoenix / $526.76
b.Same query but display only Name and AccountBalance, and sort by AccountBalance, Ascending.
SELECT Name, AccountBalance
FROM Customers
WHERE (AccountBalance > 200)
ORDER BY AccountBalance ASC;
(Default is ASC.)
output:
Name / AccountBalanceJames / $255.93
Smitz / $353.76
Adamz / $526.76
Kolke / $863.39
1.(I usually prefer to specify the table with the name, even though the next line says which table to use. This is because when you join tables, you usually have to include the table name when specifying which columns to display.)
SELECT Customers.Name, Customers.AccountBalance
FROM Customers
WHERE (Customers.AccountBalance > 200)
ORDER BY Customers.AccountBalance ASC;
c.Considering those from Denver with AccountBalance > 200, display only Name and AccountBalance, and sort by Name, Ascending. Here we're leaving off the table name as prefix to the column name.
SELECT Name, AccountBalance
FROM Customers
WHERE (AccountBalance > 200) AND City = "Denver"
ORDER BY Name ASC;
(Default is ASC, so this last statement isn't necessary.)
output:
Name / AccountBalanceKolke / $863.39
d.Find those from Miami with AccountBalance between 200 and 400; display only Name and AccountBalance, and sort by Name, Ascending.
SELECT Name, AccountBalance
FROM Customers
WHERE (AccountBalance BETWEEN 200 AND 400) AND City = "Miami"
ORDER BY Name ASC;
(Default is ASC.)
output:
Name / AccountBalanceJames / $255.93
78.JOINING TABLES: Given these TWO tables:
Customers(CID,Name,Phone,City,AccountBalance)
Orders (OrderID,CID,SID,ODate,Amount) (Amount is the dollar amount of the order.)
a.Find those with AccountBalance> 300, where the ODate is between 3/1/2004and 6/1/2004. Display CID, Name, ODate, and AccountBalance.
SELECT Customers.CID, Customers.Name, Orders.ODate, Customers.AccountBalance
FROM Customers INNER JOIN Orders
ON Customers.CID=Orders.CID
WHERE (Customers.AccountBalance > 300)
AND (Orders.ODate BETWEEN #3/1/2004# AND #6/1/2004#);
b.In Access, the date delimiter is "#", although Access will allow you to leave this off.
c.Note that Smitz appears twice, but with different dates.
output:
CID / Name / ODate / AccountBalance29587 / Smitz / 5/6/2004 / $353.76
29587 / Smitz / 5/8/2004 / $353.76
44453 / Kolke / 5/1/2004 / $863.39
79.Not Responsible for: ORACLE SQL: JOINING TABLES:
The SQL for Joining is slightly different for Oracle (and easier):
Given these TWO tables:
Customers(CID,Name,Phone,City,AccountBalance)
Orders (OrderID,CID,SID,ODate,Amount) (Amount is the dollar amount of the order.)
a.Find those with AccountBalance> 300, where the ODate is between 3/1/2004and 6/1/2004. Display CID, Name, ODate, and AccountBalance.
ORACLE VERSION:
SELECT Customers.CID, Customers.Name, Orders.ODate, Customers.AccountBalance
FROM Customers, Orders
WHERE Customers.CID=Orders.CID
AND (Customers.AccountBalance > 300)
AND (Orders.ODate BETWEEN #3/1/2004# AND #6/1/2004#);
b.A Left Join has the (+) with the Left Table :
WHERE Customers.CID(+)=Orders.CID
c.A Right Join has the (+) with the Right Table :
WHERE Customers.CID=Orders.CID(+)
80.JOINING TABLES: Given these THREE tables:
Customers(CID,Name,Phone,City,AccountBalance)
Orders(OrderID,CID,SID,ODate,Amount)(Amount is the dollar amount of the order.)
Salespeople(SID,Name,DateHired,Phone,Commission)
a.List salespeople (sorted by Name, ascending), and the customer names who placed orders with those salespeople. Don't display duplicate records. Display the names of the salespeople and the customers.
b.Note: it takes 3 tables:
1.Customer Name comes from Customers
2.Salesperson's Name comes from Salespeople
3.But table Customers can't join with table Salespeople, since they have no common column. Hence, the table Orders must be used as an intermediary.
SELECT DISTINCT Customers.Name, Salespeople.Name
FROM Customers INNER JOIN
(Orders INNER JOIN Salespeople ON Orders.SID=Salespeople.SID)
ON Customers.CID=Orders.CID;
output:
Customers.Name / Salespeople.NameAdamz / Bird
James / Bird
James / Johnson
Jones / Jabbar
Jones / Johnson
Kolke / Bird
Kolke / West
Smitz / Jabbar
Smitz / West
4.The relationships are:
Customers
|
INNER JOIN |
(on CID) |
Orders ------Salespeople
INNER JOIN
(on SID)
c.This could be stated another way, of course, with the same results, by specifying the joins in a different order.
SELECT DISTINCT Customers.Name, Salespeople.Name
FROM Salespeople INNER JOIN
(Orders INNER JOIN Customers ON Orders.CID=Customers.CID)
ON Salespeople.SID=Orders.SID;
1.Stated this way, the relationships are:
Salespeople
|
INNER JOIN |
(on SID) |
Orders ------Customers
INNER JOIN
(on CID)
81.JOINING TABLES: Given these TWO tables, using keyword SUM: (this is similar to CH6 slide 22). (Amount is the amount in dollars.)
Customers(CID,Name,Phone,City,AccountBalance)
Orders(OrderID,CID,SID,ODate,Amount) (Amount is the dollar amount of the order.)
a.For customers who live in Miami, what is the total Dollar amount of orders placed? (Each order has an entry in the Amount column. Thus, what is the total of the Amount values for the Miamicustomers? Display both the City and the Total dollar Amount.
SELECT Customers.City, SUM(Orders.Amount)
FROM Customers INNER JOIN Orders
ON Customers.CID=Orders.CID
WHERE (Customers.City = "Miami")
GROUP BY Customers.City ;
output:
City / Expr1001Miami / $686.81
1.NOTE: the GROUP BYis required because of the display of City, the thing we are focusing on, and it must follow the WHERE.
a.The GROUP BY is required because we are displaying Customers.City . Recall the GROUP BY clause is required when both these conditions are met:
[1] You are using an aggregate function (like SUM) and
[2] you are displaying something in addition to the aggregate function result. Here we're displaying both the City and the SUM.
b.The DB must group the Amounts by City, and then from the various City groups, select the specified one, which is "Miami".
2.(Note: you couldn't display Customers.Name along with the SUM, since there is more than one Name, unless you did a WHERE to restrict what is being displayed.)
3.Suppose you only wanted to display the total Amount of orders by those in Miami, and not display the City name. Then you don't need the GROUP BY:
SELECT SUM(Orders.Amount)
FROM Customers INNER JOIN Orders
ON Customers.CID=Orders.CID
WHERE (Customers.City = "Miami");
output:
Expr1000$686.81
CONVERTING FROM SQL TO WORDS
82.Given these tables from a database
Bikes(Serial#,make,model,color,category,size)
Customer(Cust#,name,address,city,state,zip,phone)
Parts(Part#,name,manufacturer,model,quantity,price)
Sales(ID#,Cust#,Date,salesperson,quantity,ordertotal)
Salespeople(salesperson#,name,address,phone,date_hired)
(Where ordertotal is the total dollars for a particular order.)
a.Given this SQL, write out the English version:
SELECT name, phone
FROM Customer INNER JOIN
(Sales INNER JOIN Bikes ON Sales.ID#=Bikes.Serial#)
ON Customer.Cust#=Sales.Cust#)
WHERE date > #9/1/2004# AND make='Trek'
1.Answer: Give the names and phone#s of the customers who purchased Trek bikes after 9/1/2004.
b.There is a name column in Customer, Parts and Salespeople. How does the DB know which one you mean?
c.Write the SQL query that gives you the total amount of sales since 7/1/2004, for the salesperson Jack Welch. Display the total.
SELECT SUM(Sales.ordertotal)
FROM Sales INNER JOIN Salespeople
ON Sales.salesperson=Salespeople.salesperson#
WHERE Salespeople.Name = "Jack Welch" AND Sales.Date >= #7/1/2004#
d.Write the SQL query that gives you the number of part numbers (not quantity) each customer bought from manufacturer Shimano. That is, one customer may have purchased 5 different parts or part numbers, with various amounts of each.
Display the Customer's cust#, the Customer's name, and the total number of different part numbers that customer bought.
SELECT Customer.cust#,Customer.name,COUNT(Parts.part#)
FROM Parts INNER JOIN Sales
ON Sales.id# = Parts.part#
GROUP BY Customer.cust#,Customer.name
WHERE Sales.manufacturer = "Shimano"
ORDER BY COUNT(part#) DESC
83.More SQL examples using the tables from database C06Ex15.mdb, from Prentice Hall Anderson&Post 3rd. As a reminder, here are the tables, columns, and relationships.
Orders(OrderID,CID,SID,ODate,Amount)Customers(CID,Name,Phone,Street,City,AccountBalance)
Salespeople(SID,Name,YearHired,Phone,Commission)
ItemsSold(OrderID,ItemID,Quantity)
Items(ItemID,Category,Description,Price) /
a.Note: to place the output tables from Access into Word:
1.Get the output in Access.
2.Click on Tools, Office Links, Publish It with MS Word
3.This will open a new Word document, and the table can be copied.
4.You may want to highlight the cells, click on borders and shading, change the color to black, and click on grid.
ItemsSoldOrderID / ItemID / Quantity
117 / 1154 / 2
117 / 3342 / 1
117 / 7653 / 4
125 / 1154 / 4
125 / 8763 / 3
157 / 7653 / 2
169 / 3342 / 1
169 / 9987 / 5
178 / 2254 / 1
188 / 3342 / 4
188 / 8763 / 1
201 / 1154 / 1
211 / 3342 / 2
211 / 9987 / 5
213 / 2254 / 1
215 / 2254 / 1
215 / 7653 / 1
280 / 2254 / 1
280 / 3342 / 3
/ Customers
CID / Name / Phone / Street / City / AccountBalance
12345 / Jones / (312) 555-1234 / 125 Elm Street / Chicago / $197.54
28764 / Adamz / (602) 999-2539 / 938 Main Street / Phoenix / $526.76
29587 / Smitz / (206) 676-7763 / 523 Oak Street / Seattle / $353.76
44453 / Kolke / (303) 888-8876 / 909 West Avenue / Denver / $863.39
87535 / James / (305) 777-2235 / 374 Main Street / Miami / $255.93
Orders
OrderID / CID / SID / ODate / Amount
0 / 28764 / 663 / 10/11/2001 / $0.00
117 / 12345 / 887 / 3/3/2004 / $57.92
125 / 87535 / 663 / 4/4/2004 / $123.54
157 / 12345 / 554 / 4/9/2004 / $297.89
169 / 29587 / 255 / 5/6/2004 / $89.93
178 / 44453 / 663 / 5/1/2004 / $154.39
188 / 29587 / 554 / 5/8/2004 / $325.46
201 / 12345 / 887 / 5/23/2004 / $193.58
211 / 44453 / 255 / 6/9/2004 / $201.39
213 / 44453 / 255 / 6/9/2004 / $154.15
215 / 87535 / 887 / 6/9/2004 / $563.27
280 / 28764 / 663 / 6/27/2004 / $255.32
Salespeople
SID / Name / YearHired / Phone / Commission
255 / West / 1975 / (213) 333-2345 / 5.00%
452 / Zeke / 1994 / (213) 343-5553 / 3.00%
554 / Jabbar / 1991 / (213) 534-8876 / 4.00%
663 / Bird / 1993 / (213) 225-3335 / 4.00%
887 / Johnson / 1992 / (213) 887-6635 / 4.00%
/ Items
ItemID / Category / Description / Price
1154 / Hard Goods / Corn Broom / $1.00
2254 / Clothes / Blue Jeans / $12.00
3342 / Soft Goods / Paper Towels, 3 rolls / $1.00
7653 / Hard Goods / Laundry Detergent / $2.00
8763 / Clothes / Men's Boots / $15.00
9987 / Hard Goods / Candy Popcorn / $0.50
FIX FIX FIX
Adding some ORDER BY examples
SELECT SUM(Amount)
FROM Orders;
Expr1000$2,416.84
SELECT SUM(Amount)
FROM Orders;
GROUP BY CID;
Expr1000$549.39
$255.32
$415.39
$509.93
$686.81
SELECT CID,SUM(Amount)
FROM Orders
GROUP BY CID;
CID / Expr100112345 / $549.39
28764 / $255.32
29587 / $415.39
44453 / $509.93
87535 / $686.81
SELECT SUM(Amount)
FROM Orders
WHERE CID=12345;