EXPERIMENT – 1

AIM: Implementation of DDL commands of SQL: Create, Alter,

Drop.

Theory: Data Definition Language or DDL commands in standard query

language (SQL) are used to describe/define the database schema. These
commands deal with database schema creation and its further modifications.
Some popularly known DDL commands are CREATE, ALTER, DROP.
 Create - It is a DDL command that is used for creating database objects such
as databases and database tables.
Syntax: CREATE TABLE table_name;
 Alter - is used to add, rename or modify, drop/delete columns in an existing
database table. It can further be used to add and remove various
constraints on an existing database table.
Syntax: ALTER TABLE table_name ADD (Column_name datatype);
 Drop - is used to delete a database object from the database. We can even
delete the database using the DROP command.
Syntax: to delete a table- DROP TABLE table_name;
to delete a column- DROP COLUMN Column_name;

Code & Observation:

  For Creating a table: CREATE TABLE Information ( Student_name char,
Roll_no int, Phone_no int);

 For Alter a table: ALTER TABLE Information ADD Pincode int;

 For Dropping a table: DROP TABLE Information;

***The drop got deleted***

Result- Thus, the implementation of DDL commands in SQL

[Create, Alter, drop] was done successfully.
 EXPERIMENT – 2
AIM: Implementation of DML commands of SQL with suitable
examples: Insert, Update and Delete.

Theory: Data Manipulation Language (DML) is a class of SQL statements

that are used to query, edit, add and delete row-level data from database tables
or views. The main DML statements are SELECT, INSERT, DELETE, and UPDATE.
· Insert – insert into is the statement which is used to add rows or data in a
table.
Syntax: INSERT INTO table_name Values (value1, value2, value3….);
· Update - With the UPDATE statement, you can change the actual data in
existing rows in a table. the UPDATE statement will alter the actual row-
level data. You can qualify an UPDATE command with a WHERE statement
to change the values of columns of only specific rows.
Syntax: UPDATE table_name SET column_name1=value1,
column_name2=value2…WHERE condition;
· Delete - It will remove rows in an existing table in your database. In
practice, it will usually specify with a WHERE clause with DELETE statement
to remove specific rows from a table.
Syntax: DELETE FROM table_name WHERE condition;

Code & Observation:

 For inserting a table - insert into information values(‘tanvi’,58,474003);

 For updating a table - update information set Pincode = 474004 where

Roll_no=58;
  For deleting a table - delete from information where Roll_no=52;

Result- Thus, the implementation of DML commands in SQL [INSERT,

UPDATE, DELETE] was done successfully.
 EXPERIMENT – 3
AIM: Implementation of different types of function with suitable
examples -Number function, Aggregate function, Character function,
Conversion function and Date function.

Theory: Functions in SQL Server are the database objects that contain a set
of SQL statements to perform a specific task. A function accepts input
parameters, perform actions, and then return the result. We should note that
functions always return either a single value or a table. The main purpose of
functions is to replicate the common task easily.

· Number Function – Numeric Functions are used to perform operations

on numbers and return numbers.
· Aggregate Function - An aggregate function performs a calculation on a
set of values and returns a single value.
· Conversion Function - SQL Conversion functions are single row functions
which are capable of typecasting column value, literal or an expression.
· Date Function - SQL Server comes with the following data types for
storing a date or a date/time value in the database: DATE - format YYYY-
MM-DD.

Code & Observation:

Result- Thus, the implementation of different types of functions in
SQL was done successfully.
 EXPERIMENT – 4
AIM: Implementation of different types of operators in SQL-
Arithmetic operators, Logical operators, Comparison operators
and Set operator.

Theory: An operator is a reserved word, or a character used primarily in an

SQL statement's WHERE clause to perform operation(s), such as comparisons and
arithmetic operations. These Operators are used to specify conditions in an SQL
statement and to serve as conjunctions for multiple conditions in a statement.
· Arithmetic Operator – Arithmetic operators perform simple arithmetic
operations such as addition (+), subtraction (-), multiplication (*), division
(/) &modulus (%).
· Logical Operator- We can use logical operators to compare multiple SQL
commands. These operators return either 1 (means true) or 0 (means
false). Logical Operators are available in SQL are - AND, OR, NOT, EXISTS,
BETWEEN, LIKE, ALL, IN, SOME, ANY.
· Comparison Operator - We can compare two values using comparison
operators in SQL. These operators return either 1 (means true) or 0 (means
false). There are some examples – equal to (=), less than (<), greater than
(>), less than or equal to (<=), greater than or equal to (>=), not equal (! =).
· Set Operator – There are following set operators in SQL such as – UNION,
INTERSECT, UNION ALL, MINUS.

Code & Observation:

  For Arithmetic Operator -
Operators Code Observation
Addition (+) select Emp_salary + Emp_Bonus
as Total_salary from Employee;

Subtraction (-) select Emp_salary - 1000 as

Deducted_salary from Employee;

Multiplication (*) select Emp_bonus*2 as

Total_bonus from Employee;

