Compulsory Task $1$

Answer the following questions:

$$ Go to the JDoodle Online SQL IDE. This is where you can write and test your SQL code using their databases. Once you are happy with your code, paste it into a text file and save the file in your task folder as Student.txt$.$

$$ Write the SQL code to create a table called Student. The table structure is summarised in the table below.

Note that STU$\_$NUM must be set up as the primary key.

$$Attribute Name

Data Type

STU$\_$NUM

CHAR$(6)$

STU$\_$SNAME

VARCHAR$(15)$

STU$\_$FNAME

VARCHAR$(15)$

STU$\_$INITIAL

CHAR$(1)$

STU$\_$STARTDATE

DATE

COURSE$\_$CODE

CHAR$(3)$

PROJ$\_$NUM

INT$(2)$

$$ After you have created the table, write the SQL code to enter the following rows of data into the table as below:

STU$\_$N UM

STU$\_$ SNAME

STU$\_$ FNAME

STU$\_$ INITIA L

STU$\_$

STARTDATE

COURSE$\_$ CODE

PROJ$\_$ NUM

$01$

Snow

Jon

E

$2014-04-05$

$201$

$6$

$02$

Stark

Arya

C

$2017-07-12$

$305$

$11$

$03$

Lanniste r

Jamie

C

$2012-09-05$

$101$

$2$

$04$

Lanniste r

Cercei

J

$2012-09-05$

$101$

$2$

$05$

Greyjoy

Theon

I

$2015-12-09$

$402$

$14$

$06$

Tyrell

Margaer y

Y

$2017-07-12$

$305$

$10$

$07$

Baratheo n

Tommen

R

$2019-06-13$

$201$

$5$

$$ Write the SQL code that will return all records which have a COURSE$\_$CODE of $305.$

$$ Write the SQL code to change the course code to $304$ for the person whose student number is $07.$

$$ Write the SQL code to delete the row of the person named Jamie Lannister, who started on $5$ September $2012,$ whose course code is $101$ and project number is $2.$ Use logical operators to include all of the information given in this problem.

$$ Write the SQL code that will change the PROJ$\_$NUM to $14$ for all those students who started before $1$ January $2016$ and whose course code is at least $201.$

$$ Write the SQL code that will delete the Student table entirely.