Consider the following relational schema:

 

            Staff                (staffNo, name, dept, skillCode)

            Skill                 (skillCode, description, chargeOutRate)

            Project            (projectNo, startDate, endDate, budget, projectManagerStaffNo)

            Booking                       (staffNo, projectNo, dateWorkedOn, timeWorkedOn)

            

            where:                      Staff    contains staff details and staffNo is the key.

            Skill                          contains descriptions of skill codes (e.g. Programmer, Analyst, 

                                             Manager, etc.) and the charge out rate per hour for that skill; the 

                                             key is skillCode. 

            Project                     contains project details and projectNo is the key. 

            Booking                               contains details of the date and the number of hours that a member of

                                             staff worked on a project and the key is staffNo/projectNo.

Formulate the following queries using SQL (1 & 2)

1. For all projects that were active in July 2022, list the staff name, project number and the date and number of hours worked on the project, ordered by staff name, within staff name by the project number and within project number by date.

 

1. Crate a view of staff details giving the staff number, staff name, skill description, and department, but excluding the skill number and charge out rate.

 

The following tables form part of a database held in a Relational Database Management System. Use this schema to answer queries 3, 4, 5, & 6.

 

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

      Department     (deptNo, deptName, mgrEmpID)

      Project            (projNo, projName, deptNo)

      WorksOn         (empID, projNo, hoursWorked)

 

      where  Employee contains employee details and empID is the key.

                 Department     contains department details and deptNo is the key. mgrEmpID 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).

      and WorksOn contains details of the hours worked by employees on each project, and empID/projNo form the key.

 

1. Produce a complete list of all managers who are due to retire this year, in alphabetical order of surname.

 

                        

 

1. Find out how many employees are managed by 'James Adams'.

 

                        

 

1. Produce a report of the total hours worked by each employee, arranged in order of department number and within department, alphabetically by employee surname.

 

            

 

1. For each project on which more than two employees worked, list the project number, project name and the number of employees who work on that project.

The relational schema shown below is part of a hospital database. The primary keys are highlighted in bold.

      Patient (patientNo, patName, patAddr, DOB)

      Ward (wardNo, wardName, wardTpe, noOfBeds)

      Contains (patientNo, wardNo, admissionDate)

      Drug (drugNo, drugName, costPerUnit)

Prescribed (patientNo, drugNo, unitsPerDay, startDate, finishDate)

                                                                  

      Formulate the following SQL statements using the above schema (7, 8, & 9)

 

1. What is the total cost of Morphine supplied to a patient called 'John Smith' ?
2. For each ward that admitted more than 10 patients today, list the ward number, ward tpe and number of beds in each ward.
3. List the numbers and names of all patients and the drugNo and number of units of their medication. The list should also include the details of patients that are not prescribed medication.

A relational database contains details about journeys from Paisley to a variety of destinations and contains the following relations:

 

                  Operator (opCode, opName)

                  Journey (opCode, destinationCode, price)

                  Destination (destinationCode, destinationName, distance)

 

      Each operator is assigned a unique code (opCode) and the relation operator records the association between this code and the operator's name (opName). Each destination has a unique code (destinationCode) and the relation destination records the association between this code and the destination name (destinationName), and the distance of the destination from Paisley. The relation Journey records the price of an adult fare from Paisley to the given destination by as specified operator, several operators may operate over the same route.

 

      Formulate the following queries using SQL (10, 11, & 12)

1. List the names of all operators with at least one journey priced at under £5.

 

1. List the names of all operators and prices of journeys to 'Ayr'.

 

1. List the names of all destinations that do not have any operators.

The following tables form part of a database held in a Relational Database Management System:

 

      Employee        (empNo, eName, salary, position)

      Aircraft                       (aircraftNo, aName, aModel, flyingRange)

      Flight              (flightNo, from, to, flightDistance, departTime, arriveTime)

      Certified                      (empNo, aircraftNo)

 

      where  Employee contains details of all employees (pilots and non-pilots) and empNo is the key.

                 AirCraft contains details of aircraft and aircraftNo is the key. 

                  Flight contains details of the flights and flightNo is the key.

      and Certified contains details of the staff who are certified to fly an aircraft, and empNo/aircraftNo form the key.

 

