List of Practical

Sr. No Practical
1 Introducation to MYSQL
Draw E-R diagram and convert entities and relationships to relation table for a given
2 scenario.Two assignments shall be carried out i.e. consider two different scenarios
(Student)( library management).
To use the data manipulation language for inserting, selecting, updating and deleting the
3 data in the database table. Ex. display employee’s last name, first name, and phone
number from the Employees table.
To use Order By, Group By and Having Clause in a database.
4 Ex. SELECT Column_name, aggregate_function(column_name) FROM table_name
WHERE column_name operator value GROUP BY column_name;
Perform the following:
a. Viewing all databases, Creating a Database,
b. Viewing all Tables in a Database, Creating Tables (With and Without
5
Constraints),
c. Inserting/Updating/Deleting Records in a Table, Saving (Commit) and Undoing
(rollback)
Perform the following:
6 a. Altering a Table, Dropping/Truncating/Renaming Tables, Backing up / Restoring a
Database.
For a given set of relation tables perform the following
a. Creating Views ,
7
b. Dropping views,
c. Selecting from a view
For a given set of relation schemes, create tables and perform the following
a. Simple Queries with Aggregate functions,
8
b. Queries with Aggregate functions (group by and having clause),
c. Queries involving- Date Functions, String Functions , Math Functions.

9 Write a Pl/SQL program using FOR loop to insert ten rows into a database table.

Given the table EMPLOYEE (EmpNo, Name, Salary, Designation, DeptID) write a
10
cursor to select the five highest paid employees from the table
Additional Practical’s
Introduction and basic operations of Python Language i).Arithmetic Operators
11
ii).Comparison Operators iii).Logical Operators iv).Bitwise Operators
Basic commands of R Language i)Relational Operators ii) Assignment Operators iii)
12
Miscellaneous Operator.
Open Ended Practical’s
Illustrate how you can embed PL/SQL in a high-level host language such as C/Java
13
And demonstrates how a banking debit transaction might be done.
14 Export SQL data into Orange Data Mining Tools.
 Practical No. 01

1. Aim: Introducation to MYSQL

2. Objective: - Learn to understand implementation of insertion sort on random numbers.

3. Theory:-

SQL (Structured Query Language) is a database query language designed for the storing and
management of data in RDBMS, database schema creation, editing and manipulating. SQL is used to
manage tuple and domain access control within a RDBMS, and database SQL was introduced by Dr.
E.F. Codd.SQL is a non procedural programming language for querying and modifying data and
managing databases. SQL allows the accessing, insertion, updating, and deletion of data.
A database management system also includes database management and database administrative
functions. SQL uses command line interface. SQL is the general language used to interact with ralational
database management systems.
SQL have two forms of languages DDL and DML.
Advantages of SQL :

 This allows users to retrieve data in relational database management.

 This allows users to explain the data.
 This allows to define and manipulate the data.
 This allows to create and delete databases and a tables.
 This allows users to create view and set permissions on tables.

HISTORY OF SQL :
1970-The first version of SQL was developed at IBM by Donald D. Chamberlin and
Raymond F. Boyce in the early 1970s. This version, initially called SEQUEL, was
designed to manipulate and retrieve data stored in IBM's original relational database
product.
1974-Structured query language came into existance.
1978- System/R released following Codd’s rules.
1986-, IBM developed the first prototype of relational database and standardized by the
American National Standards Institute (ANSI) as SQL-86.
SQL PROCESS:
While executing the SQL Command the following components play an important role-

 Query Dispatcher
 Optimization Engines
 Classic Query Language
 SQL Query Engine, etc

Common criticisms of SQL include a perceived lack of cross-platform portability between

vendors, inappropriate handling of missing data (see Null (SQL)), and unnecessarily complex and
occasionally ambiguous language grammar and semantics.

FEATURES OF SQL:
SQL is both an easy-to-understand language and a comprehensive tool for managing data. Some of
the major features of SQL are
 Vendor independence
 Portability across computer systems
 SQL standards
 High-level, English-like structure
 Interactive, ad hoc queries
 Programmatic database access
 Multiple views of data
 Complete database language
 Dynamic data definition
 Client/server architecture
 Enterprise application support
 Extensibility and object technology
 Internet database access
 Java integration (JDBC)
