Difference between revisions of "DD Class11"
(→Sub Query) |
(→Sub Query) |
||
| Line 46: | Line 46: | ||
Here a student has many course and a course has many student. Imagine in the registration system the student will be presented with a list of classes that they can add. That list of classes '''should not''' include the courses that they are already taking. | Here a student has many course and a course has many student. Imagine in the registration system the student will be presented with a list of classes that they can add. That list of classes '''should not''' include the courses that they are already taking. | ||
Course | Course | ||
| − | {| | + | {| class="wikitable" cellpadding="5" cellspacing="0" |
!CourseID !! CourseName !! CourseNumber | !CourseID !! CourseName !! CourseNumber | ||
|- | |- | ||
| Line 61: | Line 61: | ||
Student | Student | ||
| − | {| | + | {| class="wikitable" cellpadding="5" cellspacing="0" |
!StudentID !! PeopleID !! OasisID | !StudentID !! PeopleID !! OasisID | ||
|- | |- | ||
| Line 71: | Line 71: | ||
StudentCourses | StudentCourses | ||
| − | {| | + | {| class="wikitable" cellpadding="5" cellspacing="0" |
! StudentID !! CourseID !! SemesterID | ! StudentID !! CourseID !! SemesterID | ||
|- | |- | ||
| Line 81: | Line 81: | ||
|} | |} | ||
| − | Course | + | StudentsCourses_vw |
| − | + | <sql> | |
| − | + | SELECT dbo.Student.PeopleID, dbo.Student.OasisID, dbo.StudentCourses.SemesterID, dbo.Course.CourseName, | |
| + | dbo.Course.CourseNumber, dbo.Student.StudentID, | ||
| + | dbo.Course.CourseID | ||
| + | FROM dbo.StudentCourses INNER JOIN | ||
| + | dbo.Student ON dbo.StudentCourses.StudentID = dbo.Student.StudentID INNER JOIN | ||
| + | dbo.Course ON dbo.StudentCourses.CourseID = dbo.Course.CourseID | ||
| + | </sql> | ||
| − | + | [[Image:StudentsCourses_vw.png]] | |
| − | <sql>SELECT CourseID, CourseName, CourseNumber | + | <sql> |
| + | SELECT CourseID, CourseName, CourseNumber | ||
FROM Course | FROM Course | ||
WHERE | WHERE | ||
| Line 97: | Line 104: | ||
) | ) | ||
</sql> | </sql> | ||
| + | |||
| + | |||
| + | Course adder example | ||
| + | |||
| + | http://iam.colum.edu/dd/classsource/class8/student.aspx | ||
| + | |||
| + | SQL Subquery | ||
==Uploader/Email== | ==Uploader/Email== | ||
Revision as of 03:07, 12 April 2007
Contents
Review Data Diagram for final
- are they normal?
- can the design acomplish your goals?
- are they over engineered?
Sql Aggregate Functions
They perform a calculation on a set to values and return a single value. Aggregate functions are determinate. They will return the same value given the same dataset.
- AVG
- MIN
- CHECKSUM
- SUM
- CHECKSUM_AGG
- STDEV
- COUNT
- STDEVP
- COUNT_BIG
- VAR
- GROUPING
- VARP
- MAX
<sql> SELECT MAX(PricePerPound) as MaxPrice from CheesePrice
SELECT AVG(PricePerPound) as AvgPrice from CheesePrice
SELECT COUNT(CheeseID) as Count from CheesePrice </sql>
Sub Query
A query may be a part of another query. This allows
IN vs EXSISTS
Remember the Many to Many relation ship in the Student Registration System
Here a student has many course and a course has many student. Imagine in the registration system the student will be presented with a list of classes that they can add. That list of classes should not include the courses that they are already taking. Course
| CourseID | CourseName | CourseNumber |
|---|---|---|
| 9 | Science | 66-0002 |
| 10 | Math | 66-0001 |
| 11 | Data Design | 36-2601 |
| 168 | Application Design | 36-4601 |
| 169 | OOP | 36-2001 |
Student
| StudentID | PeopleID | OasisID |
|---|---|---|
| 1 | 1 | 666666 |
| 2 | 2 | 777777 |
StudentCourses
| StudentID | CourseID | SemesterID |
|---|---|---|
| 1 | 9 | 1 |
| 2 | 9 | 1 |
| 1 | 10 | 1 |
StudentsCourses_vw <sql> SELECT dbo.Student.PeopleID, dbo.Student.OasisID, dbo.StudentCourses.SemesterID, dbo.Course.CourseName,
dbo.Course.CourseNumber, dbo.Student.StudentID, dbo.Course.CourseID
FROM dbo.StudentCourses INNER JOIN
dbo.Student ON dbo.StudentCourses.StudentID = dbo.Student.StudentID INNER JOIN dbo.Course ON dbo.StudentCourses.CourseID = dbo.Course.CourseID
</sql>
<sql> SELECT CourseID, CourseName, CourseNumber FROM Course WHERE (
CourseID not in
(
SELECT CourseID FROM StudentsCourses_vw WHERE StudentID = @StudentID
)
) </sql>
Course adder example
http://iam.colum.edu/dd/classsource/class8/student.aspx
SQL Subquery
Uploader/Email
Mappath
Server.Mappath
upload permissions
HomeWork
Web Skeleton of your site
Demostate one feature
