database

Consider the following two tables:

Table – EmployeeInformations Table – EmployeeSalary

1. Write a SQL query to find the names of all employees having salary greater than or equal to 5000 and less than or equal 10000?

select name from EmployeeInformations i

inner join employeesalary s

on i.EmpID=s.EmpId

where salary>=5000 and salary<=10000

2. Write a SQL query to find all employees from the " EmployeeInformations " table, sorted by the " ManagerId " column:

select * from EmployeeInformations order by ManagerId

3. Write a SQL query to find the average salary of all employees

select avg(salary) from employeesalary

4. Write a SQL query to find all employee records from EmployeeInformations table who have a salary record in EmployeeSalary table.

SELECT i.Empid, Name, ManagerId, DateOfJoining

FROM EmployeeInformations i

inner JOIN EmployeeSalary s ON i.EmpId=s.EmpId

What do you mean by A view in SQL language? Give an example?

In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

Syntax

CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;

Example

CREATE VIEW [Brazil Customers] AS SELECT CustomerName, ContactName FROM Customers WHERE Country = "Brazil";

Question Three

Learning Outcome(s):

LO 6 create a database application to store and retrieve data.

2 Marks

Explain the role of SQL authorization mechanisms that allow to differentiate among the users of the database as far as the type of access they are permitted on various data values in the database?

Authorization is the process where the database manager gets information about the authenticated user. Part of that information is determining which database operations the user can perform and which data objects a user can access.

Authorization Specification in SQL

The grant statement is used to confer authorization

Privileges in SQL

· select: allows read access to relation,or the ability to query using the view

· insert: the ability to insert tuples

· update: the ability to update using the SQL update statement

· delete: the ability to delete tuples.

· all privileges: used as a short form for all the allowable privileges

Revoking Authorization in SQL

The revoke statement is used to revoke authorization