DD Class11

From IAMMediaWiki Create Change

Contents 1 Review Data Diagram for final 2 Sql Aggregate Functions 3 Sub Query 4 Uploader/Email 4.1 Send Mail 5 HomeWork

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

 
SELECT MAX(PricePerPound) AS MaxPrice FROM CheesePrice
 
SELECT AVG(PricePerPound) AS AvgPrice FROM CheesePrice
 
SELECT COUNT(CheeseID) AS Count FROM CheesePrice

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

 
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

 
SELECT CourseID, CourseName, CourseNumber 
FROM Course 
WHERE 
(
   CourseID NOT IN 
   (
      SELECT CourseID FROM StudentsCourses_vw WHERE StudentID = @StudentID
   )
)

This query has two parts

SELECT CourseID FROM StudentsCourses_vw WHERE StudentID = 1

returns

CourseID
9
10

then

 
SELECT CourseID, CourseName, CourseNumber 
FROM Course 
WHERE 
(
   CourseID NOT IN 
   (
      SELECT CourseID FROM StudentsCourses_vw WHERE StudentID = 1
   )
)

returns

CourseID	CousreName	CourseNumber
11	Data Design	36-2601
168	Application Design	36-4601
169	OOP	36-2001

Course adder example

http://iam.colum.edu/dd/classsource/class8/student.aspx

SQL Subquery

Uploader/Email

Send Mail

<% @Page Language="C#" %>
<% @Import Namespace="System.Web.Mail" %>
<%
    //set up some strings for the email
    
    string strTo = "only_a_test@fastmail.fm";
    string strFrom = "jeff@interactive.colum.edu";
    string strSubject = "Hi jeff";
    string strBody = "A real nice body text here";
    
    //Send email
    SmtpMail.SmtpServer = "localhost";
    SmtpMail.Send(strFrom, strTo, strSubject, strBody);
%>

http://iam.colum.edu/oop/classsource/class14/mail.aspx [-source]

HomeWork

Web Skeleton of your site

Demostate one feature