Labs for SQL Training

1. Labs for SQL Training Ashish Raghute, Manager of Applications Development, Fleetwood Enterprises

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

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

4. 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:

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

6. Exercises • 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".

7. 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: • Select all columns for everyone in your employee table. • Select all columns for everyone with age over 30. • Select first and last names for everyone that's under 30 years old. • Select all columns for everyone whose last name contains "ebe". • Select the first name for everyone whose first name equals "Potsy". • Select all columns for everyone over 80 years old. • Select all columns for everyone whose last name ends in "ith".

8. Exercises Update your employee table (see example below) Update employee set age=33 where first=‘Potsy’; • Write and execute updates for the following – • 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 – • 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

9. Exercises Delete examples - delete from employee where lastname = 'May'; delete from employee where firstname = 'Mike' or firstname = 'Eric'; • Try these: • 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.

10. 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>;

11. 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 – createtable 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.

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

13. 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!

14. Exercises items_ordered

15. Exercises customers

16. Exercises • 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;

17. Exercises • REFRESHER - use of HAVING clause in group by • SELECT dept, avg(salary) FROM employeeGROUP 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'.

18. Exercises Some Refreshers - Some typical “where” clauses with IN and BETWEEN usage Using “IN” SELECT employeeid, lastname, salaryFROM 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;

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

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

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