Scribd
Upload
1 views19 pages

Windows PowerShell2

The document details a session using PostgreSQL via PowerShell, including commands to connect to a database, create a new database, and attempt to import SQL data from a file. It lists various databases and demonstrates the creation of a new database named 'dq_prac' and the execution of SQL commands, including schema creation and data insertion. Several errors and warnings are noted, particularly regarding file paths and data integrity issues in the imported dataset.

Uploaded by

mahalovaleyla
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
1 views19 pages

Windows PowerShell2

The document details a session using PostgreSQL via PowerShell, including commands to connect to a database, create a new database, and attempt to import SQL data from a file. It lists various databases and demonstrates the creation of a new database named 'dq_prac' and the execution of SQL commands, including schema creation and data insertion. Several errors and warnings are noted, particularly regarding file paths and data integrity issues in the imported dataset.

Uploaded by

mahalovaleyla
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Windows PowerShell

Copyright (C) Microsoft Corporation. All rights reserved.

Install the latest PowerShell for new features and improvements!


[Link]

PS C:\Users\User> psql -U postgres


Password for user postgres:

psql (18.2, server 15.2)


WARNING: Console code page (866) differs from Windows code page (1251)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.

postgres=# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype
| Locale | ICU Rules | Access privileges
-------------+----------+----------+-----------------+----------------------------+----------------------------
+--------+-----------+-----------------------
a2 | postgres | UTF8 | libc | English_United States.1251 | English_United
States.1251 | | |
a2_demo | postgres | UTF8 | libc | English_United States.1251 |
English_United States.1251 | | |
a2_demo2 | postgres | UTF8 | libc | English_United States.1251 |
English_United States.1251 | | |
a2_football | postgres | UTF8 | libc | English_United States.1251 |
English_United States.1251 | | |
a3 | postgres | UTF8 | libc | English_United States.1251 | English_United
States.1251 | | |
a5 | postgres | UTF8 | libc | English_United States.1251 | English_United
States.1251 | | |
a5b | postgres | UTF8 | libc | English_United States.1251 |
English_United States.1251 | | |
a5demo | postgres | UTF8 | libc | English_United States.1251 |
English_United States.1251 | | |
c3 | postgres | UTF8 | libc | English_United States.1251 | English_United
States.1251 | | |
cw2 | postgres | UTF8 | libc | English_United States.1251 |
English_United States.1251 | | |
postgres | postgres | UTF8 | libc | English_United States.1251 |
English_United States.1251 | | |
template0 | postgres | UTF8 | libc | English_United States.1251 |
English_United States.1251 | | | =c/postgres +
| | | | | | | |
postgres=CTc/postgres
template1 | postgres | UTF8 | libc | English_United States.1251 |
English_United States.1251 | | | =c/postgres +
| | | | | | | |
postgres=CTc/postgres
(13 rows)

