PRACTICAL 1

Objective: To study theory and concepts of SQL

SQL was originated in 1970s in IBMs San Jose laboratory where it was used as the relational
database language for System-R DBMS .SQL has various standards as a language.There are number
of advantages in using a standardised relational language such as
Reduced training costs
Application portability
Application longality
Reduced dependence on a single vendor
Cross-system communication
Disadvantages of such language are
Loss of application portability due to addition of special SQL features by the vendor
It is difficult to fix any deficiencies in the standard.
There are three types of SQL commands :
1.Data definition language (DDL) - these commands are issued to create, alter or delete
tables. Usually these commands can be executed by database administrator only to prevent
any accidental or deliberate damage to the database.
2.Data manipulation language (DML) - these commands are used to insert, update, delete or
query data from tables.
3.Data control language (DCL) - these commands are used to grant various access privileges
of the database structure to users. Only the database administrator can execute these
commands.
SQL is used to implement the Relational Database Model, i.e., to handle data through tables. The
following points display the underlying concepts that make RDBMS so easy to use.
A Relational database management system (RDBMS) is a database management system (DBMS)
that is based on the relational model as introduced by E. F. Codd.
The data in RDBMS is stored in database objects called tables. The table is a collection of related
data entries and it consists of columns and rows.Remember, a table is the most common and
simplest form of data storage in a relational database. Following is the example of a CUSTOMERS
table:
| 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 | MP
| 4500.00 |
| 7 | Muffy
| 24 | Indore
| 10000.00 |

Every table is broken up into smaller entities called fields. The fields in the CUSTOMERS
table consist of ID, NAME, AGE, ADDRESS and SALARY.A field is a column in a table

that is designed to maintain specific information about every record in the table.

A record, also called a row of data, is each individual entry that exists in a table. A record is
a hrizontal entity in the table.

A column is a vertical entity in a table that contains all information associated with a
specific field in a table.

A NULL value in a table is a value in a field that appears to be blank, which means a field
with a NULL value is a field with no value.It is very important to understand that a NULL
value is different than a zero value or a field that contains spaces. A field with a NULL value
is one that has been left blank during record creation.

Constraints are the rules enforced on data columns on table. These are used to limit the type
of data that can go into a table. This ensures the accuracy and reliability of the data in the
database.

Constraints could be column level or table level. Column level constraints are applied only
to one column where as table level constraints are applied to the whole table.

Following are commonly used constraints available in SQL:

NOT NULL Constraint: Ensures that a column cannot have NULL value.

DEFAULT Constraint: Provides a default value for a column when none is specified.

UNIQUE Constraint: Ensures that all values in a column are different.

PRIMARY Key: Uniquely identified each rows/records in a database table.

FOREIGN Key: Uniquely identified a rows/records in any another database table.

CHECK Constraint: The CHECK constraint ensures that all values in a column
satisfy certain conditions.

INDEX: Use to create and retrieve data from the database very quickly.

Using these basic concepts and a little theory how the SQL system works we can create a powerful
DBMS that can cater to most modern data management needs :

Defining a structure to store the data

Providing a mechanism to manipulate stored information

Giving access and storage security to data

Avoiding concurrency anomalies if any

Keeping these concepts in mind we can now move on to creating tables and storing data in them.

PRACTICAL 2
Objective: To create a table and specify related queries
A Table is the most fundamental unit of RDBMS. Creating tables and managing data stored in them
is therefore the most basic part of SQL. In this practical we shall create a tables and use a few basic
commands to manage the table and data

To create a table: CREATE table <tablename> (<field 1> <type> <constraints>,<field 2>
<type> <constraints>,...,<field n> <type> <constraints>);

To insert values into the tables: INSERT INTO <tablename> VALUES(value1 ,value2 ,...,
value n); [The values should match the domain of the type otherwise an error shall be
created].

To view data stored: SELECT * FROM <tablename>; [Here we could also view only
specific data and lay constraints using the where clause]

To update data that is already stored we use UPDATE command with different conditions.

