DBMS Lab-Manual Final

SAL Engineering & Technical Institute
CE, IT, CSE & ICT Department
Database Management System

(3130703)
Laboratory Manual
Year: 2022-2023
Prepared By:
Dr. Ajay N. Upadhyaya,
Prof. Megha Joshi,
Prof. Sachi Bhavsar
INDEX
Page No.
Sr.No. Experiment Date Marks Signature
From To
Introduction of DBMS, SQL
1. and Oracle concepts and Create 3 5
a table.
How to insert data in a table
2. using insert command and 6 9
display the records in a table.
Update or Delete records of a
3. table and modifying structure 10 12
of a table using Alter and Drop
command.
Study of system defined
4. functions for manipulation of 13 16
data items in SQL.
Study of numeric/math function
5. and date functions for 17 19
manipulation of data items.
6. To perform join operation 20 23
between various tables.
7. Study and Implementation of 24 27
different types of constraints.
How to retrieve data from
8. different tables using sub 28 29
queries and correlated queries.
Understanding of Database
9. Objects: synonym, sequence, 30 33
index and view.
10. To study the concepts of 34 35
Normalization.
DBMS 3130703 Page 2 of 37

Prepared By: Dr. Ajay N. Upadhyaya, Prof. Megha Joshi, Prof. Sachi Bhavsar
EXPERIMENT NO: 1 DATE: / /
TITLE: Introduction of Database Management Systems, SQL Concepts, Oracle
Concepts and Create a table.
OBJECTIVES: On completion of this experiment student will able to<

 Know the concept of database management system.
 Know the concept of Oracle.
 Create a table in database.
THEORY:
 Introduction of Database Management Systems:

 DBMS is a collection of interrelated data and a set of programs to access those
data.
 Primary goal of DBMS is to provide a way to store and retrieve database
information that is both convenient and efficient.
 Examples of DBMS are Banking System, Universities, Airlines, etc.
 Introduction of Oracle:
 The relational model, sponsored by IBM (in June 1970), then came too accepted
as the definitive model for RDBMS. The language developed by IBM to
manipulate the data stored within model (Dr. E.F.Codd model) was originally
called Structured English Query Language (SEQUEL) with the word English
later dropped in favor Structured Query Language (SQL).
 In 1979 a company called Relational Software, Inc. released the first commercially
available implementation of SQL. Relational Software later come to be known as
Oracle Corporation. Oracle Corporation is a company that produces the most
widely used, Server based, Multi user RDBMS named Oracle.
 Oracle Tools:
 The Oracle product is primarily divided into
 Oracle Server tools: Oracle Server Product is either called Oracle Workgroup
Server or Oracle Enterprise Server. Oracle Workgroup Server or Oracle
Enterprise Server is used for data storage.
 Oracle Client tools: The client roll most commonly used for Commercial
Application Development is called Oracle Developer 2000. Oracle Developer
2000, Oracle’s tool box which consists of Oracle Forms, Oracle Reports and
Oracle Graphics. This suite of tools is used to capture, validate and display
data according to user and system needs.

 SQL*Plus is a separate Oracle client-side tool. Oracle Workgroup or
Enterprise Server is bundled with this SQL*Plus. It is a product that works on
Microsoft Windows 95 and Windows NT both of which are standard Client
based GUI operating systems.
 Oracle Workgroup Server and Oracle Developer 2000 are separate products
and they must be purchased separately.
 SQL (Structured Query Language):

 SQL (Structured Query Language) is a database sublanguage for querying and
modifying relational databases. It was developed by IBM Research in the mid-
1970 and standardized by ANSI in 1986.
 Components of SQL:
1) DDL (Data Definition Language):
Is a language, which includes the commands, which are used dynamically to set
up, change and remove any data structure e.g. tables, views and indexes. The
examples are CREATE, ALTER & DROP.
2) DML (Data Manipulation Language):
Is a language, which includes the commands, which are used to enter new rows,
change existing rows and remove unwanted rows from the tables in database.
The examples are INSERT, UPDATE & DELETE.
3) DCL (Data Control Language):
Is a language, which includes the commands, which are used to give or remove
access rights to both the Oracle database and the structures within it. The
examples are GRANT & REVOKE.
4) DQL (Data Query Language):
It is the component of SQL statement that allows getting data from the database
and imposing ordering upon it. In includes the SELECT statement. It allows
getting the data out of the database perform operations with it.
 The CREATE TABLE command: The CREATE TABLE command defines

each column of the table uniquely. Each column has a minimum of three
attributes, a name, data type and size (i.e. column width).
 Rules for creating Tables:

1. A name can have maximum up to 30 characters.
2. Alphabets from A-Z, a-z and numbers from 0-9 are allowed.
3. A name should begin with an alphabet.

4. The use of the special character like _ is allowed and also recommended
(Special characters like $, # are allowed only in Oracle).
5. SQL reserved words not allowed. For example: create, select and so on.
Syntax: CREATE TABLE <tablename>

(<ColumnName1> <DataType>(<size>),
<ColumnName2> <DataType>(<size>), …… );
Example: Create table client_master

(c_no varchar2(5), name varchar2(10), address varchar2(20),
pincode number(6), bal_due number(10,2));
EXCERCISE:
1) Create a table ‚emp‛ with the following fields:
EMPNO ENAME JOB HIREDATE SAL COMM DEPTNO
2) Create a table ‚dept‛ with the following fields:

DEPTNO DNAME LOCATION
3) Create a table ‚stud_master‛ with the following fields:

REG_NO S_NAME BRANCH
4) Create a table ‚stud_detail‛ with the following fields:

REG_NO COURSE_CODE COURSE_NAME MARKS SEM
EVALUATION:
Problem
Understanding Timely Total
Analysis & Mock
Level Completion
Solution (2) (10)
(3) (2)
(3)
Signature with date: ________________

TITLE: How to insert data in a table using insert command and display the
records in a table.

 Insert records into a table.
 Display records from a table.
THEORY:
 Inserting Data into Tables using INSERT INTO command:
 Once a table is created, most natural thing to do is load this table with data to be
