1 / 16

Basic Structured Query Language (SQL)

Basic Structured Query Language (SQL). Sam Quantz Salt Lake City School District January 21, 2014. What is SQL. SQL is a special purpose programming language designed for managing data in a relational database ( http:// en.wikipedia.org/wiki/SQL )

darius-padilla
Download Presentation

Basic Structured Query Language (SQL)

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. Basic Structured Query Language (SQL) Sam Quantz Salt Lake City School District January 21, 2014

  2. What is SQL • SQL is a special purpose programming languagedesigned for managing data in a relational database (http://en.wikipedia.org/wiki/SQL) • DDL (data definition language) (database object statements such as create) • DML (data manipulation language) (statements like select, delete, and update)

  3. Query Tools • Microsoft Access (Good visual interface, not available on Macs) • Oracle SQL Developer (Free runs on Java and works on Windows or Macs) • Toad for Oracle (Expensive) • Others

  4. How to find Tables and View in PowerSchool • Tables vs. Views • Data Dictionary • DDE/DDA • Custom Page Management

  5. Parts of a SQL statement • SELECT column names • FROM table names (Can include more than one table through joins) • WHERE conditions • GROUP BY column names. Used to group data. For use with built in functions, such as COUNT(),SUM(),AVG(), etc… • ORDER BY column names.

  6. Now you try • Get student number, schoolid, grade_level • Schoolid 100 • Grade_level 9

  7. Answer Select student_number, schoolid, grade_level From students where schoolid=100 and grade_level=9

  8. Try again • Get student_number, schoolid, grade_level • Schoolid 100 • Grade_level 9 or grade_level 10

  9. Answer Select student_number, schoolid, grade_level From students where schoolid=100 and (grade_level=9 or grade_level=10)

  10. Table Joins • How tables relate to each other • Primary vs. Foreign Keys • Inner Joins • Outer Joins • Left • Right

  11. Foreign Keys Students Table Spenrollements Table

  12. Now you try Get name, student_number, program id, program start date, program exit date From students and spenrollments Studentid is 200

  13. Answer Select s.student_number, s.lastfirst, sp.programid, sp.enter_date, sp.exit_date From students s join spenrollmentssp on s.id = sp.studentid Where s.id = 200

  14. Accessing custom fields • View PVSIS_CUSTOM_STUDENTS • Use function ps_customfields.getstudentscf(studentid,field_name)

  15. Example Select s.student_number, s.lastfirst, sp.programid, sp.enter_date, sp.exit_date, ps_customfields.getstudentscf(s.id,'bike_brand') bike_brand From students s join spenrollmentssp on s.id = sp.studentid Where s.id = 200

  16. PowerViews • PVSIS_ • PSSIS_

More Related