Scribd
Upload
16 views1 page

Revision2 Basic Queries

The document outlines a bus reservation system and a student management system, detailing the creation of tables with specified primary and foreign keys, and the insertion of sample data. It includes various SQL queries to retrieve and manipulate data, such as listing passengers based on travel frequency, updating journey dates, and counting students based on gender. Additionally, it requires the creation of views and the display of specific student details based on their marks and demographics.

Uploaded by

Gayu Ks
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
16 views1 page

Revision2 Basic Queries

The document outlines a bus reservation system and a student management system, detailing the creation of tables with specified primary and foreign keys, and the insertion of sample data. It includes various SQL queries to retrieve and manipulate data, such as listing passengers based on travel frequency, updating journey dates, and counting students based on gender. Additionally, it requires the creation of views and the display of specific student details based on their marks and demographics.

Uploaded by

Gayu Ks
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Q1.

Consider the following relations for a bus reservation system application:


BUS (ROUTENO, SOURCE, DESTINATION)
PASSENGER (PID, PNAME, DOB, GENDER)
BOOK_TICKET (PID, ROUTENO, JOURNEY_DATE, SEAT_NO)
C1. Create the above tables by properly specifying the primary keys and foreign keys and enter at least three
tuples for each relation. (10 marks)

C2. a) List the PID of passengers who have travelled more than two times on the same route. (2 marks)
b) List the details of passengers who have travelled with seat no 12. (2 marks)
c) List the details of passengers who are senior citizen. (2 marks)
d) Update the Journey date from 20.5.22 to 24.5.2022. (2 marks)
e) Count the number of passengers travelling to Madurai from Chennai. (2 marks)

C3. a) Create a view that displays the RouteNo, source, destination and journey_date which moves from Chennai
to Pune.
b) Write a SQL statement to display all passengers, who travels to Madurai and senior citizen.

Q2. Create the following table with the mapping given below.
stu_details (reg_no, stu_name, DOB, address, city,gender,dno)
mark_details (reg_no, mark1, mark2, mark3, total)
department(dno,dname)
C1.Create the above tables by properly specifying the primary keys and foreign keys and enter at least three
tuples for each relation. (10 marks)

C2. a)Find the name of the student whose reg_no is’107’.( 2 marks)
b)Count the male and female students in the class .( 2 marks)
c)Update the mark 2 of reg.no 105, from 50 to 70. (2 marks)
d) List the students whose DOB are the same. (2 marks)
e) Display the Class of the student based on the total marks obtained. (2 marks)

C3. A) Display the dname where more number of students got total >250 .
b) List the names of the students who have scored minimum marks in mark1.
c)List the names of the students whose city is either Madurai or his total is greater than 200 .

You might also like