You must have completed Exercise 11 of Chapter 10 before beginning this exercise, and thus have used the SQL Data Definition Language to populate the tables for the three relations COMPANY, STUDENT, and INTERNSHIP. Once the three tables have been populated, write SQL Select statements to satisfy the following information requests.
Use the SQL Data Definition Language to create a relational schema that consists of the following three relations:
COMPANY (Co_name, Co_size, Co_headquarters)
STUDENT (St_name, St_major, St_status)
INTERNSHIP (In_co_name, In_st_name, In_year, In_qtr, In_location, In_stipend)
When you create a table for each relation, in addition to defining its primary key, define the all appropriate referential integrity constraints. Assume that Co_name is a character data type of size 5, Co_size is an integer data type of size 4, Co_headquarters is a character data type of size 10, St_name is a Varchar data type of size 10, St_major is a character data type of size 20, St_status is a character data type of size 2, In_co_name is a character data type of size 5, In_st_name is a Varchar data type of size 10, In_year is an integer data type of size 4, In_qtr is a character data type of size 10, In_location is a character data type of size 15, and In_stipend is an integer data type of size 4. In_stipend represents the monthly stipend associated with the internship.
Use the SQL Insert statement to populate the table with the following data:

After populating the three tables, write SQL Select statements to satisfy the following information requests:
a. Display the total monthly stipend received by each student.
b. For each internship, display the year and quarter offered, headquarters of the company offering the internship, and location of the internship. The output should be displayed in ascending order by the headquarters of the company offering the internship.
c. Display the names of all who have not participated in an internship.
d. Display the number of internships offered for each year, quarter, and internship location.
e. Use pattern matching to display the names of those students whose name begins with a capital A and ends with some letter other than a lowercase a.
f. Use a natural join to display the name, major, and status of those students with an internship in the same city where the company is headquartered.
g. Use a subquery to display the name, major, and status of those students with an internship in the same city where the company is headquartered.
h. Display the difference between the average stipend offered by Company A and the average stipend offered by all other companies excluding Company A.
i. Use a left outer join to display the total monthly stipend received by each student including those students who have not participated in an internship. What, if anything, makes you uncomfortable about the result obtained?
j. Use a union to display the total monthly stipend received by each student including those students who have not participated in an internship?

Transcribed Image Text:

Co size 1000 500 1000 400 Co headquarters Boston Chicago Boston Houston Co name St name Michelle Communications chris Andy Anna Amy St ma1O Chemistry cinance Communications Communications St status SR JR 30 3R FR In stipend 1000 1000 600 900 1000 1200 In co name In st name In location Concord Concord Concord South Bend South Bend Concord Houston in year Chris Anna Chris Amy Andy Chris Anna 2006 2006 2006 2006 Fall 2006 2005 Spring 2006 Spring Fall Fall Fall A. Spring