EXERCISE TASKS

(with lecture 4 and 5 – SQL)

FUNCTIONS AND OPERATORS

The first seven tasks deal with table dep_wth (an acronym for deposit-
withdrawal). The script for creating the table and inserting data into it is given
below. The remaining three tasks refer to the table STUDENT in the
STUDADMIN database. The eleventh task is not related to the studadmin
database.

Table dep_wth stores data on bank accounts, which are uniquely described by
their id (account_id). An account is created (opened) on a certain date
(date_creation) and related to certain deposit (deposited) and withdrawal
(withdrawn) of money amounts.

account_id date_creation deposited withdrawn

123456 01.02.2017 120,00 43,00
134567 08.05.2017 173,47 115,21
145678 07.07.2017 83,20 345,21
156789 12.09.2017 205,11 17,65
167890 28.10.2017 365,11 100,23
234567 01.02.2018 28,10 200,02
245678 12.03.2018 110,05 45,63

The following script creates table and inserts data into it (execute the statements
below one by one):
CREATE TABLE dep_wth(
account_id INTEGER
, date_creation DATE
, deposited NUMERIC(9,2)
, withdrawn NUMERIC(9,2));

INSERT INTO dep_wth VALUES (123456,'1.2.2017', 120.00, 43.00);

INSERT INTO dep_wth VALUES (134567,'8.5.2017', 173.47, 115.21);
INSERT INTO dep_wth VALUES (145678,'7.7.2017', 83.20, 345.21);
INSERT INTO dep_wth VALUES (156789,'12.9.2017', 205.11, 17.65);
INSERT INTO dep_wth VALUES (167890,'28.10.2017', 365.11, 100.23);
INSERT INTO dep_wth VALUES (234567,'1.2.2018', 28.10, 200.02);
INSERT INTO dep_wth VALUES (245678,'12.3.2018', 110.05, 45.63);
1. Write a SELECT statement that will retrieve the account id and the absolute
value of the transaction (transaction: the difference between the deposited
and the withdrawn amount) for every account in the table.

2. Write a SELECT statement that will retrieve the account id and the deposited
amount, but only if no more than 540 days have passed from the day of the
account creation until today (the query must be independent of the execution
date).

3. Write a SELECT statement that will retrieve the account id and the deposited
amount, in such way that the amount of the deposited euro is shown in a
separate column called “deposit_euro”, and the amount of the remaining
cents is put in the column called “deposit_cent”.

4. Write a SELECT statement that will retrieve the account id, month in which
the account was created, the deposited amount and the withdrawn amount.
Combine the deposited and the withdrawn amount in a single character array
separated by a dash, without redundant empty spaces at the beginning and at
the end. E.g. "120.00-43.00".

5. Write a SELECT statement that will retrieve data on all accounts created
during weekend days (Saturday, Sunday) if such accounts exist.

6. What content will be shown by executing the following SELECT statement?

SELECT DISTINCT
EXTRACT(DAY FROM date_creation) as day
,EXTRACT(MONTH FROM date_creation) as month
,EXTRACT(YEAR FROM date_creation) as year
FROM dep_wth
WHERE SUBSTRING(CAST(account_id AS CHAR(10)) FROM 1 FOR 1)='2';

7. What content will be shown by executing the following SELECT statement?

SELECT account_id, date_creation,

date_creation + 180 AS date_archiving
FROM dep_wth
WHERE EXTRACT(YEAR FROM date_creation) = 2018;

Note: The following tasks refer to the student table in the studadmin database.
8. What content will be shown by executing the following SELECT statement?

SELECT UPPER(firstname) as firstName, LOWER(lastname) as lastName

FROM student
WHERE EXTRACT(YEAR FROM dateofbirth) < 1988
LIMIT 5;

9. What content will be shown by executing the following SELECT statement?

SELECT DISTINCT LOWER(firstname) as firstname
FROM student
WHERE CHAR_LENGTH(firstname)<4;

10. What content will be shown by executing the following SELECT statement?
SELECT CURRENT_USER
, lastname
, EXTRACT(YEAR FROM dateofbirth) as yearOfBirth
FROM student
WHERE SUBSTRING(pin FROM 10 FOR 2)='99';

(Note: next task is not related with the studadmin database)

11. The attendance table contains information about exact timing of the entry
and exit of employees from the workplace. The employees are uniquely
identified by their identifier.

attendance
employeeid entry exit
10 14.03.2019 08:17:03 14.03.2019 16:03:20
223 22.02.2019 09:04:55 22.02.2019 18:15:31
584 22.02.2019 10:10:32 22.02.2019 12:12:49
584 22.02.2019 14:10:11 22.02.2019 19:14:55
97 17.03.2019 08:53:55

What content will be displayed by executing the following query:

a)
SELECT employeeid,
entry::DATE AS dateEntry,
entry::TIME AS timeEntry,
exit - entry AS difference
FROM attendance
WHERE exit IS NOT NULL;

b)
SELECT *
FROM attendance
WHERE (exit - entry) < '8 hour'::interval
c)
SELECT employeeid,
EXTRACT(DAY FROM -(entry - CURRENT_TIMESTAMP)) AS elapsed
FROM attendance
WHERE EXTRACT(MONTH FROM entry) = 2

d) Write a SQL query that will retrive entry records since 14.03.2019. and the next 22
days.

e) Write a SQL query that will retrive employee idendtifiers whom does not match the
date of entry and exit and the time of entry is recorded before 9 am.

f) Write a SQL query that will retrive records from the student table displaying date of the
birth, years of birth as interval, date of the birht in the current year and number of days to
birthday. The number of days to birthday can be a negative number, if the date of
birthday is less than the current date.
WHERE CLAUSE, BETWEEN, IN, IS NULL, LIKE, SELECT CASE

1. Write a query that will retrieve first and last names of all teachers who got
employed within the interval [15.1.2001, 15.7.2001] but are not employed any
more (dateemployedto is known, i.e. it is not NULL).

2. Write a query that will retrieve names of all organizational units (each name
only once) where there is at least one currently employed teacher
(dateemployedto is unknown, .i.e. NULL) whose name ends with "a" (e.g.
"Ivana", "Vanja").

