Lab work 7
Queries (University database, individual work)
Goal: to implement SELECT queries for the following tasks with the PostgreSQL
DBMS.
Note:
• Use the University database from the LW4,5.
• 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 LW7 is a report with SQL scripts and result screenshots for every
task. Moreover, the University db ER-diagram must be added to the report.
Tasks:
1. Use SELECT statement for any table with aliasing a result column.
2. Use aliasing of tables in a SELECT statement with several tables (with join
operation).
3. Select data from any table and represent two different attributes in one column
in the result table.
4. Write a SELECT query to get data from any table's attribute without duplicates
values.
5. Select only null or not null values of any table's attribute.
6. Use SELECT statement to get rows from any table with filtering values in a
column between two values (using the <,<=,> and >= operators).
7. Use SELECT statement to get rows from any table with filtering values in a
column between two values (using the BETWEEN operator).
8. Use % with LIKE operator to find some values.
9. Use _ with LIKE operator to find some values.
10. Write a SELECT query with CAST or :: operators.
Required structure of the answer:
Copy the University database ER-diagram here.
1. Show all information about all students.
SELECT *
FROM Students;
2. …
Upload: a report (*.doc(x) or *.pdf)
Materials:
• Lecture 9;
• Connolly, Thomas M. Database Systems: A Practical Approach to Design,
Implementation, and Management.
• www.postgresql.org/docs/manuals/
