PERI INSTITUTE OF TECHNOLOGY

DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

CS3481- DATABASE MANAGEMENT SYSTEMS LABORATORY

2021- REGULATIONS

BE CSE IV SEMESTER
 PERI Institute of Technology
Department of Computer Science and Engineering

VISION AND MISSION OF THE INSTITUTION

Vision:

PERI Institute of Technology visualizes growing in future to an internationally recognized

seat of higher learning in engineering, technology & science. It also visualizes being a research
incubator for academicians, industrialists and researchers from across the world, enabling them to
work in an environment with the sophisticated and state of the art equipment and amenities provided
at the institute.

Mission:

In the process of realization of its Vision, PERIIT strives to provide quality technical
education at affordable cost in a challenging & stimulating environment with state-of-the-art facilities
and a global team of dedicated and talented academicians, without compromising in its core values
of honesty, transparency and excellence.

VISSION AND MISSION OF THE DEPARTMENT

Vision:
The vision of the department is to prepare industry-ready competent professionals with moral
values by imparting scientific knowledge and skill-based education.

Mission:

• To provide exposure of latest tools and technologies in the broad area of computing.
• To promoter search-based projects/activities in the emerging area soft technology.
• ToenhanceIndustryInstituteInteractionprogramtogetacquaintedwithcorporateculture
and to develop entrepreneurship skills.
• To induce ethical values and spirit of social commitment.
PROGRAM OUTCOMES (POs)

1 Engineering knowledge: Apply the knowledge of mathematics, science, engineering

fundamentals, and an engineering specialization to the solution of complex engineering
problems.

2 Problem analysis: Identify, formulate, review research literature, and analyze complex
engineering problems reaching substantiated conclusions using first principles of
mathematics, natural sciences, and engineering sciences.

3 Design/development of solutions: Design solutions for complex engineering problems and

design system components or processes that meet the specified needs with appropriate
consideration for the public health and safety, and the cultural, societal, and environmental
considerations.

4 Conduct investigations of complex problems: Use research-based knowledge

and research methods including design of experiments, analysis and interpretation of data,
and synthesis of the information to provide valid conclusions.

5 Modern tool usage: Create, select, and apply appropriate techniques, resources, and
modern engineering and IT tools including prediction and modeling to complex engineering
activities with an understanding of the limitations.

6 The engineer and society: Apply reasoning informed by the contextual knowledge to
assess societal, health, safety, legal and cultural issues and the consequent responsibilities
relevant to the professional engineering practice.

7 Environment and sustainability: Understand the impact of the professional engineering

solutions in societal and environmental contexts, and demonstrate the knowledge of, and need
for sustainable development.

8 Ethics: Apply ethical principles and commit to professional ethics and responsibilities and
norms of the engineering practice.

9 Individual and team work: Function effectively as an individual, and as a member or

leader in diverse teams, and in multidisciplinary settings.

10 Communication: Communicate effectively on complex engineering activities with the

engineering community and with society at large, such as, being able to comprehend and write
effective reports and design documentation, make effective presentations, and give and receive
clear instructions.

11 Project management and finance: Demonstrate knowledge and understanding of the

engineering and management principles and apply these to one’s own work, as a member and
leader in a team, to manage projects and in multidisciplinary environments.

12 Life-long learning: Recognize the need for, and have the preparation and ability to
engage in independent and life-long learning in the broadest context of technological change.
CS3481 DATABASE MANAGEMENT SYSTEMS LABORATORY LTPC
0 0 3 1.5
COURSE OBJECTIVES:

• To learn and implement important commands in SQL.

• To learn the usage of nested and joint queries.
• To understand functions, procedures and procedural extensions of databases.
• To understand design and implementation of typical database applications.
• To be familiar with the use of a front end tool for GUI based application development.

LIST OF EXPERIMENTS:
1. Create a database table, add constraints (primary key, unique, check, Not null), insert rows,
update and delete rows using SQL DDL and DML commands.
2. Create a set of tables, add foreign key constraints and incorporate referential integrity.
3. Query the database tables using different ‘where’ clause conditions and also implement
aggregate functions.
4. Query the database tables and explore sub queries and simple join operations.
5. Query the database tables and explore natural, equi and outer joins.
6. Write user defined functions and stored procedures in SQL.
7. Execute complex transactions and realize DCL and TCL commands.
8. Write SQL Triggers for insert, delete, and update operations in a database table.
9. Create View and index for database tables with a large number of records.
10. Create an XML database and validate it using XML schema.
11. Create Document, column and graph based data using NOSQL database tools.
12. Develop a simple GUI based database application and incorporate all the above-mentioned
features
13. Case Study using any of the real life database applications from the following list
a) Inventory Management for a EMart Grocery Shop
b) Society Financial Management
c) Cop Friendly App – Eseva
d) Property Management – eMall
e) Star Small and Medium Banking and Finance
● Build Entity Model diagram. The diagram should align with the business and functional
goals stated in the application.
● Apply Normalization rules in designing the tables in scope.
● Prepared applicable views, triggers (for auditing purposes), functions for enabling
enterprise grade features.
● Build PL SQL / Stored Procedures for Complex Functionalities, ex EOD Batch Processing
for calculating the EMI for Gold Loan for each eligible Customer.
● Ability to showcase ACID Properties with sample queries with appropriate settings

TOTAL: 45 PERIODS
COURSE OUTCOMES:
At the end of this course, the students will be able to:
CO1: Create databases with different types of key constraints.
CO2: Construct simple and complex SQL queries using DML and DCL commands.
CO3: Use advanced features such as stored procedures and triggers and incorporate in GUI based
application development.
CO4: Create an XML database and validate with meta-data (XML schema).
CO5: Create and manipulate data using NOSQL database
Ex.No : 1 DDL & DML Commands

Create a database table, add constraints (primary key, unique, check, Not null), insert
rows,update and delete rows using SQL DDL and DML commands.

AIM:
To perform various DDL & DML commands and implement them on the database.

DDL COMMANDS

1. The Create Table Command: - it defines each column of the table uniquely. Each column
has minimum of three attributes, a name , data type and size.

Syntax:
Create table <table name> (<col1> <datatype>(<size>),<col2> <datatype><size>));

Ex:create table emp(empno number(4) primary key, ename char(10));

2. Modifying the structure of tables.

a) Add new columns

Syntax:
Alter table <tablename> add(<new col><datatype(size),<new col>datatype(size));

Ex:alter table emp add(sal number(7,2));

3. Dropping a column from a table.

Syntax:
Alter table <tablename> drop column <col>;

Ex:alter table emp drop column sal;

4. Modifying existing columns.

Syntax:
Alter table <tablename> modify(<col><newdatatype>(<newsize>));

Ex:alter table emp modify(ename varchar2(15));

5. Renaming the tables

Syntax:
Rename <oldtable> to <new table>;

Ex:rename emp to emp1;

 6. truncating the tables.
Syntax:
Truncate table <tablename>;

Ex:trunc table emp1;

7. Destroying tables.
Syntax:
Drop table <tablename>;

Ex:drop table emp;

EXAMPLE:

SQL>create table std(sno number(5),sname varchar(20),age number(5),sdob date,sm1

number(4,2),sm2 number(4,2),sm3 number(4,4));

Table created.

SQL>insert into std values(101,’AAA’,16,’03-jul-88’,80,90,98);

1 row created.

SQL>insert into std values(102,’BBB’,18,’04-aug-89’,88,98,90);

1 row created.

OUTPUT:
Select * from std;

SNO SNAME AGE SDOB SM1 SM2 SM3

101 AAA 16 03-jul-88 80 90 98

102 BBB 18 04-aug-89 88 98 90

ALTER TABLE WITH ADD:

SQL>create table student(id number(5),name varchar(10),game varchar(20));

Table created.

SQL>insert into student values(1,’mercy’,’cricket’);

1 row created.

SYNTAX:

alter table<tablename>add(col1 datatype,col2 datatype..);

EXAMPLE:
 SQL>alter table student add(age number(4));

SQL>insert into student values(2,’sharmi’,’tennis’,19);

OUTPUT:
ALTER: select * from student;

ID NAME GAME

1 Mercy Cricket

ADD: select * from student;

ID NAME GAME AGE

1 Mercy cricket

2 Sharmi Tennis 19

ALTER TABLE WITH MODIFY:

SYNTAX:
Alter table<tablename>modify(col1 datatype,col2 datatype..);

EXAMPLE:

SQL>alter table student modify(id number(6),game varchar(25));

OUTPUT:

MODIFY
desc student;

NAME NULL? TYPE

Id Number(6)

Name Varchar(20)

Game Varchar(25)

Age Number(4)

DROP:
SYNTAX: drop table<tablename>;
 EXAMPLE:

SQL>drop table student;

SQL>Table dropped.

TRUNCATE TABLE

SYNTAX: TRUNCATE TABLE <TABLE NAME>;

Example: Truncate table stud;

DESC

Example: desc emp;

Name Null? Type

EmpNo NOT NULL number(5)

EName VarChar(15)

Job NOT NULL Char(10)

DeptNo NOT NULL number(3)

PHONE_NO number (10)

DML COMMANDS

DML commands are the most frequently used SQL commands and is used to query
and manipulate the existing database objects. Some of the commands are Insert, Select,
Update, Delete.

Insert Command This is used to add one or more rows to a table. The values are separated by
commas and the data types char and date are enclosed in apostrophes. The values must be
entered in the same order as they are defined.

Select Commands It is used to retrieve information from the table. It is generally referred to
as querying the table. We can either display all columns in a table or only specify column
from the table.

Update Command It is used to alter the column values in a table. A single column may be
updated or more than one column could be updated.
Delete command After inserting row in a table we can also delete them if required. The delete
command consists of a from clause followed by an optional where clause.
Q1: Insert a single record into dept table.

Ans: SQL> insert into dept values (1,'IT','Tholudur');

1 row created.

Q2: Insert more than a record into emp table using a single insert command.

Ans: SQL> insert into emp values(&empno,'&ename','&job',&deptno,&sal);

Enter value for empno: 1

Enter value for ename: Mathi

Enter value for job: AP

Enter value for deptno: 1

Enter value for sal: 10000

old 1: insert into emp values(&empno,'&ename','&job',&deptno,&sal)

new 1: insert into emp values(1,'Mathi','AP',1,10000)

1 row created.

SQL> / Enter value for empno: 2

Enter value for ename: Arjun

Enter value for job: ASP

Enter value for deptno: 2

Enter value for sal: 12000

old 1: insert into emp values(&empno,'&ename','&job',&deptno,&sal)

new 1: insert into emp values(2,'Arjun','ASP',2,12000)

1 row created.

SQL> / Enter value for empno: 3

Enter value for ename: Gugan

Enter value for job: ASP

Enter value for deptno: 1

Enter value for sal: 12000

old 1: insert into emp values(&empno,'&ename','&job',&deptno,&sal)