To start MySQL you would:
1. Select the Start button
2. Select All Programs and then MySQL
3. Select MySQL Server 5.0
4. Click on the MySQL Command line client

Once you have successfully logged on you will see the opening screen as shown in Figure 2. To work in
MySQL requires you to type in commands. For example typing in the following will show you a list of
help commands shown in Figure 3:
mysql> \h
CONCLUSION
Thus we have studied and understood SQL features.

Viva Voce Questions

 What is SQL?
 What are the features of SQL?
 What are the various SQL languages

Practical No. 02
Aim: Draw E-R diagram and convert entities and relationships to relation table for a given scenario.Two
assignments shall be carried out i.e. consider two different scenarios (Student)( library management).

Objective: - Learn to understand the E-R diagram.

Theory:

ER Diagram is known as Entity-Relationship Diagram, it is used to analyze the structure of the

Database. It shows relationships between entities and their attributes. An ER Model provides a means of
communication.

Entity type becomes a table.

In the given ER diagram, LECTURE, STUDENT, SUBJECT and COURSE forms individual tables.

All single-valued attribute becomes a column for the table.

In the STUDENT entity, STUDENT_NAME and STUDENT_ID form the column of STUDENT table.
Similarly, COURSE_NAME and COURSE_ID form the column of COURSE table and so on.

A key attribute of the entity type represented by the primary key.

In the given ER diagram, COURSE_ID, STUDENT_ID, SUBJECT_ID, and LECTURE_ID are the key
attribute of the entity.

The multivalued attribute is represented by a separate table.

In the student table, a hobby is a multivalued attribute. So it is not possible to represent multiple values in
a single column of STUDENT table. Hence we create a table STUD_HOBBY with column name
STUDENT_ID and HOBBY. Using both the column, we create a composite key.

Composite attribute represented by components.

In the given ER diagram, student address is a composite attribute. It contains CITY, PIN, DOOR#,
STREET, and STATE. In the STUDENT table, these attributes can merge as an individual column.

Derived attributes are not considered in the table.

In the STUDENT table, Age is the derived attribute. It can be calculated at any point of time by
calculating the difference between current date and Date of Birth.

ER Diagram of Student

The Library Management System database keeps track of readers with the following considerations –
  The system keeps track of the staff with a single point authentication system comprising login Id
and password.
 Staff maintains the book catalog with its ISBN, Book title, price(in INR), category(novel, general,
story), edition, author Number and details.
 A publisher has publisher Id, Year when the book was published, and name of the book.
 Readers are registered with their user_id, email, name (first name, last name), Phone no (multiple
entries allowed), communication address. The staff keeps track of readers.
 Readers can return/reserve books that stamps with issue date and return date. If not returned
within the prescribed time period, it may have a due date too.
 Staff also generate reports that has readers id, registration no of report, book no and return/issue
info.

Library ER diagram illustrates key information about the Library, including entities such as staff, readers,
books, publishers, reports, and authentication system. It allows for understanding the relationships
between entities.

Entities and their Attributes –

 Book Entity : It has authno, isbn number, title, edition, category, price. ISBN is the Primary Key
for Book Entity.
 Reader Entity : It has UserId, Email, address, phone no, name. Name is composite attribute of
firstname and lastname. Phone no is multi valued attribute. UserId is the Primary Key for Readers
entity.
 Publisher Entity : It has PublisherId, Year of publication, name. PublisherID is the Primary Key.
 Authentication System Entity : It has LoginId and password with LoginID as Primary Key.
 Reports Entity : It has UserId, Reg_no, Book_no, Issue/Return date. Reg_no is the Primary Key of
reports entity.
 Staff Entity : It has name and staff_id with staff_id as Primary Key.
 Reserve/Return Relationship Set : It has three attributes: Reserve date, Due date, Return date.

Relationships between Entities –

 A reader can reserve N books but one book can be reserved by only one reader. The relationship
