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 BasicsA 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:

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

Quick Refresher Selecting Data

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. Create the empinfo table in your account (see last slide). 2. Insert the data from into this table using ‘insert’ statements.3. Commit the data4. 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

%';5. 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 table employee (first varchar2(15), last varchar2(20), age number(3), address varchar2(30), city varchar2(20), state varchar2(20));

Create the following table in your account:

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 tableJonie Weber, 28, 1 Main Street, Riverside, CaliforniaPotsy Weber, 32, 100 Harbor Blvd, Austin, TexasDirk 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. Select all columns for everyone in your employee table. 2. Select all columns for everyone with age over 30. 3. Select first and last names for everyone that's under 30 years old. 4. Select all columns for everyone whose last name contains "ebe". 5. Select the first name for everyone whose first name equals "Potsy". 6. Select all columns for everyone over 80 years old. 7. Select all columns for everyone whose last name ends in "ith".

Exercises

Update employee set age=33 where first=‘Potsy’;

Update your employee table (see example below)

Write and execute updates for the following –

1. Jonie Weber just got married to Bob Williams. She has requested that her last name be updated to Weber-Williams.

2. 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 above2. Give a 10% salary raise to all employees with salary < 30000

Exercises

delete from employee where lastname = 'May';

delete from employee where firstname = 'Mike' or firstname = 'Eric';

Delete examples -

Try these:

1. Jonie Weber-Williams just quit, remove her record from the table. 2. 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.

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.

Try ‘select age from employee’ and ‘select distinct age from employee’;

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 recordIn 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 item quantity price

10330 30-Jun-99 Pogo stick 1 2810101 30-Jun-99 Raft 1 5810298 1-Jul-99 Skateboard 1 3310101 1-Jul-99 Life Vest 4 12510299 6-Jul-99 Parachute 1 125010339 27-Jul-99 Umbrella 1 4.510449 13-Aug-99 Unicycle 1 180.7910439 14-Aug-99 Ski Poles 2 25.510101 18-Aug-99 Rain Coat 1 18.310449 1-Sep-99 Snow Shoes 1 4510439 18-Sep-99 Tent 1 8810298 19-Sep-99 Lantern 2 2910410 28-Oct-99 Sleeping Bag 1 89.2210438 1-Nov-99 Umbrella 1 6.7510438 2-Nov-99 Pillow 1 8.510298 1-Dec-99 Helmet 1 2210449 15-Dec-99 Bicycle 1 380.510449 22-Dec-99 Canoe 1 28010101 30-Dec-99 Hoola Hoop 3 14.7510330 1-Jan-00 Flashlight 4 2810101 2-Jan-00 Lantern 1 16

10299 18-Jan-00Inflatable Mattress 1 38

10438 18-Jan-00 Tent 1 79.9910413 19-Jan-00 Lawnchair 4 3210410 30-Jan-00 Unicycle 1 192.510315 2-Feb-00 Compass 1 810449 29-Feb-00 Flashlight 1 4.510101 8-Mar-00 Sleeping Bag 2 88.710298 18-Mar-00 Pocket Knife 1 22.3810449 19-Mar-00 Canoe paddle 2 4010298 1-Apr-00 Ear Muffs 1 12.510330 19-Apr-00 Shovel 1 16.75

customers Exercises

customerid firstname lastname city state

10101 John Gray Lynden Washington10298 Leroy Brown Pinetop Arizona10299 Elroy Keller Snoqualmie Washington10315 Lisa Jones Oshkosh Wisconsin10325 Ginger Schultz Pocatello Idaho10329 Kelly Mendoza Kailua Hawaii

10330 Shawn DaltonCannon Beach Oregon

10338 Michael Howell Tillamook Oregon10339 Anthony Sanchez Winslow Arizona10408 Elroy Cleaver Globe Arizona10410 Mary Ann Howell Charleston South Carolina10413 Donald Davids Gila Bend Arizona10419 Linda Sakahara Nogales Arizona10429 Sarah Graham Greensboro North Carolina10438 Kevin Smith Durango Colorado10439 Conrad Giles Telluride Colorado10449 Isabela Moore Yuma Arizona

Exercises 1. Select the maximum price of any item ordered in the

items_ordered table. Hint: Select the maximum price only.2. Select the average price of all of the items ordered that were

purchased in the month of Dec. 3. What are the total number of rows in the items_ordered table? 4. 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.

5. 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”.

6. 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.

7. 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 functionSELECT quantity, max(price) FROM items_ordered GROUP BY

quantity;

Exercises REFRESHER - use of HAVING clause in group bySELECT 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_infoWHERE lastname IN ('Hernandez', 'Jones', 'Roberts', 'Ruiz');

The above is same as –

SELECT employeeid, lastname, salaryFROM employee_infoWHERE lastname = 'Hernandez' OR lastname = 'Jones' OR lastname = 'Roberts' OR lastname = 'Ruiz';

Using “Between”

SELECT employeeid, age, lastname, salaryFROM employee_infoWHERE age BETWEEN 30 AND 40;

The above is same as –

SELECT employeeid, age, lastname, salaryFROM employee_infoWHERE 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.itemFROM customer_info, purchasesWHERE 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

Ashish 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 raghute@yahoo.com.