Oracle SQL Excercises

Department Employee Address Tables

Select employee numbers, employee names from employee table.
Select employee numbers grater than 1010.
Select employee number, employee name who draws more than 2000 as the Salary.
Select employee number, sal, comm for department 20.
Select employee names who were getting salary greater than 1500.
Select all the employees whose job = 'CLERK'.
Select all the employees whose job = 'CLERK' and salary > 2000.
Select employee number, name who works in deptno 30 and name starts with 'K
Select all the columns from employee table.
Select employee number, name, sal, comm who works in deptno 10, salary greater than 2000 and comm less than 250.

Select maximum, minimum salary in employee table.
Select minimum salary,commission in employee table for deptno 20.
Select employee number, employee name, department number whose sum of salary and commission greater than 2500.
Select employee number, employee name, employee age from employee table.
Selectemployee number , employee name born in the month of july.
Select employee name ,DOB in the format of "2nd of july 1997" for deptno 10 and 20.
Display employee names in lower case whose salary is greater than 2000 and less than 2800.
Select the number of employees who has no commission .
Select employee name whose job is Manager or Clerk and Salary greater than 1000 and commission greater than 100 and deptno is either 10 or 20.
Select employee names in an alphabetical order.

Display current date and 78 days after.
Display the current date in the following fashion. Today's date is : Jan 4 1998.
Display Employee name and Job information from employee in the following fashion. SMITH Works as CLERK.
Display all the information from department table where second character of city name is 'A'.
Display rowid, employee number from employee table.
Display rowid, row number, deptno from department table.
Display the last date in Feb-97
Display logged on user name in the following fashion. Current User Name is : SCOTT
Display 'YOU ARE GOOD IN SQL' text as 'You Are Good In Sql' in a Select statement.
Display current date and 78 days before.

Display employee number, employee name, department name, location from employee and department tables.
Display employee number, employee name, salary + comm as TOTAL, location from employee and department tables.
Display employee name, location from employee and department tables.
Diplay employee name, department name, job from employee and department tables who job is CLERK.
Display employee name, deptno, dname from employee and department table where location is DALLAS.
Display employee number, name, address and city from employee, employee address tables.
Display employee nmae, address from employee and employee address tables who lives in zip code 75039
Display employee name, department name, location who were getting comm as null.
Display first 3 characters of employee name, job, sal, comm, location from employee and department tables who works in department 20 or 30, salary should be more than 1000 comm as not null.
Display location, department name, length of location, length of department name for employee numbers 1001, 1008, 1009.

Display deptno, number of employee works in each department.
Display job, number of employees works in each category.
Display job and averge salary paid for each jobs in the company.
Display location and sum of the salary spending for employees on that location respectively.
Select minimum salary, maximum salary in each department.
Select department number, dname, average salary for each department.
Select job, number of employees working in that job, maximum and minimum salary for each job.
Select department number, name where atleast 3 employees works.
Select department name, location where the average salary in each department more than 1600.
Select department name, job and sum of salary by each job with in the department.

Display Current date
Display Current Time.
Display current user name.
Display Current date in July 3 1997 format
Display Current date and the date after 78 days.
Display Current date, day, 70 days after current date and its day also.
Display the months between ‘JAN-10-65’ and ‘JUN-23-98’ take the input in specified format.
Display the current date as Today’s date is : current date from system.
Display Current date, time and user name.
Display the day like Sunday or what for this date ‘July 4 1998’

Create a user named JOHN.
Create a Role named ‘DEPT_MANAGERS’
Grant Select, Update permissions on Employee table to DEPT_MANGAGERS role.
Create a Role named ‘CHIEF_MANAGERS’.
Grant Select, Update, Delete, Insert permissions on Employee to CHIEF_MANAGERS.
Grant DEPT_MANAGERS role to SCOTT.
Grant CHIEF_MANAGERS role to JHON.
Revoke update on permission on Salary column of Employee table from DEPT_MANAGERS.
Revoke update on employee from DEPT_MANAGERS
Assign DEPT_MANAGERS role to everyone in the database.