Transcript TAIR Galveston 2008 - TAIR-Texas Association for

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
Basic SQL Statements
SQL Data Definition Language (DDL)





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 the
SELECT Statement

Column Alias – assign meaningful name to a column


DISTINCT – eliminates duplicates


SELECT (on_campus_hrs + off_campus_hrs) AS SCH
Concatenation – combine two or more columns



SELECT DISTINCT id, l_name, f_name…
Arithmetic Operations – multiply *, divide /, add +,
subtract –


Example: majr_code1 AS major
l_name || ‘, ’ || f_name
Smith, John
Single Row Functions

SUBSTR, TRUNC, UPPER, LOWER, LENGTH, etc.
Operations within the
SELECT 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_name
FROM courses a, faculty b
WHERE 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


Get to know your data


Data Types, Formats, Tables, Views
Use a SQL software tool





Registered students, Admitted students
PL/SQL Developer
Toad
SQL Developer
SQL*Plus
Keep your reference materials on hand


SQL 101 Books
SQL Code Websites
http://www.w3schools.com/sql/
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.
Take it step by step…
Narrow down the data with a
WHERE
Use an AND to add more
conditions
Exclude rows with a NOT IN
Use a DISTINCT to return a
row only once
Concatenate fields
Use a CASE statement to
make the data easier for users
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
Questions?
Presenter Information




Tarleton State University
Office of Planning, Evaluation &
Institutional Research
Box-T 0505
Stephenville, TX 76402
http://www.tarleton.edu/~opeir