Solved: Were starting a new project and need to know the names of, Business Management

+44 141 628 6080
[email protected]

Were starting a new project and need to know the names of

Database for in-class exercises and practice


CREATE TABLE JOB (
JOB_CODE		CHAR(3) 	NOT NULL,
JOB_DESCRIPTION 	VARCHAR(25)	NOT NULL,
JOB_CHG_HOUR		DECIMAL(5,2)	NOT NULL,	
JOB_LAST_UPDATE	        DATE		NOT NULL,
PRIMARY KEY (JOB_CODE),
CONSTRAINT Code_Ck CHECK (JOB_CODE > 499));


INSERT INTO JOB
   VALUES ('500', 'Programmer', 35.75, '20-Nov-09');

INSERT INTO JOB
   VALUES ('501', 'Systems Analyst', 96.75, '20-Nov-09');

INSERT INTO JOB
   VALUES ('502', 'Database Designer', 125.00, '24-Mar-10');

INSERT INTO JOB
   VALUES ('503', 'Electrical Engineer', 84.50, '20-Nov-09');

INSERT INTO JOB
   VALUES ('504', 'Mechanical Engineer', 67.90, '20-Nov-09');

INSERT INTO JOB
   VALUES ('505', 'Civil Engineer', 55.78, '20-Nov-09');

INSERT INTO JOB
   VALUES ('506', 'Clerical Support', 26.87, '20-Nov-09');

INSERT INTO JOB
   VALUES ('507', 'DSS Analyst', 45.95, '20-Nov-09');

INSERT INTO JOB
   VALUES ('508', 'Applications Designer', 48.10, '24-Mar-10');

INSERT INTO JOB
   VALUES ('509', 'Bio Technician', 34.55, '20-Nov-09');

INSERT INTO JOB
   VALUES ('510', 'General Support', 18.36, '20-Nov-09');

Select *
  From Job;

===============================================================================


CREATE TABLE EMPLOYEE (
EMP_NUM		CHAR(3) 	NOT NULL UNIQUE,
EMP_LNAME 	VARCHAR(15)	NOT NULL,
EMP_FNAME	VARCHAR(15)	NOT NULL,
EMP_INITIAL	CHAR(1),		
EMP_HIREDATE	DATE		NOT NULL,
JOB_CODE	CHAR(3)		NOT NULL,
PRIMARY KEY (EMP_NUM),
FOREIGN KEY (JOB_CODE) REFERENCES JOB (JOB_CODE));


INSERT INTO EMPLOYEE
   VALUES ('101', 'News', 'John', 'G','18-Nov-00','502');

INSERT INTO EMPLOYEE
   VALUES ('102', 'Senior', 'David', 'H','12-Jul-89','501');

INSERT INTO EMPLOYEE
   VALUES ('103', 'Arbough', 'June', 'E','01-Dec-96','503');

INSERT INTO EMPLOYEE
   VALUES ('104', 'Ramoras', 'Anne', 'K','15-Nov-87','501');

INSERT INTO EMPLOYEE
   VALUES ('105', 'Johnson', 'Alice', 'K','01-Feb-93','502');

INSERT INTO EMPLOYEE
   VALUES ('106', 'Smithfield', 'William', '','22-JUN-04','500');

INSERT INTO EMPLOYEE
   VALUES ('107', 'Alonzo', 'Maria', 'D','10-Oct-93','500');

INSERT INTO EMPLOYEE
   VALUES ('108', 'Washington', 'Ralph', 'B','22-Aug-91','501');

INSERT INTO EMPLOYEE
   VALUES ('109', 'Smith', 'Larry', 'W','18-Jul-97','501');

INSERT INTO EMPLOYEE
   VALUES ('110', 'Olendo', 'Gerald', 'A','11-Dec-95','505');

INSERT INTO EMPLOYEE
   VALUES ('111', 'Walsh', 'Geoff', 'B','04-Apr-91','506');

INSERT INTO EMPLOYEE
   VALUES ('112', 'Smithson', 'Darlene', 'M','23-Oct-94','507');

INSERT INTO EMPLOYEE
   VALUES ('113', 'Joenbrood', 'Delbert', 'K','15-Nov-96','508');

