Dbms Lab Manual

DBMS LAB MANUAL CYCLE-1: 1->Create a student table that contains sid,sname,branch,sem,dob,marks. 2->Insert rows into the table.Using various ways of insertion(insert)command. 3->alter the student table to contain address of the student,and also modify the size of sname. 4->Display the content of the table. 5->delete some rows from student table based on conditions. 6->drop the student table.

CYCLE-2: 1->Create the payroll data base consider only two tables employee and department. 2->simple queries on the above database. a)list all information of the above both tables. b)list the empno,ename,jobtitle,and hiredate of employee from the employee table. 3->list the name,salary of the employees who are clerks. 4->list the name,job,salary of every employee joined on ‘december 17,1980’, 5->list name and annual salary of all the employees. 6->list the department name &deptno for departments having deptno.>=20.

7->list the employees who have salary between 1000&2000. 8->list the name,salary,commision of the employees where commision is greater than their salary. 9->list the name,monthly salary and daily salary and hourly salary for employee;assume that there are 22 working days in a month,8 working hours in a day.display the rows with coloumn names as monthly,daily &hourly sal. 10->list the name and empno of managers who earn more than 20000.display the result in alphabetical order of the name. 11->display all employee names which have ‘TH’or ‘LL’ in them. 12->list the details of the employees in the depts of 10 &20 in the order of empno. 13->display the information about the managers and clerks from the column ‘Job’ in the table emp,order the result by deptno. 14->list the empnames that don’t end with ‘s’. 15->list the emp names that begin with ‘c’ 16->list the name,job&dept of each emp whose names falls in the range c to p. 17->list the names of the employees strats with T/t anad ends with R/r. 18->list the name of employees who joined during 1983. 19->list the data as shown below”Smith has held the position of clerk in dept 20 since 19 june,1983” 20->list all rows in employee table by converting commisison column to zero.

CYCLE-3: 1->Write a query that accepts a jobtitle and displays all the rows according to that title. 2->list the length of the names appearing on the emptable,eliminate duplicate values. 3->list the names and hire the date of those in the dept of 20.display the hire date formatted as ‘13/oct/99’(ie. ‘dd/mm/yy’). 4->display the experience of the manager round to the nearest whole number of the months. 5->write a query to find the average sal per job in each departments. 6->display avg sal.minimum sal,max sal of emp having job clerk or manager. 7->list the emp who earned less than 30%of the managers salary. 8->display the details of the employee who joined last in each dept. 9->Write a query to display the no.of employees and their totla sum of salaries grouped by their deptno. 10-> Write a que ry to list no.of employees working in “NEW YORK”. 11-> Write a query to list no.of employees along with a message depending on salary,if salary>1500 ,the message will be ‘above target’;salary<1500,the message will be ‘below target’;salary=1500,then it is ‘not in target’. 12->display those jobs where the min sal is >=3000. 13->find all the depts which have more than 3 employees. 14-> Write a query to list the emp whose job is same as ‘Blake’.

15-> Write a query to list no.of employees who earn more than the lowest salary in a department. 16->list the job with highest average salary. 17->display each empname with hire date and review date.assume review date is one year after the hire date,order the output in the ascending order of the review date. 18->list the emp where salary is that of the “scott”or “ward”. 19-> list the emp whose salary is greater than any clerk. 20-> list the dept with average salary is higher than any other department. 21-> Write a query to list the emp who are earning more than “miller”. 22-> Write a query to list the depts which are having avg sal more than avg sal of all employees. 23->-> Write a query to list the dept with highest avg salary. 24-> Write a query to list the dept which are having the max sal>4000 and total no.of employees>6. 25-> Write a query to list the dept which are having the tot sal more than all clerks total salary but having max sal lesser than all managers. 26-> Write a query to list the emps who are earning more than managers. 27-> Write a query to list the emp with top 3 salaries. 28-> Write a query to list the ename,sal and their salary to avg.sal difference. 29-> Write a query to display the service left for an employee assuming that 58 years of the age limit for the service.

30->display all the employees who haven’t joined in the same year as that of those employees working in the dept with deptno..’20’.

CYCLE-4: Simple PL/SQL programs: 1->Write a program to accept two numbers and add them. 2-> Write a program to print below given no. 3-> Write a program wish the user depending on the time. 4-> Write a program to check whethere a given number is odd or even. 5-> Write a program to find sum of n numders. 6-> Write a program to reverse a given number. 7-> Write a program ot find the no of vowels in a given string. 8-> Write a program that checks the validity of a givem empno.handling with exception. 9-> Write a program to accept empno and print the details as follows: Emp name/designation/annual income/grade. 10-> Write a program which accepts an empno and increases his salary by 1000/-.

CYCLE-5: 1-> Write a program to list all the employee details.

2-> Write a program to check for the existance of a given employees in emptable using the cursors. 3-> Write a program to list the details of dept entered if the dept is having employees.

CYCLE-6: PROCEDURES.FUNCTIONS AND TRIGGERS. 1-> Write a procedure that takes emp no and amount as arguments and update the salary of an employee by the given amount. 2-> Write a procedure that delete student details by accepting studentno. 3-> Write a procedure that accepts rhe empno and return his total earning per year to the calling environment. 4-> Write a procedure to print the details of emp who is earning maximum salary. 5-> write a function to check for the existance of an employee. 6-> write a function which accepts empno as parameters and return his frade. 7->Write a trigger on employee table to fire after insert or update or delete. 8-> Write a trigger on employee table to fire before insert or update or delete. 9-> Write a trigger on employee table to fire before insert for each row. 10-> Write a trigger on employee table to fire before delete for each row.