3. Write a query that will retrieve all course names containing the character array
‘theory’, regardless of capital and small letters (e.g. 'Information theory', or
‘INFORMATION THEORY’ or ‘Theory of Informations’).

4. Write a query that will retrieve the studentids (each studentid only once) for all
students who passed a course containing the character array 'mathematics' in its
name (regardless of capital and small letters, e.g. 'Mathematics I', 'Statistics and
mathematics') and who were graded as sufficient (2) or very good (4) (use the IN
operator).

5. Write a query that will retrieve the student id, course name and status for every
student who enrolled a course containing the character array 'mathematics' in its
name (regardless of capital and small letters). The status is determined as
follows:
 if the grade is insufficient the status is 'not_passed'
 if the grade is positive (>1) the status is 'passed'
 if no grade is not recorded (NULL) the status is 'unknown'
Every student id, course name and status should be shown only once
(projection).
E.g. the result could look appear as:

studentid name status

0036342145 Mathematics I passed
0036342145 Mathematics II not_passed

6. What data will be shown by executing the following queries?

enrolledcourse
studentid courseid academicyear groupid gradedate grade
0555001157 26 2005 D-A5 (null) (null)
0555001157 27 2005 D-A5 (null) (null)
0555001115 2 2003 C-A2 02.02.2004 2
0555001211 2 2003 C-B2 02.02.2004 5
a)
SELECT enrolledcourse.*
FROM enrolledcourse
WHERE grade IN (NULL);

b)
SELECT enrolledcourse.*
FROM enrolledcourse
WHERE grade IS NULL;
c)
SELECT enrolledcourse.*
FROM enrolledcourse
WHERE grade IN (NULL, 5);

d)
SELECT enrolledcourse.*
FROM enrolledcourse
WHERE grade BETWEEN NULL AND 5;

Explain (by words; not by drawing tables) what content the following queries will
display:

e)
SELECT *
FROM student
WHERE firstname BETWEEN 'I%' AND 'J%';
f)
SELECT *
FROM student
WHERE firstname BETWEEN 'I' AND 'J';
g)
SELECT *
FROM student
WHERE firstname >= 'I' AND firstname <= 'J';

7. What content will be displayed by executing the following SELECT statements:

student
studentid firstname
100 Ivan
101 Ivana
102 Ivano

a)
SELECT *
FROM student
WHERE UPPER(firstname) LIKE 'IVAN_';
b)
SELECT *
FROM student
WHERE UPPER(firstname) LIKE 'IVAN%';

8. What content will be displayed by executing the following SELECT statements:

measurements
property amount
Pressure 1012hPa
Temperature 25 C
Humidity 80%
Wind 2-3 NW
CO2 (null)

a)
SELECT property, amount,
CASE
WHEN (amount LIKE '%#%' ESCAPE '#') THEN 'Percentage'
ELSE 'Absolute value'
END as type,
CASE
WHEN NOT (amount LIKE '%#%' ESCAPE '#') THEN 'Absolute value'
ELSE 'Percentage'
END as type2
FROM measurements;
b)
SELECT property, amount,
CASE
WHEN (amount LIKE '%8%' ESCAPE '8') THEN 'Percentage'
ELSE 'Absolute value'
END as type,
CASE
WHEN NOT (amount LIKE '%8%' ESCAPE '8') THEN 'Absolute value'
ELSE 'Percentage'
END as type2
FROM measurements;
c)
SELECT property, amount,
CASE
WHEN (amount LIKE '__%%' ESCAPE '%') THEN 'Percentage'
ELSE 'Absolute value'
END as type,
CASE
WHEN NOT (amount LIKE '__%%' ESCAPE '%') THEN 'Absolute value'
ELSE 'Percentage'
END as type2
FROM measurements;
9. Explain (by words) what content the following query returns.

SELECT orgunit.*
FROM orgunit
JOIN orgunit as suporgunit
ON orgunit.superorgunitid = suporgunit.orgunitid
WHERE suporgunit.superorgunitid IS NULL
AND LOWER (suporgunit.orgunitname) LIKE '%vele%';

10. What content will be retrieved by the following query?

orgunit
orgunitid orgunitname superorgunitid
1 FER (null)
2 ZPR 1
3 ZOEEM 1
4 ZPM 1
5 PMF (null)

SELECT orgunit.orgunitname,
CASE
WHEN(orgunit.superorgunitid IS NULL) THEN 'no superior'
ELSE 'has a superior unit, which is' || suporgunit.orgunitname
END as suporgunit
FROM orgunit, orgunit suporgunit
WHERE orgunit.superorgunitid = suporgunit.orgunitid;
UNION, NULL, OUTER JOIN, ORDER BY

All tasks in this section use the tables from the studAdmin database. A detailed
description of the studAdmin database exists on the course web site.

1. Retrieve data on students and sort them according to their date of birth.
Younger students are closer to the start of the list, older students closer to
the end.

2. Retrieve the names of all enrolled courses and grades obtained in each of
them. Sort the results according to the course name ascending, then
according to the grades descending. The display must include enrolled
courses for which no grade is recorded.

3. Retrieve data on all students for whom at least one of the following
attributes does not have a known value: date of birth, town of birth, town of
residence.

4. Write a statement for inserting a student into the student relation. The data
on the student are the following:
studentid = 0036368145
firstname = Ante
lastname = Dragan
gender = 'M'
birth date = '12.01.1995.'

5. Retrieve the first and the last name of the five teachers situated at the
start of the list of all teachers sorted according their coefficient value,
descending.

6. Write a single SQL statement that will retrieve the first name of all
students born in the town of Dubrovnik and the first name of all teachers
resident in Dubrovnik. (identical names may appear multiple times in the
result)

7. Retrieve distinct zip codes and the corresponding town names for all
towns where at least one student or one teacher have residence. Sort the
results alphabetically, according to the town name.

8. Retrieve classroom ids and capacities, and, in addition to that, the

academic year and group id for groups that had lectures in those
classrooms. Classrooms where no lectures were held at all must also be
displayed, with NULL values for the two group attributes.

9. Retrieve ids and names of organizational units that have the

organizational unit identified by 36 as their superior unit. Additionally,
 retrieve the ids of teachers currently employed in those units. If no teacher