postgres=#
postgres=# CREATE DATABASE dq_prac
postgres-# ;
CREATE DATABASE
postgres=# cd dq_prac
postgres-# ;
ERROR: syntax error at or near "cd"
LINE 1: cd dq_prac
^
postgres=# \c dq_prac
psql (18.2, server 15.2)
You are now connected to database "dq_prac" as user "postgres".
dq_prac=# \i 'C:\Users\User\Downloads\dq_dimensions_faulty_sample_postgres.sql'
C:UsersUserDownloadsdq_dimensions_faulty_sample_postgres.sql: No such file or
directory
dq_prac=# \i "C:\Users\User\Downloads\dq_dimensions_faulty_sample_postgres.sql"
"C:/Users/User/Downloads/dq_dimensions_faulty_sample_postgres.sql: No such file or
directory
dq_prac=# \i 'C:/Users/User/Downloads/dq_dimensions_faulty_sample_postgres.sql'
psql:C:/Users/User/Downloads/dq_dimensions_faulty_sample_postgres.sql:15: NOTICE:
schema "dq_lab" does not exist, skipping
DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
INSERT 0 5
CREATE TABLE
INSERT 0 5
CREATE TABLE
INSERT 0 16
row_id | order_id | customer_id | customer_name | email | phone |
order_date | ship_date | product_id | quantity | unit_price | total_amount | currency |
order_status | country_code | postal_code | created_at | updated_at |
source_system | notes
--------+----------+-------------+------------------+------------------------+---------------+------------
+------------+------------+----------+------------+--------------+----------+--------------+--------------
+-------------+---------------------+---------------------+---------------+----------------------------------------
1 | O1001 | C001 | Leyla Aliyeva | leyla@[Link] |
+994501112233 | 2026-07-01 | 2026-07-03 | P001 |1 | 1200.00 | 1200.00
| AZN | DELIVERED | AZ | AZ1000 | 2026-07-01 10:00:00 | 2026-07-03
12:00:00 | web | clean row
2 | O1002 | C002 | | | +994501234567 | 2026-07-02
| 2026-07-04 | P002 |2 | 25.00 | 50.00 | AZN | DELIVERED |
| AZ1001 | 2026-07-02 09:10:00 | 2026-07-04 15:00:00 | | missing name,
email, country, source
3 | O1003 | C003 | Aysel Mammadova | aysel@email | phone123 |
02/07/2026 | 2026-07-05 | P003 | ten | 12O.50 | 125.00 | AZN |
DELIVERED | AZ | 1002 | 2026-07-02 11:00:00 | 2026-07-05 13:00:00 |
mobile | bad formats
4 | O1001 | C004 | Nigar Karimova | nigar@[Link] |
+994551112233 | 2026-07-03 | 2026-07-04 | P004 |1 | 250.00 | 250.00
| AZN | DELIVERED | AZ | AZ1003 | 2026-07-03 08:00:00 | 2026-07-04
10:00:00 | web | duplicate order id
5 | O1005 | C005 | Robert Smith | robert@[Link] |
+994701112233 | 2026-07-03 | 2026-07-06 | P002 | -3 | 99999.99 | -
299999.97 | AZN | DELIVERED | AZ | AZ1004 | 2026-07-03 12:00:00 |
2026-07-06 16:00:00 | web | negative quantity and impossible price
6 | O1006 | C001 | Leyla Aliyeva | leyla@[Link] |
+994501112233 | 2026-07-06 | 2026-07-04 | P003 |2 | 40.00 | 150.00 |
AZN | DELIVERED | AZ | AZ1000 | 2026-07-06 10:30:00 | 2026-07-04
09:30:00 | web | ship before order and wrong total
7 | O1007 | C999 | Unknown Customer | unknown@[Link] |
+994501119999 | 2026-07-07 | 2026-07-09 | P999 |1 | 100.00 | 100.00
| AZN | DELIVERED | AZ | AZ1005 | 2026-07-07 10:00:00 | 2026-07-09
11:00:00 | web | invalid customer and product
8 | O1008 | C002 | Murad Hasanov | murad@[Link] |
+994501234567 | 2026-07-07 | 2026-07-08 | P005 |1 | 80.00 | 80.00 |
AZN$ | OK | Azerbaijan | AZ1006 | 2026-07-07 14:00:00 | 2026-07-08
15:00:00 | telegram_bot | invalid currency, status, country code
9 | O1009 | C003 | J0hn D@vis | [Link]@[Link] |
+994501110000 | 2026-07-08 | 2026-07-10 | P004 |1 | 300.00 | 300.00
| AZN | DELIVERED | AZ | AZ1007 | 2026-07-08 09:00:00 | 2026-07-10
10:00:00 | web | ш┐Щф╕кф║зхУБщЭЮх╕╕хе╜
10 | O1010 | C004 | Nigar Karimova | nigar@[Link] |
+994551112233 | 2024-01-01 | 2024-01-02 | P001 |1 | 1100.00 | 1100.00
| AZN | DELIVERED | AZ | AZ1008 | 2024-01-01 08:00:00 | 2024-01-02
08:00:00 | web | old data / freshness issue
11 | O1011 | C005 | Robert Smith | robert@[Link] |
+994701112233 | 2026-07-08 | 2026-07-11 | P005 |1 | 90.00 | 90.00 |
AZN | DELIVERED | AZ | AZ1009 | 2026-07-08 10:00:00 | 2026-07-11
10:00:00 | web | business duplicate part 1
12 | O1012 | C005 | Robert Smith | robert@[Link] |
+994701112233 | 2026-07-08 | 2026-07-11 | P005 |1 | 90.00 | 90.00 |
AZN | DELIVERED | AZ | AZ1009 | 2026-07-08 10:05:00 | 2026-07-11
10:05:00 | web | business duplicate part 2
13 | O1013 | | | | +994501112244 | 2026-07-09 |
2026-07-10 | P002 |1 | 30.00 | 30.00 | AZN | DELIVERED | AZ
| AZ1010 | 2026-07-09 10:00:00 | 2026-07-10 11:00:00 | | blank strings
instead of NULL
14 | O1014 | C001 | Leyla Aliyeva | leyla@[Link] |
+994501112233 | 2026-07-09 | | P003 |2 | 40.00 | 80.00 | AZN
| CANCELLED | AZ | AZ1000 | 2026-07-09 12:00:00 | 2026-07-09 13:00:00 |
web | cancelled order has non-zero amount
15 | O1015 | C002 | Murad Hasanov | murad@[Link] |
+994501234567 | 2026-07-09 | | P004 |1 | 250.00 | 250.00 | AZN
| DELIVERED | AZ | AZ1011 | 2026-07-09 13:00:00 | 2026-07-09 14:00:00 |
mobile | delivered but no ship date
16 | O1016 | C003 | Aysel Mammadova | aysel@[Link] |
+994501233333 | 2026-07-09 | 2026-07-12 | P002 | 100000 | 25.00 |
2500000.00 | AZN | DELIVERED | AZ | AZ1012 | 2026-07-09 14:00:00 |
2026-07-12 14:00:00 | web | outlier quantity and total amount
(16 rows)

