PostgreSQL: Advanced SQL Queries

In this course, we will discuss advanced queries for PostgreSQL. We will learn about functions and operators, type conversions, and transactions.
Course info
Rating
(69)
Level
Advanced
Updated
Aug 13, 2015
Duration
1h 41m
Table of contents
Description
Course info
Rating
(69)
Level
Advanced
Updated
Aug 13, 2015
Duration
1h 41m
Description

PostgreSQL is commonly known as Postgress. It is often referred to as the world’s most advanced open source database. In this course, we will discuss advanced queries for PostgreSQL. We will learn about functions and operators, type conversions, and transactions.

About the author
About the author

Pinal Dave is a Pluralsight Developer Evangelist.

More from the author
Getting Started with MariaDB
Beginner
2h 23m
Nov 2, 2018
Monitoring MySQL with Performance Schema
Intermediate
1h 59m
Apr 28, 2017
More courses by Pinal Dave
Section Introduction Transcripts
Section Introduction Transcripts

Introduction
Hi, this is Pinal Dave, and I welcome you to the PostgreSQL: Advanced SQL Queries course. This is an Introduction module, and we will explore what we are going to learn in this course. First things first, PostgreSQL or Postgres? Well, the official logo has the word PostgreSQL, but it is commonly known as Postgres. In this course we are going to call PostgreSQL Postgres. This is the third course in a series of fives courses. In the very first course, we learned how to install and configure Postgres. In the second course we learned the basics of SQL queries, and that course is a prerequisite of this course. In this course, we will discuss various advanced concepts related to SQL queries like functions, operators, type conversions, and transactions. In the next course, we will talk about advanced server programming like triggers and procedures. And in the final course, we will discuss performance tuning concepts and index tuning. Well, before we understand what we are going to cover in this course, let's see a few of the important downloads. If you have not installed Postgres, you can download it for free from the URL displayed on your screen. In the very first course, I explain from the fundamentals how you can install Postgres. If you are using a Windows platform, you can also download a Windows graphical installer. A Windows graphical installer installs the Postgres server with pgAdmin III. PgAdmin III is a graphical integrated developer environment for Postgres. We will be using pgAdmin III as a default IDE for this course. You can download the sample database from the URL displayed on the screen. We will be running various examples on this sample database. Now, let's see what we are going to cover in this course. In this course, first we are going to talk about functions and operators. Postgres has a wide library of various functions and operators. If you want to do any mathematical calculation, advanced science, operations or simple string manipulation, Postgres has many different functions and operators for different purposes. In the module about functions and operators, we will understand a few of the important functions and understand how we can use them with various different operators. Right after that we will talk about type conversion. Type conversion has two important impacts on your database. The first is performance and the second is database integrity. With the help of real world scenarios, we will see how type conversion impacts performance, as well as data ambiguity. We want to make sure we pick the right kind of conversion and keep our database in its optimal state. Finally, we will talk about transactions. Transactions are a very key concept when it is about multiple updates to a database. If there is no concept of transactions, the queries will end up in the state, which is inconsistent. With the help of various transaction operators and keywords, we can ensure database integrity and consistency. Well, with this, our introduction is over. Let's start with our journey to learn Postgres. If, during this journey of learning Postgres advanced SQL queries, if you have any questions, feel free to post it in the Pluralsight discussion forum. You can also reach out to me on my social media coordinates for any questions or feedback. Let's start. In the next module, we are going to talk about functions and operators and its impact on our real world scenarios.

PostgreSQL: Operators and Functions
Hi, this is Pinal Dave, and welcome to this module of PostgreSQL: Operators and Functions. This module covers operations and functions available in Postgres. These character symbols and identifiers allow the Postgres user to flexibly modify and compare results within SQL statements. The result of these operations can be used in a variety of ways, from updating existing rows to constraining query results to only rows matching particular conditions. Let's see the agenda of this module. Postgres provides a large number of functions and operators for the building data types. First we will cover operators. An operator performs operations on values and returns the result of that operation. For example, there are logical, comparison, string, date time, and various other operators available in Postgres. Right after that, we will cover functions. They instruct Postgres to perform programatical operations within a SQL statement. There are mathematical, string, date time, and various other functions. In Postgres, sometimes we have operators and functions both accomplish the same task. It's quite common to see operators and functions working together in a single SQL statement and returning the desired result. Hence, we will see operators and functions together in many places in this module. To strengthen the concept of the operators and functions, we will see a few real world scenarios. We will take the help of two of the characters inspired from the real world and see how they deal with various issues of databases where operators and functions play a very critical roll. In the next clip we will understand the definition of operators and functions.

PostgreSQL: Type Conversion
Hi. This is Pinal Dave, and welcome to the module on Type Conversion. SQL statements can intentionally or not require the mixing of different data types in the same expression. Postgres has extensive facilities for evaluating mixed type expressions. This particular behavior of evaluating mixed type expressions is commonly known as type conversion. This module introduces Postgres type conversion mechanisms and conventions. Let's see what we are going to cover in this module next. Here is the agenda. First we will have an introduction to type conversion. We will understand the significance of type conversion and why every developer should keep this in mind when writing SQL queries. Right after that we will discuss implicit conversion and explicit conversion. Once we have a clear understanding of these two types of conversion, we will see a few of the real world scenarios. Now let's understand what type conversion is in the next clip.

PostgreSQL: Transactions
Hi. This is Pinal Dave, and welcome to this module of Postgres: Transactions. This module will discuss transactions in Postgres. If there is no concept of transactions, the queries will end up in a state that is not consistent. We will discuss this with the help of a demonstration where we will see inconsistent data without transactions, and we will see consistent data with transactions. Let's see next what we are going to cover in this module. Here is the agenda. First we will talk about what is a transaction. Right after that we will discuss the significance of a transaction in the light of atomicity, which is all or nothing, guarantee of record, and visibility of updates. We will understand all this with the help of real world scenarios. Now let's start this module with understanding what is a transaction in the next clip.