Formulate the following queries in SQL (13, 14, 15, & 16)

1. List the employee numbers of employees who have the highest salary.

 

 

1. List the employee numbers of employees who have the second highest salary.

 

 

1. List the employee numbers of employees who are certified for exactly three aircraft.

 

1. Write code that will write Oracle sequences to produce automatic customer number and invoice number values. Start the customer numbers at 1000 and the invoice numbers at 5000. 

 

 

1. Modify the CUSTOMER table to included two new attributes: CUST_DOB and CUST_AGE. Customer 1000 was born on March 15, 1979, and customer 1001 was born on December 22, 1988.

 

 

 

 

 

 

        Consider the relational model for Bearcat Incorporated. 

EMPLOYEE (Emp_fname, Emp_minit, Emp_lname, Emp_nametag, Emp_emp_e#a, Emp_emp_e#n, Emp_address, Emp_salary, Emp_pl_name, Emp_gender, Emp_datehired, Emp_e#a, Emp_e#n) 

 

PLANT (Pl_p#, Pl_budget, Pl_name, Pl_emp_e#a, Pl_emp_e#n, Pl_mgrstdte) 

 

BUILDING (Bld_building, Bld_pl_p#)

PROJECT (Prj_name, Prj_location, Prj_p#, Prj_pl_p#) 

ASSIGNMENT (Asg_prj_p#, Asg_emp_e#a, Asg_emp_e#n, Asg_hrs) 

DEPENDENT (Dep_sex, Dep_brthdte, Dep_name, Dep_relhow, Dep_emp_e#a, Dep_emp_e#n) 

BCU_ACCOUNT (Bcu_dep_name, Bcu_dep_relhow, Bcu_dep_emp_e#a, Bcu_dep_emp_e#n, Bcu_acct_tpe, Bcu_acct#, Bcu-balance, Bk_emp_e#a, Bcu_emp_e#n) 

PARTICIPATION (Par_dep_name, Par_dep_relhow, Par_dep_emp_e#a, Par_dep_emp_e#n, Par_hob_name, Par_anncost, Par_hrsweek) 

HOBBY (Hob_name, Hob_Ioact, Hob_giact) 

In addition to the primary key constraints shown in the figure, these tables contain the following constraints (i.e., business rules).

PLANT Table

• No two plants can have the same name.

• Plant numbers are allowed to range between 10 and 20 inclusive.

EMPLOYEE Table

• Each employee must have a first name and a last name.

• Employee salaries can range between $35,000 and $90,000 inclusive.

• Valid genders are 'M' and 'F'.

• Each employee must work in an existing plant.

• The supervisor of an employee must be an existing employee.

• No two employees can have the same first name, middle initial, last name, and nametag combination.

BUILDING Table

• Each building must be part of an existing plant.

PROJECT Table

• Projects are located in the following cities (Bellaire, Blue Ash, Mason, Stafford, and Sugarland).

• Each project must be associated with an existing plant.

• Project numbers range from 1 to 40 inclusive.

ASSIGNMENT Table

• Each assignment must be associated with an existing employee and an existing project.

DEPENDENT Table

• The sex of a dependent can be ('M', 'F', 'm', or 'f').
• A dependent must be a dependent of an existing employee.
• A dependent can be related to an employee in the following ways:
  • A dependent can be the employee's spouse.
  • A dependent who is a mother or daughter must be a female.
  • A dependent who is a father or son must be a male.

BCU_ACCOUNT Table

• A bcu_account can belong to either an employee, a dependent, or (an employee and a dependent).

• Valid account tpes are 'C', 'S', or 'I'.

HOBBY Table

• Valid values for the indoor/outdoor attribute are 'I' or 'O'.

• Valid values for the group/individual attribute are 'G' or 'I'.

PARTICIPATION Table

• A participation must involve an existing hobby and an existing dependent. 

18, 19, 20. Write SQL code to create Employee, Dependent, and Hobby tables.