Using MySQLWorkbench, complete the following: (Please just attach the code writt
ID: 3723746 • Letter: U
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 No. Of Credit hours of all the Students Hired by Microsoft Corporation.
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 the names of all the Students along with their Student ID who have been hired by McKinsey.
4. List the particulars of the student who stay in the same state as that of McKinsey Corporation.
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);
Explanation / Answer
1. List the No. Of Credit hours of all the Students Hired by Microsoft Corporation.
SELECT S.FNAME, S.LNAME, S.CREDITS, E.ENAME FROM STUDENT S
INNER JOIN OFFERBY O ON S.STUDENT_NUM = O.STUDENT_NUM
INNER JOIN EMPLOYER E ON E.EMPLOYER_NUM = O.EMPLOYER_NUM
WHERE E.ENAME = "MICROSOFT Corp."
2. Which Student can expect the maximum salary if hired out of all the students that have applied for Jobs to various companies.
SELECT S.FNAME, S.LNAME FROM `STUDENT` S
INNER JOIN APPLYIN A ON A.STUDENT_NUM = S.STUDENT_NUM
INNER JOIN POSTJOB J ON J.JOB_NUM = A.JOB_NUM
WHERE J.PACKAGE = (SELECT MAX(PACKAGE) FROM POSTJOB)
3. List the names of all the Students along with their Student ID who have been hired by McKinsey.
SELECT S.STUDENT_NUM, S.FNAME, S.LNAME FROM `STUDENT` S
INNER JOIN OFFERBY O ON S.STUDENT_NUM = O.STUDENT_NUM
INNER JOIN EMPLOYER E ON E.EMPLOYER_NUM = O.EMPLOYER_NUM
WHERE E.ENAME = "MCKINSEY."
4. List the particulars of the student who stay in the same state as that of McKinsey Corporation.
SELECT S.* FROM STUDENT S, EMPLOYER E
WHERE S.STATE = E.STATE AND E.ENAME = "MCKINSEY."
NOTE:
1. You can customize the output with what details you want. We need to add that column name in the displayed col list.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.