Earn Higher Grades With Instant Assignment Help.Ask Question!

Database
(5/5)

This assignment will give you hands-on practice in working with DDL, DML and Constraints using MySQL workbench.For this assignment you will be using MySQL workbench tool.

INSTRUCTIONS TO CANDIDATES
ANSWER ALL QUESTIONS

This assignment will give you hands-on practice in working with DDL, DML and Constraints using MySQL workbench.

For this assignment you will be 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 questions. All Questions are equally weighted.

  • It is best practice to check your create and insert statements with describe and select statements, where

  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(10) 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)

  1. Modify project_num to auto_increment and also auto_increment starts from 10.

  1. Modify project_budget datatype from decimal (5, 2) to (10, 2).

  1. Insert following values into the Project 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

 

  1. Create a table PayRoll with the following info:

employee_num INT(10) PRIMARY KEY AUTO_INCREMENT job_id INT(10) NOT NULLjob_desc VARCHAR(40) emp_pay DECIMAL (10,2)

  1. Alter PayRoll table with the following, make sure to write each scripts separately

    1. Add constraint on emp_pay so that only value greater than 10,000 can beinserted

    2. Add constraint on job_desc so that default value set to ‘Data Analyst’.

    3. Add column pay_date (DATE) after job_desc

  1. Add Foreign Key constraint in PayRoll table with job_id column referencing to project_num column in Project

  1. 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,

job_id

pay_date

emp_pay

10

current date

12000.99

11

current date

14000.99

12

current date

16000.99

 

  1. Update emp_pay in PayRoll table for employee_num = 2 with 10% emp_pay increase

i.e. (emp_pay * 0.10).

  1. Create Project_backup table from project table you created above using bulk insert statement only for last_name 'Smith'.

  1. 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.

  1. Create Index for pay_date on PayRoll table.

  1. Delete all data from project_backup table but keep the table structure

  1. Write a DELETE script to delete a row from Project table where project_num = If there is an error, give a short explanation of what/why about error msg?

  1. Solve the question 15 above without error, i.e. write a script how you can delete

Attachments:
(5/5)

Related Questions

CSI 1420 Introduction to C Programming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two majorconstructs of the C programming language – Fu

The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

Ask This Assignment To Be Done By Our ExpertsGet A+ Grade Solution Guaranteed

expert
joyComputer science
(4/5)
12 Answers Hire Me
expert
Robert DLaw
(4.8/5)
906 Answers Hire Me
expert
Dr Samuel BarberaStatistics
(5/5)
954 Answers Hire Me
expert
Tutor For YouEconomics
(5/5)
549 Answers Hire Me