Data Mining Lab : SQLQUERIES

Display the Dept information from department table
Display the details of all employees
Display the name and job for all employees
Display name and salary for all employees
Display employee number and total salary fro each employee
Display employee name and annual salary for all employees
Display names of all employees who are working in department number 10
Display names of all employees working as clerks and drawing a salary more than 3000
Display the employee number and names for all employees who earn commission
Display the names of employees who do not earn any commission
Display the names of employees who are working as clerk, salesman or analyst and drawing salary more than 3000
Display the names of employees who are working in the company for past 5 years
Display the list of employees who have joined the company before 30^th june 90 or after 31^st dec 90
Display current date
Display the list of users in your database
Display the names of all users from the current user
Display the name of the current user
Display the names of employees working in department number 10 or 20 or 40 or employees working as clerk, salesman or analyst
Display the names of employees whose name starts with alphabet S
Display the names fro employees whose name ends with alphabet S
Display the names of employees whose names have second alphabet A in their names
Display the names of the employees whose name exactly five characters in length
Display the names of employees who are not working as managers
Display the names of employees who are not working as salesman,clerkor analyst
Display all rows form emp table ,the system should wait after every screen full of information
Display total number of employees working in the company
Display total salary being paid to all employees
Display maximum salary from emp table
Display minimum salary from emp table
Display average salary from emp table
Display maximum salary being paid to clerk
Display maximum salary being paid in dept 20
Display maximum salary being paid to any salesman
Display the average salary drawn by managers
Display the total salary drawn by the analysts working in dept no 30
Display the names of employees in order of salary i.e, the name of the employee earning lowest salary should appear first
Display the names of the employees in descending order of salary
Display the details from emp table in order of emp name
Display empno,ename,deptno and sal sort the output first based on name and with in name by deptno and with in dept no by sal
Display the name of the employee along with their annual salary (sal*12). The name of the employee earning highest salary should appear first
Display name,sal for each employee.The output should be in the order of total sal, hra 15% of sal, da 10% of sal, pf 5% of sal ,total salary will be(sal+hra+da)-pf.
Display department numbers and total number of employees in each department.
Display the various jobs and total number of employees with each job group;
Display department numbers and total salary for each department
Display department numbers and maximum salary for each department.
Display the various jobs and total salary for each job
Display each job along with minimum sal being paid in each job group
Display the department numbers with more than 3 employees in each department
Display the various jobs along with total salary is greater than 4000.
Display the various jobs along with total number of employees in each job. The output should contain only those jobs more than 3 employees
Display the name of the employee who earn the highest salary.
Display the employee number and name of the employee working as clerk and earns highest salary among clerks
Display the names of the salesman who earns a salary more than highest salary of any clerk
Display the names of the clerks who earns salary more than that of james and lesser than that of scott
Display employee names who are working in Chicago
Display the job groups having total salary greater than maximum salary for managers
Display the names of the employees from dept no 10 with salary greater than that of any employee working in other departments
Display the names of the employees from dept no 10 with salary greater than that of employees working in other departments
Display the names of the employees in uppercase
Display the names of the employees in lowercase
Display the names of the employees in propercase
Find out the length of your name using appropriate function
Display length of all employee names
Display the name of the employee concatenate with eno
Use appropriate function to extract 3 characters starting from 2 characters from following string ‘Oracle’ i.e., output should be ‘rac’
Find the first occurrence of character ‘a’ from the following string ‘computer maintenance corporation’
Replace every occurrence of alphabet ‘a’ with ‘b’ in the string allens
Display the information from emp table where ever job ‘manager’ is found it should be displayed as Boss
display your age in days
Display your age in months
Display current date as 15^th august Fridat nineteen fourty seven
Display the following output for each row from emp table as ‘scott has joined the company on Wednesday 13^th August Ninteen Ninty’
Find the date of nearest Saturday after current day
display current time
Display the date three months before the current date
Display the common jobs from department number 10 and 20
Display the jobs found in department number 10 and 20 and eliminate duplicates
Display the the jobs which are unique to department number 10
Display those emoloyees whose name contains not less tan 4 characters
Display those departments whose name starts with ‘S’ while location name ends with ‘O’
Display those employees whose manager name is jonea
Display the names of the employees whose salary is more than 3000 after giving increment
Display all employees with their dept name
Display ename who are working in sales dept
Display ename,dname,salary and commission for those sal in between 2000 and 5000 while location in Chicago
DISPLAY THOSE EMPLOYESS WHOSE SALARY GREATER THAN HIS

MANAGER SALARY

DISPLAY THOSE EMPLOYEES WHO ARE WORKING IN THE SAME DEPT
WHERE HIS MANAGER IS WORKING.

DISPLAY THOSE EMPLOYEES WHO ARE NOT WORKING UNDER ANY
MANAGER.

DISPLAY GRADE AND EMPLOYEE NAMES FOR THE DEPT NO 10 OR 30
BUT GRADE IS NOT 4 AND JOINED THE COMPANY BEFORE 31-DEC-82

UPDATE SALARY OF EACH EMPLOYEE BY 10% INCREMENTS THAT ARE NOT ELIGIBLE FOR COMMISSION.

