Scribd Logo
Upload

Welcome to Scribd!

  • 278 views

    DB Tut5

    Uploaded by

    wirdina
    This document contains examples of database queries on sample tables from relational databases. It provides queries to: 1) List details of hotels, rooms, bookings, and guests from a hotel database. 2) List book titles, borrower details, and book loans from a library database. 3) List employee, department, project, and work assignment details from a project management database. 4) Provide example queries on an instructor database table.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    DB Tut5

    Uploaded by

    wirdina
    278 views2 pages
    This document contains examples of database queries on sample tables from relational databases. It provides queries to: 1) List details of hotels, rooms, bookings, and guests from a hotel database. 2) List book titles, borrower details, and book loans from a library database. 3) List employee, department, project, and work assignment details from a project management database. 4) Provide example queries on an instructor database table.

    Original Description:

    Database tutorial

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document contains examples of database queries on sample tables from relational databases. It provides queries to: 1) List details of hotels, rooms, bookings, and guests from a hotel database. 2) List book titles, borrower details, and book loans from a library database. 3) List employee, department, project, and work assignment details from a project management database. 4) Provide example queries on an instructor database table.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    278 views2 pages

    DB Tut5

    Uploaded by

    wirdina
    This document contains examples of database queries on sample tables from relational databases. It provides queries to: 1) List details of hotels, rooms, bookings, and guests from a hotel database. 2) List book titles, borrower details, and book loans from a library database. 3) List employee, department, project, and work assignment details from a project management database. 4) Provide example queries on an instructor database table.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 2

    1/11/2019 Oct 2019

    BMIS 32113 – Database


    Tutorial Lecture 5 (Chapter 6)

    1. The following tables form part of a database held in a relational DBMS:

    Hotel (hotelNo, hotelName, city)


    Room (roomNo, hotelNo, type, price)
    Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)
    Guest (guestNo, guestName, guestAddress)

    where Hotel contains hotel details and hotelNo is the primary key;
    Room contains room details for each hotel and (roomNo, hotelNo) forms the primary key;
    Booking contains details of bookings and (hotelNo, guestNo, dateFrom) forms the primary key;
    Guest contains guest details and guestNo is the primary key.

    a. List full details of all hotels.


    b. List full details of all hotels in New York.
    c. List the names and addresses of all guests living in London, alphabetically ordered by name.
    d. List all double or family rooms with a price below £40.00 per night, in ascending order of price.
    e. List the bookings for which no dateTo has been specified.

    2. The following tables form part of a Library database held in an RDBMS:


    Book (ISBN, title, edition, year)
    BookCopy (copyNo, ISBN, available)
    Borrower (borrowerNo, borrowerName, borrowerAddress)
    BookLoan (copyNo, dateOut, dateDue, borrowerNo)

    where Book contains details of book titles in the library and the ISBN is the key.
    BookCopy contains details of the individual copies of books in the library and copyNo is the key. ISBN is a
    foreign key identifying the book title.
    Borrower contains details of library members who can borrow books and borrowerNo is the key.
    BookLoan contains details of the book copies that are borrowed by library members and
    copyNo/dateOut forms the key. borrowerNo is a foreign key identifying the borrower.

    a. List all book titles.


    b. List all borrower details.
    c. List all books titles published between 2010 and 2014.
    d. List all copies of book titles that are available for borrowing.
    e. List all book titles that contain the word ‘database’ and are available for loan.

    Submit your work after class.


    1/11/2019 Oct 2019

    3. The following tables form part of a Project database held in an RDBMS:

    Employee (empNo, fName, lName, address, DOB, sex, position, deptNo)


    Department (deptNo, deptName, mgrEmpNo)
    Project (projNo, projName, deptNo)
    WorksOn (empNo, projNo, dateWorked, hoursWorked)

    where Employee contains employee details and empNo is the key.


    Department contains department details and deptNo is the key. mgrEmpNo identifies the employee who
    is the manager of the department. There is only one manager for each department.
    Project contains details of the projects in each department and the key is projNo (no two departments
    can run the same project).
    WorksOn contains details of the hours worked by employees on each project, and empNo/
    projNo/dateWorked form the key.

    a. List all employees in alphabetical order of surname, and then first name.

    b. List all the details of employees who are female.

    c. List the names and addresses of all employees who are Managers.

    d. Produce a list of the names and addresses of all employees who work for the IT department.

    4.

    Figure 4.0

    a. Based on above Instructor relation, what will be the query for the result as shown in Figure 4.0?

    b. Find the names of all instructors in the Computer Science department who have salary greater than

    $70,000.

    c. Produce a list of instructor if 10% of salary rise is given to them.

    Submit your work after class.

    You might also like