Contents

Instructions

This homework should be uploaded to Canvas by 11:59pm on Sunday, October 12. All submissions should be in PDF format (if you use Word, just 'PDF' in the 'Save as' dialog). Be sure to list who you've worked with, if anyone.

This assignment uses the PostgreSQL database on Joust. I will be checking everyone's PostgreSQL databases, so make sure you actually carry out these steps.

Questions

1. (10 pts.) Suppose you have the following relations:

Log into Joust and enter into the PostgreSQL shell (psql). Create a new database called hw3 and create each of the tables above. For this question, submit the SQL statement you used to create them and the output of the Postgres command to display the schema of each table.

2. (5 pts.) In the PostgreSQL shell, enter the SQL statements to enter all of the data shown in the tables above. For this question, submit those SQL statements.

3. (5pts.) In the PostgreSQL shell, enter the SQL statement to find all employees receiving a bonus. Submit the command and the results.

4. (10 pts.) In the PostgreSQL shell, enter the SQL statement to find all position titles that have at least one level with a base salary greater than $60,000. Submit the command and the results.

5. (10 pts.) Convert the following expression to SQL and enter it in the PostgresQL shell. Submit the SQL statement along with the output.

$$ {\large\pi}_{\textrm{EmployeeID}} \left( \textrm{Bonuses} \bowtie \left( {\large\sigma}_{\textrm{Base} < 60000} \left(\textrm{Positions} \bowtie \textrm{SalaryBases}\right) \right)\right)$$

6. (10 pts.) Convert the following expression to SQL and enter it in the PostgresQL shell. Submit the SQL statement along with the output.

$$ {\large\pi}_{\textrm{PositionTitle}} \left( {\large\sigma}_{\textrm{Base} < 60000} \textrm{SalarayBases} \cap {\large\sigma}_{\textrm{Base} > 54000} \textrm{SalarayBases} \right)$$ (Back to top) (Back to top)