SQL Syntax I
Peter
Komisar ©
Conestoga College version 1.1
reference:
Structured Query Language(SQL) ,D.Geller, R. Roselius,
Danielle Waleri, DDC Publishing, Core MySQL, Leon Atkinson,
Prentice-Hall Publishing
Following is a solution for the somewhat unfair
second assignment which ask you to build a
database before we learned SQL! This was
done to provide us a base against which we
could execute our SQL statements. Following
is one solution.
While unfair, in that the syntax was not covered
formally yet, starting with your own table of values
makes the learning of the subsequent SQL
statements more 'hands on'.
Canadian version of Store table
STORE
NUMBER INTEGER
MANAGER_ID
INTEGER
STREET
VARCHAR(45)
CITY
VARCHAR(25)
PROVINCE
CHAR(2)
POSTAL_CODE
CHAR(6)
AREA_CODE
CHAR(3)
PHONE_NUMBER
CHAR(7)
GST_TAX_RATE
DECIMAL(1,1)
Store Create Statement
CREATE TABLE STORE (
STORE_NUMBER
INTEGER,
MANAGER_ID INTEGER,
STREET
VARCHAR(45),
CITY
VARCHAR(25),
PROVINCE
CHAR(2),
POSTAL_CODE
CHAR(6),
AREA_CODE
CHAR(3),
PHONE_NUMBER
CHAR(7),
GST_TAX_RATE
DECIMAL(1,1)
);
Enter
password: ****
Welcome
to the MySQL monitor. Commands end with ; or \g.
Your
MySQL connection id is 2
Server
version: 5.0.45-community-nt MySQL Community Edition (GPL)
Type
'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE DATABASE bizzy;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|
bizzy
|
|
mysql
|
|
test
|
+--------------------+
4 rows in set (0.05 sec)
mysql> use bizzy;
Database changed
mysql> show tables;
Empty set (0.00 sec)
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
|
Database |
+--------------------+
| information_schema |
|
bizzy
|
|
mysql
|
|
test
|
+--------------------+
4 rows in set (0.05 sec)
mysql> use bizzy;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create database townfinder;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE STORE(
->
STORE_NUMBER INTEGER,
->
MANAGER_ID INTEGER,
-> STREET
VARCHAR(45),
-> CITY
VARCHAR(25),
-> PROVINCE
CHAR(2),
->
POSTAL_CODE CHAR(6),
->
AREA_CODE CHAR(3),
->
PHONE_NUMBER CHAR(7),
->
GST_TAX_RATE DECIMAL(1,1)
-> );
Query OK, 0 rows affected (0.22
sec)
mysql> SHOW TABLES;
+-----------------+
| Tables_in_bizzy |
+-----------------+
|
store |
+-----------------+
1 row in set (0.00 sec)
mysql> DROP TABLE STORE;
Query OK, 0 rows affected (0.09
sec)
mysql> show tables;
Empty set (0.00 sec)
mysql> CREATE TABLE STORE(
->
STORE_NUMBER INTEGER,
->
MANAGER_ID INTEGER,
-> STREET
VARCHAR(45),
-> CITY
VARCHAR(25),
-> PROVINCE
CHAR(2),
->
POSTAL_CODE CHAR(6),
->
AREA_CODE CHAR(3),
->
PHONE_NUMBER CHAR(7),
->
GST_TAX_RATE DECIMAL(2,1),
->
PST_TAX_RATE DECIMAL(2,1)
-> );
Query OK, 0 rows affected (0.06
sec)
mysql> show tables;
+-----------------+
| Tables_in_bizzy |
+-----------------+
|
store |
+-----------------+
1 row in set (0.00 sec)
mysql>
Store Data
We need (10 x 2) stores (-1 ) for PEI and plus four,
two each for Ontario and Quebec. We need a total of
23 stores. The following is a lift of a text file where each
field has been tab separated.
//
assume it is a Toronto based store chain.
St.No.MgrNo
Street
City Prov.
PC AreaCode Ph_No GST PST
1001
1893 '1111 Rosemary Blvd.'
'Victoria' 'BC'
'B1C9W2' '204'
'3231122' 5.0 8.0
1002
4930 '579a Delaware St.'
'Vancouver' 'BC'
'B2C8T1' '204'
'2039485' 5.0 8.0
1003
2309 '2 Shepard Rd.'
'Edmonton' 'AL'
'A2BW2T' '780'
'3345555' 5.0, 0.0
1004
1234 '555 Queen St.'
'Calgary' 'AL'
'A6NN3W' '403'
'9192834' 5.0 0.0
1005
9991 '33 WheatField Ave.'
'Saskatoon' 'SK'
'S3S9Q2' '306'
'1223344' 5.0 6.0
1006
1029 '4301 Main St. West'
'Regina' 'SK'
'S3C8B9' '306'
'9987787' 5.0 6.0
1007
1111 '1099 Portage Ave.'
'Winnipeg' 'MB'
'M4W9S8' '204'
'5456566' 5.0 8.0
1008
2143 '507 King St.'
'Brandon' 'MB'
'M4W3N2' '204'
'8273364' 5.0 8.0
1009
1011 '2044 Arlington Ave.'
'Ottawa' 'ON'
'JOX1B0' '613'
'9348888' 5.0 8.0
1010
1012 '3099 College St.'
'Toronto' 'ON'
'M4T2K9' '416'
'7634567' 5.0 8.0
1011
9001 '993 Eglington Ave.'
'Toronto' 'ON'
'M4T5L2' '416'
'5673300' 5.0 8.0
1012
1105 '456 Dunlop Ave.'
'Barrie' 'ON'
'N4Z3L7' '705'
'6830987' 5.0 8.0
1013
2019 '62 Broadway Ave.'
'Orangeville' 'ON'
'L1N4Y0' '519'
'4343431' 5.0 8.0
1014
2222 '444 Vincent Blvd.'
'Moncton' 'NB'
'N2E1G4' '506'
'4400493' 5.0 8.0
1015
3345 '305 Wade St.'
'Bathurst' 'NB'
'N6F2H1' '506'
'2999393' 5.0 8.0
1016
2435 '444 Governor Blvd.'
'Halifax' 'NS'
'S1T3V4' '902'
'8112777' 5.0 8.0
1017
8876 '486 Hyde St.'
'Sydney' 'NS'
'S3B8F2' '902'
'5484448' 5.0 8.0
1018
1239 '99 Victoria Ave.'
'Charlottetown' 'PE'
'P3E1I8' '902'
'3394483' 5.0 8.0
1019
6685 '3 St. Laurent St.' 'St.
John' 'NF'
'N3F1L3' '709'
'4456281' 5.0 8.0
1020
9387 '112 Marlee St.' 'Labrador
City' 'NF'
'N7F8L2' '709'
'3918884' 5.0 8.0
1021
1212 '488 Trembly Ave.'
'Montreal' 'QC'
'Q2U9B8' '450'
'2998858' 5.0 7.0
1022
2882 '95 Baptiste Ave.' 'Quebec
City' 'QC'
'Q4C1D9' '819'
'2292299' 5.0 7.0
1023
7665 '777 Rue Fontaine'
'Hull' 'QC'
'Q2D4G4' '819'
'4499660' 5.0 7.0
Entering
MYSQL
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
Changing the Database
mysql> use bizzy;
Database changed
Inserting Values
mysql> insert into store values(
-> 1002, 4930, '579a Delaware
St.','Vancouver','BC',
-> 'B2C8T1','204','2039485',5.0,8.0
-> );
Query OK, 1 row affected (0.03 sec)
mysql> select Store_Number,Manager_ID,City,Province from store;
+--------------+------------+-----------+----------+
| Store_Number | Manager_ID |
City | Province |
+--------------+------------+-----------+----------+
|
1001 | 1893 | Victoria |
BC |
|
1002 | 4930 | Vancouver |
BC |
+--------------+------------+-----------+----------+
2 rows in set (0.00 sec)
Loading and
Outputting to File // just for reference at this point
While we are here we should
mention that there is an
easier way to load and store a database. Following is
the LOAD and INTO directives used to load to from file.
SELECT ... INTO OUTFILE is the
complement of LOAD DATA INFILE
Example
mysql> select
Store_Number,Manager_ID,City,Province
-> into OUTFILE 'R_Set.txt' from store;
Query OK, 2 rows affected (0.00 sec)
C:\Program Files\MySQL\'MySQL Server
5.0'\data\bizzy>notepad R_Set.txt
SQL Syntax I
Select
Statements
Select Statements have the following form
Select Statement Form
SELECT [ DISTINCT ] { column_list | * } FROM
table_name
//
where [ ] means optional, and * means all columns
Select statements can be used to retrieve data from one
or more tables. The returned set of values is referred to as
a 'result set' or 'virtual table'.
Select statements can be used to make selections based
on columns.
The Wild
Card // selecting all
columns
The asterisk, * is used as a 'wild card' and can represent
any column. Thus the following statement selects all values
from the store table.
Example
SELECT * FROM store;
The output is large so suffice it to say the whole table
content was displayed to console.
Selecting Sub-sets of Columns
To select one or two columns, the columns are listed
in place of the wild card symbol, separated by columns.
In the following example three columns from the store
table are selected.
Example
SELECT Store_Number,City, Province FROM store;
Following is the executed statement.
mysql> SELECT
Store_Number,City, Province FROM store;
+--------------+---------------+----------+
| Store_Number |
City | Province |
+--------------+---------------+----------+
|
1001 | Victoria |
BC |
|
1002 | Vancouver |
BC |
|
1003 | Edmonton |
AL |
|
1004 | Calgary |
AL |
|
1005 | Saskatoon |
SK |
|
1006 | Regina |
SK |
|
1007 | Winnipeg |
MB |
|
1008 | Brandon |
MB |
|
1009 | Ottawa |
ON |
|
1010 | Toronto |
ON |
|
1011 | Toronto |
ON |
|
1012 | Barrie |
ON |
|
1013 | Orangeville | ON
|
|
1014 | Moncton |
NB |
|
1015 | Bathurst |
NB |
|
1016 | Halifax |
NS |
|
1017 | Sydney |
NS |
|
1018 | Charlottetown | PE |
|
1019 | St. John |
NF |
|
1020 | Labrador City | NF |
|
1021 | Montreal |
QC |
|
1022 | Quebec City | QC
|
|
1023 | Hull |
QC |
+--------------+---------------+----------+
23 rows in set (0.00 sec)
The DISTINCT Modifier
To select each distinct occurrence of some column
value the DISTINCT modifier may be used after the
SELECT keyword.
Example
SELECT DISTINCT Province FROM store;
Following is the executed statement.
mysql> SELECT DISTINCT
Province FROM store;
+----------+
| Province |
+----------+
|
BC |
|
AL |
|
SK |
|
MB |
|
ON |
|
NB |
|
NS |
|
PE |
|
NF |
|
QC |
+----------+
10 rows in set (0.01 sec)
Column Aliasing
A different name can be assigned to represent a
column. This may be a single word or more than
one word bounded by double quotes. This is useful
in doing calculations and presenting data in informative
ways unencumbered by the actual column names.
Example
SELECT PST_TAX_RATE AS PST,
GST_TAX_RATE AS GST,
PST + GST AS "Total Tax"
FROM store WHERE STORE_Number = 1001;
mysql> SELECT PST_TAX_RATE AS
PST,
->
GST_TAX_RATE AS GST,
->
PST_TAX_RATE + GST_TAX_RATE AS "Total Tax"
->
from store where store_number=1001;
+------+------+-----------+
| PST | GST | Total
Tax |
+------+------+-----------+
| 8.0 | 5.0
| 13.0 |
+------+------+-----------+
1 row in set (0.00 sec)
Notice we used an operator, the plus symbol to
add the values for two columns. We will look at
operators in more detail later.
Order
Column data can be ordered using the ORDER BY
clause. This optional clause is applied to columns
where the data can be listed in ascending or descending
order.
We note that the store_number in our table of data
shown above are listed in ascending order. The following
statement can be used to show them in reverse order.
Example
SELECT STORE_NUMBER, Street, Province FROM store
ORDER BY STORE_NUMBER DESC;
Following is a sample executed in MySQL.
mysql> SELECT STORE_NUMBER,
Street, Province FROM store
-> ORDER BY
STORE_NUMBER DESC;
+--------------+----------------------+----------+
| STORE_NUMBER |
Street
| Province |
+--------------+----------------------+----------+
|
1023 | 777 Rue Fontaine |
QC |
|
1022 | 95 Baptiste Ave. |
QC |
|
1021 | 488 Trembly Ave. |
QC |
|
1020 | 112 Marlee St. |
NF |
|
1019 | 3 St. Laurent St. |
NF |
|
1018 | 99 Victoria Ave. |
PE |
|
1017 | 486 Hyde St. |
NS |
|
1016 | 444 Governor Blvd. |
NS |
|
1015 | 305 Wade St. |
NB |
|
1014 | 444 Vincent Blvd. |
NB |
|
1013 | 62 Broadway Ave. |
ON |
|
1012 | 456 Dunlop Ave. |
ON |
|
1011 | 993 Eglington Ave. |
ON |
|
1010 | 3099 College St. |
ON |
|
1009 | 2044 Arlington Ave. |
ON |
|
1008 | 507 King St. |
MB |
|
1007 | 1099 Portage Ave. |
MB |
|
1006 | 4301 Main St. West |
SK |
|
1005 | 33 WheatField Ave. |
SK |
|
1004 | 555 Queen St. |
AL |
|
1003 | 2 Shepard Rd. |
AL |
|
1002 | 579a Delaware St. |
BC |
|
1001 | 1111 Rosemary Blvd. |
BC |
+--------------+----------------------+----------+
23 rows in set (0.03 sec)
Notice in the following example where the order is based
on street the alphabetic value of each number is evaluated
not the number value.
Example
mysql> SELECT Store_NUMBER, Street, Province FROM store
-> ORDER BY Street ASC;
+--------------+----------------------+----------+
| Store_NUMBER |
Street
| Province |
+--------------+----------------------+----------+
| 1020 | 112
Marlee St. |
NF |
| 1021 | 488
Trembly Ave. |
QC |
| 1007 | 1099 Portage
Ave. | MB |
| 1001 | 1111 Rosemary
Blvd. | BC |
| 1003 | 2 Shepard
Rd. |
AL |
| 1009 | 2044
Arlington Ave. | ON |
| 1019 | 3 St. Laurent
St. | NF |
| 1015 | 305 Wade
St. |
NB |
| 1010 | 3099 College
St. | ON |
| 1005 | 33 WheatField
Ave. | SK |
| 1006 | 4301 Main St.
West | SK |
| 1016 | 444 Governor
Blvd. | NS |
| 1014 | 444 Vincent
Blvd. | NB |
| 1012 | 456 Dunlop
Ave. |
ON |
| 1017 | 486 Hyde
St. |
NS |
| 1008 | 507 King
St. |
MB |
| 1004 | 555 Queen
St. |
AL |
| 1002 | 579a Delaware
St. | BC |
| 1013 | 62 Broadway
Ave. | ON |
| 1023 | 777 Rue
Fontaine | QC |
| 1022 | 95 Baptiste
Ave. | QC |
| 1018 | 99 Victoria
Ave. | PE |
| 1011 | 993 Eglington
Ave. | ON |
+--------------+----------------------+----------+
23 rows in set (0.00 sec)
Where Clauses
The Where clause allows selecting a subset of the rows
retrieved by a select statement. The form of the where
clause is added to the select form below.
Where Clause Form
Select Statement Form
SELECT [ DISTINCT ] { column_list | * } FROM
table_name
[ WHERE conditions ]
The WHERE is following by one or more conditions against
which each row is tested. The condition evaluates to one
of the following:
Condition Evaluation Values
If for a row the condition evaluates to true, then that
row is added to the returned result set.
Following are the operators that may be used in
WHERE clauses.
Operators Used With Where Clauses
operator
|
meaning
|
=
|
equal
to |
!=,^=,<>
|
not
equal to
|
| >,
< |
is
greater than, is less than |
| >=,
<= |
is
greater than or equal,
is less than or equal |
In the following example, first we select all the
store numbers that are in Ontario. We will this
with a compound statement where, the 'not
equal to' operator is tested to eliminate one of
these numbers.
Example
mysql> SELECT
Store_Number FROM store
-> where
province ='ON';
+--------------+
| Store_Number |
+--------------+
|
1009 |
|
1010 |
|
1011 |
|
1012 |
|
1013 |
+--------------+
5 rows in set (0.00 sec)
//
one store is eliminated
mysql> SELECT Store_Number
FROM store
-> where
(store_number <> 1011) & (province='ON');
+--------------+
| Store_Number |
+--------------+
|
1009 |
|
1010 |
|
1012 |
|
1013 |
+--------------+
4 rows in set (0.00 sec)
The NULL
Value and IS, IS NOT
Null represents an absence of value and is
not equal to an empty string, a blank or a zero.
Null is tested with
IS or IS NOT operators.
Example
SELECT * FROM Store WHERE POSTAL_CODE IS NULL.
mysql> SELECT * FROM store WHERE postal_code IS NULL;
Empty set (0.00 sec)
Using IS NOT NULL returns the entire table.
Any other operation on NULL yields NULL. Therefore
using NULL in an equality test will always yield null even
if the value in that cell is NULL.
Example
WHERE postal_code=NULL; // always yields NULL
Compound
Expressions
More than one condition may be applied in a WHERE
clause as we saw in an earlier example. Three operators
are used to combine expressions.
Compound Operators
- AND // In MySQL & works with warning,
&& without
- OR // In MySQL | works with warning, ||
without
- NOT
AND
AND is used to support the comparisons where
both conditions must be true in order for the compound
condition to be true.
All Boolean Combination With
the AND Operator
FALSE AND FALSE yields
FALSE // 0 && 0 = 0
FALSE AND TRUE yields
FALSE // 0 &&
1 = 0
TRUE AND FALSE yields
FALSE // 1 && 0 = 0
TRUE AND TRUE
yields TRUE // 1 && 1 = 1
In the first example a randomly created postal code
is used to arrive at a negative comparison. Because
this value is false any value 'ANDED' with it will be
false so an empty set is achieved.
Example
mysql> SELECT * FROM store
-> WHERE postal_code='BTW1W2' AND city ='Toronto';
Empty set (0.00 sec)
OR
OR is used to support the comparisons where if one
or the other, or both conditions are true then the compound
condition will also be true.
All Boolean Combination With
the OR Operator
FALSE AND FALSE yields
FALSE // 0 || 0 = 0
FALSE AND TRUE yields
TRUE // 0 || 1 = 1
TRUE AND FALSE yields
TRUE // 1 ||
0 = 1
TRUE AND TRUE
yields TRUE // 1 ||
1 = 1
Repeating the earlier example with the OR
operator
yields rows with city 'Toronto'. This is because though
the first condition is false, the second condition is
true for two rows. A fewer number of columns than
the wildcard returns was selected, just for presentation
purposes.
Example
mysql>
SELECT STORE_NUMBER, City, Province FROM store
-> WHERE
postal_code='BTW1W2' OR city ='Toronto';
+--------------+---------+----------+
| STORE_NUMBER |
City | Province |
+--------------+---------+----------+
|
1010 | Toronto | ON |
|
1011 | Toronto | ON |
+--------------+---------+----------+
2 rows in set (0.00 sec)
NOT
Not
inverts the value of a condition. Not true
is false. Not
false is true. In the following
example rows
with every city except Toronto
are included.
Example
mysql> SELECT STORE_NUMBER, City, Province FROM store
-> WHERE postal_code='BTW1W2' OR NOT city
='Toronto';
+--------------+---------------+----------+
| STORE_NUMBER |
City | Province |
+--------------+---------------+----------+
| 1001 |
Victoria |
BC |
| 1002 |
Vancouver |
BC |
| 1003 |
Edmonton |
AL |
| 1004 |
Calgary |
AL |
| 1005 |
Saskatoon |
SK |
| 1006 |
Regina |
SK |
| 1007 |
Winnipeg |
MB |
| 1008 |
Brandon |
MB |
| 1009 |
Ottawa |
ON |
| 1012 |
Barrie |
ON |
| 1013 |
Orangeville | ON |
| 1014 |
Moncton |
NB |
| 1015 |
Bathurst |
NB |
| 1016 |
Halifax |
NS |
| 1017 |
Sydney |
NS |
| 1018 | Charlottetown
| PE |
| 1019 | St.
John |
NF |
| 1020 | Labrador City
| NF |
| 1021 |
Montreal |
QC |
| 1022 | Quebec
City | QC |
| 1023 |
Hull |
QC |
+--------------+---------------+----------+
21 rows in set (0.00 sec)
Following is a straight forward example of
two conditions
being satisfied.
Example
mysql> SELECT STORE_NUMBER, City, Province FROM store
-> where province = 'ON'
-> and city ='Barrie';
+--------------+--------+----------+
| STORE_NUMBER | City | Province |
+--------------+--------+----------+
| 1012 | Barrie |
ON |
+--------------+--------+----------+
1 row in set (0.00 sec)
Nested Queries //
Sub Queries
Queries may be nested inside other queries. Subqueries
are placed inside round brackets. Further, subqueries
can be further nested inside subqueries. You are free to
use any SQL in the subquery except for SET operations
and the ORDER BY Clause.
The following example shows that the return type
of the nested query must match the column type
it is being equated with.
Conditional
Comparisons With Nested Queries
When a nested query is being used to return
a value that will be used in a comparison such
as with the operators, = or !=, a single value
must be returned so that a logical comparison
can be made.
Nested Form
SELECT [ column_list | * ]
WHERE column_name = ( Nested Query )
In the following example the store number is obtained
using a nested query based on selecting a street name.
This return value is used in the primary select to return
row information on that store.
Example
SELECT Store_Number,
Province
FROM store
WHERE
store_number = (SELECT store_number
FROM store WHERE street = '993 Eglington Ave.');
Execution
mysql> SELECT
Store_Number, Province FROM store
-> WHERE
store_number = (SELECT store_number
-> FROM store WHERE
street = '993 Eglington Ave.');
+--------------+----------+
| Store_Number | Province |
+--------------+----------+
|
1011 | ON |
+--------------+----------+
1 row in set (0.00 sec)
White Space
Padding
Something to watch out for, the street names are stored
with white space intact. A search for '488 Trembly' didn't
work. However quoting the address with white spaces
did work as is shown below.
//
Ave. has been added to Trembly for the note
// but not in the database yet
Example
mysql>
SELECT Store_Number, Province FROM store
-> WHERE
store_number = (SELECT store_number
-> FROM store WHERE
street = ' 488 Trembly Ave. ');
+--------------+----------+
| Store_Number | Province |
+--------------+----------+
|
1021 | QC |
+--------------+----------+
1 row in set (0.00 sec)
Most databases including MySQL will have a TRIM
function that can remove leading or trailing whitespaces
or both.
IN or NOT IN
IN allow comparing a value to a list of possible matches.
For instance to select western stores we might search
for stores in B.C, Alberta and Saskatchewan. IN is
logically is an 'OR' function selecting item 1, OR
item 2 OR item N from a list.
Example
SELECT Store_Number,
Province
FROM store
WHERE province
IN ('BC', 'AL', 'SK');
Execution
mysql> SELECT Store_Number,
Province FROM store
-> WHERE
province IN ('BC', 'AL', 'SK');
+--------------+----------+
| Store_Number | Province |
+--------------+----------+
|
1001 | BC |
|
1002 | BC |
|
1003 | AL |
|
1004 | AL |
|
1005 | SK |
|
1006 | SK |
+--------------+----------+
6 rows in set (0.00 sec)
Equivalent Form Using OR
The equivalent form can be written or the OR
operator.
Example
mysql> select Manager_ID from store
-> where province ='BC'
-> or province ='AL'
-> or province ='SK';
Execution
+------------+
| Manager_ID |
+------------+
|
1893 |
|
4930 |
|
2309 |
|
1234 |
|
9991 |
|
1029 |
+------------+
6 rows in set (0.00 sec)
Simply adding NOT will give
us all the
stores not in the western provinces selected.
Example
SELECT Store_Number,
Province
FROM store
WHERE province
NOT IN ('BC', 'AL', 'SK');
Execution
mysql>
SELECT Store_Number, Province FROM store
-> WHERE
province NOT IN ('BC', 'AL', 'SK');
+--------------+----------+
| Store_Number | Province |
+--------------+----------+
|
1007 | MB |
|
1008 | MB |
|
1009 | ON |
|
1010 | ON |
|
1011 | ON |
|
1012 | ON |
|
1013 | ON |
|
1014 | NB |
|
1015 | NB |
|
1016 | NS |
|
1017 | NS |
|
1018 | PE |
|
1019 | NF |
|
1020 | NF |
|
1021 | QC |
|
1022 | QC |
|
1023 | QC |
+--------------+----------+
17 rows in set (0.00 sec)
BETWEEN
The
BETWEEN operator allows the selection
of values within
a specified range. In the following
example we select the stores with numbers between
1004 and 1009. Notice BETWEEN is inclusive of
the range delimiters. The example might be described
in terms of 'values equal and greater than the lower
limiting value and equal and less than the upper limiting
value'.
Example
SELECT Store_Number,
Province
FROM store
WHERE
store_number BETWEEN 1004 AND 1009;
Execution
mysql> SELECT
Store_Number, Province FROM store
-> WHERE
store_number BETWEEN 1004 AND 1009;
+--------------+----------+
| Store_Number | Province |
+--------------+----------+
|
1004 | AL |
|
1005 | SK |
|
1006 | SK |
|
1007 | MB |
|
1008 | MB |
|
1009 | ON |
+--------------+----------+
6 rows in set (0.02 sec)
LIKE
The LIKE operator is used to do pattern matching.
Consider the (imaginary) postal code B1C9W2.
We pretend the B and the C are related to British
Columbia. We can use LIKE with a pattern to select
all such postal codes.
We need to wild card all the other characters.
Two wild cards are used with LIKE;
- %
- percent symbol
- to match zero or more characters
- _
- underscore
- to match one position-dependent character.
We know the
number of characters in the postal
code so we can use underscore to wildcard all
the other letters.
Example
B_C___ // groups of one and three underscores
We use the pattern in the following query.
Example
SELECT Store_Number, City,
Province
FROM store
WHERE
postal_code LIKE 'B_C___';
Execution
mysql> SELECT Store_Number, City,
Province FROM store
-> WHERE
postal_code LIKE 'B_C___';
+--------------+-----------+----------+
| Store_Number |
City | Province |
+--------------+-----------+----------+
|
1001 | Victoria | BC |
|
1002 | Vancouver | BC |
+--------------+-----------+----------+
2 rows in set (0.01 sec)
Assignment
1 ) Check in MySQL or whichever database you are using,
which of the following operators for 'not equal to' is
supported.
Do this by testing with a select statement such as the following:
mysql> SELECT Store_Number
FROM store
-> where
(store_number <> 1011) & (province='ON');
+--------------+
| Store_Number |
+--------------+
|
1009 |
|
1010 |
|
1012 |
|
1013 |
+--------------+
4 rows in set (0.00 sec)
Report which of these are supported for your database.
2) Using the database you created, do the following.
a ) List all the store' cities, provinces, the manager ids.
b) List only the cities that store's are located in using
the DISTINCT keyword to eliminate repeats.
c ) Select a store based on two conditions , it's
city and it's
province. Use a province where
there are two stores
or more. Show the city
province and store number of this
store.
d) Choose a manager's id. Select all column information
for that manager's store based on his or here id.
e) Select a store number. Select all column information
for that store id.
f ) Select the province in which you have most stores.
Select these store's based on that province and order
the output in descending order based on the store number.
Output province and store number for these stores.
g ) Select all the store numbers and manager ids for
stores in the Maritime provinces using the IN operator.
h ) Use the BETWEEN operator to select a range of
three stores based on store_number, showing store
number and manager ids.
i ) Use LIKE and it's associated wildcard notation to
select all store numbers with a specific pattern found
in your postal code examples.