Description

What To Do:
• Students in 601.415 and 601.615 should implement the following queries in SQL: 1, 2, 3, 4, 5, 7, 8, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 29, 30, 31, 32, 33, 34, 35, 36, 37, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49.
Also, Students in 601.415 and 601.615 should write the QBE equivalent of the queries: 1, 4, 5, 13, 22, 24, 26, 31, 34, 35, 42, 43, 49.
• Students in 601.315 should implement the following queries in SQL: 1, 2, 4, 6, 7, 8, 9, 10, 11, 12, 13, 15, 17, 18, 19, 22, 23, 24, 25, 26, 28, 31, 32, 34, 36, 38, 39, 40, 42, 44, 46, 47, 49.
Also, Students in 601.315 should write the QBE equivalent of the queries: 1, 4, 6, 13, 22, 24, 31, 34, 38, 42, 49.
All queries are based on the schema in jhu.sql on the class website.
Students in 601.415/615 have both more difficult queries and a greater number of queries than students in 601.315.
What To Hand In:
You should write all your SQL queries in a text editor, formatted clearly (preferably using select/from/where/group by commands in all caps, with attribute names in all lower case and relation names with the first letter capitalized).
Submit this program as a plain text file named yourusername.sql.txt using using gradescope as instructed.
You should also submit the output of your SQL code running on the actual database tuples in jhu.sql as instructed on piazza.
Finally, you are required to submit an electronic copy yourusername.qbe.txt of your QBE queries based on editing the plain-text schema we have provided for you in jhuschema.txt on the class website, again using gradescope as instructed.
Queries:
The relational table specifications for all queries used in this assignment are given in the file jhu.sql on the class website.
For the purposes of this exercise, the enrolled in relation only contains one semester’s worth of courses (student’s classes for the most recent semester) and the students’ grades for those courses. There are no semester/year attributes in the enrolled in relation. Questions concerning class enrollment should use the entire relation.
When a question asks “List the name” of a person, give their first name followed by their last (family) name.
If two or more entities are tied for the answer condition (e.g. for maximum, minimum, etc.), then list all matches. You should never assume that there is only one minimum, maximum, etc.
1. List the names of all pairs of students who are from the same city and like each otherbut don’t love each other.
2. List the name of the car-owning student in the database who owns the lowest MPG vehicle (of all the student-owned cars), along with the Manufacturer, Model, and MPG of that car.
3. List the name(s), age and major of student(s) who own every model of car manufactured by Nissan listed in the database. You cannot assume that Nissan only manufactures 3 models, as this can change.
4. List the names of students who live in a dorm and own more than one car.
5. List the names of students who live in a dorm and don’t own a car but own a pet.
6. List the names of students who live in a dorm and own exactly two cars and own two or more pets.
7. What is the minimum, maximum and average MPG of all models of Porsche listed inthe database?
8. What is the minimum, maximum and average age of students who live in the JHUdorms and do not own a car?
9. List the names and ages of all students who do not live in a dorm.
10. What is the average age of students who participate in more than 2 activities?
11. How many students participate in the most popular student activity, and what is thename of this activity?
12. List the names of all activities with no student participants which do have facultyparticipants.
14. List the names of students who share at least one activity with their advisor; exclude advisors who do not teach any classes. List the name of the advisor in addition to that of the student.
15. Find all pairs of students who are roommates and from different countries. List eachpair only once. (List their names).
16. List the dormitory whose residents have the highest gradepoint average.
17. You have been given two tables relating to location of and distance between cities: DIRECT DISTANCE and CITY. CITY contains the latitude and longitude of each city. DIRECT DISTANCE contains the actual distance (great-circle distance, or in other words, shortest distance along the surface of the earth) between two cities. However, DIRECT DISTANCE as it is given is an incomplete relation: not all city pairs are represented there. Your job is to augment DIRECT DISTANCE with approximated distances where needed. First note that, conveniently, the direct distance between Baltimore and every other city is listed in the DIRECT DISTANCE table. This will form the basis of the first distance approximation.
1. First, create a table called BALTIMORE DISTANCE. This table represents the “distance via Baltimore” between any two cities. For example, the Baltimore distance between Washington, DC and Philadelphia is: direct distance from
Washington to Baltimore + direct distance from Baltimore to Philadelphia In this case the direct distance and the Baltimore distance are comparable, at least. Now consider the Baltimore distance between San Diego and Los Angeles. In this case Baltimore distance is not a very good approximation of real distance. In any case, you are to create the table BALTIMORE DISTANCE having the same attributes as DIRECT DISTANCE, and write a query that populates it with the direct distance between every two cities. Be careful to make your query insert two tuples for every city pair: e.g., for Boston and Hong Kong, the tuples might look like (BOS,HKG,9000) or (HKG,BOS,9000).
2. Secondly, create a table called RECTANGULAR DISTANCE. Write a query to populate it with the approximate distance between each city pair using the formula:

