Database SQL. You can download the schemas here: https://drive.google.com/drive/

Question

Database SQL.

You can download the schemas here: https://drive.google.com/drive/folders/0B0kVn308bqFud21IMVBfQUZqUEU?usp=sharing

====================== Use Census table ==========================

1) What is the average course per week of people in the data set?

2) How many people come from each of the native countries? For each native country that appears in the data set give the name of the country and the count of people from the country?

======== Use Courses Tables ==================

3) What is the name of the student with the most total credits? Give the name and total credits. (If more than one such student, give them all.)

4) For each semester (such as “Fall”, “Spring” ) give the semester and the number of sections offered in that semester. Include past and present sections. You do not need to show a semester if it has no associated sections.

5) For each instructor, give the instructor’s ID and the number of sections the instructor has taught (or is currently teaching).

6) For each instructor, give the instructor name and the number of years in which the instructor has taught. (Hint: be careful to count only the distinct years).

7) For each course that has been taught, give the course ID, the title, and the number of times the course has been taught. Order by decreasing number of times the course has been taught.

8) Repeat the previous problem, but this time include all courses, even those that have not been taught. (Hint: you may need an outer join, and remember how ‘count’ works with null values.)

9) For each section offered in 2009, give the course_id, course title, semester, and year.

10) List the name of instructors who belong to departments in which the average salary is greater than $50.000?

11) Increase all instructors’ salaries by 6%. (Hint: the answer starts with “update instructor set”)? (Fill in the blank) Update instructor set .............

12) Create a view named ‘course_counts’ that gives, for each course_id, the number of students who have taken (or who are taking) a section of the course. Use the name ‘student_cnt’ for the attribute giving the number of students. (Fill in the blanks). Create view ........... as ..........

============== The basic of Database use courses schema ================

13) Which courses have never been taught? (Fill in the blank).

select course_id from course where course_id ............ (Select course_id from course natural join section);

Explanation / Answer

1.

Select avg(hours_per_week) from census;

2.

Select native_country,count(*) from census group by native_country ;

3.

Select name,max(tot_cred) from student;

4.

Select semester ,count(section.sec_id) from takes inner join section on takes.sec_id = section.sec_id group by semester;

5.

Select ID,count(sec_id) from teaches group by ID;

6.

Select name ,year from instructor inner join teaches on teaches.ID = instructor.ID ;

7.

Select course.course_id,course.title,count(teaches.course_id) from course inner join teaches on course.course_id = teaches.course_id order by count(teaches.course_id) desc;

8.

Select course.course_id,course.title,count(teaches.course_id) from course left outer join teaches on course.course_id = teaches.course_id order by count(teaches.course_id) desc;

9.

Select course_id,title,semester,year from teaches inner join section on teaches.sec_id = section.sec_id inner join course on course.course_id = teaches.course_id group by section.sec_id where year = 2009;

10.

Select name from instructor inner join department on department.dept_name = instructor.dept_name group by department having avg(salary) > 50000;

11.

Update instructor set salary = salary + salary *0.06;

12.

Create view course_counts as Select course_id,count(takes.ID) as 'student_cnt' from takes inner join student group by course_id;

13.

select course_id from course where course_id NOT IN (Select course_id from course natural join section);

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.