1:N.
 A publisher can publish many books but a book is published by only one publisher. The
relationship 1:N.
 Staff keeps track of readers. The relationship is M:N.
 Staff maintains multiple reports. The relationship 1:N.
 Staff maintains multiple Books. The relationship 1:N.
 Authentication system provides login to multiple staffs. The relation is 1:N.
 Library Management System

Conclusion: Thus we have studied and E-R diagram.

Viva Voce Questions

1. Define ER diagram
2. What is Derived, Composite and Multivalued Attribute?
3. What is relationship? Give examples
4. Define weak and strong entity
 Practical No. 03

Aim: - To use the data manipulation language for inserting, selecting, updating and deleting the data in the
database table. Ex. display employee’s last name, first name, and phone number from the Employees table,.

Objective: - To understand the structure of databases, To create, alter and drop the tables.

Theory:-

Structured query language:-

SQL is a structured query language used for retrieving data in a relational database.

DDL- Data Definition Language:-

DDL commands are the SQL commands you use to create, alter, remove different database objects in an
MySQL/ORACLE database.

Table Definition:-
A database object is something created and sorted in databases. Tables, views, synonyms indexes,
sequences, clusters are all types of database objects.
A table is a unit of the storage that holds data in the form of rows and columns.
1. CREATE TABLE
2. ALTER TABLE
3. DROP TABLE
4. TRUNCATE TABLE

Create table command:-is used to create the database, tables, views, procedures and triggers.
Syntax
Create table < table_name>
( c1 datatype(size),
c2 datatype(size),
: :
cn datatype(size));

Ex CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,
LastName VARCHAR(50),
FirstName VARCHAR(50),
PhoneNumber VARCHAR(15)
);

OUTPUT:
The above SQL statement will create a table STUD_ID with the given columns to view the
structure of the table created use the DESCRIBE COMMAND.

The result of the command is to see the column names and data types.
Syntax:-
DESC <table_name>;
e.g.
DESC STUD_INFO;
OUTPUT :
Restrictions for creating a table:
1. Always start table name and column name with a letter
2. Table names and column names can be 1 to 30 characters long
3. Table names must contain only the characters A-Z, a-z,0-9,underscore
_,$ and #.
4. Table name should be unique
5. Table name must not be an ORACLE reserved word
6. Column name should be unique

Alter table command:

Syntax:-
Case 1: alter table <table_name>
add (c1 datatype, c2 datatype,
: :
: :
cn _namen datatype);

Case 2: alter table<table_name>

modify (c1 datatype, c2 datatype,

: :
cn _namen datatype);
After you create a table, you may need to change the table structures once you omitted a column or your
column definition needs to be changed. Using the ALTER TABLE statement you can do it.
You can add columns to a table using the ALTER TABLE statement with the ADD clause.
e.g
To add a column ADDRESS2 to the table STUDENT_INFO.
Alter table Employees Add(Address varchar(20));

OUTPUT :
MODIFY Clause is used with the ALTER TABLE statement.

e.g To modify the length of the FirstName field to 60 in the Employees table.

Alter table Employees modify (FirstName VARCHAR(60));

OUTPUT :

Result of the above commands can be seen by describing the table.

Rename Table
To rename a table name
To change the name of the table Employees to Employee_Details
Syntax;-
Rename old_table_name to New_table_name;
e.g.
Rename Employees to Employee_Details;

Drop table command

The drop table command removes the definition(structure) of an Oracle table. When drop table command
is used , the database loses all the data in the table and all the indexes
concerned with it.

Syntax:-
 drop table <table_name>;
e.g.
drop table Employees;

5. Conclusion: Thus we have studied and performed the above mysql commands.

6. Viva –Voce Questions:-

Viva Voce Questions

1. Write DDL Commands.
2. What is difference between Drop Delete and Truncate?
3. What is the use of alter command?
4. Write a Syntax to create table ?
 Practical No. 04

Aim: -. To use Order By, Group By and Having Clause in a database.

Ex. SELECT Column_name, aggregate_function(column_name)

FROM table_name WHERE column_name operator value GROUP BY column_name;