manipulated later.
When inserting a single raw of data into the table, insert operation:
 Creates a new raw (empty) in the database table.
 Loads the values passed (by the SQL insert) into the columns specified.
Syntax: INSERT INTO <tablename> [(<ColumnName1>, <ColumnName2>, < )]

VALUES(<value1>,< value2>, <<);
Example: INSERT INTO client_master (c_no, name, address, pincode, bal_due)

VALUES (‘C001’, ‘Ajay’, ‘A-5, Bhandu’, 384120, 500 );
Note: Character value (expression) placed within the INSERT INTO statement
must be enclosed in single quotes (‘).
 Display / Viewing data in the Tables using SELECT command:

 Once data has been inserted into a table, the next most logical operation would
be to view what has been inserted. The SELECT SQL verb is used to achieve
this. The SELECT command is used to retrieve rows selected from one or
more tables.
 The SELECT statement can be used to Display some or all the columns from a
specified table.
 Display some or all of the rows from a specified table.
 Display calculated values from the table.
 Display statistical information from the tables, like averages or sums of column
values.
 Combine information from two or more tables.
In order to view global table data the syntax is:

SELECT <ColumnName 1> TO <ColumnName N> from TableName;
Note: Here, ColumnName 1 to ColumnName N represents table column names and

they separated by ‘,’.
All Rows and All Columns: When data from all rows and columns from the table
are to be viewed the syntax of the SELECT statement will be used. The syntax is:
Syntax: SELECT * FROM <TableName>;
Example: SELECT * FROM client_master;
Oracle allows the use of the Meta character asterisk (*), this is expanded by Oracle to
mean all rows and all columns in the table.
Displaying Some Columns from a Table:
Syntax: SELECT <ColumnName 1>,<ColumnName 2>, <, <ColumnName N>

FROM <TableName>;
Example: SELECT c_no, name FROM client_master;
Displaying Some Specified Rows from the Table:

If you want conditional retrieval of rows i.e. only those rows which satisfy
certain condition. You can use WHERE clause in the SELECT statement.
Syntax: SELECT <ColumnName 1>,<ColumnName 2>, <, <ColumnName N>

FROM <TableName>
WHERE <Condition>;
Here, <Condtion> is always quantified as <ColumnName = Value>.
Example: SELECT c_no, name FROM client_master WHERE bal_due>500;
Elimination of duplicates from the select statement:

A table could hold duplicate rows. In such a case, to see only unique rows, you have
to use DISTINCT clause. The DISTINCT clause allows removing duplicates from the
result set. The DISTINCT clause can be only be used with SELECT statements.

Syntax: SELECT DISTINCT <ColumnName 1>, <, <ColumnName N>
FROM <TableName>;
Example: SELECT DISTINCT job FROM emp;
The SELECT DISTINCT * SQL syntax scans through entire rows, and
eliminates rows that have exactly the same contents in each column.
Syntax: SELECT DISTINCT * FROM <TableName>;
Example: SELECT DISTINCT * FROM client_master;
Sorting data in a Table:

Oracle allows data from a table to be viewed in a sorted order. The rows retrieved from
the table will be sorted in either ascending or descending order depending on the
condition specified in the SELECT sentence.
Syntax: SELECT <ColumnName 1>,<, <ColumnName N> FROM

<TableName> ORDER BY <ColumnName 1>, <, <ColumnName N>
<[Sort Order]>;
Sort Order can be ascending (use word asc) or descending (use word desc). In case
there is no mention of the sort order, the Oracle engine sorts in ascending order by
default.
Example: a. SELECT * FROM client_master ORDER BY Name; (In

ascending order)
b. SELECT * FROM emp ORDER BY Job DESC; (In descending
order)

The format to display the records -
SELECT *DISTINCT+ ,*, column *alias+,…}

FROM table
WHERE condition(s)
Group by column(s)
HAVING group of row condition(s)
ORDER BY {column. Expr} [ASC/DESC];
EXERCISES:
1) Insert records into emp table.

2) Insert records into dept table.
3) Insert records into stud_master table.
4) Insert records into stud_detail table.
5) Select all information from emp table.
6) List all the employees who have salary between 1000 and 2000.
7) List student details in ascending and descending order.
8) Display all the different job types.
9) List department numbers and names in department name order.
10) Select all information from stud_master table.
11) Display Registration number and name of students whose department is
‚computer engineering‛.
EVALUATION:
Problem
Analysis & Mock
Level Completion
Solution (2) (10)
(3) (2)
(3)
Signature with date: _______________

TITLE: Update or Delete records of a table and modifying structure of a table using
Alter and Drop command.

 update or delete the data or records in the table.
 add or delete column in the table.
 change data type of given column and rename column.
 drop a table.
THEORY:
 Updating Rows: The UPDATE command is used to change or modify data values
in table.
Updating of All Rows:

Syntax: UPDATE <TableName> SET <ColumnName 1> = <Expression 1 or
Value 1>, <ColumnName N> = <Expression N or Value N>;
Example: Update the address details by changing its city name to Ahmedabad.
UPDATE ADDR_DTLS SET City = ‘Ahmedabad’;
Updating Records Conditionally:

Syntax: UPDATE <TableName> SET <ColumnName 1> = <Expression 1 or
Value 1>, <ColumnName N> = <Expression N or Value N> WHERE
<Condition>;
Example: Update the branch details by changing the AMP (HO) to Head Office.
UPDATE BRANCH_MSTR SET NAME = ‘Head Office’
WHERE NAME = ‘AMP (HO)’;
 Delete Operations:
 The DELETE command deletes rows from the table that satisfies the condition
provided by its WHERE clause, and returns the number of records deleted.
Removal of All Rows:

Syntax: DELETE FROM <TableName>;
Example: Empty the ACCT_DTLS table.

DELETE FROM ACCT_DTLS;
DBMS 3130703 Page 10 of 37

Removal of Specific Rows:
Syntax: DELETE FROM <TableName> WHERE <Condition>;
Example: Remove only the savings bank account details from the ACCT_DTLS
table.
DELETE FROM ACCT_DTLS WHERE ACCT_NO LIKE ‘SB%’;
 Inserting Data into a Table from another Table:

 To insert data one row at a time into a table, it is quite possible to populate a
