DATABASE SYSTEMS

PRACTICAL FILE

SUBMITTED BY: SUBMITTED TO:

Anshika Dhaundiyal Mrs. Meenu Bhagat
SGM22359
CSE 3rd Semester

DEPARTMENT OF COMPUTER SCIENCE

UNIVERSITY INSTITUTE OF ENGINEERING AND TECHNOLOGY
PANJAB UNIVERSITY SSG REGIONAL CENTRE
BAJWARA, HOSHIARPUR

1
Structured query language

2
 INDEX

S.No. Description Page No. Remarks

1 Introduction to SQL

2 RDBMS

3 SQL Queries

4 SQL Commands

5 Exercise 01

6 SQL for Data Definition

7 SQL for Data Manipulation

8 SQL for Data Query

9 Joins

10 Exercise 02

3
11 Triggers in SQL

12 Sequence in SQL

4
 INTRODUCTION TO SQL

Structured Query Language (SQL) is a standardized programming language that is used

to manage relational databases and perform various operations on the data in them.
Initially created in the 1970s, SQL is regularly used not only by database
administrators, but also by developers writing data integration scripts and data
analysts looking to set up and run analytical queries. It is a computer language for
storing, manipulating, and retrieving data stored in a relational database. SQL is the
standard language for Relational Database System. All the Relational Database
Management Systems (RDMS) like MySQL, MS Access, Oracle, Sybase, Informix,
Postgres, and SQL Server use SQL as their standard database language.
SQL is widely popular because it offers the following advantages:
• Allows users to access data in the relational database management systems.
• Allows users to define the data in a database and manipulate that data.
• Allows to embed within other languages using SQL modules, libraries & pre-compilers.
• Allows users to create and drop databases and tables.
• Allows users to create view, stored procedure, functions in a database.
• Allows users to set permissions on tables, procedures, and views.

The major differences between Data and Information in DBMS are as follows –

5
 DATABASE MANAGEMENT
SYSTEM

A database management system (or DBMS) is essentially nothing more than a

computerized data-keeping system. Users of the system are given facilities to perform
several kinds of operations on such a system for either manipulation of the data in the
database or the management of the database structure itself. Database Management
Systems (DBMSs) are categorized according to their data structures or types. It is a
software or system used to access, create, update, and retrieve any information from
database.

RDBMS – RELATIONAL DATABASE

MANAGEMENT SYSTEM

RDBMS stands for Relational Database Management System. It is the basis for SQL and
for all the modern database systems such as MS SQL server, Oracle, etc. The data in a
RDBMS is stored in database objects which are called as table or relation. It consists of
numerous rows and columns. A relational database is the most commonly used
database. It contains several tables, and each table has its primary key. Due to a
collection of an organized set of tables, data can be accessed easily in RDBMS.

6
 TABLE/RELATION

Everything in a relational database is stored in the form of relations. The RDBMS

database uses tables to store data. A table is a collection of related data entries and
contains rows and columns to store data. Each table represents some real-world
objects such as person, place, or event about which information is collected. The
organized collection of data into a relational table is known as the logical view of the
database. A table is the simplest example of data stored in RDBMS.

Properties of a Relation:

o Each relation has a unique name by which it is identified in the database.

o Relation does not contain duplicate tuples.
o The tuples of a relation have no specific order.
o All attributes in a relation are atomic, i.e., each cell of a relation contains exactly
one value.

ROW/RECORD

A row of a table is also called a record or tuple. It contains the specific information of
each entry in the table. It is a horizontal entity in the table. No two tuples are identical
to each other in all their entries. All tuples of the relation have the same format and
the same number of entries. The order of the tuple is irrelevant. They are identified by
their content, not by their position.

COLUMN/ATTRIBUTE

7
A column is a vertical entity in the table which contains all information associated with
a specific field in a table. Every attribute of a relation must have a name. Null values
are permitted for the attributes. Default values can be specified for an attribute
automatically inserted if no other value is specified for an attribute. Attributes that
uniquely identify each tuple of a relation are the primary key.

NULL VALUES

The NULL value of the table specifies that the field has been left blank during record
creation. It is different from the value filled with zero or a field that contains space.

8
 SQL QUERIES

A query is a question, regularly communicated formally. A database query can be either

a select question or an action query. A select query is an information recovery query,
while an activity query requests extra tasks on the information, for example, addition,
refreshing or deletion.

Queries are helpful devices with regards to databases and they are regularly called by
the client through a structure. They can be utilized to look for and get information from
at least one of your tables, play out specific activities on the database and even carry
out an assortment of calculations relying upon your necessities.

TYPES OF SQL COMMANDS

SQL commands are instructions. It is used to communicate with the database. It is