INSERT INTO EMPLOYEE
   VALUES ('114', 'Jones', 'Annelise', '','20-Aug-93','508');

INSERT INTO EMPLOYEE
   VALUES ('115', 'Bawangi', 'Travis', 'B','15-Jan-92','501');

INSERT INTO EMPLOYEE
   VALUES ('116', 'Pratt', 'Gerald', 'L','05-Mar-97','510');

INSERT INTO EMPLOYEE
   VALUES ('117', 'Williamson', 'Angie', 'H','19-Jun-96','509');

INSERT INTO EMPLOYEE
   VALUES ('118', 'Frommer', 'James', 'J','04-Jan-05','510');


Select *
  From Employee;

CREATE TABLE PROJECT (
PROJ_NUM	CHAR(2) 	NOT NULL UNIQUE,
PROJ_NAME 	VARCHAR(15)	NOT NULL,
PROJ_VALUE	DECIMAL(10,2)	NOT NULL,
PROJ_BALANCE	DECIMAL(10,2)   NOT NULL,
EMP_NUM		CHAR(3) 	NOT NULL,
PRIMARY KEY (PROJ_NUM),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM));


INSERT INTO PROJECT
   VALUES ('15', 'Evergreen', 1453500.00, 1002350.00, '103');


INSERT INTO PROJECT
   VALUES ('18', 'Amber Wave', 3500500.00, 2110346.00, '108');

INSERT INTO PROJECT
   VALUES ('22', 'Rolling Tide', 805000.00, 500345.20, '102');


INSERT INTO PROJECT
   VALUES ('25', 'Starflight', 2650500.00, 2309880.00, '107');


Select *
  From Project;

CREATE TABLE ASSIGNMENT (
ASSIGN_NUM      CHAR(4)         NOT NULL UNIQUE,
ASSIGN_DATE	DATE		NOT NULL,
PROJ_NUM	CHAR(2) 	NOT NULL,
EMP_NUM		CHAR(3) 	NOT NULL,
ASSIGN_JOB	CHAR(3) 	NOT NULL,
ASSIGN_CHG_HR 	DECIMAL(5,2)	NOT NULL,
ASSIGN_HOURS	DECIMAL(2,1)	NOT NULL,		
PRIMARY KEY (ASSIGN_NUM),
FOREIGN KEY (PROJ_NUM) REFERENCES PROJECT(PROJ_NUM),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (ASSIGN_JOB) REFERENCES JOB(JOB_CODE));

INSERT INTO ASSIGNMENT
   VALUES ('1001', '22-Mar-10', '18', '103', '503', 84.50, 3.5);

INSERT INTO ASSIGNMENT
   VALUES ('1002', '22-Mar-10', '22', '117', '509', 34.55, 4.2);

INSERT INTO ASSIGNMENT
   VALUES ('1003', '22-Mar-10', '18', '117', '509', 34.55, 2.0);

INSERT INTO ASSIGNMENT
   VALUES ('1004', '22-Mar-10', '18', '103', '503', 84.50, 5.9);

INSERT INTO ASSIGNMENT
   VALUES ('1005', '22-Mar-10', '25', '108', '501', 96.75, 2.2);

INSERT INTO ASSIGNMENT
   VALUES ('1006', '22-Mar-10', '22', '104', '501', 96.75, 4.2);

INSERT INTO ASSIGNMENT
   VALUES ('1007', '22-Mar-10', '25', '113', '508', 50.75, 3.8);

INSERT INTO ASSIGNMENT
   VALUES ('1008', '22-Mar-10', '18', '103', '503', 84.50, 0.9);

INSERT INTO ASSIGNMENT
   VALUES ('1009', '23-Mar-10', '15', '115', '501', 96.75, 5.6);

INSERT INTO ASSIGNMENT
   VALUES ('1010', '23-Mar-10', '15', '117', '509', 34.55, 2.4);

INSERT INTO ASSIGNMENT
   VALUES ('1011', '23-Mar-10', '25', '105', '502', 105.00, 4.3);

INSERT INTO ASSIGNMENT
   VALUES ('1012', '23-Mar-10', '18', '108', '501', 96.75, 3.4);

INSERT INTO ASSIGNMENT
   VALUES ('1013', '23-Mar-10', '25', '115', '501', 96.75, 2.0);