is employed in such a unit at all, the unit must still be displayed, while the
data on the teachers will in that case set to NULL.

10. Retrieve the student’s id and all grades accomplished for the course
'Intelligent systems' (a student can be graded positively only once for each
course; however he/she may first be graded negatively) The display must
also include the students that never enrolled the course: in that case set
NULL as the grade value. Sort data according to ids, ascending, then
according to the grade, descending.

11. For each student, retrieve the first and the last name, the name of their
town of residence and the name of the county the town of residence
belongs to. All students must be shown, regardless of whether their
residence is recorded and whether the county is recorded for the particular
town of residence.

12. Retrieve the first and the last name of every student born in June.
Retrieve, additionally, for every such student the academic year in which
he/she enrolled a course that he/she passed with 4 or 5 as well as the
name of the course. If courses graded with 4 or 5 do not exist for a
particular student, display NULL for the academic year and course name.

13. For every student retrieve the first and the last name, as well as the last
name of every student with the same first name and born in the same
year. If there is no namesake born in the same year for a particular
student, display NULL value for the last name of the namesake. Sort data
alphabetically, according to the first name, then the last name.

14. Write an SQL statement that will retrieve the town name, student identifier
and student’s first and last name for all students who are born or reside in
the town whose name begins with the letter “K”. If no students reside in
that town, only retrieve the town name. Sort data according to the town
name ascending, then according to the last name and first name
ascending.
 PARALLEL JOIN, REFLEXIVE JOIN, GROUP BY, HAVING

All tasks in this section use the tables from the studAdmin database. A detailed
description of the studAdmin database exists on the course web site.

1. Write an SQL statement that will retrieve the studentid, the first and the last
name of students resident in the county of their birth.
2. Write an SQL statement that will retrieve students’ id, first and last name and
birth date as well as the id, first and last name and birth date of all students
older than him/her and sharing the same first and last name initials [initials: A.
E. for Albert Einstein]. There is no need to retrieve data on the oldest of the
students.
3. Let there be the relational schema
EXAM {courseid, studentid, dateWritten, teacheridWritten, gradeWritten,
teacheridOral, gradeOral, dateGradeOral}
that records students’ exams in particular courses. The values of the
attributes teacheridWritten, gradeWritten, teacheridOral, gradeOral, dateGradeOral
may sometimes be unknown. Attribute domains for the exam table are equal
to the domains of the corresponding attributes of the table enrolledcourse.
Write SQL statements that will enable the creation of the exam table with the
following content:

date teacherid grade teacherid grade dateGrade

courseId studentId
Written Written Written Oral Oral Oral
5 0555000048 02.02.2017 271 2 268 1 05.02.2017
5 0555000032 02.02.2017 267 2 271 1 05.02.2017
14 0555000048 05.02.2017 267 3 268 4 08.02.2017
5 0555000032 02.02.2017 267 4 271 5 05.02.2017
40 0555000688 02.02.2017 268 4 268 5 05.02.2017

4. Write an SQL statement for producing a tuple of the following content for
each record in the exam table:
studentid lastnameStudent firstnameStudent coursename
gradedWrittenFirstname gradedWrittenLastname gradeWritten
gradedOralFirstname gradedOralLastname gradeOral
5. Write an SQL statement for producing the following tuple for each exam
where the written and the oral part were graded by two different teachers,
both of them [teachers] living in the same town:
studentid lastnameStudent firstnameStudent coursename
gradeWritten gradeOral
6. Write an SQL statement that retrieves the student id, first name and last
name for students who accessed exam(s) for the course 'Databases', who
are not resident in the town of their birth, but with the town of birth and the
town of residence belonging to the same county. The result must be a
relation.
7. Insert the following tuple into exam:
t1 =  4, '0555000053', '02.02.2017', NULL, NULL, NULL, NULL, NULL
Execute again the statement that is the solution of Task 4. Why does the
tuple t1 not appear in the result? Fix the statement in Task 4 so that the
result also includes t1 and similar tuples. Drop the exam relation.
8. Write an SQL statement that retrieves the names of organizational units
that are immediately subordinated to Faculty of Electricl Engineering and
Computing [Fakultet elektrotehnike I računarstva].
9. Write an SQL statement that retrieves the first and the last name of a
teacher, the name of the organizational unit where the teacher is
employed and the name of the organizational unit that is immediately
superior to that unit. The selection should include all teachers who are still
employed on the very day of query execution.
10. Let there be the relational schema
PERSON {idPerson, firstname, lastname, idPersonMarriedTo}
that records persons and their spouses. The value of attribute
idPersonMarriedTo may not always be known. Attributes domains for
PERSON are equal to the domains of the corresponding attributes in the
teacher relation.
Write SQL statements that will enable the creation of table person with
the following content in the studAdmin database:
idPerson firstname Lastname idPersonMarriedTo
238 Ivana Martinec 249
249 Andrija Salopek-Rabatić 238
252 Jure Damiani-Einwalter 263
256 Milica Balen
257 Tatjana Kancir 261
261 Salih Jalšovec 257
262 Antun Pajnić
263 Vlasta Zanchi 252
Write an SQL statement that retrieves the first and last name of persons
and their spouses. Unmarried persons should not appear in the result.
Drop table person.

11. Write an SQL statement that retrieves names of the towns where at least
two students are resident.
12. Write an SQL statement that retrieves the academic year and the name of
the course for which there exist exactly ten groups in that particular
academic year.
13. Write an SQL statement that retrieves the name of organizational units
and the number of the immediately subordinated organizations. For
organizational units that do not have a subordinate unit, display zero as
the number of subordinates.
14. Write an SQL statement that will retrieve the town name, and number of
studnts who are born or reside in a town whose name begins with the
letter “B” or “D”. If there is a town where none of the students were born or
reside display zero for the number of studnets. Sort data according to the
number of students descending
SOLUTIONS
Functions and operators

1.
SELECT account_id, ABS(deposited-withdrawn) as trans FROM dep_wth;

account_id trans
123456 77.00
134567 58.26
145678 262.01
156789 187.46
167890 264.88
234567 171.92
245678 64.42