q(d ∗ lat(city1) − d ∗ lat(city2))2 + (d ∗ long(city1) − d ∗ long(city2))2
where d = 70. In other words, this is the euclidian distance between the two points IF longitude and latitude are considered to be a rectangular coordinate system. As above, populate your table with two tuples for each city pair. (Note: 70 is the approximate number of miles per degree latitude)
3. Thirdly, create a table called ALL DISTANCES. This table should have tuples of the following type: (city1,city2,direct distance,baltimore distance,rectangular distance) Write a query to populate this table.
4. Finally, create a table called BEST DISTANCE with tuples of this type: (city1,city2,distance). Write a query that for each city pair takes the minimum of direct distance, baltimore distance and rectangular distance and inserts it into BEST DISTANCE. Clearly, direct distance will always be selected if it is present. It is guaranteed to be less than or equal to the approximations we are using.
For the remaining queries that require a distance relation between two cities, use the BEST DISTANCE relation you have just created.
18. List each city that is home to at least 2 students, and how many students live there.
19. Find all students living in dorms with fewer than 300 residents, whose homes arewithin 100 miles of someone else in the dorm they live in. List the name and city, state and country of origin for each of these students.
20. For every country, list the student(s) whose homes are in the city furthest from Baltimore.
21. List the name of the activity whose participants’ homes have the greatest averagedistance from Baltimore.
23. List the names and student ID numbers of all students who are enrolled in everycourse taught by Paul Smolensky.
25. List the names of courses that are enrolled with students who are not a member of any club, who have no allergies, and who likes people who is both in a club and has an allergy.
26. List the first and last name and dorm name of any student with at least one conductviolation, and the total number of his/her violations.
27. List the first and last name and dorm name the student with the most total conductviolations, along with that total. If there are multiple tied students tied, list all.
28. List the name, capacity and amenities of the dorm with the most alcohol conduct violations.
29. List the name and department of classes where the enrolled students have the mostconduct violations (of any type).
30. List the names of student organizations whose members collectively have over 3 conduct violations.
37. List the names of all people who worked as an Intern (Intern anywhere in his/her jobname) in at least two positions at the same time (i.e. where the dates overlap by at least one day).
38. List the names of all people who have studied abroad in the same country where they live now, along with the name of that country.
39. For each company, list the total number of JHU Computer Science majors who haveworked for that company.
41. List the name of the student with the longest total internship, as well as the nameof the company they were an intern for and the total number of days the student worked there. As before, an internship is a job with the word Intern anywhere in the job title.
42. List the student name and dorm name of all the students who live in a dorm that has a pet living there of the same type that the student is allergic to. Assume that if a student both has a pet and lives in a dorm, that pet lives with the student in the dorm. The student and pet don’t have to live in the same room.
43. List the names of every pair of students love each other and live with someone thathas a pet with the same name as the first name of the person they love. Include the pet’s name and list the person who matches the pet’s name 1st.
44. List the name and age of the student who owns the oldest dog in the database. Also include the name of the pet and the age of the pet next to the student’s name.
45. Assuming that dogs cannot live with cats, cats cannot live with parrots but parrots canlive with dogs, which pair of students who own pets cannot potentially be roommates. List the student names, dorm name and dorm room number of both students, with each pair listed only once.
46. For each dorm, list the name of the dorm and the total number of occupied roomsin the dorm (rooms with at least one listed occupant), and the total capacity of each dorm.
47. For each dorm, list the name of the dorm and the total number of rooms in the dormwith at least one pet living in that room.
48. For each dorm, list the name of the dorm, the total number of pets living in the dorm,and the percentage of occupied rooms in the dorm which have at least one pet living in that room. Do not use the dorm capacity, but the total number of rooms with a listed occupant.
49. Invent a complex, interesting question of your choice using the data in the jhu.sql schema and write both a SQL and QBE query to compute the answer to your question. Grading of this question will be based as much on your creativity as the correctness of your solution.