dbms mba record

7/27/2019 Dbms Mba Record

1/21

MASTER OF BUSINESS ADMINISTRATION

I YEAR

COMPUTER APPLICATION FOR BUSINESS

LAB RECORD

BY

P.SREENIVAS, M.TECH.

1


2/21

ACADEMIC YEAR 2012-2013

EXEPERIMENTS INDEX

SNO EXPERIMEN

T NO

DESCRIPTION

1 1 Emp/dept creation2 2 top 3 earner of company.

3 3 joined the company before 15th of the

month

4 4 working under each manager

5 5 Delete the employee

6 6 count of employees

7 7 primary key

8 8 view

9 9 alter

10 10 Group by

11 11 Nested query

12 12 areas

13 13 update em set

14 14 loop

15 15 Loop 2

16 16 Payroll worksheet

17 17 Students marks sheet

18 18 Inventory worksheet19 19 Library worksheet

20 20 Faculty worksheet

PAGE - 2


3/21

Structured Query Language

SQL (Structured Query Language) is a computer language aimed to store, manipulate, and

query data stored in relational databases. The first commercial relational database was

released by Relational Software

In a simple manner, SQL is a non-procedural, English-like language that processes data in

groups of records rather than one record at a time. Few functions of SQL are:

store data

modify data

retrieve data

modify data

delete data

create tables and other database objects

delete data

SQL (Structure Query Language) is the standard language used for querying Relational

Databases. It allows you to view and change data held within an RDB as well as allowing you

to actually modify the Structure of the database. SQL Uses are listed below.

Query the Database - SQL allows you to easily query data held on the database.

Queries can be very simple and only take up a couple of lines or they can be quite

complex and take up several pages of text.

SQL Commands:

DDL Commands: Commands which allow you to modify the structure of the database

are known as Data Definition Language commands or DDL

DML Commands: Commands that allow you to modify the data held within the

database are referred to as Data Manipulation Language commands or DML

DCL Commands: Commands that are used to control a database, including

administrating privileges and committing of data.

Queries: They are for retrieving data from the database, and they are neither DML nor

DDL.

PAGE - 3


4/21

To start SQL LAB PROCEDURE :

Username: scott

Password: tiger

Commands may be on a single line, or many lines

Different clauses should be placed on separate lines for the sake of readability and

also make use of tabs and indents

SQL Command words cannot be split or abbreviated

SQL commands are not case sensitive

All commands entered at the SQL prompt are saved into a command buffer

Execution of SQL Command can be done in no.of ways.

o Place a (;) Semicolon at the end of last clause.

o Place a forward slash (/) at the SQL Prompt

PAGE - 4


5/21

SQL EXPERIMENTS

EXP -1 :

1. Dept (deptno, dname, loc)2. Emp (empno, ename, job, mgr, hiredate, sal, comm., deptno)

1. Creating DEPT table:

SQL> CREATE TABLE dept(

deptno NUMBER(2) PRIMARY KEY,

dname VARCHAR2(14),

loc VARCHAR2(13));

Table created

Enter values in the Table:

SQL> INSERT INTO dept VALUES(10,Accounting,new york);

SQL> SELECT * FROM dept;

2. Creating EMP table:

SQL> CREATE TABLE emp(

empno NUMBER(4) PRIMARY KEY,

ename VARCHAR2(10),

job VARCHAR2(9),

mgr NUMBER(4),hiredate DATE,

sal NUMBER(7,2),

comm. NUMBER(7,2));

Table created


SQL> INSERT INTO emp VALUES(7369,Smith,clerk,7902,17-dec-80,800,

,20);

PAGE - 5


6/21

1 row created

SQL> SELECT * FROM emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- --------- ---- -------------- -------- ---------------- ------- --------- ---------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 307521 WARD SALESMAN 7698 22-FEB-81 1250 500 307566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

1. Creating EMP table:

EXP 2:



ename VARCHAR2(10),

job VARCHAR2(9),

mgr NUMBER(4),

hiredate DATE,

sal NUMBER(7,2),

comm. NUMBER(7,2));

Table created


SQL> INSERT INTO emp VALUES (&empno,&ename,&job,&mgr,&hiredate,&sal,

&comm.);

1 row created

Continue by using / key :

1. Find out the details of top 3 earner of company.

