Introduction to Databases - Assignment_1

Introduction to Databases - Assignment_1

• 1.
  Introduction to databases MohdTousif Assignment – S1 1. Prepare the below tables in database in the given format (Employee) A) Employee Column Name Data Type Size Employee_Id Number 6 First_Name Varchar2 20 Last_Name Varchar2 25 Email_Id Varchar2 14 Phone_Number Varchar2 20 Hire_Date Date Job_Id Varchar2 10 Salary Number 8,2 Commission_Percent Number 2,2 Manager_Id Number 6 Department_Id Number 4 Insert into the above table below values Emplo yee_Id First_N ame Last_N ame Email_ Id Phone_ Numbe r Hire_D ate Job_Id Salary Commi ssion_P ercent Manag er_Id Depart ment_I d 100 Sailaja Balgeri sb 515.123 .1421 06/17/8 7 AD_PR ES 24000 0.9 90 101 Neelim a Atmuri na 515.124 .1431 09/21/8 9 AD_VP 17000 0.9 100 90 102 Nitin Agarw al nag 514.123 .1543 01/13/9 3 AD_VP 17000 0.9 100 90 103 Prasan th Rathor e pr 515.124 .1421 01/03/9 0 IT_PR OG 9000 0.6 102 60 104 Santos h Devun uri sd 514.152 .1278 21/05/9 1 IT_PR OG 6000 0.6 103 60 107 Ravi ra 512.123 .1234 07/02/9 9 IT_PR OG 4200 0.6 103 60
• 2.
  124 Vijay vi513.235 .1246 16/11/9 9 ST_M AN 5800 0.5 100 50 141 Naveen nav 512.124 .1234 17/10/9 5 ST_CL ERK 3500 0.5 124 50 142 Amrith a am 512.124 .2345 29/01/9 7 ST_CL ERK 3100 0.5 124 50 143 Srikant h G sg 512.123 .1245 15/03/9 8 ST_CL ERK 2600 0.5 124 50 144 Amol Singh as 09/07/9 8 ST_CL ERK 2500 0.5 124 50 B) Department Column Name Data Type Size Department_Id Number 4 Department_Name Varchar2 30 Manager_Id Number 6 Location_Id Number 4 Insert into the above table below values Department_Id Department_Name Manager_Id Location_Id 10 Administration 200 1700 20 Marketing 201 1800 50 Shipping 124 1500 60 IT 103 1400 80 Sales 149 2500 90 Executive 100 1700 110 Accounting 205 1700 190 Contracting 1700 C) Salgrade Column Name Data Type Size Grade_Level Varcahr2 3 Lowest_Sal Number 6 Highest_Sal Number 6 Insert into the above table below values Grade_Level Lowest_Sal Highest_Sal A 1000 2999 B 3000 5999 C 6000 9999
• 3.
  D 10000 14999 E15000 24999 F 25000 40000 2. Create an Emp table with the following criteria: Eno number(6) Ename varchar2(30) Sal number(8,2) 3. Add a column deptno with the datatype number of size 4 4. Change the name of the column Sal to Salary 5. Change the size of a column Ename to 40 character long 6. Create a Dept table with the following criteria: Dno number(6) Dname varchar2(40) loc_id number(4) 7. remove a column loc_id from dept table 8. change the name of the Dept to Departments 9. insert into emp table the below values Eno Ename Salary Deptno 1 ANIL 20000 1001 2 ATUL 25000 2002 3 ARUL 30000 1001 4 AMIT 27000 3003 5 ANUP 35000 2002 6 ANIR 30000 1001 10. Insert into departments table the below information Dno Dname 1001 MARKETING 2002 SALES 3003 EXECUTIVE 4004 IT 5005 PRODUCTION 11. Change the salary of all employees who belong to department number 1001 to 40000 12. Change the department number of ARUL to 2002 13. Remove the information of those employees who are earning 27000 14. Empty the table departments 15. Remove the table Departments from the database 16. Remove the table Emp from the database 17. Clear the recyclebin 18. Show the structure of the SALGRADE table. 19. Select all data from the DEPARTMENT table. 20. Show the structure of the EMPLOYEE table. 21. Write a query to display the last name,job code,hire date, and employee number for
• 4.
  each employee, withemployee numbers appearing first. Provide an alias name STARTDATE for the HIRE DATE column. 22. Write a query to display unique job codes from the EMPLOYEE table. 23. Write a query to display the last name concatenated with the job code, separated by a comma and space 24. Create a query to display the last name and salary of employees earning more than 12,000. 25. Write a query to display the last name and department number for employee number 176. 26. Write a query to display the last name and salary of employees whose salary is not in the range of 5,000 and 12,000. 27. Write a query to display the last name, job code and start date of employees hired between February 20,1998 and May 1,1998. Order the query in the ascending order by start date. 28. Write a query to display the last name and department number of all employees in department number 20 and 50 in the alphabetical order by last name. 29. Write a query to display the last name and salary of employees who earn between 5,000 and 12,000 and are in the department 20 or 50. Label the columns as Employee and Monthly Salary. 30. Write a query to display the last name and hire date of all employees who was hired in 1994. 31. Write a query to display the last name, salary and commission for all employees who earn commission. Sort data in descending order of salary and commission. 32. Write a query to display the last names of all employees where the third letter of the last name is an 'A'. 33. Write a query to display the last name of all employees who have an 'A' and 'E' in their last name. 34. Write a query to display the last name,job and salary whose job is sales representative or stock clerk and whose salary is not equal to 2500,3500,7000. 35. Write a query to display the current date 36. For each employee, display the employee number, last name and salary and the salary increased by 15% ,rename it as New Salary 37. For the above query add a column called Increase which should contain the difference between the old and new salaries. 38. Write a query that produces the following for each employee: 39. <employee last name> earns <salary> monthly but wants <3 times salary>. Label the column as Dream Salaries. 40. Create a query to display the last name and salary for all employees. Format the salary to be 15 character long, left-padded with $. label the column Salary. 41. Display each employee's last name,hire date, and salary review date, which is the first Monday after six months of service. Label the column REVIEW. Format the dates to appear in the format similar to “Monday, the Thirty-First of July, 2000”. 42. Display the last name, hire date, and day of the week on which the employee started. Label the column DAY. Order the results by the day of the week. 43. Create a query that display's the employee's last names and indicates the amounts of their annual salaries. Sort the data in the descending order of salary. Label the column EMPLOYEE_AND_THEIR_SALARIES.