Page |1

EXPERIMENT-1
1) Create a table STUDENT with appropriate data types and perform the follow-
ing queries. Attributes are Roll number, student name, date of birth, branch
and year of study
AIM: To Create a table STUDENT with appropriate data types and perform the
following queries.
DESCRIPTION:
CREATE:
CREATE TABLE table_name(
column1 datatype,column2 datatype,.... );

INSERT:
INSERT INTO table_name
VALUES (value1, value2, value3, ...);

SELECT:
SELECT column1, column2, ...
FROM table_name;

UPDATE:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

DELETE:
DELETE FROM table_name
WHERE condition;

LIKE:
The LIKE operator is used in a WHERE clause to search for a specified pattern in a
column.
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

REVERSE:
The REVERSE() function reverses a string and returns the result.
SELECT REVERSE('SQL Tutorial');
 Page |2

SOURE CODE:
a) Insert 5 to 10 rows in a table?

b) List all the students of all branches

c) List student names whose name starts with ‘s ‘

d) List student names whose name contains ‘s as third literal

 Page |3

e) List student names whose contains two ‘s ‘anywhere in the name

f) List students whose branch is NULL

g) List students of AIDS & ECE who born after 1980

h) List all students in reverse order of their names

i) Delete students of any branch whose name starts with ‘s ‘

j) Update the branch of ECE students to AIDS

 Page |4

RESULT: EXECUTED SUCESSFULLY

 Page |5

EXPERIMENT-2
2) Create the following tables based on the above Schema Diagram with appropriate
data types and constraints and perform the following queries.
SAILORS (Sail id, Sailname, Rating, Age)
RESERVES (Sailid, boatid, Day)
BOATS (Boatid, Boat-name, Color)

AIM: To Create the following tables based on the above Schema Diagram with appro-
priate data types and constraints and perform the following queries.
DESCRIPTION:
AND:
The AND operator displays a record if all the conditions separated by AND are TRUE.
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
OR:
The OR operator displays a record if any of the conditions separated by OR is TRUE.
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
UNION:
The UNION operator is used to combine the result-set of two or more SELECT state-
ments.
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
SELECT DISTINCT:
The SELECT DISTINCT statement is used to return only distinct (different) values.
SELECT DISTINCT column1, column2, ...
FROM table_name;
 Page |6

SOURCE CODE:

a) Insert 5 to 10 rows in all tables

b) Find the name of sailors who reserved boat number 3.

c) Find the name of sailors who reserved green boat.

d) Find the colors of boats reserved by Ramesh.

e)Find the names of sailors who have reserved atleast one boat
 Page |7

f) Find the all sailed ofs ailors who have a rating of 10 or have reserved boated
104

g) Find the Sailid ‘s of sailors with age over 20 who have not registered a red
boat.

h) Find the names of sailors who have reserved a red or green boat.

i) Find sailors whose rating is better than some sailor called Salvador.

j) Find the names of sailors who are older than the oldest sailor with a rating of
10.

RESULT: EXECUTED SUCESSFULLY

 Page |8

EXPERIMENT-3
3) Schema Diagram for the rest of the SQL and PLSQL Programs. Create the fol-
lowing tables based on the above Schema Diagram with appropriate data types
and constraints.
EMPLOYEE (Fname, Mname, Lname, SSN, Bdate, Address, Gender, Salary,
SuperSSN,DnO,)
DEPARTMENT (Dnumber, Dname, MgrSSN, Mgrstartdate)
DEPENDENT (ESSN, Dependent_Name, Gender, Bdate, Relationship)

AIM: To Create the following tables based on the above Schema Diagram with appropri-
ate data types and constraints.
DESCRIPTION:
IN:
The IN command allows you to specify multiple values in a WHERE clause.
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

NOT IN:

The NOT operator displays a record if the condition(s) is NOT TRUE.

SELECT column1, column2, ...

FROM table_name
WHERE NOT condition;
 Page |9

