dbms lab programs

PART 1

DATABASE

Week 1

Entity: An entity may be defined as a thing which is recognized as being capable of an

independent existence and which can be uniquely identified.

An entity may be a physical object such as a house or a car, an event such as a house sale

or a car service, or a concept such as a customer transaction or order.

Attribute: A single data item related to a database object. The database schema

associates one or more attributes with each database entity.

Also Known As: field, column

Entity-Attribute Diagram:

Types of attributes: There are different types of attributes

1. Domain Attribute: The set of possible values for an attribute is called the

domain of the attribute.

2.Key Attribute: The attribute (or combination of attributes) that is unique for every

entityinstance.

Eg: The account number of account

The employee id of an employee

Divorced

Marital status

Single

Married Widowed

Bus

Name

Bus

Bus no

Bname

3. Simple Attribute: The attribute which can’t be broken-down into small components.

4. Composite Attribute: The attribute which can be split into components.

5. Single valued Attribute: The attribute can take on only a single value for each entity

instance.

6. Multi-valued Attribute: An attribute that may take more than one value for a given

entity. It is represented with double ellipse.

7. Stored Attribute: Attribute that need to be stored permanently.

Eg: Name of employee

Day

Month

Year

Date

Addr

Hno

Street City

Pincode

AGE

Phno

8. Derived Attribute: Attribute that can be calculated based on other attributes. It is

represented with dotted lines.

Types of keys: There are different types of keys in the database.

1. Primary key: The value this key holds should be unique for each record in the

database. Every database table should have one or, more columns designated as the

primary key.

Eg: Bus no in the Bus record or table

PNR no in the passenger table

2. Candidate key: A candidate key is a combination of attributes that can be uniquely

used to identify a database record without any extraneous data. Each table may have one

or more candidate keys. One of these candidate keys is selected as the table primary key.

Eg: An entity Ticket contains the attributes tno, bus no, PNR no, no-of-seats

(candidate keys)

3. Foreign key: A foreign key (FK) is a column or combination of columns that is used

to establish and enforce a link between the data in two tables.

In a foreign key reference, a link is created between two tables when the column or

columns that hold the primary key value for one table are referenced by the column or

columns in another table. This column becomes a foreign key in the second table.

Eg: bus (bus no, bname, src, dest)

Ticket (tno, bus no, src, dest, age, gender)

EMP

Current Date

Joining Date

Bus no is the foreign key in the ticket entity.

4. Composite key: Sometimes it requires more than one attribute to uniquely identify an

entity. A primary key that made up of more than one attribute is known as a composite

