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
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
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
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
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
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;
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.
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;
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
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.
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
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.
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
Presenter Information • Tarleton State University • Office of Planning, Evaluation & Institutional Research • Box-T 0505Stephenville, TX 76402 • http://www.tarleton.edu/~opeir