select distinct -- 3. select results from the rows in (2) from -- 1. data sources -> a 'big' table where ; --2. filter the rows in (1): check the row in (1) -- one row at the time. -- all names of csci majors. select distinct fname, lname from student where major = 'CSCI'; -- all students with the major department codes. select distinct fname, lname, major from student; -- all students with the major department names (without missing department) select distinct fname, lname, deptName from student, department where department.deptCode = student.major; select distinct s.fname, s.lname, d.deptName from student as s, department d -- alias: good style where d.deptCode = s.major; select distinct s.fname, s.lname, d.deptName from student s join department d on (d.deptCode = s.major); -- join -- all students with the major department names (with missing department) select distinct s.fname, s.lname, d.deptName from student s left join department d on (d.deptCode = s.major); -- join -- all students with the major department names in school CSE (with missing department) select distinct s.fname, s.lname, d.deptName from student s left join department d on (d.deptCode = s.major) where d.schoolCode='CSE'; -- join -- all students with the major department names in -- school CSE or without a school (with missing department) select distinct s.fname, s.lname, d.deptName from student s left join department d on (d.deptCode = s.major) where (d.schoolCode='CSE' or d.schoolCode is null); -- join -- all class id with students 100004 and 100,005 enrolled. -- expected result: 10004 and 10005. select distinct e.classid -- 3 from enroll e -- 1. where e.stuId = 100004 and e.stuId = 100005; -- empty set. -- all class id with students 100004 or 100,005 enrolled. -- expected result: 10004 and 10005. select distinct e.classid -- 3 from enroll e -- 1. where e.stuId in (100004, 100005); -- all class id with students 100004 and 100,005 enrolled. -- expected result: 10004 and 10005. select distinct e1.classid -- 3 from enroll e1, enroll e2 -- 1. where e1.stuId = 100004 and e2.stuId = 100005; -- empty set. -- all class id with students 100002 and 100,004 enrolled. -- expected result: 10003 select distinct e1.classid -- 3 from enroll e1, enroll e2 -- 1. where e1.stuId = 100002 and e2.stuId = 100004; -- empty set. -- all class id with students 100002 and 100,004 enrolled. -- expected result: 10003 select distinct e1.classid, e1.stuId, e2.classId, e2.stuId -- 3 from enroll e1, enroll e2 -- 1. where e1.stuId = 100002 and e2.stuId = 100004; -- empty set. -- all class id with students 100002 and 100,004 enrolled. -- expected result: 10003 select distinct e1.classid -- 3 from enroll e1, enroll e2 -- 1. where e1.stuId = 100002 -- 2 and e2.stuId = 100004 and e1.classId = e2.classId; -- empty set. select major from student; select distinct major from student; select distinct e1.* from enroll e1; -- 2 select distinct e1.*, e2.* from enroll e1, enroll e2; -- 1. -- all class id with student 100002, but not 100,004 enrolled. -- expected results: 10000 and 10002 select distinct e1.* from enroll e1; select distinct e1.*, e2.* from enroll e1, enroll e2; -- 1. select distinct e1.*, e2.* from enroll e1, enroll e2 -- 1 where e1.stuId = 100002 -- 2 and e2.stuId = 100004 and e1.classId = e2.classId; select distinct e1.classid -- 3 from enroll e1, enroll e2 -- 1 where e1.stuId = 100002 -- 2 and e2.stuId = 100004 and e1.classId = e2.classId; -- all class id with student 100,002, but not 100,004 enrolled. -- expected results: 10000 and 10002 select distinct e.classid -- 3 from enroll e -- 1. where e.stuId = 100002 -- 2 and e.stuId <> 100004; -- 10,003 is in the result -> no good. select * from enroll; -- class by 100,002 select distinct e.classid -- 3 from enroll e -- 1. where e.stuId = 100002; -- 2 -- class by 100,004 select distinct e.classid -- 3 from enroll e -- 1. where e.stuId = 100004; -- 2 -- answer: -- select distinct e.classid -- 3 from enroll e -- 1. where e.stuId = 100002 -- class by 100,002 and e.classid not in -- class not enrolled by 100,0004 : subquery (select distinct e2.classid -- classes by 100,004 from enroll e2 -- 1. where e2.stuId = 100004); -- class id and the number of students enrolled in the class. -- enroll. select * -- 3 from enroll; -- 1 -- 2 filter the rows from step 1. select e.classId, count(*) -- 4 (groups only, no individual rows): group properties only -- count: group function. from enroll e -- 1 -- 2 filter the rows from step 1. group by e.classId; -- 3: distinct value of classId forms a unique group -- have groups only, no individual rows. select e.classId, -- 4 (groups only, no individual rows): group properties only count(*) as "Number of students" -- label as alias -- count: group function. from enroll e -- 1 -- 2 filter the rows from step 1. group by e.classId; -- 3: distinct value of classId forms a unique group -- have groups only, no individual rows. -- class id and the number of students enrolled in the class with an A or A-. select e.classId, -- 4 (groups only, no individual rows): group properties only count(*) as "Number of students" -- label as alias -- count: group function. from enroll e -- 1 -- 2 filter the rows from step 1. where (e.grade = 'A' or e.grade = 'A-') group by e.classId; -- 3: distinct value of classId forms a unique group -- have groups only, no individual rows. -- checking: select distinct * from enroll e -- 1 -- 2 filter the rows from step 1. where (e.grade = 'A' or e.grade = 'A-'); -- class id and the number of students enrolled in the class with an A or A-. select e.classId, -- 4 (groups only, no individual rows): group properties only count(*) as "Number of students", -- label as alias grade -- grade of a student; attribute of a row -> semantically incorrect. -- count: group function. -- row properties should not be included. from enroll e -- 1 -- 2 filter the rows from step 1. where (e.grade = 'A' or e.grade = 'A-') and count(*) > 10 -- error because no group has been formed in step 2. group by e.classId; -- 3: distinct value of classId forms a unique group -- have groups only, no individual rows. -- class id and the number of students enrolled in the class with an A or A-. -- Include only classes with 2 or more students select e.classId, -- 5 (groups only, no individual rows): group properties only count(*) as `Number of students` -- variable alias using backquote -- count: group function. from enroll e -- 1 -- 2 filter the rows from step 1. where (e.grade = 'A' or e.grade = 'A-') -- no no, cannot have: and count(*) >= 2 no group function allowed. group by e.classId -- 3: distinct value of classId forms a unique group -- have groups only, no individual rows. having count(*) >= 2; -- 4. filter the groups -- List the names of the products that use the material with id FINGLS select distinct p.ProductDescription from product_t p, uses_t u -- 1. data source where p.ProductID = u.ProductID and u.MaterialID = 'FINGLS'; select distinct p.ProductDescription from product_t p, uses_t u -- 1. data source where p.ProductID = u.ProductID and u.MaterialID = 'NF1';