UNIVERSITY of GREENWICH
Structure Physical design.
SQL basics Tutorial.
E. I. Teodorescu 1
Dr E. I. Popa
UNIVERSITY of
EXERCISE 1
CREATE A simple Student System ERD (This is an individual task)
Case study – “A students(children)’ exam system “
This is a short description:
When creating a student we need to store information such as ID for unique
identification, the name, date of birth, gender, a username, password
Many exams is taken by many students. To clarify this, a student can have
many attempts for an exam and an exam can have many attempts by students.
The system should also store information about one parent of each student. A
parent can have many students and the system should store a username and
password for them so they can access the system and view their kids grades.
TO DO: design and implement a simple database for a students’ exam system which
will hold the grades for students’ attempts for particular exams
[Link]. E.
Teodorescu
I. 2
UNIVERSITY of
Student System ERD – possible answer
Notice the primary key
[Link]. E.
Teodorescu
I. 3
UNIVERSITY of
EXERCISE 2
You are required to write a set of the SQL
statements given in the following two slides.
[Link]. E.
Teodorescu
I. 4
UNIVERSITY of
Write the SQL statements ( ) 5
Based on the given database write
the following SQL statements:
Insert a few parents using the INSERT
INTO statement.
Change a parent’s password by using the UPDATE
statement
Create a list with all students names by
using SELECT statement
Create a list of all parents
Delete a parent with a particular name
[Link]. E.
Teodorescu
I. 5
UNIVERSITY of
Write the SQL statements (2)
Based on the given database, write the
following SQL statements:
Create a list of subjects for the exams – the list should
not have duplicates
Create a list with all male students ordered
alphabetically
Create a list with all children for a particular Parent
Optional Challenge: Create a list with all children and
their grades for each attempt grouped by each subject
[Link]. E.
Teodorescu
I. 6
UNIVERSITY of
SQL Insert Example
Table Parent with the attributes: ParentId,
FirstName, Surname, Username,
Password
INSERT INTO Parent
VALUES ('Nilsen', 'Johan', 'Bakken 2', 'Stavanger’)
INSERT INTO Parent (FirstName)
VALUES ('Jakob')
Did you notice that we did not insert any
number into the ParentID field?
[Link]. E.
Teodorescu
I. 7
UNIVERSITY of
The ParentId is an autonumber
[Link]. E.
Teodorescu
I. 8
UNIVERSITY of
SQL example
UPDATE Parent
SET Password='Nis67'
WHERE Username=‘paul1’;
SELECT LastName, Surname FROM Student;
SELECT * FROM Parent;
DELETE FROM Parent
WHERE Username=‘paul1’;
[Link]. E.
Teodorescu
I. 9
UNIVERSITY of
SQL statements examples
SELECT DISTINCT Subject FROM Exam;
you can also use INNER JOIN if
SELECT * FROM Student you wish
WHERE Gender=‘Male’;
SELECT [Link]
FROM Student, Parent
WHERE [Link] = Student. fkParentID
AND [Link]=‘Paul‘;
[Link]. E.
Teodorescu
I. 1
UNIVERSITY of
The challenge SQL
SELECT [Link], [Link], [Link], [Link]
FROM Exam INNER JOIN (Student INNER JOIN Attempt ON [Link] =
[Link]) ON [Link] = [Link]
Group by [Link], [Link], [Link], [Link]
;
Or
SELECT [Link], [Link], [Link], [Link]
FROM Exam, Student, Attempt
WHERE [Link] = [Link] and [Link] =
[Link]
Group by [Link], [Link], [Link], [Link]
;
[Link]. E.
Teodorescu
I. 1
UNIVERSITY of
Optional Challenge
Implement the ERD you created for last week’s
tutorial and write 10 useful SQL statements
[Link]. E.
Teodorescu
I. 1