dq_prac=#
dq_prac=#
dq_prac=#
dq_prac=#
dq_prac=#
dq_prac=#
dq_prac=#
dq_prac=#
dq_prac=# SET search_path TO dq_lab;
SET
dq_prac=#
dq_prac=# SELECT *
dq_prac-# FROM customer_orders_raw
dq_prac-# ORDER BY row_id;
row_id | order_id | customer_id | customer_name | email | phone |
order_date | ship_date | product_id | quantity | unit_price | total_amount | currency |
order_status | country_code | postal_code | created_at | updated_at |
source_system | notes
--------+----------+-------------+------------------+------------------------+---------------+------------
+------------+------------+----------+------------+--------------+----------+--------------+--------------
+-------------+---------------------+---------------------+---------------+----------------------------------------
1 | O1001 | C001 | Leyla Aliyeva | leyla@[Link] |
+994501112233 | 2026-07-01 | 2026-07-03 | P001 |1 | 1200.00 | 1200.00
| AZN | DELIVERED | AZ | AZ1000 | 2026-07-01 10:00:00 | 2026-07-03
12:00:00 | web | clean row
2 | O1002 | C002 | | | +994501234567 | 2026-07-02
| 2026-07-04 | P002 |2 | 25.00 | 50.00 | AZN | DELIVERED |
| AZ1001 | 2026-07-02 09:10:00 | 2026-07-04 15:00:00 | | missing name,
email, country, source
3 | O1003 | C003 | Aysel Mammadova | aysel@email | phone123 |
02/07/2026 | 2026-07-05 | P003 | ten | 12O.50 | 125.00 | AZN |
DELIVERED | AZ | 1002 | 2026-07-02 11:00:00 | 2026-07-05 13:00:00 |
mobile | bad formats
4 | O1001 | C004 | Nigar Karimova | nigar@[Link] |
+994551112233 | 2026-07-03 | 2026-07-04 | P004 |1 | 250.00 | 250.00
| AZN | DELIVERED | AZ | AZ1003 | 2026-07-03 08:00:00 | 2026-07-04
10:00:00 | web | duplicate order id
5 | O1005 | C005 | Robert Smith | robert@[Link] |
+994701112233 | 2026-07-03 | 2026-07-06 | P002 | -3 | 99999.99 | -
299999.97 | AZN | DELIVERED | AZ | AZ1004 | 2026-07-03 12:00:00 |
2026-07-06 16:00:00 | web | negative quantity and impossible price
6 | O1006 | C001 | Leyla Aliyeva | leyla@[Link] |
+994501112233 | 2026-07-06 | 2026-07-04 | P003 |2 | 40.00 | 150.00 |
AZN | DELIVERED | AZ | AZ1000 | 2026-07-06 10:30:00 | 2026-07-04
09:30:00 | web | ship before order and wrong total
7 | O1007 | C999 | Unknown Customer | unknown@[Link] |
+994501119999 | 2026-07-07 | 2026-07-09 | P999 |1 | 100.00 | 100.00
| AZN | DELIVERED | AZ | AZ1005 | 2026-07-07 10:00:00 | 2026-07-09
11:00:00 | web | invalid customer and product
8 | O1008 | C002 | Murad Hasanov | murad@[Link] |
+994501234567 | 2026-07-07 | 2026-07-08 | P005 |1 | 80.00 | 80.00 |
AZN$ | OK | Azerbaijan | AZ1006 | 2026-07-07 14:00:00 | 2026-07-08
15:00:00 | telegram_bot | invalid currency, status, country code
9 | O1009 | C003 | J0hn D@vis | [Link]@[Link] |
+994501110000 | 2026-07-08 | 2026-07-10 | P004 |1 | 300.00 | 300.00
| AZN | DELIVERED | AZ | AZ1007 | 2026-07-08 09:00:00 | 2026-07-10
10:00:00 | web | ш┐Щф╕кф║зхУБщЭЮх╕╕хе╜
10 | O1010 | C004 | Nigar Karimova | nigar@[Link] |
+994551112233 | 2024-01-01 | 2024-01-02 | P001 |1 | 1100.00 | 1100.00
| AZN | DELIVERED | AZ | AZ1008 | 2024-01-01 08:00:00 | 2024-01-02
08:00:00 | web | old data / freshness issue
11 | O1011 | C005 | Robert Smith | robert@[Link] |
+994701112233 | 2026-07-08 | 2026-07-11 | P005 |1 | 90.00 | 90.00 |
AZN | DELIVERED | AZ | AZ1009 | 2026-07-08 10:00:00 | 2026-07-11
10:00:00 | web | business duplicate part 1
12 | O1012 | C005 | Robert Smith | robert@[Link] |
+994701112233 | 2026-07-08 | 2026-07-11 | P005 |1 | 90.00 | 90.00 |
AZN | DELIVERED | AZ | AZ1009 | 2026-07-08 10:05:00 | 2026-07-11
10:05:00 | web | business duplicate part 2
13 | O1013 | | | | +994501112244 | 2026-07-09 |
2026-07-10 | P002 |1 | 30.00 | 30.00 | AZN | DELIVERED | AZ
| AZ1010 | 2026-07-09 10:00:00 | 2026-07-10 11:00:00 | | blank strings
instead of NULL
14 | O1014 | C001 | Leyla Aliyeva | leyla@[Link] |
+994501112233 | 2026-07-09 | | P003 |2 | 40.00 | 80.00 | AZN
| CANCELLED | AZ | AZ1000 | 2026-07-09 12:00:00 | 2026-07-09 13:00:00 |
web | cancelled order has non-zero amount
15 | O1015 | C002 | Murad Hasanov | murad@[Link] |
+994501234567 | 2026-07-09 | | P004 |1 | 250.00 | 250.00 | AZN
| DELIVERED | AZ | AZ1011 | 2026-07-09 13:00:00 | 2026-07-09 14:00:00 |
mobile | delivered but no ship date
16 | O1016 | C003 | Aysel Mammadova | aysel@[Link] |
+994501233333 | 2026-07-09 | 2026-07-12 | P002 | 100000 | 25.00 |
2500000.00 | AZN | DELIVERED | AZ | AZ1012 | 2026-07-09 14:00:00 |
2026-07-12 14:00:00 | web | outlier quantity and total amount
(16 rows)

