数据库管理代写 database management代写

database management

数据库管理代写 Question 1:  Using MySQL (Workbench or at the Command line):Write and execute the SQL code that will create the table structure for ···

 

Use the following diagram in answering Questions 1-7

 

Question 1:  (20 points)

Using MySQL (Workbench or at the Command line):

• Write and execute the SQL code that will create the table structure for tables named EMPLOYEE and PROJECT tables. Note: it’s good practice to insert the “DROP TABLEIF EXISTS” command before attempting to create a stored procedure in case it already exists.  Refer to the MySQL documentation for additional details.
• Use “insert” or “import” functions per your choice to load all 18 records appearing in the EMPLOYEE table and all 4 records appearing in the PROJECT table.
• Create a copy of the EMPLOYEE table for only the 6 columns defined in the table below, and name the new table EMP_1.
• Use “insert” (not “select from EMPLOYEE”) to add EMP_1 the data you see in the first two records of EMPLOYEE.
• Provide image(s) from MySQL showing:
  • the queries you wrote and their execution, and
  • the resulting tables (PROJECT, EMPLOYEE and EMP_1), including the table structures and contents.

The EMP_1 table will be a subset of the EMPLOYEE table.  As the table below indicates, EMP_1 has only 6 columns while EMPLOYEE has 7 columns.

The EMP_1 table structures are summarized in the following table.  Use EMP_NUM as the primary key.  Note that the JOB_CODE is the PK to JOB so be certain to enforce referential integrity.  Your code should also ensure the table does not allow null entries in EMP_LNAME and EMP_FNAME.

 

 

Question 2:  (10 points)  数据库管理代写

Using MySQL (Workbench or at the Command line):

• Write and execute a MySQL subquery to insert into EMP_1 the remaining 16 rows from the EMPLOYEE table.
• Remember, your subquery should only retrieve the columns needed for the new EMP_1 table and only the employees shown in the figure not already in EMP_1.
• Write and execute the SQL code to change the job code in EMP_1 to 501 for the person whose employee number (EMP_NUM) is 107.
• Provide image(s) from MySQL showing:
  • the subquery you wrote and its execution,
  • the 2 records originally in EMP_1,
  • table EMP_1 with the 18 records after executing the subquery, showing the original job code from employee 107, and
  • the final table EMP_1 contents after executing the query changing the job code for employee 107.

 

Question 3:  (10 points)  数据库管理代写

Using MySQL (Workbench or at the Command line):

• Write and execute the SQL code to delete the row in EMP_1 for William Smithfield, who was hired on June 22, 2004, and whose job code is 500. Hint: use logical operators to include all of the information given in this problem.
• Write and execute the SQL code to create a copy of EMP_1, including all of its data, and naming the copy EMP_2.
• Provide image(s) from MySQL showing:
  • the queries you wrote and their execution,
  • the final table EMP_1 after deleting William Smithfield, and
  • the new table EMP_2 with its data.

 

Question 4:  (10 points)  数据库管理代写

Using MySQL (Workbench or at the Command line):

• Using the EMP_2 table, write and execute the SQL code that will add the attributes EMP_PCT and PROJ_NUM to EMP_2.  The EMP_PCT is the bonus percentage to be paid to each employee.  The new attribute characteristics are:
  • EMP_PCT     DECIMAL(4,2)
  • PRO_NUM   CHAR(3)
• Using the EMP_2 table, write and execute the SQL code to change EMP_PCT value to 3.85 for the person whose employee number (EMP_NUM) is 103.
• Provide image(s) from MySQL showing:
  • the queries you wrote and their execution,
  • table EMP_2 after adding EMP_PCT, and
  • table EMP_2 after changing EMP_PCT for employee 103.

 

Question 5:  (10 points)  数据库管理代写

Using MySQL (Workbench or at the Command line):

• Using the EMP_2 table, write a single SQL command to change the EMP_PCT to 5.00 for employee numbers 101, 105, and 107.
• Using the EMP_2 table, write a single SQL command to change the EMP_PCT to 10.00 for all employees who do not currently have a value for EMP_PCT.
• Provide image(s) from MySQL showing:
  • the queries you wrote and their execution,
  • the original contents of table EMP_2,
  • the contents of table EMP_2 after changing EMP_PCT for employee numbers 101, 105, and 107, and
  • the contents of table EMP_2 after changing EMP_PCT for employees who did not originally have a value for EMP_PCT.

 

Question 6:  (10 points)  数据库管理代写

Using MySQL (Workbench or at the Command line):

• Using the EMP_2 table, write a single SQL command to add 0.15 to the EMP_PCT of the employee whose name is Maria D. Alonzo.  Use the employee’s name to determine the correct employee.
• Using the EMP_2 table, write a single SQL command to change the PROJ_NUM to 18 for all employees whose job code is 500.
• Provide image(s) from MySQL showing:
  • the queries you wrote and their execution,
  • the original contents of table EMP_2,
  • the contents of table EMP_2 after adding to the EMP_PCT of the employee whose name is Maria D. Alonzo, and
  • the contents of table EMP_2 after changing PROJ_NUM to 18 for all employees whose job code is 500.

 

Question 7:  (10 points) 数据库管理代写

Using MySQL (Workbench or at the Command line):

• Using the EMP_2 table, write a single SQL command to change the project number (PROJ_NUM) to 25 for all employees whose  JOB CODE is 502 or higher.
• Using the EMP_2 table, write a single SQL command to change the PROJ_NUM to 14 for all employees who were hired before January 1, 1994, and whose job code is at least 501.
• When you finish Question 7, the EMP_2 table will contain the data shown below.
• Provide image(s) from MySQL showing:
  • the queries you wrote and their execution,
  • the original contents of table EMP_2,
  • the contents of table EMP_2 after changing changing PRO_NUM for employees whose JOB_CODE is 502 or higher,
  • the contents of table EMP_2 after changing PROJ_NUM to 14 for all employees who were hired before January 1, 1994, and whose job code is at least 501.

 

 

Question 8:  (20 points)

Using MySQL (Workbench or at the Command line):

• Write a stored procedure using a cursor to count the number of projects in the PROJECT table you created in Question 1 whose value are (a) at or above 2,000,000 and (b) below 2,000,000. Note: it’s good practice to insert the “DROP PROCEDURE IF EXISTS” command before attempting to create a stored procedure in case it already exists.
• Use a Call to execute the stored procedure.
• Provide image(s) from MySQL showing:
  • the stored procedure you wrote including a cursor to group the respective totals,
  • the original contents of table EMP_2.

 

 

更多代写：数据库作业代写 澳大利亚会计代考 英国商科作业代写 英文论文代写 奥克兰paper代写 英国商学院代写

合作平台：随笔代写 论文代写 写手招聘 英国留学生代写