CEIS236 Week 5 Project Deliverable
docx
keyboard_arrow_up
School
DeVry University, Chicago *
*We aren’t endorsed by this school
Course
236
Subject
English
Date
Jun 18, 2024
Type
docx
Pages
9
Uploaded by BaronNeutronButterfly254
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.
drop table employee;
create table employee(
emp_num varchar(3),
emp_lname varchar(15) not null,
emp_fname varchar(15) not null,
emp_initial varchar(1),
emp_hiredate datetime,
job_code varchar(3),
primary key(emp_num)
);
Problem 2
Having created the table structure in
Problem 1
, write the SQL code to enter the first 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. Take a screenshot of the code and data entered. insert into employee values('101','News','John','G','2000-11-8','502');
insert into employee values('102','Senior','David','H','1989-7-12','501');
insert into employee values('103','Arbough','June','E','1996-12-01','500');
insert into employee values('104','Ramoras','Anne','K','1987-11-15','501');
insert into employee values('105','Johnson','Alice','K','1993-02-01','502');
insert into employee values('106','Smithfield','William','','2004-06-22','500');
insert into employee values('107','Alonzo','Maria','D','1993-10-10','500');
insert into employee values('108','Washington','Ralph','B','1991-08-22','501');
insert into employee values('109','Smith','Larry','W','1997-07-18','501');
Problem 3
Write the SQL code to change the job code to 501 for the person whose employee number (
EMP_NUM) is 103.
update employee
set job_code='501'
where emp_num='103';
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 4
Write the SQL code to create a copy of
EMPLOYEE
, including all of its data, and naming the copy
EMP_2.
drop table emp_2;
create table emp_2 as select * from employee;
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 NUMBER(4,2)
PROJ_NUM CHAR(3)
Note: If your SQL implementation requires it, you may use DECIMAL(4,2)or NUMERIC(4,2) rather than NUMBER(4,2).
Alter table emp_2 add
emp_pct decimal(4,2),
add proj_num char(3);
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, 105, and 107 (hint: use the IN keyword).
UPDATE EMP_2
SET EMP_PCT = '5.00'
WHERE EMP_NUM IN('101','105','107');
Problem 7
Write the SQL code to join the PRODUCT and VENDOR tables using common attributes. Display the result sorted by price.
select * from product, vendor
where product.v_code = vendor.v_code
order by p_price;
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. SELECT P_CODE, P_DESCRIPT, ROUND(P_QOH * P_PRICE,2) AS total_inventory
from product
order by total_inventory desc;
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 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. SELECT CUS_LNAME, CUS_FNAME, INV_DATE, LINE_PRICE
FROM CUSTOMER, INVOICE, LINE
WHERE CUSTOMER.CUS_CODE = INVOICE.CUS_CODE
AND INVOICE.INV_NUMBER = LINE.INV_NUMBER
AND LINE_PRICE < 10;
Problem 10
Write a query to count the number of customers with a balance of more than $500.
select count(*)
from customer where cus_balance > 500;
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