To change the structure of the table we shall use ALTER?MODIFY command.

To delete data from tables without deleting the log(such as auto increment counter) we use
DELETE command. To remove all data in the table including the log we use TRUNCATE
command. In both these tables however the structure of the table is not lost.

To completely remove the table we use DROP command.

PRACTICAL 3
Objective: To use foreign key and establish referential integrity
A foreign key is a unique non null value of an external table that ensures that only those values
which exist in an external table can be used in the current table otherwise an error shall be
generated. Since we will 'refer' to an external table for values and integrity of data, this constraint is
called referential integrity. It forms a connection between the data in two tables using these
constraints. The following commands have been used:
CREATE table DEPT(SNo int(11) not null,Branch char(3) not null primary key,Fees int
(11));
INSERT into DEPT VALUES(1,'CSE',35000);
INSERT into DEPT VALUES(2,'ECE',35000);
INSERT into DEPT VALUES(3,'MAE',35000);
Create Table Student(ID int(11) not null auto_increment primary key, Name varchar(30) not
null,Branch char(3) not null, Year char(1) not null, Foreign key (Branch) references
DEPT(Branch));
INSERT into Student values('',Rajagopala,CSE,3);
INSERT into Student values('',Himanshu,MAE,3);
INSERT into Student values('',Pravin,ECE,4);
Erraneous Commands:
INSERT into Student values('',Rohan,EEE,2);

PRACTICAL 4
Objective: To use select statement for retrieval of the data by column.

The SELECT statement is used to pull information from a table. The general form of the statement
is:
SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy;
what_to_select indicates what you want to see. This can be a list of columns, or * to indicate all
columns.which_table indicates the table from which you want to retrieve data. The WHERE clause
is optional. If it is present, conditions_to_satisfy specifies one or more conditions that rows must
satisfy to qualify for retrieval.

PRACTICAL 5
Objective: To use aggregate and Date functions.
SQL aggregate functions return a single value, calculated from values in a column.
Useful aggregate functions:

AVG() - Returns the average value

COUNT() - Returns the number of rows

MAX() - Returns the largest value

MIN() - Returns the smallest value

SUM() - Returns the sum

There are lots of date function use in MYSQL

Useful date functions:

CURDATE() - Returns the current date

CURTIME() - Returns the current time

DATEDIFF() Subtract two dates

MONTH() - Returns the month

DAY() - Returns the day of month

DAYNAME() - Returns the name of the weekday

WEEK() - Returns the week number

PRACTICAL 6
Objective: To use commands for updation, deletion and alteration of data ad table structure.
Update and delete commands are used to modify the data stored in a table. The alter table command
is used to change or modify the structure of table itself.
Updation: Data already stored can be changed or updated using the update command. We can use it
to change values of existing fields or insert values into empty fields of existing tuples.
Deletion: Using the delete command we can delete a single tuple or multiple tupples from the table
depending on the logic specified. The delete command deletes a whole row at once.It does not reset
counters while deleting data, therefore auto increment counters remain unchanged when the delete
command is used.
The truncate command works the same as the delete command except that it deletes the logs
too, so that counters such as the auto increment counter are reset
Alteration: The alter command is used to change the structure of the table. It is used as alter table
because it affects the schema of the table. Using this command, we can add fields to our table, drop
existing fields from the table or change the type or name of existing tables. We can also control
where to place new fields, that is, before or after a certain field. The alter tables modifies the
schema of the table.

PRACTICAL 7
Objective: To use group by and having clauses along on the given table
The following points describe these two clauses briefly:
The GROUP BY clause will gather all of the rows together that contain data in the
specified column(s) and will allow aggregate functions to be performed on the one
or more columns.
It simply groups all matching values for an aggregate function so that distinct values
can have separate aggregate values.

The HAVING clause allows you to specify conditions on the rows for each group - in
other words, which rows should be selected will be based on the conditions you
specify. The HAVING clause should follow the GROUP BY clause.
It further adds restrictions on the output value that shall be displayed. It is useful
when we work with a large number of values and only want a specified range of
outputs.

