1 / 26

TAIR Galveston 2008

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?

vlora
Télécharger la présentation

TAIR Galveston 2008

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

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

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

  14. Take it step by step…

  15. Narrow down the data with a WHERE

  16. Use an AND to add more conditions

  17. Exclude rows with a NOT IN

  18. Use a DISTINCT to return a row only once

  19. Concatenate fields

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

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

  22. Questions?

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

More Related