Question

Put all of your SQL code in a file named grades.sql and submit it below. Download the starter code, which contains import_grades.sql and the grades.csv file. Using he import_grades, sql file, create your database and table. - 0 eded. 1 T Une Modify the LOAD DATA INFILE to correct the path to load the grades.csv file, and add/remove LOCAL he 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 he bat. 1. The assgn column also has problems. It needs to be standardized so that each value is in the form HWd where dis 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 CONCATO function e. 2. 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. 3. 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. -

Answer 1

1) SELECT CONCAT('Sname'.'.', 'Assgn ', 'Grade') as homework FROM grades 2) UPDATE grades SET 'Sname 'econcat ( upper(substring(Sname, 1,1)), lower (right (Sname, length (Sname)-1))) 3) ALTER TABLE grades. ADD 'id' INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY ('id'); 4) a) ALTER TABLE grades' ADD sid' INT NOT NULL AFTER 'id', b) SET @i := ; UPDATE grades SET Sid = @i:-@i+1 ORDER BY Sname; 5) Update 'grades SET Grade=0 WHERE Grade" 6) Name of assignment : SELECT DISTINCT (Assgn) FROM grades Avg grade of assignment : SELECT AVG(Grade) AS avggrade , Assgn FROM grades GROUP BY Assgn Lowest Grade: SELECT Grade, Assgn FROM grades GROUP BY Assgn asc Lowest to highest SELECT * FROM grades ORDER BY Grade ASC