2.
SELECT account_id, deposited FROM dep_wth
WHERE CURRENT_DATE-date_creation<=540;

Account_id deposited
167890 365.11
234567 28.10
245678 110.05

Note: tuples displayed as the query result depend on the date of query execution
(The result displayed for 27.3.2019)

3.
SELECT account_id, ROUND(deposited) AS deposit_euro
, deposited*100%100 AS deposit_cent
FROM dep_wth;

account_id deposit_euro deposit_cent

123456 120 0
134567 173 47
145678 83 20
156789 205 11
167890 365 11
234567 28 10
245678 110 5
4.
SELECT account_id, EXTRACT(MONTH FROM date_creation) as monthCreated
, TRIM(deposited) || '-' || TRIM(withdrawn) as depWith
FROM dep_wth;

account_id monthCreated depWith

123456 2 120.00-43.00
134567 5 173.47-115.21
145678 7 83.20-345.21
156789 9 205.11-17.65
167890 10 365.11-100.23
234567 2 28.10-200.02
245678 3 110.05-45.63

5.
SELECT * FROM dep_wth
WHERE EXTRACT(DOW FROM date_creation) IN (0,6);

account_id date_creation deposited withdrawn

167890 2017-10-28 365.11 100.23

6.
The day, month and year of account creation will be displayed for every
account starting with the character (digit) ‘2’. Applying the reserved word
DISTINCT prevents repetion of identical rows.

day month year

1 2 2018
12 3 2018

7.
The account id the date of the account creation and the data of archiving the
account will be displayed (180 days after the account creation) for accounts
created in 2018.
account_id date_creation date_archiving
234567 01.02.2018 31.07.2018
245678 12.03.2018 08.09.2018

8.
Data on the first five students born before 1988 will be displayed in the
following manner: students’ first name in uppercase, lastname in lowercase.

firstname lastname
KRISTIJAN paškoto
HRVOJE korkut
HRVOJE buneta
TONEI matković
LOBELL ćubelić
NOTE: The illustration above is only one possible display option. Since the
query does not define the expected order of records (ORDER BY) it is not
possible to define a priori which 5 students will be displayed.

9.
Students’ first names consisting of less than 4 letters will be displayed in
lowercase, without redundant repetition.

firstname
tea
mia
ivo
ana
iva
tin
ani
eva
jan

10.
The user accessing the table, the students’ last name and the students’ year
of birth are displayed, but only for students whom the last two digits of the pin
are ‘99’.

current_user prezime yearOfBirth

postgres Frančišković 1993
postgres Orlić 1994
posgtres Gomojić 1998
postgres Keršić 1998

11.
a) Displays the student identifier, the date and time of entry and exit and the
difference between the exit and the entry shown as an interval.

employeeid dateEntry timeEntry difference

10 14.03.2019 08:17:03 07:46:17
223 22.02.2019 09:04:55 09:10:36
584 22.02.2019 10:10:32 02:02:17
584 22.02.2019 14:10:11 05:04:44
The same query can be executed using the function CAST:
SELECT employeeid,
CAST(entry AS DATE) AS dateEntry,
CAST(exit AS TIME) AS timeEntry,
exit – entry AS difference
FROM attendance
WHERE exit IS NOT NULL;

b) Displays records where the difference between the entry time and the exit time
is less than 8 hours.
employeeid entry exit
10 14.03.2019 08:17:03 14.03.2019 16:03:20
584 22.02.2019 10:10:32 22.02.2019 12:12:49
584 22.02.2019 10:11:11 22.02.2019 19:14:55

c) Displays student identifiers and the number of days elapsed since the entry
until now for records from Februrary.

sifradjelatnik proteklo
223 33
584 33
584 33

Note: tuples displayed as the query result depend on the date of query execution
(The result displayed for 27.3.2019)

What contetnt would be desplayed if we change the command EXTRACT(DAY

FROM current_timestamp – entry with current_timestamp –
entry?

A column with the number of days, hours, minutes and secons passed since the
employee's entry to the moment of the query execution would be displayed.
employeeid elapsed
223 33 days 05:46:08.085846
584 33 days 04:40:31.085846
584 33 days 00:40:52.085846

d)
SELECT *
FROM attendance
WHERE entry >= '14.03.2019' AND
entry <= CAST('14.03.2019' AS DATE) + 22
e)
SELECT employeeid
FROM attendance
WHERE entry::DATE <> exit::DATE AND
entry::TIME < '09:00'
f)
SELECT dateOfBirth,
(EXTRACT (YEAR FROM CURRENT_DATE) -
EXTRACT (YEAR FROM dateOfBirth))*'1 Y'::INTERVAL AS year,
(dateOfBirth+ (EXTRACT (YEAR FROM CURRENT_DATE) -
EXTRACT (YEAR FROM dateOfBirth))*'1 year'::INTERVAL)::DATE
AS birthday,
- (CURRENT_DATE -
(dateOfBirth+ (EXTRACT (YEAR FROM CURRENT_DATE) -
EXTRACT (YEAR FROM dateOfBirth))*'1 year'::INTERVAL)::DATE)
AS daysToBirthday
FROM student
Note the red highlighted part of the query. If we only subtracted two years, we
would get an ordinary double precision numeric value. Since the task requires
the age to be expressed as an interval, this number must be multiplied by '1 Y' ::
INTERVAL. Multiplying the numerical value and the interval gives the data type
interval (lecture: SQL Part 1, Slide 36).

To indicate that these are years (not days, hours, etc.), the numerical expression
must be multiplied by the expression '1 Y'. However, that is not enough. The
system interprets the term '1 Y' as a character string. Therefore, the term '1 Y'
should be cast first at intervals. If we omit :: INTERVAL the system will report an
error.

For exercise, try converting the last column to the interval type yourself (help:
multiply by '1 DAY' :: INTERVAL).

WHERE CLAUSE, BETWEEN, IN, IS NULL, LIKE, SELECT CASE

1.
SELECT firstname, lastname
FROM teacher
WHERE dateemployedfrom BETWEEN '15.1.2001' AND '15.7.2001'
AND dateemployedto IS NOT NULL;