a) Insert 5 to 10 rows into all the tables

 P a g e | 10

b) Display all employees’ names along with their department names.

c) Display all employees’ names along with their dependent details.

d) Display name and address of all employees who work for Research depart-
ment.

e) List the names of all employees with two or more dependents.

 P a g e | 11

f) List the names of employee who have no dependents

g) List the names of employees who have at least one dependent.

h) List the names of the employees along with names of their supervisors using
aliases.

i) Display name of the department and name of manager for all the departments.

j) Display the name of each employee who has a dependent with the same first
name and gender as the employee.

RESULT: EXECUTED SUCESSFULLY

 P a g e | 12

EXPERIMENT-4
4) Create the following tables based on the above Schema Diagram with appropri-
ate data types and constraints in addition to the tables in Experiment 2.
DEPT_LOCATIONS (Dnumber, Dloaction)
PROJECT (Pname, Pnumber, Plocation, Dnum)
WORKS_ON (ESSN, Pno, Hours)

AIM: To Create the following tables based on the above Schema Diagram with appro-
priate data types and constraints and perform the following queries.
DESCRIPTION:
GROUP BY:
The GROUP BY statement is often used with aggregate function (
COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more col-
umns.
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
SOURCE CODE:

a) Insert 5 to 10 rows into all the tables.

 P a g e | 13

b) Find the names of the employees who work on all the projects controlled by the
department Research.

c) List the project number, name and no. Of employees who work on that project
for all the projects.

d) List the names of all the projects controlled by the departments department
wise.
 P a g e | 14

e) Retrieve the names of employees who work on all projects that tharun works
on.

f) List the project numbers for projects that involve an employee either as worker
or as a manager of the department that controls the project.

g) List the names of all employees in one department who work more than 10
hours on one specific project.

h) For each project, list the project name and total hours (by all employees) spent
on that project.

i) Retrieve the names of all employees who work on every project

j) Retrieve the names of all employees who do not work on any project.

RESULT: EXECUTED SUCESSFULLY

 P a g e | 15

EXPERIMENT-5
5) Create a view that has project name, controlling department name, number of
employees and total hours worked on the project for each project with more than
one employee working on it.
AIM: To create a view that has project name, controlling department name, number of
employees and total hours worked on the project for each project with more than one
employee working on it.
DESCRIPTION:
VIEW:
A view contains rows and columns, just like a real table. The fields in a view are
fields from one or more real tables in the database.
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
SOURCE CODE:

a) List the projects that are controlled by one department from this view.

b) List the managers of the controlling departments for all the projects.

c) Demonstrate one update operation on this view.

A) UPDATION OF VIEW CAN’T BE DONE IF THAT VIEW CONTAINS ANY
PRIMARY OR FOREIGN KEYS.
 P a g e | 16

d) List the Location of the controlling departments for all the projects.

e) Retrieve the data from the view.

RESULT: EXECUTED SUCESSFULLY

 P a g e | 17

EXPERIMENT-6
6) Create a view emp from employee such that it contains only emp_no, emp_name
and department.
AIM: To create a view emp from employee such that it contains only
emp_noemp_name and department.
SOURCE CODE:

RESULT: EXECUTED SUCESSFULLY

 P a g e | 18

EXPERIMENT-7
7) Create a view dept from department with only dept_no and location.
AIM: To create a view dept from department with only dept_no and location.
SOURCE CODE:

RESULT: EXECUTED SUCESSFULLY

 P a g e | 19

EXPERIMENT-8
8) Create a view that contains the details of employees who are managers only
AIM: To create a view that contains the details of employees who are managers only
SOURCE CODE:

RESULT: EXECUTED SUCESSFULLY

 P a g e | 20