PRACTICAL 8
Objective: To perform nested queries and retrieve data from database
A Subquery or Inner query or Nested query is a query within another SQL query and
embedded within the WHERE clause. A subquery is used to return data that will be used in
the main query as a condition to further restrict the data to be retrieved.
There are a few rules that subqueries must follow:
Subqueries must be enclosed within parentheses.

A subquery can have only one column in the SELECT clause, unless multiple columns
are in the main query for the subquery to compare its selected columns.
An ORDER BY cannot be used in a subquery, although the main query can use an
ORDER BY. The GROUP BY can be used to perform the same function as the
ORDER BY in a subquery.
Subqueries that return more than one row can only be used with multiple value
operators, such as the IN operator.
The BETWEEN operator cannot be used with a subquery; however, the BETWEEN
operator can be used within the subquery.

PRACTICAL 9
Objective: To depict the various types of joins possible on a given set of relations
A "Join" makes relational database systems "relational".Joins allow you to link data from
two or more tables together into a single query result--from one single SELECT statement.
The following types of Joins are possible:
1 Inner Join : it joins two tables based on the common fields and a logical codition
specified on the join using a logical condition and the 'ON' clause.
2 Left outer join :The result of a left outer join for tables A and B always contains all
records of the "left" table (A), even if the join-condition does not find any matching
record in the "right" table (B). This means that if the clause matches zero records in
B (for a given record in A), the join will still return a row in the result (for that record
but with NULL in each column from B. A left outer join returns all the values from an
inner join plus all values in the left table that do not match to the right table,
including rows with NULL values in the link field.
3 Right outer join: The right outer join is the same as Left outer join but does so
with preference to ''right'' table. The rest of the concept is same for both outer joins.
4 Full outer join:Conceptually, a full outer join combines the effect of applying both
left and right outer joins. Where records in the FULL OUTER JOINed tables do not
match, the result set will have NULL values for every column of the table that lacks
a matching row. For those records that do match, a single row will be produced in
the result set.
5 Cross Join: returns the Cartesian product of rows from tables in the join. In other
words, it will produce rows which combine each row from the first table with each
row from the second table. Implicitly a cross join is made by simply separating the
tables witha comma, explicitly, we write the phrase 'cross join' where used.
6 Natural Join:Natural join ( ) is a binary operator that is written as (R S) where R
and S are relations.The result of the natural join is the set of all combinations of
tuples in R and S that are equal on their common attribute names. It works similar
to an inner join but matches only through equality of common values.
7 Self Join: A self-join is joining a table to itself.

PRACTICAL 10
Objective: To understand and use the concept of views
A view can contain all rows of a table or select rows from a table. A view can be created
from one or many tables which depends on the written SQL query to create a view.
Views, which are kind of virtual tables, allow users to do the following:
Structure data in a way that users or classes of users find natural or intuitive.
Restrict access to the data such that a user can see and (sometimes) modify
exactly what they need and no more.
Summarize data from various tables which can be used to generate reports.
A view gives a smaller area to work with, making access to data simpler and quicker.
Views are created using the Create View command and deleted using drop view command
when they are no longer needed. Values in views are inserted just as they are inserted
normally into the table and the change is reflected in the main table as well.

PRACTICAL 11
Objective: To write queries for a given problem using basic DBMS concepts
Given below are a set of tables:
BOOK(Bookid,Title,Publisher_Name)
BOOK_AUTHOR(Bookid,Author_Name)
PUBLISHER(Name,Address,Phone)
BOOK_COPIES(Bookid,Branchid,No_of_Copies)
BOOK_LOANS(Bookid,Branchid,CardNo,Dateout,Duedate)
LIBRARY_BRANCH(Branchid,Branch_Name,Address)
BORROWER(CardNo,Name,Address,Phone)
For the above relations, retrieve the following information from the tables:
1 How many copies of the book titled 'The Lost Tribe' are owned by the Library
branch whose name is 'Sharpstown'?
2 Retrieve the names of all borrowers who do not have any books checked out.
3 For each library branch, retrieve the branch name and total number of books loaned
out from that branch.
4 Retrieve the names, addresses and number of books borrowed for all those
students having issued more than five books
Given below are the queries used to obtain the above information:
1 Select No_of_Copies from BOOK_COPIES where Book_id in(Select Book_id
from BOOK where Title='The Lost Tribe') and Branchid in(Select Branchid
from LIB_BRANCH where Br_Name='Sharpstown');
2 Select Name from BORROWER where CardNo not in(Select CardNo from
BOOK_LOANS);
3 Select LIB_BRANCH.Br_Name,count(BOOK_LOANS.Branchid) as
Books_Loaned from LIB_BRANCH natural join BOOK_LOANS where
LIB_BRANCH.Branchid in(Select Branchid from BOOK_LOANS) group by
BOOK_LOANS.Branchid;
4 Select BORROWER.Name, BORROWER.Address,
count(BOOK_LOANS.CardNo) as Books_Issued from BORROWER natural
join BOOK_LOANS where BORROWER.CardNo in(Select CardNo from
BOOK_LOANS where CardNo in (Select CardNo from BOOK_LOANS group
by CardNo having count(CardNo)>5));

PRACTICAL 12
Objective: Introduction to PL/SQL
PL/SQL is a combination of SQL along with the procedural features of programming languages. It
was developed by Oracle Corporation in the early 90's to enhance the capabilities of SQL. PL/SQL
is one of three key programming languages embedded in the Oracle Database, along with SQL itself
and Java. The PL/SQL programming language was developed by Oracle Corporation in the late
1980s as procedural extension language for SQL and the Oracle relational database.
Following are notable facts about PL/SQL:
PL/SQL is a completely portable, high-performance transaction-processing language.
PL/SQL provides a built-in interpreted and OS independent programming environment.
PL/SQL can also directly be called from the command-line SQL*Plus interface.
Direct call can also be made from external programming language calls to database.
PL/SQL's general syntax is based on that of ADA and Pascal programming language.
Apart from Oracle, PL/SQL is available in TimesTen in-memory database and IBM DB2.

Features of PL/SQL

PL/SQL has the following features:

PL/SQL is tightly integrated with SQL.
It offers extensive error checking.
It offers numerous data types.
It offers a variety of programming structures.
It supports structured programming through functions and procedures.
It supports object-oriented programming.
It supports developing web applications and server pages.
Every PL/SQL statement ends with a semicolon (;). PL/SQL blocks can be nested within other
PL/SQL blocks using BEGIN and END. Here is the basic structure of a PL/SQL block:
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;
A PL/SQL unit is any one of the following:
PL/SQL block
Package body
Function
Procedure
Package
Trigger

Type
Type body

A procedure is created with the CREATE OR REPLACE PROCEDURE statement. The

simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as follows:

CREATE [OR REPLACE] PROCEDURE procedure_name

[(parameter_name [IN | OUT | IN OUT] type [, ...])]

{IS | AS}

BEGIN

< procedure_body >

END procedure_name;
Where,
procedure-name specifies the name of the procedure.
[OR REPLACE] option allows modifying an existing procedure.
The optional parameter list contains name, mode and types of the parameters. IN represents
that value will be passed from outside and OUT represents that this parameter will be used
to return a value outside of the procedure.
procedure-body contains the executable part.
The AS keyword is used instead of the IS keyword for creating a standalone procedure.
For Example:
CREATE OR REPLACE PROCEDURE greetings

AS

BEGIN

dbms_output.put_line('Hello World!');

END;

PL/SQL Block
DECLARE
x NUMBER := 100;
BEGIN
FOR i IN 1..10 LOOP
IF MOD(i,2) = 0 THEN
-- i is even
INSERT INTO temp VALUES (i, x, 'i is even');
ELSE
INSERT INTO temp VALUES (i, x, 'i is odd');
END IF;
x := x + 100;
END LOOP;
COMMIT;
END;

Output Table