$1$a$.$ Create a unique index for inventory table on partno and name it as part$\_$inv$\_$index.

Query: create UNIQUE INDEX part$\_$inv$\_$index ON inventory $($partno$)$;

Output: INDEX PART$\_$INV$\_$INDEX created.

$1$b$.$ Create an index cust$\_$balance$\_$indx to support sorting customers by postal$\_$code, balance desc.

Query: CREATE INDEX cust$\_$balance$\_$indx ON customer $($postal$\_$code ASC, balance DESC$)$;

Output: Index CUST$\_$BALANCE$\_$INDX created.

$2$a$.$ Permit joshi to update customer table.

Query: GRANT UPDATE ON customer TO joshi;

Output: Grant succeeded.

$2$b$.$ Permit everyone to select all on your inventory table.

Query: GRANT SELECT ON inventory TO PUBLIC;

Output: Grant succeeded.

$3$a$.$ Add a character type column named customer$\_$type with $5$ character positions to customer table.

Query: ALTER TABLE customer ADD customer$\_$type CHAR$(5)$;

Output: Table CUSTOMER altered.

$3$b$.$ Now change the width of customer$\_$type to $10$ characters in customer table.

Query: ALTER TABLE customer MODIFY customer$\_$type CHAR$(10)$;

Output: Table CUSTOMER altered.

$3$c$.$ Rename Price in Item table to ListPrice.

Query: ALTER TABLE Item RENAME COLUMN Price TO ListPrice;

Output: Table ITEM altered.

$4$a$.$ Modify Enroll table $($refer to Tiny College$)$ to add a new column gradepoints. $($For the Follwing questions, refer to the ERD of Tiny College placed in Module $1).$ Tiny College ERD.pdf

Before doing the query I created the tables for Tiny College ERD: Create table Professor$($Prof$\_$Num int Primary key,Dept$\_$Code VARCHAR$(30),$Prof$\_$Speciality VARCHAR$(30),$Prof$\_$Rank int,Prof$\_$Lname VARCHAR$(30),$Prof$\_$Fname VARCHAR$(30),$Prof$\_$Intial VARCHAR$(10),$Prof$\_$Email VARCHAR$(30))$;

Create table School$($School$\_$Code int Primary key,School$\_$Name VARCHAR$(30),$Prof$\_$Num int$)$;

Create table Department$($Dept$\_$Code int Primary key,Dept$\_$Name VARCHAR$(30),$School$\_$Code int,Prof$\_$Num int$)$;

Create table Course$($Crs$\_$Code int Primary key,Dept$\_$Code int,Crs$\_$Title VARCHAR$(30),$CRS$\_$Description VARCHAR$(30),$CRS$\_$Credit int$)$;

Create table Class$\_($Class$\_$Code int Primary key,Class$\_$Section int,Class$\_$Time VARCHAR$(30),$Crs$\_$Code int,Prof$\_$Num int,Room$\_$Code int$)$;

Create table Student$($Stu$\_$Num int Primary key,Dept$\_$Code int,Stu$\_$Lname VARCHAR$(30),$Stu$\_$Fname VARCHAR$(30),$Stu$\_$Intial VARCHAR$(30),$Stu$\_$Email VARCHAR$(30),$Prof$\_$Num int$)$;

Create table Enroll$($Class$\_$Code int,Stu$\_$Num int,Enroll$\_$Date date,Enroll$\_$Grade VARCHAR$(30))$;

Create table Room$($Room$\_$Code int Primary key,Room$\_$Type VARCHAR$(30),$Bldg$\_$Code int$)$;

Create table Building$($Bldg$\_$Code int Primary key,Bldg$\_$Name VARCHAR$(30),$Bldg$\_$Location VARCHAR$(30))$;

Query: ALTER TABLE Enroll ADD gradepoints DECIMAL$(4,2)$;

Output: Table ENROLL altered.

$4$b$.$ Now use case statement to fill gradepoint value of $4$ if the grade is A$,3$ for B$,2$ for C$,1$ for D$,$ and $\mathrm{.}05$ for F$.$

Query: UPDATE Enroll

SET gradepoints $=$ CASE

WHEN enroll$\_$grade $=\text{'}$A$\text{'}$ THEN $4$

WHEN enroll$\_$grade $=\text{'}$B$\text{'}$ THEN $3$

WHEN enroll$\_$grade $=\text{'}$C$\text{'}$ THEN $2$

WHEN enroll$\_$grade $=\text{'}$D$\text{'}$ THEN $1$

WHEN enroll$\_$grade $=\text{'}$F$\text{'}$ THEN $0\mathrm{.}05$

ELSE NULL $--$ Handle other cases as needed

END;

Output: $5$ rows updated.

$5.$ Now compute Average GPA in each class. Display class$\_$code, Class$\_$GPA. Assume all courses are $3$ credit courses. A general formula for GPA would be sum$($gradepoints x Hrs$)/$sum$($Hrs$).$ Here you can just take an average of gradepoints as the credit hours are same for all courses.

Query: SELECT class$\_$code, AVG$($gradepoints$)$ AS Class$\_$GPA

FROM Enroll

GROUP BY class$\_$code;

Output: $($null$)2\mathrm{.}01$

$6.$ Create a view Course$\_$Grades showing GPA of all the students in a class with Class$\_$Code: $11794.$ Include Student$\_$Number, Student$\_$Name, GPA. Grant read ability to Joshi for this view.

Provide a select statement, via which, Joshi can see the names, and GPAs of students with GPA above $3\mathrm{.}0,$ where GPAs are arranged in a descending order.

Query Part $1$: CREATE VIEW Course$\_$Grades AS

SELECT

e$.$stu$\_$num AS Student$\_$Number,

s$.$stu$\_$fname AS First$\_$Name,

s$.$stu$\_$lname AS Last$\_$Name,

e$.$class$\_$code AS Class$\_$Code,

e$.$gradepoints AS GPA

FROM Enroll e

JOIN Student s ON e$.$stu$\_$num $=$ s$.$stu$\_$num

WHERE e$.$class$\_$code $=\text{'}11794\text{'}$;

GRANT SELECT ON Course$\_$Grades TO joshi;

Output Part $1$: View COURSE$\_$GRADES created.

Grant succeeded.

Query Part $2$: SELECT first$\_$name, last$\_$name, GPA

FROM Course$\_$Grades

WHERE GPA $>3\mathrm{.}0$

ORDER BY GPA DESC;

Output Part $2$: no data

Is this correct?