Write and run following SQL queries on the database. All five tables (employee,

Question

Write and run following SQL queries on the database. All five tables (employee, FTemployee, PTemployee, healthplan, and enrollment)

1). Insert a new full time employee record into the database

2). List the names and types of all available health plans to part time employee

3). List the total number of full time employee in the company

4). List the names of the employee who are enrolled in a dental plan. You pick up a plan based on the records in your database table.

5). List the names of the employee who are enrolled in two or more health plan.

6). List the names and types of the health plan(s) most popular for full time employee.

7). List the owner of the employee table (hint: query DBA_TABLES)

8). Create a trigger that will display warning message, “please consult with DBA first before delete a database table”, when user attempting to delete a database table.

Explanation / Answer

Since you have now mentioned the column names of the table, I will be providing the solution which is generall later you can replace that from your table column names.

2.)

SELECT name, type FROM healthplan INNER JOIN PTemployee ON PTempolyee.id= healthplan.Employeeid

Inner join is done to fetch both name and type.

3.)

SELECT count(*) as Total number FROM FTemployee.

This query will results in the number of full time employees in the organization.

4.)

SELECT name FROM employee INNER JOIN enrollment ON employee.id= enrollment.employeeid WHERE enrolled= 'dental plan'

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.