key.`

Eg: ticket (tno, bus no, age)

Tno & bus no are primary keys.

5. Super key: A super key is a combination of attributes that can be uniquely used to

identify a database record. A table might have many super keys.

Eg: bus (bus no, bname, src, dest)

<Bus no> <bus no, src> <bname, src> <bus no, dest>

6. Partial key: A weak entity type normally has a partial key, which is the set of attribute

that can uniquely identify weak entities that are related to the same owner entity.

Entities:

Bus (Bus no, No-of-seats, Type)

Ticket (Tno, Journey Date, Seat no, Src, Dest, Deptime, Gender, Bus no)

Passenger (PNR no, Pname, Age, Gender, Addr, Phno, Type)

Driver (Did, Dname, Addr, Age, Phno, Brno, Bus no)

MD (MDname, Phno, Addr, Gender, Age)

Branch (Brno, Brname, Addr, Phno, Email-Id)

Entity-Attributes with Data Types:

BUS Entity

Bus no: Varchar (20): Primary key

No-of-seats: Int (10)

Type: Varchar2 (20)

TICKET Entity

Tno: Int (10): Primary key

Journey Date: Date

Seat no: Int (10)

Src: Varchar (20)

Dest: Varchar (20)

Deptime: Time

Gender: Varchar (10): Male/Female

Bus no: Varchar (20): Foreign key

PASSENGER Entity

PNR no: Int (10): Primary key

Pname: Varchar (20)

Age: Int (10)


Addr: Varchar (30)

Phno: Varchar (20)

RESERVATION Entity

PNR no: Int (10): Foreign key

Journey Date: Date


Addr: Varchar (30)

Phno: Varchar (20)

Status: Varchar (10): Yes/No

CANCELLATION Entity

PNR no: Int (10): Foreign key

Journey Date: Date


Addr: Varchar (30)

Phno: Varchar (20)

Status: Varchar (10): Yes/No

All these entities are used for extract data when necessary while writing the queries in the

coming weeks. There are some extra entities which are not used for writing queries.

DRIVER Entity

Did: Int (10): Primary key

Dname: Varchar (20)

Addr: Varchar (30)

Age: Int (10)

Brno: Int (10): Foreign key

Bus no: Varchar (20): Foreign key

Phno: Varchar (20)

MD Entity

MDname: Varchar (20)

Phno: Varchar (20)

Addr: Varchar (30)


Age: Int (10)

BRANCH Entity

Brno: Int (10): Primary key

Brname: Varchar (20)

Addr: Varchar (30)

Phno: Varchar (20)

Email-Id: Varchar (30)

Week 2

Relationship: A relationship type between two entity types defines the set of all

associations between these entity types

Each instance of the relationship between members of these entity types is called a

relationship instance

Eg: passenger travels-in bus, here travels-in is a relation between passenger n bus

entity.

Entity diagrams with Attributes:

Bus Entity:

Ticket Entity:

Bus

Bus no

No-of-seats

Type

TicketBus no

Tno

Deptime

Src

Seat no

DestGender

Journey Date

Passenger Entity:

Driver Entity:

MD Entity:

Passenger

PhnoAge

Pname

Gender

Addr

PNR no

Driver

Did

Dname

Addr Age

Brno

Bus noPhno

Branch Entity:

Cardinalities: Cardinality

* Relationships can have different connectivity

A ratio, such as 1:1, 1: N, M: 1, and M: N, gives a cardinality constraint or numeric

restriction on the possible relationships

One-to-one (1:1): relationship from entity type S to entity type T is one in which an

entity from S is related to at most one entity from T and vice versa.

MD

AddrGender

PhnoAge

MDname

Branch

Brname

Brno

AddrPhno

Email-Id

One-to-many (1: N): relationship from entity type S to entity type T is one in which two

or more entities from T can be related to an entity from S.

Many-to- One (M: 1): relationship from entity type S to entity type T is one in which an

entity from S can be related to two or more entities from T.

PassengerTicketReserve

s

PNR noPname

Age

Gender

Addr Phno

Tno

Seat No

Journey Date

Dest

Src

Deptime

Bus no

Gender

Bus PassengerContains

Bus no

Type

No-of-seats

Phno

Addr

PNR no

Age

Gender

Pname

Many-to-many (M:N): relationship from entity type S to entity type T is one in which

an entity from S can be related to two or more entities from T, and an entity from T can

be related to two or more entities from S.

E.g.:

Passenger Ticket (1:1)

Bus Passenger(1:N)

Branch MD (M: 1)

Driver Branch (M: N)

Entity-Relationship Diagrams:

Types of Entities:

There are 2 types of entities

1. Strong Entity : A Strong Entity is one that exists on its own, independent of other

entities. An entity set that has primary key is termed as strong entity set.

MD BranchOwns

PhnoMDName

Age

GenderAddr

Email-Id

Brno

Phno

Addr

Brname

Bus

TicketDriver

MDPassenger

Branch

Drives

Contains

Reserves

Owns

Issues

2. Weak Entity : A weak entity can be identified uniquely only by considering the

primary key of another (owner) entity.

Owner entity set and weak entity set must participate in a one-to-many

relationship set (one owner, many weak entities).

Weak entity set must have total participation in this identifying relationship set.

Aggregation: Aggregation refers to an abstraction in which a relationship between

objects is regarded as a higher-level object.

Generalization: It is a relationship that exists between a high level entity set and one or

more lower level entity set.

Specialization: Is the process of defining a set of subclasses of a super class .The set of

subclasses is based upon some distinguishing characteristics of the entities in the super

class. Super class/subclass relationships and specialization can be diagrammatically

represented in ER diagrams.

Generalization Diagram:

MD

Owns

Branch1HYD

Branch3Chennai

Branch2Mumbai

Week 3

Schema: The schema defines the tables, the fields in each table, and the relationships

between fields and tables.

Schemas for Entities:

Bus Schema:

Bus no No-of-seats Type

Bus (Bus no Primary key Varchar (20), No-of-seats Int (10), Type Varchar (20))

Ticket Schema:

Tno Journey Date

Day Seat no Src Dest Deptime Gender Bus no

Ticket (Tno Primary key Int (10), Journey Date Date, Day Varchar (10), Src Varchar

(20), Dest Varchar (20), Deptime Varchar (20), Gender Varchar (10), Bus no Foreign key

Varchar (20))

Passenger Schema:

PNR no Pname Age Gender Addr Phno

Passenger (PNR no Primary key Int (10), Pname Varchar (20), Age Int (10), Gender

Varchar (10), Addr Varchar (30), Phno Varchar (20))

Reservation Schema:

PNR no Journey Date No-of-seats Addr Phno Status

Reservation (PNR no Foreign key Int (10), Journey Date Date, No-of-seats Int (10),

Addr Varchar (30), Phno Varchar (20), Status Varchar (10))

Cancellation Schema:

PNR no Journey Date No-of-seats Addr Phno Status

Cancellation (PNR no Foreign key Int (10), Journey Date Date, No-of-seats Int (10),

Addr Varchar (30), Phno Varchar (20), Status Varchar (10))

Tables for Each Entity:

Bus Table:

Bus no No-of-seats Type

AP10A05 40 A/C

AP23B25 40 Super Luxury

AP29B78 50 Express

AP10A05 40 Super Deluxe

AP29J501 50 Express

Ticket Table:

Tno Journey Day Seat Src Dest Deptime Gender Bus no

Date no

2100 15/08/10 Sun 35 Hyd Delhi 10hrs M AP1000

2710 20/08/10 Mon 28 B’lore Hyd 15hrs F AP2014

1020 22/08/10 Wed 38 Delhi B’lore 18hrs F AP2387

6100 10/08/10 Tue 20 Hyd Chennai 14hrs M AP09525

1478 25/08/10 Wed 15 Chennai Delhi 06hrs F AP29457

Passenger Table:


457 Satish 27 M Plot No. 5 RK. Puram, Hyderabad 9008745625,

147 Sheela 25 F H.No:1-9-88,DilsukhNagar,Hyderabad 9000998683,

258 Vinay 24 M H.No:11-12-31, Hospet 9908730203

501 Kiran 29 M Plot no:35, Gandhinagar Hyderabad 9247881452

605 Bindu 22 F Plot no:34,Arandalpet, Guntur 9052980015

Reservation Table:

PNR

no

Journey

Date

No-of-

seats

Address Phno Status

457 10/08/10 40 Plot No. 5 RK. Puram, Hyderabad 9008745625, Yes

147 20/08/10 40 H.No:188,DilsukhNagar,Hyderabad 9000998683, No

258 25/08/10 40 H.No:11-12-31, Hospet 9908730203 Yes

501 14/08/10 40 Plot no:35, Gandhinagar Hyderabad 9247881452 No

605 18/08/10 40 Plot no:34,Arandalpet, Guntur 9052980015 Yes

Cancellation Table:

PNR

no

Journey

Date

No-of-

seats

Addr Phno Status

457 10/08/10 40 Plot No. 5 RK. Puram, Hyderabad 9008745625, Yes

147 20/08/10 40 H.No:188,DilsukhNagar,Hyderabad 9000998683, No

258 25/08/10 40 H.No:11-12-31, Hospet 9908730203 Yes

501 14/08/10 40 Plot no:35, Gandhinagar Hyderabad 9247881452 No

605 18/08/10 40 Plot no:34,Arandalpet, Guntur 9052980015 Yes

Week 4

Normalization: The process of decomposing relations with anomalies to produce smaller

well structured relations.

Normal Form: A state of a relation that results from applying simple rules regarding

functional dependencies to that relation.

Functional Dependencies: A functional dependency is a constraint between two

attributes or two sets of attributes.

A functional dependency of B on A represented by an arrow A->B

Eg: Bus no->Bname, Type

1 st Normal Form: A relation in the 1st normal form if it contains no multivalue attributes.

2 nd Normal Form : A relation is in 2nd normal form if it is in 1st normal form and every

non-key attribute is fully functionally dependent on the primary key.

Partial Dependency is functional dependencies in which one or more non-key

attributes are functionally depends on part of the primary key.

3 rd Normal Form : A relation is in 3rd normal form if it is in 2nd normal form and no

transitive dependency exists.

Transitive dependency is a relation is a functional dependency between two (or

more) non-key attributes.

Boyce Codd Normal Form: A relation in a Boyce Codd normal form iff every

determinant in the relation is a candidate key.

4 th Normal Form : When a relation is in BCNF there are no longer any anomalies that

result from functional dependency.

Eg: 1st Normal Form:







Multivalue attributes are normalized into single value attributes.










Eg: 2nd Normal Form:










PNR no is the only primary key .so the partial dependency is as follows:

PNR no -> Pname, Age, Gender

PNR no -> Addr, Phno

PNR no Pname Age Gender

457 Satish 27 M

746 Sheela 25 F

129 Vinay 24 M

231 Kiran 29 M

654 Bindu 22 F

PNR no Addr Phno

457 Plot No. 5 RK. Puram, Hyderabad 9008745625,

746 H.No:1-9-88,DilsukhNagar,Hyderabad 9000998683,

129 H.No:11-12-31, Hospet 9908730203

231 Plot no:35, Gandhinagar Hyderabad 9247881452

654 Plot no:34,Arandalpet, Guntur 9052980015

Eg: 3rd Normal Form:

Tno Journey

Date

Day Seat

no

Src Dest Deptime Gender Bus no

2100 15/08/10 Sun 35 Hyd Delhi 10hrs M AP1000

2710 20/08/10 Mon 28 B’lore Hyd 15hrs F AP2014

1020 22/08/10 Wed 38 Delhi B’lore 18hrs F AP2387

6100 10/08/10 Tue 20 Hyd Chennai 14hrs M AP09525

1478 25/08/10 Wed 15 Chennai Delhi 06hrs F AP29457

Tno -> Journey Date

Journey Date -> Day

The Table is normalized as follows:

Tno Seat

no

Src Dest Deptime Gender Bus no

2100 35 Hyd Delhi 10:00 M AP1000

2710 28 B’lore Hyd 15:00 F AP2014

1020 38 Delhi B’lore 18:00 F AP2387

6100 20 Hyd Chennai 14:00 M AP09525

1478 15 Chennai Delhi 06:00 F AP29457

Journey Date Day

15/08/10 Sun

20/08/10 Mon

22/08/10 Wed

10/08/10 Tue

25/08/10 Wed

Week 5

1. Steps for installing MySQL

Step 1Make sure you already downloaded the MySQL essential 5.0.45 win32.msi file. Double click on the .msi file.

Step 2This is MySQL Server 5.0 setup wizard. The setup wizard will install MySQL Server 5.0 release 5.0.45 on your computer. To continue, click next.

Step 3Choose the setup type that best suits your needs. For common program features select Typical and it’s recommended for general use. To continue, click next.

Step 4

This wizard is ready to begin installation. Destination folder will be in C:\Program Files\MySQL\MySQL Server 5.0\. To continue, click next.

Step 5The program features you selected are being installed. Please wait while the setup wizard installs MySQL 5.0. This may take several minutes.

Step 6To continue, click next.

Step 7To continue, click next.

Step 8Wizard Completed. Setup has finished installing MySQL 5.0. Check the configure the MySQL server now to continue. Click Finish to exit the wizard

d.

Step 9The configuration wizard will allow you to configure the MySQL Server 5.0 server instance. To continue, click next.

Step 10Select a standard configuration and this will use a general purpose configuration for the server that can be tuned manually. To continue, click next.

Step 11

Check on the install as windows service and include bin directory in windows path. To continue, click next.

Step 12Please set the security options by entering the root password and confirm retype the password. To continue, click next.

Step 13Ready to execute? Clicks execute to continue.

Step 14Processing configuration in progress.

Step 15Configuration file created. Windows service MySQL5 installed. Press finish to close the wizard.

WEEK 6

Mysql> create table BUS(BUS_NO varchar(10) primary key, source varchar(10),destination varchar(10));

Query OK, 0 rows affected (0.06 sec)

Mysql> desc BUS;

Field Type Null Key Default ExtraBUS_NO Sourcedestination

varchar(10) varchar(10) varchar(10)

NOYESYES

PRINULLNULL

3 rows in set (0.00 sec)

mysql> insert into BUS values('9W01','HYD','CHENNAI');Query OK, 1 row affected (0.02 sec)

mysql> insert into BUS values('3A06','MUMBAI','BANGLORE');Query OK, 1 row affected (0.03 sec)

mysql> insert into BUS values('4B08','HYD','BANGLORE');

Query OK, 1 row affected (0.03 sec)

mysql> insert into BUS values('6Z07','MUMBAI','CHENNAI');Query OK, 1 row affected (0.03 sec)

mysql> insert into BUS values('8Y08','CHENNAI','HYD');Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM BUS;

BUS_NO source destination3A064B08 6Z07 8Y089W01

MUMBAIHYDMUMBAICHENNAIHYD

BANGLOREBANGLORECHENNAIHYD CHENNAI


mysql>create table Ticket(Ticket_No integer(10) primary key, journey_date date, age integer(10),sex varchar(10), source varchar(10), destination varchar(10), dep_time varchar(10), TBUS_NO varchar(10) references BUS(BUS_NO));

mysql> desc Ticket;

Field Type Null Key Default ExtraTicket Nojourney dateagesexsourcedestinationdep_timeTBUS_NO

int(10)datetimeint(10)varchar(10)varchar(10)varchar(10)varchar(10)varchar(10)

NOYESYESYESYESYESYESYES

PRINULLNULLNULLNULLNULLNULLNULL


mysql>insert into Ticket values(20,’20101010’,36,,’F’,’CHENNAI’,’HYD’,’19:30:00’,’8Y08’);Query OK, 1 row affectedmysql>insert into Ticket values(22,’20100810’,25,,’M’,’HYD’,’CHENNAI’,’18:30:00’,’9W01’);Query OK, 1 row affected

mysql>insert into Ticket values(23,’20100909’,44,,’M’,’MUMBAI’,’CHENNAI’,’12:30:00’,’6Z07’);Query OK, 1 row affectedmysql>insert into Ticket values(23,’20100910’,34,,’F’,’MUMBAI’,’BANGLORE’,’17:30:00’,’3A06’);Query OK, 1 row affectedmysql>insert into Ticket values(33,’20101120’,30,,’F’,’HYD’,’BANGLORE’,’22:30:00’,’4B08’);Query OK, 1 row affected

mysql> select * from Ticket;

Ticket_No journey_date age sex source destination dep_time TBUS_No2022232333

2010-10-10 00:00:002010-08-10 00:00:002010-09-09 00:00:002010-09-10 00:00:002010-11-20 00:00:00

3625443430

FMMFF

CHENNAIHYD

MUMBAIMUMBAI

HYD

HYDCHENNAICHENNAI

BANGLOREBANGLORE

19:30:0018:30:0012:30:0017:30:0022:30:00

8Y089W016Z073A064B08

mysql> create table passenger(PNR_NO integer(10) primary key,Ticket_No integer(10) references Ticket(Ticket_No),name varchar(10),age integer(10),sex varchar(10),PPNO varchar(10),category varchar(10),journey_time integer(10),source varchar(10),destination varchar(10));Query OK, 0 rows affected (0.06 sec)mysql> desc passenger;

Field Type Null Key Default ExtraPNR_NOTicket_NoNameAgeSexPPNOCategoryjourney_timesourcedestination

int(10)int(10)varchar(10)int(10) varchar(10)varchar(10)varchar(10)int(10)varchar(10)varchar(10)

NOYESYESYESYESYESYESYESYESYES

PRINULLNULLNULLNULLNULLNULLNULLNULLNULL


mysql> insert into passenger values(1,20,'Ramu',35,'M','abc123','AC',12,'CHENNAI','HYD','8Y08'); Query OK, 1 row affected (0.05 sec)

mysql> insert into passenger values(2,33,'hansika',25,'M','abc139','AC',10,'HYD',’BANGALORE’,'4B08'); Query OK, 1 row affected

mysql> insert into passenger values(3,22,'Amith',26,'M','abc125','AC',11, 'HYD', 'CHENNAI','9W01');Query OK, 1 row affected

mysql> insert into passenger values(6,23,'Avinash',36,'M','abc127','AC',13,’MUMBAI’,'CHENNAI','6Z07'); Query OK, 1 row affected

mysql> insert into passenger values(7,24,'Anand',28,'M','abc128','NON AC',8,’MUMBAI’,’BANGALORE’ ,'3A06'); Query OK, 1 row affected

mysql> select * from passenger;

PNR_NO

Ticket_No

name age sex PPNO category journey_time

source Destination PBUS_NO

12367

2033222324

RAMUHANSIKAAMITHAVINASHANAND

3525263628

MFMMM

abc123abc139abc125abc127abc128

ACACACACNONAC

121011138

CHENNAIHYDHYDMUMBAIMUMBAI

HYDBANGLORECHENNAICHENNAIBANGLORE

8Y084B089W016Z073A06

5 rows in set (0.00 sec)mysql> create table Reservation(PNR_NO integer(10) references passenger(PNR_NO), journey_date date,no_of_seats integer(10),address varchar(10),Contact_NO varchar (10),status varchar(10),RBUS_NO varchar(10) references BUS(BUS_NO)); Query OK, 0 rows affected (0.06 sec)

mysql> desc Reservation;

Field Type Null Key Default ExtraPNR_NOjourney_dateno_of_seatsaddressContact_NOStatusRBUS_NO

int(10)dateint(10)varchar(10)varchar(10)varchar(10)varchar(10)

YESYESYESYESYESYESYES

NULLNULLNULLNULLNULLNULLNULL


mysql> insert into Reservation values(1,'2010/10/10',2,'HYD','9959190929','YES','9W01');

Query OK, 1 row affected (0.02 sec)mysql> insert into Reservation values(3,'2010/08/11',4,'MUMBAI','9985512234','YES','6Z07'); Query OK, 1 row affectedmysql> insert into Reservation values(6,'2010/09/10',5,'BANGALORE','9985587564','YES

','3A06'); Query OK, 1 row affectedmysql> insert into Reservation values(7,'2010/11/20',8,'HYD','9985587564','NO

','9W01'); Query OK, 1 row affectedmysql> insert into Reservation values(2,'2010/11/20',5,'CHENNAI','9985432123','NO

','4B08'); Query OK, 1 row affected

mysql> select * from Reservation;

PNR_NO journey_date no_of_seats address Contact_NO status RBUS_NO13672

2010-10-102010-08-112010-09-102010-11-202010-11-20

24585

HYDMUMBAIBANGLOREHYDCHENNAI

99591909299985512234998558756499855475649985432123

YESYESYESNO.NO

9W016Z073A069W014B08


Mysql>create table Cancellation(PNR_NO integer(10) references passenger(PNR_NO), journey_date date,no_of_seats integer(10),address varchar(10),Contact_NO varchar (10),status varchar(10),CBUS_NO varchar(10) references BUS(BUS_NO));mysql> desc cancellation;

| Field Type Null Key Default ExtraPNR_NOjourney_dateno_of_seatsaddressContact_NOStatusCBUS_NO

int(10)dateint(10)varchar(10)varchar(10)varchar(10)varchar(10)

YESYESYESYESYESYESYES

NULLNULLNULLNULLNULLNULLNULL


mysql> insert into Cancellation values(61'2010/12/09',3,'HYD','9985512524','YES','9W01');

Query OK, 1 row affected

mysql> select * from cancellation;

PNR_NO journey_date no_of_seats address Contact_NO status CBUS_NO12367

2010-12-092010-08-102010-09-102010-09-102010-11-20

34715

HYDCHENNAICHENNAIHYDMUMBAI

99855125249985512224998551122499855112949985511264

YESYESYESYESNO

9W016Z074B089W014B08


Week 7

1) Display unique PNR_no of all passengers ?

select PNR_NO from passenger where PNR_NO IN(select PNR_NO from passenger);

PNR_NO12367


2) DISPLAY ALL THE NAMES OF MALE PASSENGERS?

select name from passenger where sex IN(select sex from passenger where sex='M');

NameRAMUAMITHAVINASHANAND

3) DISPLAY THE TICKET NUMBERS AND NAMES OF ALL THE PASSENGERS?

select Ticket_No,name from passenger where PNR_NO IN(select PNR_NO from passenger);

Ticket_No Name20233222324



4) DISPLAY THE SOURCE AND DESTINATION HAVING JOURNEY TIME MORE THEN 10HRS?

select source,destination from passenger where journey_time IN(select jou rney_time from passenger where journey_time>10);

source destinationCHENNAIHYDMUMBAI

HYDCHENNAICHENNAI


5) FIND THE TICKET NUMBERS OF THE PASSENGERS WHO’S NAME START WITH ‘A’ AND ENDS WITH ‘H’?

select Ticket_No from passenger where name IN(select name from passenger where name like 'A%H');

Ticket_No222324


6) FIND THE NAMES OF PASSENGERS WHOS AGE IS BEETWIN 30 AND 40?

select name from passenger where age IN(select age from passenger where age between 30 and 45);

nameRAMUAVINASH


7) DISPLAY THE ALL THE PASSENGERS NAMES STARTS WITH ‘A’?

select * from passenger where name IN(select name from passenger where name like 'A%');

PNR_NO

Ticket_No



367

222324

AMITHAVINASHANAND

263628

MMM

abc125abc127abc128

ACACNON AC

11138

HYDMUMBAIMUMBAI

CHENNAICHENNAIBANGLORE

9W016Z073A06


8) DISPLAY THE SORTED LIST OF PASSENGERS NAMES? select name from passenger order by name;

nameAMITHANANDAVINASHHANSIKARAMU


9) DISPLAY THE BUS NUMBERS THAT TRAVEL ON SUNDAYAND WEDNESDAY?

select RBUS_NO from Reservation where journey_date IN(select journey_date from Reservation where dayname(journey_date)='Sunday' UNION select journey_date from Reservation where dayname(journey_date)='Wednesday');

RBUS_NO9W016Z07


10) DISPLAY THE DETAILS OF PASSENGERS WHO ARE TRAVELING EITHER IN ‘AC’OR ‘NON_AC’ (USING ONLY ‘IN’ OPERATOR)

select * from passenger where category IN('AC','NON AC');

PNR_NO

Ticket_No



12367

2033222324


3525263628

MFMMM

abc123abc139abc125abc127abc128

ACACACACNON AC

121011138

CHENNAIHYDHYDMUMBAIMUMBAI

HYDBANGLORECHENNAICHENNAIBANGLORE

8Y084B089W016Z073A06


Week 8&9

1) Write a query to display the info present in the passenger and cancellation table . (HINT: use UNION operator)

select PNR_NO,PBUS_NO from passenger UNION select PNR_NO,CBUS_NO from cancellation;

PNR_NO PBUS_NO1236712367

8Y084B089W016Z073A069W016Z074B089W014B08


2) Write quarry to display different traveling options available in British airway?

select * from bus;

BUS_NO source destination3A064B086Z078Y089W01


BANGLOREBANGLORECHENNAIHYDCHENNAI


3) Display the number of days in a week on which the 9W01 bus is available?

select count(journey_date),RBUS_NO,journey_date from Reservation where RBUS_NO='9W01' GROUP BY RBUS_NO HAVING journey_date IN(select journey_date from Reservation where dayname(journey_date) between 'Monday'and 'Sunday');

Count(journey_date) RBUS_NO journey_date2 9W01 2010-10-10

1 row in set (0.00 sec)

4) Find number of tickets booked for each PNR-no using GROUP BY CLAUSE.Hint:use GROUP BY on PNR_No? select count(Ticket_No),PNR_NO from passenger GROUP BY PNR_NO;

count(Ticket_No) PNR_NO11111

12367

5) Find the distinct PNR numbers that are present? select distinct PNR_NO from Reservation;

PNR_NO13672


6) find the number of tickets booked in each class where the number of seats is grater then ‘1’Hint: use GROUP BY, WHERE and HAVING CLAUSES? select PNR_NO,no_of_seats from Reservation GROUP BY PNR_NO HAVING no_of_seats>1;

PNR_NO no_of_seats12367

25458


7) Find the total number of cancelled seats? select sum(no_of_seats) from cancellation;

sum(no_of_seats)20

1 row in set (0.03 sec)

8) Write a query to count the number of tickets for the buses,which traveled after the date’14/3/2009’.Hint:use HAVING CLAUSES? select COUNT(Ticket_No),TBUS_NO,journey_date from Ticket GROUP BY TBUS_NO HAVING journey_date>'2009/3/14';

COUNT(Ticket_No) TBUS_NO journey_date11111

3A064B086Z078Y089W01

2010-09-10 00:00:002010-11-20 00:00:00 2010-09-09 00:00:002010-10-10 00:00:002010-08-10 00:00:00


VIEWS

CREATING VIEWS:

mysql> create view busview as select * from BUS; Query OK, 0 rows affected (0.02 sec)

mysql> select * from busview;



BANGLORE BANGLORE CHENNAI HYDCHENNAI


DROPPING VIEWS:

mysql> drop view busview;

WEEK 10

1. Triggers: To write a TRIGGER to ensure that Bus table does not contain duplicate of null values in Bus_No column.

INSERT TRIGGER:

mysql>delimiter //

mysql> create trigger insr before INSERT ON cancellation -> for each row -> begin -> update bus set source='HYDERABAD' where source='HYD'; -> end; -> //Query OK, 0 rows affected (0.00 sec)

mysql> select * from BUS; //

BUS_NO source destination3A064B086Z07

MUMBAIHYDMUMBAI

BANGLORE BANGLORE CHENNAI

8Y089W01

CHENNAIHYD

HYDCHENNAI


mysql>insert into cancellation values(7,'2010/12/11',4,'KOLKATA','9987654321','YES','9W01'); -> //Query OK, 1 row affected (0.03 sec)




BANGLORE BANGLORE CHENNAI HYDCHENNAI

UPDATE TRIGGER:

mysql> create trigger updt before update on passenger -> for each row -> begin -> insert into BUS values('6Y01','TIRUPATI','VIJAYAWADA'); -> end; -> //Query OK, 0 rows affected (0.13 sec)

mysql> update passenger set category='NON AC' where name='Ramesh'; -> //Query OK, 1 row affected (0.13 sec)Rows matched: 1 Changed: 1 Warnings: 0


BUS_NO source destination3A064B086Y016Z078Y08

MUMBAIHYDTIRUPATHIMUMBAICHENNAI

BANGLORE BANGLORE VIJAYAWADA CHENNAI HYD

9W01 HYD CHENNAI 6 rows in set (0.00 sec)

DELETE TRIGGER:

mysql> create trigger del before delete on passenger -> for each row -> begin -> insert into BUS values('7D0$','KOCHIN','SIMLA'); -> end; -> //Query OK, 0 rows affected (0.02 sec)

mysql> delete from passenger where name='Ramesh'; -> //Query OK, 1 row affected (0.03 sec)


BUS_NO source destination3A064B086Y016Z077D0$8Y089W01

MUMBAIHYDTIRUPATIMUMBAIKOCHINCHENNAIHYD

BANGLORE BANGLORE VIJAYAWADACHENNAI SIMLAHYDCHENNAI


WEEK 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;

A simple PL/SQL Procedure Example :

CREATE OR REPLACE PROCEDURE my_first_proc (p_name IN VARCHAR2 := 'Lewis', p_address IN VARCHAR2 := '123 Mockingbird Ln', p_an_in_out_parameter IN OUT NUMBER, p_an_out_parameter OUT DATE ) AS

v_a_variable VARCHAR2(30);

BEGIN IF p_name = 'Lewis' THEN DBMS_OUTPUT.PUT_LINE( p_name || ': ' || p_address ); END IF; v_a_variable := 99; p_an_in_out_parameter := v_a_variable; p_an_out_parameter := SYSDATE; END;

call this procedure as:

DECLARE v_employee VARCHAR2(30) := 'BillyBob'; v_number NUMBER := 22; v_date DATE;BEGIN my_first_proc( p_name => v_employee, p_an_in_out_parameter => v_number, p_an_out_parameter => v_date ); DBMS_OUTPUT.PUT_LINE( v_employee || ', ' || to_Char(v_number) || ', ' || to_char(v_date) );

my_first_proc( p_an_in_out_parameter => v_number, p_an_out_parameter => v_date ); DBMS_OUTPUT.PUT_LINE( v_employee || ', ' || to_Char(v_number) || ', ' || to_char(v_date) );END;

Example: To find no.of tickets passengers who age is greater than 40

Create procedure myproc()

Begin

Select count(ticket_id) from ticket where age>=40;

End;

Example:

mysql> delimiter //

mysql> create procedure retrieve() -> begin -> select COUNT(Ticket_No) from Ticket where age>=22; -> end; -> // Query OK, 0 rows affected (0.05 sec)mysql> delimiter ;mysql> CALL retrieve();

COUNT(Ticket_No)5

1 row in set (0.00 sec)Query OK, 0 rows affected (0.02 sec)

WEEK 12 Cursors: In SQL procedures, a cursor make it possible to define a result set (a set of data rows) and perform complex logic on a row by row basis. By using the same mechanics, an SQL procedure can also define a result set and return it directly to the caller of the SQL procedure or to a client application.

A cursor can be viewed as a pointer to one row in a set of rows. The cursor can only reference one row at a time, but can move to other rows of the result set as needed.

To use cursors in SQL procedures, you need to do the following: 1. Declare a cursor that defines a result set.2. Open the cursor to establish the result set.3. Fetch the data into local variables as needed from the cursor, one row at a time.4. Close the cursor when done

To work with cursors you must use the following SQL statements: DECLARE CURSOR OPEN FETCH CLOSE

The following example demonstrates the basic use of a read-only cursor within an SQL procedure:

CREATE PROCEDURE sum_salaries(OUT sum INTEGER) LANGUAGE SQL BEGIN DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; DECLARE p_sum INTEGER; DECLARE p_sal INTEGER; DECLARE c CURSOR FOR SELECT SALARY FROM EMPLOYEE; SET p_sum = 0;

OPEN c;

FETCH FROM c INTO p_sal;

WHILE(SQLSTATE = '00000') DO SET p_sum = p_sum + p_sal; FETCH FROM c INTO p_sal; END WHILE;

CLOSE c;

SET sum = p_sum;

END;

Example-2:

mysql> delimiter //mysql> create procedure myproc(IN pp VARCHAR(10)) -> BEGIN -> DECLARE s_age INTEGER; -> DECLARE s VARCHAR(10); -> DECLARE cur CURSOR FOR SELECT age,sex from passenger where PPNO=pp; -> OPEN cur; -> FETCH cur into s_age,s; -> insert into Ticket values(21,'2012/12/12',s_age,s,'EARTH','SWARGAM', '10:00:00','9W01'); -> Close cur; -> END; -> //Query OK, 0 rows affected (0.00 sec)

mysql> CALL myproc('abc123'); -> //Query OK, 1 row affected (0.03 sec)

mysql> select * from Ticket;

Ticket_No TBUS_NO journey_date age sex source destination dep_ti200021002200230024003300

8Y089W019W016Z073A064B08

2010-10-10 00:00:002012-12-12 00:00:002010-08-10 00:00:002010-09-09 00:00:002010-09-10 00:00:002010-11-20 00:00:00

363525443430

FMMMFF

CHENNAIEARTHHYDMUMBAIMUMBAIHYD

HYDSWARGAMCHENNAICHENNAIBANGLOREBANGLORE

19:30:10:00:18:30:12:30:17:30:22:30:


dbms lab programs

Documents

key attribute

bus attribute

bus bus

composite attribute

domain attribute

stored attribute

table primary key

simple attribute