Узнайте о подзапросах, CTE, индексах, PL / SQL, PL / pgSQL, триггерах, курсорах, разделах, представлениях, JSON, оконных функциях... Этот всеобъемлющий курс научит вас всему, что касается PostgreSQL, для использования в вашем следующем большом проекте!
Я разработал этот курс от поэтапного перехода от базовых к продвинутым темам.
Вот неполный список некоторых тем, которые рассматриваются в более чем 50 разделах и 60+ часах этого курса:
Применяйте мощные команды SQL для хранения, обновления и извлечения информации;
Пошаговое руководство для выполнения простых и сложных SQL-запросов;
Как создать базу данных с нуля, Создание и изменение таблиц;
Использование UPSERT для INSERT и UPDATE одновременно;
Запрос данных с помощью ORDER BY, логических операторов и фильтров;
Сможем разобраться в различных типах данных PostgreSQL на примерах;
Преобразование типов данных с помощью CAST и функций преобразования;
Создавайте свои собственные определяемые пользователем типы данных;
Изучите ограничения таблиц и столбцов, создайте несколько составных ограничений, посмотрите операции целостности данных;
Создавайте свои собственные пользовательские последовательности;
Анализ данных с помощью запросов с использованием строковых функций;
Агрегатные данные с агрегатными функциями;
Типы данных даты и времени, интервалы и часовые пояса;
Группировка данных с помощью GROUP BY, условный фильтр с HAVING;
Объединение нескольких таблиц с помощью различных методов объединения;
Объединение запросов вместе с UNION, INTERSECT и EXCEPT;
Оптимизация производительности запросов с помощью индексов;
Построение массивов и диапазонов, запрос данных массива с условными фильтрами;
Хранение данных с помощью объектов JSONB и индексация;
Создание общих табличных выражений (CTE) и рекурсивных запросов CTE;
Объединение данных в наборы групп;
Простые и продвинутые Windows Functions;
Использование регулярных выражений для текстовых шаблонов;
Мощный текстовый поиск в PostgreSQL;
Создание и изучение методов разделения таблиц;
Серверное программирование на SQL и PL / pqSQL;
Создания Stores Procedures;
Расширение эхосистемы PostgreSQL с помощью кастомных индексов и агрегатных функций;
Создание и выполнение триггеров;
Использование курсоров;
Команды утилиты pqsql;
Создание Crosstab reports;
Создание базы данных для хранения глобальных языковых данных - Интернационализация;
Выполнение запросов с транзакциями PostgreSQL;
Создание и управление безопасностью PostgreSQL;
Все о внутреннем устройстве PostgreSQL, о том, как хранятся данные, и многом другом. ;
Выполнение сотен запросов к образцам баз данных, таким как HR, Northwind, Stock Markets, Word Trades database.
Посмотреть больше
Это пробный урок. Оформите подписку, чтобы получить доступ ко всем материалам курса. Премиум
Ограничение времени просмотра
Вы можете просматривать пробный урок только 10 минут. Получите полный доступ, чтобы смотреть без ограничений.
Creating a Junction table with movies and actors tables
Урок 19.
00:05:51
Install sample data for 'movies' database
Урок 20.
00:05:37
Using pgAdmin - Create and modify a table
Урок 21.
00:05:29
Using pgAdmin - View table structure, and create column
Урок 22.
00:06:45
Using pgAdmin - Rename, delete and change the data type of a column
Урок 23.
00:02:49
Deleting tables from a database
Урок 24.
00:04:47
Insert a data into table
Урок 25.
00:02:36
Insert multiple records into a table
Урок 26.
00:02:02
Insert a data that had quotes
Урок 27.
00:02:52
Use RETURNING to get info on added rows
Урок 28.
00:03:26
Update data in a table
Урок 29.
00:02:14
Updating a row and returning the updated row
Урок 30.
00:03:11
Updating all records in a table
Урок 31.
00:03:08
Delete data from a table
Урок 32.
00:10:00
Using UPSERT
Урок 33.
00:06:32
Select all data from a table
Урок 34.
00:03:44
Selecting specific columns from a table
Урок 35.
00:09:24
Adding Aliases to columns in a table
Урок 36.
00:03:16
Using SELECT statement for expressions
Урок 37.
00:07:55
Using ORDER BY to sort records
Урок 38.
00:03:15
Using ORDER BY with alias column name
Урок 39.
00:03:09
Using ORDER BY to sort rows by expressions
Урок 40.
00:03:20
Using ORDER BY with column name or column number
Урок 41.
00:05:54
Using ORDER BY with NULL values
Урок 42.
00:05:28
Using DISTINCT for selecting distinct values
Урок 43.
00:02:20
Comparison, Logical and Arithmetic operators
Урок 44.
00:07:27
AND operator
Урок 45.
00:03:24
OR operator
Урок 46.
00:04:44
Combining AND, OR operators
Урок 47.
00:02:49
What goes before and after WHERE clause
Урок 48.
00:01:54
Execution order with AND, OR operators
Урок 49.
00:02:21
Can we use column aliases with WHERE?
Урок 50.
00:01:34
Order of execution of WHERE, SELECT and ORDER BY clauses
Урок 51.
00:16:16
Using Logical operators
Урок 52.
00:16:52
Using LIMIT and OFFSET
Урок 53.
00:09:47
Using FETCH
Урок 54.
00:10:15
Using IN, NOT IN
Урок 55.
00:11:32
Using BETWEEN and NOT BETWEEN
Урок 56.
00:17:19
Using LIKE and ILIKE
Урок 57.
00:12:37
Using IS NULL and IS NOT NULL keywords
Урок 58.
00:08:21
Concatenation techniques
Урок 59.
00:04:37
Concatenation with ||, CONCAT and CONCAT_WS
Урок 60.
00:13:15
Boolean
Урок 61.
00:18:20
CHAR, VARCHAR and TEXT
Урок 62.
00:10:15
NUMERIC
Урок 63.
00:09:54
DECIMALS
Урок 64.
00:05:52
Selecting Numbers data types
Урок 65.
00:03:44
Date/Time data types
Урок 66.
00:06:20
DATE
Урок 67.
00:14:58
TIME
Урок 68.
00:12:50
TIMESTAMP and TIMESTAMPTZ
Урок 69.
00:11:39
UUID
Урок 70.
00:07:08
Array
Урок 71.
00:08:01
hstore
Урок 72.
00:12:30
JSON
Урок 73.
00:14:01
Network Addresses
Урок 74.
00:04:39
Creating sample database 'mydata', adding columns
Урок 75.
00:09:00
Modify Table Structures, Add/Modify Columns
Урок 76.
00:10:28
Add constraints to columns
Урок 77.
00:05:47
What is a data type conversion
Урок 78.
00:11:34
Using CAST for data conversions
Урок 79.
00:04:13
Implicit to Explicit conversions
Урок 80.
00:07:16
Table data conversion
Урок 81.
00:08:07
to_char
Урок 82.
00:05:19
to_number
Урок 83.
00:05:34
to_date
Урок 84.
00:04:48
to_timestamp
Урок 85.
00:06:46
CREATE DOMAIN - Create a DOMAIN data type, create an address
Урок 86.
00:02:17
CREATE DOMAIN - Create a data type for a positive number
Урок 87.
00:03:01
CREATE DOMAIN - Create a postal code validation data type
Урок 88.
00:03:46
CREATE DOMAIN - Create a domain data type for an email validation
Урок 89.
00:04:36
CREATE DOMAIN - Create a Enum or Set of values domain data type
Урок 90.
00:02:18
CREATE DOMAIN - Get the list of all DOMAIN data types
Урок 91.
00:04:45
CREATE DOMAIN - How to drop a domain data type
Урок 92.
00:06:45
CREATE TYPE - Create a composite address object
Урок 93.
00:04:34
CREATE TYPE - Create a composite inventory_item data type
Урок 94.
00:04:34
CREATE TYPE - Create an ENUM data type and see how to drop a data type
Урок 95.
00:04:18
ALTER TYPE - Alter a composite data type, change schema and more..
Урок 96.
00:03:23
ALTER TYPE - Alter an ENUM data type
Урок 97.
00:08:14
Update an ENUM data in production server
Урок 98.
00:03:40
An ENUM with a DEFAULT value in a table
Урок 99.
00:02:00
Create a type if not exists using a PL/pgSQL function
Урок 100.
00:04:17
Introduction to constraints
Урок 101.
00:11:31
NOT NULL constraint
Урок 102.
00:15:18
UNIQUE constraint
Урок 103.
00:05:35
DEFAULT constraint
Урок 104.
00:21:45
PRIMARY KEY Constraints
Урок 105.
00:13:49
PRIMARY KEY Constraints on multiple columns
Урок 106.
00:06:13
FOREIGN KEY Constraints
Урок 107.
00:07:43
Tables without foreign key constraints
Урок 108.
00:07:50
Creating foreign key constraints
Урок 109.
00:07:45
Foreign keys maintains referential data integrity
Урок 110.
00:02:14
Drop a constraint
Урок 111.
00:03:06
Add or update foreign key constraint on existing table
Урок 112.
00:01:33
CHECK constraint - An Introduction
Урок 113.
00:10:13
CHECK constraint - Add to new table
Урок 114.
00:10:34
CHECK constraint - Add, Rename, Drop on existing table
Урок 115.
00:07:21
Create a sequence, advance a sequence, get current value, set value
Урок 116.
00:04:19
Restart, rename a sequence, and use pgAdmin to alter a sequence
Урок 117.
00:03:17
Create a sequence with START WITH, INCREMENT, MINVALUE and MAXVALUE
Урок 118.
00:02:43
Create a sequence using a specific data type
Урок 119.
00:04:45
Creating a descending sequence, and CYCLE sequence
Урок 120.
00:01:29
Delete a sequence
Урок 121.
00:08:54
Attach a sequence to a table column
Урок 122.
00:01:27
List all sequences in a database
Урок 123.
00:04:16
Share one sequence between two tables
Урок 124.
00:07:51
Create an alphanumeric sequence
Урок 125.
00:04:38
UPPER, LOWER and INITCAP
Урок 126.
00:08:08
LEFT and RIGHT
Урок 127.
00:02:09
REVERSE
Урок 128.
00:06:22
SPLIT_PART
Урок 129.
00:06:39
TRIM, BTRIM, LTRIM and RTRIM
Урок 130.
00:05:54
LPAD and RPAD
Урок 131.
00:03:53
LENGTH
Урок 132.
00:03:20
POSITION
Урок 133.
00:04:56
STRPOS
Урок 134.
00:03:52
SUBSTRING
Урок 135.
00:02:29
REPEAT
Урок 136.
00:04:10
REPLACE
Урок 137.
00:07:36
Counting results via COUNT function
Урок 138.
00:09:13
Sum with SUM function
Урок 139.
00:13:33
MIN and MAX functions
Урок 140.
00:05:09
GREATEST AND LEAST functions
Урок 141.
00:12:53
Average with AVG function
Урок 142.
00:11:07
Combining Columns using Mathematical operators
Урок 143.
00:09:39
Datetimes data types
Урок 144.
00:03:08
System Month Date settings
Урок 145.
00:06:10
Time of day formats and inputs
Урок 146.
00:11:19
Strings to Dates conversions
Урок 147.
00:09:44
Using TO_TIMESTAMP function
Урок 148.
00:10:34
Formatting Dates
Урок 149.
00:06:29
Date construction functions
Урок 150.
00:06:35
Using MAKE_INTERVAL function
Урок 151.
00:09:37
Using MAKE_TIMESTAMPTZ function
Урок 152.
00:05:56
Date Value Extractors functions
Урок 153.
00:09:25
Using math operators with dates
Урок 154.
00:04:04
OVERLAPS Operator
Урок 155.
00:05:02
Date / Time Functions
Урок 156.
00:04:24
PostgreSQL Date / Time Functions
Урок 157.
00:03:38
AGE function
Урок 158.
00:01:19
CURRENT_DATE function
Урок 159.
00:03:52
CURRENT_TIME function
Урок 160.
00:07:03
Date accuracy with EPOCH
Урок 161.
00:08:15
Using Date, time, timestamp in tables
Урок 162.
00:03:30
View and set timezones
Урок 163.
00:10:43
How to handle timezones
Урок 164.
00:10:44
date_part function
Урок 165.
00:08:58
date_trunc function
Урок 166.
00:11:55
Using GROUP BY
Урок 167.
00:14:46
Using GROUP BY with multiple columns, ORDER BY
Урок 168.
00:01:34
Order of execution in GROUP BY clause
Урок 169.
00:11:38
Using HAVING
Урок 170.
00:01:07
Order of execution in HAVING clause
Урок 171.
00:05:40
HAVING vs WHERE
Урок 172.
00:10:10
Handling NULL values with GROUP BY
Урок 173.
00:20:19
INNER joins
Урок 174.
00:08:56
INNER joins with USING
Урок 175.
00:10:19
INNER joins with filter data Part 1
Урок 176.
00:03:59
INNER joins with filter data Part 2
Урок 177.
00:05:29
INNER joins with filter data Part 3
Урок 178.
00:05:12
INNER joins with different data type columns
Урок 179.
00:09:34
LEFT joins Part 1
Урок 180.
00:07:28
LEFT joins Part 2
Урок 181.
00:08:02
LEFT joins Part 3
Урок 182.
00:05:50
LEFT joins Part 4
Урок 183.
00:09:44
RIGHT joins
Урок 184.
00:03:15
RIGHT joins Part 2
Урок 185.
00:05:05
FULL Joins
Урок 186.
00:09:05
Joining multiple tables
Урок 187.
00:08:30
Self Joins Part 1
Урок 188.
00:02:59
Self Joins Part 2
Урок 189.
00:10:35
CROSS Joins
Урок 190.
00:05:24
Natural Joins Part 1
Урок 191.
00:02:15
Natural Joins Part 2
Урок 192.
00:13:44
Append tables with different columns
Урок 193.
00:11:06
Combine results sets with UNION
Урок 194.
00:10:27
UNION with filters and conditions
Урок 195.
00:03:29
UNION tables with different number of columns
Урок 196.
00:04:27
INTERSECT with tables
Урок 197.
00:05:49
EXCEPT with tables
Урок 198.
00:06:09
What is a Schema?
Урок 199.
00:07:25
Schema Operations (Add/Alter/Delete schemas)
Урок 200.
00:06:22
Schema Hierarchy
Урок 201.
00:02:54
Move a table to a new schema
Урок 202.
00:14:11
Schema search path
Урок 203.
00:02:08
Alter a schema ownership
Урок 204.
00:06:20
Duplicate a schema along with all data
Урок 205.
00:05:21
What is a system catalog schema?
Урок 206.
00:07:41
Compare tables and columns in two schemas
Урок 207.
00:06:30
Schemas and Privileges
Урок 208.
00:07:23
Constructing arrays and ranges
Урок 209.
00:07:44
Using comparison operators
Урок 210.
00:04:17
Using inclusion operators
Урок 211.
00:05:15
Array constructions
Урок 212.
00:08:42
Array metadata functions
Урок 213.
00:06:33
Array search functions
Урок 214.
00:06:47
Array modification functions
Урок 215.
00:13:03
Array comparison with IN, ALL, ANY and SOME
Урок 216.
00:09:04
Formatting and converting an array
Урок 217.
00:04:34
Using arrays in tables
Урок 218.
00:05:51
Insert data into arrays
Урок 219.
00:07:10
Query array data
Урок 220.
00:03:08
Modifying array data
Урок 221.
00:04:12
Array Dimensions are ignored!
Урок 222.
00:03:53
Display all array elements
Урок 223.
00:10:38
Using Multi-dimensional arrays
Урок 224.
00:14:04
Array vs JSONB
Урок 225.
00:04:22
What is JSON?
Урок 226.
00:09:07
JSON Syntax
Урок 227.
00:04:10
JSON and JSONB data types
Урок 228.
00:06:31
Exploring JSON objects
Урок 229.
00:07:49
Create our first table with JSONB data type
Урок 230.
00:12:47
Update and Delete JSON Data
Урок 231.
00:05:05
Create JSON from tables
Урок 232.
00:05:24
Use json_agg to aggregate data
Урок 233.
00:04:40
Build a JSON array
Урок 234.
00:06:17
Creating a document from data
Урок 235.
00:05:05
Null Values in JSON documents
Урок 236.
00:06:26
Getting information from JSON documents
Урок 237.
00:02:55
The Existence Operator
Урок 238.
00:04:07
The Containment Operator
Урок 239.
00:04:09
JSON search with PostgreSQL functions
Урок 240.
00:11:30
Indexing on JSONB
Урок 241.
00:03:57
What is an index?
Урок 242.
00:15:48
Create an index
Урок 243.
00:11:11
Create unique indexes
Урок 244.
00:04:18
List all indexes
Урок 245.
00:05:19
Size of the table index
Урок 246.
00:04:35
List counts of all indexes
Урок 247.
00:03:35
Drop a index
Урок 248.
00:04:36
SQL Statement execution process
Урок 249.
00:06:36
SQL statement execution stages
Урок 250.
00:09:31
The query optimizer
Урок 251.
00:05:50
Optimizer node types
Урок 252.
00:06:24
Sequential Nodes
Урок 253.
00:07:52
Index Nodes
Урок 254.
00:06:47
Join Nodes
Урок 255.
00:04:48
Index Types - B-Tree Index
Урок 256.
00:04:45
Hash Index
Урок 257.
00:02:23
BRIN index
Урок 258.
00:01:56
GIN Index
Урок 259.
00:14:32
The EXPLAIN statement
Урок 260.
00:02:56
EXPLAIN output options
Урок 261.
00:09:11
Using EXPLAIN ANALYZE
Урок 262.
00:13:45
Understanding query cost model
Урок 263.
00:12:04
Index are not free
Урок 264.
00:08:52
Indexes for sorted output
Урок 265.
00:07:52
Using multiple indexes on a single query
Урок 266.
00:07:03
Execution plans depends on input values
Урок 267.
00:20:11
Using organized vs random data
Урок 268.
00:04:08
Try to use index only scan
Урок 269.
00:11:08
Partial indexes
Урок 270.
00:16:26
Expression Index
Урок 271.
00:03:12
Adding data while indexing
Урок 272.
00:07:55
Invalidating an index
Урок 273.
00:07:10
Rebuilding an index
Урок 274.
00:07:21
Introduction to views
Урок 275.
00:10:53
Creating a view
Урок 276.
00:04:29
Rename a view
Урок 277.
00:01:55
Delete a view
Урок 278.
00:07:09
Using filters with views
Урок 279.
00:04:09
A view with UNION of multiple tables
Урок 280.
00:07:18
Connecting multiple tables with a single view
Урок 281.
00:03:01
Re-arrange columns in a view
Урок 282.
00:01:52
Delete a column in a view
Урок 283.
00:02:19
Add a column in a view
Урок 284.
00:02:37
Regular views are dynamic
Урок 285.
00:03:55
What is an updatable view?
Урок 286.
00:05:28
An updatable view with CRUD operations
Урок 287.
00:07:54
Updatable views using WITH CHECK OPTION
Урок 288.
00:09:28
Updatable views using WITH LOCAL and CASCADED CHECK OPTION
Урок 289.
00:09:30
What is a Materialized View
Урок 290.
00:04:41
Creating a materialized view
Урок 291.
00:03:21
Drop a materialized view
Урок 292.
00:06:41
Changing materialized view data
Урок 293.
00:02:46
How to check if a materialized view is populated or not?
Урок 294.
00:06:35
Refreshing data in materialize views
Урок 295.
00:04:16
Why not use a table instead of materialized view?
Урок 296.
00:03:25
The downsides of using materialized views
Урок 297.
00:11:25
Using materialized view for websites page analysis
Урок 298.
00:01:34
List all materialized views by a SELECT statement
Урок 299.
00:03:49
List materialized views with no unique index
Урок 300.
00:13:09
Subqueries with WHERE Clause
Урок 301.
00:04:50
Subquery with IN operator
Урок 302.
00:08:38
Subquery with JOINs
Урок 303.
00:04:13
Order entries in UNION without using ORDER BY
Урок 304.
00:01:24
Subquery with an alias
Урок 305.
00:02:42
A SELECT without a FROM
Урок 306.
00:11:59
Correlated Queries
Урок 307.
00:07:04
SELECT.. IN (Subquery)
Урок 308.
00:07:45
Using ANY with subquery
Урок 309.
00:08:16
Using ALL with subquery
Урок 310.
00:04:52
Subquery using EXISTS
Урок 311.
00:05:45
Introduction to CTEs
Урок 312.
00:15:45
CTE query examples
Урок 313.
00:07:00
Combine CTE with a table
Урок 314.
00:08:18
Simultaneously DELETE INSERT via CTE
Урок 315.
00:04:46
Recursive CTEs
Урок 316.
00:19:48
Parent-child relationship via recursive CTE
Урок 317.
00:03:32
Introduction to summarization
Урок 318.
00:12:00
Subtotals on group sets
Урок 319.
00:07:34
Adding subtotal with ROLLUP
Урок 320.
00:13:55
Using GROUPING with ROLLUP
Урок 321.
00:03:36
Introduction to Window Functions
Урок 322.
00:08:23
Analyze Global Trades Data
Урок 323.
00:09:01
Using aggregate functions
Урок 324.
00:05:25
GROUP BY ROLLUP
Урок 325.
00:04:10
ROLLUP - Grouping multiple columns
Урок 326.
00:05:06
GROUP BY CUBE
Урок 327.
00:03:37
GROUP BY GROUPING SETS
Урок 328.
00:05:08
Query performance check
Урок 329.
00:05:27
Using FILTER clause
Урок 330.
00:04:44
Using Window Functions
Урок 331.
00:04:38
Partitioning the data
Урок 332.
00:02:45
Set data into millions format
Урок 333.
00:08:19
Ordering inside window
Урок 334.
00:08:06
Sliding dynamic windows
Урок 335.
00:21:41
Understanding window frames
Урок 336.
00:11:00
ROWS and RANGE indicators
Урок 337.
00:08:43
Using WINDOW
Урок 338.
00:03:22
Using WINDOW with ROWS BETWEEN
Урок 339.
00:05:01
RANK and DENSE_RANK functions
Урок 340.
00:09:01
NTILE function
Урок 341.
00:14:25
LEAD and LAG functions
Урок 342.
00:14:28
FIRST_VALUE, LAST_VALUE and NTH_value functions
Урок 343.
00:09:57
ROW_NUMBER function
Урок 344.
00:05:52
Finding Correlations
Урок 345.
00:09:46
ROW_NUMBER() - Window with Partition datasets
Урок 346.
00:02:24
ROW_NUMBER() - Reverse fields with order by
Урок 347.
00:03:17
ROW_NUMBER() - Find Nth record
Урок 348.
00:05:01
ROW_NUMBER() - Find DISTINCT with subquery
Урок 349.
00:06:29
ROW_NUMBER() - Pagination technique
Урок 350.
00:06:01
Using OVER() to calculate percentage
Урок 351.
00:05:52
Calculate difference compared to average
Урок 352.
00:05:09
Calculating cumulative totals
Урок 353.
00:05:37
Using LEAD to compare with next values
Урок 354.
00:09:34
Comparing with least number
Урок 355.
00:04:24
Window Function Summary
Урок 356.
00:06:52
Planning tips on using Window Functions
Урок 357.
00:03:01
Difference between RANK and DENSE_RANK
Урок 358.
00:04:59
Getting RANK to compute group and global ranks
Урок 359.
00:06:22
Using PARTITION BY for group averages
Урок 360.
00:04:16
Using WITH clause to create your own data
Урок 361.
00:06:40
Using WITH clause to set ORDER BY values
Урок 362.
00:05:23
DELETE and INSERT in one query using WITH
Урок 363.
00:06:19
Text to structured data
Урок 364.
00:09:01
Regular expressions notations table
Урок 365.
00:06:43
SIMILAR TO operator
Урок 366.
00:04:57
POSIX regular expressions
Урок 367.
00:17:54
SUBSTRING with regular expressions
Урок 368.
00:09:53
REGEXP_MATCHES Function
Урок 369.
00:08:01
REGEXP_REPLACE Function
Урок 370.
00:01:30
REGEXP_SPLIT_TO_TABLE Function
Урок 371.
00:02:45
REGEXP_SPLIT_TO_ARRAY function
Урок 372.
00:09:08
The Good Ol' Text Search
Урок 373.
00:11:38
Introducing tsvector
Урок 374.
00:15:46
Using tsquery with operators
Урок 375.
00:14:39
Full text search within a table
Урок 376.
00:07:41
Setup presidents speeches data
Урок 377.
00:07:45
Analyzing presidents speeches text data
Урок 378.
00:05:33
Ranking and Normalizing text results
Урок 379.
00:05:50
What is a partition
Урок 380.
00:18:25
When a partition can be used?
Урок 381.
00:16:12
Table inheritance
Урок 382.
00:02:41
Partition types
Урок 383.
00:19:23
Partition by Range
Урок 384.
00:23:32
Partition by List
Урок 385.
00:13:58
Partition by Hash
Урок 386.
00:06:46
DEFAULT Partition
Урок 387.
00:13:57
Multi-level partitioning
Урок 388.
00:05:07
Partition maintenance
Урок 389.
00:09:23
ALTERing the partition bounds
Урок 390.
00:07:12
Partition Indexes
Урок 391.
00:03:59
Partition pruning
Урок 392.
00:07:43
Determining a field for partition over
Урок 393.
00:05:57
Sizing the partition
Урок 394.
00:04:55
Partitioning Advantages
Урок 395.
00:04:43
Common partitioning mistakes
Урок 396.
00:09:48
PostgreSQL as a development platform?
Урок 397.
00:05:20
Procedural languages
Урок 398.
00:05:48
Keep the data on the server!
Урок 399.
00:03:53
Functions vs stored procedures
Урок 400.
00:03:31
User-defined functions
Урок 401.
00:02:36
Structure of a function
Урок 402.
00:10:02
Creating our first SQL function
Урок 403.
00:03:47
Introducing dollar quoting
Урок 404.
00:05:07
Function returning no values
Урок 405.
00:12:24
Function returning a single value
Урок 406.
00:05:38
Function returning a single value Part 2
Урок 407.
00:03:43
Function returning a single value Part 3
Урок 408.
00:14:04
Function using parameters
Урок 409.
00:05:21
Function using parameters Part 2
Урок 410.
00:14:43
Function using parameters Part 3
Урок 411.
00:13:19
Function using parameters Part 4
Урок 412.
00:13:10
Function returning a composite
Урок 413.
00:10:23
Function returning multiple rows
Урок 414.
00:03:10
Function - order matters!
Урок 415.
00:02:55
Function returning a table
Урок 416.
00:10:38
Function as a table source
Урок 417.
00:03:10
Function parameter modes
Урок 418.
00:11:20
Function parameters with default values
Урок 419.
00:06:57
Function based on views
Урок 420.
00:04:19
Drop a function
Урок 421.
00:02:10
Introduction to PL/pgSQL language
Урок 422.
00:02:41
PL/pgSQL vs SQL
Урок 423.
00:05:16
Structure of a PL/pgSQL function
Урок 424.
00:02:19
PL/pgSQL block structure
Урок 425.
00:06:03
Declaring variables
Урок 426.
00:01:41
Declaring variables via ALIAS FOR
Урок 427.
00:05:40
Declaring variables in function
Урок 428.
00:02:28
Variable initializing timing
Урок 429.
00:03:08
Copying data types
Урок 430.
00:10:03
Assigning variables from query
Урок 431.
00:04:36
Using IN, OUT without RETURNS
Урок 432.
00:04:51
Variables in block and subblock
Урок 433.
00:05:49
How to return query results
Урок 434.
00:09:07
Control Structures - IF statement
Урок 435.
00:06:06
Using IF with table data
Урок 436.
00:12:38
CASE Statement
Урок 437.
00:12:12
Searched CASE statement
Урок 438.
00:06:20
LOOP statement
Урок 439.
00:05:50
FOR Loops
Урок 440.
00:04:28
FOR Loops iterate over result set
Урок 441.
00:05:20
CONTINUE statement
Урок 442.
00:04:08
FOREACH loop with arrays
Урок 443.
00:13:39
WHILE loop
Урок 444.
00:11:37
Using RETURN QUERY
Урок 445.
00:04:38
Returning a table
Урок 446.
00:14:49
Using RETURN NEXT
Урок 447.
00:06:29
Error and exception handling
Урок 448.
00:05:40
Exception - Too many rows
Урок 449.
00:04:20
Using SQLSTATE codes for exception handling
Урок 450.
00:07:22
Exception with data exception errors
Урок 451.
00:05:37
Functions vs Stored Procedures
Урок 452.
00:08:16
Create a transactions
Урок 453.
00:05:09
Understanding the use of stored procedures
Урок 454.
00:04:11
Returning a value
Урок 455.
00:01:55
Drop a procedure
Урок 456.
00:10:41
What is a trigger
Урок 457.
00:02:35
Types of triggers
Урок 458.
00:02:55
Trigger table
Урок 459.
00:10:44
Pros and Cons of triggers
Урок 460.
00:02:46
Trigger key points
Урок 461.
00:05:29
Steps to create a trigger
Урок 462.
00:11:41
Data auditing with a trigger
Урок 463.
00:08:39
Bind a function to a table with trigger
Урок 464.
00:11:57
Modify data at INSERT event
Урок 465.
00:04:54
View triggers variables
Урок 466.
00:10:08
Disallow DELETE on a table
Урок 467.
00:05:18
Disallow TRUNCATE on a table
Урок 468.
00:20:43
Creating an audit trigger
Урок 469.
00:15:06
Creating conditional triggers
Урок 470.
00:05:37
Disallow data change on primary key
Урок 471.
00:03:31
Use triggers very cautiously
Урок 472.
00:01:51
What is an event trigger
Урок 473.
00:03:22
Event triggers usage scenarios
Урок 474.
00:04:37
Creating event triggers
Урок 475.
00:04:26
Event trigger events and variables
Урок 476.
00:14:58
Creating an audit event trigger
Урок 477.
00:08:53
Prevent schema changes
Урок 478.
00:00:58
Dropping a trigger
Урок 479.
00:06:41
Understanding row by row operations
Урок 480.
00:04:31
Cursors and procedural languages
Урок 481.
00:02:47
Steps to create a cursor
Урок 482.
00:08:39
Creating a cursor
Урок 483.
00:09:40
Opening a cursor
Урок 484.
00:06:34
Using a cursor
Урок 485.
00:01:43
Updating data
Урок 486.
00:01:23
Closing a cursor
Урок 487.
00:10:32
Creating a PL/PGSQL Cursor
Урок 488.
00:14:04
Using a parametric cursor via function
Урок 489.
00:04:03
What is a crosstab report
Урок 490.
00:01:54
Installing the tablefunc extension
Урок 491.
00:14:39
Creating a crosstab report - Student Rankings
Урок 492.
00:03:03
Orders matters in crosstab reports
Урок 493.
00:11:03
Pivoting with Rainfalls data
Урок 494.
00:04:03
Pivoting rows and columns
Урок 495.
00:09:57
Matrix report via a query
Урок 496.
00:05:49
Aggregate over filter
Урок 497.
00:06:51
Static to dynamic pivots
Урок 498.
00:06:59
Dynamic pivot query via JSON
Урок 499.
00:17:17
Dynamic pivot table columns
Урок 500.
00:06:06
Interactive client-side pivot
Урок 501.
00:10:32
Handling missing values
Урок 502.
00:10:15
Global Character Set Support
Урок 503.
00:11:55
Client and Server Encoding
Урок 504.
00:08:36
What is a transaction?
Урок 505.
00:05:21
How SQL protect database during transaction
Урок 506.
00:08:41
ACID Database
Урок 507.
00:02:22
Transaction setup
Урок 508.
00:13:45
Transaction analysis
Урок 509.
00:03:45
How to fix aborted transaction
Урок 510.
00:05:33
How to fix transactions on crash
Урок 511.
00:03:37
Partial transaction rollback with savepoints
Урок 512.
00:05:41
Using SAVEPOINT with transaction
Урок 513.
00:01:12
Using Northwind database
Урок 514.
00:02:09
Orders shipping to USA or France
Урок 515.
00:01:49
Total numbers of orders shipped to USA or France
Урок 516.
00:03:22
Orders shipped to latin america
Урок 517.
00:04:02
Show total order amount for each order
Урок 518.
00:02:27
First the oldest and latest order date
Урок 519.
00:03:56
Total products in each categories
Урок 520.
00:03:42
List products that needs re-ordering
Урок 521.
00:10:01
Freight analysis
Урок 522.
00:05:53
Customers with no orders
Урок 523.
00:04:59
Top customers with total orders amount
Урок 524.
00:02:50
Orders with many lines of ordered items
Урок 525.
00:10:17
Orders with double entry line items
Урок 526.
00:10:53
Late shipped orders by employees
Урок 527.
00:02:51
Countries with customers or suppliers
Урок 528.
00:06:16
Countries with customers or suppliers - Using CTE
Урок 529.
00:07:23
Customers with multiple orders
Урок 530.
00:07:49
First order from each country
Урок 531.
00:03:15
Human Resources Database Structure
Урок 532.
00:05:32
Quick overview of all tables
Урок 533.
00:03:21
Quick overview of all tables Part 2
Урок 534.
00:03:50
Get highest, lowest, total, and average salaries of employees
Урок 535.
00:01:26
Difference b/w highest and lowest salaries by job_id
Урок 536.
00:02:16
Get lowest paid salaries by each manager
Урок 537.
00:02:37
Average salary for each department with more than 10 employees.
Урок 538.
00:02:00
Average salary for each post excluding programmer
Урок 539.
00:02:29
Maximum salary for each post where salary is at or above $5000
Урок 540.
00:01:41
Using an alias name with columns
Урок 541.
00:02:53
Compute 15% of salary for all employees
Урок 542.
00:01:23
To list all employees IDs within each job_id group
Урок 543.
00:05:43
Discard characters from employees email address
Урок 544.
00:03:13
List all employees with first_name starts with letters "A", "C" or "M"
Урок 545.
00:02:06
String manipulation with upper, lower and initcap functions
Урок 546.
00:00:53
Using SUBSTRING to get portion of string data
Урок 547.
00:02:10
Get unique designations in employees table
Урок 548.
00:02:16
Select employees with particular department id only
Урок 549.
00:03:22
Select employees not in range
Урок 550.
00:02:37
Find Letter "C" in last_name at 3rd or greater position
Урок 551.
00:02:58
Update phone_number with your strings
Урок 552.
00:02:12
Get the monthly salary of each employees
Урок 553.
00:01:52
Calculate average salary with total number of employees
Урок 554.
00:02:30
find employees whose names contains exactly six characters
Урок 555.
00:03:50
Select first or last records in a table
Урок 556.
00:04:17
Get first or last record per each group
Урок 557.
00:01:56
How to calculate cube root in PostgreSQL?
Урок 558.
00:04:19
Security concepts and and levels
Урок 559.
00:05:38
Instance Level Security
Урок 560.
00:04:31
Instance Level Security - Add users to roles
Урок 561.
00:04:08
Use pgAdmin to create roles
Урок 562.
00:05:34
Database Level Security
Урок 563.
00:11:14
Schema Level Security
Урок 564.
00:11:06
Table Level Security
Урок 565.
00:08:33
Column Level Security
Урок 566.
00:14:54
Row Level Security
Урок 567.
00:06:15
Using CURRENT_USER with RLS
Урок 568.
00:05:04
Row level policy for application users
Урок 569.
00:02:22
DROP a policy
Урок 570.
00:02:51
Inspecting permissions
Урок 571.
00:04:17
Row Level Security Performance
Урок 572.
00:07:20
Encrypted data in columns
Урок 573.
00:04:08
Connect to local or remote database
Урок 574.
00:01:46
Switch connection to a new database
Урок 575.
00:03:25
List all databases and tables
Урок 576.
00:03:33
List all table space, schemas, and indices
Урок 577.
00:07:12
List all sequences, roles, data types, domain data types
Урок 578.
00:01:02
Describe a table
Урок 579.
00:01:18
Edit SQL commands in editor
Урок 580.
00:03:06
Display command history, run commands from a file
Урок 581.
00:01:06
Built-in syntax reference for commands
Урок 582.
00:03:51
Represent NULL values on psql terminal
Урок 583.
00:02:11
Make table layout funkey!
Урок 584.
00:01:42
Repeatedly Execute a Query
Урок 585.
00:03:51
Represent NULL Visibly
Урок 586.
00:02:45
Turn on query execution time
Урок 587.
00:04:11
List database users, and database sizes with and without indexes
Урок 588.
00:04:21
List all database and schemas
Урок 589.
00:08:25
List all tables and views
Урок 590.
00:02:47
List all columns from a table
Урок 591.
00:02:34
View system metadata via system information functions
Урок 592.
00:08:16
View privileges information across tables
Урок 593.
00:05:25
Using system Administration functions
Урок 594.
00:04:17
Show all running queries
Урок 595.
00:02:55
Terminate running and IDLE process
Урок 596.
00:01:50
How to check live and dead rows in tables
Урок 597.
00:09:22
File layout of PostgreSQL Tables
Урок 598.
00:12:07
Using SELECT INTO to create a new table with joins
Урок 599.
00:03:09
Duplicate a table with or without data
Урок 600.
00:07:00
Import data from CSV files
Урок 601.
00:02:43
Export Data to CSV files
Урок 602.
00:07:27
Deleting duplicate records
Урок 603.
00:05:58
Database operations and table size
Урок 604.
00:07:27
Tracking table size
Урок 605.
00:10:29
PostgreSQL autovacuum proces
Урок 606.
00:09:56
Recovering unused space with VACUUM
Урок 607.
00:10:46
Generated Columns
Урок 608.
00:22:37
Create a custom index method
Урок 609.
00:10:57
Create a user-defined aggregate function
Урок 610.
00:00:49
Thank You!
Автор - udemy
udemy
Udemy - одна из самых больших площадок в мире по доставке обучающего контента от разных авторов всего мира. Присутсвуют курсы практически на любую тему.
drop table if exists "customers";
create table "customers"(
"customer_id" int4 not null,
"first_name" varchar(100) collate "default",
"last_name" varchar(255) collate "default"
)
with (oids=false);
begin;
insert into "customers" values ('1', 'John', 'Doe');
insert into "customers" values ('2', 'Jeff', 'Smith');
insert into "customers" values ('3', 'Mike', 'Steel');
insert into "customers" values ('4', 'Mark', 'Benjamin');
insert into "customers" values ('5', 'Hannah', 'Rose');
commit;
alter table "customers" add primary key ("customer_id") not deferrable initially immediate;
drop table if exists "public"."products";
create table "public"."products" (
"product_id" int4 not null,
"product_name" varchar(255) not null COLLATE "default",
"unit_price" numeric(10,2)
)
with (oids=false);
begin;
insert into "public"."products" values ('1', 'Computer', '500');
insert into "public"."products" values ('2', 'Mouse', '20.00');
insert into "public"."products" values ('3', 'Printer', '300.00');
insert into "public"."products" values ('4', 'Monitor', '200.00');
insert into "public"."products" values ('5', 'Microphone', '215.00');
insert into "public"."products" values ('6', 'Laptop', '800.00');
commit;
alter table "public"."products" add primary key ("product_id") not deferrable initially immediate ;
drop table if exists "public"."purchases";
create table "public"."purchases" (
"purchase_id" int4 not null,
"product_id" int4,
"customer_id" int4
)
with (oids=false);
begin;
insert into "public"."purchases" values ('1', '1', '1');
insert into "public"."purchases" values ('2', '3', '1');
insert into "public"."purchases" values ('3', '6', '2');
insert into "public"."purchases" values ('4', '6', '2');
insert into "public"."purchases" values ('5', '3', '3');
insert into "public"."purchases" values ('6', '2', '3');
insert into "public"."purchases" values ('7', '4', '4');
insert into "public"."purchases" values ('8', '2', '4');
insert into "public"."purchases" values ('9', '3', '5');
insert into "public"."purchases" values ('10', '6', '5');
commit;
alter table "public"."purchases" add primary key ("purchase_id") not deferrable initially immediate;
Mendax47
Does anyone know where i can find his "type conversion.sql" in the course material.
Anonymous
This is one of the best courses for learning SQL! Thank you.
smart_enough
the order of videos is a bit messy
Djoudi
@admin
could you upload the author second course titled Master Redis - From Beginner to Advanced, 20+ hours .
thank you in advance.
Команда внимательно читает ваши комментарии и оперативно на них реагирует. Вы можете спокойно оставлять запросы на обновления или задавать любые вопросы о курсе здесь.
PostgreSQL - одна из самых популярных свободно распространяемых реляционных СУБД. В России PostgreSQL используют многие интернет-проекты: Рамблер, Яндекс, Mail.ru, avito.ru, а также различные государственные и коммерческие организации разного масштаба - от малых до крупных.
Если вы занимаетесь администрированием базы данных и хотите быстро обновить свой набор навыков, этот курс для вас. Каждый модуль стоит отдельно и обучает практическим задачам, которые вам необходимо выполнить на работе, например, оптимизация оборудования, тестирование производительности вашего сервера и многое другое.
create table "customers"(
"customer_id" int4 not null,
"first_name" varchar(100) collate "default",
"last_name" varchar(255) collate "default"
)
with (oids=false);
begin;
insert into "customers" values ('1', 'John', 'Doe');
insert into "customers" values ('2', 'Jeff', 'Smith');
insert into "customers" values ('3', 'Mike', 'Steel');
insert into "customers" values ('4', 'Mark', 'Benjamin');
insert into "customers" values ('5', 'Hannah', 'Rose');
commit;
alter table "customers" add primary key ("customer_id") not deferrable initially immediate;
drop table if exists "public"."products";
create table "public"."products" (
"product_id" int4 not null,
"product_name" varchar(255) not null COLLATE "default",
"unit_price" numeric(10,2)
)
with (oids=false);
begin;
insert into "public"."products" values ('1', 'Computer', '500');
insert into "public"."products" values ('2', 'Mouse', '20.00');
insert into "public"."products" values ('3', 'Printer', '300.00');
insert into "public"."products" values ('4', 'Monitor', '200.00');
insert into "public"."products" values ('5', 'Microphone', '215.00');
insert into "public"."products" values ('6', 'Laptop', '800.00');
commit;
alter table "public"."products" add primary key ("product_id") not deferrable initially immediate ;
drop table if exists "public"."purchases";
create table "public"."purchases" (
"purchase_id" int4 not null,
"product_id" int4,
"customer_id" int4
)
with (oids=false);
begin;
insert into "public"."purchases" values ('1', '1', '1');
insert into "public"."purchases" values ('2', '3', '1');
insert into "public"."purchases" values ('3', '6', '2');
insert into "public"."purchases" values ('4', '6', '2');
insert into "public"."purchases" values ('5', '3', '3');
insert into "public"."purchases" values ('6', '2', '3');
insert into "public"."purchases" values ('7', '4', '4');
insert into "public"."purchases" values ('8', '2', '4');
insert into "public"."purchases" values ('9', '3', '5');
insert into "public"."purchases" values ('10', '6', '5');
commit;
alter table "public"."purchases" add primary key ("purchase_id") not deferrable initially immediate;
could you upload the author second course titled Master Redis - From Beginner to Advanced, 20+ hours .
thank you in advance.