Assignment Help - homework help

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 )

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)