Question

IN THE PREVIOUS CHAPTER WE MADE A DATABASE USING OUR LAST NAME AS ITS NAME. QUESTION 1 STATES THIS.

PLEASE TYPE WHAT NEEDS TO BE TYPED FOR ALL STEPS. THIS USES CODIO

a. 1. Connect to the database you created and named in Module One (for example, Jetson). Type after the prompt mysql> a. use (table you named); i. Example: mysql> use Jetson; 2. Create the Employee table using the SQL statement shown here. Press Return after each line. 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)); a. 3. Create the Branches table. Fill in the missing characters or punctuation in the incomplete statement shown below to complete this action. CREATE Branches Department_ID SMALLINT, Department_Name ) 4. After creating the tables, use the correct commands to describe them. You will only be given commands to describe one of the tables and must complete the same action for the second one on your own. Validate your work with a screenshot. a. describe Employee; b. Write the correct command to describe the Branches table 5. Insert the following records into the Employee table. Each line going from left to right is a record. Each line going from top to bottom is a column. Validate your work with a screenshot. a. INSERT INTO Employee VALUES (100, John', 'Smith, 1, 'Exempt', 'Full-Time', 90000), (101, Mary Jones,2,Non-Exempt','Part-Time 35000), (102) Mary Williams/3,'ExemptFull-Time80000); b. Type the command select from Employee; and take a screenshot of it to validate this step. 6. Insert the following records into the Employee table for Gwen Johnson and Michael Jones by writing the correct SQL commands on your own. a. Gwen Johnson: Employee ID = 103, DEPARTMENT_ID = 4, Classification = NULL, Status = Full-Time, SALARY = 40000 b. Michael Jones: Employee ID = 104, DEPARTMENT_ID = 4, Classification = Non-Exempt, Status = Full-Time, SALARY = 90000 c. Insert your name into the table to verify and prove your work. (Your First and Last Name, or a nickname): Employee ID = 105, DEPARTMENT_ID = 1, Classification = Non-Exempt, Statues = Full-time, SALARY = (Choose a value between 50000 and 99000) d. Type the command select from Employee; and take a screenshot of it to validate this step. e. Insert records for a musician, athlete, or other famous character of your choice. Make sure to enter information for all of the fields listed in this table. The Department_ID must be a number between one and four. f. Write the correct command to prove that you've successfully completed this step, and validate it with a screenshot. 7. Select the fields of last name, first name, and department id from the table. Validate your work with a screenshot. a. Select First_Name, Last_Name, Employee_ID, Department_ID from Employee;

Answer 1

2)

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) );

3)

create table Branches(Department_ID SMALLINT,Department_Name varchar(30) );

4)a,b

desc Employee;

desc Branches;

mysql> desc Employee; Field | Type | Null | Key | Default | Extra | Employee_ID First_Name Last_Name Department_ID Classification STATUS Salary smallint(6) YES varchar(40) YES varchar(60) YES smallint(6) YES varchar(10) | YES varchar(10) | YES decimal(7,2) YES NULL NULL NULL NULL NULL NULL NULL 7 rows in set (0.05 sec) mysql> desc Branches; | Field | Type Null | Key | Default | Extra | Department_ID | smallint(6) | YES Department_Name | varchar(30) YES NULL NULL 2 rows in set (0.04 sec)

5)a,b
insert into Employee values(100,'John','Smith',1,'Exemp','Full-Time',90000),(101,'Mary','Jones',2,'Non-Exempt','Part-Time',35000),(102,'Mary','Williams',3,'Exempt','Full-Time',80000);

mysql> insert into Employee values (100,'John', 'Smith', 1, 'Exemp', 'Full-Time',90000),(101, 'Mary', 'Jones', 2, 'Non-Exempt', 'Part-Time', 35000),(102, 'Mary', 'Williams', 3, 'Exemp t', 'Full-Time', 80000); Query OK, 3 rows affected (0.09 sec) Records: 3 Duplicates: @ Warnings: 0 mysql> select *from Employee; | Employee_ID First Name Last_Name | Department_ID | Classification STATUS Salary 100 101 102 John Mary Mary Smith Jones Williams 1 Exemp 2 Non-Exempt 3 | Exempt Full-Time Part-Time Full-Time 90000.00 35000.00 80000.00 3 rows in set (0.00 sec) mysql>

6) a,b,c,d

insert into Employee values(103,'Gwen','Johnson',4,Null,'Full-Time',40000),(104,
'Michael','Jones',4,'Non-Exempt','Full-Time',90000),(105,'basant','dwivedi',1,'Non-Exempt',
'Full-Time',90000);

mysql> select *From Employee; | Employee_ID | First_Name | Last_Name | Department_ID | Classification | STATUS | Salary 100 | John 101 | Mary 102 | Mary 103 Gwen 104 | Michael 105 | basant Smith Jones Williams Johnson Jones dwivedi 1 Exemp 2 Non-Exempt 3 | Exempt 4 | NULL 4 Non-Exempt 1 | Non-Exempt Full-Time Part-Time Full-Time Full-Time Full-Time | Full-Time 90000.00 35000.00 80000.00 40000.00 90000.00 90000.00 6 rows in set (@.00 sec)

e)

insert into Employee values(106, 'Tom','Cruise',4,'NULL','Full-Time',99000);

select *From Employee;

mysql> select *From Employee; | Employee_ID | First_Name | Last_Name | Department_ID | Classification | STATUS | Salary 100 | John 101 | Mary 102 Mary 103 Gwen 104 | Michael 105 basant 106 | Tom Smith Jones Williams Johnson Jones dwivedi | Cruise 1 Exemp 2 Non-Exempt 3 Exempt 4 | NULL 4 Non-Exempt 1 | Non-Exempt 4 | NULL Full-Time Part-Time Full-Time Full-Time Full-Time Full-Time | Full-Time 90000.00 35000.00 80000.00 40000.00 90000.00 90000.00 99000.00 7 rows in set (@.00 sec)

7)

select first_name,last_name,employee_id,department_id from Employee;

mysql> select first_name, last_name, employee_id, department_id from Employee; first_name | last_name | employee_id | department_id | John Mary Mary Gwen Michael basant Tom Smith Jones Williams Johnson Jones dwivedi Cruise 100 101 102 103 104 105 106 1 2 3 4 4 1 4 7 rows in set (0.00 sec)

DON'T FORGET TO HIT LIKE.

THANKS BY HEART.