You are on page 1of 37

LIST OF EXPERIMENTS

1. Practice session: Students should be allowed to choose appropriate DBMS software, install it,
configure it and start working on it. Create sample tables, execute some queries, use SQLPLUS
features, use PL/SQL features like cursors on sample database. Students should be permitted to
practice appropriate User interface creation tool and Report generation tool.

2. A college consists of number of employees working in different departments. In this context,
create two tables’ employee and department. Employee consists of columns empno, empname,
basic, hra, da, deductions, gross, net, date-of-birth. The calculation of hra,da are as per the rules
of the college. Initially only empno, empname, basic have valid values. Other values are to be
computed and updated later. Department contains deptno, deptname, and description columns.
Deptno is the primary key in department table and referential integrity constraint exists between
employee and department tables. Perform the following operations on the database:
1. Create tables department and employee with required constraints.
2. Initially only the few columns (essential) are to be added. Add the remaining columns
separately by using appropriate SQL command
3. Basic column should not be null
4. Add constraint that basic should not be less than 5000.
5. Calculate hra,da,gross and net by using PL/SQL program.
6. Whenever salary is updated and its value becomes less than 5000 a trigger has to be
raised preventing the operation.
7. The assertions are: hra should not be less than 10% of basic and da should not be less
than 50% of basic.
8. The percentage of hra and da are to be stored separately.
9. When the da becomes more than 100%, a message has to be generated and with user
permission da has to be merged with basic.
10. Empno should be unique and has to be generated automatically.
11. If the employee is going to retire in a particular month, automatically a message has to be
generated.
12. The default value for date-of-birth is 1 jan, 1970.
13. When the employees called daily-wagers are to be added the constraint that salary should
be greater than or equal to 5000 should be dropped.

14. Display the information of the employees and departments with description of the fields.
15. Display the average salary of all the departments.
16. Display the average salary department wise.
17. Display the maximum salary of each department and also all departments put together.
18. Commit the changes whenever required and rollback if necessary.
19. Use substitution variables to insert values repeatedly.
20. Assume some of the employees have given wrong information about date-of-birth.
Update the corresponding tables to change the value.
21. Find the employees whose salary is between 5000 and 10000 but not exactly 7500.
22. Find the employees whose name contains ‘en’.
23. Try to delete a particular deptno. What happens if there are employees in it and if there
are no employees.
24. Create alias for columns and use them in queries.
25. List the employees according to ascending order of salary.
26. List the employees according to ascending order of salary in each department.
27. Use ‘&&’ wherever necessary
28. Amount 6000 has to be deducted as CM relief fund in a particular month which has to be
accepted as input from the user. Whenever the salary becomes negative it has to be
maintained as 1000 and the deduction amount for those employees is reduced
appropriately.
29. The retirement age is 60 years. Display the retirement day of all the employees.
30. If salary of all the employees is increased by 10% every year, what is the salary of all the
employees at retirement time.
31. Find the employees who are born in leap year.
32. Find the employees who are born on feb 29.
33. Find the departments where the salary of at-least one employee is more than 20000.
34. Find the departments where the salary of all the employees is less than 20000.
35. On first January of every year a bonus of 10% has to be given to all the employees. The
amount has to be deducted equally in the next 5 months. Write procedures for it.
36. As a designer identify the views that may have to be supported and create views.
37. As a designer identify the PL/SQL procedures necessary and create them using cursors.

38. Use appropriate Visual programming tools like oracle forms and reports, visual basic etc
to create user interface screens and generate reports.

Note: As a designer identifies other operations that may be required and add to the above list.
The above operations are not in order. Order them appropriately. Use SQL or PL/SQL depending
on the requirement.

3. Students may be divided into batches and the following experiments may be given to them to
better understand the DBMS concepts. Students should gather the required information, draw ER
diagrams, map them to tables, normalize, create tables, triggers, procedures, execute queries,
create user interfaces, and generate reports.
 Student information system
 APSRTC reservation system
 Hostel management
 Library management
 Indian Railways reservation
 Super market management
 Postal system
 Banking system
 Courier system
 Publishing house system

