- Consider the tables given below and answer the questions that follow :
Answer the following questions:
a.Display name of student and course name in which they are enrolled.
b.Add a new record in Student table whose is enrolled in course C004
c.Display the total no students enrolled in various courses.
d.Identify Primary and Foreign key in Student table.
e.Display all those students who are enrolled in courses of more than 80 hrs of duration.
Answer:
a. SELECT name, c_name FROM student,course WHERE student.course_id = course.c_code;
b. INSERT INTO student VALUES("S05","john","KV","AIZAWL","C004");
c. SELECT c_name, count(name) AS no_of_students FROM student,course WHERE student.course_id = course.c_code GROUP BY C_name;
d. Primary key is std_code and Foreign key is course_id
3.Consider the tables FLIGHTS & FARES. Write SQL commands for the statements
i) Display flight number & number of flights from Mumbai from the table flights.
ii) Arrange the contents of the table flights in the descending order of destination.
iii) Increase the tax by 2% for the flights starting from Delhi.
iv) Display the flight number and fare to be paid for the flights from Mumbai to Kochi using the tables, Flights & Fares, where the fare to be paid =fare+fare*tax/100.
v) Display total no of source stations(eliminate duplicate) present in the table.
vi) Display the fare for the flight for MUMBAI to BANGLORE
vii)Display the records of source stations started with letter ‘B’.
viii) Display the flight no. for which fare to be paid is less than 3000.
ix) Display total no. of flights available for each Airlines
x) Add a new column Dep_Time in the table Flight.
xi) Delete the record of flight no. IC301 from the table FARE.
xii) increase the size of the column ‘source’ to 30 in the Table FLIGHT.