dq_prac=#
dq_prac=#
dq_prac=# SELECT column_name, data_type
dq_prac-# FROM information.scheme_columns
dq_prac-# WHERE table_scehem;
ERROR: relation "information.scheme_columns" does not exist
LINE 2: FROM information.scheme_columns
^
dq_prac=# SELECT column_name, data_type
dq_prac-# FROM information.schema_columns
dq_prac-# WHERE table_scehema = 'dq_lab'
dq_prac-# AND table_name = 'customer_orders_raw';
ERROR: relation "information.schema_columns" does not exist
LINE 2: FROM information.schema_columns
^
dq_prac=# SELECT column_name, data_type
dq_prac-# FROM information_schema.columns
dq_prac-# WHERE table_schema = 'dq_lab'
dq_prac-# AND table_name = 'customer_orders_raw';
column_name | data_type
---------------+-------------------
row_id | integer
order_id | character varying
customer_id | character varying
customer_name | character varying
email | character varying
phone | character varying
order_date | character varying
ship_date | character varying
product_id | character varying
quantity | character varying
unit_price | character varying
total_amount | character varying
currency | character varying
order_status | character varying
country_code | character varying
postal_code | character varying
created_at | character varying
updated_at | character varying
source_system | character varying
notes | text
(20 rows)

dq_prac=# SELECT * FROM customer_orders_raw


