Must know SQL/MySQL. The assignment description is linked so use that as your guide.
(1) Assignment description & the files needed p1/Assingment description.docx Lab 2 – Group Lab (100 points) Part 1: Practice on subquery Use university data (The CSV files). Please fill in the SQL and Result columns of the following table for each query. Query (20 points) SQL Result 1. Find the age of the oldest student who is either a History major or enrolled in a course taught by ‘I. Teach’. 2. Find the names of all classes that either meet in room R128 or have five or more students enrolled. 3. Find the names of all students who are enrolled in two classes that meet at the same time. 4. Find the names of faculty members for whom the combined enrollment of the courses that they teach is less than five. Practice on Stored procedure (10 point each) 1. (i) Write a stored procedure to return the number of faculty from Faculty table. (ii) Write Java code to get call this procedure from Java and display the total number of faculty from Java console. _____________________________________________________________________ 2. (i) Write a stored procedure to return information about a student, given a student id. (ii) Write Java code to ask users to enter a student id, call this procedure and display the information on Java console. _____________________________________________________________________ 3. (i) Write a stored procedure using cursor to print out information on all classes. (ii) Write Java code to call this procedure from Java (iii) Write a SQL statement to print out all information on all classes (iv) Write Java code to get this SQL statement executed. Compare the approach 1 which contains the steps (i) and (ii) and approach 2 which contains the step (iii) and (iv). Please give your comments/thought on which situations which approach should be used. All the stored procedure, screenshots (Java, mysql) should be stored/cut-pasted to a Word document. (1) Assignment description & the files needed p1/Class.csv Air Quality Engineering,TuTh 10:30-11:45,R15,11564812 American Political Parties,TuTh 2-3:15,20 AVW,619023588 Archaeology of the Incas,MWF 3-4:15,R128,248965255 Artificial Intelligence,,UP328, Aviation Accident Investigation,TuTh 1-2:50,Q3,11564812 Communication Networks,MW 9:30-10:45,20 AVW,141582651 Dairy Herd Management,TuTh 12:30-1:45,R128,356187925 Data Structures,MWF 10,R128,489456522 Database Systems,MWF 12:30-1:45,1320 DCL,142519864 Intoduction to Math,TuTh 8-9:30,R128,489221823 Introductory Latin,MWF 3-4:15,R12,248965255 Marketing Research,MW 10-11:15,1320 DCL,489221823 Multivariate Analysis,TuTh 2-3:15,R15,90873519 Operating System Design,TuTh 12-1:20,20 AVW,489456522 Optical Electronics,TuTh 12:30-1:45,R15,254099823 Orbital Mechanics,MWF 8,1320 DCL,11564812 Organic Chemistry,TuTh 12:30-1:45,R12,489221823 Patent Law,F 1-2:50,R128,90873519 Perception,MTuWTh 3,Q3,489221823 Psychology,,,619023588 Seminar in American Art,M 4,R15,489221823 Social Cognition,Tu 6:30-8:40,R15,159542516 Urban Economics,MWF 11,20 AVW,489221823 (1) Assignment description & the files needed p1/Enroll.csv 99354543,Patent Law 112348546,Database Systems 112348546,Operating System Design 115987938,Database Systems 115987938,Operating System Design 301221823,American Political Parties 301221823,Perception 301221823,Social Cognition 322654189,Database Systems 322654189,Operating System Design 348121549,Database Systems 455798411,Operating System Design 455798411,Optical Electronics 455798411,Organic Chemistry 552455318,Communication Networks 552455318,Database Systems 552455318,Operating System Design 556784565,Air Quality Engineering 567354612,Data Structures 567354612,Operating System Design 574489456,Urban Economics (1) Assignment description & the files needed p1/Faculty.csv 11564812,John Williams,68 90873519,Elizabeth Taylor,11 141582651,Mary Johnson,20 142519864,I. Teach,20 159542516,William Moore,33 242518965,James Smith,68 248965255,Barbara Wilson,12 254099823,Patricia Jones,68 287321212,Michael Miller,12 356187925,Robert Brown,12 486512566,David Anderson,20 489221823,Richard Jackson,33 489456522,Linda Davis,20 548977562,Ulysses Teach,20 619023588,Jennifer Thomas,11 (1) Assignment description & the files needed p1/Student.csv 51135593,Maria White,English,SR,21 60839453,Charles Harris,Architecture,SR,22 99354543,Susan Martin,Law,JR,20 112348546,Joseph Thompson,Computer Science,SO,19 115987938,Christopher Garcia,Computer Science,JR,20 132977562,Angela Martinez,History,SR,20 269734834,Thomas Robinson,Psychology,SO,18 280158572,Margaret Clark,Animal Science,FR,18 301221823,Juan Rodriguez,Psychology,JR,20 318548912,Dorthy Lewis,Finance,FR,18 320874981,Daniel Lee,Electrical Engineering,FR,17 322654189,Lisa Walker,Computer Science,SO,17 348121549,Paul Hall,Computer Science,JR,18 351565322,Nancy Allen,Accounting,JR,19 451519864,Mark Young,Finance,FR,18 455798411,Luis Hernandez,Electrical Engineering,FR,17 462156489,Donald King,Mechanical Engineering,SO,19 550156548,George Wright,Education,SR,21 552455318,Ana Lopez,Computer Engineering,SR,19 556784565,Kenneth Hill,Civil Engineering,SR,21 567354612,Karen Scott,Computer Engineering,FR,18 573284895,Steven Green,Kinesiology,SO,19 574489456,Betty Adams,Economics,JR,20 578875478,Edward Baker,Veterinary Medicine,SR,21