Beginner's Guide to PostgreSQL

Udemy

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> Who is the target audience?
  • Database beginners
  • Basic database users who want to understand and learn PostgreSQL

Requirements
  • Basic computer knowledge (no programming or database knowledge required)

What Will I Learn?
  • 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


Curriculum For This Course Expand All Collapse All 70 Lectures 05:58:04 + – Introduction 9 Lectures 39:44

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
An Introduction to the Course Preview 02:19

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

  • Tables
  • Columns
  • Rows
  • Fields
Database Overview Preview 05:15

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.

Identities in database tables Preview 02:45

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.

Normalization Preview 04:10

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

Relationships 07:00

Just a quick check up quiz

Relationship Quiz 3 questions

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
Relationship Types 05:47

Quick quiz to see if you've this one down!

Relationship Types Quiz 4 questions

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

Database Management Systems 03:14

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

PostgreSQL features 03:34

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

PostgreSQL Windows Installation Preview 05:40 + – Database Usage Basics 26 Lectures 02:35:18

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.

Connecting to Database Server with pgAdmin III 03:30

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

Configuring PostgreSQL to Accept External Connections 07:29

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.

Creating Databases 06:21

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

Creating Tables 06:59

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.

Inserting and viewing data in pgAdmin III 09:46

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.

DataTypes Intro 01:19

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.

Numeric DataTypes in PostgreSQL 10:58

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

Serial Datatypes For Row Ids 04:34

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.

Character Types 06:11

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

The Date Datatype 05:05

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

The time datatype 03:53

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.

Timestamps 07:28

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.

The boolean datatype 02:36

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.

NULL Values 05:05

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.

Requirements 02:23

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

Car Repair Shop DB Requirements 4 pages

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

Designing the repair shop database Pt1 08:06

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

Designing the repair shop database Pt2 04:27

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

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

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

Car Repair Shop DB Table and Column Definitions 3 pages

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.

Building the Database - Part 1 07:25

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.

Applying constraints to table columns 08:03

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.

Creating relationships between tables in pgAdmin III 05:08

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

Creating the remaining tables 12:34

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.

Users and Privileges 14:10

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.

Backup and Restore in PostgreSQL with pgAdmin III 09:06 + – SQL in PostgreSQL 34 Lectures 02:35:57

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

Introduction to SQL Preview 02:49

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.

The CREATE TABLE command 05:48

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.

The CREATE TABLE command with constraints PT 1 06:29

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.

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

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

Script: First Two Create Table commands 00:08

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

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

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.

The CREATE TABLE command with foreign keys PT 2 05:18 Scripts: Create Tables with Foreign keys 00:08

A couple of quiz style questions about the CREATE TABLE command

CREATE TABLE quiz 2 questions

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.

Drop Table 04:22

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.

The Alter Table command 12:48 Script: Drop And Alter Table commands 00:09

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 capita

Dates:
  • Free schedule
Course properties:
  • Free:
  • Paid:
  • Certificate:
  • MOOC:
  • Video:
  • Audio:
  • Email-course:
  • Language: English Gb

Reviews

No reviews yet. Want to be the first?

Register to leave a review

Show?id=n3eliycplgk&bids=695438
NVIDIA
More on this topic:
67874_1106_5 Practical SQL Reporting with SSRS
Get hands on with Microsoft SQL Server Reporting Services and learn how to create...
16610_a6a5_3 Online Courses - Anytime, Anywhere
Learn Node. js and Socket. io along with how to how to query Mysql through Node...
19615_2f71_3 Unity 3d Game Development - iOS, Android, & Web - Beginners
Want to get into making high quality games, but don't know where to start? This...
Small-icon.hover Introduction to Data Science
Join the data revolution. Companies are searching for data scientists. This...
98130_3ccf_16 Learn SQL in 6 days
Learn SQL and handle databases confidently
More from 'Udemy':
Show?id=n3eliycplgk&bids=323058 Thousands of online courses for only $15
Most courses are dropped to $15 flat price!
Banner_ruby Ruby on Rails Tutorial: Learn From Scratch
This post is part of our “Getting Started” series of free text tutorials on...
Show?id=n3eliycplgk&bids=323058 $10 Sitewide Deal
$10 Fixed Price across the site!
64422_c6dd_6 1 Hour HTML
Learn how to code in HTML in 1 hour. This class is set up for complete beginners...
72262_60dc_6 1 hour jQuery
Have you ever wanted to learn to code. This course will show you the basics...

© 2013-2019