configure it and start working on it. select Programs(or All Programs). use PL/SQL features like cursors on sample database. and then Go To Database Home Page. Create sample tables. ■Password: Enter the password that was specified when Oracle Database XE was installed. execute some queries.Logging in as the Database Administrator: The first thing you need to do is to log in as the Oracle Database XE Administrator. use SQLPLUS features.then Oracle Database 10g Express Edition.Open the Database Home Page login window: On Windows. 2. then point to Oracle Database 10g Express Edition. enter the following information: ■Username: Enter system for the user name. and then Go To Database Home Page. from the Start menu.Click Login . 3. ■On Linux. Practice session: Students should be allowed to choose appropriate DBMS software.1. This guide covers the following topics: ■Logging in as the Database Administrator ■Unlocking the Sample User Account ■Logging in as the Sample User Account ■Creating a Simple Application ■Running Your New Application ■Using the Oracle Database XE Menus 1. Students should be permitted to practice appropriate User interface creation tool and Report generation tool. click the Application menu (on Gnome) or the K menu (on KDE). oracle database 10g express edition installation Welcome to Oracle Database 10g Express Edition (Oracle Database XE)! This tutorial gets you quickly up and running using Oracle Database XE by creating a simple application.At the Database Home Page login window. Follow these steps: 1. install it.

you need to log in as a database user.The Oracle Database XE home page appears. Make sure you are still logged on as the database administrator. Click the HR schema icon to display the user information for HR. You need to unlock this account before you can build a sample application. However. as described in the previous section. To unlock the sample user account: 1. and then click Database Users. this user’s account is locked. enter the following settings: . Click the Administration icon. for security reasons. 4. 2.Unlocking the Sample User Account To create your application. Under Manage Database User. 3. This user owns a number of database tables in a sample schema that can be used to create applications for a fictional Human Resources department. 2. Oracle Database XE comes with a sample database user called HR.

On the Database Home Page. select Create Application and click Next. 2. Click Alter User. click Login. c. add pages to your application. ■ Roles: Ensure that both CONNECT and RESOURCE are enabled. Next. Under Create Application. To create an application based on the EMPLOYEES table: 1. Under Add Page: a. 4. ■ Account Status: Select Unlocked. The Database Home Page appears. In the window. enter hr for both the user name and password. Accept the remaining defaults. Creating a Simple Application Creating an application is an easy way to view and edit your database data. select Report and Form. 3. Click Login. 5. In the Login window. 5. Now you are ready to create your first application. 2. 3. 4. Click Next. which is part of the HR schema. Log out from the database administrator account by clicking Logout in the upper right corner of the Database Home Page. You create this application based on the EMPLOYEES table. Logging in as the Sample User Account To log in as the sample user account: 1. Under Create Application: a. click the Application Builder icon. Name: Enter MyApp. 3. For Select Page Type. b. 4. . Click the Create button.■ Password and Confirm Password: Enter hr for the password.

Running Your New Application: . Shared components are common elements that can display or be applied on any page within an application. Click Add Page. under Create Application. accept the default (No) and click Next. After you click Create. 8. 9. To accept your selections. For the theme. accept the defaults and click Next. Next to the Table Name field. Confirm your selections. accept the default (One Level of Tabs) and click Next.Notice that Action describes the type of page you are adding. On the Shared Components panel. Click Next 6. Language. 7. Themes are collections of templates that you can use to define the layout and style of an entire application. click the up arrow. 10. select Theme 2 click Next. On the Tabs panel. For Authentication Scheme. and then select EMPLOYEES from the Search Dialog window. To return to a previous wizard page. This option enables you to import shared components from another application. d. and User Language Preference Derived From. b. click Previous. c. Two new pages display at the top of the page. the following message displays at the top of the page: Application created successfully. 5. click Create.

. . if you want. select the Home breadcrumb at the top of the page. In the log in page. .To run your application: 1. select Programs (or All Programs) and then Oracle Database 10g Express Edition. showing the EMPLOYEES table. click the Application menu (on Gnome) or the K menu (on KDE) and then point to Oracle Database 10g Express Edition. or component. view session state. click Edit Page 1 on the Developer toolbar.Go To Online Forum: Displays the online forum for discussions about Oracle Database XE. Your application appears. This help is only available if the database is started. 6. To manage the application. 5. Congratulations! You have just created your first application using Oracle Database XE. 4. The following menu items are available: ■ Get Help: Displays the following selections: . To return to the Database Home Page.Using the Oracle Database XE Menus: You can use the Oracle Database XE menus to perform basic functions with Oracle Database XE. control. do the following: ■ On Windows. 3.Register For Online Forum: Allows you to register for the Oracle Database XE online forum. To see the menus. You can query the EMPLOYEES table. from the Start menu. Click the Run Application icon. use the Developer toolbar at the bottom on the page. create a new page. Explore your application. To exit your application and return to Application Builder. ■ On Linux.Read Online Help: Displays the Oracle Database XE online help.Read Documentation: Displays the Oracle Database XE documentation library on the Internet. The Developer toolbar offers a quick way to edit the current page. enter hr for both the User Name and Password. or toggle debugging or edit links on and off. .

