SQL Queries for Student Grades Analysis
Uploaded by
jtijerina8208SQL Queries for Student Grades Analysis
Uploaded by
jtijerina8208Common questions
Powered by AITo determine the highest salary among instructors, you use the SQL query: SELECT MAX(salary) FROM instructor. SQL facilitates this query by providing aggregate functions like MAX(), which can easily calculate the highest value within a specified column .
You would use a SQL query that joins multiple tables to find student IDs without duplicates: SELECT DISTINCT student.ID FROM (student JOIN takes USING(ID)) JOIN teaches USING(course_id, sec_id, semester, year) WHERE instructor.name = 'Einstein'. This query involves joining the 'student', 'takes', and 'teaches' tables, reflecting a normalized data structure where teaching information is separate from student course records .
To assign grades based on scores, use a CASE statement: SELECT ID, CASE WHEN score < 40 THEN 'F' WHEN score BETWEEN 40 AND 59 THEN 'C' WHEN score BETWEEN 60 AND 79 THEN 'B' WHEN score >= 80 THEN 'A' END AS grade FROM marks. This reflects SQL's ability to process conditional logic within queries using the CASE statement to classify data based on criteria .
To delete courses never offered in any section, you would execute: DELETE FROM course WHERE course_id NOT IN (SELECT course_id FROM section). This SQL approach ensures data integrity by only removing courses that do not appear in the 'section' table, illustrating how foreign key constraints and subqueries can prevent deletion of related data that should remain intact .
To find a student's GPA, calculate the total grade-points and divide by total credits using SQL: (SELECT SUM(course.credits * grad_points.points) FROM takes JOIN course USING(course_id) JOIN grad_points ON takes.grade = grad_points.grade WHERE takes.ID = 12345) / (SELECT SUM(credits) FROM takes JOIN course USING(course_id) WHERE takes.ID = 12345). SQL simplifies this by allowing nested subqueries and mathematical operations directly within the SELECT clause .
To find the titles of all 3-credit courses offered by the 'Comp. Sci.' department, you would use the query: SELECT title FROM course WHERE depart_name = 'Comp. Sci.' AND credits = 3. This query reveals that the database has a table named 'course' with fields including 'title', 'depart_name', and 'credits', which allows for filtering specifically by department and credit value .
You calculate a student's total grade-points by linking the 'takes' table with 'grad_points' and using: SELECT SUM(course.credits * grad_points.points) FROM takes JOIN course USING(course_id) JOIN grad_points ON takes.grade = grad_points.grade WHERE takes.ID = 12345. This involves understanding the database structure, whereby you join courses, grades, and their respective points to compute an aggregate total .
To increase salaries by a percentage, use UPDATE with a calculation: UPDATE instructors SET salary = salary * 1.10 WHERE depart_name = 'Comp. Sci.'. This query highlights the use of SQL to perform arithmetic operations directly within the update command to adjust data based on defined criteria .
To find all instructors earning the maximum salary, you would use: SELECT name FROM instructors WHERE salary = (SELECT MAX(salary) FROM instructor). This illustrates the use of subqueries in SQL, where a subquery can provide a value (in this case, the maximum salary) to filter results in a main query .
To identify sections with maximum enrollment in Fall 2009, you can use: SELECT section_id FROM enrollment WHERE semester = 'Fall' AND year = 2009 AND enrollment = (SELECT MAX(enrollment) FROM enrollment WHERE semester = 'Fall' AND year = 2009). This query employs nested subqueries to first compute the maximum enrollment, and then find all sections with this enrollment, demonstrating advanced query structuring .