🐘PostgreSQL

IMPORTANT NOTE: We will continue the second week lessons based on the information on https://www.postgresqltutorial.com/. You can reach detailed explanations of each subject from the relevant links below. In our physical lesson, we will do exercises on the sample database used here. For this reason, it is important that we come to the lesson with the sample database file running on our computers from the link https://www.postgresqltutorial.com/load-postgresql-sample-database/. The name of the sample database file is dvdrental. We will do most of the SQL query exercises from PostgreSQL 14 version and PgAdmin 4 interface.

Section 1. Querying Data

• Select : show you how to query data from a single table.

• Column aliases : learn how to assign temporary names to columns or expressions in a query.

• Order By : guide you on how to sort the result set returned from a query.

• Select Distinct : provide you a clause that removes duplicate rows in the result set.

Section 2. Filtering Data

• Where : filter rows based on a specified condition.

• Limit : get a subset of rows generated by a query.

• Fetch : limit the number of rows returned by a query.

• In : select data that matches any value in a list of values.

• Between : select data that is a range of values.

• Like : filter data based on pattern matching.

• Is Null : check if a value is null or not.

Section 3. Joining Multiple Tables

• Joins : show you a brief overview of joins in PostgreSQL.
• Table aliases : describes how to use table aliases in the query.
• Inner Join : select rows from one table that has the corresponding rows in other tables.
• Left Join : select rows from one table that may or may not have the corresponding rows in other tables.
• Self-join : join a table to itself by comparing a table to itself.
• Full Outer Join : use the full join to find a row in a table that does not have a matching row in another table.
• Cross Join : produce a Cartesian product of the rows in two or more tables.
• Natural Join : join two or more tables using implicit join condition based on the common column names in the joined tables.

Section 4. Grouping Data

• Group By : divide rows into groups and applies an aggregate function on each.
• Having : apply conditions to groups.

Section 5. Set Operations

• Union : combine result sets of multiple queries into a single result set.
• Intersect : combine the result sets of two or more queries and returns a single result set that has the rows appear in both result sets.
• Except : return the rows in the first query that does not appear in the output of the second query.

Section 6. Grouping sets, Cube, and Rollup

• Grouping Sets : generate multiple grouping sets in reporting.
• Cube : define multiple grouping sets that include all possible combinations of dimensions.
• Rollup : generate reports that contain totals and subtotals.

Section 7. Subquery

• Subquery : write a query nested inside another query.
• ANY : retrieve data by comparing a value with a set of values returned by a subquery.
• ALL : query data by comparing a value with a list of values returned by a subquery.
• EXISTS : check for the existence of rows returned by a subquery.

Section 8. Common Table Expressions

• PostgreSQL CTE : introduce you to PostgreSQL common table expressions or CTEs.
• Recursive query using CTEs :discuss the recursive query and learn how to apply it in various contexts.

Section 9. Modifying Data

In this section, you will learn how to insert data into a table with the INSERT statement, modify existing data with the UPDATE statement, and remove data with the DELETE statement. Besides, you learn how to use the upsert statement to merge data.

• Insert : guide you on how to insert single row into a table.
• Insert multiple rows : show you how to insert multiple rows into a table.
• Update : update existing data in a table.
• Update join : update values in a table based on values in another table.
• Delete : delete data in a table.
• Upsert : insert or update data if the new row already exists in the table.

Section 10. Transactions

• PostgreSQL Transactions : show you how to handle transactions in PostgreSQL using BEGIN, COMMIT, and ROLLBACK statements.

Section 11. Import & Export Data

You will learn how to import and export PostgreSQL data from and to CSV file format using the copy command.

• Import CSV file into Table : show you how to import CSV file into a table.
• Export PostgreSQL Table to CSV file : show you how to export tables to a CSV file.

Section 12. Managing Tables

In this section, you will start exploring the PostgreSQL data types and showing you how to create new tables and modify the structure of the existing tables.

• Data types : cover the most commonly used PostgreSQL data types.
• Create table : guide you on how to create a new table in the database.
• Select Into & Create table as : shows you how to create a new table from the result set of a query.
• Auto-increment column with SERIAL uses SERIAL to add an auto-increment column to a table.
• Sequences : introduce you to sequences and describe how to use a sequence to generate a sequence of numbers.
• Identity column : show you how to use the identity column.
• Alter table : modify the structure of an existing table.
• Rename table : change the name of the table to a new one.
• Add column : show you how to use add one or more columns to an existing table.
• Drop column : demonstrate how to drop a column of a table.
• Change column data type : show you how to change the data of a column.
• Rename column : illustrate how to rename one or more columns of a table.
• Drop table : remove an existing table and all of its dependent objects.
• Truncate table : remove all data in a large table quickly and efficiently.
• Temporary table : show you how to use the temporary table.
• Copy a table : show you how to copy a table to a new one.

Section 15. Conditional Expressions & Operators

• CASE : show you how to form conditional queries with CASE expression.
• COALESCE : return the first non-null argument. You can use it to substitute NULL by a default value.
• NULLIF : return NULL if the first argument equals the second one.
• CAST : convert from one data type into another e.g., from a string into an integer, from a string into a date.

Section 16. PostgreSQL Utilities

• psql commands : show you the most common psql commands that help you interact with psql faster and more effectively.

Section 17. PostgreSQL Recipes

• How to compare two tables : describe how to compare data in two tables in a database.
• How to delete duplicate rows in PostgreSQL : show you various ways to delete duplicate rows from a table.
• How to generate a random number in a range : illustrate how to generate a random number in a specific range.
• EXPLAIN statement : guide you on how to use the EXPLAIN statement to return the execution plan of a query.
• PostgreSQL vs. MySQL : compare PostgreSQL with MySQL in terms of functionalities.

END OF THE LECTURE