RESORT (resortno, resortname, resorttype, resortaddr, resortcity , numsuite)

SUITE (suiteno, resortno, suiteprice)

RESERVATIOn (reservationno, resorttype, resortaddr, resortcity , numsuite)

VISITOR(visitno, firstname, lastname, visitoraddr)

i) Write the SQL to list full details of all the resorts on Los Angeles.

ii) Write the SQL to list full details of all resorts having number of suites more than 30.

iii) Write the SQL to list visitors in ascending order by firstname.

STUDENT (SSn, Name, Major, bdate)

COURSE (Courseno, Cname, dept)

ENROLL (SSn , Quarter , Courseno, grade)

BOOK_ADOPTION(Courseno, Quarter, book_isbn)

TEXT(book_isbn, book_title, Publisher, Author)

Write the following queries in relational algebra on the database schema:

i) List the number of courses taken by all students named John Smith in winter 2009(i.e Quarter = W09).

ii) Produce a list of text books (include courseno, book_isbn, book_title) for courses offered by the 'CS' department that have used more than books.

iii) List any department that has all its adopted books published by 'Person' publishing.

i) Nested Queries ii) Aggregate functions iii) Triggers iv) Views and their updatability v) Schema change statements vi) Group by and having clause.

EMP(Name, SSn, Salary, Supersn, dno)

DEPT (dnum, dname, mgrssn)

DEPT_LOC (dnum, dlocation)

PROJECT(Pname, Pnumber, Ploaction, dnum)

WORKS ON(Essn, dept_name, sex)

Write SQL queries for the following:

i) Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees.

ii) Retrieve the names of names of employees who make atleast 10000 more than the employee who is paid the least in the company.

iii) a view that has the employee name, supervisor name and employee salary for each employee who works in the Research' department.

iv) A view that has the project name, controlling department name, number of employees and total hours worked per week on the project for each project with more than one employee working on it.