Lab 5 : Exercise on SQL queries.

(a) Consider the relational table given below and answer the following SQL queries.   Think that the table has filled up by employee’s records.

 EMPLOYEE (ID_NO, NAME, DEPARTMENT_ID, SALARY)

·        List all the employees whose name starts with the letter ‘L’
·        Find the maximum salary given to employees in each department
·        Find the number of employees working in ‘accounts’ department
·        Find the second maximum salary from the table

·        Find the employee who is getting the minimum salary


(b) Consider a university database for the scheduling of classrooms for final exams. This database could be modeled as the single entity set exam, with attributes course-name, section-number, room-number, and time.Alternatively, one or more additional entity sets could be defined, along with relationship sets to replace some the attributes of the exam entity set, as
  •         course with attributes name, department and c-number
  •         section  with attributes s-number and enrollment, and department as a weak entity     set on course
  •         room with attributes r-number, capacity, and building
  1.         Show and E-R diagram illustrating the use of all three additional entity sets listed.
  2.         Explain what application characteristics would influence the decision to include or not to include each of the additional entity sets.

 

No comments:

Post a Comment

Popular Posts