Download the starter code, which contains import_grades.sql and the grades.csv f

Question

Download the starter code, which contains import_grades.sql and the grades.csv file. Using the import_grades.sql file, create your database and table: https://usflearn.instructure.com/courses/1287154/files/68393197/download?verifier=Hs2PQFkyN655cf41eLpkwL15Lofori3wpFpWm0zf&wrap=1

Modify the LOAD DATA INFILE to correct the path to load the grades.csv file, and add/remove LOCAL if needed. That is, the only modification you may make to the import_grades.sql or the grades.csv files.

The data represents grades for assignments in a class. Look through the data, you should notice some issues right off the bat.

The Assgn column also has problems. It needs to be standardized so that each value is in the form HWd where d is the number for the homework, just like the row for Julio, HW1, 45. Write a SQL statement to fix the data in this column. You may need the CONCAT()function (Links to an external site.)Links to an external site..

The data in the first column has various forms of capitalization. Write a SQL statement that will modify the names such that they are in the form of first letter capitalized and all the other letters in lower case.

To convert this table to first normal form, write the SQL to add a column named id that is an auto-incremented non-null integer. The column must be the first column in the table.

Since it is likely that two students' may have the same name, write a SQL statement first to add a column named sid right after id in the table as a non-null integer. Then write a SQL update statement using case to set the sids of each student. Start at 1, and do so alphabetically. That is, Jesse is 1, Julio is 2, etc.

Next try the following query: select sname, count(*), sum(grade), avg(grade), sum(grade)/count(*) from grades group by sname; Notice any discrepancies with the output? Write a SQL update statement that will change the missing grades to 0.

Write a SQL query that shows the name of the assignment, the average grade on the assignment, the lowest grade on the assignment and the highest grade on the assignment sorted from highest to lowest of the highest grade.

Using a SQL select statement together with case, write a SQL query that shows each student's avg grade converted to a letter grade (using the conversion from our syllabus), sorted by letter grade.

Submit your SQL from questions 1 - 7 as grades.sql attached below

Explanation / Answer

SELECT SName, sid

    WHEN 'Jesse' THEN sid=1

    WHEN 'Julio' THEN sid=2

    WHEN 'Percy' THEN sid=3

    WHEN 'Samantha' THEN sid=4

END   

FROM grades

AS temp

SET grades.sid=temp.sid

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.