TAIR Galveston 2008

1. TAIR Galveston 2008 Ad Hoc Query Reporting – SQL 101 Tips, Tools & Techniques Ms. Bonnie Hurford, Tarleton State University Ms. Lauren Morton, Tarleton State University Wednesday, February 6th, 3:30 p.m. Concurrent Sessions F

2. Topics for Discussion • What is SQL? • Overview of SQL Statements • Syntax for SELECT • Joins • Restricting Rows • Operations • Functions • CASE Statement • I wish I knew then… • IR SQL Example • Questions & Answers

3. What is SQL Anyway? • Structured Query Language (SQL) is the industry standard for interacting with relational databases • Processes sets of data as groups and navigates the data stored within various tables • Create, modify tables, enter & maintain data & retrieve data • From “Oracle 9i: SQL with an Introduction to PL/SQL” by Morris-Murphy

4. Basic SQL Statements Data Manipulation Language (DML) • SELECT: retrieve a set of data stored in the database. • INSERT INTO: add new rows of data into a table • UPDATE: changes existing data in your database • DELETE: removes rows of data from a table

5. SQL Data Definition Language (DDL) Basic SQL Statements • CREATE TABLE - creates a new database table • ALTER TABLE - changes a table • DROP TABLE - deletes a database table • CREATE INDEX - creates an index (search key) • DROP INDEX - deletes an index

6. SELECT Statement Syntax SELECT [DISTINCT] * or column1, column2 AS alias… FROM table1, table2… WHERE conditions GROUP BY column1, column2… HAVING group conditions ORDER BY column1,… ASC | DESC;

7. Operations within theSELECT Statement • Column Alias – assign meaningful name to a column • Example: majr_code1 AS major • DISTINCT – eliminates duplicates • SELECT DISTINCT id, l_name, f_name… • Arithmetic Operations – multiply *, divide /, add +, subtract – • SELECT (on_campus_hrs + off_campus_hrs) AS SCH • Concatenation – combine two or more columns • l_name || ‘, ’ || f_name • Smith, John • Single Row Functions • SUBSTR, TRUNC, UPPER, LOWER, LENGTH, etc.

8. Operations within theSELECT Statement • Group Functions (multiple row function) • AVG, SUM, COUNT MAX, MIN • CASE Statement – assign more meaningful descriptions to a value in a column • (CASE WHEN gender_code = ‘F’ THEN ‘Female’ WHEN gender_code = ‘M’ THEN ‘Male’ ELSE ‘Not Reported’ END) AS gender;

9. WHERE Statements (Restricting Rows) • Mathematical Comparison Operators • = <> < > <= >= • WHERE year = ‘2008’ • Other Comparison Operators • IN, BETWEEN x AND y, LIKE, IS NULL • WHERE year IN (‘2008’, ‘2007’, ‘2006’) • Logical Operators • AND, OR, NOT

10. Joining Tables • Equality/Simple Join – method used to combine two or more tables based on a common column. • SELECT a.crn, a.subject, a.crs_num, b.fac_nameFROM courses a, faculty bWHERE a.fac_id = b.fac_id; • “a” is known as the column qualifier and indicates the from which table the column is being referenced.

11. I Wish I Knew Then… • Start a library of base SQL code • Registered students, Admitted students • Get to know your data • Data Types, Formats, Tables, Views • Use a SQL software tool • PL/SQL Developer • Toad • SQL Developer • SQL*Plus • Keep your reference materials on hand • SQL 101 Books • SQL Code Websites

15. http://www.w3schools.com/sql/

16. SQL Example • A faculty member would like a list of Fall 2007 Registered Students that have not declared a major so that he can advise them. • He does not want to include students attending the Killeen and Gatesville campuses. • He would like to include phone number and gender, so he will know to address them as Mr. or Ms. when he contacts them.

17. Take it step by step…

18. Narrow down the data with a WHERE

19. Use an AND to add more conditions

20. Exclude rows with a NOT IN

21. Use a DISTINCT to return a row only once

22. Concatenate fields

23. Use a CASE statement to make the data easier for users

24. Finish the request • Ship the data to the user • Most SQL tools will provide a way to export the data to other formats like Excel or .PDF

25. Questions?

26. Presenter Information • Tarleton State University • Office of Planning, Evaluation & Institutional Research • Box-T 0505Stephenville, TX 76402 • http://www.tarleton.edu/~opeir