EXPERIMENT-9
9) Write a procedure to check whether the given number is Armstrong or not.
AIM: To write a procedure to check whether the given number is Armstrong or not.
DESCRIPTION:
DELIMITER:
Delimiters can be used when you need to define the stored procedures, function as well as to create triggers.
The default delimiter is semicolon. You can change the delimiters to create procedures and so on. However,
but if you are considering multiple statements, then you need to use different delimiters like $$ or //.
SOURCE CODE:

RESULT: EXECUTED SUCESSFULLY

 P a g e | 21

EXPERIMENT-10
10)Write a procedure which accept the account number of a customer and re-
trieve the balance
AIM: To write a procedure which accept the account number of a customer and re-
trieve the balance.
SOURCE CODE:

RESULT: EXECUTED SUCESSFULLY

 P a g e | 22

EXPERIMENT-11
11) Write a procedure which accepts the student number and displays the depart-
ment in which he belongs to.
AIM: To write a procedure which accepts the student number and displays the depart-
ment in which he belongs to.
SOURCE CODE:

RESULT: EXECUTED SUCESSFULLY

 P a g e | 23

EXPERIMENT-12
12) Create a cursor to modify the salary of all employees belonging to 'Research' de-
partment by 150%.
AIM: To create a cursor to modify the salary of all employees belonging to 'Research'
department by 150%.
DESCRIPTION:
CURSOR:
Cursor is a Temporary Memory or Temporary Work Station. It is Allocated by Database
Server at the Time of Performing DML (Data Manipulation Language) operations on
Table by User. Cursors are used to store Database Tables. There are 2 types of Cursors:
Implicit Cursors, and Explicit Cursors. These are explained as following below.
Implicit Cursors:
Implicit Cursors are also known as Default Cursors of SQL SERVER. These Cursors
are allocated by SQL SERVER when the user performs DML operations.
Explicit Cursors:
Explicit Cursors are Created by Users whenever the user requires them. Explicit Cur-
sors are used for Fetching data from Table in Row-By-Row Manner.
Declare Cursor Object.
Syntax: DECLARE cursor _ name CURSOR FOR SELECT * FROM table _ name
SOURCE CODE:

RESULT: EXECUTED SUCESSFULLY

 P a g e | 24

EXPERIMENT-13
13) Consider the college database. Retrieve all students who have registered for a
specific course and store their details into another table using Cursors.
AIM: To retrieve all students who have registered for a specific course and store their
details into another table using Cursors.
SOUREC CODE:

RESULT: EXECUTED SUCESSFULLY

 P a g e | 25

EXPERIMENT-14
14) Write an update trigger on Account table. The system should keep track of the
records that are being updated.
AIM: To update trigger on Account table. The system should keep track of the records
that are being updated.
DESCRIPTION:
TRIGGER:
Trigger: A trigger is a stored procedure in database which automatically invokes when-
ever a special event in the database occurs. For example, a trigger can be invoked when
a row is inserted into a specified table or when certain table columns are being updated.
Syntax:
create trigger [trigger _name]
[before | after]
{insert | update | delete}
on [table _name]
[for each row]
[trigger _body]
SOURCE CODE:
 P a g e | 26

RESULT: EXECUTED SUCESSFULLY

 P a g e | 27

15) Create NOSQL database for a sample application and perform CRUD opera-
tions.
To install MongoDB:
sudo apt install mongodb
To see databases:
su show
dbs sudo mongo
To create:
use database_name
To insert:
db.std.insert({sno:14,name:”Rishi”, Rishi branch:”AI&DS”})
db.std.find()
({(“id”: objected)” sno”:”14”})
db.sd.find().pretty()
{
id =”14”
name = “Rishi”
branch = “AI&DS”
}
To insert many collections:
db.std.insertmany({sno:”04”, sname:”Sankar”, branch:”AI&DS”},
{id=”104”, sno:”300”,sname:”Rahul”})
To update:
db.std.update({sno;”500”},{$set;{sname:”some”}})
To delete:
db.std.deleteOne({sno:”04”})
To Drop database:
{dropped: “database_name”, ”ok”,:1}
RESULT: EXECUTED SUCESSFULLY
 P a g e | 28

