1 / 108

Basic SQL

Basic SQL. Joe Meehean. What we are going to Learn. How do we access the data stored in a DBMS? What happens when we access data in a DBMS? How do we add or modify data in a DBMS?. Structured Query Language (SQL). Often pronounced “sequel” Standardized interface for interacting with DBMSs

gay-holt
Download Presentation

Basic 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 SQL Joe Meehean

  2. What we are going to Learn • How do we access the data stored in a DBMS? • What happens when we access data in a DBMS? • How do we add or modify data in a DBMS?

  3. Structured Query Language (SQL) • Often pronounced “sequel” • Standardized interface for interacting with DBMSs • as standardized as any CS interface gets • Oracle, DB2, SQL Server, MySQL, PostgreSQL,… • Used to • create/define tables • destroy tables • look up data • insert/modify data • delete data

  4. Structured Query Language (SQL) • Declarative language • states what answer should look like • NOT how to compute it • e.g. “Who are the students with GPAs < 2.0?”

  5. Querying Tables • Looking up information in relational tables • Use the SQL SELECT statement • SELECT <list of columns>FROM table • Result of a SELECT is another table

  6. Extracting Columns • SELECT LastName, MajorFROM Students Students

  7. Extracting Columns • SELECT LastName, MajorFROM Students Students

  8. Extracting Columns • SELECT LastName, MajorFROM Students Result

  9. Extracting Columns • SELECT LastName, MajorFROM Students • Note the duplicate rows in the result Result

  10. Extracting Columns • SELECT DISTINCT LastName, MajorFROM Students • DISTINCT removes duplicate rows Result

  11. Getting All the Columns • SELECT * • returns all columns Students SELECT * FROM Students

  12. Getting All the Columns • SELECT * • returns all columns Result SELECT * FROM Students

  13. Calculated Fields • Create new columns using arithmetic • Can use +, −, *, /, ( ) • Should name new column • Use the AS keyword

  14. Calculated Fields Parts SELECT PartNo, Pdesc, Pqty * Wholsale AS StockCost FROM Parts

  15. Calculated Fields Result SELECT PartNo, Pdesc, Pqty * Wholsale AS StockCost FROM Parts

  16. Extracting Specific Rows • Add the WHERE clause to the SELECT • SELECT <columns,…>FROM <table>WHERE <condition> • WHERE conditions similar to C++ conditions

  17. Extracting Specific Rows Students

  18. Extracting Specific Rows Students SELECT LastName, FirstName FROM Students WHERE GPA >= 3.5

  19. Extracting Specific Rows Students SELECT LastName, FirstName FROM Students WHERE GPA >= 3.5

  20. Extracting Specific Rows Students SELECT LastName, FirstName FROM Students WHERE GPA >= 3.5

  21. Extracting Specific Rows Result SELECT LastName, FirstName FROM Students WHERE GPA >= 3.5

  22. Extracting Specific Rows • SQL supports following comparison operators • = : equals • <, <= : less than, less than or equal to • >, >= : greater than, greater than or equal to • <> : not equal • Combination of conditions • OR: inclusive • AND • NOT

  23. Extracting Specific Rows Parts SELECT PartNo, Pdesc, Pqty FROM Parts WHERE PDesc = ‘5mm wrench’ OR Pdesc = ‘10mm nut’

  24. Extracting Specific Rows Result SELECT PartNo, Pdesc, Pqty FROM Parts WHERE PDesc = ‘5mm wrench’ OR Pdesc = ‘10mm nut’

  25. Extracting Specific Rows • LIKE operator • used for inexact string matching • like regular expressions • * wild card for any series of characters • e.g., ‘CS*’ matches strings prefixed with CS • e.g., ‘*Algebra*’ matches strings that contain Algebra • _ matches a single character • e.g., ‘CS24_’ matches CS241 and CS242, but not CS242A

  26. Extracting Specific Rows Parts SELECT PartNo, Pdesc, Pqty FROM Parts WHERE PDesc LIKE ‘5mm *’

  27. Extracting Specific Rows Result SELECT PartNo, Pdesc, Pqty FROM Parts WHERE PDesc LIKE ‘5mm *’

  28. Extracting Specific Rows • Matching a range • e.g., parts that cost between $0.50 and $1.00 • use the BETWEEN-AND operator Parts SELECT * FROM Parts WHERE Wholesale BETWEEN 0.50 AND 1.00

  29. Extracting Specific Rows • Matching a range • e.g., parts that cost between $0.50 and $1.00 • use the BETWEEN-AND operator Result SELECT * FROM Parts WHERE Wholesale BETWEEN 0.50 AND 1.00

  30. Extracting Specific Rows • Testing for null • IS NULL • IS NOT NULL Offering SELECT CourseNo, SectionNo FROM Offering WHERE CourseNo = ‘CS242’ AND FacultyID IS NOT NULL

  31. Extracting Specific Rows • Testing for null • IS NULL • IS NOT NULL Result SELECT CourseNo, SectionNo FROM Offering WHERE CourseNo = ‘CS242’ AND FacultyID IS NOT NULL

  32. Questions?

  33. Sorting the Results • Want sorted results • ORDER BY clause • Ascending order by default • Add DESC for descending order • More than 1 column • sorts by 1st column, then 2nd, 3rd, etc…

  34. Sorting the Results Students SELECT LastName, GPA FROM Students ORDER BY GPA

  35. Sorting the Results Results SELECT LastName, GPA FROM Students ORDER BY GPA

  36. Sorting the Results Students SELECT LastName, GPA FROM Students ORDER BY GPA DESC

  37. Sorting the Results Results SELECT LastName, GPA FROM Students ORDER BY GPA DESC

  38. Sorting the Results Students SELECT Major, GPA FROM Students ORDER BY Major, GPA DESC

  39. Sorting the Results Students SELECT Major, GPA FROM Students ORDER BY Major, GPA DESC

  40. Data Aggregation • Summarizing data • e.g., average GPA of all students • called data aggregation • COUNT(*) • computes the total number of rows • COUNT(column) • counts the non-null values in a column • counts duplicates • COUNT( DISTINCT column) • counts the non-null unique values in a column

  41. Data Aggregation • AVG • computes the average of a numeric column • excludes null values • adding DISTINCT computes average of unique column values • SUM • computes the sum of a numeric column • excludes null values • adding DISTINCT computes sum of unique column values

  42. Data Aggregation • MIN • finds the smallest value • works on both numeric and string columns • MAX • finds the largest value • works on both numeric and string columns

  43. Data Aggregation Students SELECT AVG(GPA) AS AvgGPA FROM Students

  44. Data Aggregation Result SELECT AVG(GPA) AS AvgGPA FROM Students

  45. Data Aggregation Students SELECT MAX(GPA) AS MaxGPA FROM Students

  46. Data Aggregation Result SELECT MAX(GPA) AS MaxGPA FROM Students

  47. Data Aggregation Offering SELECT COUNT(*) AS NumOfferings, COUNT(DISTINCT CourseNo) AS NumCourse FROM Offering WHERE Term = ‘Spring’

  48. Data Aggregation Result SELECT COUNT(*) AS NumOfferings, COUNT(DISTINCT CourseNo) AS NumCourses FROM Offering WHERE Term = ‘Spring’

  49. Joining Tables • Combining multiple tables into a single query • Join • most common way to query multiple tables • combines related rows across several tables • rows combined must have common value in column Offering Course

  50. Joining Tables • Combining multiple tables into a single query • Join • most common way to query multiple tables • combines related rows across several tables • rows combined must have common value in column Result

More Related