dbms lab manual

MYSQL COMMANDS:

To login (from unix shell) use -h only if needed.

# [mysql dir]/bin/mysql -h hostname -u root -p

Create a database on the sql server.

mysql> create database [databasename];

List all databases on the sql server.

mysql> show databases;

Switch to a database.

mysql> use [db name];

To see all the tables in the db.

mysql> show tables;

To see database's field formats.

mysql> describe [table name];

To delete a db.

mysql> drop database [database name];

To delete a table.

mysql> drop table [table name];

Show all data in a table.

mysql> SELECT * FROM [table name];

Returns the columns and column information pertaining to the designated table.

mysql> show columns from [table name];

Show certain selected rows with the value "whatever".

mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";

Show all records containing the name "Bob" AND the phone number '3444444'.

mysql> SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';

Show all records not containing the name "Bob" AND the phone number '3444444' order by the phone_number field.

mysql> SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;

Show all records starting with the letters 'bob' AND the phone number '3444444'.

mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';

Show all records starting with the letters 'bob' AND the phone number '3444444' limit to records 1 through 5.

mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444' limit 1,5;

Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a.

mysql> SELECT * FROM [table name] WHERE rec RLIKE "^a";

Show unique records.

mysql> SELECT DISTINCT [column name] FROM [table name];

Show selected records sorted in an ascending (asc) or descending (desc).

mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

Return number of rows.

mysql> SELECT COUNT(*) FROM [table name];

Sum column.

mysql> SELECT SUM(*) FROM [table name];

Join tables on common columns.

mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;

Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));
mysql> flush privileges;

Change a users password from unix shell.

# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password 'new-password'

Change a users password from MySQL prompt. Login as root. Set the password. Update privs.

# mysql -u root -p
mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
mysql> flush privileges;

Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.

# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("newrootpassword") where User='root';
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

Set a root password if there is on root password.

# mysqladmin -u root password newpassword

Update a root password.

# mysqladmin -u root -p oldpassword newpassword

Allow the user "bob" to connect to the server from localhost using the password "passwd". Login as root. Switch to the MySQL db. Give privs. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by 'passwd';
mysql> flush privileges;

Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');
mysql> flush privileges;

or

mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;

To update info already in a table.

mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';

Delete a row(s) from a table.

mysql> DELETE from [table name] where [field name] = 'whatever';

Update database permissions/privilages.

mysql> flush privileges;

Delete a column.

mysql> alter table [table name] drop column [column name];

Add a new column to db.

mysql> alter table [table name] add column [new column name] varchar (20);

Change column name.

mysql> alter table [table name] change [old column name] [new column name] varchar (50);

Make a unique column so you get no dupes.

mysql> alter table [table name] add unique ([column name]);

Make a column bigger.

mysql> alter table [table name] modify [column name] VARCHAR(3);

Delete unique from table.

mysql> alter table [table name] drop index [colmn name];

Load a CSV file into a table.

mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);

Dump all databases for backup. Backup file is sql commands to recreate all db's.

# [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

Dump one database for backup.

# [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql

Dump a table from a database.

# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Restore database (or database table) from backup.

# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

Create Table Example 1.

mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username  VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

Create Table Example 2.

mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');

ROAD WAY TRAVELS

(RWT), which can be used for the all of the internet users. The Road Way Travels is in the business since 1997 connecting different locations in India its main office is located in Hyderabad. The application  can  do operations ticket reservation and cancel a ticket, This  application  will  automate the  reservation  of  tickets and  Enquiries  about  availability of the tickets .

The  system  also  provides  a  comprehensive  mechanism  of  ticket  booking  for  any  travel agency Reservations are directly handled by  booking office. Reservations can be made 30 days in advance in either cash or crediting case not available wait listed ticket is issued to the passenger. One passenger/person can book many tickets (to his/her family).

The cancellations are also directly handled at the booking office. Cancellations charges will be charged wait listed tickets that do not get confirmed are fully refunded.

Scope of the Project:  A booking officer should be able to

                    i.            Should be able to access the reservation Facility.

                  ii.            Query  the  buses  for  one  month  (Only  one  month  advance reservation Is available)

                iii.            No reservation before two days can be done.

                iv.            See his/her current reservations on different buses along with the Details.                                     

                  v.            Able to choose the seats which can are available for a certain class.

                vi.            Give details about the details about the credit card details

              vii.            He can reserve a ticket to select dates.

            viii.            It should also show the public and nation holidays.

2.                  The system should automatically show the fare for the corresponding seat and amount of money needs to be pay for selected seats.

MANUAL PROCESS :



               

              

As the above diagrams depict, it is a very tedious process for the User to keep track through the system, related to what is happening and the required information that generically may be needed at all stages specified before

DATABASE DIAGRAM

                             

 

customer Name

 

Experiment 1: E-R MODEL