VIVA VOICE
1)Define Database.
A prearranged collection of figures known as data is called database.
2) What is DBMS?
Database Management Systems (DBMS) are applications designed especially which
enable user interaction with other applications.
3) What are the various kinds of interactions catered by DBMS?
The various kind of interactions catered by DBMS are:
Data definition
Update
Retrieval
Administration
4) Segregate database technology’s development.
The development of database technology is divided into:
Structure or data model
Navigational model
SQL/ relational model
5) Who proposed the relational model?
Edgar F. Codd proposed the relational model in 1970.
6) What are the features of Database language?
A database language may also incorporate features like:
DBMS-specific Configuration and management of storage engine
Computations to modification of query results by computations, like summing, count-
ing, averaging, grouping, sorting and cross-referencing Constraint enforcement Appli-
cation programming Interface
7) What do database languages do?
As special-purpose languages, they have:
Data definition language
Data manipulation language
Query language
8) Define database model.
 P a g e | 29

A data model determining fundamentally how data can be stored, manipulated and
Organised and the structure of the database logically is called database model.
9) What is SQL?
Structured Query Language (SQL) being ANSI standard language updates database
and commands for accessing.
10) Enlist the various relationships of database.
The various of database are:
One-to-one: Single table having drawn relationship with another table having simi-
lar kind
of columns.
One-to-many: Two tables having primary and foreign key relation.
Many-to-many: Junction table having many tables related to many tables.
11) Define Normalization.
Organized data void of inconsistent dependency and redundancy within a database is
called normalization.
12) Enlist the advantages of normalizing database.
Advantages of normalizing database are:
No duplicate entries
Saves storage space
Boasts the query performances.
13) Define Denormalization.
Boosting up database performance, adding of redundant data which in turn helps rid of
Complex data is called denormalization.
14) Define DDL and DML.
Managing properties and attributes of database is called Data Definition Language
(DDL).
Manipulating data in a database such as inserting, updating, deleting is defined as Data
Manipulation Language. (DML)
15) Enlist some commands of DDL.
They are:
CREATE:
 P a g e | 30

Create is used in the CREATE TABLE statement. Syntax is:

CREATE TABLE [column name] ( [column definitions] ) [ table parameters]
ALTER:
It helps in modification of an existing object of database. Its syntax is:
ALTER object type object name parameters.
DROP:
It destroys an existing database, index, table or view. Its syntax is:
DROP object type object name.
16) Define Union All operator and Union.
Full recordings of two tables is Union All operator.
A distinct recording of two tables is Union.
17) Define cursor.
A database object which helps in manipulating data row by row representing a result
set is called cursor.
18) Enlist the cursor types.
They are:
Dynamic: it reflects changes while scrolling.
Static: doesn’t reflect changes while scrolling and works on recording of snapshot.
Keyset: data modification without reflection of new data is seen.
19) Enlist the types of cursors.
The types of cursor are:
Implicit cursor: Declared automatically as soon as the execution of SQL takes place
without the awareness of the user.
Explicit cursor: Defined by PL/ SQL which handles query in more than one row.
20) Define sub-query.
A query contained by a query is called Sub-query.
21) Why is group-clause used?
Group-clause uses aggregate values to be derived by collecting similar data.
22) Compare Non-clustered and clustered index
Both having B-tree structure, non-clustered index has data pointers enabling one table
many non-clustered indexes while clustered index is distinct for every table.
 P a g e | 31

23) Define Aggregate functions.

