QUIZ Students and Majors For this quiz, your task is to use MySQL\'s workbench t
ID: 3733112 • Letter: Q
Question
QUIZ Students and Majors For this quiz, your task is to use MySQL's workbench to create queries for a database to keep track of Students who will have a student ID, a unique SSN, the student's name, which consists of both a first name and a last name, year (freshman, sophomore, junior, senior), and gpa. It will also keep track of Majors at the University, which will have a unique major code, unique major name, and the number of professors for that major. Each Student must have exactly one major (Undecided would be a major with its own code), and each Major may have many or no Students in it. Please download the Setup and Insert script for Students and Majors based on the following: Student studentiD INT StudentSSN VARCHAR(45) StudentFirstName VARCHAR45) Major MajorCode INT MajorName VARCHAR(45) MajorProfessorCount VARCHAR(45) StudentastName VARCHAR(45) H StudentYear VARCHAR(45) StudentGPA DOUBLE Major MajorCode INT Indexes Inde xesExplanation / Answer
//StudentQueries
1. select StudentLastName from Student;
2.select distinct StudentLastName from Student; // distinct is used to select values in the fields uniquly
3.select * from student where StudentFirstName like 'L%' OR StudentLastNmae like 'L%';
// * used to select all fields in a table and L% means it selects all names which starts with L.
4.select count(StudentYear) as Total from Student where StudentYear='junior'l
// where clause select all juniors and count() selects total count .
5. select concate(StudentFirst, ' ', StudentLastName) as fullname from Student where StudentFirstName='Lewis' or StudentFirstName = 'Roberts'l
6. select StudentFirstName, StudentLastName from Student order by StudentLastName;
// order by used to arrange in either ascending or descending order, to arrange in ascending we don t need to mention explictly, to arrange in decending order mention 'ORDER By desc' , desc means short form of descending.
7.select StudentFirstName, StudentLastName,StudentYear from (select * from Student order by StudentYear) order by StudentYear,StudentLastName;
// inner query returns the information in student table sorted using year.
8. select avg(StudentGPA) as AVERAGE GPA from Student;
// StudentMajorQueries
1. select MajorName, MajorProfessorCount from Major
2. select StudentFirstName, StudentLastName, MajorCode from Student s , Major m where s.Major_MajorCode=m.MajorCode;
4. Select StudentFirstName, StudentLastName , MajorName from Student, Major where count(MajorProfessorCount)>20;
5. select MajorName, from Major m,Student s where s.Major_MajorCode=m.MajorCode group by MajorNmae;
6.select StudentFirstName, StudentLastName, StudentGPA where All(StudentGPA<=avgz(StudentGPA));
7.select StudentFirstName, StudentLastName, StudentYear,StudentGPA, MajorName from Student s , Major m where s.Major_MajorCode = m.MajorCode order by MajorName, StudentGPA desc