2.
SELECT DISTINCT orgunit.orgunitname
FROM teacher, orgunit
WHERE teacher.orgunitid = orgunit.orgunitid
AND dateemployedto IS NULL
AND firstname like '%a';

3.
SELECT coursename
FROM course
WHERE LOWER(coursename) LIKE '%theory%';
Note: we could have also used: UPPER(coursename) LIKE '%THEORY%'

4.
SELECT DISTINCT studentid
FROM enrolledcourse JOIN course USING (courseid)
JOIN exam USING (studentid, courseid)
WHERE UPPER(coursename) LIKE '%MATHEMATICS%'
AND grade IN(2, 4);
5.
Note: the solution also contains an alternative status, status2, which is not
required by the task, but serves to demonstrate an alternative solution.

SELECT DISTINCT enrolledcourse.studentid, coursename,

CASE
WHEN(grade = 1) THEN 'not_passed'
WHEN(grade IN(2, 3, 4, 5)) THEN 'passed'
ELSE 'unknown'
END as status,
CASE
WHEN(grade = 1) THEN 'not_passed'
WHEN(grade IS NULL) THEN 'unknown'
ELSE 'passed'
END as status2
FROM enrolledcourse
JOIN course
ON enrolledcourse.courseid = course.courseid
AND UPPER(coursename) LIKE '%MATHEMATICS%'
JOIN exam
ON enrolledcourse.courseid = exam.courseid
AND enrolledcourse.studentid = exam.studentid

6.

a)
studentid courseid academicyear groupid dategrade grade

b)
studentid courseid academicyear groupid dategrade grade
0555001157 26 2005 D-A5 (null) (null)
0555001157 27 2005 D-A5 (null) (null)

c)
studentid courseid academicyear groupid dategrade grade
0555001211 2 2003 C-B2 02.02.2004 5

d) This is an incorrect query – null cannot be used in this way.

e) f) g) We must remember that the % sign has wildcard status in combination

with the LIKE operator, and therefore does not represent any character array.
Query f) is analogous to query g). Likewise, the e) query produces a result
identical to the result of the following query:

SELECT *
FROM student
WHERE firstname >= 'I%' AND firstname <= 'J%';

and therefore retrieves every student whose first name is alphabetically greater
than or equal to 'I%' and also smaller than or equal to 'J%'. Since the ASCII value
of character '%' is 37, al digits and letters are alphabetically larger than '%' (since
they have larger ASCII values), e.g. 'I0' is greater than 'I%', 'IA' is greater than
'I%'. Using the'%' character has no sense in the presented context.
Since first names consist exclusively of letters, greater than or equal to 'I%' de
facto means greater than or equal to 'I', all three queries return the same result:
any student whose name starts with 'I' or a student whose name is exactly "J".
Execute the queries against the table student in the studAdmin database.

7.
a)
studentid firstname
101 Ivana
102 Ivano

b)
studentid firstname
100 Ivan
101 Ivana
102 Ivano

Note that '%' represents a character array 0 or more characters long, while _
represents exactly one character.

8.
a)
property amount type type2
Pressure 1012hPa Absolute value Absolute value
Temperature 25 C Absolute value Absolute value
Humidity 80% Percentage Percentage
Wind 2-3 NW Absolute value Absolute value
CO2 (null) Absolute value Percentage

b) The retrieved data is identical to the data retrieved by the query a). Note that
the ESCAPE character '8' does not refer to character array content in the table
('80%') but serves only to determine to which '%' character the reserved word
ESCAPE refers to. Anyway, it is better to avoid such potential dilemmas and use
a "special" character (e.g. #, ^, $, etc.) which almost surely will not be part of
character arrays, as presented in query a).

property Amount type type2

Pressure 1012hPa Absolute value Absolute value
Temperature 25 C Absolute value Absolute value
Humidity 80% Percentage Percentage
Wind 2-3 NW Absolute value Absolute value
CO2 (null) Absolute value Percentage
c) Note (below) that the red-color % character states that the green-color %
character does not have a wildcard role, and thus selects 3-character-long arrays
ending with %. Thus, the character array '80%' satisfies the condition, whereas
'5%' (for instance) does not (its length is 2).

LIKE '__%%' ESCAPE '%'

Anyway, it is better to avoid such potential dilemmas and use a "special"

character (e.g. #, ^, $, etc.) which almost surely will not be part of character
arrays, as presented in query a).

property amount type type2

Pressure 1012hPa Absolute value Absolute value
Temperature 25 C Absolute value Absolute value
Humidity 80% Percentage Percentage
Wind 2-3 NW Absolute value Absolute value
CO2 (null) Absolute value Percentage

9. The query retrieves all organizational units that have superior organizational
units, and those superior units satisfy two conditions:
a) they have no superior organizational unit themselves
b) their name contains the character array 'vele' regardless of letter
capitalization
Execute the query against the studAdmin database.

10.
orgunitname suporgunit
ZPR has a superior unit, which is FER
ZOEEM has a superior unit, which is FER
ZPM has a superior unit, which is FER

Organizational units that do not have a superior will not appear in the result,
since they do not satisfy the join condition:

WHERE orgunit.superorgunitid = suporgunit.orgunitid

In case we wanted to include them into the result as well, outer join should be
applied:

SELECT orgunit.orgunitname,
CASE
WHEN(orgunit.superorgunitid IS NULL) THEN 'no superior'
ELSE 'has a superior unit, which is' || suporgunit.orgunitname
END as suporgunit
FROM orgunit
LEFT OUTER JOIN orgunit suporgunit
 ON orgunit.superorgunitid = suporgunit.orgunitid;

In that case the result would be:

orgunitname suporgunit
FER no superior
ZPR has a superior unit, which is FER
ZOEEM has a superior unit, which is FER
ZPM has a superior unit, which is FER
PMF no superior

Execute both queries against the studAdmin database.

UNION, NULL, OUTER JOIN, ORDER BY

1. SELECT * FROM student

ORDER BY dateofbirth DESC

2. SELECT course.coursename, exam.grade

FROM enrolledcourse INNER JOIN course
ON course.courseid = enrolledcourse.courseid
LEFT JOIN exam
ON enrolledcourse.courseid = exam.courseid
AND enrolledcourse.studentid = exam.studentid
ORDER BY coursename, grade desc
3.
SELECT *
FROM student
WHERE student.dateofbirth IS NULL
OR student.zipbirth IS NULL
OR student.zipresidence IS NULL