also used to perform specific tasks, functions, and queries of data. SQL can perform
various tasks like create a table, add data to tables, drop the table, modify the table,
set permission for users.

A SQL code is usually divided into four main categories:

9
• Queries are performed utilizing the omnipresent yet well-known SELECT state,
entry, which is additionally partitioned into provisos, including SELECT, FROM,
WHERE and ORDER BY.
• Data Manipulation Language (DML) is utilized to include, update, or erase
information and is really a SELECT articulation subset and is involved the INSERT,
DELETE and UPDATE statements, just as control statements, e.g., BEGIN
TRANSACTION, SAVEPOINT, COMMIT and ROLLBACK.
• Data Definition Language (DDL) is utilized for overseeing tables and list
structures. Instances of DDL proclamations incorporate CREATE, ALTER,
TRUNCATE and DROP.
• Data Control Language (DCL) is utilized to dole out and repudiate database rights
and consents. Its principle statements are GRANT and REVOKE.

SQL COMMANDS

1. CREATE TABLE COMMAND

It is used to create a table. The basic syntax of the CREATE TABLE statement is as
follows –

2. INSERT TABLE COMMAND

It is used to insert values in the created table. The syntax of the INSERT TABLE
statement is as follows –
10
 3. ALTER TABLE COMMAND
Alter table command allows changing the structure of an existing value. With alter
table command, it is possible to add or delete columns.
The syntax of an ALTER TABLE command to add a New Column in an existing table is as
follows-

The syntax of an ALTER TABLE command to DROP COLUMN in an existing table is as

follows-

The syntax of an ALTER TABLE command to change the DATA TYPE of a column in a
table is as follows-

4. DELETE COMMAND
The SQL DELETE Query is used to delete the existing records from a table. We can use
the WHERE clause with a DELETE query to delete the selected rows, otherwise all the
records would be deleted.
The syntax of the DELETE query with the WHERE clause is as follows –

11
5. UPDATE COMMAND

The SQL UPDATE Query is used to modify the existing records in a table. We can
use the WHERE clause with the UPDATE query to update the selected rows,
otherwise all the rows would be affected.

The syntax of the UPDATE query with a WHERE clause is as follows-

6. SELECT COMMAND

It is used to select rows and columns based on a particular condition.

• ALL ROWS AND COLUMNS
Syntax:
SELECT * FROM table_name;

• FOR SELECTING PARTICULAR COLUMNS

Syntax:

SELECT column_name1,column_name2 FROM table_name;

• FOR RETRIEVING DATA BASED ON PARTICULAR CONDITION

Syntax:

12
 SELECT column_name FROM table_name
WHERE [condition];

7. SORTING OF DATA IN TABLE

The SQL ORDER BY clause is used to sort the data in ascending or descending
order, based on one or more columns. Some databases sort the query results in
an ascending order by default.

8. LIKE CLAUSE
The SQL LIKE clause is used to compare a value to similar values using wildcard
operators. There are two wildcards used in conjunction with the LIKE operator.
• The percent sign (%)
• The underscore (_)
The percent sign represents zero, one or multiple characters. The underscore represents
a single number or character. These symbols can be used in combinations.

CREATING A TABLE AND INSERTING DATA IN THE TABLE

13
14
Insert the following data into their respective tables:

1) Find out the names of all the clients.

2) Retrieve the entire contents of the table.
3) Retrieve the list of names, city, and the state of all the clients.
4) List all the clients located in Mumbai.
5) Change the city of ClientNo ‘C00005’ to ‘Bangalore’.
6) Change the BalDue of ClientNo ‘C00001’ to Rs.1000.
7) Delete from table where the column state holds the value ‘Tamil
Nadu’.

15
 CREATING TABLE

create table client_master

(ClientNo , name ,city,pincode,state,BalDue);
insert into Client_master
(ClientNo , name , city,pincode,state,BalDue)
values ("C00001","Ivan Bayross","Mumbai","400054","Maharastra ","15000"),
("C00002","Mamta Mazumdar","Madras","780001","Tamil Nadu ","0"),
("C00003","Chaya Bankar","Mumbai","400057","Maharastra ","5000"),
("C00004","Ashwini Joshi","Bangalore","560001","karnataka ","0"),
("C00005","Hansel Colaco","Mumbai","400060","Maharastra ","2000"),
("C00006","Deepak Sharma","Mangalore","560050","Karnataka ","0");

1. Find out the names of all the clients.

INPUT:
select name
from Client_master

16
 2. Retrieve the entire contents of the table.
INPUT:
select *
from Client_master

3. Retrieve the list of names, city, and the state of all the
clients.

select name,city,state
from Client_master;

17
 4. List all the clients located in Mumbai.

