Solution-Database for in-class exercises and practice

Database for in-class exercises and practice

Assignment Help Business Management

Reference no: EM132139492

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 )

Reference no: EM132139492

Questions Cloud

Assess outcomes against plans and standards : You will be required to research the cosmetic industry as a continuation of Project 1. Do not take the research lightly as you are required to do significant.

Compare the mission of the it professional organization : Considering their code of ethics, compare the mission of the following IT professional organization: ACM, IEEE, AITP, and PMI.

Block of code works well accept for the fact : The following block of code works well accept for the fact that when it attempts to calculate the average temperature in a given amount of days

Program that displays the prime numbers : Need help with a program that displays the prime numbers in its command-line arguments, which are assumed to be integers. Example run of the program:

Database for in-class exercises and practice : 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 l

Describe key religious practices for an individual follower : Describe key religious practices for an individual follower of Chinese popular religion in his or her daily life (e.g., arranging a marriage, funerals, etc.).

How did the lives of african americans change : How did the lives of women change over the course of the Nineteenth and Twentieth Centuries?

Write function that takes an array of doubles : Write function that takes an array of doubles and the array length as parameters (compute the array length using size of, don't just put in the number 5).

What or who do you turn to when you want to learn more : Describe a topic, idea, or concept you find so engaging that it makes you lose all track of time. why does it captivate you?

User Account

All Pages

Database for in-class exercises and practice

Reference no: EM132139492

Reference no: EM132139492

Questions Cloud

Reviews

Write a Review

Business Management Questions & Answers

Caselet on michael porter’s value chain management

Mountain man brewing company

Mountain man brewing company

Analysis of the case using the doing ethics technique

Affiliations and partnerships

Innovation-friendly regulations

Effect of regional and corporate cultural issues

Structure of business plan

Identify the purposes of different types of organisations

Entrepreneur case study for analysis

Forecasting and business analysis

Educational instructional leadership

Assured A++ Grade

Academics

Major Subjects

Majors

Get In Touch

TERMS & POLICIES

HELP & SUPPORT