new 1: insert into emp values(3,'Gugan','ASP',1,12000)

1 row created.

Q3: Update the emp table to set the salary of all employees to Rs15000/- who are working as
ASP

Ans: SQL> select * from emp;

EMPNO ENAME JOB DEPTNO SAL

1 Mathi AP 1 10000

2 Arjun ASP 2 12000

3 Gugan ASP 1 12000

SQL> update emp set sal=15000 where job='ASP'; 2 rows updated.

SQL> select * from emp;

EMPNO ENAME JOB DEPTNO SAL

1 Mathi AP 1 10000

2 Arjun ASP 2 15000

3 Gugan ASP 1 15000

Q4: Create a pseudo table employee with the same structure as the table emp and insert rows
into the table using select clauses.

Ans: SQL> create table employee as select * from emp;

Table created.

SQL> desc employee;

Name Null? Type

EMPNO NUMBER(6)

ENAME NOT NULL VARCHAR2(20)

JOB NOT NULL VARCHAR2(13)

DEPTNO NUMBER(3)

SAL NUMBER(7,2)

Q5: select employee name, job from the emp table

Ans: SQL> select ename, job from emp;

ENAME JOB

Mathi AP
Arjun ASP
Gugan ASP
Karthik Prof
Akalya AP
suresh lect
6 rows selected.
Q6: Delete only those who are working as lecturer
Ans: SQL> select * from emp;
EMPNO ENAME JOB DEPTNO SAL

1 Mathi AP 1 10000
2 Arjun ASP 2 15000
3 Gugan ASP 1 15000
4 Karthik Prof 2 30000
5 Akalya AP 1 10000
6 suresh lect 1 8000
6 rows selected.
SQL> delete from emp where job='lect';
1 row deleted.
SQL> select * from emp;
EMPNO ENAME JOB DEPTNO SAL
-
1 Mathi AP 1 10000
2 Arjun ASP 2 15000
3 Gugan ASP 1 15000
4 Karthik Prof 2 30000
5 Akalya AP 1 10000

Q7: List the records in the emp table orderby salary in ascending order.
Ans: SQL> select * from emp order by sal;
EMPNO ENAME JOB DEPTNO SAL
1 Mathi AP 1 10000
5 Akalya AP 1 10000
2 Arjun ASP 2 15000
3 Gugan ASP 1 15000
4 Karthik Prof 2 30000

Q8: List the records in the emp table orderby salary in descending order.
Ans: SQL> select * from emp order by sal desc;
EMPNO ENAME JOB DEPTNO SAL

4 Karthik Prof 2 30000

2 Arjun ASP 2 15000
3 Gugan ASP 1 15000
1 Mathi AP 1 10000
5 Akalya AP 1 10000
Q9: Display only those employees whose deptno is 30.
Solution: Use SELECT FROM WHERE syntax.

Ans: SQL> select * from emp where deptno=1;

EMPNO ENAME JOB DEPTNO SAL

1 Mathi AP 1 10000
3 Gugan ASP 1 15000
5 Akalya AP 1 10000

Q10: Display deptno from the table employee avoiding the duplicated values.
Solution:
1. Use SELECT FROM syntax.
2.Select should include distinct clause for the deptno.
Ans: SQL> select distinct deptno from emp;
DEPTNO

RESULT:

Thus the DDL & DML commands have been executed successfully.
Ex.No: 2 Constraints
Create a set of tables, add foreign key constraints and incorporate referential integrity.

Aim:
To execute and verify the various constraints in set of tables.

CONSTRAINTS:

Create table tablename (column_name1 data_ type constraints, column_name2 data_ type
constraints …)

Example:

Create table Emp ( EmpNo number(5), EName VarChar(15), Job Char(10) constraint un
unique, DeptNo number(3) CONSTRAINT FKey2 REFERENCES DEPT(DeptNo));

Create table stud (sname varchar2(20) not null, rollno number(10) not null,dob date not null);

DOMAIN INTEGRITY

Example: Create table cust(custid number(6) not null, name char(10));

Alter table cust modify (name not null);

CHECK CONSTRAINT

Example: Create table student (regno number (6), mark number (3) constraint b check (mark
>=0 and mark <=100)); Alter table student add constraint b2 check (length(regno<=4));

ENTITY INTEGRITY

a) Unique key constraint

Example: Create table cust(custid number(6) constraint unique, name char(10)); Alter table
cust add(constraint c unique(custid));

b) Primary Key Constraint

Example: Create table stud(regno number(6) constraint primary key, name char(20));

Queries:
Q1. Create a table called EMP with the following structure.
Name Type

EMPNO NUMBER(6) ENAME VARCHAR2(20)JOB VARCHAR2(10) DEPTNO NUMBER(3)

SAL NUMBER(7,2)
Allow NULL for all columns except ename and job.
 Solution:
1. Understand create table syntax.
2. Use the create table syntax to create the said tables.
3. Create primary key constraint for each table as understand from logical table structure.
Ans:
SQL> create table emp(empno number(6),ename varchar2(20)not null,job varchar2(10) not
null, deptno number(3),sal number(7,2));
Table created.
Q2: Add a column experience to the emp table.
experience numeric null allowed.
Solution:
1. Learn alter table syntax.
2. Define the new column and its data type.
3. Use the alter table syntax.
Ans: SQL> alter table emp add(experience number(2));
Table altered.

Q3: Modify the column width of the job field of emp table.
Solution:
1. Use the alter table syntax.
2. Modify the column width and its data type.
Ans: SQL> alter table emp modify(job varchar2(12));
Table altered.

SQL> alter table emp modify(job varchar(13));

Table altered.

Q4: Create dept table with the following structure.

Name Type

DEPTNO NUMBER(2)
DNAME VARCHAR2(10)
LOC VARCHAR2(10)
Deptno as the primarykey
Solution:
1. Understand create table syntax.
2. Decide the name of the table.
3. Decide the name of each column and its data type.
4. Use the create table syntax to create the said tables.
5. Create primary key constraint for each table as understand from logical table structure.
Ans:
SQL> create table dept(deptno number(2) primary key,dname varchar2(10),loc
varchar2(10));
Table created.
 Q5: create the emp1 table with ename and empno, add constraints to check the empno value
while entering (i.e) empno > 100.
Solution:
1. Learn alter table syntax.
2. Define the new constraint [columns name type]
3. Use the alter table syntax for adding constraints.
Ans:
SQL> create table emp1(ename varchar2(10),empno number(6) constraint
check(empno>100));
Table created.

Q6: drop a column experience to the emp table.

Solution:
1. Learn alter table syntax. Use the alter table syntax to drop the column.
Ans:
SQL> alter table emp drop column experience; Table altered.

Q7: Truncate the emp table and drop the dept table

Solution:

1. Learn drop, truncate table syntax.

Ans: SQL> truncate table emp; Table truncated.

RESULT:

Thus various constraint are set in table & executed successfully.

Ex.No:3 Aggregate Functions

Query the database tables using different ‘where’ clause conditions and also implement
aggregate functions.

Aim:
To query the table using aggregate function & different where clause

Q1: Insert a single record into dept table.

Ans: SQL> insert into dept values (1,'IT','Tholudur');

1 row created.

Q2: Insert more than a record into emp table using a single insert command.

Ans: SQL> insert into emp values(&empno,'&ename','&job',&deptno,&sal);

Enter value for empno: 1

Enter value for ename: Mathi

Enter value for job: AP

Enter value for deptno: 1

Enter value for sal: 10000

old 1: insert into emp values(&empno,'&ename','&job',&deptno,&sal)

new 1: insert into emp values(1,'Mathi','AP',1,10000)

1 row created.

SQL> / Enter value for empno: 2

Enter value for ename: Arjun

Enter value for job: ASP

Enter value for deptno: 2

Enter value for sal: 12000

old 1: insert into emp values(&empno,'&ename','&job',&deptno,&sal)

new 1: insert into emp values(2,'Arjun','ASP',2,12000)

1 row created.

SQL> / Enter value for empno: 3

Enter value for ename: Gugan

Enter value for job: ASP

Enter value for deptno: 1

Enter value for sal: 12000

old 1: insert into emp values(&empno,'&ename','&job',&deptno,&sal)

new 1: insert into emp values(3,'Gugan','ASP',1,12000)

1 row created.

Q3: Update the emp table to set the salary of all employees to Rs15000/- who are working as
ASP

Ans: SQL> select * from emp;

EMPNO ENAME JOB DEPTNO SAL

1 Mathi AP 1 10000

2 Arjun ASP 2 12000

3 Gugan ASP 1 12000

SQL> update emp set sal=15000 where job='ASP'; 2 rows updated.

SQL> select * from emp;

EMPNO ENAME JOB DEPTNO SAL

1 Mathi AP 1 10000

2 Arjun ASP 2 15000

3 Gugan ASP 1 15000

Q4: Create a pseudo table employee with the same structure as the table emp and insert rows
into the table using select clauses.

Ans: SQL> create table employee as select * from emp;

Table created.

SQL> desc employee;

Name Null? Type

EMPNO NUMBER(6)

ENAME NOT NULL VARCHAR2(20)

JOB NOT NULL VARCHAR2(13)

DEPTNO NUMBER(3)

SAL NUMBER(7,2)

Q5: select employee name, job from the emp table

Ans: SQL> select ename, job from emp;

ENAME JOB

Mathi AP
Arjun ASP
Gugan ASP
Karthik Prof
Akalya AP
suresh lect
6 rows selected.
Q6: Delete only those who are working as lecturer
Ans: SQL> select * from emp;
EMPNO ENAME JOB DEPTNO SAL

1 Mathi AP 1 10000
2 Arjun ASP 2 15000
3 Gugan ASP 1 15000
4 Karthik Prof 2 30000
5 Akalya AP 1 10000
6 suresh lect 1 8000
6 rows selected.
SQL> delete from emp where job='lect';
1 row deleted.
SQL> select * from emp;
EMPNO ENAME JOB DEPTNO SAL
-----------------
1 Mathi AP 1 10000
2 Arjun ASP 2 15000
3 Gugan ASP 1 15000
4 Karthik Prof 2 30000
5 Akalya AP 1 10000
 Q7: List the records in the emp table orderby salary in ascending order.
Ans: SQL> select * from emp order by sal;
EMPNO ENAME JOB DEPTNO SAL

1 Mathi AP 1 10000
5 Akalya AP 1 10000
2 Arjun ASP 2 15000
3 Gugan ASP 1 15000
4 Karthik Prof 2 30000

Q8: List the records in the emp table orderby salary in descending order.
Ans: SQL> select * from emp order by sal desc;
EMPNO ENAME JOB DEPTNO SAL

4 Karthik Prof 2 30000

2 Arjun ASP 2 15000
3 Gugan ASP 1 15000
1 Mathi AP 1 10000
5 Akalya AP 1 10000
Q9: Display only those employees whose deptno is 30.
Solution: Use SELECT FROM WHERE syntax.