table with data that already exists in another table.
Syntax: INSERT INTO <TableName> SELECT <ColumnName1>, < ,

<ColumnName N> FROM < TableName> [WHERE <Condition>];
 Here the WHERE clause is optional. If you are not specify the WHERE clause
then all the from source table to target table is copied.
Example: Insert only the savings bank accounts details in the target table
ACCT_DTLS from the source table ACCT_MSTR.
INSERT INTO ACCT_DTLS SELECT

ACCT_NO,BRANCH_NO,CURBAL FROM ACCT_MSTR
WHERE ACCT_NO LIKE ‘SB%’;
 Modifying the Structure of Tables:

 The structure of a table can be modified by using ALTER TABLE command.
ALTER TABLE allows changing the structure of an existing table. With ALTER
TABLE it is possible to add or delete columns, change the data type of existing
columns.
Adding New Columns:

Syntax: ALTER TABLE <TableName>
ADD (<NewColumnName> <DataType>(<Size>),
<NewColumnName> <DataType>(<Size>), …);
Example: Enter a new field called city in the table BRANCH_MSTR.

ALTER TABLE BRANCH_MSTR ADD(CITY VARCHAR2(25));
Dropping a Column from a Table:

Syntax: ALTER TABLE <TableName> DROP COLUMN <ColumnName>;
DBMS 3130703 Page 11 of 37

Example: Drop the column city from the table BRANCH_MSTR.
ALTER TABLE BRANCH_MSTR DROP COLUMN CITY;
Modifying Existing Columns:

Syntax: ALTER TABLE <TableName>
MODIFY(<ColumnName> <NewDataType>(<NewSize>));
Example: Alter table BRANCH_MSTR to allow the NAME field to hold

maximum of 30 characters.
ALTER TABLE BRANCH_MSTR MODIFY(NAME VARCHAR2(30));
 Destroying Tables:
 Sometimes tables within a particular database become obsolete and need to be
discarded. In such situation using DROP TABLE statement with the table name
can destroy a specific table. If a table is dropped all records held within it are lost
and cannot be recovered.
Syntax: DROP TABLE <TableName>;
Example: Remove the table BRANCH_MSTR along with the data held.
DROP TABLE BRANCH_MSTR;
EXCERCISES:
1) Add a column ‚SPOUSE‛ to the emp table that will hold the name of an
employee’s spouse.
2) Modify the job of employees to ‚programmer‛ whose job is ‚trainee‛.
3) Delete record whose location is ‚Baroda‛ from dept table.
4) Drop a table ‚stud_master‛.
5) Create a table ‚ManagerHist‛ from emp whose job is ‚Manager‛.
6) Copy all the information of ‚department‚ into the ‚ManagerHist‛ table.
EVALUATION:
Problem
Analysis & Mock
Level Completion
Solution (2) (10)
(3) (2)
(3)
DBMS 3130703 Page 12 of 37

TITLE: Study of system defined functions for manipulation of data items in SQL.

 know the concept of system defined function.
THEORY:
 System Defined functions: SQL Server has many built-in functions.
Aggregate Function: These are system defined functions which deal with numbers
Sum(), AVG(), MAX(), MIN(), COUNT() etc. are some example
String Function: LTRIM(), RTRIM(), LEN(), LEFT(), RIGHT(), LOWER() etc. are
some of the example
Date Function: GETDATE(), DATEADD(), DAY(), MONTH(), YEAR() etc. are some
of the example
Advanced Function: IIF(), CAST(), CONVERT(), CURRENT_USER(), ISNUMERIC()

etc. are some examples.
DBMS 3130703 Page 13 of 37

Aggregate function:
An aggregate function performs a calculation on a set of values, and returns a single
value.
COUNT() function:
The COUNT() function returns the number of rows that matches a specified
criterion.
COUNT() Syntax:
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
AVG() function:
The AVG() function returns the average value of a numeric column.
AVG() Syntax:
SELECT AVG(column_name)
FROM table_name
WHERE condition;
SUM() function
The SUM() function returns the total sum of a numeric column.
SUM() Syntax:
SELECT SUM(column_name)
FROM table_name
WHERE condition;
MIN() Function:
The MIN() function returns the smallest value of the selected column.
MIN() Syntax:
SELECT MIN(column_name)
FROM table_name
WHERE condition;
MAX() function
The MAX() function returns the largest value of the selected column.
DBMS 3130703 Page 14 of 37

MAX() Syntax:
SELECT MAX(column_name)
FROM table_name
WHERE condition;
String functions:
String functions are used to perform an operation on input string and return an
output string.
Following are the string functions defined in SQL:
1. ASCII(): This function is used to find the ASCII value of a character.

2. LEN(): This function is used to find the length of a string.
3. LTRIM(): Removes leading spaces from a string.
4. RTRIM(): Removes trailing spaces from a string.
5. LEFT(): Extracts a number of characters from a string (starting from left).
6. REPLACE(): Replaces all occurrences of a substring within a string, with a new
substring.
7. LOWER(): Converts a string to lower-case.
Date functions:
Date functions are generally used with date and time fields.
1. GETDATE(): Returns the current date and time.

2. DATEPART(): Returns a single part of a date/time.
3. DATEADD(): Adds or subtracts a specified time interval from a date.
4. DATEDIFF(): Returns the time between two dates.
5. CONVERT(): Displays date/time data in different formats.
Advanced Functions:
1. CURRENT_USER(): It returns the user name and host name for the SQL
account used by the server to authenticate the current client.
2. IIF(): It returns a value if a condition is TRUE, or another value if a condition is
FALSE.
3. ISNULL() : It returns the specified value IF the expression is NULL, otherwise
return the expression.
4. CONVERT() : It converts a value (of any type) into a specified datatype.
5. CAST(): The CAST() function converts a value (of any type) into a specified
datatype.
DBMS 3130703 Page 15 of 37