select name
from Client_master
where city ="Mumbai" ;

5. Change the city of ClientNo ‘C00005’ to ‘Bangalore’.

Input :
update Client_master
set city="Bangalore"
where ClientNo ="C00005"

18
6. Change the BalDue of ClientNo ‘C00001’ to Rs.1000.
update Client_master
set BalDue="1000"
where ClientNo ="C00001";

7. Delete from table where the column state holds the value
‘Tamil Nadu’.

19
20
SQL for Data Manipulation

1) INSERTION of records
o INSERT INTO statement is used to insert new records in a table.

Syntax:

Example:

SQL for Data Query

1) SELECT statement
▪ The SQL statement SELECT is used to retrieve data from the tables in a database and
the output is also displayed in tabular form.
Syntax:

21
2) QUERYING using Database OFFICE
• Retrieve selected columns

• Renaming of columns

• DISTINCT Clause

• WHERE Clause

22
 • ORDER BY Clause

• Handling NULL Values

SQL JOIN
A JOIN clause is used to combine rows from two or more tables, based on a related column
between them.
Let us look at a selection from the "Orders" table:

23
 Then, look at a selection from the "Customers" table:

and it will produce something like this:

Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:
• (INNER) JOIN: Returns records that have matching values in both tables
• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched
records from the right table
• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched
records from the left table
• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right
table

24
 I. INNER JOIN
The INNER JOIN keyword selects records that have matching values in both tables.

Syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2

ON table1.column_name = table2.column_name;

Let's look at a selection of the Products table:

And a selection of the Categories table:

25
 II. SQL LEFT JOIN
The LEFT JOIN keyword returns all records from the left table (table1), and the matching
records from the right table (table2). The result is zero records from the right side if there is
nomatch.

LEFT JOIN Syntax

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Below is a selection from the "Customers" table:

And a selection from the "Orders" table

III. SQL RIGHT JOIN

The RIGHT JOIN keyword returns all records from the right table (table2), and the matching
records from the left table (table1). The result is zero records from the left side, if there is no
Match.

26
RIGHT JOIN Syntax
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Below is a selection from the "Orders" table:

And a selection from the "Employees" table:

IV. SQL FULL JOIN

The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or
right (table2) table records.

Tip: FULL OUTER JOIN and FULL JOIN are the same.
FULL OUTER JOIN Syntax
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

27
 Below is a selection from the "Customers" table:

And a selection from the "Orders" table:

Example:

28
 V. SQL Self Join
A self join is a regular join, but the table is joined with itself.

Self Join Syntax

SELECT column_name(s)
FROM table1 T1, table1 T2 WHERE
condition;

Below is a selection from the "Customers" table

29
Insert the following data into their respective tables :

1) List the various products available from the table.

2) Change the cost price of ‘Trousers’ to Rs. 950.
3) Delete all products from the table where the
Quantity on hand is equal to 100.
4) Destroy the table along with its data.

30
Creating table
create table Product_master
(productNo char , description char , ProfitPercent
int ,UnitMeasure char ,QtyOnHand int ,ReorderLvl
int ,SellPrice int ,CostPrice int );

insert into Product_master(productNo ,

description, ProfitPercent ,UnitMeasure
,QtyOnHand, ReorderLvl ,SellPrice ,CostPrice)
values("P0001" ,"T-Shirts" , "5" ,"Piece", "200"
,"50" , "350" ,"250"),
("P0345" ,"Shirts" , "6" ,"Piece", "150" ,"50" , "500"
,"350"),
("P06734" ,"Cotton Jeans" , "5" ,"Piece", "100"
,"20" , "650" ,"450"),
("P07865" ,"Jeans" , "5" ,"Piece", "100" ,"20" ,
"750" ,"500"),
("P07868" ,"Trousers" , "2" ,"Piece", "150" ,"50" ,
"850" ,"550"),

("P07885" ,"Pull Overs" , "2.5" ,"Piece", "80" ,"30" ,

"700" ,"450"),
("P07965" ,"Denim Shirts" , "4" ,"Piece", "100"
,"40" , "350" ,"250"),
("P07975" ,"Lyera Tops" , "5" ,"Piece", "70" ,"30" ,
"300" ,"175"),
("P08865" ,"Skirts" , "5" ,"Piece", "75" ,"30" , "450"
,"300");

31
32
 1.) List the various products available from the table.

select description
from Product_master

2.) Change the cost price of ‘Trousers’ to Rs. 950.

update Product_master
set CostPrice ="950"
where description ="Trousers";

33
 3.) Delete all products from the table where the Quantity
on hand is equal to 100.
delete from Product_master
where QtyOnHand = "100";

