Test 2

profileDeepak Baid
contract.zip
Home>Computer Science homework help>Test 2

Contract.accdb

ContractID StudentID TeacherID ContractStartDate ContractEndDate LessonType LessonLength MonthlyLessonCost MonthlyRentalCost
3100 CAR7534 13-1100 3/31/12 3/31/13 Piano 30 ¤ 130.00 ¤ 0.00
3102 MEH7551 63-1554 5/18/13 5/18/14 Guitar 30 ¤ 100.00 ¤ 30.00
3103 MAK7556 34-4506 6/15/13 12/15/13 Violin 30 ¤ 100.00 ¤ 0.00
3105 BUR7559 22-0102 4/8/12 4/8/13 Cello 60 ¤ 280.00 ¤ 50.00
3107 MIL7512 22-0102 6/15/13 6/15/14 Piano 60 ¤ 260.00 ¤ 0.00
3108 SHA7522 63-1554 6/17/12 6/17/13 Guitar 30 ¤ 100.00 ¤ 30.00
3110 HIR7521 17-1798 9/9/12 9/9/13 Percussion 30 ¤ 120.00 ¤ 0.00
3113 YAM7535 91-0178 9/10/12 9/10/13 Violin 60 ¤ 280.00 ¤ 0.00
3114 MEN7541 70-4490 9/10/12 9/10/13 Guitar 60 ¤ 200.00 ¤ 30.00
3117 BRA7545 55-5310 3/11/13 3/11/14 Voice 30 ¤ 120.00 ¤ 0.00
3119 BAR7544 55-5310 9/15/12 3/15/13 Voice 30 ¤ 100.00 ¤ 0.00
3121 ROC7545 96-2151 9/20/13 9/20/14 Guitar 30 ¤ 100.00 ¤ 0.00
3122 SMI7538 17-1798 8/24/12 2/24/13 Percussion 30 ¤ 120.00 ¤ 75.00
3125 TEA7562 22-0102 10/1/12 10/1/13 Cello 60 ¤ 280.00 ¤ 50.00
3126 MCE7551 70-4490 12/1/12 6/1/13 Guitar 30 ¤ 100.00 ¤ 30.00
3128 FOR7548 72-0005 4/7/13 10/7/13 Violin 30 ¤ 100.00 ¤ 35.00
3130 TAN7549 72-0005 4/26/13 4/26/14 Violin 60 ¤ 200.00 ¤ 35.00
3133 LAM7537 63-1554 9/2/13 9/2/14 Saxophone 60 ¤ 200.00 ¤ 25.00
3137 BOL7533 63-1554 1/9/13 1/9/14 Guitar 60 ¤ 200.00 ¤ 30.00
3139 ECK7542 96-2151 7/15/13 7/15/14 Guitar 60 ¤ 200.00 ¤ 30.00
3141 KAL7530 13-1100 3/23/13 3/23/14 Piano 30 ¤ 130.00 ¤ 0.00
3144 LIM7553 70-4490 10/15/12 10/15/13 Guitar 30 ¤ 100.00 ¤ 0.00
3145 BRA7545 98-9123 9/14/13 9/14/14 Flute 30 ¤ 130.00 ¤ 25.00
3146 MIL7512 63-1554 12/1/13 6/1/14 Guitar 30 ¤ 100.00 ¤ 0.00
3147 GRE7502 96-2151 9/13/13 9/13/14 Saxophone 30 ¤ 100.00 ¤ 25.00
3148 APP7509 17-1798 2/1/12 2/1/13 Percussion 30 ¤ 120.00 ¤ 75.00
3150 THO7505 77-1201 2/2/13 2/2/14 Voice 30 ¤ 100.00 ¤ 0.00
3152 RIV7511 17-1798 2/2/13 8/2/13 Percussion 60 ¤ 260.00 ¤ 75.00
3153 PEN7515 98-9123 10/5/13 10/5/14 Flute 30 ¤ 130.00 ¤ 0.00
3156 HAM7518 96-2151 8/4/13 8/4/14 Saxophone 60 ¤ 200.00 ¤ 0.00
3157 YAM7535 13-1100 5/20/13 11/20/13 Piano 30 ¤ 130.00 ¤ 0.00
3158 GIL7529 91-0178 3/15/13 3/15/14 Violin 60 ¤ 280.00 ¤ 35.00
3162 CAR7534 13-1100 3/31/13 3/31/14 Piano 30 ¤ 130.00 ¤ 0.00
3164 MEH7551 63-1554 9/21/13 3/21/14 Saxophone 30 ¤ 100.00 ¤ 0.00
3165 BUR7559 22-0102 4/8/13 4/8/14 Cello 30 ¤ 140.00 ¤ 50.00
3166 COR7531 22-0102 6/16/12 12/16/12 Piano 30 ¤ 125.00 ¤ 0.00
3167 TAN7549 13-1100 11/3/13 11/3/14 Piano 30 ¤ 130.00 ¤ 0.00
3168 GAR7553 34-4506 6/16/12 6/16/13 Violin 30 ¤ 100.00 ¤ 0.00
3169 SHA7522 63-1554 6/17/13 6/17/14 Guitar 30 ¤ 100.00 ¤ 0.00
3170 KAL7530 34-4506 10/20/13 4/20/14 Violin 30 ¤ 100.00 ¤ 35.00
3171 HIR7521 17-1798 9/9/13 9/9/14 Percussion 60 ¤ 260.00 ¤ 0.00
3172 MEN7541 70-4490 9/10/13 9/10/14 Guitar 30 ¤ 100.00 ¤ 0.00
3174 BAR7544 77-1201 3/15/13 9/15/13 Voice 30 ¤ 100.00 ¤ 0.00
3175 SMI7538 17-1798 2/24/13 2/24/14 Percussion 60 ¤ 260.00 ¤ 0.00
3176 VAR7527 91-0178 3/21/13 3/21/14 Violin 30 ¤ 140.00 ¤ 35.00
3179 MCE7551 70-4490 6/1/13 6/1/14 Guitar 60 ¤ 200.00 ¤ 0.00
StudentID FirstName LastName Address City State Zip Phone BirthDate Gender
APP7509 Pat Nielson 15675 SW Greens Way Portland OR 97224 5039682245 10/10/96 M
BAR7544 Andrea Barreau 7660 SW 135th Ave Beaverton OR 97008 5035792277 11/28/99 F
BOL7533 Eddie Bolton 320 Main St Gresham OR 97236 5037605326 6/12/97 M
BRA7545 Lindsay Brandenburg 78 Calaveras Ct Beaverton OR 97007 5036724409 8/20/00 F
BUR7500 Joshua Burgess 11250 SW Walnut St Portland OR 97223 5415631135 1/12/99 M
BUR7559 Sara Burmeister 279 Summer St Cornelius OR 97113 5033578410 9/24/01 F
CAR7534 Nicki Carroll 1401 Lorraine Dr Beaverton OR 97229 5036173045 10/5/99 F
COR7531 Mary Beth Corbett 205 Cedar St Fairview OR 97030 5036740838 7/26/02 F
ECK7542 Mark Eckstein 12830 SW Trigger Dr Beaverton OR 97008 5035249610 3/27/01 M
FOR7548 Nicole Forrest 19210 SE Division St Portland OR 97266 5032562368 10/24/03 F
GAR7553 Brett Garver 17015 NW Holcomb Dr Portland OR 97229 5036908879 2/4/02 M
GIL7529 Aimee Gilbertson 11763 SE Reedway St Portland OR 97266 5037623027 11/29/98 F
GRE7502 Melissa Greco 1329 SE 135th Ave Portland OR 97233 5032564038 9/13/01 F
HAM7518 Akashi Hamada 130 Maple Ave Beaverton OR 97229 5036177433 4/4/00 M
HIR7521 Erica Hirsch 655 Langley Rd Cornelius OR 97123 5035472553 6/14/00 F
KAL7530 Ansis Kalnajs 5504 SW 14th Ave Portland OR 97239 5032468864 4/23/00 M
LAM7537 Blake Lamont 14305 SE Powell Blvd Portland OR 97236 5037611430 1/31/02 M
LIM7553 Youngho Lim 14320 SW Barlow Rd Beaverton OR 97008 5034306218 3/15/03 M
MAK7556 Kim Maki 15500 SW Allen St Portland OR 97223 5415636639 7/27/02 F
MCE7551 Danny McElroy 1965 Alpine St Cornelius OR 97113 5035301269 12/13/99 M
MEH7551 Amrita Mehta 18615 Yamhill Circle Portland OR 97233 5034910559 11/13/03 F
MEN7541 James Mendoza 1347 Lancewood St Beaverton OR 97008 5036461155 4/18/97 M
MIL7512 Andy Miller 6168 NE Rosebay Dr Hillsboro OR 97124 5036813379 2/16/99 M
NEL7584 Kayla Nelson 15540 Belleview Dr Portland OR 97229 5415633156 10/13/03 F
PAP7524 Teddy Pappas 78 Fairview Rd Beaverton OR 97007 5036726004 6/16/03 M
PEN7515 Dana Pennimore 4015 SE 149th Ave Portland OR 97236 5037618155 11/20/97 F
RIV7511 John Rivera 5436 NE Farmcrest St Hillsboro OR 97124 5036401517 5/22/99 M
ROC7545 Max Roche 2385 Overlook Dr Hillsboro OR 97124 5036173717 3/23/98 M
SHA7522 Stacey Shanahan 17440 Holcomb Dr Portland OR 97229 5415637998 8/22/02 F
SMI7538 Wilson Smith 1634 Marian St Gresham OR 97233 5032584232 10/19/98 M
TAN7549 Kendra Tang 2350 SE 157th Ave Portland OR 97233 5037617302 6/6/02 F
TEA7562 Jeff Tealey 304 Forest Ave Hillsboro OR 97124 5036171027 10/14/03 M
THO7505 Maddie Thorsten 60 Yearling Ct Beaverton OR 97008 5035242125 7/24/99 F
VAR7527 Kristin Vargas 11084 SE 63rd St Portland OR 97222 5037625928 9/18/98 F
YAM7535 Angie Yamamoto 8850 SW Burnham St Portland OR 97223 5036039451 9/30/96 F
TeacherID FirstName LastName Degree School HireDate TakesBeginners
13-1100 Yuka Koyama MM Pacific University 1/13/12 false
17-1798 Richard Jacobson PhD Pacific University 1/15/12 false
22-0102 Andre Dvorak BM University of Portland 3/3/12 false
34-4506 Marilyn Schwartz BM University of Portland 5/1/12 true
55-5310 Annamaria Romano BM Lewis & Clark College 3/12/12 true
63-1554 Carey Eberle BM University of Oregon 6/3/12 true
70-4490 Roger Norris BA University of Portland 9/2/12 true
72-0005 Sachi Tanaka BM Lawrence University 3/30/13 true
77-1201 Joy Culbertson BM Reed College 1/25/13 true
91-0178 Fred Bishop DMA Eastman School of Music 8/14/12 false
96-2151 Eric Mueller BA Whitman College 6/22/13 true
98-9123 Diana Reese MM Berklee College of Music 9/2/13 false
TeacherID FirstName LastName Degree School HireDate TakesBeginners
13-1100 Yuka Koyama MM Pacific University 1/13/12 false
17-1798 Richard Jacobson PhD Pacific University 1/15/12 false
22-0102 Andre Dvorak BM University of Portland 3/3/12 false
34-4506 Marilyn Schwartz BM University of Portland 5/1/12 true
55-5310 Annamaria Romano BM Lewis & Clark College 3/12/12 true
63-1554 Carey Eberle BM University of Oregon 6/3/12 true
70-4490 Roger Norris BA University of Portland 9/2/12 true
72-0005 Sachi Tanaka BM Lawrence University 3/30/13 true
77-1201 Joy Culbertson BM Reed College 1/25/13 true
91-0178 Fred Bishop DMA Eastman School of Music 8/14/12 false
96-2151 Eric Mueller BA Whitman College 6/22/13 true
98-9123 Diana Reese MM Berklee College of Music 9/2/13 false
TeacherID FirstName LastName Degree School HireDate TakesBeginners
13-1100 Yuka Koyama MM Pacific University 1/13/12 false
17-1798 Richard Jacobson PhD Pacific University 1/15/12 false
22-0102 Andre Dvorak BM University of Portland 3/3/12 false
34-4506 Marilyn Schwartz BM University of Portland 5/1/12 true
55-5310 Annamaria Romano BM Lewis & Clark College 3/12/12 true
63-1554 Carey Eberle BM University of Oregon 6/3/12 true
70-4490 Roger Norris BA University of Portland 9/2/12 true
72-0005 Sachi Tanaka BM Lawrence University 3/30/13 true
77-1201 Joy Culbertson BM Reed College 1/25/13 true
91-0178 Fred Bishop DMA Eastman School of Music 8/14/12 false
96-2151 Eric Mueller BA Whitman College 6/22/13 true
98-9123 Diana Reese MM Berklee College of Music 9/2/13 false
TeacherID FirstName LastName Degree School HireDate TakesBeginners
PARAMETERS __TeacherID Value; SELECT DISTINCTROW * FROM tblContract AS frmTeachersAndContracts WHERE ([__TeacherID] = TeacherID);
SELECT DISTINCTROW * FROM tblStudent;
SELECT DISTINCTROW * FROM tblTeacher;
SELECT DISTINCTROW * FROM tblTeacher;
SELECT [tblStudent].[FirstName], [tblStudent].[LastName] AS tblStudent_LastName, [tblStudent].[Phone], [tblContract].[ContractID], [tblTeacher].[LastName] AS tblTeacher_LastName, [tblContract].[LessonType], [tblContract].[LessonLength], [tblContract].[MonthlyLessonCost], [tblContract].[MonthlyRentalCost], [tblStudent].[StudentID] FROM tblTeacher INNER JOIN (tblStudent INNER JOIN tblContract ON tblStudent.StudentID=tblContract.StudentID) ON tblTeacher.TeacherID=tblContract.TeacherID;
SELECT DISTINCTROW * FROM tblTeacher;
SELECT tblTeacher.LastName, tblContract.StudentID, tblContract.ContractEndDate, tblContract.LessonLength, tblContract.MonthlyLessonCost FROM tblTeacher INNER JOIN tblContract ON tblTeacher.TeacherID = tblContract.TeacherID WHERE (((tblContract.ContractEndDate)>=#7/1/2013#) AND ((tblContract.LessonType)="Guitar")) ORDER BY tblTeacher.LastName, tblContract.StudentID;
SELECT tblTeacher.LastName, tblContract.StudentID, tblContract.ContractEndDate, tblContract.LessonType, tblContract.LessonLength, tblContract.MonthlyLessonCost FROM tblTeacher INNER JOIN tblContract ON tblTeacher.TeacherID = tblContract.TeacherID WHERE (((tblContract.ContractEndDate)>=#7/1/2013#)) ORDER BY tblTeacher.LastName, tblContract.StudentID;
SELECT tblTeacher.LastName, tblContract.StudentID, tblContract.ContractEndDate, tblContract.LessonType, tblContract.LessonLength, tblContract.MonthlyLessonCost FROM tblTeacher INNER JOIN tblContract ON tblTeacher.TeacherID = tblContract.TeacherID ORDER BY tblTeacher.LastName, tblContract.StudentID;
SELECT tblStudent.LastName, tblStudent.FirstName, tblStudent.Phone FROM tblStudent ORDER BY tblStudent.LastName, tblStudent.FirstName;
SELECT tblTeacher.FirstName, tblTeacher.LastName, tblTeacher.HireDate FROM tblTeacher ORDER BY tblTeacher.LastName, tblTeacher.FirstName;
SELECT tblStudent.Phone, tblStudent.Zip FROM tblTeacher INNER JOIN (tblStudent INNER JOIN tblContract ON tblStudent.StudentID = tblContract.StudentID) ON tblTeacher.TeacherID = tblContract.TeacherID WHERE (((tblStudent.Phone) Like "541*"));
SELECT tblStudent.FirstName, tblStudent.LastName, tblStudent.City, tblStudent.Phone FROM tblTeacher INNER JOIN (tblStudent INNER JOIN tblContract ON tblStudent.StudentID = tblContract.StudentID) ON tblTeacher.TeacherID = tblContract.TeacherID WHERE (((tblStudent.Phone) Like "541*")) ORDER BY tblStudent.LastName;
SELECT tblTeacher.LastName, tblContract.StudentID, tblContract.ContractEndDate, tblContract.LessonType, tblContract.LessonLength, tblContract.MonthlyLessonCost FROM tblTeacher INNER JOIN tblContract ON tblTeacher.TeacherID = tblContract.TeacherID WHERE (((tblContract.ContractEndDate)>=#7/1/2013#) AND ((tblContract.LessonType)="Cello")) OR (((tblContract.LessonType)="Flute")) OR (((tblContract.LessonType)="Violin")) ORDER BY tblTeacher.LastName, tblContract.StudentID;
SELECT tblStudent.FirstName, tblStudent.LastName, tblStudent.City, tblStudent.Phone FROM tblTeacher INNER JOIN (tblStudent INNER JOIN tblContract ON tblStudent.StudentID = tblContract.StudentID) ON tblTeacher.TeacherID = tblContract.TeacherID ORDER BY tblStudent.City;
SELECT tblStudent.FirstName, tblStudent.LastName, tblStudent.City, tblStudent.Phone FROM tblTeacher INNER JOIN (tblStudent INNER JOIN tblContract ON tblStudent.StudentID = tblContract.StudentID) ON tblTeacher.TeacherID = tblContract.TeacherID WHERE (((tblStudent.City)<>"Portland"));
SELECT tblTeacher.TeacherID, tblTeacher.Degree, tblTeacher.School, tblTeacher.HireDate, tblTeacher.TakesBeginners FROM tblTeacher ORDER BY tblTeacher.LastName, tblTeacher.FirstName DESC;
SELECT tblTeacher.TeacherID, tblTeacher.Degree, tblTeacher.School, tblTeacher.HireDate, tblTeacher.TakesBeginners, tblTeacher.[FirstName] & " " & [LastName] AS Name FROM tblTeacher;
SELECT [FirstName] & " " & [LastName] AS TeacherName FROM tblTeacher;
SELECT tblContract.ContractID, tblContract.StudentID, tblContract.TeacherID, tblContract.ContractStartDate, tblContract.ContractEndDate, tblContract.ContractEndDate, tblContract.LessonType, tblContract.LessonLength, tblContract.MonthlyLessonCost, tblContract.MonthlyRentalCost FROM tblContract WHERE (((tblContract.LessonType)=[What LessonType]));
TRANSFORM Count(tblContract.ContractID) AS CountOfContractID SELECT tblContract.LessonType FROM tblContract GROUP BY tblContract.LessonType PIVOT tblContract.LessonLength;
SELECT tblContract.[StudentID], tblContract.[LessonType], tblContract.[ContractID] FROM tblContract WHERE (((tblContract.[StudentID]) In (SELECT [StudentID] FROM [tblContract] As Tmp GROUP BY [StudentID],[LessonType] HAVING Count(*)>1 And [LessonType] = [tblContract].[LessonType]))) ORDER BY tblContract.[StudentID], tblContract.[LessonType];
SELECT tblStudent.StudentID, tblStudent.FirstName, tblStudent.LastName, tblStudent.Address, tblStudent.City, tblStudent.State, tblStudent.Zip, tblStudent.Phone, tblStudent.BirthDate, tblStudent.Gender FROM tblStudent LEFT JOIN tblContract ON tblStudent.[StudentID] = tblContract.[StudentID] WHERE (((tblContract.StudentID) Is Null));