EXECUTE SOME SQL QUERIES For create table: 1) Table name 2) Column name 3) Data type Table restriction: •Table name must be unique in database schema. •Maximum 1000 columns •Name can contain: A-Z. "Logging in as the Database Administrator" on page 1 explains how to log into this home page as a database administrator. such as sys. shuts down the database. system. However. ■ Restore Database: Shuts down and then restores the database to the most recent backup. •The table name should begin with a letter and can be 1-30 characters long. or another account name. once you have connected to the database. and password is the password that was assigned when Oracle Database XE was installed. ■ Stop Database: Stops Oracle Database XE. the database is started for you after installation and every time your computer is restarted. issue the following command at the SQL prompt that appears: connect username/password where username is the user name. In ARCHIVELOG mode. refer to Oracle Database Express Edition 2 Day DBA. The get help. ■ Run SQL Command Line: Starts the SQL Command Line utility for Oracle Database XE. if you think the database is not running you can use this menu item to start it. By default. To connect to the database. performs an online backup of the database. ■ Go To Database Home Page: Displays the Oracle Database XE Home Page in your default browser. For more information on backups. backs it up. CREATE SAMPLE TABLES. refer to Oracle Database Express Edition 2 Day DBA. . ■ Start Database: Starts Oracle Database XE.■ Backup Database: In NOARCHIVELOG mode (the default). you can enter the command help at the SQL prompt. ■ Get Started: Link to this tutorial. For more information on restoring a database. 0-9. and then restarts it.