EXCERCISE:
1) What's the difference between COUNT(column) and COUNT(DISTINCT column)
2) Convert 25.65 values to an int datatype.
3) Write the difference between REPLACE and TRANSLATE functions.
4) The LENGTH function returns the length of a word. (State True / False with
justification.)
5) The __________ function removes characters from the left of char with initial
characters removed upto the first character not in set.
6) Consider following table:
1. From the above table, write a SQL query to find the minimum purchase amount.
2. Write a SQL query to count the number of orders.
3. Write a SQL query to find the highest purchase amount ordered by each
customer. Return customer ID, maximum purchase amount.
EVALUATION:
Problem
Analysis & Mock
Level Completion
Solution (2) (10)
(3) (2)
(3)
Signature with date: _____________
DBMS 3130703 Page 16 of 37

TITLE: Study of numeric/math function and date functions for manipulation of data
items.

 know the concept of number function
 know the concept of date function.
THEORY:
 Number functions:
 Number functions are described as follow:
Function Description Example

ABS(d) Returns the absolute value of the double d. ABS(-25.52) = 25.52
Returns the smallest integer that is greater CEIL(24.8) = 25
CEIL(d)
or equal to d. CEIL(-24.8)= -24
EXP(d) Returns ed EXP(5) = 148.413159
FLOOR(24.8) = 24,
FLOOR(d) Returns the largest integer less or equal to d.
FLOOR(-24.8)= -25
LN(d) Returns the natural logarithm of d. LN(10) = 2.30258509
LOG(b, d) Returns logbd. LOG(10,40) = 1.60205999
Returns i1 modulo i2, or the reminder of i1
MOD(i1, i2) MOD(15,7) = 1
divided i2.
POWER(d, p) Returns d to the pth power (dp). POWER(2,5)= 32
Returns d rounded to i digits of precision to
the right of the decimal point. If i is ROUND(2.6586,2) = 2.66
ROUND(d, i)
negative, d is rounded to the left of the ROUND(289.6586,-2)= 300
decimal point.
SIGN(-25) = -1
Returns -1 if d is negative, 1 if d is positive,
SIGN(d) SIGN(0) = 0
and 0 if d is zero.
SIGN(25) = 1
SQRT(d) Returns the square root of d. SQRT(81) = 9
Returns d truncated to i digits of precision
TRUNC(256.8888,2) =
to the right of the decimal point. If i is
TRUNC(d, i) 256.88
negative, d is truncated to the left of the
TRUNC(256.8888,-2) = 200
decimal point.
DBMS 3130703 Page 17 of 37

 Date functions:
 Implicit Date-to-String conversion is based on the value of the
NLS_DATE_FORMAT variable. To change its value use the ALTER SESSION
command:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';
 Date functions are described as follow:
Function Description Example

Returns the date dt plus i months. If i
is a decimal number. Oracle will ADD_MONTHS(SYS
ADD_MONTHS(dt, i) automatically convert it to an integer DATE, 2) = 13-10-2008
by truncating the decimal portion (it 21:23:00
may also be negative).
MONTHS_BETWEEN(
Returns the number of months that '01-JAN-
dt1 is later than dt2. A whole number 2009',SYSDATE) = 4
MONTHS_BETWEEN(dt1,
dt2)
is returned if dt1 and dt2 are the same
day of the month or if both are th last MONTHS_BETWEEN(
day of the month. '01-JAN-
2007',SYSDATE) = -19
Returns the date that corresponds to
the next day of week specified by the
string s following the date dt. The
NEXT_DAY(SYSDATE
time portion of the date is the same as
NEXT_DAY(dt, s) , 'Mon') = 18-08-2008
the time portion of dt. The string s
21:26:32
should be a text string containing the
full or abbreviated day of the week in
the session's language.
Returns the date dt rounded to the
ROUND(SYSDATE,
granularity specified in the date-
ROUND(dt[, fmt]) 'HH24') = 13-08-2008
format string fmt. See date format
21:00:00
codes.
Returns the current date/time, takes SYSDATE = 13-08-
SYSDATE
no arguments. 2008 21:28:13
DBMS 3130703 Page 18 of 37

 Conversion functions:
 Conversion functions are described as follow:
Function Description
Takes up to three arguments, where x is either a date or a number, fmt is
a format string specifying the format that x will appear in, and nls
TO_CHAR(x[,
specifies language or location formatting string.
fmt[, nls]])
If x is a date, fmt is a date format code.
If x is a number, fmt is a numeric format code.
Converts string s to DATE datatype accordingly the format string fmt.
TO_DATE(s[,
fmt[, nls]])
The fmt string uses the same date format code. The default is the value
stored in the NLS_DATE_FORMAT session variable.
TO_NUMBER(s[, Returns the numeric value represented by the string s. The format string
fmt[, nls]]) fmt specifies the format the that s appears in.
EXCERCISE:
1) Write down the query that ‚Display each employee name with hire date, and salary
review date. Assume review date is after one year after hire date. Order the output in
ascending review date order‛.
2) Differentiate: ROUND and TRUNC functions.
3) The __________ function returns number of months between two dates.
4) The TO_DATE() function also disallows part insertion of a DATE value into a column.
(State True / False with justification.)
5) The __________ function converts a value of a DATE datatype to CHAR value. (State
True / False with justification.)
EVALUATION:
Problem
Analysis & Mock
Level Completion
Solution (2) (10)
(3) (2)
(3)
DBMS 3130703 Page 19 of 37

TITLE: To perform join operation between various tables.

 fetch the data from more than one table on database.
 know different types of join.
THEORY:
 Join: A join is used when a SQL query requires data from more than one table on
database.
 There are two main types of join conditions: -
 Equi-join
 Non-equi join
 Equi-join: EQUI JOIN creates a JOIN for equality or matching column(s) values of
the relative tables. EQUI JOIN also create JOIN by using JOIN with ON and then
providing the names of the columns with their relative tables to check equality using
equal sign (=).
Syntax :
SELECT column_list
FROM table1, table2....
WHERE table1.column_name = table2.column_name;
Example :
SELECT student.name, student.id, record.class, record.city
FROM student, record
WHERE student.city = record.city;
 Non Equi-join: NON EQUI JOIN performs a JOIN using comparison operator