4.) Destroy the table along with its data.

drop table Product_master ;

34
 TRIGGERS IN SQL SERVER

A trigger is a set of SQL statements that reside in system memory with unique
names. It is a specialized category of stored procedure that is called
automatically when a database server event occurs. Each trigger is always
associated with a table.
A trigger is called a special procedure because it cannot be called directly like a
stored procedure. The key distinction between the trigger and procedure is that
a trigger is called automatically when a data modification event occurs against a
table. A stored procedure, on the other hand, must be invoked directly.
The following are the main characteristics that distinguish triggers from stored
procedures:

o We cannot manually execute/invoked triggers.

o Triggers have no chance of receiving parameters.
o A transaction cannot be committed or rolled back inside a trigger .

Syntax of Trigger
We can create a trigger in SQL Server by using the CREATE TRIGGER statement
as follows:

CREATE TRIGGER schema.trigger_name

ON table_name
AFTER {INSERT, UPDATE, DELETE}
[NOT FOR REPLICATION]
AS
{SQL Statements}
Triggers will be helpful when we need to execute some events automatically
on certain desirable scenarios. For example, we have a constantly changing
table and need to know the occurrences of changes and when these changes
happen. If the primary table made any changes in such scenarios, we could
create a trigger to insert the desired data into a separate table.

35
 SEQUENCE IN SQL SERVER

In SQL server, a sequence can be created using the CREATE

SEQUENCE statement. The statement specifies the name of the
sequence, the starting value, the increment, and other properties
of the sequence.
Syntax
Following is the syntax to create a sequence in SQL −

CREATE SEQUENCE Name

START WITH Initial Value
INCREMENT BY Increment Value
MINVALUE Minimum Value
MAXVALUE Maximum Value
CYCLE|NOCYCLE.
Here,
• Sequence Name − This specifies the name of the sequence.
• Initial Value − This specifies the starting value from where
the sequence should start.
• Increment Value − This specifies the value by which the
sequence will increment by itself. This can be valued
positively or negatively.
• Minimum Value − This specifies the minimum value of the
sequence.
• Maximum Value − This specifies the maximum value of the
sequence.
• Cycle − When the sequence reaches its Maximum Value, it
starts again from the beginning.
• No cycle − An exception will be thrown if the sequence
exceeds the Maximum Value.
Example
First of all, let us create a table named CUSTOMERS using the
following query −

CREATE TABLE CUSTOMERS (

ID INT,

36
 NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
);
We are inserting some records in the above-created table using
INSERT INTO statement as shown in the query below −

INSERT INTO CUSTOMERS VALUES

(NULL, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(NULL, 'Khilan', 25, 'Delhi', 1500.00),
(NULL, 'Kaushik', 23, 'Kota', 2000.00),
(NULL, 'Chaitali', 25, 'Mumbai', 6500.00),
(NULL, 'Hardik', 27, 'Bhopal', 8500.00),
(NULL, 'Komal', 22, 'Hyderabad', 4500.00),
(NULL, 'Muffy', 24, 'Indore', 10000.00);
The table is successfully created in the SQL database.

ID NAME AGE ADDRESS SALARY

NULL Ramesh 32 Ahmedabad 2000.00

NULL Khilan 25 Delhi 1500.00

NULL Kaushik 23 Kota 2000.00

NULL Chaitali 25 Mumbai 6500.00

NULL Hardik 27 Bhopal 8500.00

NULL Komal 22 Hyderabad 4500.00

NULL Muffy 24 Indore 10000.00

Now, create a sequence using the following query −

CREATE SEQUENCE My Sequence AS INT

START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 7
37
CYCLE.
In the above query, the sequence is named My_Sequence and it
starts with the value 1 and increments by 1 each time a value is
generated. The sequence has a maximum value of 5 and cycles
back to the starting value when it reaches the maximum value.
Once the sequence is created, it can be used to generate unique
integer values. Now, let us update the data in the ID column of
the CUSTOMERS table using the following query −
UPDATE CUSTOMERS SET ID = NEXT VALUE FOR my_Sequence.
Output
When you execute the above query, the output is obtained as
follows −

(7 rows affected)
Verification
Let us verify whether is sequence is updated in the ID column of
the table or not using the following query −

SELECT * FROM CUSTOMERS.

The table will be displayed as −

ID NAME AGE ADDRESS SALARY

1 Ramesh 32 Ahmedabad 2000.00

2 Khilan 25 Delhi 1500.00

3 Kaushik 23 Kota 2000.00

4 Chaitali 25 Mumbai 6500.00

5 Hardik 27 Bhopal 8500.00

6 Komal 22 Hyderabad 4500.00

7 Muffy 24 Indore 10000.00

38