Database Systems 600.415 HW2 - Sample solutions to QBE problems ======= Query 2 ======= List the names and gender of all people in the database who either teach or are enrolled in a computer science course. ----------------------------------------------------------------------- DEPARTMENT | DNO | Division | DName | Room | Building | DPhone -----------|-----|----------|----------------|------|----------|------- | _dno| |Computer Science| | | ---------------------------------------------------------------------------------- COURSE | CID | Cname | Credits | Instructor | Days | Hours | DNO -----------|-----|----------|----------------|------------|----------|-------|---- |_cid | | | | | | _dno | | | | _fid | | | _dno ----------------------------------- ENROLLED_IN | Stuid | CID | Grade ------------|--------|-----|------- |_sid |_cid | ---------------------------------------------------------------------------------- FACULTY | Facid | Fname | Lname | Rank | Sex | Phone | Room | Building -----------|-------|----------|--------------|-------|-----|-------|------|-------- |_fid | _fname | _lname | | _sex| | | ---------------------------------------------------------------------------------- STUDENT | Stuid | Fname | Lname | Age | Sex | Major | Advisor -----------|-------|----------|--------------|-------|-----|-------|-------------- |_sid | _fname | _lname | | _sex| | ----------------------------------------- RESULT |Fname | Lname | Sex -----------|-------|-------|------------- P. |_lname | _fname| _sex ======== Query 5 ======== List the name and major of all the students who are enrolled in more than 20 credits ---------------------------------------------------------------------------------- STUDENT | Stuid | Fname | Lname | Age | Sex | Major | Advisor -----------|-------|----------|--------------|-------|-----|--------|-------------- |G._sid | P.G_Fname| P.G_Lname | | | P.G_Maj| ---------------------------------------------------------------------------------- COURSE | CID | Cname | Credits | Instructor | Days | Hours | DNO -----------|-----|----------|----------------|------------|----------|-------|---- |_cid | |SUM.ALL._cr | | | | ----------------------------------- ENROLLED_IN | Stuid | CID | Grade ------------|--------|-----|------- |_sid |_cid | ---------------- |Conditions | |--------------| |SUM.ALL_cr >20| ======= Query 6 ======= List the name of all faculty members who advice a student who is enrolled in a course with a student who is a roommate of a student who majors or minors in the same department that the faculty is a member of. ---------------------------------------------------------------------------------- FACULTY | Facid | Fname | Lname | Rank | Sex | Phone | Room | Building -----------|-------|----------|--------------|-------|-----|-------|------|-------- |_fid | P. | P. | | | | | ----------------------------------- ENROLLED_IN | Stuid | CID | Grade ------------|--------|-----|------- |_sid1 |_cid1| |_sid3 |_cid1| ---------------------------------------------------------------------------------- STUDENT | Stuid | Fname | Lname | Age | Sex | Major | Advisor -----------|-------|----------|--------------|-------|-----|-------|-------------- |_sid2 | | | | | _maj | |_sid3 | | | | | | _fid ------------------------ MINOR_IN | Studid | DNO | ---------|--------|-----| |_sid2 |_dno | LIVES_IN | Stuid | DormID | Room_number ---------|-------|--------|------------ |_sid1 | _dorm | _room |_sid2 | _dorm | _room MEMBER_OF | Facid | DNO | Appt_Type ----------|-------|-----|---------- |_fid |_dno | ======== Query 7 ======== List the names of all the students who like every other student who is in their major ---------------------------------------------------------------------------------- STUDENT | Stuid | Fname | Lname | Age | Sex | Major | Advisor -----------|-------|----------|--------------|-------|-----|-------|-------------- |_sid1 | P. | P. | | | _maj | |_sid2 | | | | | _maj | |_sid3 | | | | | _maj | ------------------------------- LIKES | Wholikes | WhoisLiked --------|----------|----------- |_sid1 | |_sid2 | Conditions ------------ CNT.UNQ._sid2= CNT.UNQ._sid3 ========= Query 12 ========= List the names and genders of all pairs of students who love each other and are also roommates ----------------------------------------- LOVES | Wholoves | WhoIsLoved --------|---------------|---------------- |_wloves1 | _wloves2 |_Wloves2 | _wloves1 ---------------------------------------- LIVES_IN | Stuid | DromID | Room_number ---------|--------|--------|------------ |_wloves1| _dorm | _room |_wloves2| _dorm | _room ---------------------------------------------------------------------------------- STUDENT | Stuid | Fname | Lname | Age | Sex | Major | Advisor -----------|------- |----------|--------------|-------|-----|-------|-------------- |_wloves1| _fname1 | _lname1 | |_sex1| | |_wloves2| _fname2 | _lname2 | |_sex2| | -------------------------------------------------------------- RESULT | Fname | Lname |Sex |Fname |Lname | Sex -------------------------------------------------------------- P. | _fname1 |_lname1|_sex1 |_fname1|_lname2|_sex2 Conditions ----------------- _wloves1<_wloves2 ======= Query 14 ======= List the names of all pairs of roommates where there exists a third party who loves them both ----------------------------------------- LOVES | Wholoves | WhoIsLoved | --------|---------------|---------------- |_wloves3 | _wloves2 | |_wloves3 | _wloves1 | ---------------------------------------- LIVES_IN | Stuid | DormID | Room_number ---------|--------|--------|------------ |_wloves1| _dorm | _room |_wloves2| _dorm | _room ---------------------------------------------------------------------------------- STUDENT | Stuid | Fname | Lname | Age | Sex | Major | Advisor -----------|------- |----------|--------------|-------|-----|-------|-------------- |_wloves1| _fname1 | _lname1 | | | | |_wloves2| _fname2 | _lname2 | | | | ------------------------------------------ RESULT | Fname | Lname | Fname | Lname ------------------------------------------- P. | _fname1|_lname1|_fname2|_lname2 Conditions ----------------- _wloves1<_wloves2 ========= Query 20 ========= For all students, list their name and the total number of students who either like or love them. Do not count the same individual twice. Sort this total in descending order. ------------------------------- LIKES | Wholikes | WhoisLiked --------|----------|----------- |_wloves2 | _wloves1 ---------------------------------------------------------------------------------- STUDENT | Stuid | Fname | Lname | Age | Sex | Major | Advisor -----------|------- |----------|--------------|-------|-----|-------|-------------- |_wloves1| _fname1 | _lname1 | | | | ----------------------------------------- LOVES | Wholoves | WhoIsLoved | --------|---------------|---------------- |_wloves3 | _wloves1 | ------------------------------------------------------------------------ RESULT | Fname | Lname | Count ------------------------------------------------------------------------- P. | _fname1|_lname1| P.DO.(CNT.UNQ._wloves2 + CNT.UNQ._wloves3) Conditions -------------------- _wloves2 -| _wloves3 ======== Query 26 ======== List the names of all students who either like or is enrolled in a class with someone who either likes or is enrolled in a class with Linda Smith ---------------------------------------------------------------------------------- STUDENT | Stuid | Fname | Lname | Age | Sex | Major | Advisor -----------|------- |----------|--------------|-------|-----|-------|-------------- |_wlikes1| Smith | Linda | | | | |_wlikes3| P. | P. | | | | ------------------------------- LIKES | Wholikes | WhoisLiked --------|----------|----------- |_wlikes2 | _wlikes1 |_wlikes3 | _wlikes2 ----------------------------------- ENROLLED_IN | Stuid | CID | Grade ------------|--------|-----|------- |_wlikes1|_cid1| |_wlikes2|_cid1| |_wlikes2|_cid2| |_wlikes3|_cid2|