Fundamentals of Databases 1
2023-12-21
Practice Exercises
1
Fundamentals of Databases 1
Tips for writing SQL Statements
• Structure the requirements and make a plan:
– input table(s)?
– where condition(s)?
– group by?
– is it a simple statement or do you need interim steps / interim results?
(CTE or subquery)
• Write down your plan - verbally
• Look at your test data and identify the expected result.
• If the query needs more than one step, build the query also step by step. Start with one
step (innermost query, CTE that renders a temporary result) and verify the result.
• Once the query is complete and runs, compare result with the expected result.
• If your query throws an error, do not panic. Read the error message: Is it a syntax error? If
not, the error message usually is quite clear. Interpret it and correct.
2
knirsch@[Link]
Fundamentals of Databases 1
Tips for writing SQL Statements
Children in the club: How many children are boys and how many children are girls?
Display gender, absolute numbers and percentage numbers (in regard to total number of
children).
• input table(s)?
• where condition(s)?
• group by?
• is it a simple statement or do you need interim steps / interim results?
(CTE or subquery)
• Write down your plan – verbally
• Expected result?
• Write down the query
3
knirsch@[Link]
Fundamentals of Databases 1
SQL Statements
Children in the club: How many children are boys and how many children are girls?
Display gender, absolute numbers and percentage numbers (in regard to total number of
children).
We have 3 syntacically different query options- What are they?
Write down the 3 syntactically different but semantically eqivalent query options.
4
knirsch@[Link]
Fundamentals of Databases 1
Self Join?
How do the (expected) result tables of the two following queries differ?
1. Display a list with all children in the club. The list is to show: name of child, age, gender
and parentName
2. Display a list with all children in the club. The list is to show: name of child, age, gender,
parentName, parentAge and parentGender
5
knirsch@[Link]
Fundamentals of Databases 1
6
Fundamentals of Databases 1
Self Join?
How do the (expected) result tables of the two following queries differ?
1. Each row in the result table of the first query refers to exactly one row of the input table.
→ no self-join needed
2. Each row in the result table of the second query refers to 2 different rows of the member table
→ self join needed
More general: a join is needed if an expected result row returns attribute values of more than
one input table.
Write the two statements.
7
knirsch@[Link]
Fundamentals of Databases 1
Join?
1. Display the parent name and his / her number of children in the club.
• Which attrbute do you need to count?
• Do you need a join?
Write the statement.
8
knirsch@[Link]
Fundamentals of Databases 1
Join?
2. Display the parent name, parent gender and parent age and his / her number of children in
the club.
• Which attrbute do you need to count?
• Do you need a join?
Write the statement.
Do we still need the clause "where parentName IS NOT NULL"?
9
knirsch@[Link]
Fundamentals of Databases 1
Join?
3. The result table is to show parents and all their children with child age and child name for each
child. In one result row to return:
• parent name,
• parent gender
• parent age
• name of one child,
• age of one child
• How many return rows do you get for a father with 2 children in the club?
• Do you need a join?
Write the statement.
10
knirsch@[Link]
Fundamentals of Databases 1
Join?
4. In our result table of (3) we get duplicate info about the parent if he / she has more than
one child in the club. This is ugly.
It would look much nicer, if we grouped by the parent name and collected the children names
into one attribute. This is what group_concat in combination with group by does:
Group_concat collects the values of different rows of one column into one attribute.
So, our result table is to show:
• parent name,
• parent gender
• parent age
• children names (name of first child, name of second child, ......)
Write the statement.
11
knirsch@[Link]
Fundamentals of Databases 1
Display values of groups in result table
5. Now, we want to display a list with the parents, parent age and gender, the number of
children in the club, the children names and ages. Result should look like this:
How do we get "aron:10" or "rose: 8" in one attribute?
12
knirsch@[Link]
Fundamentals of Databases 1
Concat and Group_Concat
What is the difference between functions
• concat
• group_concat
Which function do we need to execute first? Concat or group_concat?
What options do we have to get an interim result that we use as input for the final result?
Write the statement.
13
knirsch@[Link]
Fundamentals of Databases 1
Triggers: before or After
Trigger on table [Link] that logs updates on that table:
before update or after update?
Trigger on table [Link] that inserts a generated student mail with each insert of a
student:
before insert or after insert?
14
knirsch@[Link]
Fundamentals of Databases 1
Prep Work - SportsClub
• Create the lookup table targetGroup – if it does not exist yet in sportsClub:
...
targetGroup char(3) NOT NULL,
description varchar(80) DEFAULT NULL,
...
with targetGroup as PK
• Populate the table with these values:
INSERT INTO `targetgroup` (`targetGroup`, `description`) VALUES
('all', 'courses for everybody'),
('fam', 'courses for families'),
('kid', 'courses for kids'),
('men', 'Courses for men'),
('sen', 'courses for seniors '),
('wom', 'Courses for women');
• Connect tables course and targetGroup with a FK-PK. (You may need to delete the existing column
targetgroup first.) Assign the courses to targetGroups, make sure that some courses are assigned to
targetGroup 'all'.
15
knirsch@[Link]
Fundamentals of Databases 1
SportsClub
Display a list that shows the number of courses per targetGroup. The list is also to show the targetGroup(s) that
do not have courses assigned. TargetGroups that do not have courses assigned are to display a 0 for the count.
Order the result by the number of courses assigned.
Make a plan:
• Input table(s):
• Where condition:
• Group By:
• interim result?
16
knirsch@[Link]
Fundamentals of Databases 1
SportsClub - View
Write a view that displays all courses for targetGroup 'all'. The view is to show
- courseID and courseName,
- total enrollment (female and male) for each such course,
- female and male enrollment for each such course,
- percentage of male and female enrollment for each such course ". Percentage is to be rounded to two
decimal digits.
Make a plan:
• input table(s)?
• where condition?
• group by?
• let us assume the there is a course with targetGroup "all" and there are female and male enrollments.
How many lines do you get for this course in the result table?
17
knirsch@[Link]
Fundamentals of Databases 1
SportsClub - View
Write a view that displays all courses for targetGroup 'all'. The view is to show
- courseID and courseName,
- female and male enrollment for each such course,
How do you get the female and male enrollment per course (for targetGroup all)?
Write the statement.
18
knirsch@[Link]
Fundamentals of Databases 1
SportsClub - View
Write a view that displays all courses for targetGroup 'all'. The view is to show
- courseID and courseName,
- female and male enrollment for each such course,
- total enrollment (female and male) for each such course,
How do you get the total enrollment per course (for targetGroup all)?
Add to your statement.
19
knirsch@[Link]
Fundamentals of Databases 1
SportsClub - View
Write a view that displays all courses for targetGroup 'all'. The view is to show
- courseID and courseName,
- female and male enrollment for each such course,
- total enrollment (female and male) for each such course,
- percentage of male and female enrollment for each such course ". Percentage is to be rounded to two
decimal digits.
How do you get percentage of female / male enrollment?
Add to your statement.
At last, when SQL statement runs without issues, create the view.
Is the vies updateable or not?
20
knirsch@[Link]