other than equal (=) sign like >, <, >=, <= with conditions.
Syntax :
SELECT *
FROM table_name1, table_name2
WHERE table_name1.column [> | < | >= | <= ]
table_name2.column;
DBMS 3130703 Page 20 of 37

Example :
SELECT student.name, record.id, record.city
FROM student, record
WHERE Student.id < Record.id;
SQL Join: This statement is used to combine data or rows from two or more
tables based on a common field between them. Different types of Joins are as
follows:
 INNER JOIN
 LEFT JOIN
 RIGHT JOIN
 FULL JOIN
 Inner Joins:
This join returns all rows from multiple tables where the join condition is met.
Note: We can also write JOIN instead of INNER JOIN. JOIN is same as INNER
JOIN.
Syntax :
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
 Left Joins:
This join returns all the rows of the table on the left side of the join and matches
rows for the table on the right side of the join. LEFT JOIN is also known as
LEFT OUTER JOIN.
Syntax :
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;
DBMS 3130703 Page 21 of 37

 Right Joins:
This join returns all the rows of the table on the right side of the join and
matching rows for the table on the left side of the join. RIGHT JOIN is also known
as RIGHT OUTER JOIN.
Syntax :
FROM table1
RIGHT JOIN table2
 Full Joins:
FULL JOIN creates the result-set by combining results of both LEFT JOIN and
RIGHT JOIN. The result-set will contain all the rows from both tables. For the
rows for which there is no matching, the result-set will contain NULL values.
Syntax :
FROM table1
FULL JOIN table2
EXCERCISE:
1) Define: Join. Explain self join.
2) Retrieve employee number, employee name and their department name, in department
name order.
3) Show all employee details who lives in Baroda.
4) Display the name, salary and department number of employees whose salary is more
than 10000.
5) List the employee name, job, and salary and department name for everyone in the
company except clerks. Sort on salary displaying the highest salary first.
6) List all employees by name and number along with their manager’s name and number.
7) Display all the employees who earn less than their managers.
8) Consider following tables Customers and Salesman.
DBMS 3130703 Page 22 of 37

1. From the above tables write a SQL query to find the salesperson and customer who
reside in the same city. Return Salesman, cust_name and city.
2. From the above tables write a SQL query to find salespeople who received
commissions of more than 12 percent from the company. Return Customer Name,
customer city, Salesman, commission.
3. From the following tables write a SQL query to find the salesperson and customer
who reside in the same city. Return Salesman, cust_name and city.
EVALUATION:
Problem
Analysis & Mock
Level Completion
Solution (2) (10)
(3) (2)
(3)
DBMS 3130703 Page 23 of 37

TITLE: Study and Implementation of different types of constraints.

 learn the different types of constraints.
THEORY:
 Constraints are classed as either:

1. Table constraints
 These may reference one or more columns and are defined separately from the
definitions of the columns in the table.
2. Column constraints
 These reference a single column and are defined within the specification for the
owning column.
 Constraint types-
 You may define the following constraint types-
1. Primary key
2. Foreign key
3. Unique
4. Null /Not null
5. Check
Primary key constraint: A primary key is a one or more column(s) in a table used to
uniquely identify each row in the table. None of the fields that are part of the primary key
can contain a null value. A table can have only one primary key.
PRIMARY KEY Constraint Defined at Column Level:
Syntax: <ColumnName> <Datatype> (<Size>) [Constraint constraint_name]

PRIMARY KEY
PRIMARY KEY Constraint Defined at Table Level:
Syntax: [Constraint constraint_name] PRIMARY KEY (<ColumnName 1>,

<ColumnName 2>)
DBMS 3130703 Page 24 of 37

Foreign key constraint:
 Foreign key represent relationships between tables. A foreign key is table whose values
are derived from the primary key or unique key of some other table.
 The table in which the foreign key is defined is called a foreign table or Detail table.
 The table that defines the primary or unique key and is referenced by the foreign key is
called the Primary table or Master table.
 The master table can be referenced in the foreign key definition by using the
REFERENCES adverb. If the name of the column is not specified, by default, oracle
references the primary key in master table.
FOREIGN KEY Constraint Defined at the Column Level:
Syntax: <ColumnName> <DataType>(<size>) [Constraint constraint_name]

REFERENCES <TableName> [(<ColumnName>)]
FOREIGN KEY Constraint Defined at the Table Level:
Syntax: [Constraint constraint_name] FOREIGN KEY ( <ColumnName>

[,<ColumnName>] ) REFERENCES <TableName> (<ColumnName>
[,<ColumnName>])
Unique constraint: The Unique column constraint permits multiple entries of NULL
into a column. These NULL values are clubbed at the top the column in order in which
they were entered into the table. This is the essential difference between the Primary Key
and Unique Constraints when applied to table column(s).
UNIQUE Constraint Defined at Column Level:
Syntax: <ColumnName> <Datatype> (<Size>) [Constraint constraint_name]

UNIQUE
UNIQUE Constraint Defined at the Table Level:
Syntax: [Constraint constraint_name] UNIQUE (<ColumnName 1>,

<ColumnName 2>)
DBMS 3130703 Page 25 of 37

The CHECK Constraint: Business rule validations can be applied to a table column by
using CHECK constraint. It must be specified as a logical expression that evaluates either
to TRUE or FALSE.
Note: A CHECK constraint takes substantially longer to execute as compared to NOT

NULL, PRIMARY KEY, FOREIGN KEY OR UNIQUE. Thus CHECK constraints must be
avoided if the constraint can be defined using the Not Null, Primary Key or Foreign Key
Constraint.
The CHECK Constraint defined at the Column Level:

Syntax: <ColumnName> <DataType>(<Size>) CHECK (<Logical Expression>)
Example: Create a table CUST_MSTR with the following CHECK constraints:
 Data values being inserted into the column CUST_NO must start with the capital
letter C.
 Data values being inserted into the column FNAME, LNAME should be in upper
case only.
CREATE TABLE CUST_MSTR