comm number(10).…. we use clscr to clear screen. NULL.). SQL> INSERT INTO EMP VALUES (100. 1 Row Inserted. 30000. In sql*plus.'PAVAN'. DESCRIBE: Command will give us with what columns we created table and their data type. 10). mgr number(10).'CHAIRMAN'. INSERTING DATA INTO REQURIED COLUMNS: SQL> INSERT INTO EMP (EMPNO.'CHAIRMAN'.sal number(10). SQL> DESC EMP Name Null? Type EMPNO NUMBER (10) ENAME VARCHAR2 (10) JOB VARCHAR2 (10) MGR NUMBER (10) HIREDATE DATE SAL NUMBER (10) COMM NUMBER (10) DEPTNO NUMBER(10) INSERTING VALUES IN TO TABLE: SYNTAX: INSERT into<table name> [list of columns] values (list of values). ENAME. deptno number(10)) . job varchar2(10). SQL>create table emp(empno number(10). . 10). 10000.<column2><Data type>. Table created.hiredate date. JOB.'01-JAN-2005'. DEPTNO) VALUES (100.ename varchar2(10).'PAVAN'.TO CREATE TABLE: SYNTAX: create table<table name> (<column1><Data type>.

HIREDATE. SQL> DELETE FROM EMPLOYEE_INFO WHERE EMPNO=102. SQL> UPDATE EMP SET SAL= SAL+1000. empname. deptname. SELECTING WITH WHERE CLAUSE: Whenever we select using where clause we get particular information depends on the column you specify in where clause. da. deductions. UPDATE TABLE USING WHERE CLAUSE: Whenever we give where clause in updating only that column corresponding rows will be updated. Employee consists of columns empno.SAL.DEPTNO FROM EMP. Deptno is the primary key in department table and . UPDATE STATEMENT: While updating a table if you don’t give where clause whole table will be updated. Department contains deptno. HERE INSTEAD OF TYPING ALL THE COLUMN NAMES WE TYPE “*” SQL> SELECT * FROM EMP. basic.COMM. and description columns. SQL> SELECT * FROM EMP WHERE EMPNO=100. DELETING DATA FROM A TABLE: If you want to delete info from a table.ENAME. gross. net. here if you won’t specify the where clause whole table info will be deleted. The calculation of hra.MGR. A college consists of number of employees working in different departments. Other values are to be computed and updated later. In this context.JOB. create two tables’ employee and department. SQL> UPDATE EMP SET SAL= SAL+1000 WHERE EMPNO=100.da are as per the rules of the college. basic have valid values. date-of-birth. SELECTING VALUES FROM A TABLE: SELECT EMPNO. 2. hra. empname. Initially only empno.

Create tables department and employee with required constraints. Basic column should not be null QUERY: .hiredate date. Add the remaining columns separately by using appropriate SQL command QUERY Alter table emp (add basic number(10). sal number(10). mgr number(10).comm number(10). Perform the following operations on the database: 1.hra number(10). EXPECTED OUTPUT: Table Altered OUTPUT: 3. description varchar2(40)). ename varchar2(10). referential integrity constraint exists between employee and department tables.da number(10)). EXPECTED OUTPUT: Table Created OUTPUT: QUERY: Create table dept( deptno number(10) primary key. QUERY: create table emp (empno number(10) primary key. Initially only the few columns (essential) are to be added. job varchar2(10). dname varchar2(20).deptno number(10)). EXPECTED OUTPUT: Table Created OUTPUT: 2.

Insert into employee values (null. QUERY: alter table employee add check (basic>5000) EXPECTED OUTPUT: Table Altered OUTPUT: 5. Calculate hra. EXPECTED OUTPUT: Cannot insert null value in basic column( not null constraint violated) OUTPUT: 4. BEGIN BASIC := &BASIC_SALARY. basic number(10) not null. gross and net by using PL/SQL program. .null. basic and we tried to insert null values in to both columns but it has not accepted. BASIC HRA DA 15000 12% 8% 12000 10% 6% 9000 7% 4% OTHERS 5% 200/- PL/SQL PROGRAM DECLARE BASIC NUMER HRA NUMBER. Note: Above we kept not null for both empno. NET NUMBER. ename varchar2(10)). Add constraint that basic should not be less than 5000. Create table employee (empno number(10) not null. DA NUMBER.da.'kumar').

1.06. end if. DA := BASIC * . END IF. Whenever salary is updated and its value becomes less than 5000 a trigger has to be raised preventing the operation.12. DA := BASIC * 200. DBMS_OUTPUT.PUT_LINE (‘DA: ‘ || DA).05. DA := BASIC * . DBMS_OUTPUT. DBMS_OUTPUT.PUT_LINE (‘NET: ‘ || NET).sal<5000 then raise_application_error(-20456. EXPECTED OUTPUT: pl/sql procedure successfully completed OUTPUT: 6..PUT_LINE (‘BASIC: ‘ || BASIC).07. EXPECTED OUTPUT: pl/sql procedure successfully completed . IF BASIC > 15000 THEN HRA := BASIC * . DBMS_OUTPUT. ELSIF BASIC > 12000 THEN HRA := BASIC * .PUT_LINE (‘HRA: ‘ || HRA). ELSE HRA := BASIC * .08. END.04. NET := BASIC + HRA + DA. PL/SQL PROGRAM Create or replace trigger sal Before insert or update on emp11 for each row begin if :new. end.'the 5000 below sal not accepted'). ELSIF BASIC > 9000 THEN HRA := BASIC * . DA := BASIC * .

so the da is merge to basic’). OUTPUT: .'the da is not accepted. EXPECTED OUTPUT: Trigger Created Successfully. EXPECTED OUTPUT: Table altered OUTPUT: 8. a message has to be generated and with user permission da has to be merged with basic.45). Alter table employee add check (da>sal*0. The assertions are: hra should not be less than 10% of basic and da should not be less than 50% of basic. QUERY: Alter table employee add check (hra>sal*0.\ PL/SQL PROGRAM Create or replace trigger da1 Before insert or update on employee for each row begin if :new. end. OUTPUT: 7.10). end if. When the da becomes more than 100%.da>sal then raise_application_error (-20456.

PL/SQL PROGRAM DECLARE v_dob DATE:='&v_dob'. Insert into employee values (s1. EXPECTED OUTPUT: Sequence created.nextval.1). Empno should be unique and has to be generated automatically.’siva’. 9000. 9000. Insert into employee values (s1. OUTPUT: 10.nextval. 900. If the employee is going to retire in a particular month. QUERY: Create or replace sequence s1 Increment by 1 Start with 501 End with 590 No cache No cycle. 4500). automatically a message has to be generated.’siva’. 4500). v_your_age NUMBER(3.9. . 900.

QUERY: Alter table employee modify date_of_birth DEFAULT ‘01-jan-70'. -of-birth is 1 jan. Emp_name varchar2(10). END IF. v_dob))/12. DBMS_OUTPUT. IF v_your_age>65 THEN DBMS_OUTPUT. Alter table employee add check (daily wagers>=5000).PUT_LINE('YOU ARE GOING TO RETAIRE IN THIS MONTH'). 1970. EXPECTED OUTPUT: Table Altered OUTPUT: 12. BEGIN v_your_age:= TRUNC (MONTHS_BETWEEN(SYSDATE. EXPECTED OUTPUT: Table Altered OUTPUT: . EXPECTED OUTPUT: pl/sql procedure successfully completed OUTPUT: 11.PUT_LINE ('Your age is ' || v_your_age). When the employees called daily-wagers are to be added the constraint that salary should be greater than or equal to 5000 should be dropped. END. QUERY: Alter table employee Add daily wagers number (10).

QUERY: desc emp. EXPECTED OUTPUT: name null? type empno number(10) ename varchar2(10) job varchar2(10) mgr number(10) hiredate date sal number(10) comm number(10) deptno number(10) OUTPUT: .13. Display the information of the employees and departments with description of the fields. desc dept.

Display the average salary of all the departments. Display the average salary department wise. QUERY: Select deptno. QUERY: Select deptno. QUERY: Select avg(sal) from emp. Display the maximum salary of each department and also all departments put together.14.---------- cse 200 eee 300 ece 600 OUTPUT: 16.max(sal) from emp group by deptno. EXPECTED OUTPUT: Sal -------- 2500 OUTPUT: 15.avg(sal) from emp group by deptno. EXPECTED OUTPUT: DEPT AVGSALARY ---------. EXPECTED OUTPUT: .

Select * from emp. DEPT MAXSALARY ---------. EXPECTED OUTPUT: empno ename job mgr hiredate sal comm deptno 100 Pavan Chairman 10 01-Jan-05 32000 10000 10 101 Gayatri P.---------- cse 300 eee 500 ece 600 OUTPUT: 17. select * from emp. QUERY: select * from emp. EXPECTED OUTPUT: 2 rows deleted. EXPECTED OUTPUT: empno ename job mgr hiredate sal comm deptno 100 Pavan Chairman 10 01-Jan-05 32000 10000 10 101 Gayatri P. Commit the changes whenever required and rollback if necessary.A 100 01-Jan-05 12000 1000 10 Delete from emp. EXPECTED OUTPUT: Rollback complete.A 100 01-Jan-05 12000 1000 10 . EXPECTED OUTPUT: No rows selected roll back.

Assume some of the employees have given wrong information about date-of-birth. Use substitution variables to insert values repeatedly. department_name varchar2(50) not null). EXPECTED OUTPUT: Table Updated OUTPUT: .’&department_name’). EXPECTED OUTPUT: No rows selected 18. QUERY: CREATE TABLE departments ( department_id number(10) not null. select * from emp. EXPECTED OUTPUT: 1 row inserted OUTPUT: 19. INSERT INTO department VALUES(&department_id. EXPECTED OUTPUT: 3 rows deleted. EXPECTED OUTPUT: Rollback complete. roll back. delete from emp. Update the corresponding tables to change the value. commit. QUERY: Update emp set date-of-birth=’04-jan-14’ Where empno =1001. EXPECTED OUTPUT: Commit complete.

Try to delete a particular deptno. EXPECTED OUTPUT: 1 row deleted. Find the employees whose name contains ‘en’.sal from emp where sal between 5000 and 10000 and not sal=7500. QUERY: a) delete from emp where deptno=10. EXPECTED OUTPUT: Ename --------- meena OUTPUT: 22. QUERY: select empno. What happens if there are employees in it and if there are no employees. EXPECTED OUTPUT: Sal ------ 7000 OUTPUT: 21. QUERY: Select empno.ename from emp Where ename like '%en%'. Find the employees whose salary is between 5000 and 10000 but not exactly 7500.20. OUTPUT: .

b) delete from emp where deptno=10.ename employeename from emp. Create alias for columns and use them in queries. QUERY: select empno as emplyeenumber. OUTPUT: 23. QUERY: a)Select * from emp b)Select * from emp Order by sal. Order by sal asc. List the employees according to ascending order of salary. ---------- 100 PAVAN 100 PAVAN OUTPUT: 24. EXPECTED OUTPUT: Eno ename sal ---------------------- 123 xyz 1000 124 abc 1200 OUTPUT: . EXPECTED OUTPUT: 0 rows deleted. EXPECTED OUTPUT: emplyeenumber employeename ------------.

