Databases | SQL | Oracle | MS Access Answers

Consider the following relation.
Instructor (ID , name, dept – name, salary) Department (dept – name , building ,budget) Classroom (building , roomno , capacity ) Teaches (ID , course – id , sec – id , semesters) Course (course – id , title , dept – name , credit)
Student (ID , name dept – name ,fot – ored ) Takes (ID , course – id , sec – id, semester , year, grade)
Write down queries expressed in SQL.

1)Find the name of all instructors who got the highest salary in their department.

2)Find the names of all the students whose total credits are greater than 100.

3)Find the course id and grades of all courses taken by any student.

4) Find all departments that have at least one instructor, and list – the name of the departments along with the number of instructor

Consider the following relation En roll (S-no, Course-no, Section)
Teach (Prot, Course-no, Section)
Advise (Port, S-no)
Pre-Req (Course-no, Precourse-no)
Grades (S-no, Course-no, grade, year)
Write down queries expressed in SQL

1) List all students taking course with smith or Jones

2) Find all students taking at least one course that their adviser teaches

3) List those professor who teach more than one section of the same course

4)Find all students whose course number is ‘101’

Consider the following schema and answer the following SQL queries
Employee (employee-name, street, city)
works (employee-name, company-name, salary)
company (company-name, city)
manages (employee-name, manger-name)

i)Find he names, street, city of all employees who works for ‘Infosys’ & earn more than $50,000)

ii)Find the name of all employees in the database who live in the same cities as the companies for which they work.

iii) Find the name of the all employees in the database who do not work for ‘Infosys’.

iv) Find the names of all employees in tha database who earn more than every employee of “TCS”.

Consider the following schema and answer the following SQL queries.
Employee ( person_name , street, city)
Works (person_name, company_name, salary) Company (company_name, city )
Manages (person_name , manager_name)

i) Find name and cities of residence of all employees who work for Wipro.

ii) Find the name of all employees in this database who live in the same city as the company for which they work.

iii) Find the name of all employees in this database who do not work for Wipro.

iv) Find the name street address and cities of all employees who work for Wipro and earn more than Rs. 10000

Use the methods find() and pretty() to implement the following queries.

(9) Display in a pretty format the names of drivers who performed no trips so far.
(10) Display in a pretty format names of drivers who started at least on trip in
Wollongong.

Need help for part 9 and 10

*Northwind Trader (SQL)* Using the OrderDetails table, list the ProductID, the total number of orders that product was included in, and the max discount amount on any order of that product. Provide appropriate column names for the number of orders and max discount amount. Only include products where the max discount amount for that product was greater than $1,000.

For the relations s and r in your study manual of our chapter 1(i.e., joining two relations), suppose we type a collection of relational algebra queries, and get the following results.

.A B C D E
A 2 a 10 B
B 2 a 10 b

Please explain how this happened? If you think this result could happen in practice, please write the corresponding relational algebra queries; otherwise, please explain why.

1 For the relations s and r in your study manual of our chapter 2 (i.e., joining two relations), suppose
we type a collection of relational algebra queries, and get the following results.
A B C D E

For the relations s and r in your study manual of our chapter 2 (i.e., joining two relations),
suppose we type a collection of relational algebra queries, and get the following results.
A B C D E
A 2 a 10 B
b 2 a 10 b
Please explain how this happened? If you think this result could happen in practice, please write the
corresponding relational algebra queries; otherwise, please explain why.