DROP DATABASE jobs;

CREATE DATABASE jobs;

USE jobs;

CREATE TABLE state (

stateCode CHAR(2),

description VARCHAR(30) NOT NULL,

CONSTRAINT state_pk PRIMARY KEY (stateCode)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE quarter (

qtrCode VARCHAR(5),

location CHAR(2),

minSal DECIMAL(6,2) NOT NULL,

minHrs TINYINT UNSIGNED NOT NULL,

CONSTRAINT quarter_pk PRIMARY KEY (qtrCode),

CONSTRAINT quarter_location_fk FOREIGN KEY (location) REFERENCES state (stateCode)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE employer (

companyName VARCHAR(30) NOT NULL,

division VARCHAR(30) NOT NULL,

address VARCHAR(30),

city VARCHAR(20),

stateCode CHAR(2) NOT NULL,

zipcode VARCHAR(10),

CONSTRAINT employer_pk PRIMARY KEY (companyName, division),

CONSTRAINT employer_statecode_fk FOREIGN KEY (stateCode) REFERENCES state(stateCode)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE interview (

interviewID INTEGER UNSIGNED ,

interviewDate DATE,

companyName VARCHAR(30) NOT NULL,

division VARCHAR(30) NOT NULL,

qtrCode VARCHAR(5),

salaryOffered DECIMAL(8,2),

minHrsOffered TINYINT UNSIGNED,

listing ENUM('y', 'n'),

jobDesc VARCHAR(255),

CONSTRAINT interview_pk PRIMARY KEY (interviewID),

CONSTRAINT interview_companyname_division_fk FOREIGN KEY (companyName, division) REFERENCES employer(companyName, division),

CONSTRAINT interview_qtrcode_fk FOREIGN KEY (qtrCode) REFERENCES quarter(qtrCode)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Questions:

?

1) Update all salaries offered 20%.

2) Create one new division for all employers with "software" in the employer name of "cloud operations". Salaries offered in "cloud operations" pay 20% higher than the average salaries in other divisions for the same number of min hours offered.

3) CCC Software has gone out of business and all their information must be removed from the database.

4) What is the average salary offered?

5) What is the average minimum number of hours offered?

6) What is the average salary by state ordered from highest to lowest?

7) What is the average salary by division ordered from lowest to highest?

8) Have average salaries been improving over time?

9) What company offered the highest salary?

7) Come up with your own cool query.

QUESTION 10 The null hypothesis is not rejected unless there is sufficient sample evidence to do so. True False QUESTION 11 The /-statistic is used in hypothesis testing for a population mean when the actual population standard deviation is not known. True False QUESTION 12 In a given hypothesis test, the null hypothesis can be rejected at the 0.10 and 0.05 level of significance, but cannot be rejected at the 0.01 level. The most accurate statement about the p-value for this test is Op-value = 0.01 Ob.p-value = 0.10 O= 0.01