3) Find all sections that had the maximum enrollment (along with the enrollment), using a subquery.

Note: In this question you need to consider only the section not the courses.

 

4) As Queston 3 but find all sections that had the maximum enrollment (along with the enrollment), using a subquery for each course.

For example: if a course has 3 sections, you are required to display the course_id, sec_id which has the highest number of the students

and the number of the students in this section. You just need display the section which has maximum number of the students for each course.

 

5) As in in Q2, but now also include sections with no students taking them; the enrollment for such sections should be treated as 0.

Do this in two different ways (and create require data for testing)

Hint: Add suitable data to section table. You know that now the added section is not taken by any students. Then, write your query accrodingly

so that you must get 0 for this section. Therefore, your minumum should be 0 because of added section.

- Using aggregation on a left outer join (use the SQL natural left outer join syntax)

 

 

- Subquery

 

 

5) Find all courses which can be shown by "CS-2". This means Computer Science Course and course id is starting with 2.

However, you are not allowed to use

select * from course where dept_name = 'Comp. Sci.' and course_id like '2%'; to get result. You should find another approach.

You need to create another attribute for course table based on the department name and course ID.

For example, Accounting will be AA, Computer Science CS,.... (Single word department name should have initial letter two times, and

two or more words department names should combine only initial letter of first two words. Therefore, Accounting will be AA and

Astronomy will also be AA. Elec. Eng will be EE.... If you add the course_id to AA or EE, it shouls be AA-123...)

Hint: Use concat, left, substring functions in mysql.

 

 

6) Find instructors who have taught all the above courses

 

7) Find the all the instructor and students which have the same ID;

 

8) Insert all the instructors (except have the same ID with a student) into student table with tot_creds = 0, in the same department

 

 

9) Now delete all the newly added "students" above (note: already existing students who have tot_creds = 0 should not get deleted)

 

 

10) Some of you may have noticed that the tot_creds value for students did not match the credits from courses they have taken. Write and execute query to update tot_creds

based on the credits passed, to bring the database back to consistency. It is safer to add a cloumn like tot_cred to student table before updating the tot_cred, and

update the null values of tot_cred2 with correct values. Then, you can alter your table.

 

 

11) Update the salary of each instructor to 10000 times the number of course sections they have taught. It is safer to add a cloumn like salary to instructor

table before updating the salary, and update the null values of salary with correct values. Then, you can alter your table.

 

 

3) Find all sections that had the maximum enrollment (along with the enrollment), using a subquery. Note: In this question you need to consider only the section not the courses. 4) As Queston 3 but find all sections that had the maximum enrollment (along with the enrollment), using a subquery for each course. For example: if a course has 3 sections, you are required to display the course_id, sec_id which has the highest number of the students and the number of the students in this section. You just need display the section which has maximum number of the students for each course. 5) As in in Q2, but now also include sections with no students taking them; the enrollment for such sections should be treated as 0. Do this in two different ways (and create require data for testing) Hint: Add suitable data to section table. You know that now the added section is not taken by any students. Then, write your query accrodingly so that you must get 0 for this section. Therefore, your minumum should be 0 because of added section. - Using aggregation on a left outer join (use the SQL natural left outer join syntax) - Subquery 5) Find all courses which can be shown by "CS-2". This means Computer Science Course and course id is starting with 2. However, you are not allowed to use select * from course where dept_name = 'Comp. Sci.' and course_id like '2%'; to get result. You should find another approach. You need to create another attribute for course table based on the department name and course ID. For example, Accounting will be AA, Computer Science CS,.... (Single word department name should have initial letter two times, and two or more words department names should combine only initial letter of first two words. Therefore, Accounting will be AA and Astronomy will also be AA. Elec. Eng will be EE.... If you add the course_id to AA or EE, it shouls be AA-123...) Hint: Use concat, left, substring functions in mysql. 6) Find instructors who have taught all the above courses 7) Find the all the instructor and students which have the same ID; 8) Insert all the instructors (except have the same ID with a student) into student table with tot_creds = 0, in the same department 9) Now delete all the newly added "students" above (note: already existing students who have tot_creds = 0 should not get deleted) 10) Some of you may have noticed that the tot_creds value for students did not match the credits from courses they have taken. Write and execute query to update tot_creds based on the credits passed, to bring the database back to consistency. It is safer to add a cloumn like tot_cred to student table before updating the tot_cred, and update the null values of tot_cred2 with correct values. Then, you can alter your table. 11) Update the salary of each instructor to 10000 times the number of course sections they have taught. It is safer to add a cloumn like salary to instructor table before updating the salary, and update the null values of salary with correct values. Then, you can alter your table.