Ans: SQL> select * from emp where deptno=1;

EMPNO ENAME JOB DEPTNO SAL

1 Mathi AP 1 10000
3 Gugan ASP 1 15000
5 Akalya AP 1 10000

Q10: Display deptno from the table employee avoiding the duplicated values.
Solution:
1. Use SELECT FROM syntax.
2.Select should include distinct clause for the deptno.
Ans: SQL> select distinct deptno from emp;
DEPTNO

IMPLEMENTATION OF DATA AND BUILT IN FUNCTIONS IN SQL

CHARACTER/STRING FUNCTION:

SQL> select upper('welcome') from dual;

WELCOME

SQL> select upper('hai') from dual;

---

HAI

SQL> select lower('HAI') from dual;

LOW

---

hai

SQL> select initcap(‘hello world') from dual;

INITCAP('Hello

Hello World

SQL> select ltrim(' hai') from dual;

LTR

---

hai

SQL> select rtrim('hai ')from dual;

RTR

---

hai

SQL> select rtrim('hai')from dual;

RTRIM('

hai
SQL> select concat('Anna',' university')from dual;

Anna university

SQL> select length('Anna’)from dual;

LENGTH('SRM')

12

SQL> select replace('RM university', 'RM','Anna')from dual;

Anna university

SQL> select substr('RM', 7,6)from dual;

SUBSTR

lingam
SQL> select rpad('hai',3,'*')from dual;

RPAD('

hai***

SQL> select lpad('hai',3,'*')from dual;

LPAD('

***hai

SQL> select replace('Dany','y','ie')from dual;

REPLACE

Danie
SQL> select translate('cold','ld','ol')from dual;

TRANSL

cool
DATE & TIME FUNCTION

SQL> select sysdate from dual;

SYSDATE

07-APR-10

SQL> select round(sysdate)from dual;

ROUND(SYS

07-APR-10

SQL> select add_months(sysdate,3)from dual;

ADD_MONTH

07-JUL-10

SQL> select last_day(sysdate)from dual;

LAST_DAY(

30-APR-10

SQL> select sysdate+20 from dual;

SYSDATE+2

27-APR-10

SQL> select next_day(sysdate,'tuesday')from dual;

NEXT_DAY(

13-APR-10
 NUMERIC FUNCTION

SQL> select round(15.6789)from dual;

ROUND(15.6789)

16

SQL> select ceil(23.20)from dual;

CEIL(23.20)

24

SQL> select floor(34.56)from dual;

FLOOR(34.56)

34

SQL> select trunc(15.56743)from dual;

TRUNC(15.56743)

15

SQL> select sign(-345)from dual;

SIGN(-345)

-1

SQL> select abs(-70)from dual;

ABS(-70)

70
 MATH FUNCTION:

SQL> select abs(45) from dual;

ABS(45)

45

SQL> select power(10,12) from dual;

POWER(10,12)

1.000E+12

SQL> select mod(11,5) from dual;

MOD(11,5)

SQL> select exp(10) from dual;

EXP(10)

22026.466

SQL> select sqrt(225) from dual;

SQRT(225)

15

Result
Thus quering the table using aggregate function & different where clause are executed successfully.
Ex.No:4 Sub Queries

Aim : To Query the database tables and explore sub queries and simple join operations.

NESTED QUERIES AND JOIN QUERIES

Q1: Display all employee names and salary whose salary is greater than minimum salary of
the company and job title starts with ‗M‘.

Solution:

1. Use select from clause.

2. Use like operator to match job and in select clause to get the result.

Ans: SQL> select ename,sal from emp where sal>(select min(sal) from emp where job like
'A%');

ENAME SAL

Arjun 12000

Gugan 20000

Karthik 15000

Q2: Issue a query to find all the employees who work in the same job as Arjun.

Ans: SQL> select * from emp;

EMPNO ENAME JOB DEPTNO SAL

1 Mathi AP 1 10000

2 Arjun ASP 2 12000

3 Gugan ASP 2 20000

4 Karthik AP 1 15000

SQL> select ename from emp where job=(select job from emp where ename='Arjun');
ENAME
 Arjun

Gugan

SET OPERATORS

Q1: Display all the dept numbers available with the dept and emp tables avoiding duplicates.

Solution:

1. Use select from clause.

2. Use union select clause to get the result.

Ans: SQL> select deptno from emp union select deptno from dept;

DEPTNO

12

30

40

Q2: Display all the dept numbers available with the dept and emp tables.

Solution:

1. Use select from clause.

2. Use union all in select clause to get the result.

Ans: SQL> select deptno from emp union all select deptno from dept;

DEPTNO

2
 1
12

30

40

9 rows selected.

Q3: Display all the dept numbers available in emp and not in dept tables and vice versa.
Solution:

1. Use select from clause.

2. Use minus in select clause to get the result.

Ans: SQL> select deptno from emp minus select deptno from dept;

DEPTNO

12

SQL> select deptno from dept minus select deptno from emp;

DEPTNO

30

40

Result:

Thus the command for subqueries are executed successfully.

Ex.No: 5 Join Queries
Query the database tables and explore natural, equi and outer joins.

Aim:
To perform various join operation in database
NESTED QUERIES AND JOIN QUERIES

Q1: Display all employee names and salary whose salary is greater than minimum salary of
the company and job title starts with ‗M‘.

Solution:

3. Use select from clause.

4. Use like operator to match job and in select clause to get the result.

Ans: SQL> select ename,sal from emp where sal>(select min(sal) from emp where job like
'A%');

ENAME SAL

Arjun 12000

Gugan 20000

Karthik 15000

Q2: Issue a query to find all the employees who work in the same job as Arjun.

Ans: SQL> select * from emp;

EMPNO ENAME JOB DEPTNO SAL

5 Mathi AP 1 10000

6 Arjun ASP 2 12000

7 Gugan ASP 2 20000

8 Karthik AP 1 15000
SQL> select ename from emp where job=(select job from emp where ename='Arjun');
ENAME

Arjun

Gugan

Q3: Issue a query to display information about employees who earn more than any employee
in dept 1. Ans: SQL> select * from emp where sal>(select max(sal) from emp where
empno=1); EMPNO ENAME JOB DEPTNO SAL

2 Arjun ASP 2 12000

3 Gugan ASP 2 20000
4 Karthik AP 1 15000
JOINS Tables used
SQL> select * from emp;
EMPNO ENAME JOB DEPTNO SAL

1 Mathi AP 1 10000
2 Arjun ASP 2 12000
3 Gugan ASP 2 20000
4 Karthik AP 1 15000
SQL> select * from dept;
DEPTNO DNAME LOC

1 ACCOUNTING NEW YORK

2 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
EQUI-JOIN

Q4: Display the employee details, departments that the departments are same in both the emp
and dept. Solution: 1. Use select from clause.
2. Use equi join in select clause to get the result.
Ans: SQL> select * from emp,dept where emp.deptno=dept.deptno;
EMPNO ENAME JOB DEPTNO SAL DEPTNO DNAME LOC

1 Mathi AP 1 10000 1 ACCOUNTING NEW YORK

2 Arjun ASP 2 12000 2 RESEARCH DALLAS
3 Gugan ASP 2 20000 2 RESEARCH DALLAS
4 Karthik AP 1 15000 1
ACCOUNTING NEW YORK
NON-EQUIJOIN
Q5: Display the employee details, departments that the departments are not same in both the
emp and dept. Solution: 1.Use select from clause. 2. Use non equi join in select clause to get
the result.

Ans: SQL> select * from emp,dept where emp.deptno!=dept.deptno;

EMPNO ENAME JOB DEPTNO SAL DEPTNO DNAME LOC

2 Arjun ASP 2 12000 1 ACCOUNTING NEW YORK

3 Gugan ASP 2 20000 1 ACCOUNTING NEW YORK
1 Mathi AP 1 10000 2 RESEARCH DALLAS
EMPNO ENAME JOB DEPTNO SAL DEPTNO DNAME LOC

4 Karthik AP 1 15000 2 RESEARCH DALLAS

1 Mathi AP 1 10000 30 SALES CHICAGO
2 Arjun ASP 2 12000 30 SALES CHICAGO
EMPNO ENAME JOB DEPTNO SAL DEPTNO DNAME LOC

3 Gugan ASP 2 20000 30 SALES CHICAGO

4 Karthik AP 1 15000 30 SALES CHICAGO
1 Mathi AP 1 10000 40 OPERATIONS BOSTON
EMPNO ENAME JOB DEPTNO SAL DEPTNO DNAME LOC

2 Arjun ASP 2 12000 40 OPERATIONS BOSTON

3 Gugan ASP 2 20000 40 OPERATIONS BOSTON
4 Karthik AP 1 15000 40 OPERATIONS BOSTON
12 rows selected.
LEFTOUT-JOIN Tables used SQL> select * from stud1;
Regno Name Mark2 Mark3 Result

john 89 80 pass
101 Raja 70 80 pass
102 Sharin 70 90 pass
103 sam 90 95 pass

SQL> select * from stud2;

NAME GRA

john s raj s sam a sharin a

Q6: Display the Student name and grade by implementing a left outer join.
Ans: SQL> select stud1.name,grade from stud1 left outer join stud2 on
stud1.name=stud2.name; Name Gra

john s raj
s sam a sharin a smith null
RIGHTOUTER-JOIN
Q7: Display the Student name, register no, and result by implementing a right outer join.
Ans: SQL> select stud1.name, regno, result from stud1 right outer join stud2 on stud1.name
= stud2.name; Name Regno Result

john 101 pass

raj 102 pass
sam 103 pass
sharin 104 pass
Rollno Name Mark1 Mark2 Total

1 sindu 90 95 185
2 arul 90 90 180
FULLOUTER-JOIN

Q8: Display the Student name register no by implementing a full outer join.
Ans: SQL> select stud1.name, regno from stud1 full outer join stud2 on (stud1.name=
stud2.name); Name Regno

john 101
raj 102 sam
103 sharin 104
SELFJOIN

Q9: Write a query to display their employee names

Ans: SQL> select distinct ename from emp x, dept y where x.deptno=y.deptno;
ENAME

Arjun
Gugan
Karthik
Mathi
 Q10: Display the details of those who draw the salary greater than the average salary.
Ans: SQL> select distinct * from emp x where x.sal >= (select avg(sal) from emp);
EMPNO ENAME JOB DEPTNO SAL

3 Gugan ASP 2 20000

4 Karthik AP 1 15000
11 kavitha designer 12 17000

Result:

Thus various join condition are executed successfully.

Ex.No: 6 Functions & Procedure

Aim: To write user defined functions and stored procedures in SQL.

PROCEDURE TO INSERT NUMBER

SQL> create table emp1(id number(3),First_name varchar2(20));

Table created.

SQL> insert into emp1 values(101,'Nithya');

1 row created.

SQL> insert into emp1 values(102,'Maya');

1 row created.

SQL> select * from emp1;

ID FIRST_NAME

101 Nithya

102 Maya

SQL> set serveroutput on;

SQL> create or replace

2 procedure insert_num(p_num number)is

3 begin

4 insert into emp1(id,First_name) values(p_num,user);

5 end insert_num;

6/

Procedure created.

SQL> exec insert_num(3);

 PL/SQL procedure successfully completed.

SQL> select * from emp1;

ID FIRST_NAME

101 Nithya

102 Maya

103 SCOTT
FUNCTION TO FIND FACTORIAL

SQL> create or replace function fact(n number)

2 return number is

3 i number(10);

4 f number:=1;

5 begin

6 for i in 1..N loop

7 f:=f*i;

8 end loop;

9 return f;

10 end;

11 /

Function created.

SQL> select fact(2) from dual;

FACT(2)

RESULT:

Thus procedures and functions were implemented successfully.

Ex.No:7 DCL & TCL Commands

Aim:
To execute complex transactions and realize DCL & TCL commands on database.

DCL COMMANDS

The DCL language is used for controlling the access to the table and hence securing the
database. DCL is used to provide certain privileges to a particular user. Privileges are rights
to be allocated.The privilege commands are namely, Grant and Revoke.The various
privileges that can be granted or revoked are, Select Insert Delete Update References Execute
All.

GRANT COMMAND: It is used to create users and grant access to the database. It requires
database administrator (DBA) privilege, except that a user can change their password. A user
can grant access to their database objects to other users.

REVOKE COMMAND: Using this command , the DBA can revoke the granted database
privileges from the user.

TCL COMMAND

COMMIT: command is used to save the Records.

ROLL BACK: command is used to undo the Records.

SAVE POINT command is used to undo the Records in a particular transaction.

Queries:

Tables Used: Consider the following tables namely “DEPARTMENTS” and

“EMPLOYEES”

Their schemas are as follows , Departments ( dept _no , dept_ name , dept_location );
Employees ( emp_id , emp_name , emp_salary );

Q1: Develop a query to grant all privileges of employees table into departments table
Ans: SQL> Grant all on employees to departments;
Grant succeeded.

Q2: Develop a query to grant some privileges of employees table into departments table
Ans: SQL> Grant select, update , insert on departments to departments with grant option;
Grant succeeded.

Q3: Develop a query to revoke all privileges of employees table from departments table
Ans: SQL> Revoke all on employees from departments; Revoke succeeded.
Q4: Develop a query to revoke some privileges of employees table from departments table
Ans: SQL> Revoke select, update , insert on departments from departments;
Revoke succeeded.
Q5: Write a query to implement the save point

Ans: SQL> SAVEPOINT S1;

Savepoint created.

SQL> select * from emp;

EMPNO ENAME JOB DEPTNO SAL

1 Mathi AP 1 10000

2 Arjun ASP 2 15000

3 Gugan ASP 1 15000

4 Karthik Prof 2 30000

SQL> INSERT INTO EMP VALUES(5,'Akalya','AP',1,10000); 1 row created.

SQL> select * from emp;

EMPNO ENAME JOB DEPTNO SAL

1 Mathi AP 1 10000

2 Arjun ASP 2 15000

3 Gugan ASP 1 15000

4 Karthik Prof 2 30000

5 Akalya AP 1 10000

Q6: Write a query to implement the rollback

Ans: SQL> rollback s1; SQL> select * from emp;

EMPNO ENAME JOB DEPTNO SAL

1 Mathi AP 1 10000

2 Arjun ASP 2 15000

 3 Gugan ASP 1 15000

4 Karthik Prof 2 30000

Q6: Write a query to implement the commit

Ans: SQL> COMMIT;

Commit complete.

RESULT

Thus the DCL,TCL commands was performed successfully and executed.

Ex.No: 8 Triggers
Aim: To write SQL Triggers for insert, delete, and update operations in a database table.

TRIGGER FOR DISPLAYING GRADE OF THE STUDENT

SQL> create table stdn(rollno number(3),name varchar(2),m1 number(3),m2 number(3),m3

number(3),tot num

ber(3),avrg number(3),result varchar(10));

Table created.

SQL> create or replace trigger t1 before insert on stdn

2 for each row

3 begin

4 :new.tot:=:new.m1+:new.m2+:new.m3;

5 :new.avrg:=:new.tot/3;

6 if(:new.m1>=50 and :new.m2>=50 and :new.m3>=50) then

7 :new.result:='pass';

8 else

9 :new.result:='Fail';

10 end if;

11 end;

12 /

Trigger created.

SQL> insert into stdn values(101,'SM',67,89,99,'','','');

1 row created.

SQL> select * from stdn;

ROLLNO NA M1 M2 M3 TOT AVRG RESULT

101 SM 67 89 99 255 85 pass

 PROGRAM TO INDICATE INVALID CONDITION USING TRIGGER

SQL> create table emp (name varchar(10),empno number(3),age number(3));

Table

created.

SQL>

1 create or replace trigger t2 before insert on emp

2 for each row

3 when(new.age>100)

4 begin

5 RAISE_APPLICATION_ERROR(-20998,'INVALID

ERROR');6* end;

SQL> /

Trigger created.

SQL> insert into emp

values('nithya',101,24);1 row created.

SQL> insert into emp

values('nithya',101,103);insert into emp

values('nithya',101,103)

*
ERROR at line 1:

ORA-20998: INVALID

ERROR ORA-06512: at

"SCOTT.T2", line 2

ORA-04088: error during execution of trigger 'SCOTT.T2'

RESULT:
Thus triggers were implemented successful
 Ex.No: 9 View & Index

Aim:
To perform view & Index for database table.

VIEWS

Q1: The organization wants to display only the details of the employees those who are
ASP.

Solution:

1. Create a view on emp table named managers

2. Use select from clause to do horizontal portioning

Ans: SQL> create view empview as select * from emp where

job='ASP';View created.

SQL CREATE VIEW Statement

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view
are fields from one or morereal tables in the database.

You can add SQL functions, WHERE, and JOIN statements to a view and present
the data as if the datawere coming from one single table.

CREATE VIEW Syntax CREATE VIEW view_name AS

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

Note: A view always shows up-to-date data! The database engine recreates the data,
using the view's SQLstatement, every time a user queries a view.

SQL CREATE VIEW Examples

If you have the Northwind database you can see that it has several views installed by
default.

The view "Current Product List" lists all active products (products that are
not discontinued) from the"Products" table. The view is created with the
following SQL:
CREATE VIEW
[Current Product
List] ASSELECT
ProductID,
ProductName
FROM Products
WHERE Discontinued = No;

Then, we can

query the view

as follows:

SELECT *

FROM [Current

Product List];

Another view in the Northwind sample database selects every product in the
"Products" table with a unitprice higher than the average unit price:

CREATE VIEW [Products

Above Average Price] AS
SELECT ProductName,
UnitPrice
FROM Products
WHERE UnitPrice > (SELECT

AVG(UnitPrice) FROM Products);We

can query the view above as follows:

SELECT * FROM [Products Above Average Price];

Another view in the Northwind database calculates the total sale for each category
in 1997. Note that thisview selects its data from another view called "Product Sales
for 1997":

CREATE VIEW [Category Sales For 1997] AS

SELECT DISTINCT CategoryName,
Sum(ProductSales) AS CategorySalesFROM
 [Product Sales for 1997]
GROUP BY CategoryName;

We can query the view above as follows:

SELECT * FROM [Category Sales For 1997];

We can also add a condition to the query. Let's see the total sale only for

the category "Beverages":SELECT * FROM [Category Sales For 1997]

WHERE CategoryName = 'Beverages';

SQL Updating a View

You can update a view by using the following syntax:

SQL CREATE OR

REPLACE VIEW

Syntax CREATE OR

REPLACE VIEW

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

Now we want to add the "Category" column to the "Current Product List" view.
We will update the viewwith the following SQL:

CREATE OR REPLACE VIEW

[Current Product List] AS
SELECT ProductID,
ProductName, Category
FROM Products WHERE Discontinued = No;SQL Dropping a View
You can delete a view with the DROP VIEW command.

SQL DROP VIEW Syntax

DROP VIEW view_name;

SQL CREATE INDEX Statement

SQL CREATE INDEX Statement

The CREATE INDEX statement is used to create indexes in tables.

 Indexes are used to retrieve data from the database very fast. The users cannot see the
indexes, they are justused to speed up searches/queries.

CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name

ON table_name (column1, column2, ...);

CREATE UNIQUE INDEX Syntax

Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name

ON table_name (column1, column2, ...);

CREATE INDEX Example

The SQL statement below creates an index named "idx_lastname" on the

"LastName" column in the"Persons" table:

CREATE INDEX idx_lastnameON Persons (LastName);

If you want to create an index on a combination of columns, you can list the
column names within theparentheses, separated by commas:

CREATE INDEX idx_pname

ON Persons (LastName, FirstName);

DROP INDEX Statement

The DROP INDEX statement is used to delete an index in a table.

MS Access:

DROP INDEX index_name ON table_name;

SQL Server:

DROP INDEX table_name.index_name;

DB2/Oracle:

DROP INDEX index_name;

MySQL:

ALTER TABLE table_name

DROP INDEX index_name;

SQL> select * from empview;

EMPNO ENAME JOB

DEPTNO SAL

2 Arjun ASP 2 12000 3 Gugan ASP 2 20000

Q2: The organization wants to display only the details like empno, empname,
deptno,deptname of the employees. (Vertical portioning)

Solution: 1. Create a view on emp table named general

2. Use select from clause to do vertical partioning

Ans: SQL> create view empview1 as select ename,sal

from emp;View created.

Q3: Display all the views

generated.Ans: SQL> select *

from tab; TNAME

TABTYPE CLUSTERID

DEPT

TABLE EMP

TABLE
EMPVIEW

VIEW

EMPVIEW1

VIEW

Q4: Execute the DML commands on the view

created.Ans: SQL> select * from empview;

EMPNO ENAME JOB DEPTNO SAL

2 Arjun ASP 2 12000

3 Gugan ASP 2 20000

Q5: Drop a view.

Ans: SQL> drop view

empview1;View dropped.

Result:

Thus View & Index are implemented in tables.

Ex.No :10 XML Database
Aim:
To create a simple XML document to display the address book.
Procedure:
Step-1: Create the xml document using notepad.
Step-2: Create the addressbook as a root tag.
Step-3: Followed by create a three employees address in detail using different tag.
Step-4: Save the file as “Employee.xml”.
Step-5: Finally execute the program in Internet Explorer to view the output.
Source Code:
Filename: “Employee.xml”
<?xml version = "1.0" encoding="UTF-8" standalone="yes" ?>
<addressbook>
<emp1>
<name>Karthick</name>
<doorno>No-2/57</doorno>
<street>Vinayakam St</street>
<area>Teynampet</area>
<city>Chennai-600020</city>
<state>Tamil Nadu</state>
<country>India</country>
<phoneno>909583625</phoneno>
</emp1>
<emp2>
<name>Murali</name>
<doorno>No-25/7</doorno>
<street>ParthasarathyKoil Street</street>
<area>Triplecane</area>
<city>Chennai-600005</city>
<state>Tamil Nadu</state>
<country>India</country>
<phoneno>9962589632</phoneno>
</emp2>
<emp3>
<name>Jagagish</name>
<doorno>No-25</doorno>
<street>Palani Street,</street>
<area>Manali</area>
<city>Chennai-600055</city>
<state>Tamil Nadu</state>
<country>India</country>
<phoneno>9003862541</phoneno>
</emp3>
</addressbook>
Output

Result:
Thus, the simple xml document creation has been executed successfully.
XML SCHEMA CREATION
Aim:
To create a program for XML Schema creation and display element and attributes.
Procedure:
Step-1: Open the XMLwriter.
Step-2: Click File->New->XML Document and name the Filename.
Step-3:Type the following program:
Source Code:
Filename:“Sche.xml”
<?xml version="1.0" encoding="UTF-8" ?>
<shiporder orderid="889923"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="sche.xsd">
<orderperson>John Smith</orderperson>
<shipto>
<name>Karthick</name>
<address>Langgt 23</address>
<city>4000 Stavanger</city>
<country>Norway</country>
</shipto>
<item>
<title>Empire Burlesque</title>
<note>Special Edition</note>
<quantity>1</quantity>
<price>10.90</price>
</item>
<item>
<title>Hide your heart</title>
<quantity>5</quantity>
<price>9.90</price>
</item>
</shiporder>
Step-4: Click File->New->XML Schema File and name the Filename.
Step-5: Type the following code:
Source Code:
Filename:“sche.xsd”
<?xml version="1.0" encoding="UTF-8" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="shiporder">
<xs:complexType>
<xs:sequence>
<xs:element name="orderperson" type="xs:string" />
<xs:element name="shipto">
<xs:complexType>
<xs:sequence>
<xs:element name="name" type="xs:string" />
<xs:element name="address" type="xs:string" />
<xs:element name="city" type="xs:string" />
<xs:element name="country" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="item" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="title" type="xs:string" />
<xs:element name="note" type="xs:string" minOccurs="0" />
<xs:element name="quantity" type="xs:positiveInteger" />
<xs:element name="price" type="xs:decimal" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="orderid" type="xs:string" use="required" />
</xs:complexType>
</xs:element>
</xs:schema>
Step-6: Finally validate the xml file by pressing the F7 key.

Output:

Result:
Thus, the program for XML Schema creation and display element and attributes has been
executed successfully.
Ex.No:11 NOSQL database tools

Aim:
To create document ,column & graph based data

Install MongoDB using the docker

➜ docker --version
Docker version 20.10.3, build 48d30b5
Awesome, now let’s proceed further and pull the mongo official image from dockerhub.

➜ docker pull mongo

Using default tag: latest
latest: Pulling from library/mongo
4bbfd2c87b75: Pull complete
d2e110be24e1: Pull complete
889a7173dcfe: Pull complete
6f6a1a25f35a: Pull complete
e87b34c16538: Pull complete
7099eef4dfe4: Pull complete
29b1d79d3b5b: Pull complete
b5c178e98a5a: Pull complete
ded800e62b93: Pull complete
b09aa2e255f0: Pull complete
c7e0f50ad27a: Pull complete
dcdad63a2ffa: Pull complete
Digest: sha256:482a562bf25f42f02ce589458f72866bbe9eded5b6f8fa5b1213313f0e00bba2
Status: Downloaded newer image for mongo:latest
docker.io/library/mongo:latest
Verify if the docker image gets downloaded

➜ docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
mongo latest 81a352f64d83 16 hours ago 449MB
Now let’s start the docker container

➜ docker run --name my-mongo -d mongo:latest

659e36f1f2b967c8cb20b1697181d03f43b83468af29c2091de40690601849ca
Type the below command to go inside the docker container

➜ docker exec -it my-mongo /bin/bash

root@659e36f1f2b9:/#
Verify mongo version

root@659e36f1f2b9:/# mongo --version

MongoDB shell version v4.4.6
Build Info: {
"version": "4.4.6",
"gitVersion": "72e66213c2c3eab37d9358d5e78ad7f5c1d0d0d7",
"openSSLVersion": "OpenSSL 1.1.1 11 Sep 2018",
"modules": [],
"allocator": "tcmalloc",
"environment": {
"distmod": "ubuntu1804",
"distarch": "x86_64",
"target_arch": "x86_64"
}
}
root@659e36f1f2b9:/#
Awesome, you have successfully installed MongoDB; let’s proceed further and see some of
the commonly used MongoDB commands.

MongoDB commands

MongoDB has a vibrant command-line interface, using which we can easily interact with
MongoDB.This tutorial will see some of the most widely used MongoDB commands.

Type the below command to get inside the mongo shell.

root@659e36f1f2b9:/# mongo
MongoDB shell version v4.4.6
connecting to:
mongodb://127.0.0.1:27017/?compressors=disabled&gssapiServiceName=mongodb
................<supressed o/p>............
................<supressed o/p>............
---
>
List Databases

show dbs command will list all the databases present in MongoDB.By default, MongoDB has
some system databases created.

> show dbs

admin 0.000GB
config 0.000GB
local 0.000GB

Create new Database

use the <dbname> command will create a new database in MongoDB

> use naivetech

switched to db naivetech

To view the current database

with the help of the db command; we can verify the current database user are in.

> db
naivetech

Create a new collection

Now let’s create a test collection in the naivetech database

> db.createCollection('test')
{ "ok" : 1 }

List collections in MongoDB

To list all the collections in the mongo naivetech database, type the below command:

> show collections

test

Drop a collection

Let’s drop the test collection that we created earlier

> db.test.drop()
true

Delete a database
To drop any database, switch to that database and type the below command to delete:

> db.dropDatabase()
{ "dropped" : "naivetech", "ok" : 1 }

Insert a document in the MongoDB collection

Now let’s proceed further and create another database, naivetechblog, and a new collection
of tech

> use naivetechblog

switched to db naivetechblog
> db.createCollection("tech")
{ "ok" : 1 }
Type the below command to insert a document in the tech collection

> db.tech.insert({
... 'Kind': 'Mobile',
... 'lang': 'iphone 6s',
... 'Rating': 4.2
... })
WriteResult({ "nInserted" : 1 })
>

Show documents in a MongoDB collection

Let’s list all the documents present in the tech collection

> db.tech.find()
{ "_id" : ObjectId("60c37656a9c3d411ad96c6e6"), "Kind" : "Mobile", "lang" : "iphone 6s",
"Rating" : 4.2 }

Pretty command

With MongoDB pretty command, we can print the output in a pretty format

> db.tech.find().pretty()
{
"_id" : ObjectId("60c37656a9c3d411ad96c6e6"),
"Kind" : "Mobile",
"lang" : "iphone 6s",
"Rating" : 4.2
}
>

Insert many documents in the MongoDB collection

Sometimes we need to insert more than 1 document at one go in our MongoDB collection.
With MongoDB insert many command, we can insert multiple documents.

> db.tech.insertMany([
... {
... 'Kind': 'Mobile',
... 'lang': 'iphone 6s',
... 'Rating': 4.2
... },
... {
... 'Kind': 'laptop',
... 'lang': 'acer',
... 'Rating': 3.9
... },
... {
... 'Kind': 'mobile',
... 'lang': 'samsung',
... 'Rating': 4.0
... },
... ])
{
"acknowledged" : true,
"insertedIds" : [
ObjectId("60c37883a9c3d411ad96c6e7"),
ObjectId("60c37883a9c3d411ad96c6e8"),
ObjectId("60c37883a9c3d411ad96c6e9")
]
}
Now let’s verify if all documents get created.

> db.tech.find()
{ "_id" : ObjectId("60c37656a9c3d411ad96c6e6"), "Kind" : "Mobile", "lang" : "iphone 6s",
"Rating" : 4.2 }
{ "_id" : ObjectId("60c37883a9c3d411ad96c6e7"), "Kind" : "Mobile", "lang" : "iphone 6s",
"Rating" : 4.2 }
{ "_id" : ObjectId("60c37883a9c3d411ad96c6e8"), "Kind" : "laptop", "lang" : "acer",
"Rating" : 3.9 }
{ "_id" : ObjectId("60c37883a9c3d411ad96c6e9"), "Kind" : "mobile", "lang" : "samsung",
"Rating" : 4 }
>

Search MongoDB

With the help of the find command, we can filter out/search the documents. Let’s try to find
the documents which contain mobile details.

> db.tech.find({Kind:'Mobile'})
{ "_id" : ObjectId("60c37656a9c3d411ad96c6e6"), "Kind" : "Mobile", "lang" : "iphone 6s",
"Rating" : 4.2 }
{ "_id" : ObjectId("60c37883a9c3d411ad96c6e7"), "Kind" : "Mobile", "lang" : "iphone 6s",
"Rating" : 4.2 }
>

FindOne command in MongoDB

findOne command displays the first output based on the filter criteria

db.tech.findOne({Kind:'Mobile'})
{
"_id" : ObjectId("60c37656a9c3d411ad96c6e6"),
"Kind" : "Mobile",
"lang" : "iphone 6s",
"Rating" : 4.2
}
>

Limit command

With the limit command, we can limit the no of documents to be displayed in MongoDB

> db.tech.find().limit(2)
{ "_id" : ObjectId("60c37656a9c3d411ad96c6e6"), "Kind" : "Mobile", "lang" : "iphone 6s",
"Rating" : 4.2 }
{ "_id" : ObjectId("60c37883a9c3d411ad96c6e7"), "Kind" : "Mobile", "lang" : "iphone 6s",
"Rating" : 4.2 }
>

Count command

Count command Counts the number of rows in the query output. Let’s try to find all
documents present in the MongoDB tech database.

> db.tech.find().count()
4

Insert document with different schema in MongoDB

In MongoDB, we can insert data with different schema; we don’t have to stick to any schema
while inserting data in mongo collection.

Let’s insert a document in mongo with a different schema than we inserted earlier.

db.tech.insert({
'Kind': 'Mobile',
'lang': 'iphone 6s',
'Build_year': 2013 ,
'Rating': 4.2
})
Let’s verify if the data gets inserted.

db.tech.find()
{ "_id" : ObjectId("60c37656a9c3d411ad96c6e6"), "Kind" : "Mobile", "lang" : "iphone 6s",
"Rating" : 4.2 }
{ "_id" : ObjectId("60c37883a9c3d411ad96c6e7"), "Kind" : "Mobile", "lang" : "iphone 6s",
"Rating" : 4.2 }
{ "_id" : ObjectId("60c37883a9c3d411ad96c6e8"), "Kind" : "laptop", "lang" : "acer",
"Rating" : 3.9 }
{ "_id" : ObjectId("60c37883a9c3d411ad96c6e9"), "Kind" : "mobile", "lang" : "samsung",
"Rating" : 4 }
{ "_id" : ObjectId("60c38f5ca9c3d411ad96c6ea"), "Kind" : "Mobile", "lang" : "iphone 6s",
"Build_year" : 2013, "Rating" : 4.2 }

Update command in MongoDB

The update command modifies the existing document or documents in a collection. With the
update command, we can modify specific fields of an existing document or replace an
existing document.

This session will see some of the most widely used update commands.

Update a row

Let’s use an update command to update a document where Kind: laptop.

verify the data before updating

> db.tech.find({Kind:'laptop'})
{ "_id" : ObjectId("60c37883a9c3d411ad96c6e8"), "Kind" : "laptop", "lang" : "acer",
"Rating" : 3.9
and run the update command

> db.tech.update({'Kind': 'laptop'},

... { 'Kind': 'laptop',
... 'Brand': 'samsung',
... 'Rating': 4.3
... })
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
Verify if the data gets successfully updated.

> db.tech.find({Kind:'laptop'})
{ "_id" : ObjectId("60c37883a9c3d411ad96c6e8"), "Kind" : "laptop", "Brand" : "samsung",
"Rating" : 4.3 }

Update with upsert

upsert flag in the update command inserted a new document in MongoDB if the document
does not exist.

> db.tech.update({'Kind': 'game'},

... { 'Kind': 'game',
... 'Brand': 'sony',
... 'Rating': 4.3
... }, {upsert: true})
WriteResult({
"nMatched" : 0,
"nUpserted" : 1,
 "nModified" : 0,
"_id" : ObjectId("60c3984464d905a23322abb1")
})
We got nUpserted”: 1 in the output, which means the record was not present in MongoDB,
and the update command has added it as a new document.

Let’s verify the document.

> db.tech.find()
{ "_id" : ObjectId("60c37656a9c3d411ad96c6e6"), "Kind" : "Mobile", "lang" : "iphone 6s",
"Rating" : 4.2 }
{ "_id" : ObjectId("60c37883a9c3d411ad96c6e7"), "Kind" : "Mobile", "lang" : "iphone 6s",
"Rating" : 4.2 }
{ "_id" : ObjectId("60c37883a9c3d411ad96c6e8"), "Kind" : "laptop", "Brand" : "vivo",
"Rating" : 4.3 }
{ "_id" : ObjectId("60c37883a9c3d411ad96c6e9"), "Kind" : "mobile", "lang" : "samsung",
"Rating" : 4 }
{ "_id" : ObjectId("60c38f5ca9c3d411ad96c6ea"), "Kind" : "Mobile", "lang" : "iphone 6s",
"Build_year" : 2013, "Rating" : 4.2 }
{ "_id" : ObjectId("60c3984464d905a23322abb1"), "Kind" : "game", "Brand" : "sony",
"Rating" : 4.3 }

Inc command

With the inc command, we can update the documents filed by specific value. Let’s run this
command and update the build_year by 2.

Document before updating

db.tech.find({Kind:'Mobile'})
{ "_id" : ObjectId("60c37656a9c3d411ad96c6e6"), "Kind" : "Mobile", "lang" : "iphone 6s",
"Rating" : 4.2 }
{ "_id" : ObjectId("60c37883a9c3d411ad96c6e7"), "Kind" : "Mobile", "lang" : "iphone 6s",
"Rating" : 4.2 }
{ "_id" : ObjectId("60c38f5ca9c3d411ad96c6ea"), "Kind" : "Mobile", "lang" : "iphone 6s",
"Build_year" : 2013, "Rating" : 4.2 }
Now, let’s update the document

> db.tech.update({'Build_year': 2013},

... {$inc:{
... Build_year: 2
... }})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
>
Now let’s verify the data after the update

db.tech.find({Kind:'Mobile'})
{ "_id" : ObjectId("60c37656a9c3d411ad96c6e6"), "Kind" : "Mobile", "lang" : "iphone 6s",
"Rating" : 4.2 }
{ "_id" : ObjectId("60c37883a9c3d411ad96c6e7"), "Kind" : "Mobile", "lang" : "iphone 6s",
"Rating" : 4.2 }
{ "_id" : ObjectId("60c38f5ca9c3d411ad96c6ea"), "Kind" : "Mobile", "lang" : "iphone 6s",
"Build_year" : 2015, "Rating" : 4.2 }

Rename command

With rename command, we can update the document filed in the MongoDB collection

db.tech.update({'Build_year': 2015},
{$rename:{
lang: "brand"
}})
verify the data after renamed

> db.tech.find({'Build_year': 2015})

{ "_id" : ObjectId("60c38f5ca9c3d411ad96c6ea"), "Kind" : "Mobile", "Build_year" : 2015,
"Rating" : 4.2, "brand" : "iphone 6s" }
The complete list of update commands can be found here

Delete command in MongoDB

We can delete any document from the mongo collection with the delete command. Let’s
delete the Kind: game from the tech collection

Data before deletion

db.tech.find()
{ "_id" : ObjectId("60c37656a9c3d411ad96c6e6"), "Kind" : "Mobile", "lang" : "iphone 6s",
"Rating" : 4.2 }
{ "_id" : ObjectId("60c37883a9c3d411ad96c6e7"), "Kind" : "Mobile", "lang" : "iphone 6s",
"Rating" : 4.2 }
{ "_id" : ObjectId("60c37883a9c3d411ad96c6e8"), "Kind" : "laptop", "Brand" : "vivo",
"Rating" : 4.3 }
{ "_id" : ObjectId("60c37883a9c3d411ad96c6e9"), "Kind" : "mobile", "lang" : "samsung",
"Rating" : 4 }
{ "_id" : ObjectId("60c38f5ca9c3d411ad96c6ea"), "Kind" : "Mobile", "Build_year" : 2015,
"Rating" : 4.2, "brand" : "iphone 6s" }
{ "_id" : ObjectId("60c3984464d905a23322abb1"), "Kind" : "game", "Brand" : "sony",
"Rating" : 4.3 }
Delete the document

db.tech.remove({"Kind":"game"})
WriteResult({ "nRemoved" : 1 })
Verify the data after the deletion

db.tech.find()
{ "_id" : ObjectId("60c37656a9c3d411ad96c6e6"), "Kind" : "Mobile", "lang" : "iphone 6s",
"Rating" : 4.2 }
{ "_id" : ObjectId("60c37883a9c3d411ad96c6e7"), "Kind" : "Mobile", "lang" : "iphone 6s",
"Rating" : 4.2 }
{ "_id" : ObjectId("60c37883a9c3d411ad96c6e8"), "Kind" : "laptop", "Brand" : "vivo",
"Rating" : 4.3 }
{ "_id" : ObjectId("60c37883a9c3d411ad96c6e9"), "Kind" : "mobile", "lang" : "samsung",
"Rating" : 4 }
{ "_id" : ObjectId("60c38f5ca9c3d411ad96c6ea"), "Kind" : "Mobile", "Build_year" : 2015,
"Rating" : 4.2, "brand" : "iphone 6s" }

Less than command in mongoDB

In MongoDB, we can use less than one command to filter out the document based on filter
criteria. Let’s filter out the Mobile whose rating is less than 4.2

db.tech.find({Rating: {$lt: 4.2}})

{ "_id" : ObjectId("60c37883a9c3d411ad96c6e9"), "Kind" : "mobile", "lang" : "samsung",
"Rating" : 4 }

Less than equal to command in MongoDB

Like less than command, we can use less than equal command in MongoDB to filter out
documents.

db.tech.find({Rating: {$lte: 4.2}})

{ "_id" : ObjectId("60c37656a9c3d411ad96c6e6"), "Kind" : "Mobile", "lang" : "iphone 6s",
"Rating" : 4.2 }
{ "_id" : ObjectId("60c37883a9c3d411ad96c6e7"), "Kind" : "Mobile", "lang" : "iphone 6s",
"Rating" : 4.2 }
{ "_id" : ObjectId("60c37883a9c3d411ad96c6e9"), "Kind" : "mobile", "lang" : "samsung",
"Rating" : 4 }
{ "_id" : ObjectId("60c38f5ca9c3d411ad96c6ea"), "Kind" : "Mobile", "Build_year" : 2015,
"Rating" : 4.2, "brand" : "iphone 6s" }

Greater than command

Let’s filter out the Mobile whose rating is greater than 4.2

db.tech.find({Rating: {$gt: 4.2}})

{ "_id" : ObjectId("60c37883a9c3d411ad96c6e8"), "Kind" : "laptop", "Brand" : "vivo",
"Rating" : 4.3 }

Greater than equal to command

Similar to the greater than command, we can use the greater than equal command in
MongoDB to filter out documents.

db.tech.find({Rating: {$gte: 4.2}})

{ "_id" : ObjectId("60c37656a9c3d411ad96c6e6"), "Kind" : "Mobile", "lang" : "iphone 6s",
"Rating" : 4.2 }
{ "_id" : ObjectId("60c37883a9c3d411ad96c6e7"), "Kind" : "Mobile", "lang" : "iphone 6s",
"Rating" : 4.2 }
{ "_id" : ObjectId("60c37883a9c3d411ad96c6e8"), "Kind" : "laptop", "Brand" : "vivo",
"Rating" : 4.3 }
{ "_id" : ObjectId("60c38f5ca9c3d411ad96c6ea"), "Kind" : "Mobile", "Build_year" : 2015,
"Rating" : 4.2, "brand" : "iphone 6s" }

Result:

Thus document column & graph based data created successfully.

Ex.No: 12 GUI based database application

Aim:

To develop a simple GUI based database application

GUI Application for the Student Management System

Write a program to build a GUI application which provides the details of the college student,
about his course and the fees that need to be paid. The fee is calculated and saved in a text
file. The program must also be able to print the receipt.
Approach: The concept is based on the GUI(graphical user interface). The idea is to use Java
Swing and AWT. Therefore, we need to define all the methods based on functionality. The
methods in the program are as follows:
Receipt: The prime functionality of this method is to display all the data that is entered in the
text fields, the course to be taken and the final amount that needs to be paid. These operations
can be done by adding ActionListeners to the button named as receipt.
Reset: The functionality of this method is to clear the information that is already visible to in
the text areas and in the text fields and to add new student’s details and print the fees of that
particular student. It can be done by adding ActionListeners to the button.
Print: The functionality of this method is to print the fee receipt if the printer is available. We
can do this by adding ActionListener to the button. The entire information in the text area is
named as area2 and is saved in a file in the computer under the name of java.txt. It will also
show a dialog box as Data saved successfully.
Below is the implementation of the above approach:

// Java program to implement a GUI

// application for the student
// management system

import javax.swing.*;
import java.awt.*;
import java.awt.Image;
import java.awt.event.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.print.*;
import javafx.print.Printer;
import java.io.*;
import java.io.IOException;

// Creating the fee class

public class fee extends Frame {
JLabel l1, l2, l3, l4,
l5, l6, l7, l8,
l9, l10, l12, l13,
l14, l11, l15;
JTextField tf1, tf2, tf3,
tf4, tf5, tf6,
tf7, tf8, tf9,
tf10;
JTextArea area2, area1;
JRadioButton rb1, rb2, rb3,
rb4, rb5, rb6,
rb7;
JFileChooser f1;
// Default constructor to
// initialize the parameters
fee()
{
l1 = new JLabel("Fee Report");
l1.setBounds(550, 100, 250, 20);
l2 = new JLabel(
"Name of the Student:");
l2.setBounds(50, 150, 250, 20);
tf1 = new JTextField();
tf1.setBounds(250, 150, 250, 20);
l3 = new JLabel(
"Name of the Father:");
l3.setBounds(50, 200, 250, 20);
tf2 = new JTextField();
tf2.setBounds(250, 200, 250, 20);
l4 = new JLabel("Roll Number:");
l4.setBounds(50, 250, 250, 20);
tf3 = new JTextField();
tf3.setBounds(250, 250, 250, 20);
l5 = new JLabel("Email ID:");
l5.setBounds(50, 300, 250, 20);
tf4 = new JTextField();
tf4.setBounds(250, 300, 250, 20);
l6 = new JLabel("Contact Number:");
l6.setBounds(50, 350, 250, 20);
tf5 = new JTextField();
tf5.setBounds(250, 350, 250, 20);
l7 = new JLabel("Address:");
l7.setBounds(50, 400, 250, 20);
area1 = new JTextArea();
area1.setBounds(250, 400, 250, 90);
l9 = new JLabel("Gender:");
l9.setBounds(50, 500, 250, 20);
JRadioButton r5
= new JRadioButton(" Male");
JRadioButton r6
= new JRadioButton(" Female");
r5.setBounds(250, 500, 100, 30);
r6.setBounds(350, 500, 100, 30);
ButtonGroup bg = new ButtonGroup();
bg.add(r5);
bg.add(r6);
l10 = new JLabel("Nationality:");
l10.setBounds(50, 550, 250, 20);
tf6 = new JTextField();
tf6.setBounds(250, 550, 250, 20);
l11 = new JLabel(
"Year of passing 10th");
l11.setBounds(50, 600, 250, 20);
String language[]
= { "2016", "2015", "2014" };
final JComboBox cb1
= new JComboBox(language);
cb1.setBounds(250, 600, 90, 20);
l12 = new JLabel(
"Year of passing 12th");
l12.setBounds(50, 650, 250, 20);
String languagess[]
= { "2019", "2018", "2017" };
l13 = new JLabel(
"Points Secured in 10th:");
l13.setBounds(50, 700, 250, 20);
tf7 = new JTextField();
tf7.setBounds(250, 700, 250, 20);
l14 = new JLabel("Percentage in 12th:");
l14.setBounds(50, 750, 250, 20);
tf8 = new JTextField();
tf8.setBounds(250, 750, 250, 20);
ImageIcon i2 = new ImageIcon("2.png");
JLabel l15
= new JLabel("", i2, JLabel.CENTER);
l15.setBounds(900, 50, 600, 200);
final JComboBox cb2
= new JComboBox(languagess);
cb2.setBounds(250, 650, 90, 20);
l8 = new JLabel(
"Groups Offered here are:");
l8.setBounds(800, 150, 250, 20);
rb1 = new JRadioButton("SEAS");
rb1.setBounds(550, 150, 100, 30);
rb2 = new JRadioButton("SLABS");
rb2.setBounds(650, 150, 100, 30);
ButtonGroup bg1 = new ButtonGroup();
bg1.add(rb1);
bg1.add(rb2);
rb3 = new JRadioButton("HOSTELLER");
rb3.setBounds(550, 200, 100, 30);
rb4 = new JRadioButton("DAY SCHOLAR");
rb4.setBounds(650, 200, 120, 30);
ButtonGroup bg2 = new ButtonGroup();
bg2.add(rb3);
bg2.add(rb4);
String languages[]
= { "CSE", "ECE", "EEE",
"CIVIL", "MECH" };
final JComboBox cb
= new JComboBox(languages);
cb.setBounds(800, 200, 90, 20);
final JLabel label
= new JLabel();
label.setBounds(600, 430, 500, 30);
JButton b = new JButton("Show");
b.setBounds(1000, 300, 80, 30);
final DefaultListModel<String> li1
= new DefaultListModel<>();
li1.addElement("CSE(2, 50, 000)");
li1.addElement("ECE(2, 50, 000)");
li1.addElement("EEE(2, 50, 000)");
li1.addElement("MECH(2, 50, 000)");
 li1.addElement("CIVIL(2, 50, 000)");
final JList<String> list1
= new JList<>(li1);
list1.setBounds(600, 300, 125, 125);
DefaultListModel<String> li2
= new DefaultListModel<>();
li2.addElement(
"2 SHARE(1, 50, 000)");
li2.addElement(
"3 SHARE(1, 40, 000)");
li2.addElement(
"5 SHARE(1, 20, 000)");
li2.addElement(
"8 SHARE(1, 10, 000)");
li2.addElement(
"bus(40, 000)");
final JList<String> list2
= new JList<>(li2);
list2.setBounds(
800, 300, 125, 125);
JButton Receipt
= new JButton("Generate Receipt");
Receipt.setBounds(600, 490, 150, 30);
JButton b2 = new JButton("Reset");
b2.setBounds(750, 490, 150, 30);
JButton Print = new JButton("Print");
Print.setBounds(900, 490, 150, 30);
area2 = new JTextArea();
area2.setBounds(600, 540, 450, 240);
add(l1);
add(l2);
add(l3);
add(l4);
add(l5);
add(l6);
add(l7);
add(l8);
add(l9);
add(l10);
add(l11);
add(l12);
add(l13);
add(l14);
add(tf1);
add(tf2);
add(tf3);
add(tf4);
add(tf5);
add(tf6);
add(tf7);
add(tf8);
add(area1);
add(area2);
add(l15);
add(rb1);
add(rb2);
add(rb3);
add(rb4);
add(r5);
add(r6);
add(cb);
add(cb1);
add(cb2);
add(list1);
add(list2);
add(b);
add(label);
add(Receipt);
add(b2);
add(Print);
b.addActionListener(new ActionListener() {
// Method to display the data
// entered in the text fields
public void actionPerformed(ActionEvent e)
{
String data = "";
if (list1.getSelectedIndex() != -1) {
data = "You had selected the Group:"
+ list1.getSelectedValue();
label.setText(data);
}
if (list2.getSelectedIndex() != -1) {
data += " and Hostel with the "
+ "facility of: ";
for (Object frame :
list2.getSelectedValues()) {
data += frame + " ";
}
}
label.setText(data);
}
});

// Reset the text fields

b2.addActionListener(
new ActionListener() {
 public void actionPerformed(
ActionEvent e)
{
area2.setText("");
area1.setText(" ");
tf1.setText("");
tf2.setText("");
tf3.setText("");
tf4.setText("");
tf5.setText("");
tf6.setText(" ");
}
});
// Implementing the Print action
Print.addActionListener(
new ActionListener() {
public void actionPerformed(
ActionEvent e)
{
try {
area2.print();
}
catch (java.awt.print
.PrinterException a) {
System.err.format(
"NoPrinter Found",
a.getMessage());
}
}
});

// Generating the receipt

Receipt.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e)
{
area2.setText(
"--------------------------------"
+ "-----------FEE RECEIPT----"
+ "--------------------------"
+ "--------------------------"
+ "-------------------\n");

area2.setText(area2.getText()
+ "Student Name: "
+ tf1.getText()
+ "\n");
area2.setText(area2.getText()
+ "Father's Name: "
+ tf2.getText()
+ "\n");
area2.setText(area2.getText()
+ "RollNumber: "
+ tf3.getText()
+ "\n");
area2.setText(area2.getText()
+ "Email ID: "
+ tf4.getText()
+ "\n");
area2.setText(area2.getText()
+ "Contact Number: "
+ tf5.getText()
+ "\n");
area2.setText(area2.getText()
+ "Wants to take: "
 + cb.getSelectedItem()
.toString()
+ "\n");
if (rb1.isSelected()) {
area2.setText(area2.getText()
+ "Wants to Join in "
+ "School of Engineering "
+ "and Applied Sciences\n");
}
if (rb2.isSelected()) {
area2.setText(area2.getText()
+ "Wants to Join in "
+ "School of Liberal "
+ "Arts and Sciences\n");
}
if (rb3.isSelected()) {
area2.setText(area2.getText()
+ "Wants to be a "
+ "Hosteller \n");
}
if (rb4.isSelected()) {
area2.setText(area2.getText()
+ "Wants to be a "
+ "Day Scholar \n");
}
area2.setText(area2.getText()
+ "Had chosen: "
+ list1.getSelectedValue()
.toString()
+ "\n");
area2.setText(area2.getText()
+ "Had chosen: "
 + list2.getSelectedValue()
.toString()
+ "\n");

int index2 = list2.getSelectedIndex();

if (index2 == 0) {
area2.setText(area2.getText()
+" "
+ "Total amount to be "
+ "paid is 4 Lakhs \n");
}

if (index2 == 1) {
area2.setText(area2.getText()
+" "
+ "Total amount to be paid "
+ "is 3.9 Lakhs \n");
}

if (index2 == 2) {
area2.setText(area2.getText()
+" "
+ "Total amount to be paid "
+ "is 3.8 Lakhs \n");
}

if (index2 == 3) {
area2.setText(area2.getText()
+" "
+ "Total amount to be paid "
+ "is 3.7 Lakhs \n");
}
 if (index2 == 4) {
area2.setText(area2.getText()
+" "
+ "Total amount to be paid "
+ "is 2.9 Lakhs \n");
}

if (e.getSource() == Receipt) {
try {
FileWriter fw
= new FileWriter(
"java.txt", true);
fw.write(area2.getText());
fw.close();
}
catch (Exception ae) {
System.out.println(ae);
}
}

JOptionPane.showMessageDialog(
area2, "DATA SAVED SUCCESSFULLY");
};
});
addWindowListener(
new WindowAdapter() {
public void windowClosing(
WindowEvent we)
{
System.exit(0);
}
 });
setSize(800, 800);
setLayout(null);
setVisible(true);
setBackground(Color.cyan);
}
public static void main(String[] args)
{
new fee();
}
}
Output:

• When the above program is run:

•
• When all the details are added in the respective fields:
•
• When the receipt button is pressed:

•
• When the data is stored in a text file:
•
• When print button is clicked, the following options are shown. If the printer is
available, then it can be directly printed.

•
• Preview of the document if the printer is available:
Result:
Thus a simple GUI based database application developed & executed.
Ex.No:13 Case Study

AIM:

Case Study using any of the real life database applications

A database is to be designed for a Car Rental Co. (CRC). The information

required includes a description of cars, subcontractors (i.e. garages), company
expenditures, company revenues and customers. Cars are to be described by such
data as: make, model, year of production, engine size, and fuel type, number of
passengers, registration number, purchase price, purchase date, rent price and
insurance details. It is the company policy not to keep any car for a period exceeding
one year.

All major repairs and maintenance are done by subcontractors (i.e. franchised
garages), with whom CRC has long-term agreements. Therefore the data about
garages to be kept in the database includes garage names, addresses, range of
services and the like. Some garages require payments immediately after a repair has
been made; with others CRC has made arrangements for credit facilities. Company
expenditures are to be registered for all outgoings connected with purchases, repairs,
maintenance, insurance etc.

Similarly the cash inflow coming from all sources - car hire, car sales,
insurance claims - must be kept of file.CRC maintains a reasonably stable client
base. For this privileged category of customers special credit card facilities are
provided. These customers may also book in advance a particular car. These
reservations can be made for any period of time up to one month. Casual customers
must pay a deposit for an estimated time of rental, unless they wish to pay by credit
card. All major credit cards are accepted. Personal details (such as name, address,
telephone number, driving license, number) about each customer are kept in the
database.
Result:
Thus different case studies are performed.
Content Beyond the Syllabus

Ex.No: 14 Study of Database tools

Aim:
To study about various database tools

The SolarWinds Database Performance Analyzer is a live monitoring tool for databases and
it runs on-premises. The system includes an alerting mechanism that draws the DBA’s attention
to performance issues, such as resource locks or long-running queries. Watching the usage of
the database enables the monitor to identify the reasons why some queries run too long.

The database performance monitoring part of the tool is very similar to the typical behavior
of a network performance monitor. A network performance monitor tracks a list of attributes
and sets thresholds of acceptable status levels. The Database Performance Analyzer applies
this strategy to databases.

The benefit of threshold-based monitoring is that the DBA doesn’t have to sit watching the
live performance graphs on the screen. Instead, admin staff can assume that everything is OK
with the database until otherwise notified. If a status passes a threshold, the Database
Performance Monitor raises an alert. That should draw the attention of technical staff back to
the tool in order to find out what performance limits are being approached, what caused the
problem, and how to head off disaster.
Threshold levels are placed well within the database’s capabilities, so this is not a last-minute
warning. The alerts give the DBA enough time to adjust resources and keep the database
running smoothly. SolarWinds deployed machine learning techniques inside the coding of
the Database Performance Analyzer so the tool adjusts thresholds over time, as it processes
more live performance tracking data. This means that the tool will customize itself to the
specific needs of the organization rather than being a standard tool applied in all
implementations.

The query analysis section of the Database Analyzer will produce recommendations on more
efficient query structures. It can also work out how indexes, views, and other database objects
can be adjusted to improve query response times. This section of the Database Performance
Analyzer also deploys machine learning.

The Database Performance Analyzer is primarily a DBA tool. The greatest value that the tool
provides comes from its alerting system because that enables DBAs to pay attention to other
tasks while the software watches normal activity. The query optimizer tool is also a useful
system to identify code that needs to be reworked, although it probably wouldn’t be suitable
for developers to have direct access to the analyzer – they would only need to see the output of
an analysis of a query. The software runs on Windows Server and Linux.

Pros:

• Highly intuitive DB management system tailored for medium to large size database
implementations
 • Monitors in real-time, offering a number of alert and notification options that can
integrate into popular helpdesk solutions
• Threshold monitoring helps keep teams proactive, and fix issues before they impact
performance
• Dashboards are highly customizable, and be tailored to individuals or teams
• Built-in query analysis helps DBAs build more efficient queries
• Leverages machines learning to identify performance bottlenecks

Cons:

• Could benefit from a longer 30-day trial period

The SolarWinds Database Performance Analyzer installs on Windows Server. It is able to

monitor remote and cloud databases just as easily as onsite instances.

2.DataDog Database Monitoring

Datadog Database Monitoring provides monitoring services for all of the services and
applications that support your user-facing software and that includes databases. This package
is able to link together the interdependencies between services, creating a stack view that
reaches all the way down to the server resources that underpin them.

This monitoring tool is able to track:

• On-premises databases

• Cloud database services

• Big-data and NoSQL systems

The system is able to provide an overview of all of your databases on all sites and cloud
platforms. This gives you an overview of all performance regardless of database management
system brand or the location of those databases.
Key Features:
• Consolidated performance reporting

• Drill down paths

• Dependency mapping

• Resource shortage predictions

Datadog Database Monitoring is a similar service to the SolarWinds tool except that it is a
SaaS package. The Datadog system includes simultaneous automated monitoring for multiple
database instances, which can all be of different DBMSs. This provides alerts if performance
is impaired. The tool also examines database instance interaction with server resources and
identifies the root cause of performance issues.
The combination of monitors for all types of infrastructure means that Datadog is able to show
you database performance in context. Your database is dependent on other systems and
performance problems might not be caused by their internal processes but by underlying
resource shortages or access locks.
The highest plan of Datadog Database Monitoring includes a machine learning process that
is able to work out what services access which resources and spot when demand is likely to use
up full capacity. With this dependency resource requirement, you can head off performance
problems before they get bad enough to be noticed by users.
After quickly ruling out external factors as the cause of database performance problems,
Datadog also provides database query and database structure optimization tools. Adding or
removing indexes could be the answer to your problems and Datadog’s database
analysis system quickly shows you how.
While examining the activities of the database to fulfill the demands of specific requests, you
can simultaneously look beneath the DBMS to identify which server resources each query
impacts.
Once you are confident that the database is finely tuned, you can leave Datadog to perform
ongoing monitoring unattended. A system of thresholds within the monitor will notify you if
a performance statistic is bringing in numbers that exceed your requirements for satisfactory
operations. Alerts can be forwarded as notifications by email or through apps such
as PagerDuty, Jira, Slack, and Webhooks.
Datadog Database Monitoring includes database and query optimization systems as well as
monitoring services. This cloud-based platform will give you an overview of all of your
databases and then let you examine the performance of each database. The system watches all
infrastructure that relates to your databases, making root cause analysis quicker when things
go wrong. The Infrastructure system includes an alerting mechanism that lets you leave the
package to watch over your databases unattended.
Like the SolarWinds tool, this system is for use by DBAs. The query analyzer utility in Datadog
is not as detailed as that available in the SolarWinds system. It will identify queries that perform
badly but won’t provide detailed explanations as to why. So, this tool isn’t for use by code
developers.
Pros:
• System-wide database performance overviews

• Monitors across sites and platforms

• Automated monitoring with alerts and notifications

• Database and query optimization analyzer

• Checks on resources supporting databases

• Identifies a stack of application interdependencies

Cons:
• The free trial period is only two weeks

3. Aqua Data Studio

The Aqua Data Studio from Aquafold is a handy tool for those who collaboratively create
and manage databases and the applications that access data. The tool can be used to
assess existing databases or to create new instances from scratch. Databases are usually
designed with applications in mind and so the Aqua Data Studio supports the simultaneous
development of queries and database structures.
Key Features:
• Database creation workspace

• Database visualization

• Database creation operation

• Query builder

Aquafold Aqua Data Studio is a completely different tool to the two preceding systems. This
package is intended for use by developers. It does have a database instance import system that
also enables ERDs to be adjusted and then applied to the instance in the form of object creation
and alteration scripts. However, these are secondary functions to the main aim of the tool,
which is to support the creation of efficient queries.
Aqua Data Studio can be used to develop and analyze databases on-premises and on the cloud.
The system offers an SSH Terminal to facilitate access to remote servers to access the
database management system and access database objects.
 The DBMSs that Aqua Data Studio can interact with are Oracle, DB2 LUW, DB2 z/OS,
MariaDB, Microsoft SQL Server, MySQL, PostGreSQL, Redshift, Greenplum, ParAccel,
Teradata Aster, Netezza, Hive, Spark, Impala, and Sybase.
You can use the system to administer databases as well as create them. Ongoing performance
analysis lets DBAs check on the response times of databases and adjust resources accordingly.
The development tools in the package include an interactive query builder that helps those
with little knowledge of SQL create data access queries to feed into their new applications. The
service also supports SQL to insert, update, and delete records, keeping an eye on the
performance of indexes.
This system should be used by developers and development project managers. It isn’t meant
for ongoing performance monitoring or management of databases by DBAs. You would get
the best use out of this tool if a new requirement necessitates the creation of new objects as
well as new applications, so you would end up creating new database structures simultaneously
with the construction of queries to input and extract data. This is an on-premises software
package for Windows, Linux, and macOS.
Pros:
• Links together query performance with database structures

• Creates schemas of databases to promote discussion

• Allows access to the same database design to multiple users

• Import or export database structures

• Guided query creation

Cons:
• A SaaS version would be nice to have

RazorSQL
RazorSQL is an extensive suite of SQL management tools that will help you run a relational
database that is accessed with SQL. The suite is able to communicate with a long list of
RDBMSs, including Oracle, MySQL, SQL Server, PostgreSQL, Informix, and Ingres.
Key Features:
• Unifies management access to multiple DBMSs

• Standardized SQL

• Query interpreter

• SQL editor

RazorSQL acts as a GUI frontend for any database instance. It provides a view of all of the
objects in an instance and provides administration functions that enable objects to be added or
altered and it will also assist with migrating or replicating an instance structure. Other admin
tools include a bulk data loader. Developers can use this tool to refer to tables and indexes and
use an editor to build SQL-based programs and scripts.
The interoperability of the tool is particularly useful if you have to deal with several database
types on your site. The SQL command set is not exactly the same for all RDBMSs, so you can
waste time entering the wrong command format when you switch from one system to another.
RazorSQL presents a GUI interface, which it then interprets into commands that get executed
in the database. However, if you want to write your own scripts, the utility includes an SQL
Editor, which will highlight syntax errors.
The package includes just about all of the functions that you will need in order to administer a
database., including backing up data in tables. One thing that is missing from this suite is a
form builder that would enable you to create front ends to give your user community safe and
easy access to the database.
RazorSQL can be used with more than 40 DBMSs and its program editor is able to support
more than 20 programming languages. This is an excellent tool for DBAs and developers.
Ideally, the tool would be used to make minor adjustments to an instance rather than as a system
to build a brand-new database.
Pros:
• Designed specifically for DevOps and DBAs

• Offers syntax highlighting and code completion to help speed up larger projects

• Can generate SQL tables directly from within the tool

• Supports Windows, Linux, and Mac, making it more flexible than some of its
competitors

Cons:
• Interface could use improvement, default layout can get cluttered very quickly

This is a paid tool but is very reasonably priced. You can buy the standard package which
includes a year of updates and support or opt for more expensive packages with longer support
periods. Another dimension in the pricing structure of RazorSQL is the number of people who
will use it.

Result:
Thus various database tools are studied.