cset3300-module-2c

G many sections of english 1 need to get rid of

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: record> IN <set> is true if and only if the tuple is a member of the relation. •<record> NOT IN <set> means the opposite. •IN-expressions can appear in WHERE clauses. •The value of <set> is often the result of a subquery. 7 Sub-Queries •Find all other students that were in the same course/section (course_offering) at the same time as “Lisa Jones” for every course she has ever taken • Find all course offerings in which “Lisa Smith” (id = 16) was enrolled • Find all students that also were enrolled in the same course offering 8 Sub-Queries •Find all other students that were in the same course/section (course_offering) at the same time as “Lisa Jones” for every course she has ever taken • Find all course offerings in which “Lisa Smith” (id = 16) was enrolled • Find all students that also were enrolled in the same course offering SELECT student_id FROM enrollment WHERE course_offering_id IN ( SELECT course_offering_id FROM enrollment WHERE student_id = 16) 8 Distinct •Problem find the courses being offered for Fall Semester 2010 • A simple select of course_id/course_no in course_offering will return lots of duplicates • e.g., many sections of English 1 • Need to get rid of duplicates: use DISTINCT 9 Distinct •Problem find the courses being offered for Fall Semester 2010 • A simple select of course_id/course_no in course_offering will return lots of duplicates • e.g., many sections of English 1 • Need to get rid of duplicates: use DISTINCT SELECT DISTINCT course_id FROM course_offerings WHERE semester = “Fall” AND year = “2010” 9 Aggregations •SUM, COUNT, AVG,MIN,MAX •NULL Values never contribute to the sum, average, or count or me the min or max values 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 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 10 Aggregations •SUM, COUNT, AVG,MIN,MAX •NULL Values never contribute to the sum, average, or coun...
View Full Document

This document was uploaded on 02/25/2014 for the course CSET 3300 at Toledo.

Ask a homework question - tutors are online