Part 1: Queries in the UNIV database
Find the number of people who have taken each course in the comp sci department. Show only the course name and count. Show only the 2 most commonly taken courses. Sort this list by course name.
What section of what course had the most students in fall of 2009?
GPA at this university is defined to be function of the graded earned and the credits for each course. For example suppose, the grades earned in two classes are A and B, and those two classes are worth 2 and 5 credits respectively. Further suppose that A has a value of 4.0 and B has a value of 3.0. Then the GPA calculation is (4.0*2 + 3.0*5)/(2+5)=(8+15)/7=3.29 Show the name, student id and GPA of the three students that have the lowest GPA. (There is a grade_points table that gives letter grade to numeric mapping)
Building from the previous query, in a single table, show the students with the three lowest, and 3 highest, GPAs. Order the resulting table by student name.
Find the IDs of students who did not take a class before 2005 (but who have taken a course after 2005). Sort this list by ID and show only the top 5.
Find the name of the instructor and salary of the highest paid instructor in all departments whose name starts with A
Find the name of the the instructor, and that person's salary and department, such that they have the highest salary in their department, but a lower salary than that of the highest paid person in any other department.
Extra Credit: In the table definitions is buried a hint about what University this DB is based upon. Find the hint and name the university.
Part 2: Queries in the Rocket database
The questions below assume you are using the rocket3 database. (The rocket database does not have the launch_nonull table.) Both of the questions about this database require written answers, not just SQL queries.
The following two queries both find all launches such that the launch apogee was higher than the launch apogee of any launch in 1957. The first is 15x slower than second!!! Explain.
select apogee, date from launch where apogee > all ( select apogee from launch where date_part('year', date)=1957); select apogee, date from launch where apogee > ( select max(apogee) from launch where date_part('year', date)=1957);
Consider a query in which you want to join the launch table to the launch_nonull table on the criteria launch_nonull.mission=launch.mission and launch_nonull.tag=launch.tag. Try every variation of join (inner join, natural join, cross join, right outer join, ....). Report the number of rows in the resulting table for each different join. Explain the differences between the sizes of the tables. (You may need to do some other queries to give good explanations.)
Part 3: Queries in the Hurricane database
Find the first observation of the 5 most recent storms in the database whcih were not given a name (that is, their name is 'UNNAMED')
Write a single query to recreate the observation table where longitude is negative in eastern hemisphere. That is eliminate the longitudehemi column and make any observation in which that column was 'E' have a value in the longitude column that is -1 times the original value
Use the answer to the previous question to find the name, date, longitude and latitude of the easternmost observed storm in the database.
Part 4
Discuss: null values in a database require ternary logic; which is a pain. So why should you you allow them (give at least two reasons)? What are the alternatives (describe at least one)? Is your alternative to nulls better or worse, why?
For Complete solution of this sample assignment mail us on contact@codersarts.com
Also, If you are looking for any kind of coding help in programming languages, Contact us for instant solution
Comments