TAIR Galveston 2008 - TAIR-Texas Association for
Download
Report
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