Using the relations in the appendix, write the following queries in SQL: i. Display a list of all instructors, showing each instructor’s ID and the number of sections taught. Your query should use an outer join, and should not use sub-queries. ii. Write the same query as in part a, but using a scalar subquery and not using outer join. iii. Display the list of all course sections offered in spring 2018, along with the ID and name of each instructor teaching the section. If a section has more than one instructor, that section should appear as many times in the result as it has instructors. If a section does not have any instructor, it should still appear in the result with the instructor name set to “—”. iv. Display the list of all departments, with the total number of instructors in each department, without using sub-queries. Make sure to show departments that have no instructors, and list those departments with an instructor count of zero.
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
Using the relations in the appendix, write the following queries in
SQL:
i. Display a list of all instructors, showing each instructor’s ID
and the number of sections taught. Your query should use an
outer join, and should not use sub-queries.
ii. Write the same query as in part a, but using a scalar subquery and not using outer join.
iii. Display the list of all course sections offered in spring 2018,
along with the ID and name of each instructor teaching the
section. If a section has more than one instructor, that section
should appear as many times in the result as it has instructors.
If a section does not have any instructor, it should still appear
in the result with the instructor name set to “—”.
iv. Display the list of all departments, with the total number of
instructors in each department, without using sub-queries.
Make sure to show departments that have no instructors, and
list those departments with an instructor count of zero.
Step by step
Solved in 4 steps
