Reference no: EM13934305
Consider the following relations:
Student(snum: integer, sname: string, major: string, level: string, age: integer) Class(name: string, meets at: time, room: string, fid: integer)
Enrolled(snum: integer, cname: string)
Faculty(fid: integer, fname: string, deptid: integer)
The meaning of these relations is straightforward; for example, Enrolled has one record per student-class pair such that the student is enrolled in the class.
Write the following queries in SQL. No duplicates should be printed in any of the answers.
1. Find the names of all Juniors (Level = JR) who are enrolled in a class taught by
I. Teach.
2. Find the age of the oldest student who is either a History major or is enrolled in a course taught by I. Teach.
3. Find the names of all classes that either meet in room R128 or have five or more students enrolled.
4. Find the names of all students who are enrolled in two classes that meet at the same time.
5. Find the names of faculty members who teach in every room in which some class is taught.
6. Find the names of faculty members for whom the combined enrollment of the courses that they teach is less than five.
7. Print the Level and the average age of students for that Level, for each Level. 40
8. Print the Level and the average age of students for that Level, for all Levels except JR.
9. Find the names of students who are enrolled in the maximum number of classes.
10. Find the names of students who are not enrolled in any class.
11. For each age value that appears in Students, find the level value that appears most often. For example, if there are more FR level students aged 18 than SR, JR, or SO students aged 18, you should print the pair (18, FR).
|
Meeting the customer requirements
: Meeting the customer requirements (Oakland 2003) is a reasonable definition of quality. b. Customer satisfaction means we can wrap up our projects more rapidly (Frame 2002, quoted in Reid 2013a) c. Not doing it right the first time at every stage ..
|
|
What is the role of an i/o psychologist in an organization
: How are I/O psychologists trained? What is the role of an I/O psychologist in an organization? How will the role of I/O psychologists change/evolve in the next several years?
|
|
Full assessment description-marking criteria
: Before starting this assessment activity, read the full assessment description, marking criteria, submission requirements and guidance provided on the course textbook. Assessment Guidance Then check the assessment discussions on the forum(s) for a..
|
|
Use excel to set up an amortization schedule
: A customer with a trade-in offers the store a $260 cash price for the cabinet. Is that a better deal (for the customer) than the store is offering? On Mary's 16th birthday, her parents invest on her behalf an amount of $5,000 into an account that ..
|
|
Find names of all students who are enrolled in two classes
: Find the names of all students who are enrolled in two classes that meet at the same time. Find the names of faculty members who teach in every room in which some class is taught.
|
|
Determine the force exerted by the deltoid muscle
: Determine the force exerted by the deltoid muscle on the upper arm at A and the x- and y-components of the force reaction at the shoulder joint O
|
|
Compare and contrast reversal treatment designs
: Compare and contrast reversal treatment designs. What advantages or disadvantages exist for these two designs? Taking into consideration the strengths, limitations, and appropriateness of each design, which, in your supported opinion, is superior ..
|
|
Discuss the various logistical issues
: Discuss the various logistical issues you would have to consider before commencing the import of products from the two new factories, taking into account the various procedures that need to be considered, the modes of transport you may need to use..
|
|
Find the aids of all aircraft that can be used on routes
: Find the aids of all aircraft that can be used on routes from Los Angeles to Chicago. Identify the routes that can be piloted by every pilot who makes more than $100,000.
|