Divide (/) select Emp_bonus / 2 as

Total_bonus from Employee;

 For Comparison Operator -

Operators Code Observation
= select *from employee
where
Emp_salary=25000;

< select *from employee

where
Emp_salary<29000;

> select *from employee

where
Emp_salary>25000;

<= select *from employee

where
Emp_salary<=29000;
>= select *from employee
where
Emp_salary>=29000;

<>,!= select *from employee

where Emp_salary!
=29000;

 For Logical Operator -

Operators Code Observation
AND select *from employee
where Emp_city="Goa"
and Emp_bonus=2000;

OR select *from employee

where
Emp_city="Lucknow"
or Emp_bonus=2000;

NOT select *from employee

where not
Emp_city="Goa";

select *from employee

BETWEEN where Emp_salary
between 25000 and
30000;

LIKE select *from employee

where Emp_salary like
"3%";
IN select *from employee
where Emp_city in
("Kolkata","Lucknow");

 For Set Operators -

 Operators Code Observation
Union select *from
employee union
select *from
employee_1;

Union ALL select *from

employee union all
select *from
employee_1;

Result- Thus, the Implementation of the different types of operators

in SQL is done correctly.

EXPERIMENT – 5
AIM: Implementation of different types of joins in SQL – Inner
Join, Outer Join, and Natural Join.

Theory: SQL Join 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 – Inner Join joins two table on the basis of the column which is
explicitly specified in the ON clause. The resulting table will contain all the
attributes from both the tables including common column also. Syntax: -
SELECT * FROM table1 INNER JOIN
table2 ON table1.Column_Name = table2.Column_Name;
· Outer Join - Outer join is an operation that returns combined tuples from a
specified table even if the join condition fails. There are three types of
outer join in SQL i.e. -Left Outer Join, Right Outer Join, Full Outer Join.
Syntax: select * from table1 LEFT OUTER JOIN
table2 on table1.column_name= table2.column_name ;

· Natural Join - Natural Join joins two tables based on same attribute name
and datatypes. The resulting table will contain all the attributes of both
the table but keep only one copy of each common column. Syntax: SELECT
* FROM table1 NATURAL JOIN table2;

Code & Observation:

 For inner join - select * from customers INNER JOIN orders;

 For outer join - select * from customers left Outer JOIN orders;

 For natural join - select * from customers NATURAL JOIN orders;

Result- Thus, implementation of different types of joins in SQL (Inner
join, Outer join, Natural join) was done successfully.

EXPERIMENT – 6
AIM: Study and implementation of - Group by and having clause,
Order by clause, Indexing.

Theory:
  Group By - is used to arrange identical data into groups with the help of
some functions. i.e., if a particular column has the same values in different
rows, then it will arrange these rows in a group.
Syntax: SELECT column1, function_name(column2) FROM table_name
WHERE condition GROUP BY column1, column2
ORDER BY column1, column2;

 Having - used to apply a filter on the result of ‘group by’ based on the
specified condition. The conditions are Boolean type i.e., use of logical
operators (AND, OR). Having clause is used to filter data according to the
conditions provided. Having clause is generally used in reports of large
data. Syntax: select col_1, function_name(col_2) FROM tablename
WHERE condition GROUP BY column1, column2
HAVING Condition ORDER BY column1, column2;

 Order By - used to sort the fetched data in either ascending or descending

according to one or more columns. the keyword DESC to sort the data in
descending order and the keyword ASC to sort in ascending order.
Syntax: SELECT * FROM table_name ORDER BY column_name ASC|DESC

 Indexing - An index is a schema object. It is used by the server to speed up

the retrieval of rows by using a pointer. An index helps to speed up select
queries and where clauses, but it slows down data input, with the update
and the insert statements.
Syntax: CREATE INDEX index ON TABLE column;
***In this way, the indexes can be created, renamed, altered, removed or
confirmed. ***

Code & Observation:

Sample table
  Order By - select *from orders order by amount ASC;

 Having - SELECT amount, sum(delivery_rate) FROM orders WHERE

customer_id=4 GROUP BY amount, delivery_rate HAVING
order_id=2ORDER BY amount, delivery_rate ;

 Group by - SELECT amount, sum(delivery_rate)FROM orders WHERE

customer_id=4 GROUP BY amount, delivery_rate ORDER BY amount,
delivery_rate;

 Index - CREATE INDEX sno ON orders (amount, item);

Result-
The implementation of Group By, Having clause, Order By and Indexing
is done successfully.
 EXPERIMENT – 7
