Overview

To succeed in data analytics, you must understand the information in tables and certain command outputs. In this assignment, you will practice identifying the parts of a database. You will also see commands used to show table information and update tables.

Directions

Download the Module Two Activity Template from the What to Submit section and retitle the file to include your last name. As you complete the activity below, you will insert your answers in the retitled template and submit the document for grading.

The tables below follow a relational data structure. The SQL statements shown are similar to commands that can be used to generate the same outcomes as those shown in the tables.

Employee Table

Employee$\_$ID First$\_$Name Last$\_$Name Department$\_$ID Classification Status Salary

$100$ John Smith $1$ Exempt Full$-$Time $90000$

$101$ Mary Jones $2$ Non$-$Exempt Part$-$Time $35000$

$102$ Mary Williams $3$ Exempt Full$-$Time $80000$

$103$ Gwen Johnson $2$ NULL Full$-$Time $40000$

$104$ Michael Jones $3$ Non$-$Exempt Full$-$Time $90000$

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 Department$\_$Name

$1$ Accounting

$2$ Human Resources

$3$ Information Systems

$4$ Marketing

SQL Statement $2$

CREATE TABLE Branches $($

Department$\_$ID SMALLINT,

Department$\_$Name VARCHAR$(50))$;

To complete this activity, visually inspect the data tables and SQL code. This activity is not a coding exercise. Rather, you will examine the tables and code above to answer the questions below. Record the answer to each question in the activity template you saved.

Specifically, you must address the following rubric criteria:

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?

What is the maximum number of decimal places that can be stored in an employee's Salary field?

What is the maximum number of decimal places that a Department$\_$ID can have?

What three rules do tables obey?

What is the result of the following query?

Select sum$($Salary$)$ from Employee where Department$\_$ID$=3$;

Identify the Employee ID or IDs that would be returned as a result of the following query. Hint: True NULL values will not return in the query.

Select $*$ from Employee where Classification $!=$ 'Exempt';

What is the result of the following query?

Select max$($Salary$)$ from Employee;