(CUST_NO VARCHAR2(10) CHECK (CUST_NO LIKE ‘C%’),
FNAME VARCHAR2(20) CHECK (FNAME = UPPER(FNAME)),
LNAME VARCHAR2(20) CHECK (LNAME = UPPER(LNAME)),
DOB DATE, PANCOPY VARCHAR2(1), PHOTOGRAPH VARCHAR2(25));
The CHECK Constraint defined at the Table Level:

Syntax: CHECK (<Logical Expression>)
Example: Create a table CUST_MSTR with the following CHECK constraints:
 Data values being inserted into the column CUST_NO must start with the capital
letter C.
 Data values being inserted into the column FNAME, LNAME should be in upper
case only.
CREATE TABLE CUST_MSTR

(CUST_NO VARCHAR2(10), FNAME VARCHAR2(20), LNAME VARCHAR2(20),
DOB DATE, PANCOPY VARCHAR2(1), PHOTOGRAPH VARCHAR2(25), CHECK
(CUST_NO LIKE ‘C%’), CHECK (FNAME = UPPER(FNAME)), CHECK (LNAME =
UPPER(LNAME)));
DBMS 3130703 Page 26 of 37

EXCERCISE:
1) Create the a table client_master with the following fields:
clientno, name, address, city, pincode, state, bal_due.
Consider the appropriate data type and size for the columns. In addition, define
clientno as the primary key column.
2) Create the a table product_master with the following fields:

Productno, Description, Qty_on_hand, Sell_price, Cost_price.
Productno as the primary key column and check data values being inserted into the
column Productno must start with the capital letter ‘P’.
3) Create the a table salesman_master with the following fields:

Sno, S_name, Address, city, Pincode, State, Sal_amt, Tgt_to_get
Consider the appropriate data type and size for the columns. In addition, define Sno as
the primary key column.
4) Create the a table sales_order with the following fields:

Orderno, clientno, orderdate, delyaddr, sno, delydate.
Orderno as the primary key column and define its clientno column as a foreign key,
which references the client_master table & sno column as a foreign key, which
references the salesman_master table.
5) Create the a table sales_order_details with the following fields:

Orderno, Productno, qtyordered
Consider the appropriate data type and size for the columns. In addition, define its
Orderno column as a foreign key, which references the sales_order table & Productno
column as a foreign key, which references the product_master table.
EVALUATION:
Understanding /
Involvement Timely
Problem solving Total
Completion
(10)
(4) (3)
(3)
DBMS 3130703 Page 27 of 37

TITLE: How to retrieve data from different tables using sub queries and
correlated queries.

 use the clauses ORDER BY, GROUP BY & HAVING BY.
 know the concept of Sub Queries
THEORY:
In SQL, a Subquery can be simply defined as a query within another query. In other words
we can say that a Subquery is a query that is embedded in WHERE clause of another SQL
query.
Types of Subqueries:
1. Single row Subquery
2. Multi row Subquery
3. Correlated Subquery
Syntax:
There is not any general syntax for Subqueries. However, Subqueries are seen to
be used most frequently with SELECT statement as shown below:
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])
 Steps:
1. The inner queries must be enclosed in parentheses, and must be on the right hand
side of the condition.
2. The sub query may not have an ORDER BY clause.
DBMS 3130703 Page 28 of 37

3. The ORDER BY clause appears at the end of the main select statement.
4. Sub queries are always executed from the most deeply nested to the least deeply
nested, unless they are correlated queries.
5. Logical and SQL operators may not be used as well as ANY and ALL.
Correlated Query:
In Correlated Query, Outer query executes first and for every Outer query row Inner
query is executed. Hence, Inner query uses values from Outer query.
EXCERCISE:
1. Find the employees who earn the maximum salary for their department. Display the
result in ascending order of salary.
2. Find the most recently hired employees in each department. Order by hire date.
3. Find the employees who earn the highest salary in each job type. Sort in descending
salary order.
4. Show the following details for any employee who earns a salary less than the average
for their department.
ENAME SALARY DNAME JOB
5. Who are the top three earners in the company? Display their name and salary.
6. Display the empno, name, job and deptno for employees whose salary is greater than
the highest salary in any SALES department.
EVALUATION:
Problem
Analysis & Mock
Level Completion
Solution (2) (10)
(3) (2)
(3)
DBMS 3130703 Page 29 of 37

TITLE: Understanding of Database Objects: synonym, sequence, index and view.

 learn view, synonym, index & sequence.
 create view, synonym, index & sequence.
THEORY:
 Introduction of Index:
 An index is an ordered list of the contents of a column, (or a group of columns) of a
table.
 Indexing involves forming a two dimensional matrix completely independent of the
table on which the index is being created. This two dimensional matrix will have a
single column, which will hold sorted data, extracted from the table column(s) on
which the index is created.
 Another column called the address field identifies the location of the record in the
oracle database.
 Creation of an Index:
 An index can be created on one or more columns. Based on the number of columns
included in the index, an index can be:
 Simple Index
 Composite Index
 Unique Index
 Creation of Index:
 An index is created on a single column of a table is called a Simple Index. The syntax
for creating simple index that allows duplicate values is as described:
Syntax: CREATE [UNIQUE] INDEX <IndexName> ON

TableName>(<ColumnName1>[,<ColumnName2>,..,<ColumnNameN>]);
Example: CREATE INDEX idx_c_no ON client_master(c_no);
 Dropping Index:
 Indexes associated with the tables can be removed by using the DROP INDEX
command.
Syntax: DROP INDEX <IndexName>;
DBMS 3130703 Page 30 of 37

Example: DROP INDEX idx_c_no;
 When a table, which has associated indexes, is dropped, the oracle engine
automatically drops all the associated indexes as well.
 Introduction of View:
 A VIEW is a virtual table in the database whose contents are defined by a query it
can represent.
 A view holds no data at all, until a specific call to the view is made. This reduces
redundant data on a HDD to a very large extent.
 Creation of views:
Syntax: CREATE VIEW <ViewName> AS
SELECT <ColumnName1>, <ColumnName2>
FROM <TableName>
WHERE <ColumnName>=expression list
GROUP BY <Grouping Criteria>
HAVING <Predicate>;
Note: The ORDER BY clause cannot be used while creating a view.
Example: Create view on the emp table for the Department 10 which access for the
columns empno,ename,sal.
Answer: create view vw_emp10 as select empno,ename,sal from emp
where deptno = 10;
 Selecting a data set from a view:

 Once a view has been created, it can be queried exactly like a base table.
 The select statement can have the clause like WHERE, ORDER BY etc.
Syntax: SELECT <ColumnName1>, <ColumnName2> FROM <ViewName>;

Example: select * from vw_emp10 where sal < 35000 order by empno;
 Introduction of Sequence:
 Most application requires automatic generation of numeric value.
 Sequences are tools used to generate a unique sequential number that can be used
in the data tables. One of the best features of sequences is that they guarantee that
you will get a unique value when you access the sequence.
 The value generated can have a maximum of 38 digits.
DBMS 3130703 Page 31 of 37

 Creation of Sequence:
Syntax: CREATE SEQUENCE <SequenceName>
[INCREMENT BY <IntegerValue>
START WITH <IntegerValue>
MAXVALUE <IntegerValue> / NOMAXVALUE
MINVALUE <IntegerValue> / NOMINVALUE
CYCLE/ NOCYCLE
CACHE <IntegerValue>/ NOCACHE
ORDER / NOORDER]
Note:
 Sequence is always given a name so that it can be referenced later when required.
 The ORDER, NOORDER Clause has no significance, if Oracle is configured with
Single server option. It is useful only when you are using Parallel Server in Parallel
mode option.
 If the CACHE / NOCACHE clause is omitted oracle caches 20 sequence numbers by
default.
Example:
Create sequence order_seq, which will generate numbers from 1 to 9999 in ascending
order with an interval of 1. The sequence must restart from the number 1 after
generating number 9999.
CREATE SEQUENCE order_seq INCREMENT BY 1 START WITH 1
MINVALUE 1 MAXVALUE 9999 CYCLE;
 Referencing a Sequence:
 Once a sequence is created SQL can be used to view the values held in its cache. To
simply view sequence value use a select sentence as described below.
SELECT <sequence_name>.NextVal FROM dual;
 This will display the next value held in the cache on the VDU screen. Every time
nextval references a sequence its output is automatically incremented from the old
value to the new value ready for use.
 After creating a table you can add the data by using the INSERT command like this:
INSERT INTO sales_order(o_no, o_date, c_no)
VALUES (order_seq.nextval, sysdate, ‘c0001’);
 To references the current value of a sequence:
SELECT <sequence_name>.CurrVal FROM dual;
DBMS 3130703 Page 32 of 37

 Introduction of Synonyms:
 A synonym is an alternative name for objects such as tables, views, sequences,
stored procedures, and other database objects.
Syntax: CREATE [OR REPLACE] [PUBLIC] SYNONYM [SCHEMA.]
SYNONYM_NAME FOR [SCHEMA.] OBJECT_NAME [@DBLINK];
Example: Create a synonym to a table EMP held by the user SCOTT.
CREATE PUBLIC SYNONYM EMPLOYEES FOR SCOTT.EMP;
Now, users of other schemas can references the table EMP, which is now called
EMPLOYEES without having the prefix the table name with the schema named
SCOTT.
For example, SELECT * FROM EMPPLOYEES;
EXCERCISE:
1) Create view on the emp table for the job ‚Clerk‛ which access for the columns empno,
ename, job, sal and rename the column empno as ‚empnumber‛. And access the data of
view.
2) Create a sequence ‚seq3‛ with the following parameters:
Increment by -1, cache 20, cycle, noorder and which will generate the numbers from 1 to
5000 in descending order.
3) Create a simple index on ‚orderid‛ column of a table ‘order’.
4) Create a synonym ‚employee‚ from the table emp.
EVALUATION:
Problem
Analysis & Mock
Level Completion
Solution (2) (10)
(3) (2)
(3)
DBMS 3130703 Page 33 of 37

TITLE: To study the concepts of Normalization.

 what is normalization?
 reasons of normalization.
 forms of normalization.
THEORY:
 WHAT IS NORMALIZATION?
 ‚Normalization is essentially the process of taking a wide table with lots of columns
but few rows and redesigning it as several narrow tables with fewer columns but
more rows.‛
 A properly normalized design allows you to use storage space efficiently, eliminate
redundant data, reduce or eliminate inconsistent data, and ease the data
maintenance burden. Before looking at the forms of normalization, you need to
know one cardinal rule for normalizing a database:
‚You must be able to reconstruct the original flat view of the data.‛
 Normalization is carried out for the following reasons:

1. To structure the data between tables so that data maintenance is simplified.
2. To allow data retrieval at optimal speed.
3. To simplify data maintenance through updates, inserts and deletes.
4. To reduce the need to restructure tables as new application requirements arise.
5. To improve the quality of design for an application by rationalization of table data.
 Forms of normalization:
 Relational database theorists have divided normalization into several rules called
normal forms.
 First Normal Form: No repeating groups.
 Second Normal Form: No nonkey attributes depend on a portion of the primary
key.
 Third Normal Form: No attributes depend on other non-key attributes.
 Boyce-Codd normal form (BCNF): Every non-trivial functional dependency in
the table is a dependency on a superkey.
 Fourth Normal Form: Every non-trivial multivalued dependency in the table is a
dependency on a superkey.
DBMS 3130703 Page 34 of 37

 Fifth Normal Form: Every non-trivial join dependency in the table is implied by
the superkeys of the table.
EXCERCISE:
1) Normalize the following table upto third normal form:
Author Author
Collection or
Last First Book Title Subject Publisher Building
Library
Name Name
PCL General
Berdahl Robert Politics History Wiley B – Block
Stacks
Legal
Yudof Mark Child Abuse Person Law Library C – Block
Procedures
Human Memory Cognitive PCL General

Harmon Glynn TMH B – Block
and Knowledge Psychology Stacks
Greek
Graves Robert The Golden Fleece Wiley Classics Library D – Block
Literature
Library and
Charles Ammi Library Information
Miksa Francis Person B – Block
Cutter Biography Science
Collection
Music Publishing Music