dq_prac-# WHERE customer_id IS NULL
dq_prac-# OR customer_name IS NULLL
dq_prac-# OR email IS NULL
dq_prac-# OR source_system IS NULL;
ERROR: syntax error at or near "NULLL"
LINE 3: OR customer_name IS NULLL
^
dq_prac=# SELECT * FROM customer_orders_raw
dq_prac-# WHERE customer_id IS NULL
dq_prac-# OR customer_name IS NULL
dq_prac-# OR email IS NULL
dq_prac-# OR source_system IS NULL;
row_id | order_id | customer_id | customer_name | email | phone | order_date |
ship_date | product_id | quantity | unit_price | total_amount | currency | order_status |
country_code | postal_code | created_at | updated_at | source_system |
notes
--------+----------+-------------+---------------+-------+---------------+------------+------------
+------------+----------+------------+--------------+----------+--------------+--------------+-------------
+---------------------+---------------------+---------------+--------------------------------------
2 | O1002 | C002 | | | +994501234567 | 2026-07-02 | 2026-07-
04 | P002 |2 | 25.00 | 50.00 | AZN | DELIVERED | | AZ1001
| 2026-07-02 09:10:00 | 2026-07-04 15:00:00 | | missing name, email,
country, source
(1 row)

dq_prac=# SELECT * FROM customer_orders_raw


dq_prac-# WHERE customer_id IS NULL;
row_id | order_id | customer_id | customer_name | email | phone | order_date |
ship_date | product_id | quantity | unit_price | total_amount | currency | order_status |
country_code | postal_code | created_at | updated_at | source_system | notes
--------+----------+-------------+---------------+-------+-------+------------+-----------+------------
+----------+------------+--------------+----------+--------------+--------------+-------------+------------
+------------+---------------+-------
(0 rows)
dq_prac=# SELECT * FROM customer_orders_raw
dq_prac-# WHERE customer_id = ''
dq_prac-# OR customer_name = ''
dq_prac-# OR email = ''
dq_prac-# OR source_system = '';
row_id | order_id | customer_id | customer_name | email | phone | order_date |
ship_date | product_id | quantity | unit_price | total_amount | currency | order_status |
country_code | postal_code | created_at | updated_at | source_system |
notes
--------+----------+-------------+---------------+-------+---------------+------------+------------
+------------+----------+------------+--------------+----------+--------------+--------------+-------------
+---------------------+---------------------+---------------+-------------------------------
13 | O1013 | | | | +994501112244 | 2026-07-09 | 2026-07-10 |
P002 |1 | 30.00 | 30.00 | AZN | DELIVERED | AZ | AZ1010
| 2026-07-09 10:00:00 | 2026-07-10 11:00:00 | | blank strings instead of NULL
(1 row)

dq_prac=# SELECT * FROM customer_orders_raw


dq_prac-# WHERE email NOT LIKE '%@%.%';
row_id | order_id | customer_id | customer_name | email | phone |
order_date | ship_date | product_id | quantity | unit_price | total_amount | currency |
order_status | country_code | postal_code | created_at | updated_at |
source_system | notes
--------+----------+-------------+-----------------+-------------+---------------+------------+------------
+------------+----------+------------+--------------+----------+--------------+--------------+-------------
+---------------------+---------------------+---------------+-------------------------------
3 | O1003 | C003 | Aysel Mammadova | aysel@email | phone123 |
02/07/2026 | 2026-07-05 | P003 | ten | 12O.50 | 125.00 | AZN |
DELIVERED | AZ | 1002 | 2026-07-02 11:00:00 | 2026-07-05 13:00:00 |
mobile | bad formats
13 | O1013 | | | | +994501112244 | 2026-07-09 | 2026-
07-10 | P002 |1 | 30.00 | 30.00 | AZN | DELIVERED | AZ |
AZ1010 | 2026-07-09 10:00:00 | 2026-07-10 11:00:00 | | blank strings
instead of NULL
(2 rows)
dq_prac=# SELECT * FROM customer_orders_raw
dq_prac-# WHERE order_date NOT LIKE '____-__-__'
dq_prac-# ;
row_id | order_id | customer_id | customer_name | email | phone | order_date |
ship_date | product_id | quantity | unit_price | total_amount | currency | order_status |
country_code | postal_code | created_at | updated_at | source_system |
notes
--------+----------+-------------+-----------------+-------------+----------+------------+------------
+------------+----------+------------+--------------+----------+--------------+--------------+-------------
+---------------------+---------------------+---------------+-------------
3 | O1003 | C003 | Aysel Mammadova | aysel@email | phone123 |
02/07/2026 | 2026-07-05 | P003 | ten | 12O.50 | 125.00 | AZN |
DELIVERED | AZ | 1002 | 2026-07-02 11:00:00 | 2026-07-05 13:00:00 |
mobile | bad formats
(1 row)

