1 / 28

COUNTING

COUNTING SQL has the ability to count and aggregate values across tuples. However since each SQL expression in the WHERE clause refers to individual tuples, counting usually is the last step. Recall that an SQL expression returns a bag of tuples.

bernad
Download Presentation

COUNTING

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. COUNTING • SQL has the ability to count and aggregate values across tuples. • However since each SQL expression in the WHERE clause refers to individual tuples, counting usually is the last step. • Recall that an SQL expression returns a bag of tuples. SELECT count(T.studid), count(DISTINCT T.sectionid) FROM Transcript T WHERE T.semester=‘Fall’ AND T.year=2002 AND T.grade=‘A’ AND T.crscode=‘CSCI4380’ How many tuples are returned by this expression? 4380 Database Systems - Fall 2002

  2. GROUP BY • Suppose we want to find how generous each professor is, by counting the total number of ‘A’s they give every semester in each class, and even compare with the class size. • We would like to return a relation with schema: • ProfId, SpecificClass, TotalAs, TotalAsByPercentage which lists the total number of As by the specific faculty in a specific class. 4380 Database Systems - Fall 2002

  3. GROUP BY First, match professors with the grades. SELECT C.instructorid, C.csrcode, C.semester, C.year, C.sectionNo, T.grade FROM Transcript T, Class C WHERE C.crscode = T.crscode AND C.semester = T.semester AND C.year = T.year AND C.sectionNo = T.sectionNo AND T.grade=‘A’ The next step is to count, but the following does not give us what we wanted: SELECT count(T.studId) FROM Transcript T, Class C WHERE C.crscode = T.crscode AND C.semester = T.semester AND C.year = T.year AND C.sectionNo = T.sectionNo AND T.grade 4380 Database Systems - Fall 2002

  4. GROUP BY Instead, first group tuples into groups for each specific instructor and course that they taught. FROM Transcript T, Class C WHERE C.crscode = T.crscode AND C.semester = T.semester AND C.year = T.year AND C.sectionNo = T.sectionNo AND T.grade=‘A’ GROUP BY C.instructorid, C.crscode, C.semester, C.year, C.sectionNo Instructor 1, 4380, Fall 2002, Section 1 Instructor 2, 4380, Fall 2002, Section 2 Instructor 3, 4380, Spring 2002, Section 1 Instructor 1, 4380, Fall 2001, Section 1 4380 Database Systems - Fall 2002

  5. GROUP BY For each group of tuples, we can now compute the necessary aggregates. We generate a new tuple for each different group. SELECT C.instructorid, C.crscode, C.semester, C.year, C.sectionNo, count(T.studId) TotalAs FROM Transcript T, Class C WHERE C.crscode = T.crscode AND C.semester = T.semester AND C.year = T.year AND C.sectionNo = T.sectionNo AND T.grade=‘A’ GROUP BY C.instructorid, C.crscode, C.semester, C.year, C.sectionNo Note that in this grouping, we cannot compare the total number of As with the total class size. We have already eliminated the students who did not get As. We will see how to do this next class. 4380 Database Systems - Fall 2002

  6. GROUP BY For all students, find the total number of credits hours they have completed. SELECT T.studId, sum(C.credithours) TotalHrs, count(T.crscode)/count(DISTINCT T.crscode) Repeats FROM Transcript T, Course C WHERE C.crscode = T.crscode AND UPPER(T.grade) IN (‘A’,’B’,’C’,’D’) GROUP BY T.studId Note that this query does not take into account if the student took the same course more than once. Then, the credit hours should only count once. We will see how to eliminate these tuples next class. 4380 Database Systems - Fall 2002

  7. GROUP BY / HAVING For all students, find the total number of credits hours they have completed. But, only return the students with at least 100 total credits hours. SELECT T.studId, sum(C.credithours) TotalHrs, count(T.crscode)/count(DISTINCT T.crscode) Repeats FROM Transcript T, Course C WHERE C.crscode = T.crscode AND UPPER(T.grade) IN (‘A’,’B’,’C’,’D’,’F’) GROUP BY T.studId HAVING sum(C.credithours) >= 100 4380 Database Systems - Fall 2002

  8. GROUP BY / HAVING • Group by creates groups, each containing a bag of tuples. • The Having clause applies to each single group. • If the group satisfies the having condition, then the whole group passes. Otherwise, the whole group is eliminated. • For each group, create a new tuple in the select statement. You can include some or all of the grouping attributes, and aggregate conditions. • Each aggregate condition is applied to each group separately. 4380 Database Systems - Fall 2002

  9. WRONG!!! WRONG!!! Find two things that are wrong with these statements!!! SELECT T.studId, T.sectionId, sum(C.credithours) TotalHrs FROM Transcript T, Course C WHERE C.crscode = T.crscode AND UPPER(T.grade) IN (‘A’,’B’,’C’,’D’,’F’) GROUP BY T.studId HAVING C.credithours >= 4 4380 Database Systems - Fall 2002

  10. WRONG!!! WRONG!!! Find two things that are wrong with these statements!!! SELECT T.studId, T.sectionId, sum(C.credithours) TotalHrs FROM Transcript T, Course C WHERE C.crscode = T.crscode AND UPPER(T.grade) IN (‘A’,’B’,’C’,’D’,’F’) GROUP BY T.studId HAVING C.credithours >= 4 • T.sectionId is not a unique value for each group!!! The statement is ambiguous • C.credithours is not a unique value for each group!!! The condition C.credithours >= 4 belongs in the WHERE clause. 4380 Database Systems - Fall 2002

  11. ORDER BY • The order by statement after select is used to order the tuples in the result. It is a simple sort statement. SQL complete: (SELECT […] FROM […] WHERE […] GROUP BY […] HAVING […] ) UNION […] UNION (SELECT […] FROM […] WHERE […] GROUP BY […] HAVING […] ) ORDER BY […] 4380 Database Systems - Fall 2002

  12. NULL VALUES • A null value usually means there is no value for a specific value. The reasons may be: • Value does not exist (yet). The grade for a course in progress does not exist. • Value is not known. We may know that a person has a phone, but we do not know the phone number. • It is not known whether a value exists or not. A student may or may not have non-campus e-mail address. 4380 Database Systems - Fall 2002

  13. NULL VALUES • To check whether a value is null or not, a specific predicate is used: WHERE T.grade IS NULL WHERE T.grade IS NOT NULL • For regular comparison conditions and other predicates, when the compared values are null, the condition evaluates to “unknown”. • WHERE T.grade = ‘A’ evaluates to unknown if T.grade is null • WHERE T.grade=T2.grade evaluates to unknown if either T.grade or T2.grade (or both) are null. 4380 Database Systems - Fall 2002

  14. NULL VALUES • Furthermore, we are given the following: • UNKNOWN AND TRUE = UNKNOWN • UNKNOWN OR TRUE = TRUE • UNKNOWN AND FALSE = FALSE • UNKNOWN OR FALSE = UNKNOWN • NOT (UNKNOWN) = UNKNOWN • UNKNOWN OR UNKNOWN = UNKNOWN • UNKNOWN AND UNKNOWN = UNKNOWN • Note that a tuple is returned by the WHERE clause, only if the condition evaluates to true. 4380 Database Systems - Fall 2002

  15. NULL VALUES SELECT T.studId, T.grade FROM Transcript T WHERE T.semester=‘Fall’ AND T.year=2002 AND NOT (T.grade IN (‘A’,’B’,’C’,’D’,’F’)) Does this select all null grades? Better select WHERE T.grade IS NULL or T.grade=‘I’ 4380 Database Systems - Fall 2002

  16. INNER JOIN Find all faculty and the classes they are teaching in Spring 2002. If they are not teaching a course, then simply return a null value next to the faculty name. First try: SELECT F.name, C.crscode, C.sectionNo FROM Faculty F, Class C WHERE C.instructorId=F.id AND C.semester=‘Spring’ AND C.year=2002 Unfortunately, this eliminates all instructors who do not teach in this semester. 4380 Database Systems - Fall 2002

  17. INNER JOIN Second try: SELECT F.name, C.crscode, C.sectionNo FROM Faculty F, Class C WHERE C.instructorId=F.id AND C.semester=‘Spring’ AND C.year=2002 UNION SELECT F.name FROM Faculty F WHERE (check if they have not taught courses in the Spring - we’ll see how next class) Too long, and the two relations are not union compatible. 4380 Database Systems - Fall 2002

  18. OUTER JOIN • A JOIN B, inner join selects tuples that satisfy a join condition, eliminates all tuples that do not satisfy the join condition. A is called the left operand and B is the right operand of the join operation. • A LEFT OUTER JOIN B returns all tuples in the inner join as well as the tuples in A that do not join with any tuples in in B. • A RIGHT OUTER JOIN B returns all tuples in the inner join as well as the tuples in B that do not join with any tuples in in A. • A FULL OUTER JOIN B returns all tuples in the inner join as well as the tuples from A and B that do not participate in the inner join. 4380 Database Systems - Fall 2002

  19. OUTER JOIN PARTSUPP JOIN SUPPLY ON Supplier=ID (3 tuples) PARTSUPP LEFT OUTER JOIN SUPPLY ON Supplier=Id 4380 Database Systems - Fall 2002

  20. INNER JOIN 4380 Database Systems - Fall 2002

  21. OUTER JOIN 4380 Database Systems - Fall 2002

  22. OUTER JOIN 4380 Database Systems - Fall 2002

  23. OUTER JOIN Find all faculty and the classes they are teaching in Spring 2002. If they are not teaching a course, then simply return a null value next to the faculty name. SELECT F.name, C.crscode, C.sectionNo FROM Faculty F LEFT OUTER JOIN Class C ON F.id = C.instructorId WHERE C.semester=‘Spring’ AND C.year=2002 If the faculty is not teaching any courses, then crscode and sectionNo fields will simply be null. 4380 Database Systems - Fall 2002

  24. CREATE VIEW • To create a view, any select statement can be used. CREATE VIEW Spring2002Teaching(Faculty, CrsCode, SectionId) AS SELECT F.name, C.crscode, C.sectionNo FROM Faculty F LEFT OUTER JOIN Class C ON F.id = C.instructorId WHERE C.semester=‘Spring’ AND C.year=2002 4380 Database Systems - Fall 2002

  25. CREATE VIEW • When a view is created, it is saved as a definition. Views are simply saved queries. • Views can be queried in the same way as an ordinary table. • The tuples in the view are constructed from the base tables whenever a view is queried. • Find all faculty who is not teaching a course in Spring 2002: SELECT S.faculty FROM Spring2002Teaching S WHERE S.crscode is null 4380 Database Systems - Fall 2002

  26. INSERT • To insert a new tuple: INSERT INTO faculty(Id, Name, DeptId) VALUES (10, ‘Legolas’, ‘ELF’) All unnamed attributes will be appended NULL values for this tuple. • To insert a number of tuples, use a select statement: INSERT INTO STUDENT(Id, Name) SELECT 10000+F.Id, F.Name FROM Faculty F WHERE F.deptid = ‘CS’ 4380 Database Systems - Fall 2002

  27. DELETE • Deleting tuples that satisfy a specific condition: DELETE FROM CLASS C WHERE C.year < 1998 • Delete all tuples: DELETE FROM CLASS C 4380 Database Systems - Fall 2002

  28. UPDATE • Update values in the tuples that satisfy the where condition: UPDATE Transcript T SET T.grade = ‘I’ WHERE T.year=2002 AND T.semester=‘Spring’ AND T.grade is null 4380 Database Systems - Fall 2002

More Related