Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore

Shri Vaishnav Institute of Information Technology

EXPERIMENT:-8

1. Title:- Write the query to create the views (1 and multiple tables), replace views and
delete views on college database.

2. Outcome:- Must be able to create views on college database.

3. Objectives :- Understand the concept of views in sql.

4. Nomenclature, theory with self-assessment questionnaire:-

4.1 Nomenclature: N/A

4.2Solution:
Views in sql:-
o Views in SQL are considered as a virtual table. A view also contains rows and
columns.
o To create the view, we can select the fields from one or more tables present in the
database.
o A view can either have specific rows based on certain condition or all the rows of
a table.

Creating a view:-
o We can create view using CREATE VIEW statement. A view can be created from
a single table or multiple tables.
Syntax:
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE condition;
4.3 Assumptions:- Not Applicable.
4.4 Dependencies:- Not Applicable.
4.5Code/ Pseudo Code:-
• Query to use college database:-
Mysql> use college;
68 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

• Query to select fees table:-

Mysql> select * from fees;

• Query to create view:-

Mysql> create view myfees as select * from fees;

69 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

• Query to show view:-

Mysql> select * from myfees;

• Query to replace view:-

Mysql> create or replace view feesinfo as select * from fees;

• Query to show replace view:-

Mysql> select * from feesinfo;

• Query to delete view:-

Mysql> drop view feesinfo;

70 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

4.6Results
4.6.1 Test Case
Input:
Output:

Input:
Output:

4.6.2 Result Analysis

[Link] Advantages:-
▪ Reduce Complexity
▪ Increase security
▪ Maintain consistency
▪ Data integrity
[Link] Issues:-
▪
You can’t be created view on temporary tables.
▪
You can not insert if the base table has any not null column that do
not apper in view.
4.7 References: 1. [Link]
2. [Link]

5 Lab Assignment: In this task you have to implement view on college database.
6 Quiz & Viva Questions
6.1Quiz:
• What is a view in a database management system (DBMS)?
a) A physical table that stores data
b) A virtual table derived from one or more tables or views
c) A query used to retrieve data from the database
71 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

d) A backup copy of a table

• Which of the following benefits is NOT provided by views in a DBMS?

a) Simplified data access
b) Data security
c) Data redundancy
d) Data abstraction and independence

• Views can be used to enforce:

a) Data duplication
b) Data inconsistency
c) Data integrity
d) Data fragmentation

• Views in DBMS help in achieving:

a) Better performance by storing data permanently
b) Simplified data manipulation
c) Increased storage space
d) Enhanced data scalability

• Which statement about views is true?

a) Views are physical tables that consume disk space.
b) Views can be updated directly, just like tables.
c) Views are only used for displaying data and cannot be queried.
d) Views retrieve data from the underlying tables based on defined conditions.

6.2 Viva
a. What is a view in a DBMS?
b. How is a view different from a physical table in a database?
c. What are the advantages of using views in a DBMS?
d. How are views used to enforce data security in a database?
e. What is the role of views in achieving data abstraction and independence?

72 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

EXPERIMENT:-6

1. Title:- Write the query for implementing the following aggregate functions: MAX (), MIN
(), AVG (), COUNT (), SUM () on bank database.

2. Outcome:- Must be able to implement aggregate functions.

3. Objectives:- Understand the concept of aggregate functions.

4. Nomenclature, theory with self-assessment questionnaire:-

4.1 Nomenclature:
MAX Maximum

AVG Average

4.2Solution:
• SQL Aggregate Functions:-
o SQL aggregate function is used to perform the calculations on multiple rows
of a single column of a table. It returns a single value.
o It is also used to summarize the data.

SQL Aggregation
Function

MIN
COUNT
SUM AVG MAX

53 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

