Beginner's Guide to PostgreSQL

PostgreSQL Tutorial: What you need to know to get started with relational databases in PostgreSQL
3 reviews
TAUGHT BY
  • Miguel Alho Develeper and Owner of Miguel Alho - Multimedia

    I am currently running my own web-based software development company (mainly .NET based ) building HRIS (Human Resource Information Systems) software for HR teams. I've also been employed as a teacher to 7th and 8th graders in Tec. Ed. classes, and voluntarily accepting 12th grade internships of IT students through local schools.

    I am currently finishing a couple of web based projects for one of Sonae's human resources departments - Sonae being the largest private employer in Portugal, after having concluded a project for a Cape Verdian Bank (in colaboration with G333.Net).

    When possible, I work on internal R&D projects, namely our devolpment framework, a DSL for code generation, and product ideas. I also colaborate with various local companies and institutions, both professionaly and voluntarily, namely the Santa Casa da Misericórdia da Murtosa.

    My colleagues and I are experienced in developing costumized software, service and database solutions for businesses. We work with .Net, PostrgeSQL databases (or others as needed) , Ajax and Javascript based interfaces and our own internal framework for rapid development and maitenance.

WHAT'S INSIDE
  • Lifetime access to 67 lectures
  • 5+ hours of high quality content
  • A community of 300+ students learning together!
SHARE

Beginner's Guide to PostgreSQL

PostgreSQL Tutorial: What you need to know to get started with relational databases in PostgreSQL
3 reviews

HOW UDEMY WORKS?

Discover courses made by experts from around the world.

Take your courses with you and learn anytime, anywhere.

Learn and practice real-world skills and achieve your goals.

COURSE DESCRIPTION

PostgreSQL is an awesome open source relational database, that's fast and easy to use. We've used it here at my company in a few large projects with great performance.

In this beginner level PostgreSQL tutorial course I'll be going over all the basic concepts of relational databases and dive into how to use PostgreSQL. No previous database experience is required for this PostgreSQL tutorial course - all the basic concepts like tables and relations are covered. It's thorough and meant to help anyone starting out with databases.

The PostgreSQL tutorial course covers database concepts, database installation and connection, client software used to connect to and manage the database (pgAdminIII) and also the main SQL statements and how they work with PostgreSQL.

This PostgreSQL tutorial course is currently under construction, with close to 5 hours of material already available. Considering that, I'll be offering it at a discounted price. Once finished, it will be priced at $99.

</p>
    • Basic computer knowledge (no programming or database knowledge required)
    • Over 67 lectures and 6 hours of content!
    • Understand why databases are useful
    • Understand how relational databases work
    • Learn how to install and setup PostgreSQL
    • Learn how to create databases and database structures
    • Learn how to insert and manipulate data in a PostgreSQL database
    • Learn how to write SQL statements for PostgreSQL
    • Database beginners
    • Basic database users who want to understand and learn PostgreSQL

THE UDEMY GUARANTEE

30 day money back guarantee
Lifetime access
Available on Desktop, iOs and Android
Certificate of completion

