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

execute some queries. Practice session: Students should be allowed to choose appropriate DBMS software. select Programs(or All Programs). click the Application menu (on Gnome) or the K menu (on KDE).Click Login . use PL/SQL features like cursors on sample database. 3. ■Password: Enter the password that was specified when Oracle Database XE was installed.Open the Database Home Page login window: On Windows. enter the following information: ■Username: Enter system for the user name. then point to Oracle Database 10g Express Edition. and then Go To Database Home Page.then Oracle Database 10g Express Edition. Students should be permitted to practice appropriate User interface creation tool and Report generation tool. Create sample tables. 2. ■On Linux. and then Go To Database Home Page.1. 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. configure it and start working on it. 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.Logging in as the Database Administrator: The first thing you need to do is to log in as the Oracle Database XE Administrator. install it.At the Database Home Page login window. Follow these steps: 1. from the Start menu. use SQLPLUS features.

this user’s account is locked. Oracle Database XE comes with a sample database user called HR. However. You need to unlock this account before you can build a sample application. and then click Database Users. as described in the previous section. Click the HR schema icon to display the user information for HR. 3. To unlock the sample user account: 1. you need to log in as a database user. enter the following settings: . 2.Unlocking the Sample User Account To create your application. 4. 2. for security reasons. 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. Make sure you are still logged on as the database administrator.The Oracle Database XE home page appears. Click the Administration icon. Under Manage Database User.

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

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

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

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

.…. mgr number(10). SQL>create table emp(empno number(10). SQL> INSERT INTO EMP VALUES (100.'PAVAN'. 30000. 1 Row Inserted. 10000. JOB.TO CREATE TABLE: SYNTAX: create table<table name> (<column1><Data type>. Table created. job varchar2(10). 10). NULL. DESCRIBE: Command will give us with what columns we created table and their data type.'PAVAN'. 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).<column2><Data type>.ename varchar2(10).comm number(10). INSERTING DATA INTO REQURIED COLUMNS: SQL> INSERT INTO EMP (EMPNO.'CHAIRMAN'. In sql*plus. DEPTNO) VALUES (100.hiredate date.). deptno number(10)) . ENAME.sal number(10). 10). we use clscr to clear screen.'CHAIRMAN'.'01-JAN-2005'.

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

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

NET NUMBER.null. ename varchar2(10)). Create table employee (empno number(10) not null. DA NUMBER. gross and net by using PL/SQL program. basic number(10) not null. QUERY: alter table employee add check (basic>5000) EXPECTED OUTPUT: Table Altered OUTPUT: 5.da. Calculate hra. basic and we tried to insert null values in to both columns but it has not accepted. EXPECTED OUTPUT: Cannot insert null value in basic column( not null constraint violated) OUTPUT: 4. BEGIN BASIC := &BASIC_SALARY. Insert into employee values (null. Add constraint that basic should not be less than 5000.'kumar'). 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.

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

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

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

QUERY: Alter table employee modify date_of_birth DEFAULT ‘01-jan-70'. EXPECTED OUTPUT: pl/sql procedure successfully completed OUTPUT: 11. DBMS_OUTPUT. v_dob))/12.PUT_LINE('YOU ARE GOING TO RETAIRE IN THIS MONTH'). EXPECTED OUTPUT: Table Altered OUTPUT: 12. EXPECTED OUTPUT: Table Altered OUTPUT: . END. 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. -of-birth is 1 jan. END IF. Emp_name varchar2(10). BEGIN v_your_age:= TRUNC (MONTHS_BETWEEN(SYSDATE.PUT_LINE ('Your age is ' || v_your_age). 1970. QUERY: Alter table employee Add daily wagers number (10). IF v_your_age>65 THEN DBMS_OUTPUT. Alter table employee add check (daily wagers>=5000).

QUERY: desc emp.13. Display the information of the employees and departments with description of the fields. 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: . desc dept.

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

Commit the changes whenever required and rollback if necessary. select * from emp. QUERY: select * from emp. EXPECTED OUTPUT: No rows selected roll back. EXPECTED OUTPUT: Rollback complete. Select * from emp. EXPECTED OUTPUT: 2 rows deleted.A 100 01-Jan-05 12000 1000 10 Delete 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. EXPECTED OUTPUT: empno ename job mgr hiredate sal comm deptno 100 Pavan Chairman 10 01-Jan-05 32000 10000 10 101 Gayatri P.A 100 01-Jan-05 12000 1000 10 .

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

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

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

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

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

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

'03-jul-1988')/12-60 "retirement year" from dual. what is the salary of all the employees at retirement time. If salary of all the employees is increased by 10% every year.706989 Select 20000+ ((20000*0. 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: .29.4) from dual. EXPECTED OUTPUT: RETIREMENT YEAR ------------------------------ -35.10)*35) from dual OUTPUT: 30. QUERY: select months_between ('19-oct-2012'. Note: MOD(2016.

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

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

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

The schemas are as follows. The Language Management System consists of two tables to store all the information . 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) .  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.

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: .

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

&DOJ.’&design’. Values are inserted.&NOB. Identify what Data has to be persisted in the databases.’&name’.&fine).&expiry. 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) .SQL>Insert into patron values(&patron_id. APSRTC RESERVATION SYSTEM Analyze the problem and come with the entities in it.

Age : integer(4) Sex : char(10) . 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 .

Destination :varchar Dep_time : varchar E-R Diagram .