Updating Tables and SQL Identification

2

>Activity Guidelines and Rubric

Overview

Understanding the information shown in tables and the outputs of certain commands are skills necessary for success in data analytics. In this assignment, you’ll practice identifying the parts of a database. You’ll also see some of the commands that can be used to show the information and update the tables.

Directions

The tables below follow a relational data structure. Download the Module Two Activity Template from the

What to Submit

section, retitle it to include your last name, place your answers in it, and submit it for grading. You’ll only answer the

1

0 questions found below and complete a visual inspection of the data tables and SQL code. This is not a coding exercise. The SQL statements shown here are similar to commands that can be used to generate the same outcomes to those shown in the tables.

Employee Table

0

1

2

5000

Mary

3ExemptFull-Time

2

Full-Time

0000

Jones3Non-ExemptFull-Time90000

Employee_ID

First_Name

Last_Name

Department_ID

Classification

Status

Salary

10

John

Smith

Exempt

Full-Time

90000

101

Mary

Jones

Non-Exempt

Part-Time

3

102

Williams

80000

103

Gwen

Johnson

NULL

4

104

Michael

SQL Statement 1

CREATE TABLE Employee (

Employee_ID SMALLINT,

First_Name VARCHAR(40),

Last_Name VARCHAR(60),

Department_ID SMALLINT,

Classification VARCHAR(10),

Status VARCHAR(10),

Salary DECIMAL(7,2));

Branches Table

Department_ID

1

2

3

4

Department_Name

Accounting

Human Resources

Information Systems

Marketing

SQL Statement 2

CREATE TABLE Branches (

Department_ID SMALLINT,

Department_Name VARCHAR(50));

Questions

How many records are shown in the Employee table?

How many attributes are there in the Branches table?

Which attribute could be a primary key for the Employee table?

How many decimal places (maximum) can be stored in an employee’s salary field?

How many decimal places (maximum) can a Department_ID have?

What three rules do tables obey?

Is the Employee table normalized? How do you know?

What is the result of the following query?- Select sum(Salary) from Employee where Department_ID=3;

Identify the Employee ID(s) that would be returned as a result of the following query.- • Select * from Employee where Classification <> ‘Exempt’;What is the result of the following query?- Select max(Salary) from Employee;What to Submit

Submit your responses in the Module Two Activity Template. All of your answers should go in their respective locations in that document and then be submitted for grading and feedback.

Module Two Activity Rubric

)

Question 1

10

Question 2Answers question correctlyDoes not attempt criterion, or answer is incorrect10Question 3Answers question correctlyDoes not attempt criterion, or answer is incorrect10Question 4Answers question correctlyDoes not attempt criterion, or answer is incorrect10Question 5Answers question correctlyDoes not attempt criterion, or answer is incorrect10Question 6Answers question correctlyDoes not attempt criterion, or answer is incorrect10Question 7Answers question correctlyDoes not attempt criterion, or answer is incorrect10Question 8Answers question correctlyDoes not attempt criterion, or answer is incorrect10Question 9Answers question correctlyDoes not attempt criterion, or answer is incorrect10Question 10Answers question correctlyDoes not attempt criterion, or answer is incorrect10

100%

Criteria	Proficient (	100%	Not Evident (0%)	Value
									Answers question correctly										Does not attempt criterion, or answer is incorrect
Total:

DAD 220 Module Two Activity Template
Replace the bracketed text in this template with your answers and any supporting screenshots. Then
submit it for the Module Two table and SQL identification activity. You’ll need to download this
template, save it as a unique file, and then submit it for grading. Rename this document to include your
last name and remove these instructions before submitting.
Using the tables and SQL statements from the prompt for Module Two Activity, answer the following
questions. Replace the bracketed text with your answers and any supporting screenshots.
1. How many records are shown in the Employee table?
[Insert your answer here.]
2. How many attributes are there in the Branches table?
[Insert your answer here.]
3. Which attribute could be a primary key for the Employee table?
[Insert your answer here.]
4. How many decimal places (maximum) can be stored in an employee’s salary field?
[Insert your answer here.]
5. How many decimal places (maximum) can a Department_ID have?
[Insert your answer here.]
6. What three rules do tables obey?
[Insert your answer here.]
7. Is the Employee table normalized? How do you know?
[Insert your answer here.]
8. What is the result of the following query?
• Select sum(Salary) from Employee where Department_ID=3;
[Insert your answer here.]
9. Identify the Employee ID(s) that would be returned as a result of the following query.
•
Select * from Employee where Classification ‘Exempt’;
[Insert your answer here.]
10. What is the result of the following query?
•
Select max(Salary) from Employee;
[Insert your answer here.]