Objective: - Sort the data in the resulting query.Apply SQL aggregate functions

Theory:-

Sorting of Oracle table data

When you execute a simple query then the order of rows is undefined. To prevent this from happening, the
ORDRE BY clause is used.

Having clause:-
The HAVING clause can be used to restrict groups from being displayed.

Grouping rows in a table.

The rows of data in a oracle table can be divided into groups by using the GROUP BY clause.

ORDER BY clause:-
Syntax:-
select <expr>
from <table_name>
[where condition(s)]
[order by {col, expr} [asc/ desc]];
Where
order by- specifies the order in which the rows are displayed.
asc - orders the rows in ascending order.
desc – orders the rows in descending order
e.g.
select SNAME,DOA,BRANCH,DEPTNO
from STUDENT_INFO
order by DOA desc;
The above example sorts the result by the students date of admission.

OUTPUT :
GROUP BY clause.
Syntax:-
select column, group_function
from <table_name>
[where condition]
[group by group_by_exp]
[order by col];
Where group by expression - specifies columns whose values determine the basis for grouping rows.
The GROUP BY clause can be used to divided the rows in a table into groups. We can then use the
group functions to return summary information for each group.
The GROUP BY column does not have to be in SELECT list.
In the above e.g. GROUOP BY column deptno is not in the GROUP BY clause on
multiple columns.

select DEPTNO, AVG(FEES)

from STUDENT_INFO
group by DEPTNO;

OUTPUT :

HAVING clause:-
Syntax:-
select col, group_function
from <table_name>
[ group by group_by_exp]
[ having group_condition]
[order by col];

Where
Group condition- Restricts the groups of rows returned to those groups for which the specified condition is
TRUE.
We use the HAVING clause to specify which groups are displayed.

e.g.
select DEPTNO, avg(FEES)
from STUDENT_INFO
group by DEPTNO
having max(FEES) > 10000;

OUTPUT :

CONCLUSION: Thus we have studied and performed the above mysql commands.

Viva Question
1. Define the terms i) DDL ii) DML

2. What is the use of GROUP BY and Having clause ?

3. What is a SELECT operation?

.
 Practical No. 05

Aim: -. Perform the following:

a. Viewing all databases, Creating a Database,
b. Viewing all Tables in a Database, Creating Tables (With and Without Constraints),
c. Inserting Updating/Deleting Records in a Table, Saving (Commit) and Undoing (rollback)

Objective: - Understand the sql command inserting and deleting, updating.

Theory:

Creating a Database

CREATE DATABASE Company;

1. Viewing all databases SHOW DATABASES;

2. Viewing all Tables in a Database, SHOW tables;

3. Creating Tables (With and Without Constraints) CREATE TABLE DEPARTMENT

(DNO VARCHAR2 (20) PRIMARY KEY, DNAME VARCHAR2 (20), MGRSTARTDATE
DATE);

CREATE TABLE EMPLOYEE

(SSN VARCHAR2 (20) PRIMARY KEY, FNAME VARCHAR2 (20),
LNAME VARCHAR2 (20),
ADDRESS VARCHAR2 (20),
SALARY INTEGER,
SUPERSSN REFERENCES EMPLOYEE (SSN), DNO REFERENCES DEPARTMENT
(DNO)

NOTE: Once DEPARTMENT and EMPLOYEE tables are created we must alter department table to add
foreign constraint MGRSSN using sql command

ALTER TABLE DEPARTMENT

ADD MGRSSN REFERENCES EMPLOYEE (SSN);

4. Inserting/Updating/Deleting Records in a Table,

INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SALARY) VALUES

