Lab 6 : Exercise on Create, Insert, Select and Join Query with DBMS concepts

A binary relationship with 1:1 cardinality ratio, an employee can have one car and a car is used by one employee only. Do the following action as per your DBMS concepts

1. Create necessary tables with constraint and necessary field
2. Insert values to tables
3. Show values
4. Find the id and the type of car assigned to employee ‘123’
5. Find the name of the employee and the make(type) of the car, if she/he drives car 987.         Where 987 is the id of a particular car.


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.

 

Popular Posts