Basic SQL

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