▪ COUNT Function:-
-COUNT Function is used to Count thee number of rows in a database table. It
can work on both numeric and non-numeric data types.
-COUNT function uses the COUNT(*) that returns the count of all the rows in a
specified table. COUNT(*) considers duplicate and null.
▪ SYNTAX:-
COUNT(*)
Or COUNT([ALL|DISTINCT] expression)
▪ Example:-
SELECT COUNT(*) FROM PRODUCT;
▪ SUM Function:-
-SUM function is used to calculate the sum of all selected columns. It works on
numeric fields only.
▪ SYNTAX:-
SUM()
Or
SUM([ALL|DISTINCT EXPRESSION)
▪ Example:-
SELECT SUM(COST) FROM PRODUCT;
▪ AVG Function:-
-the AVG function is used to calculate the average value of the numeric type.
AVG function returns the average of all non-null values.
▪ SYNTAX:-
AVG()
Or
AVG([ALL|DISTINCT] EXPRESSION)
▪ Example:-
SELECT AVG(COST) FROM PRODUCT;
▪ MAX Function:-
-MAX function is used to find the maximum value of a certain
column. This function determines the largest value of all selected
values of a column.
SYNTAX:-
MAX()
Or
MAX([ALL|DISTINCT] expression)
▪ Example:-
54 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

SELECT MAX(RATE) FROM PRODUCT;

▪ MIN Function:-
-MIN Function is used to find the minimum value of a certain column. This
function determines the smallest value of all selected values of a column.
▪ SYNTAX:-
MIN()
Or
MIN([ALL|DISTINCT] expression)
▪ Example:-
SELECT MIN(RATE) FROM PRODUCT;

4.2 Assumptions:- Not Applicable

4.3 Dependencies:- Not Applicable
4.4Code/ Pseudo Code
• Query to show inserted values:-

• Query to demonstrate use of AVG() function:-

Mysql> select avg(A_Balance) from account;

55 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

• Query to demonstrate use of COUNT() function:-

Mysql> select count(*) from account;

• Query to demonstrate use of MAX() function:-

Mysql> select max(A_Balance) from account;

• Query to demonstrate use of MIN() function:-

Mysql> select min(A_Balance) from account;

56 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

• Query to demonstrate use of SUM() function:-

Select sum(A_Balance) from account;

4.5Results
4.5.1 Test Case
Input:
Output:

Input:
Output:

4.5.2 Result Analysis

[Link] Advantages:- Simplify data analysis, Performance optimization
[Link] Issues:- Difficulty in combining aggregates
4.6 References: 1. [Link]
2. [Link]

5. Lab Assignment:- In this task you have to know how to implement aggregate functions.

6. Quiz & Viva Questions

57 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

6.1Quiz:

• Which of the following set of operations is a valid set of aggregate operations in

SQL?
(a) COUNT, MAX, AVG, SUM
(b) MAX, AVG, SUM, SELECT
(c) UNION, COUNT, MIN, DESC
(d) AVG, MIN, MAX, ASC

• In SQL the function- avg, min, max, sum, count are called as_____.
(a) Aggregate function
(b) Adjunct function
(c) Set operations
(d) Scalar operation

• Which SQL aggregate function is used to retrive minimum value?

(a) Max
(b) Min
(c) Avg
(d) Minimum

• Which aggregate function does not ignore nulls?

(a) Max
(b) Min
(c) Count
(d) Grouping

• Which of the following functions returrn the count of all the rows in a specified table?
(a) COUNT(*)
(b) COUNT(UNIQUE expression)
(c) COUNT(DISTINCT expression)
(d) COUNT(+)
6.2 Viva
• Define aggregate functions?
• Define atomicity and aggregation?
• Explain different types of aggregate functions?
• Explain COUNT() function

58 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

EXPERIMENT:-7

1. Title:- Write the query to implement the concept of different Integrity constraints.
2. Outcome:- Must be able to implement integrity constraints.
3. Objectives:- Understand the concept of different types of integrity constraints.
4. Nomenclature, theory with self-assessment questionnaire:-
4.1 Nomenclature:
Not Null Null values

PK Primary key

4.2Solution:
• Integrity constraints:-
➢ Integrity constraints are a set of rules. It is used to maintain the quality of
information.
➢ Integrity constraints ensure that the data insertion, updating and other processes
have to be performed in such a way that data integrity is not affected.
➢ Thus, integrity constraint is used to guard against accidental demage to the
database.

o Domain Constraint:- Domain integrity constraint contains a certain set of rules

or conditions to restrict the kind of attributes or values a column can hold in the
database table. The data type of a domain can be string, integer, character,
DateTime, currency, etc.

59 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

o Entity Integrity Constraint:- Entity Integrity Constraint is used to ensure that

the primary key cannot be null. A primary key is used to identify individual
records in a table and if the primary key has a null value, then we can't identify
those records. There can be null values anywhere in the table except the primary
key column.

o Referential Integrity Constraint:- Referential Integrity Constraint ensures that

there must always exist a valid relationship between two relational database
tables. This valid relationship between the two tables confirms that a foreign key
exists in a table. It should always reference a corresponding value or attribute in
the other table or be null.

o Key Constraint:- Keys are the set of entities that are used to identify an entity
within its entity set uniquely. There could be multiple keys in a single entity set,
but out of these multiple keys, only one key will be the primary key. A primary
key can only contain unique and not null values in the relational database table.

60 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

4.3 Assumptions:- Not Applicable

4.4 Dependencies:- Not Applicable
4.5Code/ Pseudo Code
Domain Constraints:-

• Query to create database:-

Mysql> create database person;

• Query to create person table:-

Mysql>use person;
Mysql> desc person;

61 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

• Query to insert values:-

Mysql> insert into person values(101, ‘Manisha’, 19);

• Query to show inserted values:-

Mysql> select * from person;

Mysql> insert into person values(105, ‘Sapna’, A);

Entity Integrity Contraints:-

• Query to create person1 table:-

Mysql> use person;
62 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

• Query to show inserted values:-

Mysql> insert into person1 select * from person;

Referential Integrity Constraints:-

• Query to create tables:-

Mysql> use college;
Mysql> desc student2;

63 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

• Query to show inserted values:-

Mysql> select * from student2;

Key Constraints:-

• Query to create person2 table:-

Mysql>use person;

64 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

• Query to insert values:-

Mysql> insert into person2 values(101, ‘Manisha’, 19);

• Query to show inserted values:-

Mysql> select * from person2;

65 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

4.6Results
4.6.1 Test Case
Input: 1
Output:

Input: 2
Output:

4.6.2 Result Analysis

[Link] Advantages:- Data Accuracy and consistency
[Link] Issues:- increased complexity
4.7 References: 1. [Link]
2. [Link]

5. Lab Assignment: In this task you have to implement different types of integrity constraints.
6. Quiz & Viva Questions
6.1Quiz:
• Information ____ is maintained by integrity constraints.
(a) Quantity
(b) Data
(c) Quality
(d) Flow

• A ____ constraint ensures that insertions, updates and other processes are performed
in a manner that does not compromise____.
(a) Data composition
(b) Data interval
(c) Data integrity
(d) Data insertion

• How many types of integrity constraints are there?

66 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

(a) 3
(b) 4
(c) 5
(d) 6

• Which of the following is not a type of integrity constraint?

(a) Domain constraint
(b) Entity integrity constraint
(c) Key constraint
(d) Primary key constraint

• To include integrity constraint in an existing relation use_____.

(a) Drop table
(b) Modify table
(c) Alter table
(d) Create table

6.2 Viva
• What is constraints?
• What is integrity constraints?
• How many types of integrity constraints?
• Define domain integrity constraints?

67 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

EXPERIMENT:-5

1. Title:- Write the queries for Retrieving Data from a Database Using the WHERE clause,
Using Logical Operators in the WHERE clause, Using IN, BETWEEN, LIKE, ORDER BY,
GROUP BY and HAVING Clause, and Combining Tables Using JOINS.

2. Outcome:- Must be able to implement where clause and logical operators.

3. Objectives:- Understand the concept of where clause and logical operators.

4. Nomenclature, theory with self-assessment questionnaire:-

4.1 Nomenclature:
SQL Structured Query Language

DBMS Database Management System

4.2Solution:
• Sql IN Operator:-
o IN is an operator in SQL, which is generally used with a WHERE clause.
o Using the IN operator, multiple values can be specified.
o It allows us to easily test if an expression matches any value in a list of values.
o IN operator is used to replace many OR conditions.
▪ SYNTAX:-
-SELECT columnName FROM tableNmae WHERE columnName IN (Value 1,
Value 2, -…, Value N);
▪ Example:-
-Mysql> SELECT * FROM STUDENT WHERE Hometown IN (“JAIPUR”,
“SIKAR”, “AJMER”);
• Sql BETWEEN Operator:-
o The BETWEEN keyword is an operator in Structured Query Language. It
allows the database users to access the values within the specified range.
o We can easily use BETWEEN operator in the WHERE clause of INSERT,
UPDATE, SELECT and DELETE SQL queries.
▪ SYNTAX:-
-SELECT column_name(s) FROM table_name WHERE column_name
BETWEEN value1 AND value2;
▪ Example:-
43 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

-SELECT Name FROM emp where Salary BETWEEN 30000 AND 45000;

• Sql LIKE Operator:-

o The LIKE is a SQL operator used to search for a particular pattern in each row of
the field. This operator is always used with the WHERE clause in the SQL
statement.
▪ SYNTAX:-
-SELECT column_name1, column_name2,….column_nameN FROM table_name
WHERE column_name LIKE Pattern;
o In Structured Query Language the LIKE operator is used in conjunction with the
following two wildcard characters:-
o Percent Sign(%) :- this sign or wildcard character compares any string with zero,
one, or multiple characters.
▪ SYNTAX:-
-SELECT column_name1, column_name2,….column_nameN FROM table_name
WHERE column_name LIKE ‘M%’;
o Underscore(_) :- this wildcard character in SQL compares any string with a
single character.
▪ SYNTAX:-
-SELECT column_name1, column_name2,….column_nameN FROM table_name
WHERE column_name LIKE ‘M_’;
• Sql ORDER BY Clause:-
o Whenever we want to sort the records based on the columns stored in the tables
of the SQL database, then we consider using the ORDER BY clause in SQL.
o The ORDER BY clause in SQL will help us to sort the records based on the
specific column of a table. This means that all the values stored in the column on
which we are applying ORDER BY clause will be sorted and the corresponding
column values will be displayed in the sequence in which we have obtained the
values in the earlier step.
o Using the ORDER BY clause, we can sort the records in ascending or
descending order as per our requirement. The records will be sorted in ascending
order whenever the ASC keyword is used with ORDER BY clause, DESC
keyword will sort the records in descending order.
o If no keyword is specified after the column based on which we have to sort the
records in that case, the sorting will be done by default in the ascending order.
▪ SYNTAX:-
-SELECT column_name11,…column_nameN FROM table_name ORDER BY
column_name ASC;
▪ Example:-
44 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

-Mysql> SELECT * FROM customers ORDER BY Name ASC;

• Sql GROUP BY clause:-
o In SQL, the GROUP BY statement is used for organizing similar data into
groups. The data is further organized with the help of equivalent function. It
means, if different rows in a precise column have the same values, it will
arrange those rows in a group.
o The SELECT statement is used with the GROUP BY clause in the SQL query.
o WHERE clause is placed before the GROUP BY clause in SQL.
o ORDER BY clause is placed after the GROUP BY clause in SQL.
▪ SYNTAX:-
SELECT column1, function_name(column2)
FROM table_name
WHERE condition
GROUP BY column1 , column2
ORDER BY column1, column2;
Function_name: TABLE name.
▪ Exapmle:-
-SELECT NAME, SUM(SALARY) FROM EMPLOYEE GROUP BY NAME;
• Sql HAVING CLAUSE:-
o The HAVING clause places the condition in the groups defined by the
GROUP BY clause in the SELECT statement.
o The HAVING clause is used to apply a filter on the result of GROUP BY
based on the specified condition.
▪ SYNTAX:-
SELECT column1 function_name(column2)
FROM table_name
WHERE condition
GROUP BY column1, column2
HAVING condition
ORDER BY column1, column2;

4.3 Assumptions:- Not Applicable

4.4 Dependencies:- Not Applicable
4.5Code/ Pseudo Code
• Query to show inserted values:-
Mysql> use employee;

45 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

Mysql> select * from employee;

• Query to display all records of table using where clause:-

Mysql> use employee;

• Query to display selected fields of table using where clause:-

Mysql> select E_ID, E_Dep from employee where E_Salary<15000;

46 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

• Query to display all records from table using logical operators:-

Mysql> select * from employee where E_Add = “Indore”;

• Query to display records of table using between:-

Mysql> select * from employee where E_Salary BETWEEN 400 AND 15000;

• Query to display records of table using IN :-

Mysql> select * from employee where E_Salary IN(8000, 20000);

• Query to display all records of table based on pattern matches:-

LIKE:-

47 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

Mysql> select E_Name from employee where E_Add LIKE “%a%;

NOT LIKE:-
Mysql> select E_Name from employee where E_Add NOT LIKE “In%”;

• Query to display all records using order by clause:-

Mysql> select E_Name, E_Add from employee where E_Add LIKE “%i%” ORDER
BY E_Name;

• Query to display all records using group by clause:-

Mysql> select E_Add, count(E_Add )from employee GROUP BY E_Add;

48 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

• Query to demonstrate use of group by with having clause:-

Mysql> select E_Name, E_Salary from employee group by E_Name having
E_Salary>8000;

• Query to join two tables:-

Mysql>select * from employee;
Mysql>select * from employee natural join department;

• Query to display records using inner join:-

49 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

Mysql> select * from employee inner join department on employee.E_ID =

department.E_ID;

• Query to display records using outer join:-

Mysql> select * from employee left join department on employee.E_ID =
department.E_ID;

4.6 Results:- We have been successfully implement the all the queries.
4.6.1 Test Case
Input: 4
Output:

Input: 7
Output:

4.6.2 Result Analysis

50 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

[Link] Advantages:- High speed and efficient query processing.

[Link] Issues:- Security issue
4.7 References: 1. [Link]
2. [Link]

5. Lab Assignment: In this task you have to implement the where clause and logical
operators.

6. Quiz & Viva Questions

6.1Quiz:
• If and only if a given ____ condition is met, a ____ operation will combine related
tuples from separate relations.?
(a) Join
(b) Return
(c) Date
(d) constraint
• How many types of join operation are there?
(a) 2
(b) 3
(c) 4
(d) 5

• Which among the following is a type of join operation?

(a) Natural join
(b) Outer join
(c) Equi join
(d) All of the above

• How many types of outer joins are there?

(a) 2
(b) 3
(c) 4
(d) 5

• A ___ piece of information can be handled by the outer join operation?

(a) Known
(b) Missing
51 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

(c) Unused
(d) None

6.2 Viva
• What are joins in SQL?
• What are the different types of joins in SQL?
• State the difference between inner join and left join?
• Explain equi join with example?

52 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

EXPERIMENT:-4

1. Title:- Apply the constraints Primary Key, Foreign key, NOT NULL and Unique key to
the tables of bank /college database.

2. Outcome:- Must be able to apply primary key and foreign key concept on the tables.

3. Objectives:- Understand the relationship between primary key and foreign key.

4. Nomenclature, theory with self-assessment questionnaire:-

4.1 Nomenclature:
PK Primary Key

FK Foreign Key

4.2 Solution:
• NOT NULL:-
A NOT NULL constraints in SQL is used to prevent inserting NULL values into the
specified column, considering it as a not accepted value for that column.
• FOREIGN KEY:-
The FOREIGN KEY constraints is used to prevent actions that would destroy links
between tables.
A foreign key is a field(or collection of fields) in one table, that refers to the primary
key in another table.
The table with the foreign key is called the child table and the table with the primary
key is called the referenced or parent table.

4.3 Assumptions:- Not Applicable

4.4 Dependencies:- Not Applicable

4.5Code/ Pseudo Code

• Query to create primary key and not null:-

Mysql>create table student2(S_ID int primary key, S_Name varchar(20) not
null, Age int not null);

37 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

• Query to describe the columns in the table:-

Mysql> create table student2(S_ID int primary key, S_Name varchar(20) not null,
Age int not null);

• Query to insert values:-

Mysql> insert into student2 values(101, ‘Manisha’, 19);

• Query to show inserted values:-

Mysql> select * from student2;

38 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

We have table 1 that is STUDENT2 having S_ID as the primary key, now we
will make this key as Foreign key in tanle 2 FEES.

STUDENT2 Table:-

Creating table2 fees:-

39 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

• Query to create fees table:-

Mysql> create table fees(F_ID int not null primary key, F_Status varchar(20) not
null, S_ID int not null, foreign key(S_ID) REFERENCES student2(S_ID);

• Query to insert values:-

Mysql> insert into fees value(1, ‘paid’, 101);

• Query to show inserted values:-

Mysql> select * from fees;

4.6 Results:- Successfully create primary key and foreign key in sql.
4.6.1 Test Case
40 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

Input:
Output:

Input:
Output:

4.6.2 Result Analysis

[Link] Advantages:- Insure data integrity, consistency, accuracy
[Link] Issues:- May require extra space and processing power to maintain and
enforce.
4.6.3 References: 1. [Link]
2. [Link]

5. Lab Assignment:- In this task you have to know how to implement primary key and
foreign key constraints on the tables.

6. Quiz & Viva Questions

6.1Quiz:

• Which one of the following is a set of one or more attributes taken collectively to
uniquely identify a record?
(a) Candidate key
(b) Super key
(c) Foreign key
(d) None

• Consider attributes ID, CITY and NAME. which one of this can be considered as a
super key?
(a) NAME
(b) ID
(c) CITY
(d) CITY,ID
41 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

• A ______ is a property of the entire relation, rather than of the individual tuples in
which each tuple is unique?
(a) Rows
(b) Key
(c) Attribute
(d) Fields
• A attribute in a relation is a foreign key if the _______ key from one relation is used
as an attribute in that relation?
(a) Candidate
(b) Primary
(c) Super
(d) Sub
• Which one of the following cannot be taken as a primary key?
(a) Id
(b) Register number
(c) Dept_id
(d) Street

6.2 Viva
• What is primary key?
• What is a candidate key?
• What is a foreign key?
• What is a super key?

42 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

EXPERIMENT:-4

1. Title:- Apply the constraints Primary Key, Foreign key, NOT NULL and Unique key to
the tables of bank /college database.

2. Outcome:- Must be able to apply primary key and foreign key concept on the tables.

3. Objectives:- Understand the relationship between primary key and foreign key.

4. Nomenclature, theory with self-assessment questionnaire:-

4.1 Nomenclature:
PK Primary Key

FK Foreign Key

4.2 Solution:
• NOT NULL:-
A NOT NULL constraints in SQL is used to prevent inserting NULL values into the
specified column, considering it as a not accepted value for that column.
• FOREIGN KEY:-
The FOREIGN KEY constraints is used to prevent actions that would destroy links
between tables.
A foreign key is a field(or collection of fields) in one table, that refers to the primary
key in another table.
The table with the foreign key is called the child table and the table with the primary
key is called the referenced or parent table.

4.3 Assumptions:- Not Applicable

4.4 Dependencies:- Not Applicable

4.5Code/ Pseudo Code

• Query to create primary key and not null:-

Mysql>create table student2(S_ID int primary key, S_Name varchar(20) not
null, Age int not null);

37 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

• Query to describe the columns in the table:-

Mysql> create table student2(S_ID int primary key, S_Name varchar(20) not null,
Age int not null);

• Query to insert values:-

Mysql> insert into student2 values(101, ‘Manisha’, 19);

• Query to show inserted values:-

Mysql> select * from student2;

38 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

We have table 1 that is STUDENT2 having S_ID as the primary key, now we
will make this key as Foreign key in tanle 2 FEES.

STUDENT2 Table:-

Creating table2 fees:-

39 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

• Query to create fees table:-

Mysql> create table fees(F_ID int not null primary key, F_Status varchar(20) not
null, S_ID int not null, foreign key(S_ID) REFERENCES student2(S_ID);

• Query to insert values:-

Mysql> insert into fees value(1, ‘paid’, 101);

• Query to show inserted values:-

Mysql> select * from fees;

4.6 Results:- Successfully create primary key and foreign key in sql.
4.6.1 Test Case
40 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

Input:
Output:

Input:
Output:

4.6.2 Result Analysis

[Link] Advantages:- Insure data integrity, consistency, accuracy
[Link] Issues:- May require extra space and processing power to maintain and
enforce.
4.6.3 References: 1. [Link]
2. [Link]

5. Lab Assignment:- In this task you have to know how to implement primary key and
foreign key constraints on the tables.

6. Quiz & Viva Questions

6.1Quiz:

• Which one of the following is a set of one or more attributes taken collectively to
uniquely identify a record?
(a) Candidate key
(b) Super key
(c) Foreign key
(d) None

• Consider attributes ID, CITY and NAME. which one of this can be considered as a
super key?
(a) NAME
(b) ID
(c) CITY
(d) CITY,ID
41 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

• A ______ is a property of the entire relation, rather than of the individual tuples in
which each tuple is unique?
(a) Rows
(b) Key
(c) Attribute
(d) Fields
• A attribute in a relation is a foreign key if the _______ key from one relation is used
as an attribute in that relation?
(a) Candidate
(b) Primary
(c) Super
(d) Sub
• Which one of the following cannot be taken as a primary key?
(a) Id
(b) Register number
(c) Dept_id
(d) Street

6.2 Viva
• What is primary key?
• What is a candidate key?
• What is a foreign key?
• What is a super key?

42 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

EXPERIMENT:-2

1. Title:- Design a Database and create required tables. For Bank Database like CUSTOMER,
LOAN, ACCOUNT, BRANCH & TRANSACTION.

2. Outcome:- Must be able to create bank database and its activities.

3. Objectives:- Understand the relationship between Bank activities and its variable.

4. Nomenclature, theory with self-assessment questionnaire:-

4.1 Nomenclature:
desc Description

DML Data Manipulation Language

4.2Solution:
➢ A database is a collection of organized data that can be easily retrieved, managed,
and updated. The data can be anything that conveys some information.
➢ Tables are created inside a database. You need to first select a database to create a
table in it.

To use a database,

USE<database_name>;

Now create a table inside the database.

CREATE TABLE table_name(column1 datatype, column2 datatype,

column3 datatype, ………);

The column parameters specify the names of the columns of the table. The
datatype parameter sets the type of data the column can hold (e.g., varchar,
integer, etc.).

4.3 Assumptions:- Not Applicable.

4.4 Dependencies:- Not Applicable.

14 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

4.5Code/ Pseudo Code:-

ER Diagram:-

15 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

• Design a Bank Database:-

• Query to show in-built database:-

Mysql>show databases;

• Query to create database:-

Mysql>create database;

• Query to use database:-

Mysql>use bank;

• Tables of Bank Database:-

1) CUSTOMER(C_ID, C_Name, city, contact, DOB, occuption);

• Query to create customer table:-

Mysql>create table customer(C_ID, C_Name, city, contact, DOB, occuption);

16 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

• Query to insert values:-

Mysql>insert into customer values(101, ‘Manisha’, ‘Indore’, 78802679, 20/08/2004,
‘Student’);

• Query to show inserted values:-

Mysql>select * from customer;

17 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

2) LOAN(C_ID, B_ID, L_Amount);

• Query to create loan table:-

Mysql>create table loan(C_ID, B_ID, L_Amount);

• Query to insert values:-

Mysql>insert into loan values(101, 11, 100000);

• Query to show inserted values:-

Mysql>select * from loan;

18 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

3) ACCOUNT(Acc_Number, C_ID, B_ID, AccOpen_Date, A_Type, A_Status,

A_Balance);