4. INSERT INTO student (JMBAG, imeStudent, prezimeStudent, OIB,

spol, datumRod, pbrRodStudent, pbrStanStudent)VALUSES
('0036368145', 'Ante', 'Dragan', NULL, 'M', '12.01.1995', NULL,
NULL);

The next expression is also correct:

INSERT INTO student VALUES ('0036368145', 'Ante', 'Dragan', NULL,

'M', '12.01.1995', NULL, NULL);

5.
SELECT firstname, lastname
FROM teacher
ORDER BY coeff DESC
LIMIT 5

6.
SELECT firstname
FROM student, town
WHERE student.zipbirth = town.zip
AND town.townname = 'Dubrovnik'
UNION ALL
SELECT firstname
FROM teacher, town
WHERE teacher.zipresidence = town.zip
AND town.townname = 'Dubrovnik'

7.
SELECT zipresidence, townname
FROM student, town
WHERE student.zipresidence = town.zip
UNION
SELECT zipresidence, townname
FROM teacher, town
WHERE teacher.zipresidence = town.zip
ORDER BY townname
8.
SELECT classroom.classroomid, capacity, academicyear, studentgroupid
FROM classroom
LEFT OUTER JOIN coursegroup
ON classroom.classroomid = coursegroup.classroomid
9.
SELECT orgunit.orgunitid, orgunitname, teacherid
FROM orgunit
LEFT OUTER JOIN teacher
ON orgunit.orgunitid = teacher.orgunitid AND dateEmployedTo
IS NULL
WHERE orgunit.superorgunitid = 36;

10.
SELECT student.studentid, grade
FROM course
INNER JOIN enrolledcourse
ON course.courseid = enrolledcourse.courseid
AND course.coursename = 'Intelligent systems'
RIGHT OUTER JOIN student
ON student.studentid = enrolledcourse.studentid
LEFT OUTER JOIN exam
ON student.studentid = exam.studentid
AND course.courseid = exam.courseid
ORDER BY studentid, grade DESC

11.
SELECT firstname, lastname, townname, countyname
FROM student
LEFT OUTER JOIN town
ON town.zip = student.zipresidence
LEFT OUTER JOIN county
ON town.countyid = county.countyid

12.
SELECT firstname, lastname, academicyear, coursename
FROM course
INNER JOIN enrolledcourse
ON enrolledcourse.courseid = course.courseid
RIGHT OUTER JOIN student
ON enrolledcourse.studentid = student.studentid
LEFT OUTER JOIN exam
ON student.studentid = exam.studentid
AND course.courseid = exam.courseid
AND (grade = 5 OR grade = 4)
WHERE EXTRACT (MONTH FROM dateofbirth) = 6;

The next query is also correct:

SELECT firstname, lastname, academicyear, coursename

FROM course
NATURAL JOIN enrolledcourse
 RIGHT OUTER JOIN student
ON enrolledcourse.studentid = student.studentid
LEFT OUTER JOIN exam
ON student.studentid = exam.studentid
AND course.courseid = exam.courseid
AND (grade = 5 OR grade = 4)
WHERE EXTRACT (MONTH FROM dateofbirth) = 6;

… as well as the following query:

SELECT firstname, lastname, academicyear, coursename

FROM course JOIN enrolledcourse USING (courseid)
RIGHT OUTER JOIN student
ON enrolledcourse.studentid = student.studentid
LEFT OUTER JOIN exam
ON student.studentid = exam.studentid
AND course.courseid = exam.courseid
AND (grade = 5 OR grade = 4)
WHERE EXTRACT (MONTH FROM dateofbirth) = 6;

13.
SELECT student.firstname
, student.lastname, namesakeStudent.lastname
FROM student
LEFT OUTER JOIN student namesakeStudent
ON student.firstname = namesakeStudent.firstname
AND EXTRACT(YEAR FROM student.dateofbirth) =
EXTRACT(YEAR FROM namesakeStudent.dateofbirth)
AND student.studentid <> namesakeStudent.studentid
ORDER BY student.firstname, student.lastname

14.
SELECT town.townname, student.studentid, firstname, lastname
FROM town
LEFT JOIN student ON student.zipbirth = town.zip OR
student.zipresidence = town.zip
WHERE townname LIKE 'K%'
ORDER BY townname, lastname, firstname
PARALLEL JOIN, REFLEXIVE JOIN, GROUP BY, HAVING

DATABASE studAdmin;
1.
SELECT student.studentid
, student.lastname
, student.firstname
FROM student, town townB, town townR
WHERE student.zipbirth = townB.zip
AND student.zipresidence = townR.zip
AND townR.countyid = townB.countyid;

2.
SELECT student.studentid
, student.dateofbirth
, student.lastname
, student.firstname
, studentOlder.studentid
, studentOlder.dateofbirth
, studentOlder.lastname
, studentOlder.firstname
FROM student, student studentOlder
WHERE student.studentid <> studentOlder.studentid
AND student.dateofbirth > studentOlder.dateofbirth
AND SUBSTRING (student.firstname FROM 1 FOR 1) =
SUBSTRING(studentOlder.firstname FROM 1 FOR 1)
AND SUBSTRING(student.lastname FROM 1 FOR 1) =
SUBSTRING(studentOlder.lastname FROM 1 FOR 1);

3.
CREATE TABLE exam (
courseid INTEGER NOT NULL
, studentid CHAR(10) NOT NULL
, examDate DATE NOT NULL
, teacheridWritten INTEGER
, gradeWritten SMALLINT
, teacheridOral INTEGER
, gradeOral SMALLINT
, dateGradeOral DATE
);
CREATE UNIQUE INDEX idx_exam ON exam(courseid, STUDENTID, examDate);

