Sname	Assgn	Grade
Julio	HW1	45
Samantha	hw1	96
Jesse	HW1	78
PERCY	Homework1	87
SAMANTHA	HW2	98
percy	Hw2	97
jesse	hW2	82
Julio	HW2	\N
Jesse	Homewrk3	77
JuLIO	HW3	\N
Samantha	HW3	100
Percy	HW3	\N
create database week11; use week11; create table grades (SName varchar(10), Assgn varchar(15), Grade INT); -- modify path to CSV file for your file location LOAD DATA LOCAL INFILE 'grades.csv' INTO TABLE grades FIELDS TERMINATED BY ',' ESCAPED BY '\\' LINES TERMINATED BY ' ' IGNORE 1 LINES;

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.

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.