4BHEL FSST Test (Group A)

To solve the tasks in this test, use the following ERD. Use SQL syntax that works in either SQLite or PostgreSQL.

1. Adding Data (20 points)
   

   Add the following data to your database.

   classes
   class_id	name	year
   1	2AKIFT	2023
   2	4BHEL	2023
   3	6ACELI	2023

   students
   student_id	full_name	class_id
   1	Pat Miller	1
   2	Chris Surströmming	2

   6+6 points for insert into (...) values incl. semicolon at end
   4+4 points for data (4 for classes, 4 for students)
   

   insert into classes (name, year) values
   ('2AKIFT', 2023),
   ('4BHEL', 2023),
   ('6ACELI', 2023);
   
   insert into students (full_name, class_id) values
   ('Pat Miller', 1),
   ('Chris Surströmming', 2);

2. Delete student 'Pat Miller'. (10 points)
   
   6 points for delete statement incl. semicolon
   4 point for correct condition
   

   delete from students where full_name = 'Pat Miller';

3. Change the year of the class with class_id=1 to 2022. (15 points)
   
   5 point for update table incl. semicolon
   5 point for set name...
   5 point for correct condition

   update classes set year = 2022 where class_id = 1;

4. Write a query that shows the full name and email address of all students that attend "4BHEL" in the school year 2023. Assume that you do not know the id of "4BHEL". Sort the results by the students' names. (25 points)
   5 points for select statement incl. semicolon
   5 points for using the correct columns
   5 points for correctly joining the tables
   5 points for correct where condition
   5 points for correct order by
   

   select full_name, email from students
   natural join classes
   where classes.name = '4BHEL' and classes.year = 2023
   order by students.full_name;

5. Write a query that shows all information on the first 5 students whose name starts with "F". Sort the result by the name of the class the students attend. (30 points)
   5 points for select statement incl. semicolon
   5 points for correctly joining the tables
   5 points for where using the correct column
   5 points for condition using like %
   5 points for correct order by
   5 points for correct limit

   select * from students
   natural join classes
   where students.full_name like 'F%'
   order by classes.name
   limit 5;