(‗RNSECE01‘,‘JOHN‘,‘SCOTT‘,‘BANGALORE‘, 450000); INSERT INTO EMPLOYEE (SSN,
FNAME, LNAME, ADDRESS, SALARY) VALUES
(‗RNSCSE01‘,‘JAMES‘,‘SMITH‘,‘BANGALORE‘, 500000); INSERT INTO EMPLOYEE (SSN,
FNAME, LNAME, ADDRESS, SALARY) VALUES
(‗RNSCSE02‘,‘HEARN‘,‘BAKER‘,‘BANGALORE‘, 700000); INSERT INTO EMPLOYEE (SSN,
FNAME, LNAME, ADDRESS, SALARY) VALUES
(‗RNSCSE03‘,‘EDWARD‘,‘SCOTT‘,‘MYSORE‘, 500000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SALARY) VALUES
(‗RNSCSE04‘,‘PAVAN‘,‘HEGDE‘,‘MANGALORE‘, 650000); INSERT INTO EMPLOYEE
 (SSN, FNAME, LNAME, ADDRESS, SALARY) VALUES
(‗RNSCSE05‘,‘GIRISH‘,‘MALYA‘,‘MYSORE‘, 450000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SALARY) VALUES
(‗RNSCSE06‘,‘NEHA‘,‘SN‘,‘BANGALORE‘‘, 800000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SALARY) VALUES
(‗RNSACC01‘,‘AHANA‘,‘K‘,‘MANGALORE‘, 350000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SALARY) VALUES
(‗RNSACC02‘,‘SANTHOSH‘,‘KUMAR‘,‘MANGALORE‘, 300000); INSERT INTO EMPLOYEE
(SSN, FNAME, LNAME, ADDRESS,SALARY) VALUES (‗RNSISE01‘,‘VEENA‘,‘M‘,‘MYSORE‘,
600000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS,SALARY) VALUES
(‗RNSIT01‘,‘NAGESH‘,‘HR‘,‘BANGALORE‘, 500000);

INSERT INTO DEPARTMENT VALUES (‗1‘,‘ACCOUNTS‘,‘01-JAN- 01‘,‘RNSACC02‘);

INSERT INTO DEPARTMENT VALUES (‗2‘,‘IT‘,‘01-AUG-16‘,‘RNSIT01‘);

INSERT INTO DEPARTMENT VALUES (‗3‘,‘ECE‘,‘01-JUN-08‘,‘RNSECE01‘); INSERT INTO

DEPARTMENT VALUES (‗4‘,‘ISE‘,‘01-AUG-15‘,‘RNSISE01‘); INSERT INTO DEPARTMENT
VALUES (‗5‘,‘CSE‘,‘01-JUN-02‘,‘RNSCSE05‘);

Update

UPDATE EMPLOYEE SET DNO=‘5‘, SUPERSSN=‘RNSCSE06‘ WHERE SSN=‘RNSCSE05‘;

Delete entries of employee table where DNO =1; DELETE FROM EMPLOYEE WHERE DNO=1;

COMMIT and ROLLBACK

Before concluding this section on Data Manipulation Language commands there are two further
commands, which are very useful. Changes made to the database by INSERT, UPDATE and DELETE
commands are temporary until explicitly committed. This is performed by the command:

COMMIT;
On execution of this command all changes to the database made by you are made permanent and cannot be
undone.
 A COMMIT is automatically executed when you exit normally from SQL*Plus. However, it does
no harm to occasionally issue a COMMIT command.
 A COMMIT does not apply to any SELECT commands as there is nothing to commit.
 A COMMIT does not apply to any DDL commands (eg CREATE TABLE, CREATE INDEX, etc).
These are automatically committed and cannot be rolled back.
 If you wished to rollback (ie undo) any changes made to the database since the last commit, you can
issue the command:

ROLLBACK;

A group of related SQL commands that all have to complete successfully or otherwise be rolled back, is
called a transaction. Part of your research for Outcome 3 includes investigating transaction processing and
the implications of rollback and commit.
CONCLUSION: Thus we have performed the above command of my sql.

Viva Question

1. What is Commit, Rollback,

2. What is Functional Dependency?
3. What is Multivalued dependency?
 Practical No. 07

Aim: -. For a given set of relation tables perform the following

a. Creating Views (with and without check option),
b. Dropping views,
c. Selecting from a view

Objective: - Understand the importance of view and its implimentation.

Perform the Following

1. Creating Views (With and Without Check Option),
2. Selecting from a View
3. Dropping Views,