&hra. &da. Note: The sal column using double amp cent(&&) so the data inserting time sal is asked one time only for first row.&gross. EXPECTED OUTPUT: 1 row inserted OUTPUT: .deptno from emp12 order by deptno.&pf.sal. List the employees according to ascending order of salary in each department.&&sal. ‘&&’ wherever necessary QUERY: Insert into emp values(&empno. EXPECTED OUTPUT: Empno sal deptno ------------------------------ 123 1000 10 124 1200 11 OUTPUT: 26. QUERY: select empno.&basic.25.’&ename’.sal.the remining columns automatically is taken the same data.’ &date’).

Table created.pamt in kab_tran_tab. If vacctype ='S' and cbal<1000 then Raise_application_error(-20345.27.accno%type. Else if vacctype ='C' and cbal<1000 then .'the bal is too low.bal into vacctype. If pttype='D' then cbal:=cbal+pamt. PL/SQL program Create table kcb_acc_tab (accno number(10).accno number(10). create table kcb_tran_tab(sno number(10).cbal from kcb_acc_tab where accno=paccno. vacctype kcb_acc_tab. amt number(10)).bal%type. Else if pttype='w' then cbal:=cbal-pamt. so no transaction').bal number(10)).amt%type) Is cbal kcb_acc_tab. Sequence created. Whenever the salary becomes negative it has to be maintained as 1000 and the deduction amount for those employees is reduced appropriately. create sequence s1 increment by 1 start with 1 Maxvalue 100 Nocycle Nocache. End if. Table created.pttype in kcb_tran_tab.acctype varchar2(20). Begin Select acctype.ttype varchar2(20).ttype%type. Create or replace procedure upd_bal (paccno in kcb_acc_tab. date_of_tran date. Amount 6000 has to be deducted as CM relief fund in a particular month which has to be accepted as input from the user.acctype%type.

'D'.paccno. End if.sysdate.6000). ------------------------- 1001 Anil 20-Dec-72 1002 Siva 21-Dec-72 1003 Gowtham 22-Dec-72 OUTPUT: . Display the retirement day of all the employees. Update kcb_acc_tab set bal=cbal where accno=paccno. Insert into kcb_tran_tab values(s1. Commit.pamt). End upd_bal .nextval. Note: In this I am considering 360 as month.'dd-mon-yyyy') "next month" from emp. EMPNO ENAME DATE_OF_BIRTH -----------. EXPECTED OUTPUT: Exec(1001.'the bal is too low. Raise_application_error(-20346. OUTPUT 28.-----------------. so no transaction'). The retirement age is 60 years.D.put_line(paccno|| ‘is not exist’). EXPECTED OUTPUT: Next month ---------------- 20-dec-2042 21-dec-2042 22-dec-2042 select * from emp. 720). exception WHEN NO_DATA_FOUND THEN Dbms_output. So 360/12=30 years (it is retirement age) QUERY: Select to char (add months (date_of_birth.

706989 Select 20000+ ((20000*0.'03-jul-1988')/12-60 "retirement year" from dual. QUERY: select months_between ('19-oct-2012'. If salary of all the employees is increased by 10% every year.4) from dual.10)*35) from dual OUTPUT: 30. Note: MOD(2016.29. EXPECTED OUTPUT: RETIREMENT YEAR ------------------------------ -35. what is the salary of all the employees at retirement time. QUERY: select mod(2016.4) u get the answer is 0 it is leap year but u r not get the answer <>0 not leap year EXPECTED OUTPUT: Mod ------ 0 OUTPUT: .

QUERY: Select deptno from emp where sal>20000. . EXPECTED OUTPUT: Empno ename dob ------------------------ 123 xxxx 29-FEB-1994 OUTPUT: 32.31. Find the departments where the salary of at-least one employee is more than 20000. EXPECTED OUTPUT: Deptno -------- 10 12 OUTPUT: 33. QUERY: Select deptno from emp where sal<20000. QUERY: Select * from emp Where date_of_birth like ‘%feb-29%’. Find the employees who are born on feb 29.

QUERY: create view emvup10 as select * from emp where deptno=10 EXPECTED OUTPUT: View created select * from emvup10. empno ename ------------------ 123 xxxx 124 abcd insert into empvu10 values(126. select * from empvu10. empno ename ------------------ 123 xxxx 124 abcd 126 xxxx OUTPUT: 35. As a designer identify the PL/SQL procedures necessary and create them using cursors. EXPECTED OUTPUT: Deptno -------- 15 17 OUTPUT: 34. As a designer identify the views that may have to be supported and create views. PL/SQL program DECLARE .'xxxx').

ENAME .  Student information system  APSRTC reservation system  Hostel management  Library management  Indian Railways reservation  Super market management . LOOP FETCH C1 INTO EMPNUM. execute queries.put_line(EMPNUM||' '||EMPNAME||' '||DEPTNUM).DEPTNO FROM EMP. BEGIN OPEN C1. normalize. and generate reports. EMPNUM EMP. Students should gather the required information. EMPNAME EMP. triggers. EXPECTED OUTPUT: PL/SQL Procedure successfully Created OUTPUT: 3.ENAME%TYPE.DEPTNUM. create tables. if c1%notfound then exit. Students may be divided into batches and the following experiments may be given to them to better understand the DBMS concepts.EMPNAME. DEPTNUM EMP. procedures. create user interfaces.EMPNO%TYPE.DEPTNO%TYPE. map them to tables.CURSOR C1 IS SELECT EMPNO. draw ER diagrams. end. end if. END LOOP. else dbms_output.

 Postal system  Banking system  Courier system  Publishing house system SAMPLE EXAMPLES LIBRARY MANAGEMENT SYSTEM The Language Management System implements Oracle as the Backend and thus the database schemas defined and modified through Oracle SQL. The Language Management System consists of two tables to store all the information . The schemas are as follows. The tables are Books and Patron. PATRON TABLE: Name Null? Type Patron_id Not null NUMBER(10) Name VARCHAR2(20) Design VARCHAR2(20) DOJ DATE Expiry DATE NOB NUMBER(1) Fine NUMBER(4) .

BOOK TABLE: Name Null? Type BAR_CODE NUMBER(6) TITLE VARCHAR2(20) AUTHOR VARCHAR2(20) ISBN NUMBER(10) STATUS VARCHAR2(10) REF_OR_NOT VARCHAR2(10) BORROWER_ID NUMBER(10) PHYS NUMBER(4) ER DIAGRAM: .

……….expiry date.. INSERTING VALUES IN PATRON: . CREATING A TABLE BOOKS: SQL>Create table books(BAR_CODE number(6).borrower_id number(10).author varchar2(20). CREATING A TABLE PATRON: Create table patron(patron_id number(10) primary key.ISBN number(10).fine number(4)).).status varchar2(10). SYNTAX FOR INSERT: Insert into tablename values(values list).columnname2 datatype.design varchar2(20).DOJ date.title varchar2(20).SYNTAX FOR CREATING A TABLE: Create table tablename(columnname1 datatype.phys number(4)).REF_OR_NOT varchar2(10).NOB number(1).name varchar2(20).

&fine).&expiry.’&design’. The Following are the entities and its attributes Bus: Bus_No: varchar (10) (primary key) Source: varchar (20) Destination: varchar (20) Passenger: PNR_No : Number(9) (primary key) Ticket_No : Number(9) Name : varchar(15) . Identify what Data has to be persisted in the databases.&NOB. Values are inserted.SQL>Insert into patron values(&patron_id.&DOJ.’&name’. APSRTC RESERVATION SYSTEM Analyze the problem and come with the entities in it.

Male/Female PPNO : varchar(15) Reservation: PNR_No : number(9) (foreign key) Journey_date : date No_of_seats : integer(8) Address : varchar(50) Contact_No : Number(9) Status : Char(2) Cancellation : PNR_No : number(9)(foreign key) Journey_date : date No_of_seats : integer(8) Address : varchar(50) Contact_No : Number(9) Status : Char(2) Ticket: Ticket_No : number(9)(primary key) Journey_date : date Age : int(4) Sex : Char(10) Source : varchar .Age : integer(4) Sex : char(10) .

Destination :varchar Dep_time : varchar E-R Diagram .