dq_prac=# SELECT * FROM customer_orders_raw


dq_prac-# WHERE order_status = 'DELIVERED'
dq_prac-# AND ship_date IS NULL;
row_id | order_id | customer_id | customer_name | email | phone |
order_date | ship_date | product_id | quantity | unit_price | total_amount | currency |
order_status | country_code | postal_code | created_at | updated_at |
source_system | notes
--------+----------+-------------+---------------+-------------------+---------------+------------+-----------
+------------+----------+------------+--------------+----------+--------------+--------------+-------------
+---------------------+---------------------+---------------+----------------------------
15 | O1015 | C002 | Murad Hasanov | murad@[Link] |
+994501234567 | 2026-07-09 | | P004 |1 | 250.00 | 250.00 | AZN
| DELIVERED | AZ | AZ1011 | 2026-07-09 13:00:00 | 2026-07-09 14:00:00 |
mobile | delivered but no ship date
(1 row)

dq_prac=# ALTER TABLE customer_orders_raw DROP COLUMN notes;


ALTER TABLE
dq_prac=# SELECT * FROM customer_orders_raw
dq_prac-# WHERE quantity !~ '^[0-9]+$';
row_id | order_id | customer_id | customer_name | email | phone |
order_date | ship_date | product_id | quantity | unit_price | total_amount | currency |
order_status | country_code | postal_code | created_at | updated_at |
source_system
--------+----------+-------------+-----------------+--------------------+---------------+------------
+------------+------------+----------+------------+--------------+----------+--------------+--------------
+-------------+---------------------+---------------------+---------------
3 | O1003 | C003 | Aysel Mammadova | aysel@email | phone123 |
02/07/2026 | 2026-07-05 | P003 | ten | 12O.50 | 125.00 | AZN |
DELIVERED | AZ | 1002 | 2026-07-02 11:00:00 | 2026-07-05 13:00:00 |
mobile
5 | O1005 | C005 | Robert Smith | robert@[Link] | +994701112233
| 2026-07-03 | 2026-07-06 | P002 | -3 | 99999.99 | -299999.97 | AZN |
DELIVERED | AZ | AZ1004 | 2026-07-03 12:00:00 | 2026-07-06 16:00:00 |
web
(2 rows)

dq_prac=# SELECT * FROM customer_orders_raw


dq_prac-# WHERE currency NOT IN ('AZN', 'USD','EUR');
row_id | order_id | customer_id | customer_name | email | phone |
order_date | ship_date | product_id | quantity | unit_price | total_amount | currency |
order_status | country_code | postal_code | created_at | updated_at |
source_system
--------+----------+-------------+---------------+-------------------+---------------+------------+------------
+------------+----------+------------+--------------+----------+--------------+--------------+-------------
+---------------------+---------------------+---------------
8 | O1008 | C002 | Murad Hasanov | murad@[Link] | +994501234567
| 2026-07-07 | 2026-07-08 | P005 |1 | 80.00 | 80.00 | AZN$ | OK
| Azerbaijan | AZ1006 | 2026-07-07 14:00:00 | 2026-07-08 15:00:00 | telegram_bot
(1 row)

dq_prac=# SELECT *
dq_prac-# FROM customer_orders_raw
dq_prac-# WHERE order_status NOT IN ('NEW', 'PAID', 'SHIPPED', 'DELIVERED',
'CANCELLED', 'RETURNED');
row_id | order_id | customer_id | customer_name | email | phone |
order_date | ship_date | product_id | quantity | unit_price | total_amount | currency |
order_status | country_code | postal_code | created_at | updated_at |
source_system
--------+----------+-------------+---------------+-------------------+---------------+------------+------------
+------------+----------+------------+--------------+----------+--------------+--------------+-------------
+---------------------+---------------------+---------------
8 | O1008 | C002 | Murad Hasanov | murad@[Link] | +994501234567
| 2026-07-07 | 2026-07-08 | P005 |1 | 80.00 | 80.00 | AZN$ | OK
| Azerbaijan | AZ1006 | 2026-07-07 14:00:00 | 2026-07-08 15:00:00 | telegram_bot
(1 row)