Theory: - Views in SQL are kind of virtual tables. A view also has rows and columns as they are in a
real table in the database. We can create a view by selecting fields from one or more tables present in the
database. A View can either have all the rows of a table or specific rows based on certain condition. In
this article we will learn about creating , deleting and updating Views.

CREATE VIEW view_name AS

SELECT column1, column2.....
FROM table_name
WHERE condition;

view_name: Name for the View

table_name: Name of the table
condition: Condition to select rows

SELECT * FROM view name;

SQL> CREATE TABLE EMPLOYEE (
SSN VARCHAR2 (20) PRIMARY KEY, FNAME VARCHAR2 (20),
LNAME VARCHAR2 (20),
ADDRESS VARCHAR2 (20),
SEX CHAR (1), SALARY INTEGER,
SUPERSSN REFERENCES EMPLOYEE (SSN), DNO REFERENCES DEPARTMENT (DNO));

SQL> DESC EMPLOYEE;

Name Null? Type

SSN NOT NULL VARCHAR2(20)

FNAME VARCHAR2(20)
LNAME VARCHAR2(20)
ADDRESS VARCHAR2(20)
SEX CHAR(1)
SALARY NUMBER(38)
SUPERSSN VARCHAR2(20)
 DNO NUMBER(38)

INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘RNSECE01‘,‘JOHN‘,‘SCOTT‘,‘BANGALORE‘,‘M‘, 450000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘RNSCSE01‘,‘JAMES‘,‘SMITH‘,‘BANGALORE‘,‘M‘, 500000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘RNSCSE02‘,‘HEARN‘,‘BAKER‘,‘BANGALORE‘,‘M‘, 700000); INSERT INTO
EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘RNSCSE03‘,‘EDWARD‘,‘SCOTT‘,‘MYSORE‘,‘M‘, 500000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘RNSCSE04‘,‘PAVAN‘,‘HEGDE‘,‘MANGALORE‘,‘M‘, 650000); INSERT INTO
EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘RNSCSE05‘,‘GIRISH‘,‘MALYA‘,‘MYSORE‘,‘M‘, 450000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘RNSCSE06‘,‘NEHA‘,‘SN‘,‘BANGALORE‘,‘F‘, 800000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘RNSACC01‘,‘AHANA‘,‘K‘,‘MANGALORE‘,‘F‘, 350000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘RNSACC02‘,‘SANTHOSH‘,‘KUMAR‘,‘MANGALORE‘,‘M‘, 300000); INSERT INTO
EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘RNSISE01‘,‘VEENA‘,‘M‘,‘MYSORE‘,‘M‘, 600000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‘RNSIT01‘,‘NAGESH‘,‘HR‘,‘BANGALORE‘,‘M‘, 500000);

Creating View

The query that defines the sales_staffview references only rows in department 5. Furthermore,
the CHECK OPTION creates the view with the constraint (named sales_staff_cnst) that INSERT
and UPDATE statements issued against the view cannot result in rows that the view cannot
select.

1. Creating Views (With and Without Check Option)

SQL> CREATE VIEW sales_staff AS

2 SELECT fname, ssn, dno
3 FROM employee
4 WHERE dno =5
5 WITH CHECK OPTION CONSTRAINT sales_staff_cnst; View created.

2. Selecting from a View

SQL> select * from sales_staff;

3. Drop View

SQL>DROP VIEW sales_staff;

5. CONCLUSION: Thus we have performed the view command of my sql.

6. Viva Question
1. What is VDL (View Definition Language)?
2. What is SDL (Storage Definition Language)?
 Practical No. 08

1. Aim: - For a given set of relation schemes, create tables and perform the following
Simple Queries, Simple Queries with Aggregate functions, Queries with Aggregate
functions (group by and having clause), Queries involving- Date Functions, String
Functions , Math Functions
2. Objective: Learn & understand sql command.

3. Theory
A group function returns a result based on a group of rows.
The group functions are listed below :
AVG
SUM
MIN
MAX
COUNT
AVG -
This command returns the average value of the specified column of number data
type .
Format:-
avg (col_name)
e.g. select avg(FEES) from STUDENT_INFO;

