PostgreSQL 11 in 7 days

Create, manage, and maintain your database efficiently using PostgreSQL in just a week
Instructor:
Packt Publishing
11 students enrolled
English [Auto-generated]
Write SQL statements for PostgreSQL
Create databases and database structures with SQL queries using PostgreSQL
Build complex PostgreSQL statements using subqueries
Insert and manipulate data in a PostgreSQL database
Use Structured Query Language (SQL) to manipulate data in databases

Companies value their data; as a result, it’s becoming more important for professionals to have technical knowledge of data management. PostgreSQL is by far one of the most popular and powerful SQL engines among other platforms such as MariaDB, MySQL, and SQL Server.

PostgreSQL in 7 Days will help you unravel the potential of PostgreSQL in a short time. You will learn about relational databases to present usable and accurate data in tables with rows and columns in just a week. You also cover the latest features of PostgreSQL 11. 

By the end of the course, you will have an invaluable knowledge of both SQL and the PostgreSQL database engine.

About the Author

Daniel Scott is self-motivated and extremely passionate about building software and empowering other people to become software developers. Having made a career transformation in the past, he understands the journey one must endure when breaking into a new industry, which is where he came up with the idea for My Developer Journey, a brand for aspiring software developers.

His main areas of expertise are PostgreSQL, full-stack JavaScript and the Django/Python application framework 

Create and Insert Data in Your Database

1
The Course Overview

This video provides an overview of the entire course.

2
Understanding PostgreSQL and Its Uses

Why learn PostgreSQL?

   •  Understand the importance of PostgreSQL

   •  Explore the benefits of this course and learning SQL/ PostgreSQL

   •  Summarize the learning objectives

3
Download and Install PostgreSQL Client pgAdmin

Learn to locate and install PostgreSQL, and pgAdmin. Learn an alternative way to run PostgreSQL if you are a macOS user

   •  Install PostgreSQL graphical installer. Learn a similar solution for macOS

   •  Install pgAdmin. Learn about alternative PostgreSQL agent for macOS users

   •  Configure local PostgreSQL server and make connection using pgAdmin

4
Setup a Practice Database Using Referenced .tar File

Learn how to setup or restore a database using an archive file using pgAdmin

   •  Use pgAdmin to create a database

   •  Use included .tar file to restore database with data

   •  Use pgAdmin to execute first SQL query on restored database

5
Create Database, Table, Insert, and Query Data – Part 1

Now that you have learned how to restore a database, you will learn how to create a brand new database, a table, a row of data in that table, and query that row using SQL.

   •  Review the syntax for creating a database in SQL

   •  Create a table using SQL syntax

   •  Summarize learning

6
Create Database, Table, Insert, and Query Data – Part 2

Now that you have learned how to restore a database, you will learn how to create a brand new database, a table, a row of data in that table, and query that row using SQL.

   •  Review syntax for creating table and inserting data

   •  Create a table using SQL syntax

   •  Insert row of data and query row

7
PostgreSQL Relationships Using What’s Called a Primary Key and Foreign Key

Learn how PostgreSQL uses key columns to uniquely identify tables rows and reference the rows from other tables

   •  Learn what Primary Keys are used for

   •  Learn what Foreign Keys are used for

   •  Learn how Primary and Foreign keys create table relationships

8
Assignment

This assignment will provide the opportunity to gain additional practice and validate knowledge of the concepts covered in section two.

   •  Introduce the tasks for the assignment

   •  Look at the rules of the assignment

Retrieve Data from Your PostgreSQL Database

1
Assignment Review from Day 1

Review the assignment from day one and go over the solutions.

   •  Review assignment tasks.

   •  Review solutions to assignment tasks.

2
Introduction to the SELECT Statement to Retrieve Rows of Data

Learn how to properly query a database using SQL starting with the SELECT statement.

   •  Understand the syntax of the SELECT statement

   •  Run the query columns from a table using the SELECT statement

3
Introduction to the SELECT DISTINCT Statement

Learn how to properly query a database with the SELECT DISTINCT statement.

   •  Understand the syntax of the SELECT DISTINCT statement

   •  Practice using the SELECT DISTINCT statement

4
SELECT Statement with WHERE Clause

Learn how to properly query a database with the SELECT statement with the WHERE clause.

   •  Understand the syntax of the SELECT statement with WHERE clause

   •  Demo using the SELECT statement with WHERE clause

5
Insert Data into Tables, Update, and Delete Rows

