- 1. List the branch ID and the number of courses offered by each branch
2. List the number of courses which do not have project marks
3. List the branch ID and the number of electives offered in each branch
4. List the course Id, average total marks obtained in each course
Hint: Total marks = project marks + assignment marks + internal marks + semester marks
- 5. List the number of courses for which exam was conducted
1-Jan-2008 and 30-may-2008
6. List the policuctor ID, number of courses offered by an policuctor
7. List the course Id which is offered by more than one extends uctor
8. List the specified uctor IDs who have finished handling at least two courses during the year 2008
9. List the total strength of students staying in each hostel
10. List the total marks in each semester of a branch
Hint: Total marks = project marks + assignment marks + internal marks + semester marks
- 11. List the student ID, Student name if the student has an email id
12. List the names of students currently studying in 5th semester
13. List the names of students along with the course names for which they have registered
14. List the name of the course and the number of 'A' grades obtained in that course
15. List the name of department along with the specified uctor name who is heading that department if he has minimum two years of experience in the college.
16. List the name of course, name of specified uctor, start date, end date if that course is allotted to the specified uctor
17. List the student ID and hostel ID if they are staying in the hostel. also display the student IDs who are not staying in the hostel
18. List the names of applicants and their user ID and password. Also display
User ID and password if the applicant has not got admission
19. List the specified uctor name, branch name, number of courses taught by the specified uctor in that branch
20. List the Student name and Course name in which they have scored 'd grade
21. List the course names which have duration less than or equal to 4 days
22. List the name of the uctor and the course names they are teaching on '27-Aug-2008'
23. List the names of students who are not staying in hostel
24. List the Student name, course name and the number of days present for each course conducted
25. List the names of courses handled by the instructors who are also head of orders
26. List the names of applicants who got admission into computer science branch
27. List the names of instructors who are handling Elective Courses
Objective: To solve SQL queries using the concept of sub queries.
Problem description:
Perform the following queries using independent sub query as well as correlated sub query. The tables created during assignment 16:
1. List the name of the customer who has sent shipments to 'London '.
[Hint :-
Using Independent subquery
Select cust_name from customer where cust_id in (select cust_id from shipment where Destination = 'London ';
Using correlated subquery
Select C. cust_name from customer C where exists
(Select S. cust_id from shipment s where
S. cust_id = C. cust_id and S. Destination = 'London ');
2. List the names of the truck drivers, who have delivered shipments to 'Rome'
3. List the names of the MERs, who have sent more than one shipment.
4. List the names of the truck drivers, whose total weight of delivered shipment is more than 1000.
5. display the name and annual revenue of the customer, who has not sent any shipment.
6. display the names and the population of the city if the population is more than 100000000 and if more than one shipment has gone to the city.
7. List the names of drivers, who have delivered shipments weighing over 100 pounds Taken cumulatively.
8. List the names of the MERs and total weight of shipments, if the total weight of shipment is the maximum among the total shipments of each customer.