AIM: Study and implementation of - Sub queries and Views.
Theory:
· Sub Queries – 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 the WHERE clause of another SQL query. Some important
rules for Subqueries:
1). You can place the Subquery in a number SQL clauses: WHERE clause, FROM clause,
HAVING clause. Subqueries can be used with SELECT, UPDATE, INSERT, DELETE
statements along with expression operator. It could be equality operator or comparison
operator such as =, >, =, <= and Like operator.
2). Subquery must be enclosed in parentheses.
3). Subqueries are on the right side of the comparison operator.
4). ORDER BY command cannot be used in a Subquery. GROUPBY command can be used
to perform same function as ORDER BY command.
5). Use single-row operators with single row Subqueries. Use multiple-row operators
with multiple-row Subqueries.

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
FROM table_name
WHERE column_name expression operator
( SELECT COLUMN_NAME from TABLE_NAME WHERE...);
· Views - Views in SQL are a kind of virtual table. A view also has rows and
columns as they are in a real table in the database. We can create a view by
selecting fields from one or more tables present in the database. A View
can either have all the rows of a table or specific rows based on certain
conditions.
o Creating Views - We can create View using CREATE VIEW statement. A View
can be created from a single table or multiple tables.
Syntax: CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE condition;

o Listing all views in a database - We can list View using the SHOW FULL
TABLES statement or using the information_schema table. A View can be created
from a single table or multiple tables.
 Syntax (Using SHOW FULL TABLES): use "database_name";
show full tables where table_type like "%VIEW";
Syntax (Using information_schema): select * from
information_schema.views where table_schema = "database_name";

o Deleting views - SQL allows us to delete an existing View. We can delete or

drop a View using the DROP statement.
Syntax: DROP VIEW view_name;
view_name: Name of the View which we want to delete.

o Updating Views – The view will not allow to update if there is group by &
order by clause, select statement have distinct keyword, null values, no complex
or nested queries.
Syntax: CREATE OR REPLACE VIEW view_name AS
SELECT column1,column2,..
FROM table_name
WHERE condition;

Code & Observation:

Sample Table:

 Sub queries: SELECT order_id, item, amount from orders where order_id in
(Select order_id where customer_id=4);

 Views: create view view_name as select order_id, item from orders where
customer_id=4;
Result- Thus, the implementation of Sub queries and Views was done
successfully.
 EXPERIMENT – 8
AIM: Study and implementation of different types of
constraints.
Theory: Constraints are the rules that we can apply on the type of data in a
table. That is, we can specify the limit on the type of data that can be stored in a
particular column in a table using constraints.
Syntax: CREATE TABLE sample_table (
column1 data_type(size) constraint_name,
column2 data_type(size) constraint_name,
column3 data_type(size) constraint_name,....);
The available constraints in SQL are:
 Not Null - If we specify a field in a table to be NOT NULL. Then the field will
never accept null value. That is, you will be not allowed to insert a new row
in the table without specifying any value to this field.
 Unique Key - This constraint helps to uniquely identify each row in the
table. i.e. for a particular column, all the rows should have unique values.
We can have more than one UNIQUE column in a table.
 Primary Key - Primary Key is a field which uniquely identifies each row in
the table. If a field in a table as the primary key, then the field will not be
able to contain NULL values as well as all the rows should have unique
values for this field.
 Foreign Key - Foreign Key is a field in a table which uniquely identifies each
row of another table. That is, this field points to the primary key of another
table. This usually creates a kind of link between the tables.
(i) CHECK – Using the CHECK constraint we can specify a condition for
a field, which should be satisfied at the time of entering values for
this field.
(ii) DEFAULT – This constraint is used to provide a default value for
the fields. That is, if at the time of entering new records in the table if
the user does not specify any value for these fields, then the default
value will be assigned to them.

Code & Observation:

 CREATE TABLE Orders ( O_ID int NOT NULL,
ORDER_NO int NOT NULL,
C_ID int,
PRIMARY KEY (O_ID),
FOREIGN KEY (C_ID) REFERENCES
Customers(C_ID))

Result- Study and implementation of different types of

constraints was done successfully.
 EXPERIMENT – 9
AIM: Study and implementation of Database Backup and
Recovery commands.
Theory: Backup and recovery methods are designed to keep our information
safe. In Command Line Interface (CLI) or Graphical User Interface (GUI) using
backup and recovery utilities you can take backup or restore the data of
databases in DB2 UDB.
 Backup - Using Backup command you can take a copy of the entire
database. This backup copy includes database system files, data files, log
files, control information and so on. You can take backup while working
offline as well as online.
Syntax: [To list the active applications/databases]
 db2 list application
 Unique Key - This constraint helps to uniquely identify each row in the
table. i.e. for a particular column, all the rows should have unique values.
We can have more than one UNIQUE column in a table.
 Primary Key - Primary Key is a field which uniquely identifies each row in
the table. If a field in a table as the primary key, then the field will not be
able to contain NULL values as well as all the rows should have unique
values for this field.
 Foreign Key - Foreign Key is a field in a table which uniquely identifies each
row of another table. That is, this field points to the primary key of another
table. This usually creates a kind of link between the tables.
(i) CHECK – Using the CHECK constraint we can specify a condition for
a field, which should be satisfied at the time of entering values for
this field.
(ii) DEFAULT – This constraint is used to provide a default value for
the fields. That is, if at the time of entering new records in the table if
the user does not specify any value for these fields, then the default
value will be assigned to them.

Code & Observation:

Result- Study and implementation of Database Backup and

Recovery commands was done successfully.