DATABASE AND SQL QUERIES

PROCEDURE OF SQL QUERY

To run the query on zoho, we have to upload the database or create the database online on the zoho then with the help of that we can run queries on it.

In this assignment we have to create two tables. One table will be of EMPLOYEE which will have the details of employee like the name of the employee, Designation, Join date, employee ID, Salary and Department.

In department we have taken Dept id, Dept name, Manager ID and the primary key here is Dept which is the foreign key in the EMPLOYEE database.

After importing this data to Zoho Database we have to run the New Query. Then while doing that we have to insert the columns and for new formulas we can use the tab with the name SQL functions there. We have to join and connect those columns and we have to define from which table they are.

e.g. While finding employee department we have to define that some of the columns which we have to put in the coding to make the query run.

For the first query of selecting employee location i.e. joining the data the we have to select the Emp ID, Last Name and first Name from EMPLOYEE table and Place from the DEPARTMENT.

In this query, there are queries for:

1. EMPLOYEE LOCATION :-

In this as explained earlier, we have to take Emp ID, last name, first name from Employee Table and Location from DEPARTMENT. This Query gives result of all the names of the employees with their Location of work.

2. CONDITION OF SALARY GREATER THAN 5,00,000:-

In this query we have to take all the values from the EMPLOYEE table and then we have to put the query as:

Select last name, first name, salary, job description from EMPLOYEE where salary > 500,000

From this query we can see that result is that we will get all our employees who are paid salary more than 5, 00, 000.00

3. STANDARD DEVIATION OF SALARY

For this query we have to select the function from the SQL function which has Std deviation in it. We have to select that SQL function and have to put salary in the bracket and after running that query we get standard deviation of salary.

4. SENIOR MANAGER AND MANAGING DIRECTOR HAVING SALARY MORE THAN 600000

In this we take the query of taking only senior manager, managing director and then in those the query will give result of those who are having salary more than 600,000.

We have to put the following query to do this:

select “EMP _id”, “LastName”, “FirstName”, “Salary”,”Dept” from “EMPLOYEE” where (“JobDescription” = ‘SR MANAGER’ or “JobDescription” = ‘JOINT M D’ ) and “Salary” >’600000′.

In this way we can prepare the database and run the queries.

the link for my queries from zoho is:-

http://db.zoho.com/ZDBDataSheetView.cc?DBID=22235000000003027