Module $2$ Assignment $2-$ Basic DDL Statements

Overview

In this assignment, you will be using SQL command to create tables, populate the tables with some data, then make update and delete records in the tables.

Instructions

Connect to the compsci server in MySQL Workbench. You have access to several databases. However, you only have write privilege to one database that is named by your user name. For example, if your MySQL username is xliu, then you can only create a table, insert into, update, or delete in the xliu database. For this assignment, make sure you use that database.

Download the SQL file, then answer the following questions in that file.

Use Create Table command, create a $$department$$ tables with the following attributes.

Attribute Data Type

dept$\_$name $($as primary key$)$

varchar$(40)$

building

varchar$(25)$

budget

numeric up to $10$ digits, with $2$ decimal point

Use Create Table command, create an $$instructor$$ tables with the following attributes.

Attribute Data Type

id $($as primary key$)$

int

fname

varchar$(20)$

lname varchar$(20)$

salary numeric up to $8$ digits, with $2$ decimal point

dept$\_$name $($as foreign key referencing department$)$

varchar$(40)$

Insert $3$ records into each table $(6$ records in total$).$ You could make up your data. Try creating a new record in the instructor table first, what happens? $($Yes$,$ you must have some data in department table before you can insert into instructor table. Indeed, the order of step $1$ and $2$ cannot be reversed either $$ you must create department before instructor because of the foreign key constrain.$)$

Write a SQL command to give a $5\%$ salary raise to instructors in a specific department you pick.

Write a SQL command to give a $5\%$ salary raise to instructors in the department that has the highest budget. $($Hint: you may use sub queries for this problem.$)$

Delete all instructors of a specific department you choose $($you may have to go to MySQL Workbench$$s menu $$ Edit $$ Preferences $$ SQL Editor, scroll down to the bottom then uncheck $$Safe Updates$).$