HW-3 DDL_DML

CSCI 3287: Design and Analysis of Data Systems Page 1
HW-3 DDL_DML
Overview
• Assignment points: 10 points or 10% toward your final grade.
• Submission: submit DDL_DML scripts in Canvas in pdf or word doc.
• This is open book exam, and any kind of resource materials are allowed.
This assignment will give you hands-on practice in working with DDL, DML and Constraints using
MySQL workbench tool. Grade points will be given to correct answers as well as clean and clear
coding, e.g., formatting, indentation, consistent upper/lower cases, etc., where applicable.
Questions:
• You must submit your SQL scripts for each question. All Questions are equally weighted.
• It is best practice to check answer with describe and/or select statements, where
applicable.
1. Create Database schema called ClassAssignment
Use ClassAssignment database for rest of the exercise.
2. Create a table called Project with the following columns:
project_num INT NOT NULL PRIMARY KEY
project_code CHAR(4)
project_title VARCHAR(45)
first_name VARCHAR(45)
last_name VARCHAR(45)
project_budget DECIMAL(5,2)
3. Modify project_num to auto_increment and also auto_increment starts from 10.
CSCI 3287: Design and Analysis of Data Systems Page 2
HW-3 DDL_DML
4. Modify project_budget datatype from decimal (5, 2) to (10, 2).
5. Insert following values into the Project table.
DO NOT insert project_num. Auto_increment should start from 10
project_code project_title first_name last_name project_budget
PC01 DIA John Smith 10000.99
PC02 CHF Tim Cook 12000.50
PC03 AST Rhonda Smith 8000.40
6. Create a table PayRoll with the following info:
employee_num INT PRIMARY KEY AUTO_INCREMENT
job_id INT NOT NULL
job_desc VARCHAR(40)
emp_pay DECIMAL (10,2)
7. Alter PayRoll table with the following, make sure to write each script separately.
i. Add constraint on emp_pay so that only value greater than 10,000 can be inserted.
ii. Add constraint on job_desc so that default value set to ‘Data Analyst’.
iii. Add column pay_date (DATE) after job_desc.
8. Add Foreign Key constraint in PayRoll table with job_id column referencing to project_num
column in Project table.
CSCI 3287: Design and Analysis of Data Systems Page 3
HW-3 DDL_DML
9. Insert following values into PayRoll table. DO NOT insert employee_num and job_desc, those
should be auto populated using auto_increment and default values, respectively.
job_id pay_date emp_pay
10 current date 12000.99
11 current date 14000.99
12 current date 16000.99
10. Update emp_pay in PayRoll table for employee_num = 2 with 10% emp_pay
increase.
11. Create Project_backup table from project table you created above using bulk insert statement
only for last_name 'Smith'.
12. Create VIEW as PayRoll_View from PayRoll table you created above. However, your VIEW
should only contain job_id, job_desc and pay_date for job_id > 10.
13. Create Index for pay_date on PayRoll table.
14. Delete all data from project_backup table but keep the table structure.
15. Write a DELETE script to delete a row from Project table where project_num = 10.
If there is an error, give a short explanation of what/why about error msg?
16. Solve the question 15 above without error, i.e. write a script how you can delete.
 The End.  