CEIS236 Module 5 Project Deliverable
docx
keyboard_arrow_up
School
DeVry University, Chicago *
*We aren’t endorsed by this school
Course
236
Subject
English
Date
Apr 3, 2024
Type
docx
Pages
5
Uploaded by MinisterElementFalcon39
Course Project
DeVry University
College of Engineering and Information Sciences
Course Number: CEIS236
Course Project Deliverable: 5
Include screenshots of the code and result
Problem 1
Write the SQL code that will create only the table structure for a table named EMPLOYEE. The basic table structure is summarized in the following table. Use EMP_NUM as the primary key. Your code should also prevent null entries in EMP_LNAME and EMP_FNAME.
Take a screenshot of the code. (hint: when creating the EMP_HIREDATE field, use the DATE datatype and not the DATETIME datatype since we only want dates and not date/times.)
Problem 2
Having created the table structure in
Problem 1
, write the SQL code to enter the five rows for the table shown in the following f
igure
. Each row should be inserted individually, without using a subquery. Insert the rows in the order that they are listed in the figure. Also note that the first row needs to contain YOUR
last name and YOUR
first
name. Take a screenshot of the code and EMPLOYEE table after entering the data. (hint: run this select query to show all records: select * from employee; )
Problem 3
Write the SQL code to change the job code to 501 for the person whose employee number (
EMP_NUM
) is 103.
Problem 4
Write the SQL code to create a copy of EMPLOYEE, including all of its data, and naming the copy
EMP_2
.
Problem 5
Using the
EMP_2
table, write 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)
PROJ_NUM
CHAR(3)
Note: If your SQL implementation requires it, you may use NUMBER(4,2) or NUMERIC(4,2) rather than DECIMAL(4,2).
Problem 6
Using the
EMP_2
table, write a single SQL command to change the
EMP_PCT
value to 5.00 for the people with employee numbers 101, 102, and 105 (hint: use the IN keyword).
Problem 7
Write the SQL code to join the PRODUCT and VENDOR tables using common attributes. Display the result sorted by price.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Problem 8
Write the SQL code to display the total inventory. Your query should retrieve the product
code, description, and the calculated total inventory. This will be calculated by multiplying P_QOH by P_PRICE and rounded to two decimal places. The new field should be named TOTAL_INVENTORY. Sort by TOTAL_INVENTORY descending. Problem 9
Write the SQL code to display the customer last name, first name, invoice date, and line price from CUSTOMER, INVOICE, LINE. Join all three tables and display only those values with a line price less than 10.
Problem 10
Write a query to count the number of customers with a balance of more than $500.