INSERT INTO exam VALUES (5 , '0555000048', '08.01.2017', 271, 2, 268,

1, '12.01.2007');
INSERT INTO exam VALUES (5 , '0555000032', '08.01.2017', 267, 2, 271,
1, '12.01.2007');
INSERT INTO exam VALUES (14, '0555000048', '05.02.2017', 267, 3, 268,
4, '08.02.2007');
INSERT INTO exam VALUES (5 , '0555000032', '02.02.2017', 267, 4, 271,
5, '05.02.2007');
INSERT INTO exam VALUES (40, '0555000688', '02.02.2017', 268, 4, 268,
5, '05.02.2007');
As a result of executing the statement:
CREATE UNIQUE INDEX idx_exam ON exam(courseid, STUDENTID, examDate);
insertion of two or more tuples with the same value of the attribute triple
(courseid, studentid, examDate) will become impossible. Without executing this
statement, (accidental) multiple execution of an insert statement would cause the
statements in following related tasks to produce results different than expected.

4.
SELECT exam.studentid
, student.lastname AS lastnameStudent
, student.firstname AS firstnameStudent
, course.coursename as coursename
, teacherW.firstname AS gradedWrittenFirstname
, teacherW.lastname AS gradedWrittenLastname
, exam.gradeWritten
, teacherO.firstname AS gradedOralFirstname
, teacherO.lastname AS gradedOralLastname
, exam.gradeOral
FROM exam, student, course, teacher teacherW, teacher teacherO
WHERE exam.studentid = student.studentid
AND exam.courseid = course.courseid
AND exam.teacheridWritten = teacherW.teacherid
AND exam.teacheridOral = teacherO.teacherid;

5.
SELECT exam.studentid
, student.lastname AS lastnameStudent
, student.firstname AS firstnameStudent
, course.coursename as coursename
, exam.gradeWritten
, exam.gradeOral
FROM exam, student, course, teacher teacherW, teacher teacherO
WHERE exam.studentid = student.studentid
AND exam.courseid = course.courseid
AND exam.teacheridWritten = teacherW.teacherid
AND exam.teacheridOral = teacherO.teacherid
AND exam.teacheridWritten <> exam.teacheridOral
AND teacherW.zipresidence = teacherO.zipresidence;

6.
SELECT exam.studentid
, student.lastname AS lastnameStudent
, student.firstname AS firstnameStudent
FROM exam, student, course, town townB, town townR
WHERE exam.studentid = student.studentid
AND exam.courseid = course.courseid
AND student.zipbirth = townB.zip
AND student.zipresidence = townR.zip
AND student.zipbirth <> student.zipresidence
AND townR.countyid = townB.countyid
AND course.coursename = 'Databases';
 7.
INSERT INTO exam VALUES (4, '0555000053', '02.02.2007', NULL, NULL,
NULL, NULL, NULL);

The tuple does not produce a tuple in the result since join conditions
exam.teacheridWritten = teacherW.teacherid
and
exam.teacheridOral = teacherO.teacherid
are evaluated as UNKNOWN (i.e. not as true). Attribute values for
exam.teacheridWritten and exam.teacheridOral are unknown – NULL, hence the
following comparison is made:
NULL = teacher.teacherid.
In order to make the tuple part of the result, those conditions should be evaluated
as TRUE.

In order to make every tuple from the exam table part of the result, both inner
joins of tables exam and teacher must be replaced with outer joins.

SELECT exam.studentid
, student.lastname AS lastnameStudent
, student.firstname AS firstnameStudent
, course.coursename as coursename
, teacherW.firstname AS gradedWrittenFirstname
, teacherW.lastname AS gradedWrittenLastname
, exam.gradeWritten
, teacherO.firstname AS gradedOralFirstname
, teacherO.lastname AS gradedOralLastname
, exam.gradeOral
FROM exam
INNER JOIN student
ON exam.studentid = student.studentid
INNER JOIN course
ON exam.courseid = course.courseid
LEFT OUTER JOIN teacher teacherW
ON exam.teacheridWritten = teacherW.teacherid
LEFT OUTER JOIN teacher teacherO
ON exam.teacheridOral = teacherO.teacherid;

Since the reserved words INNER and OUTER may be omitted, the SELECT
statement above is equal to the following statement:
SELECT exam.studentid
, student.lastname AS lastnameStudent
, student.firstname AS firstnameStudent
, course.coursename as coursename
, teacherW.firstname AS gradedWrittenFirstname
, teacherW.lastname AS gradedWrittenLastname
, exam.gradeWritten
, teacherO.firstname AS gradedOralFirstname
, teacherO.lastname AS gradedOralLastname
, exam.gradeOral
FROM exam
JOIN student
ON exam.studentid = student.studentid
 JOIN course
ON exam.courseid = course.courseid
LEFT JOIN teacher teacherW
ON exam.teacheridWritten = teacherW.teacherid
LEFT JOIN teacher teacherO
ON exam.teacheridOral = teacherO.teacherid;
DROP TABLE exam;

8.
SELECT orgunitSub.orgunitname
FROM orgunit orgunitSub, orgunit
WHERE orgunitSub.superorgunitid = orgunit.orgunitid
AND orgunit.orgunitname = 'Fakultet elektrotehnike i računarstva';

9.
SELECT teacher.firstname, teacher.lastname, orgunit.orgunitname,
orgunitSuper.orgunitname
FROM teacher
INNER JOIN orgunit
ON teacher.orgunitid = orgunit.orgunitid
LEFT OUTER JOIN orgunit orgunitSuper
ON orgunit.superorgunitid = orgunitSuper.orgunitid
WHERE teacher.dateemployedto IS NULL;

10.
CREATE TABLE person (
idPerson INTEGER NOT NULL
, firstname VARCHAR(25) NOT NULL
, lastname VARCHAR(25) NOT NULL
, idPersonMarriedTo INTEGER
);
CREATE UNIQUE INDEX idx_person ON person (idPerson);

INSERT INTO person VALUES (238, 'Ivana','Martinec', 249);

INSERT INTO person VALUES (249, 'Andrija','Salopek-Rabatić', 238);
INSERT INTO person VALUES (252, 'Jure', 'Damiani-Einwalter', 263);
INSERT INTO person VALUES (256, 'Milica', 'Balen', NULL);
INSERT INTO person VALUES (257, 'Tatjana', 'Kancir', 261);
INSERT INTO person VALUES (261, 'Salih', 'Jalšovec', 257);
INSERT INTO person VALUES (262, 'Antun', 'Pajnić', NULL);
INSERT INTO person VALUES (263, 'Vlasta', 'Zanchi', 252);

