<MySQL DML practice 2 Part 1 |Main Java & Gang |Install, Use jGRASP & Do Java Programming >
MySQL Database and NetBeans Practice:
Another MySQL DML Exercise 2 Part2
Summarize the average GPA of upper-division (junior or senior) students by major. Only list the major with average GPA greater than 3.1.
SELECT StdMajor, AVG(StdGPA) AS AvgGpa FROM Student WHERE StdClass IN ('JR', 'SR') GROUP BY StdMajor HAVING AVG(StdGPA) > 3.1;
List the number of upper-division students and their average GPA.
SELECT COUNT(*) AS StdCnt, AVG(StdGPA) AS AvgGPA FROM Student WHERE StdClass = 'JR' OR StdClass = 'SR';
|
Grouping on Two Columns
Summarize the minimum and maximum GPA of students by major and class.
SELECT StdMajor, StdClass, MIN(StdGPA) AS MinGPA,
MAX(StdGPA) AS MaxGPA
FROM Student
GROUP BY StdMajor, StdClass;
Combining Grouping and Joins
Summarize the number of IS course offering by course description.
SELECT CrsDesc, COUNT(*) AS OfferCount
FROM Course, Offering
WHERE Course.CourseNo = Offering.CourseNo
AND Course.CourseNo LIKE 'IS%'
GROUP BY CrsDesc;
Sorting on a Single Column
List the GPA, name, city and state of juniors. Order the result by GPA in ascending order.
SELECT StdGPA, StdFirstName, StdLastName, StdCity, StdState
FROM Student
WHERE StdClass = 'JR'
ORDER BY StdGPA;
Sorting on Two Columns with Descending Order
List the rank, salary, name and department of faculty. Order the result by ascending (alphabetic) rank and descending salary.
SELECT FacRank, FacSalary, FacFirstName, FacLastName, FacDept
FROM Faculty
ORDER BY FacRank, FacSalary DESC;
Result with Duplicates
List the city and state of faculty members.
SELECT FacCity, FacState FROM Faculty;
Eliminating Duplicates with DISTINCT
List the unique city and state combination in the Faculty table.
SELECT DISTINCT FacCity, FacState FROM Faculty;
Depict Many Parts of SELECT Statement
List the course number, offer number and average grade of students enrolled in fall 2005 IS course offerings in which more than one student is enrolled. Sort the result by course number in ascending order and average grade in descending order.
SELECT CourseNo, Enrollment.OfferNo, AVG(EnrGrade) AS AvgGrade
FROM Enrollment, Offering
WHERE CourseNo LIKE 'IS%' AND OffYear = '2005'
AND OffTerm = 'FALL'
AND Enrollment.OfferNo = Offering.OfferNo
GROUP BY CourseNo, Enrollment.OfferNo
HAVING COUNT(*) > 1
ORDER BY CourseNo, 3 DESC;
Joining Two Tables
List the student name, offering number and grade of students who have a grade >= 3.5 in a course offering.
SELECT StdFirstName, StdLastName, OfferNo, EnrGrade
FROM Student, Enrollment
WHERE EnrGrade >= 3.5
AND Student.StdSSN = Enrollment.StdSSN;
Join with Duplicates
List the names of students who have grades >= 3.5 in a course offering.
SELECT StdFirstName, StdLastName
FROM Student, Enrollment
WHERE EnrGrade >= 3.5
AND Student.StdSSN = Enrollment.StdSSN;
Join with Duplicates Removed
List the names of students (without duplicates) who have grades >= 3.5 in a course offering.
SELECT DISTINCT StdFirstName, StdLastName
FROM Student, Enrollment
WHERE EnrGrade >= 3.5
AND Student.StdSSN = Enrollment.StdSSN;
Joining Three Tables with Columns from Only Two Tables
List the student names and the offering number in which the grade is greater than 3.7 and the offering is given in fall 2005.
SELECT StdFirstName, StdLastName, Enrollment.OfferNo
FROM Student, Enrollment, Offering
WHERE Student.StdSSN = Enrollment.StdSSN
AND Offering.OfferNo = Enrollment.OfferNo
AND OffYear = '2005' AND OffTerm = 'FALL'
AND EnrGrade >= 3.7;
|
Joining Three Tables with Columns from Only Two Tables
List Leonard Vince's teaching schedule in fall 2005. For Each course, list the offering number, course number, number of units. days, location and time.
SELECT OfferNo, Offering.CourseNo, CrsUnits, OffDays, OffLocation, OffTime
FROM Faculty, Course, Offering
WHERE Faculty.FacSSN = Offering.FacSSN
AND Offering.CourseNo = Course.CourseNo
AND OffYear = '2005' AND OffTerm = 'FALL'
AND FacFirstName = 'LEONARD'
AND FacLastName = 'VINCE';
Joining Four Tables
List Bob Norbert's course schedule in spring 2006. For each course, list the offering number, course number, days, location, time and faculty name.
SELECT Offering.OfferNo, Offering.CourseNo, OffDays, OffLocation, OffTime,
FacFirstName, FacLastName
FROM Faculty, Offering, Enrollment, Student
WHERE Offering.OfferNo = Enrollment.OfferNo
AND Student.StdSSN = Enrollment.StdSSN
AND Faculty.FacSSN = Offering.FacSSN
AND OffYear = '2006' AND OffTerm = 'SPRING'
AND StdFirstName = 'BOB'
AND StdLastName = 'NORBERT';
Joining Five Tables
List Bob Norbert's course schedule in spring 2006. For each course, list the offering number, course number, days, location, time, course units and faculty name.
SELECT Offering.OfferNo, Offering.CourseNo, OffDays,
OffLocation, OffTime, CrsUnits, FacFirstName, FacLastName
FROM Faculty, Offering, Enrollment, Student, Course
WHERE Faculty.FacSSN = Offering.FacSSN
AND Offering.OfferNo = Enrollment.OfferNo
AND Student.StdSSN = Enrollment.StdSSN
AND Offering.CourseNo = Course.CourseNo
AND OffYear = '2006' AND OffTerm = 'SPRING'
AND StdFirstName = 'BOB'
AND StdLastName = 'NORBERT';
Joining Two Tables without Matching on a Primary and Foreign Key
List students who are on the faculty. Include all student columns in the result.
SELECT Student.* FROM Student, Faculty WHERE StdSSN = FacSSN;
Join Two Tables Using the Join Operator Style
Retrieve the name, city and grade of students who have is greater than or equal to 3.5 in a course offering.
SELECT StdFirstName, StdLastName, StdCity, EnrGrade
FROM Student INNER JOIN Enrollment
ON Student.StdSSN = Enrollment.StdSSN
WHERE EnrGrade >= 3.5;
Join Three Tables Using the Join Operator Style
Retrieve the name, city and grade of students who have greater than or equal 3.5 in a course offered in fall 2005.
SELECT StdFirstName, StdLastName, StdCity, EnrGrade
FROM (Student INNER JOIN Enrollment
ON Student.StdSSN = Enrollment.StdSSN)
INNER JOIN Offering
ON Offering.OfferNo = Enrollment.OfferNo
WHERE EnrGrade >= 3.5 AND OffTerm = 'FALL'
AND OffYear = '2005';
Join Four Tables Using the Join Operator Style
Retrieve the name, city and grade of students who have a greater than or equal to 3.5 in a course offered in fall 2005 taught by Leonard Vince.
SELECT StdFirstName, StdLastName, StdCity, EnrGrade
FROM ((Student INNER JOIN Enrollment ON Student.StdSSN = Enrollment.StdSSN)
INNER JOIN Offering ON Offering.OfferNo = Enrollment.OfferNo)
INNER JOIN Faculty ON Faculty.FacSSN = Offering.FacSSN
WHERE EnrGrade >= 3.5 AND OffTerm = 'FALL'
AND OffYear = '2005' AND FacFirstName = 'LEONARD'
AND FacLastName = 'VINCE';
Combine the Cross Product and Join Operator Styles
Retrieve the name, city and grade of students who have a greater than or equal to 3.5 in a course offered in fall 2005 taught by Leonard Vince. Same result as previous example should be expected.
SELECT StdFirstName, StdLastName, StdCity, EnrGrade
FROM ((Student INNER JOIN Enrollment ON Student.StdSSN = Enrollment.StdSSN)
INNER JOIN Offering ON Offering.OfferNo = Enrollment.OfferNo), Faculty
WHERE EnrGrade >= 3.5 AND OffTerm = 'FALL'
AND OffYear = '2005' AND FacFirstName = 'LEONARD'
AND FacLastName = 'VINCE'
AND Faculty.FacSSN = Offering.FacSSN;
Self Join
List faculty member who have a higher salary than their supervisor. List the Social Security number, name and salary of the faculty and supervisor.
SELECT Subr.FacSSN, Subr.FacLastName, Subr.FacSalary,
Supr.FacSSN, Supr.FacLastName, Supr.FacSalary
FROM Faculty Subr, Faculty Supr
WHERE Subr.FacSupervisor = Supr.FacSSN
AND Subr.FacSalary > Supr.FacSalary;
-----------------------------------------------------------------------------------------------------------------------
More Than One Join between Tables Using Alias Table Names
List the names of faculty members and the course number for which the faculty member teaches the same course number as his or her supervisor in 2006.
SELECT FacFirstName, FacLastName, O1.CourseNo
FROM Faculty, Offering O1, Offering O2
WHERE Faculty.FacSSN = O1.FacSSN
AND Faculty.FacSupervisor = O2.FacSSN
AND O1.OffYear = '2006' AND O2.OffYear = '2006'
family:"Courier New"; color:maroon"> AND O1.CourseNo = O2.CourseNo;
Join with Grouping on Multiple Columns
List the course number, the offering number and the number of students enrolled. Only include courses offered in 2006.
SELECT CourseNo, Enrollment.OfferNo, Count(*) AS NumStudents
FROM Offering, Enrollment
WHERE Offering.OfferNo = Enrollment.OfferNo
AND OffYear = '2006' AND OffTerm = 'SPRING'
GROUP BY Enrollment.OfferNo, CourseNo;
Join, Grouping and Group Conditions
List the course number, the offering number and the average GPA of students enrolled. Only include courses offered in fall 2005 in which the average GPA of enrolled students is greater than 3.0.
SELECT CourseNo, Enrollment.OfferNo, Avg(StdGPA) AS AvgGPA
FROM Student, Offering, Enrollment
WHERE Offering.OfferNo = Enrollment.OfferNo
AND Enrollment.StdSSN = Student.StdSSN
AND OffYear = '2005' AND OffTerm = 'FALL'
GROUP BY CourseNo, Enrollment.OfferNo
HAVING Avg(StdGPA) > 3.0;
UNION Query
Show all faculty and students. Only show the common columns in the result
SELECT FacSSN AS SSN, FacFirstName AS FirstName,
FacLastName AS LastName, FacCity AS City,
FacState AS State FROM Faculty
UNION
SELECT StdSSN AS SSN, StdFirstName AS FirstName,
StdLastName AS LastName, StdCity AS City,
StdState AS State FROM Student;
No INTERSECT (Oracle and SQL Server) AND MINUS (Oracle)/EXCEPT(SQL Server) statements or equivalent found in MySQL 5.x
Data Control Language DCL
DCL SQL statement allows you to control who has access to specific object in your database. With the DCL statements, you can grant or restrict access for example by using the GRANT or REVOKE statements. For other statements, please refer to MySQL documentation.The SQL script used in this practice can be foundhere. The first part of this exercise can be foundhere.