WRITE the “compilable” SQL query. DRAW the resulting Relation. a. Retrieve all m

Question

WRITE the “compilable” SQL query. DRAW the resulting Relation.

a.  Retrieve all male students. Return Last Name, First Name, and Gender.

b. Retrieve t.utor Key, Course KEY, Session Date, and Student Key from scheduled sessions BETWEEN “11/1/2009” and “11/15/2009”.

c. Retrieve the COUNT of all students over 25 and rename the output Total over 25.

d. Retrieve t.utor Key and number of sessions (rename Total Sessions) of t.utors that have less than four sessions scheduled.

e. Create a view vw_Sessions for the students to look at the t.utoring sessions. It should contain

        T.utorLastName aliased as [T.utor],

StudentKey AS [ Student],

        SessionDateKey AS [ Date],

        SessionTimeKey AS [ Time],

        CourseKey AS [ Course]

f. Using the View vw_Sessions retrieve the distinct list of t.utors.

NOTE: I had to place a period in the word t.utor, becuase it's apparently an illegal word to post on here.

Tutor Table Data: Tutorkey 980010000 Robarts TulorLast Name TudorFIrstName TutorPhone TutorÉmalil TutorHireDate TuterStatus Marth Susan Danle Natian 2065551467 moberts@yahoo com 1/6/2010 Actve 10001 |Brown 90010012 Foster 980010003 Andn 980010004 ests 21/2009 Active 2065553490 Foster32aol.com 212/2009 Actve 3065556320 Null 2065652985 gngrndmal com 3/15/2009 32/2009 Course Table Data: CourseKey CourselName TC110 TC220 TC255 MAT107 ENG211 WEB110 ITC226 Introduction to Database Systams Analysis Applled Math Technical Witng Beginning Web Page Design Bac xrtm Daabase Administation CourseDescription Programming using C Overview of dalabase design and topics Systams anatysis and design Applied malth for computers Technlcal writing tor Information technology SQL Server administration Ethnicity Table Data: EtbnleityKey ElbnleltyDeseription Asan AtrAm Hispanic Padilic MIdhast Other White. European origin Chinese, Japanese, Korean, Southaast Asian lcan Amarican or of Atrican Mexican.Central or Soutn American, Carbbean Pasitic istander Arabic or Porstan Cther or not disclosed

Explanation / Answer

Hi, Please find the queries below:

a) select StudentLastName, StudentFirstName, StudentGender from Student where StudentGender='M';

b) Select T.utorKey, CourseKey, SessionDateKey, StudentKey from Sessions where SessionDateKey between "11/1/2009" and "11/15/2009";

c) select count(*) as "Total over 25" from Student where StudentAge>25;

d) Select T.utorKey, count(*) as "Total Sessions" from Session group by T.utorKey having count(*)<4;

e) Create view vw_Sessions as Select T.utorLastName as T.utor, StudentKey as Student, SessionDateKey as Date, SessionTimeKey as Time, CourseKey as Course from Session NATURAL JOIN T.utor;

f) Select DISTINCT(T.utor) from vw_Sessions;

NOTE: I had to place a period in the word t.utor, becuase it's apparently an illegal word to post on here.

Kindly let me know if you have any doubts in the solution!

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.