Question

code the following SQL questions

1. Find the trips where the distance is greater than the average distance for all trips.
2. List the name of each guide who is assigned to a trip.

     

Answer 1

Solution:

Creating tables:

create table trip (tripid int primary key,distance int,guideid int);

create table guide(guideid int primary key,guidename char(20));

alter table trip add foreign key(guideid) references guide(guideid);

Inserting rows into table:

insert into guide values (1,"Adam"),(2,"Charlie"),(3,"Roman"),(4,"Lynn"),(5,"Rajat");

insert into trip values (100,400,1),(101,300,3),(102,150,2),(103,500,5),(104,273,4),(105,146,3);

Displaying rows of the tables:

select * from trip;

select * from guide;

Query 1:

select tripid,distance from trip where distance > (select avg(distance) from trip);

Explanation:

In above query first we are retrieving average distance from trip table and checking each trip distance is greater than average or not if yes add that record to result else continue.

Query 2:

select g.guideid,tripid,guidename from guide g join trip t on g.guideid=t.guideid;

Explanation:

In above query we are joining both guide and trip tables and retrieving the guides who assigns to a trip.

Code and Output Screenshots:

Note: if you have any queries please post a comment thanks a lot..always available to help you..