Transcript document

Labs for SQL Training
Ashish Raghute, Manager of Applications Development,
Fleetwood Enterprises
Quick Refresher
What is SQL?
SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is used to
communicate with a database. According to ANSI (American National Standards Institute),
it is the standard language for relational database management systems. SQL statements are
used to perform tasks such as update data on a database, or retrieve data from a database.
Some common relational database management systems that use SQL are: Oracle, Sybase,
Microsoft SQL Server, Access, Ingres, etc. Although most database systems use SQL, most
of them also have their own additional proprietary extensions that are usually only used on
their system. However, the standard SQL commands such as "Select", "Insert", "Update",
"Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs
to do with a database.
Quick Refresher
Table Basics
A relational database system contains one or more objects called tables.
The data or information for the database are stored in these tables.
Tables are uniquely identified by their names and are comprised of
columns and rows. Columns contain the column name, data type, and
any other attributes for the column. Rows contain the records or data
for the columns. Here is a sample table called "weather".
city, state, high, and low are the columns. The rows contain the data for
this table:
Quick Refresher
Selecting Data
The select statement is used to query the database and retrieve selected data that match the
criteria that you specify. Here is the format of a simple select statement:
select column1
[,column2,etc]
from tablename
[where condition];
[] = optional
The column names that follow the select keyword determine which columns will be returned
in the results. You can select as many column names that you'd like, or you can use a "*" to
select all columns.
The table name that follows the keyword from specifies the table that will be queried to
retrieve the desired results.
The where clause (optional) specifies which data values or rows will be returned or displayed,
based on the criteria described after the keyword where.
Conditional selections used in the where clause:
Quick Refresher
Selecting Data
Conditional selections used in the where clause:
=
Equal
>
Greater than
<
Less than
>=
Greater than or equal
<=
Less than or equal
<>
Not equal to
LIKE *See note below
The LIKE pattern matching operator can also be used in the conditional selection of
the where clause. Like is a very powerful operator that allows you to select only
rows that are "like" what you specify. The percent sign "%" can be used as a wild
card to match any possible character that might appear before or after the
characters specified. For example:
select first, last, city from empinfo where first LIKE 'Er%'; This SQL statement will match
any first names that start with 'Er'. Strings must be in single quotes.
Or you can specify,
select first, last from empinfo where last LIKE '%s'; This statement will match any last
names that end in a 's'.
select * from empinfo where first = 'Eric'; This will only select rows where the first name
equals 'Eric' exactly.
Exercises
1.
2.
3.
4.
5.
Create the empinfo table in your account (see last slide).
Insert the data from into this table using ‘insert’ statements.
Commit the data
Try the following SQLs
•
select first, last, city from empinfo; select last, city, age from empinfo where age > 30;
•
select first, last, city, state from empinfo where first LIKE 'J%'; select * from empinfo;
•
select first, last, from empinfo where last LIKE '%s';
•
select first, last, age from empinfo where last LIKE '%illia%';
•
select * from empinfo where first = 'Eric';
•
select first, last, age from empinfo where upper(last) LIKE '%ILLIA%';
Write your own SQLs for the following –
•
•
•
•
•
•
Display the first name and age for everyone that's in the table.
Display the first name, last name, and city for everyone that's not from
Payson.
Display all columns for everyone that is over 40 years old.
Display the first and last names for everyone whose last name ends in an
"ay".
Display all columns for everyone whose first name equals "Mary".
Display all columns for everyone whose first name contains "Mary".
Exercises
Create the following table in your account:
create table employee (first varchar2(15), last varchar2(20), age
number(3), address varchar2(30), city varchar2(20), state varchar2(20)
);
Insert a record into above table as follows insert into employee (first, last, age, address, city, state) values ('Luke', 'Duke', 45, '2130 Boars
Nest', 'Hazard Co', 'Georgia');
Similarly, insert the following data into the table
Jonie Weber, 28, 1 Main Street, Riverside, California
Potsy Weber, 32, 100 Harbor Blvd, Austin, Texas
Dirk Smith, 45, 1000 Central Ave, Any City, Nevada
then insert at least 5 more of your own list of employees in the table.
After they're inserted into the table, create/run select statements to:
1.
2.
3.
4.
5.
6.
7.
Select
Select
Select
Select
Select
Select
Select
all columns for everyone in your employee table.
all columns for everyone with age over 30.
first and last names for everyone that's under 30 years old.
all columns for everyone whose last name contains "ebe".
the first name for everyone whose first name equals "Potsy".
all columns for everyone over 80 years old.
all columns for everyone whose last name ends in "ith".
Exercises
Update your employee table (see example below)
Update employee set age=33 where first=‘Potsy’;
Write and execute updates for the following –
1.
2.
Jonie Weber just got married to Bob Williams. She has requested that
her last name be updated to Weber-Williams.
Dirk Smith's birthday is today, add 1 to his age.
Write select statements to verify your updates.
Modify structure of your employee table as follows:
Alter table employee
Add (Title varchar2(20), salary number(8,2));
Write and execute updates for the following –
1. Update the above table to add some information in the empty fields we
Created above
2. Give a 10% salary raise to all employees with salary < 30000
Exercises
Delete examples -
delete from employee where lastname = 'May';
delete from employee where firstname = 'Mike' or firstname = 'Eric';
Try these:
1.
2.
Jonie Weber-Williams just quit, remove her record from the table.
It's time for budget cuts. Remove all employees who are making over 70000
dollars.
Commit your transaction.
Create at least two of your own delete statements, and then issue a command to
delete all records from the table
Rollback your transaction. Verify you did not lose the records by logging out and
logging back in.
Exercises
Lets make a backup of your employee table as follows:
Create table employee_backup as select * from employee;
Delete all records from employee_backup table but don’t commit.
Create a small table of your own.
Rollback. See if the table you created still exists. Also see if you got back your deleted records.
Do you understand what you observed? If not, contact your trainer! Hint – DDL statements and
effect on transaction processing.
Drop the small table you created. Also drop the employee_backup table.
Hint Syntax - Drop table <tablename>;
Exercises
Update your employee table such that atleast two employees have same age.
Try ‘select age from employee’ and ‘select distinct age from employee’;
Create a table ITEMS_ORDERED with the following structure:
customerid, order_date, item, quantity, price (use your judgement to select datatypes)
Your script should resemble to the following –
Create table items_ordered
(customerid int, Order_date date, Item varchar2(100), Quantity int,
Price number(7,2));
Also create a customers table –
create table customers
(
customerid int, firstname varchar2(50), lastname varchar2(50), city
varchar2(50), state varchar2(20))
We will add data to these tables using MS-Access. If you don’t have
this application, get this installed thru’ OA or use your friend’s
PC temperorily.
Exercises
Create ODBC connection for your Oracle account as follows –
Open “Control Panel” -> Administrative Tools -> Data Sources (ODBC)
Add a data source using “ODBC for Oracle”
Exercises
If you have difficulty completing the ODBC connection creation, contact Ravi or Mark
Launch MS-Access, select “Blank Database” and give a name like training.mdb
Go to FILE menu, OPEN sub-menu and select “ODBC Databases” from file type drop-down.
Select your odbc connection from “Machine Data Source” tab. Logon to Oracle with your
Password.
You should see a list of tables. Choose your tables (eg. Train1.customers,
Train1.items_ordered).
You can select more than one tables at once by using control-click.
When prompted by MS-Access to specify unique keys, select customerid from customers
Table and customerid+item for items_ordered table.
Double-click on the table name to open the data entry form. Input one record
In each table using the data from next slides (use the first record for each table).
Verify the results thru’ SQLPlus using selects.
For rest of the records, use SQL*Plus or MS-Access (your choice!). Don’t you love data entry!
items_ordered
Exercises
customerid
order_date
10330
10101
10298
10101
10299
10339
10449
10439
10101
10449
10439
10298
10410
10438
10438
10298
10449
10449
10101
10330
10101
30-Jun-99
30-Jun-99
1-Jul-99
1-Jul-99
6-Jul-99
27-Jul-99
13-Aug-99
14-Aug-99
18-Aug-99
1-Sep-99
18-Sep-99
19-Sep-99
28-Oct-99
1-Nov-99
2-Nov-99
1-Dec-99
15-Dec-99
22-Dec-99
30-Dec-99
1-Jan-00
2-Jan-00
10299
10438
10413
10410
10315
10449
10101
10298
10449
10298
10330
18-Jan-00
18-Jan-00
19-Jan-00
30-Jan-00
2-Feb-00
29-Feb-00
8-Mar-00
18-Mar-00
19-Mar-00
1-Apr-00
19-Apr-00
item
Pogo stick
Raft
Skateboard
Life Vest
Parachute
Umbrella
Unicycle
Ski Poles
Rain Coat
Snow Shoes
Tent
Lantern
Sleeping Bag
Umbrella
Pillow
Helmet
Bicycle
Canoe
Hoola Hoop
Flashlight
Lantern
Inflatable
Mattress
Tent
Lawnchair
Unicycle
Compass
Flashlight
Sleeping Bag
Pocket Knife
Canoe paddle
Ear Muffs
Shovel
quantity
price
1
1
1
4
1
1
1
2
1
1
1
2
1
1
1
1
1
1
3
4
1
28
58
33
125
1250
4.5
180.79
25.5
18.3
45
88
29
89.22
6.75
8.5
22
380.5
280
14.75
28
16
1
1
4
1
1
1
2
1
2
1
1
38
79.99
32
192.5
8
4.5
88.7
22.38
40
12.5
16.75
Exercises
customers
customerid
firstname
lastname
10101
10298
10299
10315
10325
10329
John
Leroy
Elroy
Lisa
Ginger
Kelly
Gray
Brown
Keller
Jones
Schultz
Mendoza
10330
10338
10339
10408
10410
10413
10419
10429
10438
10439
10449
Shawn
Michael
Anthony
Elroy
Mary Ann
Donald
Linda
Sarah
Kevin
Conrad
Isabela
Dalton
Howell
Sanchez
Cleaver
Howell
Davids
Sakahara
Graham
Smith
Giles
Moore
city
Lynden
Pinetop
Snoqualmie
Oshkosh
Pocatello
Kailua
Cannon
Beach
Tillamook
Winslow
Globe
Charleston
Gila Bend
Nogales
Greensboro
Durango
Telluride
Yuma
state
Washington
Arizona
Washington
Wisconsin
Idaho
Hawaii
Oregon
Oregon
Arizona
Arizona
South Carolina
Arizona
Arizona
North Carolina
Colorado
Colorado
Arizona
Exercises
1.
2.
3.
4.
5.
6.
7.
Select the maximum price of any item ordered in the
items_ordered table. Hint: Select the maximum price only.
Select the average price of all of the items ordered that were
purchased in the month of Dec.
What are the total number of rows in the items_ordered table?
For all of the tents that were ordered in the items_ordered
table, what is the price of the lowest tent? Hint: Your query
should return the price only.
How many people are in each unique state in the customers
table? Select the state and display the number of people in
each. Hint: count is used to count rows in a column, sum
works on numeric data only, use “group by”.
From the items_ordered table, select the item, maximum price,
and minimum price for each specific item in the table. Hint: The
items will need to be broken up into separate groups.
How many orders did each customer make? Use the
items_ordered table. Select the customerid, number of orders
they made, and the sum of their orders.
REFRESHER HINT – See example of group by in aggregate function
SELECT quantity, max(price) FROM items_ordered GROUP BY quantity;
Exercises
REFRESHER - use of HAVING clause in group by
SELECT dept, avg(salary) FROM employee
GROUP BY dept
HAVING avg(salary) > 20000;
•
•
•
•
•
•
•
•
How many people are in each unique state in the customers table that
have more than one person in the state? Select the state and display the
number of how many people are in each if it's greater than 1.
From the items_ordered table, select the item, maximum price, and
minimum price for each specific item in the table. Only display the
results if the maximum price for one of the items is greater than 190.00.
How many orders did each customer make? Use the items_ordered
table. Select the customerid, number of orders they made, and the sum
of their orders if they purchased more than 1 item.
Select the lastname, firstname, and city for all customers in the
customers table. Display the results in Ascending Order based on the
lastname.
Same thing as exercise #1, but display the results in Descending order.
Select the item and price for all of the items in the items_ordered table
that the price is greater than 10.00. Display the results in Ascending
order based on the price.
Select the customerid, order_date, and item from the items_ordered
table for all items unless they are 'Snow Shoes' or if they are 'Ear Muffs'.
Display the rows as long as they are not either of these two items.
Select the item and price of all items that start with the letters 'S', 'P', or
'F'.
Exercises
Some Refreshers Some typical “where” clauses with IN and BETWEEN usage
Using “IN”
SELECT employeeid, lastname, salary
FROM employee_info
WHERE lastname IN ('Hernandez', 'Jones', 'Roberts', 'Ruiz');
The above is same as –
SELECT employeeid, lastname, salary
FROM employee_info
WHERE lastname = 'Hernandez' OR lastname = 'Jones' OR lastname = 'Roberts'
OR lastname = 'Ruiz';
Using “Between”
SELECT employeeid, age, lastname, salary
FROM employee_info
WHERE age BETWEEN 30 AND 40;
The above is same as –
SELECT employeeid, age, lastname, salary
FROM employee_info
WHERE age >= 30 AND age <= 40;
Exercises
•
•
•
Select the date, item, and price from the items_ordered table for all of
the rows that have a price value ranging from 10.00 to 80.00.
Select the firstname, city, and state from the customers table for all of
the rows where the state value is either: Arizona, Washington,
Oklahoma, Colorado, or Hawaii.
Select the item and per unit price for each item in the items_ordered
table. Hint: Divide the price by the quantity
REFRESHER EXAMPLE ON TABLE JOIN:
SELECT customer_info.firstname, customer_info.lastname, purchases.item
FROM customer_info, purchases
WHERE customer_info.customer_number = purchases.customer_number;
Exercises
•
Write a query using a join to determine which items were ordered by
each of the customers in the customers table. Select the customerid,
firstname, lastname, order_date, item, and price for everything each
customer purchased in the items_ordered table.
•
Repeat exercise #1, however display the results sorted by state in
descending order.
•
Add a record in the customer table but don’t enter matching record in
the items_ordered table. Repeat #1. You wont see the record you
entered. Use outer join to see your newly entered record.
Exercises
Congratulations! You are now ready to do real SQL development. Please keep
the momentum going and practice more SQL to gain more expertise.
Here are some useful links –
http://searchdatabase.techtarget.com/
http://www.programmingtutorials.com/main.asp
http://technet.oracle.com
About the Author
A
shish Raghute currently works as the IT Manager for Applications
Development at Fleetwood Enterprises, Inc. , USA’s leader in
recreational vehicle sales and a leading producer and retailer of
manufactured housing. Prior to joining Fleetwood, Ashish was a Principal
at IBM Business Consulting Services and Principal Consultant at
PricewaterhouseCoopers Consulting. For more than 10 years, Ashish has
guided companies of various sizes from dot net startups to Fortune
1000 to successfully realize their IT vision in the areas of CRM, ERP,
Data Warehousing and E-Business. Ashish can be contacted via email at
[email protected].