Using MySQLWorkbench, complete the following: (Please just attach the code writt

Question

Using MySQLWorkbench, complete the following: (Please just attach the code written for each one) and (each one must be answered in a single query)

1. List the contact email address of all the companies to which Nancy Drew applied.

2. List all the Jobs for which both Michael Jackson and Nancy Drew have applied.

3. Assuming that a company is charged $1000 for posting a job on the Job board, how many dollars does Intel owe?

Given:

CREATE TABLE STUDENT
(STUDENT_NUM BIGINT NOT NULL,
LNAME CHAR(20) NOT NULL,
FNAME CHAR(16) NOT NULL,
PASSWORD BIGINT NOT NULL,
ADD1 CHAR(20) NOT NULL,
ADDAPT_NUM BIGINT NOT NULL,
CITY CHAR(16) NOT NULL,
STATE CHAR(10) NOT NULL,
ZIPCODE BIGINT NOT NULL,
TELEPHONE INT NOT NULL,
EMAIL CHAR(20) NOT NULL,
DEPT CHAR(10) NOT NULL,
CREDITS BIGINT NOT NULL,
CGPA FLOAT NOT NULL,
CONSTRAINT PK_STUDENT_NUM PRIMARY KEY (STUDENT_NUM)
);

CREATE TABLE EMPLOYER
(EMPLOYER_NUM BIGINT NOT NULL,
ENAME CHAR(40) NOT NULL,
PASSWORD BIGINT NOT NULL,
ADD1 CHAR(40) NOT NULL,
ADD2 CHAR(40) NOT NULL,
CITY CHAR(20) NOT NULL,
STATE CHAR(10) NOT NULL,
ZIPCODE BIGINT NOT NULL,
WEBSITE CHAR(40) NOT NULL,
EMAIL CHAR(40) NOT NULL,
TELEPHONE INT NOT NULL,
FEE FLOAT NOT NULL,
CONSTRAINT PK_EMPLOYER_NUM PRIMARY KEY (EMPLOYER_NUM)
);

CREATE TABLE POSTJOB
(JOB_NUM BIGINT NOT NULL,
JNAME CHAR(20) NOT NULL,
EMPLOYER_NUM BIGINT NOT NULL,
DOP CHAR(40) NOT NULL,
LDOA CHAR(40) NOT NULL,
DEPT CHAR(10) NOT NULL,
PACKAGE FLOAT NOT NULL,
EWEBLINK CHAR(40) NOT NULL,
CONSTRAINT PK_JOB_NUM PRIMARY KEY(JOB_NUM),
CONSTRAINT POSTJOB_EMPLOYER_NUM_FK FOREIGN KEY (EMPLOYER_NUM) REFERENCES EMPLOYER(EMPLOYER_NUM)
);

CREATE TABLE APPLYIN
(APPLICATION_ID BIGINT NOT NULL,
STUDENT_NUM BIGINT NOT NULL,
JOB_NUM BIGINT NOT NULL,
CONSTRAINT PK_APPLICATION_ID PRIMARY KEY (APPLICATION_ID),
CONSTRAINT APPLYIN_STUDENT_NUM_FK FOREIGN KEY (STUDENT_NUM) REFERENCES STUDENT (STUDENT_NUM),
CONSTRAINT APPLYIN_JOB_NUM_FK FOREIGN KEY (JOB_NUM) REFERENCES POSTJOB (JOB_NUM)
);

CREATE TABLE OFFERBY
(APPLICATION_ID BIGINT NOT NULL,
STUDENT_NUM BIGINT NOT NULL,
EMPLOYER_NUM BIGINT NOT NULL,
CONSTRAINT PK_APPLICATION_IDSTUDENT_NUMEMPLOYER_NUM PRIMARY KEY (APPLICATION_ID,STUDENT_NUM,EMPLOYER_NUM),
CONSTRAINT OFFERBY_APPLICATION_ID_FK FOREIGN KEY (APPLICATION_ID) REFERENCES APPLYIN(APPLICATION_ID),
CONSTRAINT OFFERBY_STUDENT_NUM_FK FOREIGN KEY (STUDENT_NUM) REFERENCES STUDENT(STUDENT_NUM),
CONSTRAINT OFFERBY_EMPLOYER_NUM_FK FOREIGN KEY (EMPLOYER_NUM) REFERENCES EMPLOYER(EMPLOYER_NUM)
);

INSERT INTO STUDENT VALUES(1234,'JACKSON','MICHAEL',4321,'APPLE STREET',12,'SEATTLE','WA',84567,8948480,'michael@Jackson.','IE',10,4);
INSERT INTO STUDENT VALUES(1406,'ANGELO','MICHAEL',6041,'UNIV VILLAS',1,'SAN JOSE','CA',12345,3456789,'ma@yahoo.com','ARCH',12,3.8);
INSERT INTO STUDENT VALUES(2419,'SIMPSON','BART',9124,'KATHAY MANOR',23,'TEMPE','AZ',85281,88888888,'bart@simpson.com','IE',9,4);
INSERT INTO STUDENT VALUES(5656,'DREW','NANCY',6565,'KATHAY ST',22,'NEW YORK','NY',89012,3778071,'drew@hotmail.com','ED',16,4);
INSERT INTO STUDENT VALUES(9999,'JUSUF','HANDY',8888,'ORANGE STREET',10,'TEMPE','AZ',85281,9999999,'HANDY@utk.edu','IE',15,3);
INSERT INTO STUDENT VALUES(5555,'Bhargava','Amit',8888,'ORANGE STREET',11,'TEMPE','AZ',85281,9999998,'Amit@utk.edu','IE',15,3);

INSERT INTO EMPLOYER VALUES(1111,'INTEL CORP.',1234,'123,INTEL DRIVE','INTEL STREET','TEMPE','AZ',85281,'www.intel.com','info@intel.com',89898980,1000);
INSERT INTO EMPLOYER VALUES(2222,'MCKINSEY.',2345,'345,MCKINSEY DRIVE','MCKINSEY STREET','LOS ANGELES','CA',42324,'www.mckinsey.com','conatctus@mckinsey.com',21212121,1000);
INSERT INTO EMPLOYER VALUES(3333,'MICROSOFT Corp.',3456,'1,MICROSOFT DRIVE','MS STREET','SEATTLE','WA',12345,'www.msn.com','career@msn.com',7777777,1000);
INSERT INTO EMPLOYER VALUES(7894,'IBM CORP.',4987,'IBM DRIVE','IBM STREET','RALEIGH','NC',89456,'www.ibm.com','you@ibm.com',1234567,1000);

INSERT INTO POSTJOB VALUES(2000,'SENIOR MANAGER',7894,'12/05/2001','12/31/2001','COB',100000,'www.ibm.com');
INSERT INTO POSTJOB VALUES(4321,'SOFTWARE ENGINEER',3333,'10/31/2001','1/12/2002','IE',75000,'www.msn.com');
INSERT INTO POSTJOB VALUES(8765,'SENIOR MANAGER',2222,'10/05/2001','11/30/2001','COB',100000,'www.mckinsey.com/conatctus.html');
INSERT INTO POSTJOB VALUES(8989,'DESIGN ENGINEER',1111,'12/01/2001','12/31/2001','IE',60000,'www.intel.com');
INSERT INTO POSTJOB VALUES(9876,'APPLICATION ENGINEER',1111,'11/26/2001','12/05/2001','CS',85000,'www.intel.com/exam1.html');

INSERT INTO APPLYIN VALUES(1,1234,8765);
INSERT INTO APPLYIN VALUES(2,1234,9876);
INSERT INTO APPLYIN VALUES(3,1234,2000);
INSERT INTO APPLYIN VALUES(4,1406,4321);
INSERT INTO APPLYIN VALUES(5,1406,9876);
INSERT INTO APPLYIN VALUES(6,1406,8765);
INSERT INTO APPLYIN VALUES(7,2419,4321);
INSERT INTO APPLYIN VALUES(8,2419,8989);
INSERT INTO APPLYIN VALUES(9,2419,9876);
INSERT INTO APPLYIN VALUES(10,5555,9876);
INSERT INTO APPLYIN VALUES(11,5656,2000);
INSERT INTO APPLYIN VALUES(12,5656,9876);
INSERT INTO APPLYIN VALUES(13,5656,8765);
INSERT INTO APPLYIN VALUES(14,9999,9876);

INSERT INTO OFFERBY VALUES(1,1234,2222);
INSERT INTO OFFERBY VALUES(3,1234,7894);
INSERT INTO OFFERBY VALUES(2,1234,1111);
INSERT INTO OFFERBY VALUES(4,1406,3333);
INSERT INTO OFFERBY VALUES(5,1406,1111);
INSERT INTO OFFERBY VALUES(6,1406,2222);
INSERT INTO OFFERBY VALUES(7,2419,3333);
INSERT INTO OFFERBY VALUES(8,2419,1111);
INSERT INTO OFFERBY VALUES(12,5656,1111);
INSERT INTO OFFERBY VALUES(14,9999,1111);

MySQL Workb APPLICAT INT(20 HAR(20 STUDENT INT(20) ASSWORD B ADD ADDAPT CITY CHAR(1 STUDENT NUM BIGINT(20) IPCOD INT(20) TELEPHONE INT(1 REDITS BIGINT (20) INT(20) AR(20) DOP CHAR(40) LDOA CHAR(40 DEPT CHAR INT(20) PACKAGE INT(20) EWE ADDI CHAR(40) ADD2 CHAR(40) CITY CHAR CHAR1 ZIPCODE BIGINT SITE CHAR(40 HAR(40) PHONE INT

Explanation / Answer

1 -> SELECT DISTINCT(EMAIL) FROM employer INNER JOIN postjob ON employer.EMPLOYER_NUM = postjob.EMPLOYER_NUM WHERE JOB_NUM = ( SELECT JOB_NUM FROM applyin INNER JOIN student ON applyin.STUDENT_NUM = student.STUDENT_NUM WHERE (student.FNAME = "NANCY" AND student.LNAME = "DREW") )

2 -> SELECT DISTINCT(JNAME) FROM postjob WHERE JOB_NUM = ( SELECT JOB_NUM FROM applyin INNER JOIN student ON applyin.STUDENT_NUM = student.STUDENT_NUM WHERE ((student.FNAME = "NANCY" AND student.LNAME = "DREW") OR (student.FNAME = "MICHAEL" AND student.LNAME = "JACKSON")))

3 -> SELECT 1000*COUNT(JOB_NUM) FROM postjob INNER JOIN employer ON postjob.EMPLOYER_NUM = employer.EMPLOYER_NUM WHERE ENAME = "INTEL"

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.