CURRICULUM

  • SECTION 1:
    Introduction
  • 1
    An Introduction to the Course
    02:19

    In this video i talk about this course and how it is structures. The course is divided in to 3 modules -

    • An introduction to databases and PostgreSQL
    • How to create and manipulate databases
    • SQL Primer in PostgreSQL
  • 2
    Database Overview
    05:15

    In this video, we'll talk about why we need databases and take a look at basic basic concepts:

    • Tables
    • Columns
    • Rows
    • Fields
  • 3
    Identities in database tables
    02:45

    In this video we'll look at Identity columns in our database tables, and how we'll use them to uniquely identify each record in a table.

  • 4
    Normalization
    04:10

    Normalization is a process in database construction. We use it to extract repeated information in our databases' tables, which avoids maitenance errors. A well normalized database is usually better organized and more maintainable. This will also set the bases for relationships in our database.

  • 5
    Relationships
    07:00

    In this video, we look at what relationships between tables are and why they are important in a database.

  • 6
    Relationship Types
    05:47

    In this video we'll have a look at the 3 main types of relationships we can create between tables in a relational database:

    • one to one
    • one to many
    • many to many
  • 7
    Database Management Systems
    03:14

    In this video, I present a quick overview of database management systems and a high level view of how they work.

  • 8
    PostgreSQL features
    03:34

    In this video i give a quick review of PostgreSQL features and some of the limits associated with it.

  • 9
    PostgreSQL Windows Installation
    05:40

    Learn step-by-step how to install PostgreSQL on a Windows machine

  • SECTION 2:
    Database Usage Basics
  • 10
    Connecting to Database Server with pgAdmin III
    03:30

    In sthis video, we'll start pgAdmin III - the client application we'll use to connect to, manage and manipulate our databases, and connect to our local database server, that we installed in the previous step.

    When you open pgAdminIII for the first time, it should have you're local server in the servers list. In this video, I'll show you how to register a server in the list, in case your local server is not available or if you wish to connect to a remote server.

  • 11
    Configuring PostgreSQL to Accept External Connections
    07:29

    By default, the server installation won't allow you to connect to the databases from external computers - only the local machine is allowed.

    To allow other computers to connect, you need to edit the pg_hba.conf, and open a firewall port. This video shows you how.

    Note:

    Around minute 4, I mention the CIDR notation. For more details and a cheat sheet for CIDR netmask notation, check out: http://www.oav.net/mirrors/cidr.html

  • 12
    Creating Databases
    06:21

    In this vídeo, I'll show you how to create a database through pgAdmin III. The process only requires you give your new database a name, but there are other options that are available and usefull, and I analyse them in this video.

  • 13
    Creating Tables
    06:59

    In this video we'll look at how tables are created in pgAdmin III and the options available for the table.

  • 14
    Inserting and viewing data in pgAdmin III
    09:46

    In this video, I show you how to add, edit and delete records in a table, and also how to use the table's ordering and filtering options, within pgAdmin III.

  • 15
    DataTypes Intro
    01:19

    PostgreSQL suports multiple data types for its columns and choosing the right datatype for each column is importante, both for correct data representation and database optimization. This lesson is just a short video to set the stage for the next few clips.

  • 16
    Numeric DataTypes in PostgreSQL
    10:58

    Postgres offers a series of datatypes to eficiently store numbers in diferent  formats and ranges. In this video I review the numeric types and show some examples of how rounding decimal numbers works, and some errors that may occur.

  • 17
    Serial Datatypes For Row Ids
    04:34

    Here We talk about how the serial and bigserial types work in creating record IDs in our tables.

  • 18
    Character Types
    06:11

    In Postgres, 3 datatypes are available to store character-based information: char(n), varchar(n) and text. In this video I go over their diferences.

  • 19
    The Date Datatype
    05:05

    There are a few datatypes we can use for date and time information. In this video we look at the date type

  • 20
    The time datatype
    03:53

    We can use the time type to store time information, without dates. This video shows how to write the times into a tables field and some variations of the notation

  • 21
    Timestamps
    07:28

    Uou can combine date and time in the same field using the timestamp datatype. It also allows the storage of timezone data, essential to any aplication that crosses timezones.

  • 22
    The boolean datatype
    02:36

    The boolean datatype allows us to store eaither a true or false value. It is very comun to use it to store data that answers true/false or yes/no questions.

  • 23
    NULL Values
    05:05

    The NULL value allows us to meaningfully indicate that a certain field's data is unkown, and a better option to inserting fake or misleading default values.

  • 24
    Requirements
    02:23

    The requirments gathering process is important in database construction since it allows for a better understanding of the problem that our databse will solve. In this video, we present requirements for our car repair shop database, to be used in the next few videos.

  • 25
    Car Repair Shop DB Requirements
    4 slides

    This PDF contains the set of slides used to define the requirements for our database. 

  • 26
    Designing the repair shop database Pt1
    08:06

    In this video, we go through the client part of requirements and design our database tables. We transform requirements into tables, columns and relationships.

  • 27
    Designing the repair shop database Pt2
    04:27

    In this second part, we design the tables to store data for our repair shop's interventions on vehicles. 

  • 28
    Designing the repair shop database Pt3 - choosing datatypes
    02:42

    In this video we choose the datatypes for our columns, based on what they will contain.

  • 29
    Car Repair Shop DB Table and Column Definitions
    3 slides

    This PDF contains the table definitions from the last set of videos. Use it to follow along the database creation in pgAdmin III

  • 30
    Building the Database - Part 1
    07:25

    When creating tables, we need to start off with those that don't have any dependencies - that is, that have no foreign keys to other tables. Creating these first avoids any problems defining the foreign key constraints on the tables.

  • 31
    Applying constraints to table columns
    08:03

    Constraints are rules we apply to our tables' columns in order to garantee that certain rules are followed when we enter data. Will take a look at the constraints Postgres allows in this video.

  • 32
    Creating relationships between tables in pgAdmin III
    05:08

    The next set of tables we create will have foreign keys so we need to create the table and while doing so, establish the relationship with the table that we will be referencing.

  • 33
    Creating the remaining tables
    12:34

    In this video, we finish creating the car repair shop database by building the remaining three tables.

  • 34
    Users and Privileges
    14:10

    We can create new users (called roles, within PG) to control how users access the database. There are a few steps you need to take in Postgres to correctly create new users. Ins this lesson, I cover those steps so that a new user can connect to a specific database and access it with the necessary privileges.

  • 35
    Backup and Restore in PostgreSQL with pgAdmin III
    09:06

    Database backups are very important in avoiding data loss due to system failures, harddrive failures and even human error. Postgres comes with a couple of aplications that allow us to backup a database (pg_dump) and restore a backup (pg_restore). We can access and manage the options through pgAdmin III. In this video I show you how to backup and restore a database.

  • SECTION 3:
    SQL in PostgreSQL
  • 36
    Introduction to SQL
    02:49

    This video is an overview of what will be covered in this third module.

  • 37
    The CREATE TABLE command
    05:48

    In this video we look at the CREATE TABLE command, and use it to build a very simple version of the carBrand table. No constraints are aplied to the table - it is ment to understande the basic sintaxe and structure of the statement.

  • 38
    The CREATE TABLE command with constraints PT 1
    06:29

    In this video, we will recreate the carBrand table, but we'll add the PRIMARY KEY constraint to the id column, and NOT NULL and UNIQUE constraints to the name column in the SQL statement.

    We'll also create the client table, and add a default value to a column in that table.

  • 39
    The CREATE TABLE command with constraints pt.2
    03:56

    In this video , we build the remaining two tables with no foreign keys in the RepairShop database.

    I'll show you how to execute multiple statements in the same run, add check constraints, and also look at an anlternative notation for the column and constraint organization of the CREATE TABLE command.

  • 40
    The CREATE TABLE command with foreign keys PT 1
    04:01

    In this table we look at creating tables with foreign key constraints and the notation variations associated to it.

  • 41
    The CREATE TABLE command with foreign keys PT 2
    05:18

    In this video we create the rest of the tables that have foreign key references. I also introduce comments and selective execution of the script.

  • 42
    Drop Table
    04:22

    In thsi video we look at the DROP TABLE and DROP TABLE... CASCADE command and also how to save and recall queries in the query editor.

  • 43
    The Alter Table command
    12:48

    The ALTER TABLE command allows us to edit our data structure: we can create or drop new columns, alter datatpyes and constraints or even modify existing constraints.

    In this video we create a blank table (partAdded) that is missing in our database, and create the missing columns using the ALTER TABLE command.

    ERRATA:

    I made a couple of mistakes in this clip. First, the third colum created in the video is "vehicle_id" but should have been "intervention_id" and reference the intervention table. Second, the check constraint on the cost column should have been ">=". The error are highlçighted in the video.

  • 44
    Insert Into SQL Command
    09:30

    The INSERT INTO SQL Command allows us to insert rows of data into our database tables. This video shows you how to build basic insert statements that insert one or more rows into the table.

    Note: When I refer to quotation marks, I sometimes don't indicate if they are single or double. So, for table names or column names that have capital letters, you need to use double quotes, while for string values, you use single quotes.

  • 45
    Insert scripts used in the previous video
    636 B

    You can use the SQL statements in this file to quickly execute the steps I take in the previous video.

  • 46
    The Update Statement
    15:30
  • 47
    Update Script file
    7.27 KB

    Includes The Insert statement to add clients to the table and the update statements used in the video

  • 48
    The DELETE FROM Statement
    05:09

    In this lesson, I talk about the DELETE FROM statement and show how to use it to delete specific rows or all the rows from a table.

  • 49
    Deletes with cascades
    08:02

    Typicly, deleting data from a table only afects the data in that tabel. When dealing with tables that are references for foreign keys, you can't delete data without deleting all the dependant elements.

    In this video, we see how to build delete statements to handle multiple tables with dependant data and how to use the ON DELETE CASCADE clause in foreign key constraints.

  • 50
    Script: Delete from commands for the previous videos
    974 B

    The file contains the script used in the delete from command videos.

  • 51
    The TRUNCATE command
    05:08

    The TRUNCATE command allows us to quickly delete all the rows in a table and CASCADE the deletes to related tables. We can also use a "Restart Identity" clause to reset the sequence that sets the primary key's value.

  • 52
    Truncate Script
    718 B

    The script used in the previous TRUNCATE command video.

  • 53
    Filling the database with data
    04:04

    Before we procede to hte next set of videos, where we will be executing SELECT commands, It is important that we add data to the database. I've provided a script file that adds data, which you can download from the next lesson. In this video, I show you how to use the file to insert data into the database.

  • 54
    Full Db Script
    204.97 KB

    Before we procede to the next set of lessons, it is important to insert data into the database to be able to create meaningfull SELECT statements. Use the provided script file to add data to the database.

  • 55
    The SELECT command
    03:30

    This video is the first in a series of clips whare we work on selecting data. We start by analysing the basic components of a query.

  • 56
    pgAdmin III's Graphical Query Builder
    02:39

    We have a look at the grafical query builder in pgAdmin that lets you create queries in a drag n' drop style of editing.

  • 57
    The WHERE clause (PT I)
    04:57

    The WHERE clause in the select statement allows us to filter the data that the query returns, generating precise result sets. In this video we look at how we can create expressions in the where clause.

  • 58
    The WHERE clause (PT 2) - BETWEEN and IN
    04:36

    In this video we look at the BETWEEN and IN operatores and their use in WHERE clauses to filter data through ranges or sets of values.

  • 59
    The WHERE clause (PT 3) - IS [NOT] NULL
    03:15

    We can't use the equal sign or not equal sign in our statements to compare with NULL since NULL represents no data. To use NULLs in a statement correctly We need to use the IS or IS NOT keywords. 

  • 60
    The WHERE clause (PT 4) - LIKE and ILIKE
    09:32

    Pattern matching is an important form of filtiring, especially when working with strings. Being able to match a part of a string can become a simple yet powerful mechanism for search. In this video we look at the various forms of the LIKE operator in the WHERE clause.

  • 61
    The WHERE clause - using the graphical query builder
    10:38

    We can build queries that have a WHERE clause, using the graphical query builder. In this video I show you how to compose those queires using diferent types of criterias.

  • 62
    The WHERE Clause Command Script
    2.91 KB

    This file contains the various SELECT statements used in the previous vídeos.

  • 63
    The LIMIT and OFFSET clauses
    05:23

    LIMIT and OFFSET allow us to create a paging mecanism in our queries - LIMIT limits the number of rows returned, while OFFSET allows us to skip rows from the beginning of the full set. In this video I'll show you some examples of how this is implemented in a SELECT statement.

  • 64
    The ORDER BY clause
    06:57

    The ORDER BY clause allows us to order the results by a set of columns. Orderingcan be ascending (ASC) or descending (DESC), and some care must be taken when handling NULL values. In this video, I show some examples of how to use the ORDER BY clause in select statements.

  • 65
    Order By commands
    766 B

    This file has the SQL statements used in the Order by video, to help you follow allong and test the commands.

  • 66
    Joins tables in a query through relationships
    Upcoming
    ...
  • 67
    SQL functions - group clause
    Upcoming
    ...

UDEMY BY THE NUMBERS

5,200,000
Hours of video content
19,000,000
Course Enrollments
5,800,000
Students

RATING

  • 2
  • 1
  • 0
  • 0
  • 0
AVERAGE RATING
NUMBER OF RATINGS
3

REVIEWS

  • Michael Jones

    The course does what the title states. It covers the basics in Postgresql and relates these to both gui driven queries and writing the same in SQL. The lectures include the lecturer typing in teh commands concerned. In this way you get a feel for both Postgresql' pgamin3 and the syntax required.

  • 30 day money back guarantee!
  • Lifetime Access. No Limits!
  • Mobile Accessibility
  • Certificate of Completion