SQL> select * from emp where sal in(select max(sal) from emp ;

PAGE - 6


7/21

EXP 3 :

Creating EMP table:



ename VARCHAR2(10),

job VARCHAR2(9),

mgr NUMBER(4),

hiredate DATE,

sal NUMBER(7,2),

comm. NUMBER(7,2));



&comm.);

1 row created


2. Display those employees who joined the company before 15th of the month?

SQL> select hiredate from emp where hiredate in(

select hiredate from emp where TO_CHAR(hiredate,'dd')


8/21

EXP 4:



ename VARCHAR2(10),

job VARCHAR2(9),

mgr NUMBER(4),hiredate DATE,

sal NUMBER(7,2),

comm. NUMBER(7,2));


&comm.);

1 row created


Display the number of employees working under each manager?

SQL> select count(empno) from emp group by mgr;

COUNT(EMPNO)

------------25

11

31

1

3. Update the employee salary by 15%, whose experience is greater than 20 years.

SQL> update emp set sal=sal+((15/100)*sal) where experience>20;

PAGE - 8


9/21

EXP 5:



ename VARCHAR2(10),job VARCHAR2(9),

mgr NUMBER(4),

hiredate DATE,

sal NUMBER(7,2),

comm. NUMBER(7,2));


&comm.);

1 row created


Delete the employee, who completed 30 years of service.

SQL> delete from emp where experience=30;

EXP - 6

Determine the count of employees, who are taking commission.



ename VARCHAR2(10),job VARCHAR2(9),

mgr NUMBER(4),

hiredate DATE,

sal NUMBER(7,2),

comm. NUMBER(7,2));

PAGE - 9


10/21


&comm.);

1 row created


SQL> select count(empno) from emp where comm>1;

COUNT(EMPNO)

------------

3

EXP 7

1. create table cust2(cno varchar2(5) primary key,

cname varchar2(10),

dob date,

occu varchar2(5),

bal number(6));

2. SQL> desc cust1;


&comm.);

1 row created


4. select * from cust1;

PAGE - 10


11/21

EXP 8

SQL> create table emp10(eno number(5),2 bno number(5),

3 name varchar2(5),dept varchar2(5),

4 des varchar2(5),

5 mno varchar2(5));


&comm.);

1 row created


SQL> create view vemp as select name,des from emp10;

SQL> select * from vemp;

EXP 9

Sql>alter table cust1 drop column occu;

SQL>alter table cust1 add adr varchar2(5);

SQL> alter table cust1 modify adr varchar2(5);

PAGE - 11


12/21

EXP 10

GROUP BY EXAMPLE

1. create table master(bno varchar2(5),eno varchar2(5));

insert into master values('&bno','&eno');

select bno "branch no" , count(eno) " no of emp" from master group by bno;

2. create table gropp(cno varchar2(5), acno varchar2(5));

insert into gropp values('&cno','&acno');

SQL> select * from gropp;

CNO ACNO

----- -----

c1 sb

c1 ca

c2 sb

c3 sb

s2 sb

SQL> select cno,count(acno)

from gropp

where

acno like 'sb%'

or

acno like 'ca%' group by cno having count(acno)>1;

CNO COUNT(ACNO)

----- -----------

c1 2

PAGE - 12


13/21

EXP 11

create table cmast (cno varchar2(5),name varchar2(10));

create table adres (cdno varchar2(5),city varchar2(10),state varchar2(10));

SQL> desc cmast;

Name Null? Type

----------------------------------------- -------- ----------------------------

CNO VARCHAR2(5)

NAME VARCHAR2(10)

SQL> desc adres;

Name Null? Type----------------------------------------- -------- ----------------------------

CDNO VARCHAR2(5)

CITY VARCHAR2(10)

STATE VARCHAR2(10)

SQL> select * from cmast;

CNO NAME

----- ----------

a1 rajc2 mohan

SQL> select * from adres;

CDNO CITY STATE

----- ---------- ----------

a1 hyd ap

c2 vizag ap

SQL> select cdno, city

2 from adres

3 where cdno in(select cno from cmast

4 where

5 name='raj');

CDNO CITY

----- ----------

a1 hyd

PAGE - 13


14/21

PL/SQL EXPERIMENTS

PAGE - 14


15/21

PL/SQL is Oracles procedural language extension to SQL. PL/SQL allows you to mix SQL

statements with procedural statements like IF statement, Looping structures etc. PL/SQL is