dq_prac=#
dq_prac=# SELECT order_id, COUNT(*)
dq_prac-# FROM customer_orders_raw
dq_prac-# GROUP BY order_id
dq_prac-# HAVING COUNT(*) > 1;
order_id | count
----------+-------
O1001 | 2
(1 row)

dq_prac=# SELECT * FROM customer_orders_raw


dq_prac-# WHERE order_id IN (
dq_prac(# SELECT order_id FROM customer_orders_raw
dq_prac(# GROUP BY order_id
dq_prac(# HAVING COUNT(*) > 1
dq_prac(# );
row_id | order_id | customer_id | customer_name | email | phone |
order_date | ship_date | product_id | quantity | unit_price | total_amount | currency |
order_status | country_code | postal_code | created_at | updated_at |
source_system
--------+----------+-------------+----------------+-------------------+---------------+------------+------------
+------------+----------+------------+--------------+----------+--------------+--------------+-------------
+---------------------+---------------------+---------------
1 | O1001 | C001 | Leyla Aliyeva | leyla@[Link] | +994501112233 |
2026-07-01 | 2026-07-03 | P001 |1 | 1200.00 | 1200.00 | AZN |
DELIVERED | AZ | AZ1000 | 2026-07-01 10:00:00 | 2026-07-03 12:00:00 |
web
4 | O1001 | C004 | Nigar Karimova | nigar@[Link] | +994551112233 |
2026-07-03 | 2026-07-04 | P004 |1 | 250.00 | 250.00 | AZN |
DELIVERED | AZ | AZ1003 | 2026-07-03 08:00:00 | 2026-07-04 10:00:00 |
web
(2 rows)

dq_prac=#
dq_prac=# SELECT column_name, data_type
dq_prac-# FROM information_schema.columns
dq_prac-# WHERE table_schema = '';
column_name | data_type
-------------+-----------
(0 rows)

dq_prac=# \dt dq_lab.*


List of tables
Schema | Name | Type | Owner
--------+---------------------+-------+----------
dq_lab | customer_orders_raw | table | postgres
dq_lab | valid_customers | table | postgres
dq_lab | valid_products | table | postgres
(3 rows)

dq_prac=# SELECT column_name, data_type


dq_prac-# FROM information_schema.columns
dq_prac-# WHERE table_schema = 'dq_lab'
dq_prac-# AND table_name = 'valid_customers';
column_name | data_type
---------------+-------------------
customer_id | character varying
customer_name | character varying
(2 rows)

dq_prac=# SELECT
dq_prac-# o.row_id,
dq_prac-# o.order_id,
dq_prac-# o.customer_id,
dq_prac-# o.customer_name AS order_customer_name,
dq_prac-# c.customer_name AS valid_customer_name
dq_prac-# FROM customer_order_raw AS o
dq_prac-# JOIN valid_customers AS c
dq_prac-# ON o.customer_id = c.customer_id
dq_prac-# WHERE o.customer_name <> c.customer_name;
ERROR: relation "customer_order_raw" does not exist
LINE 7: FROM customer_order_raw AS o
^
dq_prac=# SELECT
dq_prac-# o.row_id,
dq_prac-# o.order_id,
dq_prac-# o.customer_id,
dq_prac-# o.customer_name AS order_customer_name,
dq_prac-# c.customer_name AS valid_customer_name
dq_prac-# FROM customer_order_raw AS o
dq_prac-# JOIN valid_customers AS c
dq_prac-# ON o.customer_id = c.customer_id;;
ERROR: relation "customer_order_raw" does not exist
LINE 7: FROM customer_order_raw AS o
^
dq_prac=# SELECT
dq_prac-# o.row_id,
dq_prac-# o.order_id,
dq_prac-# o.customer_id,
dq_prac-# o.customer_name AS order_customer_name,
dq_prac-# c.customer_name AS valid_customer_name
dq_prac-# FROM customer_orders_raw AS o
dq_prac-# JOIN valid_customers AS c
dq_prac-# ON o.customer_id = c.customer_id;;
row_id | order_id | customer_id | order_customer_name | valid_customer_name
--------+----------+-------------+---------------------+---------------------
14 | O1014 | C001 | Leyla Aliyeva | Leyla Aliyeva
6 | O1006 | C001 | Leyla Aliyeva | Leyla Aliyeva
1 | O1001 | C001 | Leyla Aliyeva | Leyla Aliyeva
15 | O1015 | C002 | Murad Hasanov | Murad Hasanov
8 | O1008 | C002 | Murad Hasanov | Murad Hasanov
2 | O1002 | C002 | | Murad Hasanov
16 | O1016 | C003 | Aysel Mammadova | Aysel Mammadova
9 | O1009 | C003 | J0hn D@vis | Aysel Mammadova
3 | O1003 | C003 | Aysel Mammadova | Aysel Mammadova
10 | O1010 | C004 | Nigar Karimova | Nigar Karimova
4 | O1001 | C004 | Nigar Karimova | Nigar Karimova
12 | O1012 | C005 | Robert Smith | Robert Smith
11 | O1011 | C005 | Robert Smith | Robert Smith
5 | O1005 | C005 | Robert Smith | Robert Smith
(14 rows)
dq_prac=# SELECT *
dq_prac-# FROM customer_orders_raw
dq_prac-# WHERE created_at < '2026-07-01'
dq_prac-# OR order_date > '2026-07-09'
dq_prac-# OR order_date > '2026-0709';
row_id | order_id | customer_id | customer_name | email | phone |
order_date | ship_date | product_id | quantity | unit_price | total_amount | currency |
order_status | country_code | postal_code | created_at | updated_at |
source_system
--------+----------+-------------+----------------+-------------------+---------------+------------+------------
+------------+----------+------------+--------------+----------+--------------+--------------+-------------
+---------------------+---------------------+---------------
10 | O1010 | C004 | Nigar Karimova | nigar@[Link] | +994551112233
| 2024-01-01 | 2024-01-02 | P001 |1 | 1100.00 | 1100.00 | AZN |
DELIVERED | AZ | AZ1008 | 2024-01-01 08:00:00 | 2024-01-02 08:00:00 |
web
(1 row)

dq_prac=# FROM customer_orders_raw


dq_prac-# WHERE created_at < '2026-07-01';
ERROR: syntax error at or near "FROM"
LINE 1: FROM customer_orders_raw
^
dq_prac=# SELECT * FROM customer_orders_raw
dq_prac-# WHERE created_at < '2026-07-01';
row_id | order_id | customer_id | customer_name | email | phone |
order_date | ship_date | product_id | quantity | unit_price | total_amount | currency |
order_status | country_code | postal_code | created_at | updated_at |
source_system
--------+----------+-------------+----------------+-------------------+---------------+------------+------------
+------------+----------+------------+--------------+----------+--------------+--------------+-------------
+---------------------+---------------------+---------------
10 | O1010 | C004 | Nigar Karimova | nigar@[Link] | +994551112233
| 2024-01-01 | 2024-01-02 | P001 |1 | 1100.00 | 1100.00 | AZN |
DELIVERED | AZ | AZ1008 | 2024-01-01 08:00:00 | 2024-01-02 08:00:00 |
web
(1 row)

dq_prac=# SELECT
dq_prac-# MAX(created_at::timestamp) AS last_created_at
dq_prac-# FROM customer_orders_raw
dq_prac-# HAVING MAX(created_at::timestamp) < CURRENT_DATE;
last_created_at
-----------------
(0 rows)

dq_prac=# SELECT row_id, customer_name, created_at FROM customer_orders_raw;


row_id | customer_name | created_at
--------+------------------+---------------------
1 | Leyla Aliyeva | 2026-07-01 10:00:00
2| | 2026-07-02 09:10:00
3 | Aysel Mammadova | 2026-07-02 11:00:00
4 | Nigar Karimova | 2026-07-03 08:00:00
5 | Robert Smith | 2026-07-03 12:00:00
6 | Leyla Aliyeva | 2026-07-06 10:30:00
7 | Unknown Customer | 2026-07-07 10:00:00
8 | Murad Hasanov | 2026-07-07 14:00:00
9 | J0hn D@vis | 2026-07-08 09:00:00
10 | Nigar Karimova | 2024-01-01 08:00:00
11 | Robert Smith | 2026-07-08 10:00:00
12 | Robert Smith | 2026-07-08 10:05:00
13 | | 2026-07-09 10:00:00
14 | Leyla Aliyeva | 2026-07-09 12:00:00
15 | Murad Hasanov | 2026-07-09 13:00:00
16 | Aysel Mammadova | 2026-07-09 14:00:00
(16 rows)

You might also like