You will gain more practice inserting data into tables, but more importantly learn how to update and delete rows of data as well.

   •  Understand the syntax to update and delete table rows

   •  Demo and practice using UPDATE and DELETE FROM in pgAdmin

6
Assignment

This assignment will provide the opportunity to gain additional practice and validate knowledge of the concepts covered in section two.

   •  Introduce the tasks for the assignment

   •  Look at the rules of the assignment

Count Rows in Your Table

1
Assignment Review from Day 2

Review the assignment from section two /day two and go over the solutions.

   •  Review assignment tasks

   •  Review solutions to assignment tasks

2
Count All Rows Using the COUNT Function

Learn how to use PostgreSQL functions like COUNT() to count rows of data in database tables.

   •  Introduce to concept, review syntax of COUNT() function

   •  Practice with COUNT() function using pgAdmin

3
Use COUNT with the DISTINCT Clause

Learn how to use the COUNT() function with the DISTINCT clause

   •  Introduce the concept, review syntax of COUNT(DISTINCT)

   •  Practice with COUNT(DISTINCT) using pgAdmin

4
Order Query Results with the ORDER BY Clause

Learn how to order the results of your query based on the column data

   •  Introduce concept and review syntax of ORDER BY clause

   •  Practice with ORDER BY using pgAdmin

5
Using ORDER BY with ASC and DESC Modifiers

Learn how to change the sorting of your ORDER BY statement using the ASC and DESC modifiers.

   •  Introduce concept and introduce the syntax of the modifiers

   •  Practice with ORDER BY clause with modifiers

6
Assignment

This assignment will provide the opportunity to gain additional practice and validate knowledge of the concepts covered in section 3.

   •  Introduce the tasks for the assignment

   •  Look at the rules of the assignment

Return Specific Data Using Special Conditional Clauses

1
Assignment Review from Day 3

We wrapped up section three with an assessment. In this video, we’ll revisit that assessment followed by the solutions to each exercise.

   •  Review the task of each exercise

   •  Review the solution to each task.

2
Query Rows of Data Using the BETWEEN Operator

We’ve already seen how to query specific data using the WHERE conditional clause. This video introduces an alternative to WHERE.

   •  Introduce the main topic and provide examples of the syntax

   •  Demonstrate a use case of BETWEEN using pgAdmin

3
Introduction to NOT BETWEEN

Now that we’ve learned how to use BETWEEN, we’ll learn how to produce the opposite of this expression with NOT BETWEEN.

   •  Introduce the main topic and provide examples of the syntax

   •  Demonstrate a use case of NOT BETWEEN using pgAdmin

4
Search for a Specified Pattern in a Column Using the LIKE Operator

As we learn new ways to set the condition for our result set, we’ll learn how to match for patterns using LIKE with the percent sign and wildcard operators.

   •  Introduce the main topic and provide examples of the syntax

   •  Demonstrate a use case of LIKE with wildcards using pgAdmin

5
Match a Value to a List of Values with the IN Operator

Now that we’ve learned pattern matching with the LIKE operator. It’s time to reach a new level of conditional querying using the IN operator to match a value against a list of values.

   •  Introduce the main topic and provide examples of the syntax

   •  Demonstrate a use case of IN with list of values

6
Assignment

This assignment will provide the opportunity to gain additional practice and validate knowledge of the concepts covered in section 4.

   •  Introduce the tasks for the assignment

   •  Look at the rules of the assignment

Use Mathematical Operations with PostgreSQL

1
Assignment Review from Day 4

We wrapped up section four with an assessment. In this video, we’ll revisit that assessment followed by the solutions to each exercise.

   •  Review the task of each exercise

   •  Review the solution to each task

2
Using MIN and MAX Functions

In order to understand some of the most advanced concepts later on in the course, students will need to learn some common aggregate functions such as MIN and MAX

   •  Introduce the main topic and provide examples of the syntax

   •  Demonstrate a use case of MIN & MAX using pgAdmin

3
Using MIN and MAX with the ROUND Function

Now that students have become familiar with MIN and MAX, we can introduce the ROUND function to help provide more contexts to PostgreSQL functions.

   •  Introduce the main topic and provide examples of the syntax

   •  Demonstrate a use case of MIN, MAX & ROUND using pgAdmin

4
Using the AVG and SUM Functions

As students expand their knowledge of PostgreSQL functions, this is a great opportunity to introduce the AVG and SUM functions.

   •  Introduce the main topic and provide examples of the syntax

   •  Demonstrate a use case of AVG and SUM in pgAdmin

