Difference between revisions of "DD Class11"

Revision as of 03:25, 12 April 2007

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

Student

StudentCourses

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>

This query has two parts

SELECT CourseID FROM StudentsCourses_vw WHERE StudentID = 1

returns

then

<sql> SELECT CourseID, CourseName, CourseNumber FROM Course WHERE (

  CourseID NOT IN 
  (
     SELECT CourseID FROM StudentsCourses_vw WHERE StudentID = 1
  )

) </sql>

returns

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