Beginner's Guide to PostgreSQL
- 6 hours on-demand video
- 3 articles
- 14 downloadable resources
- Full lifetime access
- Access on mobile and TV
- Certificate of Completion
Get your team access to 4,000+ top Udemy courses anytime, anywhere.Try Udemy for Business
- 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
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
In this video, we'll talk about why we need databases and take a look at basic basic concepts:
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.
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.
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.
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.
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
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.
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.
This video is an overview of what will be covered in this third module.
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.
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 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.
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 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.
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.
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.
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.
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.
- Basic computer knowledge (no programming or database knowledge required)
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>
- Database beginners
- Basic database users who want to understand and learn PostgreSQL