INSERT INTO ASSIGNMENT
   VALUES ('1014', '23-Mar-10', '22', '104', '501', 96.75, 2.8);

INSERT INTO ASSIGNMENT
   VALUES ('1015', '23-Mar-10', '15', '103', '503', 84.50, 6.1);

INSERT INTO ASSIGNMENT
   VALUES ('1016', '23-Mar-10', '22', '105', '502', 105.00, 4.7);

INSERT INTO ASSIGNMENT
   VALUES ('1017', '23-Mar-10', '18', '117', '509', 34.55, 3.8);

INSERT INTO ASSIGNMENT
   VALUES ('1018', '23-Mar-10', '25', '117', '509', 34.55, 2.2);

INSERT INTO ASSIGNMENT
   VALUES ('1019', '24-Mar-10', '25', '104', '501', 110.50, 4.9);

INSERT INTO ASSIGNMENT
   VALUES ('1020', '24-Mar-10', '15', '101', '502', 125.00, 3.1);

INSERT INTO ASSIGNMENT
   VALUES ('1021', '24-Mar-10', '22', '108', '501', 110.50, 2.7);

INSERT INTO ASSIGNMENT
   VALUES ('1022', '24-Mar-10', '22', '115', '501', 110.50, 4.9);

INSERT INTO ASSIGNMENT
   VALUES ('1023', '24-Mar-10', '22', '105', '502', 125.00, 3.5);

INSERT INTO ASSIGNMENT
   VALUES ('1024', '24-Mar-10', '15', '103', '503', 84.50, 3.3);

INSERT INTO ASSIGNMENT
   VALUES ('1025', '24-Mar-10', '18', '117', '509', 34.55, 4.2);


Select *
  From Assignment;

1. What is Alice Johnson's job title?

2. We're starting a new project and need to know the names of our systems analysts and applications designers. Include last names labeled Employee and job titles labelled Title.

3. What is the last name labeled Manager and job description (Job) of each person managing a project? Include the project name and label it In Charge of.

4. List the last name of all employees currently working on a project. Include the name of the project labeled Working On. Do not include duplicates.

5. Name the employees (first and last name) who worked more than 5 hours on a job? Include the job number and number of hours worked (More than 5 hours). You do not need to total hours per job.

6. What is the total number of hours worked so far on the Rolling Tide project? Label as Total Hours on Rolling Tide Project.

7. How many employees are working on the Rolling Tide project? Label the output

Total Employees on Rolling Tide.

(SQL JOINS QUESTION )

View Complete Question

Solution Preview :

Prepared by a verified Expert

Business Management: Were starting a new project and need to know the names of

Reference No:- TGS02932943

Now Priced at $35 (50% Discount)

Recommended (91%)

Rated (4.3/5)

Solution Preview :

Prepared by a verified Expert

Business Management: Were starting a new project and need to know the names of

Reference No:- TGS02932943

Have a Question? (oR Write a Review)

Recent Questions Asked Business Management

Q : Describe a topic idea or concept you find so engaging that

Q : Write function that takes an array of doubles and the array

Q : How did the lives of women change over the course of the

Q : Describe key religious practices for an individual follower

Q : Were starting a new project and need to know the names of

Q : Need help with a program that displays the prime numbers in

Q : The following block of code works well accept for the fact

Q : Considering their code of ethics compare the mission of the

Q : You will be required to research the cosmetic industry as a

What should daniel do first to address this problem

Ways to embed more consultation into the regulations

What are true of strategy implementation

Determining whether that evaluation can be applied

Are my behaviors guided by my personal values

Making an investment in personal improvement

Identify the substrategies that should be adopted

Solution Preview :

Prepared by a verified Expert

Business Management: Were starting a new project and need to know the names of

Reference No:- TGS02932943

Recent Questions Asked Business Management

Q : Describe a topic idea or concept you find so engaging that

Q : Write function that takes an array of doubles and the array

Q : How did the lives of women change over the course of the

Q : Describe key religious practices for an individual follower

Q : Were starting a new project and need to know the names of

Q : Need help with a program that displays the prime numbers in

Q : The following block of code works well accept for the fact

Q : Considering their code of ethics compare the mission of the

Q : You will be required to research the cosmetic industry as a

Asked Questions