• Query to create account table:-

Mysql>create table account(Acc_Number, C_ID, B_ID, AccOpen_Date, A_Type,
A_Status, A_Balance);

• Query to insert values:-

Mysql>insert into account values(12345678, 101, 11, 21/11/2003, ‘Saving’, ‘Active’,
50000);

• Query to show inserted values:-

Mysql>select * from account;

19 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

4) BRANCH(B_ID, B_Name, city, BIFSC_Code);

• Query to create branch table:-

Mysql>create table branch(B_ID, B_Name, city, BIFSC_Code);

• Query to insert values:-

Mysql>insert into branch values(11, ‘HDFC’, ‘Indore’, ‘HDFC000136’);

• Query to show inserted values:-

Mysql>select * from branch;

20 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

5) TRANSACTION(T_ID, T_Date, T_Amount, T_Desc, Balance);

• Query to create transaction table:-

Mysql>create table transaction(T_ID, T_Date, T_Amount, T_Desc, Balance);

• Query to insert values:-

Mysql>insert into transaction values(101, ’20 Aug’, 799, ‘Mobile Recharge’, 50000);

• Query to show inserted values:-

Mysql>select * from transaction;

21 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

4.6 Results:- The database Bank and table related to it have been created.
4.6.1 Test Case
Input:
Output:

Input:
Output:

4.6.2 Result Analysis

[Link] Advantages:- Database improves security,
Expanded end-client efficiency

[Link] Issues:- Multiuser DBMS can be more expensive

Damage to database affects virtually all applications
4.7 References: 1. [Link]
2. [Link]

5. Lab Assignment: In this task you have to implement Bank Database in MySQL and
create related tables using SQL queries.

6. Quiz & Viva Questions

6.1 Quiz:

• Which of the following is(are) logical database structure?

22 | P a g e
 Shri Vaishnav Vidyapeeth Vishwavidyalaya, Indore
Shri Vaishnav Institute of Information Technology

(a) Network
(b) Tree
(c) Chain
(d) All of these
• A database management system(DBMS) is a?
(a) Hardware system used to create, maintain and provide controlled access to a
database
(b) Hardware system used to create, maintain and provide uncontrolled access to a
database.
(c) Software system used to create, maintain and provide uncontrolled access to a
database.
(d) Software system used to create, maintain and provide controlled access to a
database.

• Which of the following is a command of DDL?

(a) Alter
(b) Delete
(c) Create
(d) Both a and c

• What is rows of a relation known as?

(a) Degree
(b) Entity
(c) Tuple
(d) None

• For performing tasks like creating the structure of the relations, deleting relation,
which of the following is used?
(a) Data definition language
(b) Data derivation language
(c) Dynamic data language
(d) Detailed data language