E-R (Entity-Relationship) Diagram is used to represents the relationship between   entities in the table. The symbols used in E-R diagrams are:                                                    

               SYMBOL                                                              PURPOSE

                                                                                          Represent Entities

                                                                                                Represent attributes.

                                   

                                                                                                                          

             Represent Relationship Sets.

                                                                                                Arrow represents flow   

                                                                                           Structured analysis is a set of tools               

                                                                              

Techniques that the analyses to develop ROAD WAY TRAVELS system

The entities include,     

JOURNEY: First the passenger can select the from which place where and when he want a journey source and destination

Journey (service_id:string,source:string,destination:string,date_of_journey:date);

BUS_INFO: This entity having information about the buses like bustype,starttime,source,destination,fare,dateofjourney,noofseats

Bus_Info(bustype:string,starttime:date,source:string,destination:string,fare:real,dateofjourney:date,avaliableseats:int,departuretime:string);

PASSENGER: This entity stores the passenger details like pid, sex, address, pname

passenger(pid:int,sex:char,address:string,pname:string,age:int);

RESERVE_TICKET: A passenger can reserve a ticket for a particular bus on specified date and multiple seats reserve.

reserve_ticket(tid:int,service_id:string,bookingdate:date,noofseats:int,source:string,destination:string,mode:string,creditecard_id:string,amount:real);

WATING_LIST:  Passengers who reserved after completion of available seats ,these passengers kept in waiting list and  he get PNR number but before journey his ticket is not conformed the amount is refundable.

wating_list(pnr:int,fare:real,source:string,destination:string,pid:int,pname:string,noofseats:int,amout:real);

CANCEL_TICKET : Passenger can cancel the ticket before the journey date ,if he cancel before 30 days 10% amount is deducted and remaining amount is returned. If passenger  cancel before 15 days 20% amount is deducted and remaining amount is returned, and he cancel less than 1 week 50% amount is deducted and remaining amount is returned.

Cancel_ticket(cid:int,tid:int,source:string,destination:string,cdate:date,dateofjourney:string,deductingamount:real,returningamount:real);

Experiment 2:  Concept design with E-R Model

E-R diagram for journey

 

E-R diagram for Bus_Info

 

E-R Diagram for passenger

 

E-R Diagram for reserve _ticket

 

E-R Diagram for Waiting_list

 

E-R Diagram for cancel ticket

 

Weak E-R Model for Bus reservation

 

Experiment 3: Relational Model   for  ROAD WAY TRAVELS

1. The passenger table.

Ticket_id	PNAME	AGE	SEX	ADDRESS
7369	SMITH	34	MALE	AMEERPET ,HYDERABAD
7499	ALLEN	24	MALE	KOTI,HYDDERABAD
7899	JYOTHI	38	FEMALE	ASOKNAGAR,SECENDRABAD
7782	JOHN	45	MALE	DISELCOLONY,KAGIPET
7882	SWATHI	26	FEMALE	KIRATHABAD, HYDERABAD
7488	KING	56	MALE	MEHADIPATNAM,HYDERABAD
7883	SWETHA	27	FEMALE	S.R NAGAR, HYDERABAD

2. Journey table

RouteNo	Source	Destination	dateofjourny
1	Hyderabad	Banglore	20/6/2010
2	Hyderabad	Pune	3/8/2010

3. Bus table

Busno	Route_no	Starttime	bustype	Availableseats	departuretime	Source	Destination	Date	fare
1	1	10am	AC	56	10.30am	hyderabad	Banglore	20/6/2010	345
2	1	11am	NONAC	45	11.30am	hyderabad	Banglore	20/6/2010	500

4. Reservation table

PNR_NO	rid	JOURNEY_DATE	NOOFSEATS	ADDRESS	bustype	CONTACT_NO	STATUS
7369	1	20/6/2010	5	AMEERPET ,HYDERABAD	AC	98453478	YES
7499	2	20/6/2010	7	KOTI,HYDDERABAD	AC	94423467	YES
7899	3	20/6/2010	3	ASOKNAGAR, SECENDRABAD	NONAC	985634523	N0
7782	4	20/6/2010	2	DISELCOLONY,KAGIPET	NONAC	88562345	YES
7882	5	20/6/2010	6	KIRATHABAD, HYDERABAD	AC	94346899	YES
7488	6	21/6/2010	1	MEHADIPATNAM,  HYDERABAD	AC	906721450	N0
7883	7	21/6/2010	4	S.R NAGAR, HYDERABAD	NONAC	96745123	NO

5. Cancellation table

PNR_NO	CID	JOURNEY_DATE	NOOFSEATS	ADDRESS	CONTACT_NO	STATUS
7899	1	20/6/2010	3	ASOKNAGAR,SECENDRABAD	985634523	YES
7488	2	21/6/2010	1	MEHADIPATNAM,HYDERABAD	906721450	YES
7883	3	21/6/2010	4	S.R NAGAR, HYDERABAD	96745123	NO

6. Ticket table

