Lab work 6
Relational algebra (University database, individual work)
Goal: to create a new database and implement SELECT queries for the following
tasks with the PostgreSQL DBMS.
Preparation:
• Create a new database with the following SQL code:
CREATE TABLE Students(
stud_id int PRIMARY KEY,
fname varchar(20),
lname varchar(20)
);
CREATE TABLE Departments(
dep_id int PRIMARY KEY,
name varchar(10)
);
CREATE TABLE Teachers(
teach_id int PRIMARY KEY,
fname varchar(20),
lname varchar(20),
dep_id int REFERENCES Departments(dep_id)
);
INSERT INTO Students VALUES (1,'A','A'), (2,'B','B');
INSERT INTO Departments VALUES (1,'AAA'), (2,'BBB');
INSERT INTO Teachers VALUES (1,'B','B', 2), (2,'C','C', 2);
Note:
• DBMS: PostgreSQL. If you have any difficulties with the PostgreSQL, you can
temporarily use online compilers (for example, extendsclass.com/postgresql-
online.html, sqliteonline.com, rextester.com, sqlfiddle.com, etc.).
• Write only one SELECT query for every task.
• Answer for the LW6 is a report with SQL scripts and result screenshots for every
task.
Tasks:
1. Show all information about all teachers.
2. Show last names of all students (only last names, not all columns).
3. Show last names of teachers whose first names = "B"
4. Show information about all teachers and all students in the one table (with 2
columns: first name, last name).
5. Show information about students, but without persons who are students and
teachers at the same time (in the table with 2 columns: first name, last name).
6. Show information only about persons who are students and teachers at the
same time (in the table with 2 columns: first name, last name).
7. Show teachers and their departments in the following table: teachers' last name,
department name. Include only teachers which have departments and
departments which have teachers.
8. Show teachers and their departments in the following table: teachers' last name,
department name. In this task include also teachers which at this moment have
not any department.
9. Show teachers and their departments in the following table: teachers' last name,
department name. In this task include also departments which at this moment
have not any teachers.
10. Show teachers and their departments in the following table: teachers' last name,
department name. In this task include also teachers which at this moment have
not any department as well as departments which at this moment have not any
teachers.
Required structure of the answer:
1. Show all information about all students.
SELECT *
FROM Students;
2. …
3. …
…
Upload: a report (*.doc(x) or *.pdf)
Materials:
• Lectures 7, 8;
• Connolly, Thomas M. Database Systems: A Practical Approach to Design,
Implementation, and Management.
• www.postgresql.org/docs/manuals/