5
Using AVG and SUM with the ROUND Function

In most cases, while using AVG and SUM, we’ll need to control how many decimal places we want returned. The solution for this is the ROUND function.

   •  Introduce the main topic and provide examples of the syntax

   •  Demonstrate a use case of AVG & SUM with ROUND in pgAdmin

6
Assignment

This assignment will provide the opportunity to gain additional practice and validate knowledge of the concepts covered in section five.

   •  Introduce the tasks for the assignment

   •  Look at the rules of the assignment

Combine Data from Two Tables Using Joins

1
Assignment Review from Day 5

We wrapped up section five with an assessment. In this video, we’ll revisit that assessment followed by the solutions to each exercise.

   •  Review the task of each exercise

   •  Review the solution to each task

2
Group Data with the GROUP BY Clause

As students advance their understanding of complex queries, it’s important to learn the GROUP BY clause to prepare for future videos in this section.

   •  Introduce the main topic and provide examples of the syntax

   •  Demonstrate a use case of GROUP BY using pgAdmin

3
The HAVING Clause

Students will need to know how to set conditions to filter the results of group rows created by the GROUP BY clause. The solution for this is the HAVING clause.

   •  Introduce the main topic and provide examples of the syntax

   •  Demonstrate a use case of HAVING using pgAdmin

4
PostgreSQL Aliases and the LIMIT Clause

As queries become more verbose, it’s important to learn methods to make queries more meaningful and easier to understand. One of the best solutions to this problem is PostgreSQL Aliases and the LIMIT clause.

   •  Introduce the main topic and provide examples of the syntax

   •  Demonstrate a use case of Aliases and LIMIT in pgAdmin

5
Exploring Inner Joins

It’s time for students to take their skills to the nest level so they can prepare to address real would problems by learning how to perform an INNER JOIN

   •  Introduce the main topic and provide examples of the syntax

   •  Demonstrate a use case of INNER JOIN in pgAdmin

6
Exploring Outer Joins

Now that students understand the importance and use case for an INNER JOIN clause, it’s important to understand how to implement OUTER JOINS.

   •  Introduce the main topic and provide examples of the syntax

   •  Demonstrate a use case of OUTER JOIN in pgAdmin

7
Assignment

This assignment will provide the opportunity to gain additional practice and validate knowledge of the concepts covered in section six.

   •  Introduce the tasks for the assignment

   •  Look at the rules of the assignment

PostgreSQL Datatypes and Table Constraints

1
Assignment Review from Day 6

We wrapped up Section 6 with an assessment. In this video, we’ll revisit that assessment followed by the solutions to each exercise.

   •  Review the task of each exercise

   •  Review the solution to each task

2
Common PostgreSQL Datatypes

As students begin learning the fundamentals of database administration, it’s important to understand the underlying datatypes that are used to create columns in database tables.

   •  Introduce the main topic and provide examples of the syntax

   •  Demonstrate an example of each datatype

3
Common PostgreSQL Constraints

As students continue their learning of database administration, it’s important to understand constraints and how they can be used to maintain the integrity of data.

   •  Introduce the main topic and provide examples

   •  Demonstrate use cases of each constraint

4
Advanced Administration of Your Database with ALTER TABLE

We will further build on previous concept taught in this section as students will learn how to perform power ALTER TABLE statements to change the structure of their database tables.

   •  Introduce the main topic and provide examples of the syntax

   •  Demonstrate a use case of different ALTER TABLE statements

5
Assignment

This assignment will provide the opportunity to gain additional practice and validate knowledge of the concepts covered in Section 7.

   •  Introduce the tasks for the assignment

   •  Look at the rules of the assignment

6
Assignment Solutions Review

We wrapped up Section 7 with an assessment. In this video, we’ll revisit that assessment followed by the solutions to each exercise.

   •  Review the task of each exercise

   •  Review the solution to each task

You can view and review the lecture materials indefinitely, like an on-demand channel.
Definitely! If you have an internet connection, courses on Udemy are available on any device at any time. If you don't have an internet connection, some instructors also let their students download course lectures. That's up to the instructor though, so make sure you get on their good side!

Be the first to add a review.

Please, login to leave a review
4bcf38da9ef552ecb19378b8446f6bcb
30-Day Money-Back Guarantee

Includes

4 hours on-demand video
Full lifetime access
Access on mobile and TV
Certificate of Completion