SQL 3
SQL 3
SQL 3
1. Create the table PROGRAMMER with the given information using SQL CREATE TABLE commands:
Attribute Name EmpNo ProjId LastName FirstName HireDate Language TaskNo Privelege Description/Data type/Constraint Employees Unique ID. Max 5 characters should be numeric Project in which programmer participates. Max 3 characters should be numeric Surname of employee. Max 30 characters Required. Employees first name. Max 30 characters Date on which employee was hired. Date data type Programming Language used by programmer. Max 15 characters Number of the rask associated with the project. Numeric column, max 2 digits Type of privilege given to programmer. Max 25 characters
3.
Write SQL queries to: a. Saurav Gupta is assigned a different project with id NITTS and he would work with C++ now. Update this change in the PROGRAMMER table. b. Supriyo Chaudhury has resigned his job. Incoprate this in the table PROGRAMMER. c. The column TaskNo in the PROGRAMMER table is no longer needed. Delete the column.
4.
5. 50 6.
In DEPARTMENT table, increase the field width if DNAME from 20 to Insert the following data into the Department table DEPTNO 10 20 30 40 DNAME ACCOUNTS MARKETING SALES RESEARCH LOC NEWYORK CHICAGO ATLANTA OHIO
7.
8. In EMPLOYEE table add a new attribute MGR(Manager ID) consisting 4 Characters 9. Insert the following DATA into the Employee Table
10.
Drop the column Lo_sal from Grade Table Add column Low_sal in Grade table Insert the following data into the table
GRADE_NO 1 2 3 4 HI_SAL 2000 3500 6000 15000 LO_SAL 500 2100 3600 6100
Create table EMPLOYEE_BACK from employee table Increase the salary of JAMES from 3000 to 3500 Increase the salary of all MANAGER by 1000 Decrease the salary of DEPTNO 10 by 100 Add a new field COMM in EMPLOYEE table Initialize the value of COMM to zero in EMPLOYEE table.
Remove the employees who have joined before 6-Jun-2005 Remove employees whose salary is less than 3000 List all employees who are working in department 10. List all employees of department 10 and are MANAGER List all employees whose salary is between 3000 and 5000 List all employees who have joined after 10th July 2005 List all employees who are MANAGER or PRESIDENT
27. 28. 29. 30. 31. 32. 33. 34. 35. 36. 37. 38.
List all employees who are in deptno 10 or 20 and who are MANAGERS Update the commission of employees in deptno 10 to 500. List all employees whose commission is null. List the employees who are not a PRESIDENT or MANAGER List all employees whose name begin with J List all employees whose name consists of A List the employee sal, comm. and bonus (Bonus is sal+comm.) Display the salary of employees of MANAGER increased by 10%. The output should display salary and increased salary. Update the salary of MANAGER by 10%. Display the employees in the desscending order of names Display the employees in the ascending order of deptno, Job Display all the employee names with the first letter in capitals and all the other characters in lower case Display all the employee names in lower case Display the employee name and the position of letter A in each name Extract the last 3 characters in employee name and display them. Display the employee name and the length of the name Display the current system date and time
Display the employee name and the date when each employee completes 5 years in the company Display the last day of the month for the current system date Display the last day of the month for all the hiredates in EMPLOYEE table Display the employee name, hiredate and the total months of experience as on 8th June 2008 Display the employee name, sal and comm. for all employees. Employees having commission as null should be displayed as 99
47. 48.
Drop table employee EMPLOYEE_BACK Display the number of employees in department 10 Display the number of employees in each department ans deptno; : select deptno,count(deptno) from employee group by
Display the number of employees in each department job wise ans : select job,count(job) from employee group by job; Display the total number of employees in the table ans : select count(empno) from employee; Display the employee earning the highest salary ans: select ename from employee where sal=(select max(sal) from employee);
55.
Display the employee earning the highest commission ans: select ename from employee where comm=(select max(comm) from employee);
56.
Display the employee earning the lowest salary ans: select ename from employee where sal=(select min(sal) from employee);
57.
ans:select deptno,avg(sal) from employee group by deptno; 58. 59. Display distinct jobs in the table ans:select distinct(job) from employee; Display the deptno where the number of employees is greater than 3. ans:select count(deptno) from employee where deptno>3; 60. Display the total salary department wise ans:select sum(sal) from employee group by deptno; 61. Display the employee name, job and rank. If the job is PRESIDENT then rank is 1, If the job is MANAGER then rank is 2 , if the job is CLERK then the rank is 3. 62. Display the job and total salary for each job having number of employees greater than 5 63. There is a deduction of 5000 from every employees salary. Check for the balance salary and display the appropriate message. If the deduction causes value to be less than zero display Insufficient for Deduction, if greater than zero Can be Deducted 64. 65. 66. 67. 68. Create a table EMPLOYEE_DUP from EMPLOYEE. The new table should only have the structure and should not have any data values. List the employees who are in the same department as that of CLARK List the employees who drawing the same salary as that of BLAKE List the employees whose salary is greater than the average salary List the employees who are located in NEWYORK or CHICAGO
69.
List the employees whose salary is greater than the salary of BLAKE or CLARK. Increase the salary of employees by 10% , who are located in CHICAGO
70.
71. 72.
Delete the employees whose rowid is greater than the rowed of CLARK List the employees whose salary is greater than the average salary of his own department. List the employees who are not in the same department as that of BLAKE or CLARK Display the department number, name and location for the department for which there exists employees Create a view emp_vw consisting of ename, sal, job of employees in department 10 Create a view emp_vw1 consisting of employee names, sal and job of employees located in CHICAGO Create a view emp_dept consisting of employee names, job, sal , department name and location Create a sequence emp_seq Create a sequence emp_seq1 starting with 1 and increment it by 1 Implement the sequence while creating table.
73.
74.