Functions which operate against a collection of values and returning single value is
called aggregate functions
24) Define Scalar functions.
Scalar function is depended on the argument given and returns sole value.
25) What restrictions can you apply when you are creating views?
Restrictions that are applied are:
Only the current database can have views.
You are not liable to change any computed value in any particular view.
Integrity constants decide the functionality of INSERT and DELETE.
Full-text index definitions cannot be applied.
Temporary views cannot be created.
Temporary tables cannot contain views.
No association with DEFAULT definitions.
Triggers such as INSTEAD OF is associated with views.
26) Define “correlated subqueries”.
A „correlated subquery‟ is a sort of sub query but correlated subquery is reliant on an-
other query for a value that is returned. In case of execution, the sub query is executed
first and then the correlated query.
27) Define Data Warehousing.
Storage and access of data from the central location in order to take some strategic de-
cision is called Data Warehousing. Enterprise management is used for managing the
information whose framework is known as Data Warehousing.
28) Define Join and enlist its types.
Joins help in explaining the relation between different tables. They also enable you to
select data with relation to data in another table.
The various types are:
INNER JOINs: Blank rows are left in the middle while more than equal to two ta-
bles are joined.
OUTER JOINs: Divided into Left Outer Join and Right Outer Join. Blank rows are
left at the specified side by joining tables in other side.
 P a g e | 32

Other joins are CROSS JOINs, NATURAL JOINs, EQUI JOIN and NON-EQUI
JOIN.
29. What is the role of view in DBMS?
Ans. A database view is a subset of a database and is based on a query that runs on
one or more database tables. Database views are saved in the database as named que-
ries and can be used to save frequently used, complex queries.
30. What are types of Views in DBMS?
Ans. There are two types of database views: dynamic views and static views. Dynamic
views can contain data from one or two tables and automatically include all of the col-
umns from the specified table or tables.
31. What are the advantages of Views?
•Views can represent a subset of the data contained in a table. ...
•Views can join and simplify multiple tables into a single virtual table.
•Views can act as aggregated tables, where the database engine aggregates data (sum,
average, etc.) ...
•Views can hide the complexity of data.
32. Why update is not possible on Complex views?
Ans. Complex View Contains Multiple Tables Data hence it is not possible to per-
form DML command on this view.
33. Can we create a view based on other views?
Ans. A view can refer to tables or views in other databases by qualifying the table or
view name with the appropriate database name.
34. What is meant by Delimiter?
Ans. Delimiters are used when we need to define the stored procedures as well as to
create triggers.
35. What is a Procedure?
Ans. A procedure is a subroutine (like a subprogram) in a regular scripting language,
stored in a database.
36. What is the process to create a procedure?
1. The name of the procedure must be specified after the Create Procedure keyword
 P a g e | 33

2.After the name of the procedure, the list of parameters must be specified in the pa-
renthesis. The parameter list must be comma-separated
3.The SQL Queries and code must be written between BEGIN and END keywords
37. What is the syntax for while loop?
[begin _label:] WHILE condition DO
Statement _list
END WHILE [end _label]
38. What is meant by Armstrong number?
Ans. An Armstrong number is a number such that the sum! of its digits raised to the
third power is equal to the number! itself.
39. How the statements written while using procedures in MYSQL?
Ans. The MYSQL Queries and code must be written between BEGIN and END key-
words while using Procedures.
40. What is the syntax to call procedure?
Ans. “call procedure _name(value)”
41. How do we retrieve the data using procedure?
Ans. A stored procedure does not have a return value so we use “call” function.
42. How many modes are available to pass parameters?
Ans. IN, OUT, INOUT
43. What is the task of IN mode?
Ans. If you want to pass the value to the value to the subprogram then like a constant
you can assign a value by using IN mode.
44. What do you about stored functions?
Ans. You can call it also User Function or User Defined Function. Simply it is a set of
PL/SQL statements that you can call by name. They are stored and compiled in a data-
base. This function will return a value to the environment in which it is called.
45. What is the use of OUT mode?
Ans. Once we expect return values to the called subprograms, you can use the OUT
method to initialize the assigned variable.
46. Tell me the use of INOUT mode?
Ans. It will help pass the values to the subprogram called and return the caller value.
 P a g e | 34

47. What is the common feature between Function and Procedure?

