Databases 600.315/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 16 ========= List the name of the student liked by the greatest number of students. If a tie, list all tied winners. ---------------------------------------------------------------------------- STUDENT| StuID | Lname | Fname | Age | Sex | Major | Advisor | City_Code | -------|-------|--------|--------|-----|-----|-------|---------|-----------| | _S1 | P. | P. | | | | | | -------------------------------------- LIKES | Wholikes | WhoisLiked --------|-----------------|----------- |CNT.UNQ.ALL. _L1 | _S1 |CNT.UNQ.ALL. _L2 | G. _S2 ----------+---------------------------------- CONDITION | MAX.ALL. _L1 >= MAX.ALL. _L2 ----------+---------------------------------- ========= Query 17 ========= 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 20 ========= List the names of all students who like someone who likes someone who likes Linda Smith. ---------------------------------------------------------------------------- STUDENT| StuID | Lname | Fname | Age | Sex | Major | Advisor | City_Code | -------|-------|--------|--------|-----|-----|-------|---------|-----------| | _s1 | P. | P. | | | | | | | _s2 | | | | | | | | | _s3 | | | | | | | | | _s4 | Linda | Smith | | | | | | ---------------------------------------- LIKES | WhoLikes | WhoIsLiked | -----------|-------------|-------------| | _s1 | _s2 | | _s2 | _s3 | | _s3 | _s4 | ======== Query 21 ======== 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| ======== Query 25 ======== ----------------------------------------------------------- DORM | DormID | Dorm_name | Student_capacity | Gender | -------|---------|-----------|------------------|---------| | P. G._d | P. | | | ---------------------------------------- LIKES | WhoLikes | WhoIsLiked | -----------|-------------|-------------| NOT | | _s | -------------------------------------------------- LIVES_IN | StuID | DormID | Room_number | ---------|------------|---------|----------------| |CNT.ALL._s | _d | | -----------+---------------------------- Conditions | CNT.ALL._s=MAX.CNT.ALL._s -----------+---------------------------- ======== Query 27 ======== ------------------------------------------------------------------------------------- STUDENT | StuID | Lname | Fname | Age | Sex | Major | Advisor | City_Code | ----------|-------|-----------|-----------|-----|-----|-------|---------|-----------| | _s | P. _l | P. _f | | | | | | | | | | | | | | | | | | | | | | | | --------------------------------------- ENROLLED_IN | StuID | CID | Grade | ------------|--------|--------|-------| NOT | _s | | _g | | | | | -------------------------------------------- GRADECONVERSION | lettergrade | gradepoint | ----------------|-------------|------------| | _g | _gp | | B+ | _bp | -------------------------------------- PARTICIPATES_IN | StuID | ActID | ----------------|---------|----------| NOT | _s | | | | | ------------------+-------------------------| CONDITION | _gp >= _bp | ------------------+-------------------------| ======== Query 29 ======== ------------------------------------------------------------------------------------- STUDENT | StuID | Lname | Fname | Age | Sex | Major | Advisor | City_Code | ----------|-------|-----------|-----------|-----|-----|-------|---------|-----------| | _s | | | | F | | | | -------------------------------------------------------------------------------- COURSE | CID | CName |Credits| Instructor | Days | Hours | DNO | ---------|-------|--------------|-------|------------|-------|-------|---------| | _c | P. | | _i | | | | --------------------------------------- ENROLLED_IN | StuID | CID | Grade | ------------|--------|--------|-------| | _s | _c | | -------------------------------------------------------------------------- FACULTY | FacID | Lname | Fname | Rank | Sex | Phone | Room | Building | ---------|-------|--------|-------|------|-----|-------|------|----------| | _i | | | | F | | | | ======== Query 36 ======== ------------------------------------------------------------------------------------- STUDENT | StuID | Lname | Fname | Age | Sex | Major | Advisor | City_Code | ----------|-------|-----------|-----------|-----|-----|-------|---------|-----------| | _s1 | P. _ln1 | P. _fn1 |>=20 | | _d | | _c | | _s2 | P. _ln2 | P. _fn2 |>=20 | | | | _c | | | | | | | | | | ------------------------------------------------------------------ DEPARMENT | DNO |Division| DName | Room | Building | DPhone | ----------|-------|--------|-------|--------|-----------|--------| | _d | | _dn | | | | | _e | | _en | | | | ------------------------------------------------------------+ CONDITION | ------------------------------------------------------------| (_dn = Physics OR _dn = Computer Science) | (_en = Physics OR _en = Computer Science) | ------------------------------------------------------------| --------------------------- MINOR_IN | StuID | DNO | ---------|--------|-------| | _s2 | _e | -------------------------------------------------------------------------------- CITY | City_code | City_name | State | Country | Latitude | Longitude | -----|-------------|------------|--------|-----------|-----------|-------------| | _c | | MD | | | | ======== Query 39 ======== ------------------------------------------------------------------------------------- STUDENT | StuID | Lname | Fname | Age | Sex | Major | Advisor | City_Code | ----------|-------|-----------|-----------|-----|-----|-------|---------|-----------| | _s1 | _ln1 | _fn1 | | | | | | | _s2 | _ln2 | _fn2 | | | | | | ----------------------------------------------- LIVES_IN | StuID | DormID | Room_number | ---------|---------|---------|----------------| | _s1 | _d | | | _s2 | _d | | ----------------------------------------------------------- DORM | DormID | Dorm_name | Student_capacity | Gender | -------|---------|-----------|------------------|---------| | _d | _dn | | | ---------------------------------------- LOVES | WhoLoves | WhoIsLoved | -----------|-------------|-------------| | _s1 | _s2 | | _s2 | _s1 | ------------------------------------------------------ RESULT | Lname1 | Fname1 | Lname2| Fname2| Dorm | ------------|--------|--------|-------|-------|------| P. | _1n1 | _fn1 | _ln2 | _fn2 | _dn | | | | | | | ------------------------+ CONDITION | _s1 < _s2 | ------------------------+ (Please note that if you are roomates in this database you, by definition, live in the same dorm as well)