DISPLAY THOSE EMPLOYEES WHO JOINED THE COMPANY BEFORE 31-DEC-82 WHILE THERE DEPT LOCATION IS NEWYORK OR CHICAGO.

DISPLAY ENAME,JOB DEPTNAME,LOCATION FOR ALL WHO ARE WORKING AS MANAGER.

. DISPLAY THOSE EMPLOYEES WHOSE MANAGER NAMES IS JONES

AND ALSO DISPLAY THERE MANAGER NAME

DISPLAY NAME AND SALARY OF FORD OF HIS SAL IS EQUAL TO

HIGH SAL OF HIS GRADE.

DISPLAY ENAME, JOB DEPTNAME HIS MANAGERS NAME HIS GRADE

AND MAKE OUT OF AN UNDER DEPARTMENT WISE

LIST OUT ALL THE ENAMES, JOB, SALARY ,GRADE AND DEPARTMENT NAME FOR EVERY ON IN THE COMPANY EXCEPT CLERK SORT ON SALARY DISPLAY THE HIGHEST SALARY

DISPLAY ENAME JOB HIS MANAGER DISPLAY ALSO EMPLOYEES WHO ARE WITHOUT MANAGERS

FIND OUT TOP 5 EARNS OF THE COMPANY.

DISPLAY THE NAMES OF EMPLOYEES WHO ARE GETTING

HIGHEST SALARY.

. DISPLAY THOSE EMPLOYESS WHOSE SALARY IS EQUAL TO

AVERAGE OF MAXIMUM AND MINIMUM.

. DISPLAY COUNT OF EMPLOYEES IN EACH DEPARTMENT WHERE COUNT GREATER THAN 3.

DISPLAY DNAME WHERE AT LEAST 3 ARE WORKING AND DISPLAY ONLY DNAME.

DISPLAY NAME OF THOSE MANAGER WHOSE SALARY IS MORE THAN AVERAGE SALARY OF HIS EMPLOYEES

.DISPLAY NAME OF THOSE MANAGERS WHOSE SALARY IS MORE THAN AVERAGE SALARY OF HIS COMPANY

.DISPLAY ENAME ,SAL,COMM AND NET PAY FOR THOSE EMPLOYEES WHOSE NET PAY IS GREATER THAN OR EQUAL TO ANY OTHER EMPLOYEE SALARY OF THE COMPANY.

FINDOUT THE LAST 5 EARNS OF THE COMPANY

FIND OUT THE NO.OF EMPLOYEES WHO’S SALARY IS GREATER THAN THEIR MANAGERS SALARY

DISPLAY THOSE MANAGERS WHO ARE NOT WORKING UNDER

PRESIDENT BUT THEY ARE WORKING UNDER ANY OTHER MANAGER

DELETE THOSE DEPARTMRNTS WHERE NO EMPLOYEE IS WORKING

DELETE THOSE RECORDS FROM EMP TABLE WHOSE DEPARTMENT NO NOT AVAILABLE IN DEPT TABLE.

DISPLAY EMP NAME.SAL.COMM AND WHOSE NET PAY IS GREATER THAN ANY OTHER IN THE COMPANY

. DISPLAY THOSE EMPLOYEES WHOSE SALARY IS ODD VALUE

DISPLAY THOSE EMPLOYEES WHOSE NAME CONTAINS ‘A’ WITHOUT USING LIKE OPERATOR

. DISPLAY THOSE EMPLOYEES WHOSE FIRST 2 CHARACTERS FROM HIREDATE LAST 3 CHARACTERS OS SALARU

OSa � M �v� �� ‘A’ WITHOUT USING LIKE OPERATOR

. DISPLAY THOSE EMPLOYEES WHOSE FIRST 2 CHARACTERS FROM HIREDATE LAST 3 CHARACTERS OS SALARU

om:0i 3 r i �v� �� .0001pt;text-align: justify;line-height:normal;mso-layout-grid-align:none;text-autospace:none'>8.List THE Name of dept where highest no.of emps are working.

A) select dname from dept where deptno in

(select deptno from emp group by deptno

having count(*) in

(select max(count(*)) from emp group by deptno) );

9.List the emps who joined in the company on the same date.

A) select * from emp e where hiredate in

(select hiredate from emp where e.empno <> empno);

10.List the Managers name who is having max no.of emps working under him.

)select m.ename,count(*) from emp w,emp m

where w.mgr = m.empno

group by m.ename

having count(*) = (select max(count(*)) from emp group by mgr);

(OR)

B) select * from emp where empno = (select mgr from emp group by mgr having count(*) = (select max(count(*)) from emp group by mgr)) ;

11.Print the details of all the emps who are sub-ordinates to Blake.

A) select * from emp where mgr in (select empno from emp where ename = 'BLAKE');

List the emps who are working as Managers using co-related sub-query.

A) select * from emp where empno in (select mgr from emp);

inText� d a � � � � nt-family:"Courier New"'> 10 madhu

20 ravi

SQL> rollback to p;

Rollback complete.

SQL> select * from student;

ROLLNO NAME

---------- --------------------

10 askhay

20 ravi