PNRNO	TICKET_ID	AGE	SEX	JOURNEY_DATE	SOURCE	DESTINATION	DEP_TIME
7369	1	34	MALE	20/6/2010	HYDERABAD	BANGLORE	10.30am
7499	2	24	MALE	20/6/2010	HYDERABAD	BANGLORE	10.30am
7899	3	38	FEMALE	20/6/2010	HYDERABAD	BANGLORE	10.30am
7782	4	45	MALE	20/6/2010	HYDERABAD	BANGLORE	10.30am
7882	5	26	FEMALE	20/6/2010	HYDERABAD	BANGLORE	11.30am

Experiment 4: Normalization

What is Normalization?

Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.

The Normal Forms: The database community has developed a series of guidelines for ensuring that databases are normalized. These are referred to as normal forms and are numbered from one (the lowest form of normalization, referred to as first normal form or 1NF) through five (fifth normal form or 5NF). In practical applications, you'll often see 1NF, 2NF, and 3NF along with the occasional 4NF.

First Normal Form (1NF)

First normal form (1NF) sets the very basic rules for an organized database:

• Eliminate duplicative columns from the same table.
• Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).

Second Normal Form (2NF)

Second normal form (2NF) further addresses the concept of removing duplicative data:

• Meet all the requirements of the first normal form.
• Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
• Create relationships between these new tables and their predecessors through the use of foreign keys.

Third Normal Form (3NF)

Third normal form (3NF) goes one large step further:

• Meet all the requirements of the second normal form.
• Remove columns that are not dependent upon the primary key.

Fourth Normal Form (4NF)

Finally, fourth normal form (4NF) has one additional requirement:

• Meet all the requirements of the third normal form.
• A relation is in 4NF if it has no multi-valued dependencies.

Table passenger is in 3NF

Functional dependencies

Pnrno->pname

Pnrno->age

Pnrno->sex,address

Only one key is identifying the whole table

THE SQL LANGUAGE

SQL is a language for relational database. SQL is a non-procedural i.e., when we use SQL we    specify what we want to be done not how to do it.

Features Of SQL

1.      SQL is an interactive query language.

2.      SQL is a database administration language.

3.      SQL is a database programming language.

4.      SQL is a client/server language.

5.      SQL is a distributed database language.

6.      SQL is a database gateway language.

Basic SQL Commands

Data Definition Language commands (DDL)

Experiment 4: Normalization

What is Normalization?

Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.

The Normal Forms: The database community has developed a series of guidelines for ensuring that databases are normalized. These are referred to as normal forms and are numbered from one (the lowest form of normalization, referred to as first normal form or 1NF) through five (fifth normal form or 5NF). In practical applications, you'll often see 1NF, 2NF, and 3NF along with the occasional 4NF.

First Normal Form (1NF)

First normal form (1NF) sets the very basic rules for an organized database:

• Eliminate duplicative columns from the same table.
• Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).

Second Normal Form (2NF)

Second normal form (2NF) further addresses the concept of removing duplicative data:

• Meet all the requirements of the first normal form.
• Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
• Create relationships between these new tables and their predecessors through the use of foreign keys.

Third Normal Form (3NF)

Third normal form (3NF) goes one large step further:

• Meet all the requirements of the second normal form.
• Remove columns that are not dependent upon the primary key.

Fourth Normal Form (4NF)

Finally, fourth normal form (4NF) has one additional requirement:

• Meet all the requirements of the third normal form.
• A relation is in 4NF if it has no multi-valued dependencies.

Table passenger is in 3NF

Functional dependencies

Pnrno->pname

Pnrno->age

Pnrno->sex,address

Only one key is identifying the whole table

THE SQL LANGUAGE

SQL is a language for relational database. SQL is a non-procedural i.e., when we use SQL we    specify what we want to be done not how to do it.

Features Of SQL

7.      SQL is an interactive query language.

8.      SQL is a database administration language.

9.      SQL is a database programming language.

10.  SQL is a client/server language.

11.  SQL is a distributed database language.

12.  SQL is a database gateway language.

Basic SQL Commands

Data Definition Language commands (DDL)

            Data Manipulation Language commands (DML)

Transaction Control Language commands (TCL)

            Data control Language commands (DCL)

Transaction Control Language commands (TCL)

            Data control Language commands (DCL)

Experiment 5: Installation of Mysql and Practicing DDL commands

Steps to Install MySQL on Windows XP

The installing of MySQL on Windows XP has the simplest of the steps to configure and run MySQL on Windows XP in the quickest of the time. The task is divided into 3 steps. They are:

Step 1: Getting the software

Download the latest version of MySQL from the community site. Unpack it into your desired folder. It is always a good practice to download the zipped archive of MySQL binary distribution since using the one click installer; you might sometimes lose the power of configuration manually.

Its also a better way to understand configuring manually since if you switch to a Unix based environment later on, you will have to carry out a similar procedure and there is usually no one click installer in such environments.

Unpack MySQL in C:\Program Files\mysql-5.0.45-win32