the superset of SQL. It uses SQL for data retrieval and manipulation and uses its own

statements for data processing. PL/SQL program units are generally categorized as follows:

Anonymous blocks

Stored procedures

Anonymous block

This is a PL/SQL block that appears within your application. In many applications PL/SQL

blocks can appear where SQL statements can appear. Such blocks are called as Anonymous

blocks.

Stored Procedure

This is a PL/SQL block that is stored in the database with a name. Application programs can

execute these procedures using the name. Oracle also allows you to create functions, which

are same as procedures but return a value, and packages, which are a collection of procedures

and functions.

PL/SQL Engine

Every PL/SQL block is first executed by PL/SQL engine. This is the engine that compiles

and executes PL/SQL blocks. PL/SQL engine is available in Oracle Server and certain Oracle

tools such as Oracle Forms and Oracle Reports. PL/SQL engine executes all procedural

statements of a PL/SQL of the block, but sends SQL command to SQL statements executor in

the Oracle RDBMS. That means PL/SQL separates SQL commands from PL/SQL commands

and executes PL/SQL commands using Procedural statement executor, which is a part of

PL/SQL engine.

Block structure

PL/SQL is a block-structured language. Each program written in PL/SQL is written as a

block. Blocks can also be nested. Each block is meant for a particular task.

PAGE - 15


16/21

Variables and constants

PL/SQL allows you to declare variables and constants. Variables are used to store values

temporarily. Variables and constants can be used in SQL and PL/SQL procedural statements

just like an expression.

Control structures

PL/SQL allows control structures like IF statement, FOR loop, WHILE loop to be used in the

block. Control structures are most important extension to SQL in PL/SQL. Control structures

allow any data process possible in PL/SQL.

Exception handling

PL/SQL allows errors, called as exceptions, to be detected and handled. Whenever there is a

predefined error PL/SQL raises an exception automatically. These exceptions can be handled

to recover from errors.

Modularity

PL/SQL allows process to be divided into different modules. Subprograms called as

procedures and functions can be defined and invoked using the name. These subprograms can

also take parameters.

Cursors

A cursor is a private SQL area used to execute SQL statements and store processing

information. PL/SQL implicitly uses cursors for all DML commands and SELECT command

that returns only one row. And it also allows you to define explicit cursor to deal with

multiple row queries.

Built-in functions

Most of the SQL functions that we have seen so far in SQL are available in PL/SQL. These

functions can be used to manipulate variables of PL/SQL.

PAGE - 16


17/21

BLOCK STRUCTURE OF PL/SQL :

[DECLAREdeclaration of variable

declaration of cursor

declaration of exception ]

BEGIN

executable commands

[EXCEPTION

exception handlers]

END;

PAGE - 17


18/21

EXP 12

1.

create table areas(radius number(5), area number(14,2));

declare

pi constant number(4,2) :=3.14;

radius number(5);

area number(14,2);

begin

radius := 3;

while radius


19/21

EXP 14

3.

declarei number := 0;

begin

loop

i := i + 2;

exit when i>10;

end loop;

dbms_output.put_line('loop exited when i reached' || to_char(i));

end;

EXP 15

4. declare

i number := 0;

begin

loop

i := i + 2;

exit when i > 10;

end loop;

dbms_output.put_line('LOOP EXIT WHEN I reached ' || to_char(i));

end;

PAGE - 19


20/21

MS-EXCEL EXPERIMENTS

EXP 16 :

CREATE PAYROLL WORKSHEET :

EMP NO, EMP NAME, BP,HRA,DA,LIC,PF, GROSS,DEDUCTION,NET

EXP 17 :

CREATE STUDENTS WORKSHEET :

ROLL.NO., NAME, SUB1, SUB2, SUB3, SUB4, SUB5, TOTAL, AVERAGE

EXP 18 :

CREATE INVENTORY WORKSHEET :

INO, INAME, COST, QUANTITY, TOTAL VALUE

EXP 19 :

CREATE LIBRARY WORKSHEET :

BNO, BANEM, AUTHOR, COST

EXP -20 :

CREATE FACULTY WORKSHEET :

FNO, FNAME , SALARY, QUALIFICATION

PAGE - 20


21/21

QUESTION PAPER COMPUTER APPLICATION FOR BUSINESS

1. Emp/dept creation & Payroll worksheet.

2.

dbms mba record

Documents