Ans. A Database can be a procedure within a function.
49. Tell me how you can recompile stored procedures at runtime?
Ans: At the time of creating stored procedures with the recompile option, it will be ex-
ecuted newly every time. This is very good for high performances. Every time perfect
variables are passed.
50. What is the syntax to declare cursors?
Ans.
DECLARE CURSOR
OPEN
FETCH
CLOSE
51. What is the syntax to create cursor?
Ans.
Declare cursor _name cursor
Select statement;
52. How to Fetch the cursor?
Ans. FETCH [ NEXT [ FROM]] cursor _name INTO variable _list;
53. Why cursor is used in MYSQL?
Ans. In MySQL, a cursor allows row-by-row processing of the result sets. A cursor is
used for the result set and returned from a query. By using a cursor, you can iterate, or
step through the results of a query and perform certain operations on each row.
54. How many types of cursors in MYSQL?
Ans. There are 2 types of Cursors: Implicit Cursors, and Explicit Cursors.
55. What is meant by Implicit cursor?
Ans. Implicit Cursors are also known as Default Cursors of SQL SERVER. These
Cursors are allocated by SQL SERVER when the user performs DML operations.
56. What is meant by Explicit cursor?
Ans. Explicit Cursors are Created by Users whenever the user requires them. Explicit
Cursors are used for Fetching data from Table in Row-By-Row Manner.
57. What are the advantages of cursors?
 P a g e | 35

Ans.
•They are helpful in performing the row-by-row processing and also row wise valida-
tion on each row.
•Better concurrency control can be achieved by using cursors.
•Cursors are faster than while loops.
58. How many rows can a cursor hold?
Ans. A cursor can be viewed as a pointer to one row in a set of rows. The cursor can
only reference one row at a time, but can move to other rows of the result set as
needed.
59. What is a Trigger?
Ans. A trigger is a stored procedure in database which automatically invokes when-
ever a special event in the database occurs. For example, a trigger can be invoked
when a row is inserted into a specified table or when certain table columns are being
updated.
60. What is the syntax for Trigger?
Ans.
create trigger [trigger _name]
[before | after]
{insert | update | delete}
on [table _name]
[for each row]
[trigger _body]
61. How many types of Triggers we have?
Ans. There are two types of triggers
• After Triggers (For Triggers)
• Instead Of Triggers
62. Explain what is after Trigger?
Ans. Insert, Update, Delete.
63. What is the maximum depth level for nested triggers?
Ans. 32 levels.
 P a g e | 36

64. What is NoSQL?

NoSQL databases are non-tabular databases which store data differently than rela-
tional databases. The hiring manager may start your interview question to check your
basic understanding of data management. In your answer, you may briefly explain
what NoSQL is and consider sharing some of its unique traits or features
65. What are the different NoSQL databases?
There are four different types of NoSQL databases. List all four of them in your an-
swer to show your understanding of the data management system to the hiring man-
ager.
66. What is the key value offering in NoSQL?
NoSQL has a few value offerings that differ from other database management sys-
tems. Based on your experience, tell the interviewer what you feel provides the most
value. Try to keep the language as simple as possible since the interviewer may not be
from a technical background.
67. How to increase scalability in NoSQL?
As the company grows, scalability is often a priority. As a team member, you may
search for ways to scale the database and offer solutions effectively. You can discuss
one such solution in your answer and prove your problem-solving ability to the hiring
manager.
68. What is a graph database?
As a technical professional, you may work with members outside of your team. The
hiring manager may want to assess your ability to explain complex terms simply. In
your answer, define what a graph database is and consider talking about its usage.
69: Can NoSQL replace SQL?
A: No, NoSQL cannot replace SQL as certain projects require SQL only.
70: Where is NoSQL used?
A: NoSQL is used by companies that need a distributed data system for storing and
maintaining a large amount of unstructured and structured types of data.
71: Where is SQL used?
A: SQL is used by administrators who want to retrieve, store and manipulate data for
different purposes. SQL supports only relation-based databases only.