cset3300-module-2c

Id courseofferingscourseid and coursescourseno like

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: t or me the min or max values Example: Find the number of students in a given section of a course SELECT COUNT(*) FROM enrollment WHERE course_offering_id = 42 Example: Find the average # of credits for CSET courses 10 Aggregations •SUM, COUNT, AVG,MIN,MAX •NULL Values never contribute to the sum, average, or count or me the min or max values Example: Find the number of students in a given section of a course SELECT COUNT(*) FROM enrollment WHERE course_offering_id = 42 Example: Find the average # of credits for CSET courses SELECT AVG(credits) FROM courses WHERE course_no LIKE “CSET%” 10 JOINs •Suppose you want to view every section of every CSET course offered in a given semester along with the number of credits, course_no, and the title of the course • Problem: you want information from 2 tables at the same time • Solution: need to JOIN tables together • INNER JOIN is most common type: 11 JOINs •Suppose you want to view every section of every CSET course offered in a given semester along with the number of credits, course_no, and the title of the course • Problem: you want information from 2 tables at the same time • Solution: need to JOIN tables together • INNER JOIN is most common type: SELECT * FROM courses,course_offerings WHERE courses.id = course_offerings.course_id AND courses.course_no LIKE “CSET%” 11 Example Join Courses Table mysql> SELECT * FROM courses; + ­ ­ ­ ­+ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­+ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­+ ­ ­ ­ ­ ­ ­ ­ ­ ­+ | id | course_no | title | credits | + ­ ­ ­ ­+ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­+ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­...
View Full Document

Ask a homework question - tutors are online