LAB MANUAL

Database Management System (DBMS)

Execute a single line query and group functions

Execute DDL Commands.

Execute DML Commands.

Execute DCL AND TCL Commands.

Implement the Nested Queries.

Implement Join operations in SQL.

Create views for a particular table.

Implement Locks for a particular Table

Write PL/SQL procedure for an application using exception handling.

Write PL/SQL procedure for an application using exception handling.

Write PL/SQL procedure for an application using cursors.

Write PL/SQL procedure for an application using functions.

Write PL/SQL procedure for an application using package.

 

1. Execute a single line query and group functions.

 

Queries:

 

1.                  Create the table Employee with the following attributes: (Empid number

 

(5), EmployeeName varchar (25), Empsal number (7));

 

2.                  Enter five tuples into the existing table and display.

 

3.                  Execute sum() to get value of numerical column and display.

 

4.                  Execute Avg() to get value of numerical column and display.

 

5.                  Execute Max() to get value of numerical column and display.

 

6.                  Execute Min() to get value of numerical column and display.

 

7.                  Execute count(expression) to get number of records in a particular column and display.

 

8.                  Execute count(*) to get number of rows in all the column and display.

 

Answer:

 

Query1: Create the table Employee with the following attributes: (Empid number (5), EmployeeName varchar (25), Empsal number (7));

 

Command 1: create table Employee(Empid number(5), EmployeeName varchar (25), Empsal number (7));

 

 

 

 

 

 

 

Query2: Enter five tuples into the existing table and display.

 

Command 2:

 

insert into Employee values(1001,’Ramesh’,25000); insert into Employee values(1002,’Tagore’,20000); insert into Employee values(1003,’Bharath’,10000); insert into Employee values(1004,’Prakesh’,5000); insert into Employee values(1005,’Shreedhar’100000);

select*from Employee;

 

 

 

Query3: Execute sum() to get value of numerical column and display.

 

Command 3: select sum (Empsal)from Employee;

 

 

 

 

Query4: Execute Avg() to get value of numerical column and display.

 

Command 4: select Avg(Empsal) from Employee;

 

 

Query5: Execute Max() to get value of numerical column and display.

 

Command 5: select Max(Empsal) from Employee;

 

 

 

 

Query6: Execute Min() to get value of numerical column and display.

 

Command 6: select Min(Empsal) from Employee;

 

 

 

 

Query7: Execute count(expression) to get number of records in a particular column and display.

 

select count (Empsal) from Employee;

 

 

 

Query 8: Execute count(*) to get number of rows in all the columns and display.

 

Command 8: select count(*) from Employee;

2. EXECUTE DDL COMMANDS

 

Queries:

 

1.                  Create the table student with following attributes: (Regno number (10), studentname varchar (25), Totalmarks number (4));

 

2.                  Add a new attribute Avg with datatype as number with size (5,2) to the existing table.

 

3.                  change the size of avg from (5,2) to (7,2).

 

4.                  Rename the table student as scholar.

 

5.                  Enter five tuples into the existing table.

 

6.                  Delete all the records of the table at once.

 

7.                  Drop the existing table scholar.

 

Answer:

 

Query1: Create the table student with following attributes:

 

(Regno number (10), studentname varchar (25), Totalmarks number (4));

 

Command1: Create table student (Regno number (10), studentname varchar (25), Totalmarks number (4));

 

Desc student;

 

 

 

 

 

 

 

Query2: Add a new attribute Avg with datatype as number with size (5,2) to the existing table.

 

Command2: alter table student add (Avg number(5,2));

 

Desc student;

 

 

 

 

 

 

BALLARI Query3: change the size of Avg from (5,2) to (7,2).

 

Command3: alter table student modify (Avg number (7,2)); Desc student;

Query4: Rename the table student as scholar.

 

Command4: Rename student to scholar;

 

Desc scholar;

 

 

 

 

 

Query5: Enter five tuples into the existing table.

 

Command5: insert into scholar values(101,’prashanth’,680,99.71); insert into scholar values(102,’Raju’,690,79.82);

 

insert into scholar values(103,’Ramesh’,700,91.91); insert into scholar values(104,’Suresh’,400,89.72); insert into scholar values(105,’Raghu’,900,321.735);

select*from scholar;

 

 

 

 

 

 

Query6: Delete all the records of the data at once.

 

Command6: truncate table scholar;

 

Select*from scholar;

Query7: Drop the existing table scholar. Command7: drop table scholar;