User talk:Aparnanayak

The following exercises are based on the tables EMP, and DEPT, the structure for which are given below:

EMP:

EMP_NO		NOT NULL		NUMBER (4) ENAME						VAR CHAR (25) DESIGN					CHAR (4) JOIN_DATE					DATE SAL						NUMBER (7, 2) PERKS						NUMBER (7, 2) DNO			NOT NULL		NUMBER (2)

DEPT:

DNO			NOT NULL		NUMBER (2) DNAME					VAR CHAR (25) BUDGET					NUMBER (15, 2) MANAGER					VAR CHAR (25)

Use the above tables to formulate SQL queries for the following :

1.Display each employee’s name and date of joining. 2.Display employees earning more than Rs.5,000. Label the column name “Employee”. 3.Display employees who are neither ASA nor SA. 4.Display all employee names and employee numbers in the alphabetical order of names. 5.Display all employee names containing the letter “S”, anywhere. 6.Display employee names and salaries for those having salaries less than their perks. 7.Display the employees hired in 1981. 8.Display the names and jobs of employees whose salary is greater than Rs.10,000/- in “Training” Department. 9.For each employee, calculate the number of months between today and the date the employee was hired. Order your result by the number of months an employee has been employed. Round the number of months to be closest whole number. 10.Display the employee name, total salary, and joining date for all employees whose salary is greater than twice their perks. Format the joining date to look like 3rd of December, 1982. 11.Display the hire dates in the EMP table in the following formats : a)Jan 31 90 b)01/31/90 12.Display the first 5 characters of the department name, in uppercase, followed by “managed by”, followed by the manager name. 13.Display the minimum and maximum salary. 14.Display the list of employees along with their department name and its manager. 15.Display the number of different employees listed for each department. 16.Insert yourself as a new employee into the EMP table. 17.Update your own employee data by giving yourself a Rs.1000 per month raise. 18.Delete yourself from EMP table. 19.Create a duplicate EMP table. Name it EMPTEST. 20.Add a new column named PHONE to the EMPTEST table, with a data type of number and length 4. 21.Update the SAL column in EMPTEST so that all employees in department 20 get a raise of Rs.500. 22.Increase the size of PHONE column to 6. 23.Drop the EMPTEST table. 24.Create a view named EMP_NO_MONEY with all columns from EMP table except the SAL & PERKS columns. Use this view to display all employee names and joining dates. 25.Create and index named I_EMP_EMPNO on the EMP table which does not allow duplicate employee numbers.