Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Community
Ask the community for help and clear up your study doubts
Discover the best universities in your country according to Docsity users
Free resources
Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors
All dbms related sql and pl/sql
Typology: Study notes
1 / 22
This page cannot be seen from the preview
Don't miss anything!
(Autonomous), Affiliated to Osmania University Re-accredited by NAAC with A Grade with CGPA 3. A Catholic Christian Minority Institution King Koti Road, Hyderabad.
B.Sc. (Computer Science) II Year/ III Semester Theory Paper - III Relational Database Management System UNIT-I: Introduction to Database concepts & Relational Model Basic Concepts and Definitions: Data, Information, Metadata, Database, DBMS. The Database Environment : Traditional File Processing Systems, The Database approach, Components of Database Environment. The Three-Level Architecture, Advantages of Database Management System, Types of databases, Database Languages, Risks and costs of Database, Data Models. Relational Model: Introduction, Keys, Relational Algebra: Unary Operations, Set Operations, Join Operations, Aggregation and Grouping Operations. UNIT-II: SQL Introduction to various Databases: Oracle-SQL. MySQL, SQL Server, DB2, MS Access. SQL: Introduction, SQL Environment, The ISO SQL Data Types, Integrity Constraints, Data Definition–Creating a Database, Creating a Table, Changing a Table Definition, Removing a Table, Creating an Index, Removing an Index, Views, Granting and Revoking Privileges to Users. Data Manipulation: Inserting, Updating & Deleting Data from database, Simple Queries, Aggregate Functions, Order by Clause, Group by Clause, Having Clause.Joins, Sub Queries, Correlated Sub Queries. PL/SQL: Introduction to PL/SQL, Advantages of PL/SQL, The Generic PL/SQL Block, The Pl/SQL Execution Environment, Declarations, Assignments, Control Statements, Exceptions, Cursors, Subprograms, Triggers , Stored Procedures, Functions, and Packages. Scheme of Instruction Scheme of Examination Total durations Hrs : 60 Max. Marks : 100 Hours/Week : 06(4T+2P) Internal Examination : Credits : 5 SBT : 10 Instruction Mode: Lecture +Practical External Examination : Course Code : BS.06.201.13.T Exam Duration : 3 Hrs Course Objectives: To impart the students with the knowledge on the database management systems, design models, Normalization, Transaction management and Oracle in Creation and maintenance of databases. Course Outcomes: At the end of the course the student will be able to CO1: Understand and evaluate the database environment in an organization. CO 2: Design and Develop database using SQL & PL/SQL. CO 3: Understand and Design the ER Model utilized for developing a database. CO 4: Applythe Normalization techniques to evaluate and correct table structures. CO 5: Design real time databases using the concepts of Transaction Management, Concurrency Control& Distributed Databases.
1. (Exercise on retrieving records from the table) EMPLOYEES (Employee_Id, First_Name, Last_Name, Email, Phone_Number, Hire_Date, Job_Id, Salary, Commission_Pct, Manager_Id, Department_Id) ( a) Find out the employee id, names, salaries of all the employees ( b) List out the employees who works under manager 100 ( c) Find the names of the employees who have a salary greater than or equal to 4800 ( d) List out the employees whose last name is ‘AUSTIN’ ( e) Find the names of the employees who works in departments 60,70 and 80 ( f ) Display the unique Manager_Id. 2. (Exercise on updating records in table) Create Client_master with the following fields(ClientNO, Name, Address, City, State, bal_due) ( a ) Insert five records ( b ) Find the names of clients whose bal_due> 5000. ( c ) Change the bal_due of ClientNO “ C123” to Rs. 5100 ( d ) Change the name of Client_master to Client. ( e ) Display the bal_due heading as “BALANCE” 3. Rollback and Commit commands Create Teacher table with the following fields(Name, DeptNo, Date of joining, DeptName, Location, Salary) ( a ) Insert five records ( b ) Give Increment of 25% salary for Mathematics Department. ( c ) Perform Rollback command ( d ) Give Increment of 15% salary for Commerce Department ( e ) Perform commit command 4. (Exercise on order by and group by clauses) Create Sales table with the following fields( Sales No, Salesname, Branch, Salesamount, DOB) ( a ) Insert five records ( b ) Calculate total salesamount in each branch ( c ) Calculate average salesamount in each branch. ( d ) Display all the salesmen, DOB who are born in the month of December as day in character format i.e. 21-Dec- 09 ( e ) Display the name and DOB of salesman in alphabetical order of the month. 5. Create an Emp table with the following fields: (EmpNo, EmpName, Job,Basic, DA, HRA,PF, GrossPay, NetPay) (Calculate DA as 30% of Basic and HRA as 40% of Basic) ( a ) Insert Five Records and calculate GrossPay and NetPay. ( b ) Display the employees whose Basic is lowest in each department. ( c ) If NetPay is less than <Rs. 10,000 add Rs. 1200 as special allowances.
( d ) Display the employees whose GrossPay lies between 10,000 & 20, ( e ) Display all the employees who earn maximum salary.
6. Employee Database An Enterprise wishes to maintain a database to automate its operations. Enterprise is divided into certain departments and each department consists of employees. The following two tables describes the automation schemas Dept (deptno, dname, loc) Emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) a) Update the employee salary by 15%, whose experience is greater than 10 years. b) Delete the employees, who completed 30 years of service. c) Display the manager who is having maximum number of employees working under him? d) Create a view, which contain employee names and their manager 7. Using Employee Database perform the following queries a) Determine the names of employee, who earn more than their managers. b) Determine the names of employees, who take highest salary in their departments. c) Determine the employees, who are located at the same place. d) Determine the employees, whose total salary is like the minimum Salary of any department. e) Determine the department which does not contain any employees. 8. Consider the following tables namely “DEPARTMENTS” and “EMPLOYEES” Their schemas are as follows, Departments ( dept no , dept name , dept_location ); Employees ( emp_id , emp_name , emp_salary,dept_no); a) Develop a query to grant all privileges of employees table into departments table b) Develop a query to grant some privileges of employees table into departments table c) Develop a query to revoke all privileges of employees table from departments table d) Develop a query to revoke some privileges of employees table from departments table e) Write a query to implement the save point. 9. Using the tables “DEPARTMENTS” and “EMPLOYEES” perform the following queries a) Display the employee details, departments that the departments are same in both the emp and dept. b) Display the employee name and Department name by implementing a left outer join. c) Display the employee name and Department name by implementing a right outer join. d) Display the details of those who draw the salary greater than the average salary. 10. Write a PL/SQL program to demonstrate Exceptions. 11. Write a PL/SQL program to demonstrate Cursors.
B.Sc. (Computer Science) II Year / III Semester PRACTICAL PAPER - III Course Name: SQL Lab (Oracle 10g XE) Course Code : BS.06.201.13.T Lab Practical No. Question:
SQL> insert into employees values(47403,'Raja','Shekhar',9848002255,'12-aug- 20 04',303,58451,603,103, 80 ); 1 row created. SQL> insert into employees values(47404,'Ravi',' AUSTIN ',9701811356,'30-sep- 2006',304,36520,604,100, 90 ); 1 row created. SQL> insert into employees values(47405,'Ranga','Raju',9032553262,'17-May- 2014',305,2568,605,105, 60 ); 1 row created. SQL> Select * from Employees;
Query: sql>update Client_Master set Balance_Due=5100 where Client_No='C123'; d ) Change the name of Client_master to Client. Query: sql>rename Client_Master to Client12; e ) Display the bal_due heading as “BALANCE” Client master table: Query: sql> select Client_No, Balance_Due Balance from Client_Master Lab Practical No: 3 Question: Rollback and Commit commands Create Teacher table with the following fields(Name, DeptNo, Date of joining, DeptName, Location, Salary) ( a ) Insert five records ( b ) Give Increment of 25% salary for Mathematics Department. ( c ) Perform Rollback command ( d ) Give Increment of 15% salary for Commerce Department ( e ) Perform commit command Create Teacher table with the following fields(Id,Name, DeptNo, Date of joining, DeptName, Location, Salary) Query : SQL> create table teacher(Id number(2) primary key, name varchar2(20) not null, Deptno number(2) not null, Deptname varchar2(20) not null, joinDate date not null, location varchar2(20) not null, salary number(10,2) not null); ( a ) Insert five records SQL> insert into teacher values(10,'kiran',4,'computer science', '03-Jun-2003', 'hyderabad', 50000); SQL> insert into teacher values(11,'ramu', 5 ,'mathematics', '13-Jul-2008', 'hyderabad', 40000); SQL> insert into teacher values(12,'raju',6,'commerce', '23-Dec-2005', 'abids', 30000); SQL> insert into teacher values(13,'manirathnam',6,'commerce', '18-Dec-2006', 'abids', 30000); SQL> insert into teacher values(14,'sita',6,'commerce', '28-Aug-2016', 'kingkoti', 23000); ( b ) Give Increment of 25% salary for Mathematics Department. Sql> update teacher set salary= salary+(salary * 0.25) where Deptname= ‘mathematics'; ( c ) Perform Rollback command Sql>rollback;
( d ) Give Increment of 15% salary for Commerce Department Sql> update teacher set salary= salary+(salary * 0.15) where Deptname= ‘commerce'; ( e ) Perform commit command Sql>commit; Lab Practical No: 4 Question:
( a ) Insert Five Records and calculate GrossPay and NetPay. Query: insert into Employee values(4019,'Talatam Venkatesh','Director',10000); insert into Employee values(4039,'Gumaa','Technical Engineer',15000); insert into Employee values(4015,'Anudeep Varma','Technical Engineer',12000); insert into Employee values(4016,'I Vasudeva Varma','Technical Engineer',13000); insert into Employee values(4027,'Uday Reddy','Director',16000); insert into Employee values(4006,'Sai Mani','Director',10000); **#Updating Attributes DA, HRA, PF, Gross pay, Net Pay?
Sql>alter table employeeadd(Emp_DA number(6)); Sql>update Employeeset Emp_DA=(30/100)Emp_Basic; # Adding coloumn to table and Updating Attributes HRA Sql>alter table employee add(Emp_HRA number(6)); update Employee set Emp_HRA=(40/100)Emp_Basic; # Adding coloumn to table and Updating Attributes PF Sql>alter table employee add (Emp_PF number(6)); Sql> Employee set Emp_pf= Emp_basic*(12/100); # Adding coloumn to table and Updating Attributes Gross Pay Sql>alter table employee add (Emp_Grosspay number(6)); Sql>update Employee set Emp_grosspay= EMp_hra+emp_da+emp_basic; # Adding coloumn to table and Updating Attributes Net Pay alter table employee add (Emp_netpay number(6)); update Employee set Emp_netpay=emp_grosspay-emp_pf; Display the employeetable: Query: sql>select * from employee; ( b ) Display the employees whose Basic is lowest in each department. Query: sql>select min(emp_basic) from employee group by designation;
( c ) If NetPay is less than <Rs. 10,000 add Rs. 1200 as special allowances. Query: Sql> update employee set emp_netpay=emp_netpay+1200 where emp_netpay<30000; ( d ) Display the employees whose GrossPay lies between 10,000 & 20, Query: sql>select * from employee where emp_grosspay between 1 0000 and 2 0000; ( e ) Display all the employees who earn maximum salary. Query: sql>select * from employee where emp_grosspay = (select max(emp_grosspay) from employee); Lab Practical No: Question:
Sql> insert into emp (empno, ename ,job,mgr, hiredate, sal, deptno) values ( 69000 ,’ JULIUS’,’ CLERK’, 66928 ,’03-Dec- 1991 ’, 105 0.00, 103 ); Sql> insert into emp (empno, ename ,job,mgr, hiredate, sal, deptno) values ( 69324 ,’ MARKER’,’ CLERK’, 67832 ,’23-Jan- 1992 ’, 140 0.00, 101 ); Sql> insert into emp (empno, ename ,job,mgr, hiredate, sal, deptno) values ( 69924 ,’ MARKER’,’ CLERK’, 67832 ,’23-Jan- 1992 ’, 140 0.00, 104 ); a)Update the employee salary by 15%, whose experience is greater than 30 years. Query Sql> update emp set sal=sal+(sal*0.15) where (sysdate-hiredate)/365>30; b)Delete the employees, who completed 30 years of service. Sql> delete from emp where (sysdate-hiredate)/365>30; c)Display the manager who is having maximum number of employees working under him? Create view Sql> create view mgrcount as select mgr, count(empno) total from emp group by mgr; Sql> select mgr from mgrcount where total in (select max(total) from mgrcount); d)Create a view, which contain employee names and their manager Sql> create view employee_manager as select e1.ename ,e2.ename from emp e1, emp e where e1.mgr=e2.empno; Lab Practical No: Question:
sql>select e1.ename from emp e1, emp e2 where e1.mgr=e2.empno and e1.sal>e2.sal; b)Determine the names of employees, who take highest salary in their departments. Create a view to store maximum salaries of each department SQL> create view maxsalaries as select max(sal) maxsalary, deptno from emp group by deptno; Displaythe names of employees, who take highest salary in their departments. Sql>select ename, sal from emp, maxsalaries where emp.deptno= maxsalaries.deptno and sal=maxsalary; c)Determine the employees, who are located at the same place. SQL> select ename,dname from emp , dept where emp.deptno=dept.deptno order by dname; d)Determine the employees, whose total salary is like the minimum Salary of any department. SQL> select empno, ename, sal from emp where sal in(select max(sal) from emp group by deptno); e)Determine the department which does not contain any employees. SQL> select dname from dept where deptno not in(select deptno from emp); Lab Practical No: 8 Question:
dbms_output.put_line(cval.empno||' '||cval.ename); end loop; close c11; end; / Output empno ename 69000 JULIUS 63679 SANDRINE 68736 ADNRES 69324 MARKER 69924 MARKER 67832 CLARE 66928 BLAZE 65646 JONAS 67858 SCARLET PL/SQL procedure successfully completed. Lab Practical No: Question: 11.Write a PL/SQL program to demonstrate Functions. SQL> create or replace function fname(a in number,b in out number) return number is begin b:=a; return b; end; SQL> / Function created. Program to invoke a function SQL> declare x number; begin x:=fname(23,x); dbms_output.put_line(x); end;
Output SQL> / 23 PL/SQL procedure successfully completed. Lab Practical No: Question: 12.Write PL/SQL queries to create Procedures. SQL> create or replace procedure emp_proc IS BEGIN update emp set sal=sal+sal0. where comm<>sal0.09; END; SQL> / Procedure created. Executing Procedure: SQL> execute emp_proc1; Output PL/SQL procedure successfully completed. Lab Practical No: Question: 13.Write a PL/SQL program to demonstrate Packages. Step1: Creating package specification CREATE or replace PACKAGE emppackage AS procedure emp_proc2; function fname2(a in number,b in out number) return number; end; Step2: Creating package definition or body
