Skip to main content

pelatihan.orgLearn. Practice. Grow.

Sign in Sign up

pelatihan.org

Learn web development with a clear roadmap, code practice, and quizzes with instant feedback.

Catalog Sign in Subscription

PostgreSQL Curriculum: 4 Chapters, 42 Lessons — pelatihan.org

Home/Catalog/PostgreSQL/Curriculum

Curriculum

PostgreSQL Curriculum

Learn PostgreSQL online with 42 structured lessons, code examples, and 210 quiz questions.

42 lessons 4 chapters 210 questions

Summaries and key points are free to preview. Full lessons, code examples, and quizzes require a subscription.

01

PostgreSQL Tutorial

5 lessons

01PostgreSQL HomeThis page is the entrance to the PostgreSQL tutorial, explaining what PostgreSQL is and what you'll learn in this series of materials. PostgreSQL is a relational database…

02PostgreSQL IntroPostgreSQL grew out of the Ingres project at the University of California, Berkeley, and is now one of the most popular open source RDBMS in the world. Its object-relational…

03PostgreSQL InstallPostgreSQL can be installed via official installers on Windows, system packages on Linux, or brew on macOS. During the installation process you will be asked to specify the port,…

04PostgreSQL Get StartedGet Started page guides your first step: create a database, connect to it, then create a table and enter the data. This thread becomes a basic pattern that is used repeatedly…

05PostgreSQL pgAdmin 4PgAdmin 4 is an official graphics application for managing PostgreSQL servers, available as both desktop applications and servers that are opened via a browser. With pgAdmin you…

02

Database

10 lessons

01PostgreSQL CREATE TABLECREATE TABLE is used to define the structure of a new table as a list of columns and its data types. In addition to the data type, you can add constraints like PRIMARY KEY, NOT…02PostgreSQL INSERT INTOINSERT INTO adds one or more new lines to the table. You name the column and then list the values that are aligned with it; the unnamed column will receive either DEFAULT or…03PostgreSQL Fetch DataFetch Data discusses how to read data from a table using the SELECT command. You can choose the entire column with an asterisk or just the specific column that you need.…04PostgreSQL ADD COLUMNWhen the structure of the table needs to evolve, the alter table... ADD Columns are used to add new columns to an existing table. You can instantly specify the data type,…05PostgreSQL UPDATEThe UPDATE command changes the value of the column on a row that meets certain conditions. The most important thing to note is the WHERE clause, because without WHERE all the…06PostgreSQL ALTER COLUMNALTER Columns are used to change the definition of an existing column, such as data type, default value, or the need to fill. PostgreSQL even converts values automatically when…07PostgreSQL DROP COLUMNALTER TABLE ... DROP COLUMN permanently deletes a column and all of its data. PostgreSQL provides IF EXISTS so that the command does not error when columns are not found, as well…08PostgreSQL DELETEThe DELETE command removes the row from the table based on the given WHERE condition. Without WHERE, the entire row of the table will be deleted, so always include the limiting…09PostgreSQL DROP TABLEDROP TABLE permanently deletes a table with all its structure and data. The IF EXISTS option is available to avoid errors as well as the CASCADE option to delete tables-dependent…10Create Demo DatabaseCreating a training-specific demo database is a wise step before exploring SQL commands. You're going to create a separate database, define some interrelated tables, and then…

03

PostgreSQL Syntax

27 lessons