OUTPUT :

2. SUM -
This command returns the summation of the specified column of number
data type .
Format:-
sum (col_name)
e.g. select sum(FEES) from STUDENT_INFO;
OUTPUT :

3. MIN -
This command returns the lowest value from the specified column of number data type .
Format:-
min (col_name)
e.g. select min(FEES) from STUDENT_INFO;

OUTPUT :
4. MAX-

This command returns the highest value from the specified column of number data type .

Format:-
max (col_name)
e.g. select max (FEES) from STUDENT_INFO;
OUTPUT

5. COUNT -
It is used to count the number of rows .
COUNT (*) – It counts all rows , inclusive of duplicate and nulls.
Format:-
count (*)
e.g. select count ( * ) from STUDENT_INFO;
OUTPUT :

COUNT (col_name)
It is used to count the number of values present in the specified column without
including nulls.

Format:-
count (col_name)
e.g. select count (comm.) from emp;
OUTPUT :

5.COUNT ( distinct col_name)

It is used to eliminate the duplicate and null values in the specified column.
Format:-
count(distinct col_name)
e.g.
select count (distinct DEPT_NO) from STUD_INFO;
OUTPUT :
 SQL> select ascii('t') from dual; ASCII('T')

116

SQL> select ascii('A') from dual; ASCII('A')

65

SQL> SELECT UPPER('bldea sb arts and kcp science college') from dual;

UPPER('BLDEASBARTSANDKCPSCIENCECOLLEG

BLDEA SB ARTS AND KCP SCIENCE COLLEGE

SQL> select LOWER('welcome to dbms lab') from dual; LOWER('WELCOMETODBM

welcome to dbms lab

SQL> SELECT CURRENT_DATE FROM DUAL;

CURRENT_D
14-AUG-19

SQL> SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL; EXTRACT(YEARFROMSYSDATE)

2019

SQL> SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL; EXTRACT(MONTHFROMSYSDATE)

8

5. CONCLUSION: Thus we have performed aggregate , mathematical and date function of my SQL

6. Viva Question
1. What is 4NF?
2. What is 5NF?
 Practical No. 09

Aim: - Write a Pl/SQL program using FOR loop to insert ten rows into a database table.

Objective: Learn & understand implementation of PI/SQL program.

Program :

SOLUTION:

PL/SQL Block

SET SERVEROUTPUT ON SIZE 1000000;

DECLARE

n_times NUMBER := 10;

BEGIN

FOR n_i IN 1..n_times LOOP

DBMS_OUTPUT.PUT_LINE(n_i);

END LOOP;

END;

Output Table
CONCLUSION: Thus we have performed and implemented the PI/SQL program.

Viva Question

1. What is extension and intension?

2. What is Data Independence?
 Practical No. 10

Aim: - Given the table EMPLOYEE (EmpNo, Name, Salary, Designation, DeptID) write a cursor to
select the five highest paid employees from the table..

Objective: Learn and understand the program of cursor.

Program

CREATE TABLE EMPLOYEE (EMPNO INTEGER PRIMARY KEY, NAME VARCHAR(20),

SALARY NUMBER(7,2), DESIGNATION VARCHAR(10), DEPTID INTEGER);

get e:/p8.sql; 1 declare

2 i number:=0;
3 cursor ec is select empno,name,salary from employee order by gross_salary desc; 4 r ec
%rowtype;
5 begin
6 open ec;
7 loop
8 exit when i=5; 9 fetch ec into r;
10 dbms_output.put_line(r.emp_no||' '||r.employee_name||' '||r.salary); 11 i:=i+1;
12 end loop;
13 close ec; 14* end; 15 .

SQL> /

1 rajesh 31000

2 paramesh 15000

3 pushpa 14000

4 vijaya 14000

5 keerthi 13000

PL/SQL procedure successfully completed.

Conclusion: Thus we have performed and implemented the PI/SQL program.

Viva –Voce Questions:-

1. Disadvantage in File Processing System?
2. Describe the three levels of data abstraction?