Hunter David TMH Fine Arts Library A – Block
and Collecting Literature
English and PCL General

Graves Robert Folksong Mahajan B – Block
Scottish Ballads Stacks
EVALUATION:
Problem
Analysis & Mock
Level Completion
Solution (2) (10)
(3) (2)
(3)
DBMS 3130703 Page 35 of 37

Extra Practice Work
Note: This work is totally optional. Student can refer for extra practice.
1) Create a table EMPLOYEE with following schema: (Emp_no, E_name,

E_address, E_ph_no, Dept_no, Dept_name,Job_id , Salary)
Write SQL queries for following question:
1. Insert aleast 5 rows in the table.
2. Display all the information of EMP table.
3. Display the record of each employee who works in department D10.
4. Update the city of Emp_no-12 with current city as Nagpur.
5. Display the details of Employee who works in department MECH.
6. Delete the email_id of employee James.
7. Display the complete record of employees working in SALES Department.
2) Consider above EMPLOYEE table:
Write SQL statements for the following query from Employee table.
1. List the E_no, E_name, Salary of all employees working for MANAGER.
2. Display all the details of the employee whose salary is more than the Sal of any IT
PROFF.
3. List the employees in the ascending order of Designations of those joined after
1981.
4. List the employees along with their Experience and Daily Salary.
5. List the employees who are either ‘CLERK’ or ‘ANALYST’ .
6. List the employees who joined on 1-MAY-81, 3-DEC-81, 17-DEC-81,19-JAN-80 .
7. List the employees who are working for the Deptno 10 or20.
8. List the Enames those are starting with ‘S’ . 9. Dislay the name as well as the first
five characters of name(s) starting with ‘H’
1. Write a query to find the salary of a person where age is <= 26 and salary >= 25000 from
customer table.
DBMS 3130703 Page 36 of 37

2. Write a query to find the salary of a person where age is <= 26 or salary > =33000 from
customer table.
3. Write a query to find the name of customer whose name is like ‚Ku%‛
4. Write a query to find the customer details using ‚IN‛ and ‚Between‛ operator where age
can be 25 or 27.
4) 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)
1. Allow NULL for all columns except ename and job.
2. Add constraints to check, while entering the empno value (i.e) empno > 100.
3. Define the field DEPTNO as unique.
4. Create a primary key constraint for the table(EMPNO).
5. Write queries to implement and practice constraints.
Perform the following:

1. Display all the fields of employee table
2. Retrieve employee number and their salary
3. Retrieve average salary of all employee
4. Retrieve number of employee
5. Retrieve distinct number of employee
6. Retrieve total salary of employee group by employee name and count similar names
7. Retrieve total salary of employee which is greater than >120000
8. Display name of employee in descending order
9. Display details of employee whose name is AMIT and salary greater than 50000.
DBMS 3130703 Page 37 of 37


DBMS Lab-Manual Final

Uploaded by

Copyright:

Available Formats

DBMS Lab-Manual Final

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

DBMS Lab-Manual Final

Uploaded by

Copyright:

Available Formats

SAL Engineering & Technical Institute

CE, IT, CSE & ICT Department

Database Management System

DBMS 3130703 Page 2 of 37

OBJECTIVES: On completion of this experiment student will able to<

 Introduction of Database Management Systems:

DBMS 3130703 Page 3 of 37

 SQL (Structured Query Language):

 The CREATE TABLE command: The CREATE TABLE command defines

 Rules for creating Tables:

DBMS 3130703 Page 4 of 37

Syntax: CREATE TABLE <tablename>

Example: Create table client_master

2) Create a table ‚dept‛ with the following fields:

3) Create a table ‚stud_master‛ with the following fields:

4) Create a table ‚stud_detail‛ with the following fields:

Signature with date: ________________

DBMS 3130703 Page 5 of 37

OBJECTIVES: On completion of this experiment student will able to<

Syntax: INSERT INTO <tablename> [(<ColumnName1>, <ColumnName2>, < )]

Example: INSERT INTO client_master (c_no, name, address, pincode, bal_due)

 Display / Viewing data in the Tables using SELECT command:

DBMS 3130703 Page 6 of 37

Note: Here, ColumnName 1 to ColumnName N represents table column names and

Syntax: SELECT * FROM <TableName>;

Example: SELECT * FROM client_master;

Displaying Some Columns from a Table:

Syntax: SELECT <ColumnName 1>,<ColumnName 2>, <, <ColumnName N>

Example: SELECT c_no, name FROM client_master;

Displaying Some Specified Rows from the Table:

Syntax: SELECT <ColumnName 1>,<ColumnName 2>, <, <ColumnName N>

Here, <Condtion> is always quantified as <ColumnName = Value>.

Example: SELECT c_no, name FROM client_master WHERE bal_due>500;

Elimination of duplicates from the select statement:

DBMS 3130703 Page 7 of 37

Example: SELECT DISTINCT job FROM emp;

Syntax: SELECT DISTINCT * FROM <TableName>;

Example: SELECT DISTINCT * FROM client_master;

Sorting data in a Table:

Syntax: SELECT <ColumnName 1>,<, <ColumnName N> FROM

Example: a. SELECT * FROM client_master ORDER BY Name; (In

DBMS 3130703 Page 8 of 37

SELECT *DISTINCT+ ,*, column *alias+,…}

1) Insert records into emp table.

Signature with date: _______________

DBMS 3130703 Page 9 of 37

OBJECTIVES: On completion of this experiment student will able to<

Updating of All Rows:

Updating Records Conditionally:

Removal of All Rows:

Example: Empty the ACCT_DTLS table.

DBMS 3130703 Page 10 of 37

 Inserting Data into a Table from another Table:

Syntax: INSERT INTO <TableName> SELECT <ColumnName1>, < ,

INSERT INTO ACCT_DTLS SELECT

 Modifying the Structure of Tables:

Adding New Columns:

Example: Enter a new field called city in the table BRANCH_MSTR.

Dropping a Column from a Table:

SELECT DISTINCT+ ,, column *alias+,…}