QUESTION $3(30$ MARKS$)$

Suppose you have the following table named Student. The table is stored in a Database called School.

Student

Student$\_$ID

Student$\_$Name

Student$\_$Surname

Student$\_$Phone

Student$\_$Address

$1234$

Erling

Sule

$0711234567$

$3321$st Avenue

$5678$

Cristiano

Ronaldo

$0724567890$

$182$nd Street

$9101$

Abedi

Pele

$0737894560$

$8$ Fauna Drive

Write SQL code to achieve the following.

$3\mathrm{.}1\mathrm{.}1$ Create a database named school and a table named student $($the table must be created within the School Database$)(5)$

$3\mathrm{.}1\mathrm{.}2$ Insert all the records shown on the table above. $(5)$

$3\mathrm{.}1\mathrm{.}3$ Insert a new record with the Student$\_$ID $9999($use your own values for the rest of the columns$)(5)$

$3\mathrm{.}1\mathrm{.}4$ List all the records where the Student$\_$Name has a letter $$e$.(5)$

$3\mathrm{.}2$ Assume you have the following entity.

Write a SQL query to list each employee name and surname, their weekly average and the monthly average. Show a field called $$OverallAverage$$ which is the weekly average $+$ monthly average. Sort the employees alphabetically. $(10)$

$*******************************************************$END$********************************************************$ Employee

EmployeeID $\{$PK$\}$

EmployeeName

EmployeeSurname

EmployeeWeeklyAverage

EmployeeMonthlyAverage