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