As a result of executing the statement:

CREATE UNIQUE INDEX idx_ person ON person (idPerson);
insertion of two or more tuples with the same value of the attribute idPerson into
person will become impossible. Without executing this statement, (accidental)
multiple execution of an insert statement would cause the SELECT statement
below to produce results different than expected
SELECT person.firstname, person.lastname, personMarried.firstname,
personMarried.lastname
FROM person, person personMarried
WHERE person.idPersonMarriedTo = personMarried.idPerson;
DROP TABLE person;

11.
SELECT town.townname
FROM student, town
WHERE student.zipresidence = town.zip
GROUP BY student.zipresidence, town.townname
HAVING COUNT(student.studentid) > 1;

12.
SELECT academicyear, course.coursename
FROM coursegroup, course
WHERE coursegroup.courseid = course.courseid
GROUP BY academicyear, course.courseid, course.coursename
HAVING COUNT (studentgroupid) = 10;

13.
SELECT orgunit.orgunitname, COUNT(orgunitSub.orgunitid)
FROM orgunit
LEFT OUTER JOIN orgunit orgunitSub
ON orgunit.orgunitid = orgunitSub.superorgunitid
GROUP BY orgunit.orgunitid, orgunit.orgunitname;

Rearrange the query above in such manner, that grouping is made only
according to organizational unit name (organizational unit id is excluded in
comparison with the query above):

SELECT orgunit.orgunitname, COUNT(orgunitSub.orgunitid)

FROM orgunit
LEFT OUTER JOIN orgunit orgunitSub
ON orgunit.orgunitid = orgunitSub.superorgunitid
GROUP BY orgunit.orgunitname;

Note that the number of produced tuples is now greater than before. Namely,
there are several cases of multiple organizational units with identical names e.g.
'Ekonomski fakultet' [School of Economics] in Zagreb, Split, Rijeka and Osijek. If
grouping is performed exclusively according to unit name these four
organizational units form a single group, which is not what was required in the
task.

14.
SELECT town.townname, COUNT(studentid) studentNum
FROM town
LEFT JOIN student ON student.zipbirth = town.zip OR
student.zipresidence = town.zip
WHERE townname LIKE 'B%'OR townname LIKE 'D%'
GROUP BY town.townname
ORDER BY studentNUm DESC

Would the result be correct if you wrote at the top of the query:
a) COUNT(firstname) or COUNT(lastname)
 b) COUNT(townname)
instead of COUNT(JMBAG)

a) It would be because the firsname and lastname are NOT NULL attributes
and the value will be known to those tuples for which the condition of the
merge is evaluated as true.
b) It would not be because townname is NOT NULL, regardless whether the
concrete tuple from the town was joined to a tuple from student. In the
result, the value 0 would never appear for the studnetNum column.

15.
Write the SQL query by which, as in the table below, the data of the weighted
grade average of the passed exams (grade is known and positive) by months
and years will be shown.
The column 'weightedAverage' is calculated as following: grade multiplied by
ECTS points of the course is summed and divided by the sum of the ECTS
points. Round it by two decimals.

Only the years and months with the weighted average greater than 3.5 should
be selected.
The tuples in the results should be ordered by the weighted average so the
year and month with the highest weighted average would be on the top of the
list.

Year Month weightedAverage

2019 12
2020 11
2019 5
... ... ...

SELECT EXTRACT(YEAR FROM examTermDate) as year

, EXTRACT(MONTH FROM examTermDate) as month
, ROUND(SUM(grade*ects)/SUM(ects), 2) weightedAverage
FROM exam
NATURAL JOIN course
WHERE grade > 1
GROUP BY EXTRACT(YEAR FROM examTermDate), EXTRACT(MONTH FROM
examTermDate)
HAVING ROUND(SUM(grade*ects)/SUM(ects), 2) > 3.50
ORDER BY ROUND(SUM(grade*ects)/SUM(ects), 2) DESC

Notice that the argument of the aggregate function SUM is the expression
grade*ects
As the aggregate function arguments it is allowed to use expressions of the
arbitrary complexity.
Their result, of course, has to be of the type for which the aggregate function
can calculate the value.
 Also, the groupping can be done by expressions. This is the case in the given
example where the groupping by expressions EXTRACT(YEAR FROM
dateExam) and EXTRACT(MONTH FROM dateExam) is done.

Moreover, notice that the tuples order is done by the complex expression
ORDER BY ROUND(SUM(grade*ects)/SUM(ects), 2) DESC.
We would get the same result by using the alias ORDER by weightedAverage
DESC instead of the expression.

16.
Write the SQL query by which, as in the table below, the data about the students'
attendance in the classrooms will be shown.
We are only interested in the attendances with the entry time between 20:00 and
22:00 that lasted more than one hour.
In the column 'totalTime' list the total time (sum) of the student's attendances with
the entry time between 20:00 and 22:00.
The resulting tuples should be ordered by the total attendance time ('totalTime')
so the student with the longest total attendance time would be on the top of the
list.

studentID lastName firstName attendanceCount totalTime

... ... ... ... ...
0555001440 Dujmović Boris 5 18:30:55
0555004512 Prodan Tihana 5 18:03:38
0555004484 Tuđman Tea 7 16:40:32
0555001888 Vretenar Slaviša 3 15:46:21
... ... ... ... ...

SELECT studentID, lastName, firstName

, COUNT(*) attendanceCount
, SUM(endts - startts) totalTime
FROM attendance
NATURAL JOIN student
WHERE startts::TIME BETWEEN '20 h'::INTERVAL and '22 h'::INTERVAL
AND endts - startts > '1 hour'::INTERVAL
GROUP BY studentID, lastName, firstName
ORDER BY SUM(startts - endts) DESC

Notice that the argument of the aggregate function in this example is also a
complex expression 'entts - startts'.
The result of the expression 'endts - startts' is of a type INTERVAL so the result
SUM(endts - startts) is also of a type INTERVAL.
In this case the order of the tuples is also done using the complex expression
ORDER BY SUM(startts - endts) DESC