6.2 Viva
• What are the types of databases?
• What is the use of DBMS?
• What is a relation schema?
• What is an entity
23 | P a g e
 SHRI VAISHNAV VIDHYAPEETH VISHWAVIDYALAYA
SHRI VAISHNAV INSTITUTE OF INFORMATION TECHNOLOGY

EXPERIMENT 6
AIM :
To create a Java Servlet program for session tracking.
THEORY :
Session Tracking is a mechanism used to maintain the state of a user across multiple
requests.
HTTP is a stateless protocol, which means it does not remember previous interactions.
Session tracking helps to identify a user and store user-specific data across multiple pages.
HttpSession is an interface provided by Java Servlet API that:
>Creates a session for each user >Stores user data as attributes
>Tracks user activity
SOURCE CODE :
 SHRI VAISHNAV VIDHYAPEETH VISHWAVIDYALAYA
SHRI VAISHNAV INSTITUTE OF INFORMATION TECHNOLOGY

OUTPUT :

VIVA QUESTIONS :
Q1. What is session tracking?
Session tracking is a technique used to maintain user data across multiple requests.
Q2. Why is session tracking needed?
Because HTTP is stateless and cannot remember previous user interactions.
Q3. What is HttpSession?
HttpSession is an interface used to create and manage user sessions in servlets.
Q4. What are different session tracking techniques?
Cookies, URL Rewriting, Hidden Fields, and HttpSession.
Q5. What is session ID?
A unique identifier assigned to each user session.
 SHRI VAISHNAV VIDHYAPEETH VISHWAVIDYALAYA