Step 2 : Configuring the environment

While configuring in the Windows environment you have to do two things. One, putting a file having a name my.ini in the Windows folder of the operating system. Two, setting the path in your system environment variable.

Here’s what you have to do:

Creating Option File (my.ini)

Go to your installation path of MySQL (In my case it is: C:\Program Files\mysql-5.0.45-win32) and rename the file my-medium.ini to my.ini. Open the renamed file in notepad and add the following lines, just beneath the line which says, # The MySQL server and above the line port = 3306:

[mysqld]
# set basedir to your installation path
basedir=C:/Program Files/mysql-5.0.45-win32
# set datadir to the location of your data directory
datadir=D:/workspace/data

The second line as shown above is, configuring your database directory. This means, the path which you assign to datadir will be the path where your databases will be created. You have to make sure that the path exists before you assign it. There will a data directory in the MySQL installed path. If you provide a different path to the data directory than this default one, you must copy the entire contents of the data directory in that path.

Finally, drop the my.ini file into your Windows directory. In my case, I copied the file into the path C:/Windows

Setting the environment variable

After completing the above step, goto

My Computer > Properties > Advanced >Environment Variables > System variables > Path > Edit

In the Value box, goto the end of the line and put a semi colon and the path of the MySQL bin folder. In my case I have put it as:

;C:\Program Files\mysql-5.0.45-win32\bin

Click OK 3 times and thats it with the configuration of MySQL. You are all set to test the installation.

step 3: Final Configurations and Running MySQL

You are actually good to go for running mysql. For this, open up your command prompt and type in the command mysqld –console. This will start the mysql database server. Whenever you run this command henceforth you will see these lines at the end:

[Note] mysqld: ready for connections.
Version: ‘5.0.45-community-nt-log’ socket: ” port: 3306 MySQL Community Edit
ion (GPL)

When you see these lines, it means that mysql has started successfully. Its time to party!

Leave the command prompt open and start up another command prompt. For the first time you should do this:

C:\>mysql -u root -p
Enter password:

