EMPLOYEE (EmpNumber, Name, Email) PROJECT (ProjectName, Description, StartDate,

Question

EMPLOYEE (EmpNumber, Name, Email)

PROJECT          (ProjectName, Description, StartDate, EndDate)

ASSIGNMENT (EmpNumber, ProjectName, TotalHoursWorked)

1.         Code a SQL statement to create a view that shows the values of EMPLOYEE Names.

2.         Code a SQL statement to create a view that shows Project Name, Description and Start Date of Project for project named Blue.

3.         Code a SQL statement to create a view that computes the sum of TotalHoursWorked for each Project. Show the Project Name as well as the Hours worked.

4.         Code an SQL statement to show the start date for a project named Blue.

Now code an SQL statement that uses the view you created in step 2 to show the start date for the Blue project.

5.         Describe how views are used to provide an alias for tables. Why is this useful?

6.         Explain how views can be used to improve data security.

Explanation / Answer

1) Create view empName as (select Name from EMPLOYEE);

2) Create view prjct as (select ProjectName, Description, StartDate from PROJECT where ProjectName = 'Blue');

3) Create view hours as (Select ProjectName, sum(TotalHoursWorked) from ASSIGNMENT group by ProjectName);

4) select StartDate from prjct where ProjectName = 'Blue';

5) Views are lightweight objects as compared to complete tables. While referencing the tables, we need to remember the details associated with the tables. Also with views, the speed increases as we deal with just a small sub set of data.

6) Views are used to create small subsets out of complete table. Views let the user to see only a subset of data hiding the important details of a table.

Thanks

Nikhil Jain

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

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