SHRI VAISHNAV INSTITUTE OF INFORMATION TECHNOLOGY

EXPERIMENT 5
AIM :
WAP to create a simple Java Servlet that generates plain text
THEORY :
A Servlet is a Java program that runs on a web server and is used to handle client requests
and generate dynamic web content.
Servlets are mainly used to develop web applications in Java. They run inside a Servlet
Container such as Apache Tomcat.
The main features of Servlets include:

• Handling client requests

• Generating dynamic responses
• Processing form data

• Communicating with databases

SOURCE CODE :
 SHRI VAISHNAV VIDHYAPEETH VISHWAVIDYALAYA
SHRI VAISHNAV INSTITUTE OF INFORMATION TECHNOLOGY

OUTPUT :

VIVA QUESTIONS :
Q1. What is a Servlet?
A Servlet is a Java program that runs on a web server to handle client requests and generate
dynamic web content.

Q2. What is the use of HttpServlet class?

HttpServlet is used to create HTTP-based servlets that handle web requests.

Q3. What is the role of doGet() method?

The doGet() method handles HTTP GET requests from the client.

Q4. What is the use of PrintWriter in Servlet?

PrintWriter is used to send output from the servlet to the client browser.

Q5. What is a Servlet container?

A Servlet container is a component of a web server that manages servlets. Example: Apache
Tomcat.
 SHRI VAISHNAV VIDHYAPEETH VISHWAVIDYALAYA
