All experiment

Experiment No. 2

Q-1> Display all tables of oracle ?
Q-2> Display a column in table dept ?
Q-3> List all dept names and location ?
Q-4> Display diff jobs available for an emp ?
Q-5> Compile the entire details of employee name “smith” ?
Q-6> Give list of emp name & their job spec who are working in dept no 20 ?
Q-7> Compile the details of emp working in dept no 3 ?
Q-8> Obtain list of all empno and names who are manager ?
Q-9> Display names of all emp whose comm is greater then salary ?
Q-10> Find list of emp whose sal is greater then 2000 along with their job spec ?
Q-11> Give list of all emp along with their monthly and annual income ?
Q-12> List all emp names and their salaries who are working in dept no 10 ?
Q-13> Give details of all dept and their no’s while changing the column name as department and department no ?
Q-14> List all managers with their respective name manager and deptno ?
Q-15> Display list of emp whose job is analyst ?












Experiment No. 3

Q-1> Create a table client master with the following fields client_no, name, address1, address2, city, state, pin_code, remarks, balance due and add the constraints?
• Create a primary key constraint on the column client_no.?
• create the following check constraints
Data values being inserted into the column client_no must start with ‘c’.
 Data values being inserted into the column name should be allowed in upper case only?
Q-2> Insert five rows in the table client master?
Q-3> Show all rows of client master?
Q-4> Add a new column in your table : AGE ?
Q-5> Update the table client master ?
Q-6> Delete a row from client master where age is greater then 60?
Q.7> Create table supplymaster (supplierno,suplliername,address1,address2,city,state,
pincode,remarks,baldue) by using clientmaster.
Q.8> Insert values in table supplymaster by using clientmaster.
Q-9> Drop table client master?
















Experiment No. 4

Q-1> Find list of emp whose empno is greater then manager no ?
Q-2> Find all manager not in dept no 10 ?
Q-3> Display everyone in dept no 20 who is neither a clerk, salesman nor analyst ?
Q-4> List all emp names, their jobs, salary in ascending order by theire jobs ?
Q-5> List all empname who are ordered by the jobs within each job there should be Descending order by empno ?
Q-6> Retrieve details of emp whose sal is between 1000 and 2000 both inclusive?
Q-7> Retrieve all details of employees whose name is either smith, blake, allen, scott, clark and King ?
Q-8> To list all employees whose salary is greater than or equal to 2000.
Q-9> To list all the employees hired during the year 1982
Q-10> To get all employees from dept 10 and 20.
Q-11> To list all employees whose name begins with ‘J’.
Q-12> To list employees whose name begins with ‘J’ and has ‘N’ as the third character?
Q-13> To list all employees not entitled for commission.
Q-14> To list all employees who receive commission.
Q-15> To list employees working in dept no 10 or 20.
Q-16> To list employees hired after 01-jan-81 and working in dept no. 10
Q-17> To list employees working in dept other than 10.








Experiment No. 5


Q-1> To find the total number of employees.
Q-2> To find the total number of clerk hired after 13-jan-81.
Q-3> To find the sum paid as salary to all employees every month.
Q-4> To find the yearly compensation paid to all sales men.
Q-5> To find the average salary of all the employees.
Q-6> To find the average yearly compensation paid to sales men.
Q-7> To find the minimum salary paid to any employee
Q-8> To list the employee who heads the list alphabetically.
Q-9> To find the maximum salary paid to any employee.
Q-10> Convert the hiredate format of employee in dd/ mm/ yyyy format ?
Q-11> Find the date after 4 month as per today ?
Q-12> Find out the last day of the current month ?
Q-13> Display the last working day of all employees on their joining month ?

Experiment No. 6

Q-1> Find the ASCII destination of alphabet ‘A’?
Q-2> Find the remainder of any number?
Q-3> Show all employees which have salary greater than 1500 and above it all are displayed as 1500?
Q-4> List all the employee having salaries below 2000 are displayed and above it all are Displayed as 2000?
Q-5> Round any particular no to two decimal places?
Q-6> Truncate any particular no to two decimal places?
Q-7> Concatenate two column names department name and location to form one column and Name it as dept?
Q-8> Translate the value of department no into ten, twenty, thirty?
Q-9> Determine which department have more then two people holding a particular job?
Q-10> Find all department that have at least two clerk?
Q-11> Divide all employee into group dept and job and also specify deptno?
Q-12> List annual salary from all job consisting of more then one employee?
Q-13> List all dept with atleast one analyst?
Q-14> List the job that have average annual salary greater than all manager?
Q-15> Find the no of employees and job group by deptno?
Q-16> Find the location number of character ‘A’ in employee table?


Experiment No. 7

Q-1> Retrieve emp name and job who have the same job as that of allen?
Q-2> List all emp name and their job of those depts. That are located at Chicago?
Q-3> List all emp name and sal whose sal is greater then that of all managers?
Q-4>display all emp names and sal where sal is less then that of any manager of any dept?
Q-5> List all emp name, dept names and location whose deptno is common in both dept table and emp table?
Q-6> List all jobs in dept 10 and 20 or in both?
Q-7> List all jobs which are common in dept 10 and 20 ?
Q-8> List all jobs which are present in dept 10 but not in dep 20?





Experiment No. 8


Q-1> Display emp name as job and working form hiredate?
In the following format:
SMITH is CLERK and working from17-DEC-80
Q-2> To get all the employees whose salary is greater than the average salary of the company.
Q-3> To select all the employees who do the same job as that of Scott.
Q-4> To see all details of depts. Who have employees working in it.
Q-5> To find names and salary details of all employees whose salary is equal to the salary given to an employee working in dept no. 30
Q-6> To select the employees whose salary is greater than the salary of all employees working in dept no. 30
Q-7> To list all details of dept which has at least one employ assigned to it.


Experiment No. 9

Q-1> Create unique index on job on the emp table.
Q-2> Remove the index created for the emp table.
Q-3> Create view on emp displaying deptno , dept name , sal , job.
Q-4> Drop the above view.
Q-5> Create view on emp displaying ename , job ,sal in dept 10.
Q-6> Drop the above view.
Q-7> Create view on emp to display sum of salary grouped according to deptno?
Q-8> Drop the above view?
Q-9> Create view on appropriate tables to display ename , job , sal , dept name?
Q-10> Drop the above view?
Experiment No. 10


Q-1> To find out average minimum and maximum salary of each dept.
Q-2> To find out average maximum and minimum salary of departments where average salary is greater than 2000.
Q-3> To list the minimum salary to various categories of employees.
Q-4> To find the minimum salaries of various categories of employees in various depts.
Q-5> To find the minimum salaries of various categories of employees, department wise such that minimum salary is greater than 1500.
Q-6> To find the minimum salary of managers in various depts.
Q-7> To find the minimum salary of managers in various depts. Having salary greater than 2500.
Q-8> To list all employees in the ascending order by name.
Q-9> To select all employees sorted dept wise in ascending order and within dept salary wise in descending order.
Q-10> To select all employees sorted dept wise in ascending order and within dept salary wise in descending order for dept no. 10 and 20.
Q-11> To select all employees along with their annual salary sorted on the basis of annual salary.