$/*$ CSC $565$ Database Management Systems

Assignment $8$ Intermediate SQL

$*/$

use university;

# Write SQL statements for the following questions:

# $1.$ show all information of all instructors along with their department information $($join instructor and department tables$).$ Using a natural join.

# $2.$ Rewrite above statement, instead of natural join, using the JOIN ON syntax.

# $3.$ Rewrite above statement to show only each instructor's ID$,$ name, department, and the building of the department.

# Hint: when you use JOIN...ON syntax to join two tables, the common $($matching$)$ attribute from both tables will appear in the resulting table.

# Specifically the previous statement should have returned two dept$\_$name columns, one from instructor, the other from department.

# To list only one of them, you have to designate which dept$\_$name you want. $($either instructor.dept$\_$name or department.dept$\_$name since they always match$)$

# $4.$ Revise above statement so that your query shows only Finance instructor

# $5.$ Write a statement to list department names, the building of the department, and the total credits that the students in each department have taken

# $($hint: join department and student, you need aggregate function, your result should look like:

# dept$\_$name building total$\_$credit

# Biology Watson $120$

# Comp. Sci. Taylor $246$

# $.........$

# $6.$ Revise previous statement so that only computer science department is listed. Also instead of total credits,

# list the average credits all CS students have taken.

# Hint: When you have several keywords to use together including from, join, on$,$ where, group... you have to follow certain orders.alter

# For example, where must be after join...on$,$ and group by should appear after where.

# $7.$ Using the student and takes tables,

# write an SQL query to find the ID of each student who has never taken a course at the university.

# $($return students who do not have a record in the takes table$)$

# Do this using an outer join $($do NOT use subquery$).$

# $8.$ Create a view named "finance$\_$salary" to show the name and salary of all Finance instructor

# Note: you may not have the priviledge to actually create a view in the university database.

# Please just write down your answer without executing it$.$

# $9.$ Write a CREATE TABLE statement to make a "student" table with four attributes: ID$,$ name, dob$($date of birth$),$ and height.

# Make sure to use appropriate data types and to apply the following constraints:

# ID is the primary key, name is not null, and height is a number greater than $0.$

# Note: if you want to test your statement, you would have to use your own database $($named by your username$)$ because you do not have CREATE priviledge

# on the university database. Or if you are confident with your answer you can write down your statement without actually creating the table.

# $10.$ Based on #$9,$ suppose you have successfully created a "student" table, create an index for the field "height".