SHRI VAISHNAV INSTITUTE OF INFORMATION TECHNOLOGY

EXPERIMENT 2
AIM : WAP to execute SELECT query using JDBC.
THEORY :
JDBC (Java Database Connectivity) is an API that allows Java programs to interact with
databases. Using JDBC, a Java application can connect to a database, execute SQL queries,
and retrieve results.
To execute a SELECT query, the following steps are required:
1. Load the database driver
2. Establish connection with the database
3. Create a statement
4. Execute the SELECT query
5. Process the result
6. Close the connection
In this program, a SELECT query is used to fetch records from the student table and display
them on the console. And try with resource feature is used to automatically close resources
like database connections and statements after use. even if an exception occurs. It reduces
code complexity and prevents resource leaks.
SOURCE CODE :

TANIYA YADAV 24100BTAIMLM17357

 SHRI VAISHNAV VIDHYAPEETH VISHWAVIDYALAYA
SHRI VAISHNAV INSTITUTE OF INFORMATION TECHNOLOGY

OUTPUT :

VIVA QUESTIONS :
1. What is JDBC?
JDBC is an API that allows Java programs to connect and interact with databases.
2. Which method is used to establish a database connection?
[Link]() is used to establish a connection.
3. What is the use of Statement interface?
Statement is used to execute SQL queries in Java.
4. What is ResultSet?
ResultSet stores the data returned by a SELECT query.
5. Why is [Link]() used?
[Link]() moves the cursor to the next row of the ResultSet.

