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. Which Student can expect the maximum salary if hired out of all the students that have applied for Jobs to various companies.

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

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);

Untitled - MySQL Workbench SQL Model* × EER Diagram × | EER Diagram! x student applyin STUDENT_NUM BIGINT(20) APPLICATION ID BIGINT(20 STUDENT-NUM BIGINT(20 LNAME CHAR(20 FNAME CHAR(16) PASSWORD BIGINT(20) ADDI CHAR(20) ADDAPT,NUM BIGINT(20) JOB-NUM BIGINT(20) Indexes offerby APPLICATION ID BIGINT(20 STUDENT NUM BIGINT(20) EMPLOYER_NUM BIGINT(20) Indexes CITY CHAR(16) STATE CHAR(10) ZIPCODE BIGINT(20) TELEPHONE INT EMAIL CHAR(20) DEPT CHAR(10) CREDITS BIGINT(20) postjob JOB NUM BIGINT(20 CGPA FLOAT JNAME CHAR(20) Indexes EMPLOYERNUM BIGIN DOP CHAR(40) LDOA CHAR(40) DEPT CHAR(10 PACKAGE FLOAT EWEBLINK CHAR(40) Indexes - employer EMPLOYER_NUM BIGINT(20) ENAME CHAR(40 PASSWORD BIGINT(20) ADDI CHAR(40 ADD2 CHAR(40) CITY CHAR(20) STATE CHAR(10) ZIPCODE BIGINT(20) WEBSITE CHAR(40) EMAIL CHAR(40) TELEPHONE INT(11) FEE FLOAT Indexes

Explanation / Answer

1.

select distinct(e.email) from employer e, postjob pj,student s, applyin a where a.student_num = s.student_num and a.job_num = pj.job_num pj.employer_num = e.employer_num and lower(s.lname) = 'drew' and lower(s.fname) = 'nancy';

2.

select distinct(s.fname) ||' ' || s.lname from student s, postjob pj, applyin a where s.student_num = a.student_num and a.job_num = pj.job_num and pj.package = (select max(package) from postjob);

3.

select jname from postjob pj, student s, applyin a where a.student_num = s.student_num and a.job_num = pj.job_num and (lower(s.lname) = 'jackson' and lower(s.fname)='michael') and a.job_num in (select distinct(a.job_num) from student s, applyin a where a.student_num = s.student_num and lower(s.lname)='drew' and lower(s.fname) = 'nancy') ;

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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