01PostgreSQL OperatorsOperators in PostgreSQL include the arithmetic, comparison, and logic operators used to construct the query expression and condition. Two-pipe combined text operators as well as…02PostgreSQL SELECTSELECT is the core command to read data from one or more tables. You specify what columns are shown in the column list, then you specify the data source in the FROM clause. The…03PostgreSQL SELECT DISTINCTSELECT DISTINCT displays only a unique value combination so that duplicate rows do not appear on the result. DISTINCT is applicable to all selected columns at once, not per…04PostgreSQL WHEREThe WHERE clause filters the line so that only those who meet certain conditions are included in the processing or display. In it you can use the comparison operator, combining…05PostgreSQL ORDER BYORDER BY sorts the query results based on one or more columns, either up or down. The order can be based on columns that aren't even shown, and even based on the column number in…06PostgreSQL LIMITLIMIT limits the number of rows returned by the query, which is very useful for viewing data clips or building a page view. Packed with OFFSET, you can skip a number of lines to…07PostgreSQL MIN and MAXThe MIN and MAX aggregate functions take the smallest and largest values of a column. Both ignore the value of NULL and can be used on numbers, text, dates, or timestamps. When…08PostgreSQL COUNTCOUNT is an aggregate function for calculating the number of rows or the number of values in a column. The COUNT(*) variants count all rows, whereas COUNT(column) only counts…09PostgreSQL SUMThe SUM function sums all the numerical values in a column and is often used to calculate the total transaction or inventory value. The value NULL is passed so that the number is…10PostgreSQL AVGAVG calculates the average value of a numerical column by dividing the total number by the number of non-P0 values. The result is often a fraction, so it needs to be rounded or…11PostgreSQL LIKEThe LIKE operator is used to compare a text column against a pattern with a wildcard percentage for zero or more characters and a bottom line for exactly one character. In…12PostgreSQL INThe IN operator checks whether a value is included in a given value list or in the result of a subquery. Logically IN is equivalent to the OR condition series, but it is more…13PostgreSQL BETWEENThe Between operator checks whether a value is within a range, including both of its limits. Between is always written with AND to separate the lower boundary and the upper…14PostgreSQL ASUS keywords give aliases to the result column and to the tables used in the query. The alias makes the results of the calculations easier to read and shorten the spelling of the…15PostgreSQL JoinsJOIN is a way of combining rows of two or more tables based on the relationships between columns, usually the main key and the foreign key. With join, the data stored separately…16PostgreSQL INNER JOININNER JOIN only returns the row that has a match on both tables according to the join condition. So the line without a pair on either side will be deviated from the result.…17PostgreSQL LEFT JOINLEFT JOIN retains the entire row from the left table, although it does not find a match in the right table. The column in the right table will contain NULL when the pair is not…18PostgreSQL RIGHT JOINRIGHT JOIN is the opposite of LEFT JOIN because it maintains the entire row on the right table. When the left table row has no pair, the column is filled. Logically it is the…19PostgreSQL FULL JOINFULL JOIN or FULL OUTER JOIN retains the entire row of both tables, whether matching or not. The value that has no pair is filled on the opposite side. This pattern is very…20PostgreSQL CROSS JOINCROSS JOIN results in a Cartesian time result of any possible pair of rows from two tables without any join condition. The number of rows results is a multiple of the number of…21PostgreSQL UNIONUNION brings together the results of two or more SELECT queries into one combined row list. The main requirement is the same number of columns and the same data type as the…22PostgreSQL GROUP BYGROUP BY groups rows that have the same column value so that the aggregate function is calculated separately per group. Each group produces one line of results, such as a number…23PostgreSQL HAVINGHAVING filters the results of groupings after GROUP BY is run, for example only displaying groups with numbers above a certain threshold. The key difference with WHERE is the…24PostgreSQL EXISTSEXISTS is a predicate operator that checks whether a subquery returns at least one line. If there is a line, the result is TRUE; if not, FALSE. Varian NOT EXIST is used to find…25PostgreSQL ANYANY operator compares a value against a set of subquery results on the condition that at least one value comparison is true. Form = ANY is equal to IN, whereas > ANY means…26PostgreSQL ALLThe ALL operator demands a value that satisfies the comparison of all the results of the subquery at once. For example > ALL means greater than the greatest value of the subquery…27PostgreSQL CASEThe CASE expression allows you to write a case-by-case condition in SQL, similar to a switch in a programming language. PostgreSQL provides a simple form of CASE that compares…

All-course subscription

$5.00 / 30 days

Access every course, lesson, and quiz. Progress is saved automatically.

This curriculum is free to browse. Activate a subscription to unlock full lessons, code examples, and quizzes.

Activate subscription Back to course