When it will ask for a password, just hit Enter. It will allow access to you without a password and will show up a prompt as:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.0.45-community-nt-log MySQL Community Edition (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>

Note that your database has anonymous access. It is not a secure thing to let it like that. You should always access the database with a password. So for setting up the password you will have to do the below steps. Put in the commands which are highlighted as shown:

mysql> update user set password=PASSWORD(“admin”) where User=’root’;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3 Changed: 0 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

Once you have done this, try logging into the server again without a password. It must show the below error:

C:\Documents and Settings\xpuser>mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user ‘root’@'localhost’ (using password: NO)

This means, now your database is secure. You should be relieved that your mysql setup has got completed. Now every time you want to start your database start the server in one command prompt and in second command prompt login to the database server by doing:

C:\>mysql -u root -p
Enter password: admin

This ends the complete process.

DDL commands

Creation of tables with constraints

1. Passenger Table

CREATE TABLE passenger (pnr_no number(6) primary key, pname  varchar(15),age number(3),sex char2(8),address varchar(30));

Query OK, 0 rows affected (0.24 sec)

2. Journey Table

CREATE TABLE journey(route_no number(6) primary key, source  varchar(15), destination varchar(30),dateofjourney varchar2(15));

 Query OK, 0 rows affected (0.24 sec)

3.      Bus Table

CREATE TABLE bus(bus_no number(6) primary key, route_no references(journey), starttime varchar(10),bustype varchar2(15),availableseats number(3),departuretime varchar2(10),source  varchar(15), destination varchar(30),date varchar2(15),fare number(8));

  Query OK, 0 rows affected (0.24 sec)

4. Reservation Table

CREATE TABLE reservation(rid number(6) primary key ,pnr_no number(6) references(passenger),noofseats number(3),address varchar2(20),contact_no number(10),bustype varchar2(10) references(bus),status varchar2(5));

    Query OK, 0 rows affected (0.24 sec)

5. Cancellation Table

CREATE TABLE cancellation(cid number(6) primary key,pnr_no number(6) references(passenger),noofseats number(3),address varchar2(20),contact_no number(10),status varchar2(5));

Query OK, 0 rows affected (0.24 sec)

6. Ticket Table

CREATE TABLE ticket(pnr_no number(6) references(passenger),ticket_id number(3) primary key,age varchar2(3),sex varchar2(6),journey_date varchar2(15),source varchar(20),destination varchar2(20),amount number(8) null);

       Query OK, 0 rows affected (0.24 sec)

Other DDL Commands

SQL ALTER TABLE Statement Syntax

ALTER TABLE <table_name> ADD <column_name1> <datatype1> <constraint1>   ALTER TABLE <table_name> ALTER COLUMN <column_name1> <datatype1> <constraint1>   ALTER TABLE <table_name> DROP COLUMN <column_name1> <datatype1>

Experiment 6: Practicing DML Commands

Data Manipulation Language (DML) is a family of computer languages used by computer programs and/or database users to insert, delete and update data in a database. Read-only querying, i.e. SELECT, of this data may be considered to be either part of DML or outside it, depending on the context.

Currently the most popular data manipulation language is that of SQL, which is used to retrieve and manipulate data in a Relational database. Other forms of DML are those used by IMS/DLI, CODASYL databases (such as IDMS), and others.

Data Manipulation Language comprises the 'SQL-data change' statements, which modify stored data but not the schema or database objects. Manipulation of persistent database objects (e.g. tables or stored procedures) via the 'SQL-schema' statements, rather than the data stored within them, is considered to be part of a separate Data Definition Language. In SQL these two categories are similar in their detailed syntax, data types, expressions etc., but distinct in their overall function.

Data Manipulation Languages have their functional capability organized by the initial word in a statement, which is almost always a verb. In the case of SQL, these verbs are:

• SELECT ... FROM ... WHERE ...
• INSERT INTO ... VALUES ...
• UPDATE ... SET ... WHERE ...
• DELETE FROM ... WHERE ...

The purely read-only SELECT query statement is classed with the 'SQL-data' statements^[2] and so is considered by the standard to be outside of DML. The SELECT ... INTO form is considered to be DML because it manipulates (i.e. modifies) data. In common practice though, this distinction is not made and SELECT is widely considered to be part of DML.^[3]

Most SQL database implementations extend their SQL capabilities by providing imperative, i.e., procedural, languages. Examples of these are Oracle's PL/SQL and DB2's SQL PL.

Data manipulation languages tend to have many different flavors and capabilities between database vendors. There have been a number of standards established for SQL by ANSI,^[1] but vendors still provide their own extensions to the standard while not implementing the entire standard.

There are two types of data manipulation languages:

• Procedural
• Declarative

Each SQL DML statement is a declarative command. The individual SQL statements are declarative, as opposed to imperative, in that they describe what the program should accomplish, rather than describing how to go about accomplishing it.

Data manipulation languages were initially only used by computer programs, but (with the advent of SQL) have come to be used by people as well.

INSERT INTO "table_name" ("column1", "column2", ...)
VALUES ("value1", "value2", ...)
Assuming that we have a table that has the following structure,
Table Store_Information

Column Name	Data Type
store_name	char(50)
Sales	Float
Date	Datetime

and now we wish to insert one additional row into the table representing the sales data for Los Angeles on January 10, 1999. On that day, this store had $900 in sales. We will hence use the following SQL script:

INSERT INTO Store_Information (store_name, Sales, Date)
VALUES ('Los Angeles', 900, 'Jan-10-1999')

INSERT INTO SELECT

The second type of INSERT INTO allows us to insert multiple rows into a table. Unlike the previous example, where we insert a single row by specifying its values for all columns, we now use a SELECT statement to specify the data that we want to insert into the table. If you are thinking whether this means that you are using information from another table, you are correct. The syntax is as follows:

INSERT INTO "table1" ("column1", "column2", ...)
SELECT "column3", "column4", ...
FROM "table2"

Note that this is the simplest form. The entire statement can easily contain WHERE, GROUP BY, and HAVING clauses, as well as table joins and aliases.

So for example, if we wish to have a table, Store_Information, that collects the sales information for year 1998, and you already know that the source data resides in the Sales_Information table, we'll type in:

INSERT INTO Store_Information (store_name, Sales, Date)
SELECT store_name, Sales, Date
FROM Sales_Information
WHERE Year(Date) = 1998

Insert statements for RWT:

(i)insert into passenger values (&pnrno,’&pname’,&age,’&sex’,’&address’);

Execute above query repeatedly.

(ii)Insert into journey values (&route_noo,’&source’,’&destination’,’&dateofjourney’);

Execute above query repeatedly.

(iii)Insert into bus values (&bus_no,&route_no,’&starttime’,’&bustype’,&availableseats,

&departuretime,’&source’,’&destination’,’&date’,&fare);

Execute above query repeatedly.

(iv) Insert  into reservation values (&rid,&pnr_no,&no_seats,’&address’,&contactno,’&bustype’,&status’);

Execute above query repeatedly.

(v) Insert into cancellation values (&cid,&pnr_no,&no_seats,’&addres’,&contactno,’&status’);

Execute above query repeatedly.

(vi)Insert into ticket values (&pnr_no,&ticket_id,&age,’&sex’,’&journey_datae’,’&source’,’&destination’,&amount);

Execute above query repeatedly.

Retrieving data from Tables:

1.      Select * from bus;

+-------+---------+-----------+---------+------------+---------+--------+------+------------+------+

| busno | routeno | starttime | bustype | availseats | deptime | source | dest | date            | fare |

+-------+---------+-----------+---------+------------+---------+--------+------+------------+------+

|  1246 |      8          | 8:30AM   | AC           |         60      | 5:00PM  | Hyd      | Vij   | 2010-08-21 |  500 |

+-------+---------+-----------+---------+------------+---------+--------+------+------------+------+

1 row in set (0.00 sec)

2.      Select * from passenger;

+-------+-------+--------+------+------+------+

| pnrno | tktno | name   | age  | sex  | ppno |

+-------+-------+--------+------+------+------+

|     1246 |     1 | rama  |   25 | male | a1   |

|     1247 |    2 | manoj |   26 | male | a2   |

+-------+-------+--------+------+------+------+

2 rows in set (0.01 sec)

3.      Select * from reservation;

+---------+------------+-------------+--------+---------+-----------+

| rpnr_no | jdate      | no_of_seats | status | address | contactno |

+---------+------------+-------------+--------+---------+-----------+

|       1246 | 2010-08-20 |           2 | yes    | hyd     | 9985441876|

|       1247 | 2010-08-20 |           2 | yes    | hyd     | 9985441876 |

+---------+------------+-------------+--------+---------+-----------+

2 rows in set (0.02 sec)

4.      Select * from cancellation;

+---------+------------+-------------+--------+---------+-----------+

| rpnr_no | jdate      | no_of_seats | status | address | contactno |

+---------+------------+-------------+--------+---------+-----------+

|       1246 | 2010-08-20 |           2 | yes    | hyd     | 9985441876|

|       1247 | 2010-08-20 |           2 | yes    | hyd     | 9985441876 |

+---------+------------+-------------+--------+---------+-----------+

2 rows in set (0.02 sec)

5.      Select * from ticket;

+-----------+------------+------+------+--------+------+----------+

| ticket_no | j_date     | age  | sex  | source | dest | dep_time |

+-----------+------------+------+------+--------+------+----------+

|         1 | 2010-08-20 |   25 | male | hyd    | bang | 9:30PM     |

|         2 | 2010-08-20 |   25 | male | hyd    | bang | 9:30 PM    |

|         3 | 2009-02-20 |   25 | male | hyd    | bang | 9:30 PM    |

+-----------+------------+------+------+--------+------+----------+

3 rows in set (0.02 sec)

The UPDATE Statement: The UPDATE statement is used to update existing records in a table.

 UPDATE Syntax

UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value

Ex: Update passenger set age=20 where pnrno=1246;

Query OK, 1 row affected (0.02 sec)

Rows matched: 1  Changed: 1  Warnings: 0

Select * from passenger;

+-------+-------+--------+------+------+------+

| pnrno | tktno | name   | age  | sex  | ppno |

+-------+-------+--------+------+------+------+

|     1246 |     1 | rama  |   20 | male | a1   |

|     1247 |    2 | manoj |   26 | male | a2   |

+-------+-------+--------+------+------+------+

2 rows in set (0.01 sec)

Experiment 7: Querying

The following are basic rules for combining the result sets of two queries by using UNION

1. The number and the order of the columns must be the same in all queries.
2. The data types must be compatible.

The syntax is as follows:

SELECT C1,C2 FROM T1

UNION 

SELECT C1,C2 FROM T2

SELECT C1,C2 FROM T1

INTERSECT

SELECT C1,C2 FROM T2

The EXCEPT (MINUS) operates on two SQL statements. It takes all the results from the first SQL statement, and then subtract out the ones that are present in the second SQL statement to get the final answer. If the second SQL statement includes results not present in the first SQL statement, such results are ignored.

EXCEPT returns any distinct values from the left query that are not also found on the right query.

The basic rules for combining the result sets of two queries that use EXCEPT are the following

1. The number and the order of the columns must be the same in all queries. 
2. The data types must be compatible.

The syntax is as follows:

[SQL Statement 1]
EXCEPT
[SQL Statement 2]

SELECT C1,C2 FROM T1

EXCEPT

SELECT C1,C2 FROM T2

Practicing queries:

1. Display unique PNR_NO of all passengers.

Select distinct pnr_no from passenger;

+--------+

| pnr_no |

+--------+

|      1 |

|      2 |

|      3 |

2. Display all the names of male passengers.

Select  pname  from passenger where sex=’male’;

+-------+

| name  |

+-------+

| rama  |

| manoj |

+-------+

3. Display ticket numbers and names of all the passengers

Select name,tkt_no from passenger;

+--------+---------+

| tkt_no | name    |

+--------+---------+

|   1001 | rama    |

|   1002 | sita    |

|   1003 | manoj   |

|   1004 | manisha |

4. Display the source and destination having journey time more than 10 hours

Select source,dest from bus where (departuretime-starttime)>10;

+--------+------+

| source | dest |

+--------+------+

| Hyd    | bang |

+--------+------+

1 row in set (0.00 sec)

5. Find ticket numbers of the passengers whose name start with ‘A’ and end with ‘H’

Select pname from passenger where pname like “A%H”;

+-------+

| pname  |

+-------+

| ash  |

+-------+

6. Find the names of passengers whose age between  30 and 45

Select pname from passenger where age beween 30 and 45;

+-------+

| pname |

+-------+

| rama   |

| manoj |

+-------+

7. Display all the names beginning with ‘A’

Select pname from passenger where pname like “A%”;

+-------+

| pname |

+-------+

| anand  |

| ajay      |

+-------+

8. Display the sorted list of passengers

Select pname from passenger order by pname;

+-------+

| pname |

+-------+

| anand  |

| ajay     |

| manisha |

| manoj  |

| rama    |

| sita      |

9. Display the bus numbers that travel on Sunday and Wednesday

SELECT busnumber FROM bus WHERE dayname(j_date)= 'Sunday' OR dayname(j_date)= 'Wednesday';

+-------+

| busno |

+-------+

|     3 |

|     4 |

+-------+

10. Display the details of passenger who are traveling either in AC or NONAC

SELECT * from passenger WHERE bustype IN (‘AC’,’NONAC’) AND (passenger.pnrno=reservation.pnrno);

+--------+---------+------+-------+--------+-----------+

| pnr_no | name    | age  | sex   | ppno   | ticket_no |

+--------+---------+------+-------+--------+-----------+

|      3 | manoj   |    4 | male  | 101003 |      1003 |

|      4 | manisha |    6 | fmale | 101004 |      1004 |

+--------+---------+------+-------+--------+-----------+

Experiment 8 and Experiment  9: Querying (continued…)

1. Write a Query to display the information present in the Passenger and cancellation tables.

Select * from passenger UNION select * from cancellation;

+-------+------------+--------+----------------------+------+-----------+

| pnrno | tktno      | name   | age                  | sex  | ppno      |

+-------+------------+--------+----------------------+------+-----------+

|     1 | 1                 | vissu     | 25                   | male   | a1        |

|     2 | 2                 | vissu1  | 26                   | male    | a2        |

|     2 | 2010-08-20 | 2      | yes                  | hyd  | 998544187 |

|     1 | 2010-08-20 | 2      | yes                  | hyd  | 998544187 |

+-------+------------+--------+----------------------+------+-----------+

2. Find number of tickets booked for each PNR_no using GROUP BY CLAUSE

select count(ticket_no),pnr_no from passenger GROUP BY pnr_no;

+--------------+-------+

| count(tktno) | pnrno |

+--------------+-------+

|            1 |     1 |

|            1 |     2 |

+--------------+-------+

2 rows in set (0.00 sec)

3. Find the distinct PNR numbers that are present

Select distinct pnrno from passenger;

+-------+

| pnrno |

+-------+

|     1 |        

|     2 |

+-------+

2 rows in set (0.00 sec)

4. Find the number of tickets booked in each class where the number of seats greater than 1

select rpnr_no,no_of_seats from reservation group by rpnr_no having no_of_seats > 1;

+----------------+-------------+

| count(rpnr_no) | no_of_seats |

+----------------+-------------+

|              1 |           2 |

|              1 |           2 |

+----------------+-------------+

2 rows in set (0.00 sec)

5. Find the total number of cancelled seats

Select count(no_of_seats) from cancellation;

+--------------------+

| count(no_of_seats) |

+--------------------+

|                  2 |

+--------------------+

6. Write a Query to count the number of tickets for the buses, which traveled after the date ‘14/3/2009’.

select count(ticket_no),j_date from ticket group by j_date having j_date>'2009/03/14';

+------------------+------------+

| count(ticket_no) | j_date     |

+------------------+------------+

|                1 | 2010-08-20 |

+------------------+------------+

1 row in set (0.00 sec)

Experiment 10: Triggers

A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for keeping the integrity of the information on the database. For example, when a new record (representing a new worker) is added to the employees table, new records should be created also in the tables of the taxes, vacations, and salaries.

Here are typically three triggering events that cause data triggers to 'fire':

• INSERT event (as a new record is being inserted into the database).
• UPDATE event (as a record is being changed).
• DELETE event (as a record is being deleted).

Structurally, triggers are either "row triggers" or "statement triggers". Row triggers define an action for every row of a table, while statement triggers occur only once per INSERT, UPDATE, or DELETE statement. DML triggers cannot be used to audit data retrieval via SELECT statements, because SELECT is not a triggering event.
Furthermore, there are "BEFORE triggers" and "AFTER triggers" which run in addition to any changes already being made to the database, and "INSTEAD OF trigger" which fully replace the database's normal activity.
Triggers do not accept parameters, but they do receive information in the form of implicit variables. For row-level triggers, these are generally OLD and NEW variables, each of which have fields corresponding to the columns of the affected table or view; for statement-level triggers, something like SQL Server's Inserted and Deleted tables may be provided so the trigger can see all the changes being made.
For data triggers, the general order of operations will be as follows:

1. a statement requests changes on a row: OLD represents the row as it was before the change (or is all-null for inserted rows), NEW represents the row after the changes (or is all-null for deleted rows)
2. each statement-level BEFORE trigger is fired
3. each row-level BEFORE trigger fires, and can modify NEW (but not OLD); each trigger can see NEW as modified by its predecessor, they are chained together
4. if an INSTEAD OF trigger is defined, it is run using OLD and NEW as available at this point
5. if no INSTEAD OF trigger is defined, the database modifies the row according to its normal logic; for updatable views, this may involve modifying one or more other tables to achieve the desired effect; if a view is not updatable, and no INSTEAD OF trigger is provided, an error is raised
6. each row-level AFTER trigger fires, and is given NEW and OLD, but its changes to NEW are either disallowed or disregarded
7. each statement-level AFTER trigger is fired
8. implied triggers are fired, such as referential actions in support of foreign key constraints: on-update or on-delete CASCADE, SET NULL, and SET DEFAULT rules

In ACID databases, an exception raised in a trigger will cause the entire stack of operations to be rolled back, including the original statement.

Triggers in Oracle

In addition to triggers that fire when data is modified, Oracle 9i supports triggers that fire when schema objects (that is, tables) are modified and when user logon or logoff events occur. These trigger types are referred to as "Schema-level triggers".

Schema-level triggers

• After Creation
• Before Alter
• After Alter
• Before Drop
• After Drop
• Before Logoff
• After Logon

The two main types of triggers are:

1. Row Level Trigger
2. Statement Level Trigger

Based on the 2 types of classifications, we could have 12 types of triggers.

Ø  Create a trigger on passenger to change the available seats and passenger details for every 60 days

Create trigger updcheck before insert on passenger

For each row

Begin

  If new.ticket_id> 60 then

RAISE_APPLICATION_ERROR(-20000, ' Bus is Full ' );

Else

Set new.ticket_id=0;

End if;

End;

O/P:@updcheck
Query OK, 0 rows affected (0.00 sec)
Insert into passenger values(678,61,’bob’,26,’male’,’a10’);
ORA 20000 Bus Is Full
ORA-06512:@88 system.updcheck , 5,5


Experiment 11: Procedures

The procedure is a program that performs an action and does not return a value (outside of IN OUT and OUT parameters).

A procedure is declared as:

CREATE OR REPLACE PROCEDURE ()

AS

  BEGIN---- END;

Ø  Write a Procedure To find no.of tickets passengers who age is greater than 20

Create procedure myproc()
Begin
Select count(ticket_id) from ticket where age>=20;
End;
To Execute this:
Call myproc()
+------------------+
| count(ticket_id) |
+------------------+
|                3 |
+------------------+

Experiment 12: Cursors:

Every SQL statement executed by the Oracle server has an individual cursor associated with it and are called implicit cursors. There are two types of cursors.

Implicit cursors: Declared for all DML and PL/SQL SELECT statements.

Explicit cursors: Declared and names by the programmer.

Explicit Cursors:

• Individually process each row returned by a multiple row select statement.
• A PL/SQL program opens a cursor, processes rows returned by a query, and then closes the cursor. The cursor marks the current position in the active set.

• Can process beyond the first row returned by the query, row by row.
• Keep track of which row is currently being processed.
• Allow the programmer to manually control explicit cursors in the PL/QL block.

Controlling Explicit Cursors:

• Declare the cursor by naming it and defining the structure of the query to be performed. Within it.
• Open the cursor: The OPEN statement executes the query and binds the variables that are referenced. Rows identified by the query are called the active set and are now available for fetching.
• Fetch data from the cursor: After each fetch, you test the cursor for any existing row. If there are no more rows to process, then you must close the cursor.
• Close the cursor: The CLOSE statement releases the active set of rows. It is now possible to reopen the cursor to establish a fresh active set.

Syntax:

Declaring a cursor:

CURSOR cursor_name IS

     Select_statement;

Opening a cursor:

OPEN cursor_name;

Fetch data from a cursor:

FETCH cursor_name INTO [variable1, variable2,….]| record_name];

Closing a cursor:

Close cursor_name;

Example:

Set SERVEROUTPUT ON

DECLARE

V_empno  employees.employee_id%TYPE;

V_ename  employees.last_name%TYPE;

    CURSOR emp_cursor IS

          SELECT employee_id, last_name

          FROM employees;

BEGIN

    OPEN emp_cursor;

    FOR I IN 1..10 LOOP

       FETCH emp_cursor INTO v_empno, b_ename;

       DBMS_OUTPUT. PUT_LINE (TO_CHAR(v_empno) || ‘ ‘ || v_ename);

    END LOOP

    CLOSE emp_cursor;

Attributes of an Explicit Cursor:

• %ISOPEN [is cursor open]
• %NOTFOUND [is row not found]
• %FOUND [is row found]
• %ROWCOUNT [rows returned so far]

Cursors can be passed parameters. Cursors also have FOR UPDATE option which allows more fine grained control of locking at a table level. WHERE CURRENT OF can be used to apply the update or delete operation to current row in the cursor.