Transcript Query Lab - La Salle University

Query Lab
CSC 240 Blum
1
Log on to PMA (PHPMyAdmin) and
click on the Northwind database
CSC 240 Blum
2
Alternative place to work:
http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all
CSC 240 Blum
3
After choosing the Northwind
database, click on the Query tab
CSC 240 Blum
4
Use the drop-down to choose `customers`.* Then check
the Show checkbox. Then click The Update Query button
The marks around the table name seen
here are called tick marks or back-ticks.
They slant and are above the Tab on the
upper left of the keyboard. They are NOT
single quotes. They are not necessary in
this case but PMA includes them.
CSC 240 Blum
5
Key words
• SELECT
– In SQL a SELECT statement is one that looks at a
subset of data
– As opposed to adding data (INSERT), taking away
data (DELETE) and changing data (UPDATE)
• FROM
– FROM indicates the source of the data – which
tables that data comes from
CSC 240 Blum
6
Next click the Submit Query button
CSC 240 Blum
7
Result: 94 records (displaying 30 per page)
CSC 240 Blum
8
Click on Northwind to move from the specific customers table to
the more general Northwind database
CSC 240 Blum
9
Click on the Query tab. This time select specific fields (instead of * for all
fields). Remember to check Show and click Update Query.
CSC 240 Blum
10
Result from clicking the Submit Query button. It produces the
same number of records but fewer fields per record.
CSC 240 Blum
11
Projection
• In “relational algebra” the previous query is an
example of a projection – we have projected
out some of the fields.
• (Don’t be intimidated by the term “relational
algebra” it just means things we can do to
database tables.)
CSC 240 Blum
12
YOU DO #1
• Make a Word document and paste into the
SQL for
– The first name and last name of Employees
CSC 240 Blum
13
Return to Northwind/Query. Choose `customers`.* and Show for one column
and `customers`.`City` and ='London' as the Criteria
CSC 240 Blum
14
Click Update to show the query
CSC 240 Blum
15
WHERE – selection
• The new keyword appearing is WHERE
– It is used to limit the record that will display to
those that meet some condition
• In relational algebra, this procedure of limiting
the number of records is known as selection
• Note in this case that the WHERE condition
uses single quotes – different from ticks
CSC 240 Blum
16
Result of selecting customers whose city is London. 4
records.
CSC 240 Blum
17
Compound condition: City is London or Berlin
CSC 240 Blum
18
Results of Compound condition: City is London or Berlin
CSC 240 Blum
19
YOU DO #2
• Paste into your Word document the SQL for
– Suppliers whose Country is France or Germany
CSC 240 Blum
20
Compound condition: contactTitle is Owner and
Country is Mexico
CSC 240 Blum
21
Result of Compound condition: contactTitle is
Owner and Country is Mexico
CSC 240 Blum
22
YOU DO #3
• Paste into your Word document the SQL for
– Employees whose Title is sales Representative and
Country is USA
CSC 240 Blum
23
Combining selection and projection
projection
CSC 240 Blum
selection
24
Result of selection and projection
CSC 240 Blum
25
Sort result by choosing Ascending or descending
from the drop-down under a field.
CSC 240 Blum
26
Keywords for sorting
• ORDER BY
– Is followed by the field (or fields) upon which the
sorting will be based
• ASC
– Indicates the results will be shown from lowest to
highest numerically or in alphabetic order for
text/strings
• DESC
– Highest to lowest or reverse alphabetical
CSC 240 Blum
27
Results of query sorted on CompanyName
CSC 240 Blum
28
YOU DO #4
• Paste into your Word document the SQL for
– Products sorted by ProductName
CSC 240 Blum
29
Obtaining a list of customer countries
CSC 240 Blum
30
Result of customer country query (so
far). Click on Edit to work on further.
CSC 240 Blum
31
SQL editing dialog box arises
CSC 240 Blum
32
Add keyword DISTINCT to eliminate duplicate country
results
CSC 240 Blum
33
Click Go. Query updated. And only unique
countries occur in result
CSC 240 Blum
34
YOU DO #5
• Paste into your Word document the SQL for
– Distinct cities of employees
CSC 240 Blum
35
Criteria other than equality: Products with a
UnitPrice less than 20
CSC 240 Blum
36
Result of Products with a UnitPrice less than 20
CSC 240 Blum
37
Products with a UnitPrice greater than or equal to 30
CSC 240 Blum
38
Result of Products with a UnitPrice greater than or
equal to 30
CSC 240 Blum
39
Switching >= to >
CSC 240 Blum
40
A BETWEEN query criteria: UnitPrice between 20 and 30
CSC 240 Blum
41
Results of BETWEEN query: UnitPrice between 20 and 30
CSC 240 Blum
42
YOU DO #6
• Paste into your Word document the SQL for
– Products having a UnitsInStock less than or equal
to 15
CSC 240 Blum
43