Reference no: EM13859343
This assignment uses a database with data about a university; the database resides in our Oracle servers. The schema of the database is provided below. Keys are in bold face and underlined, field types are omitted; assume that appropriate foreign key constraints have been defined in the full version of the schema (for example, a dname in the prof table does indeed references a department that exists in the dept table):
- student(sid, sname, sex, age, year, gpa)
- dept(dname, numphds)
- prof(pname, dname)
- course(cno, cname, dname)
- major(dname, sid)
- section(dname, cno, sectno, pname)
- enroll(sid, grade, dname, cno, sectno)
We ask that you write the following queries in SQL. Each question is worth 2 points.
Print the sid and name of students who have enrolled in at least one course offered by the 'Chemical Engineering' department and at least one course offered by the 'Industrial Engineering' department.
Print the name and age of the student(s) with the highest GPA among all students of the same age.
Top students per major. For each department with more than 8 students majoring in the department, print the following information about the student(s) with the highest GPA within the department: the student id, student name and GPA, and the department name the student is major in.
Difficult courses. For each department offering more than 2 courses, print the department name, course number, and course average grade of each course that has an average grade that is at least 5% less than the average grade obtained by students in all courses offered by the same department.
Top 2 unpopular courses. Print the department name, course number, and course enrollment of each course that has an enrollment equal to the lowest or second to the lowest enrollment of all courses offered by the same department. The enrollment of a course is the sum of the enrollment of all its sections. Note: for this query, you should consider all courses, including those with zero enrollment. [Hint: You will need to use outer joins for this query; see class notes on the subject and for more the Oracle's online manual on Outer Joins and Using Outer Joins: Examples.]
What to do
- Write one SQL query for each question above.
- Run your SQL queries on one of our Oracle servers (choose any one, they are all identical) using the web interface that we have set up at:
- https://w4111a.cs.columbia.edu/~af2728/hw2.php
- https://w4111e.cs.columbia.edu/~af2728/hw2.php
- https://w4111h.cs.columbia.edu/~af2728/hw2.php
- https://w4111i.cs.columbia.edu/~af2728/hw2.php
- https://w4111l.cs.columbia.edu/~af2728/hw2.php
- https://w4111d.cs.columbia.edu/~af2728/hw2.php
|
Why is accounting for a manufacturing business
: In your opinion, why is accounting for a manufacturing business more complicated than accounting for a merchandising business and provide three examples each of materials, labor costs, and factory overhead.
|
|
Draw isoquants for the firms production function
: Say that technology is relatively labor intensive if it uses more labor to produce the same amount of output than the other technology. Which technology A or B is labor intensive?
|
|
Calculate equilibrium products at 500k for the reaction
: Calculate equilibrium products at 500K for the reaction of 1 mole CO and 1 mole H2O according to the water-gas shift reaction (CO • H2O CO2 • H2) as a function of temperature four ways
|
|
Recognize the elements and correct use of a thesis statement
: Recognize the elements and correct use of a thesis statement
|
|
Database with data about a university
: This assignment uses a database with data about a university; the database resides in our Oracle servers. The schema of the database is provided below. Keys are in bold face and underlined, field types are omitted; assume that appropriate foreign ..
|
|
Create a new custom form that contains a subform
: 1) You are going to create a new custom form that contains a subform, the main from will contain the classes and the subform will contain the students , when the user navigate the classes from the main form, the subform will display the students f..
|
|
Identify the form of normalization
: For each of the examples below, indicate the normal form for each relation. If the relation is NOT in third normal form, decompose it into 3NF relations. Enter 1NF, 2NF or 3NF in the blank next to each item.
|
|
Understand first about the debate or disagreement
: understand first about the debate or disagreement
|
|
Write paper about causes and symptoms of alzheimer
: Write paper about causes and symptoms of Alzheimer's and Dementia
|