TANIYA YADAV 24100BTAIMLM17357

 SHRI VAISHNAV VIDHYAPEETH VISHWAVIDYALAYA
SHRI VAISHNAV INSTITUTE OF INFORMATION TECHNOLOGY

EXPERIMENT 4
AIM : WAP to create a simple student login page using HTML
SOURCE CODE

TANIYA YADAV 24100BTAIMLM17357

 SHRI VAISHNAV VIDHYAPEETH VISHWAVIDYALAYA
SHRI VAISHNAV INSTITUTE OF INFORMATION TECHNOLOGY

OUTPUT :

TANIYA YADAV 24100BTAIMLM17357

[Type here]

SHRI VAISHNAV INSTITUTE OF INFORMATION TECHNOLOGY

EXPERIMENT 3
AIM : WAP to update student information.
THEORY :
JDBC is an API used to connect Java applications with databases. It allows execution of SQL
queries such as SELECT, INSERT, UPDATE, and DELETE.

To update records in a database, the UPDATE SQL statement is used.

In JDBC, PreparedStatement is preferred for executing update queries because:

• It improves performance
• It prevents SQL injection
• It allows passing values dynamically
In this program, the student’s marks are updated based on student ID using a prepared
statement.
SOURCE CODE :

TANIYA YADAV 24100BTAIMLM17357

SHRI VAISHNAV VIDHYAPEETH VISHWAVIDYALAYA

SHRI VAISHNAV INSTITUTE OF INFORMATION TECHNOLOGY

 SHRI VAISHNAV VIDHYAPEETH VISHWAVIDYALAYA
SHRI VAISHNAV INSTITUTE OF INFORMATION TECHNOLOGY

OUTPUT :

VIVA QUESTIONS :
1. What is PreparedStatement?
PreparedStatement is an interface used to execute parameterized SQL queries.
2. Which SQL command is used to update records?

The UPDATE command is used to modify existing records.

3. What does executeUpdate() return?
It returns the number of rows affected by the query.
4. Why is PreparedStatement preferred over Statement?

Because it is faster, secure, and supports dynamic values.

5. What is the use of setInt() method?
It assigns integer values to the placeholders in the SQL query.

TANIYA YADAV 24100BTAIMLM17357

 SHRI VAISHNAV VIDHYAPEETH VISHWAVIDYALAYA
SHRI VAISHNAV INSTITUTE OF INFORMATION TECHNOLOGY

EXPERIMENT 1
AIM :
To WAP for creating text box ,check box, radio button ,list, submit button.
THEORY :
A Graphical User Interface (GUI) allows users to interact with a program using visual
components instead of typing commands. Java supports GUI programming mainly through
AWT (Abstract Window Toolkit) and Swing.
AWT provides basic GUI components like buttons, text fields, check boxes, labels, lists, and
frames. These components help in building interactive window-based applications.
In this program, GUI components are placed inside a main window.
Each component performs a specific task:
• Text Box is used to enter text input.
• Check Box allows multiple selections.

• Radio Button allows only one selection from a group.

• List displays selectable items.
• Submit Button is used to perform an action when clicked.
Event handling is used so that when the Submit button is clicked, the entered information is
displayed.
SOURCE CODE :

TANIYA YADAV 24100BTAIMLM17357

 SHRI VAISHNAV VIDHYAPEETH VISHWAVIDYALAYA
SHRI VAISHNAV INSTITUTE OF INFORMATION TECHNOLOGY

TANIYA YADAV 24100BTAIMLM17357

 SHRI VAISHNAV VIDHYAPEETH VISHWAVIDYALAYA
SHRI VAISHNAV INSTITUTE OF INFORMATION TECHNOLOGY

OUTPUT :

VIVA QUESTIONS :
1. What is GUI?
A GUI (Graphical User Interface) allows users to interact with applications using graphical
components like buttons, text boxes, and lists.
2. What is the use of TextField / JTextField?
It is used to accept single-line text input from the user.
3. Difference between Check Box and Radio Button?

• Check Box allows multiple selections

• Radio Button allows only one selection from a group
4. Why is ButtonGroup used?
ButtonGroup is used to group radio buttons so that only one radio button can be selected at
a time.
5. What is event handling in Java GUI?

Event handling is the mechanism that handles user actions like button clicks using listeners
such as ActionListener.

TANIYA YADAV 24100BTAIMLM17357