Filtering Data

Private: Mastering SQL using Postgresql Writing Basic SQL Queries Filtering Data

Let us understand how we can filter the data as part of our queries.

We use WHERE clause to filter the data.
All comparison operators such as =, !=, >, <, <=, >= etc can be used to compare a column or expression or literal with another column or expression or literal.
We can use operators such as LIKE with % or ~ with regular expressions for pattern matching.
Boolean OR and AND can be performed when we want to apply multiple conditions.
- Get all orders with order_status equals to COMPLETE or CLOSED. We can also use IN operator.
- Get all orders from month 2014 January with order_status equals to COMPLETE or CLOSED
We can also use BETWEEN along with AND to compare a column or expression against range of values.
We need to use IS NULL and IS NOT NULL to compare against null values.

%load_ext sql

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

%%sql 

SELECT * FROM orders 
WHERE order_status = 'COMPLETE' 
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

order_id	order_date	order_customer_id	order_status
3	2013-07-25 00:00:00	12111	COMPLETE
5	2013-07-25 00:00:00	11318	COMPLETE
6	2013-07-25 00:00:00	7130	COMPLETE
7	2013-07-25 00:00:00	4530	COMPLETE
15	2013-07-25 00:00:00	2568	COMPLETE
17	2013-07-25 00:00:00	2667	COMPLETE
22	2013-07-25 00:00:00	333	COMPLETE
26	2013-07-25 00:00:00	7562	COMPLETE
28	2013-07-25 00:00:00	656	COMPLETE
32	2013-07-25 00:00:00	3960	COMPLETE

%sql SELECT count(1) FROM orders

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

count
68883

%%sql 

SELECT count(1) 
FROM orders
WHERE order_status = 'COMPLETE'

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

count
22899

%%sql 

SELECT DISTINCT order_status
FROM orders
WHERE order_status = 'COMPLETE'

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

order_status
COMPLETE

%%sql

SELECT DISTINCT order_status
FROM orders

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
9 rows affected.

order_status
COMPLETE
ON_HOLD
PENDING_PAYMENT
PENDING
CLOSED
CANCELED
PROCESSING
PAYMENT_REVIEW
SUSPECTED_FRAUD

%%sql 

SELECT * FROM orders 
WHERE order_status IN ('COMPLETE', 'CLOSED') 
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

order_id	order_date	order_customer_id	order_status
1	2013-07-25 00:00:00	11599	CLOSED
3	2013-07-25 00:00:00	12111	COMPLETE
4	2013-07-25 00:00:00	8827	CLOSED
5	2013-07-25 00:00:00	11318	COMPLETE
6	2013-07-25 00:00:00	7130	COMPLETE
7	2013-07-25 00:00:00	4530	COMPLETE
12	2013-07-25 00:00:00	1837	CLOSED
15	2013-07-25 00:00:00	2568	COMPLETE
17	2013-07-25 00:00:00	2667	COMPLETE
18	2013-07-25 00:00:00	1205	CLOSED

%%sql

SELECT count(1) FROM orders 
WHERE order_status IN ('COMPLETE', 'CLOSED')

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

count
30455

%%sql 

SELECT count(1) FROM orders 
WHERE order_status = 'COMPLETE' OR order_status = 'CLOSED'

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

count
30455

%%sql

SELECT * FROM orders
WHERE order_date = '2014-01-01'
LIMIT 3

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
3 rows affected.

order_id	order_date	order_customer_id	order_status
25876	2014-01-01 00:00:00	3414	PENDING_PAYMENT
25877	2014-01-01 00:00:00	5549	PENDING_PAYMENT
25878	2014-01-01 00:00:00	9084	PENDING

Note

This query will not work as LIKE cannot be used to compare against columns with date data type

%%sql

SELECT * FROM orders
WHERE order_date LIKE '2014-01%'
LIMIT 3

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
(psycopg2.errors.UndefinedFunction) operator does not exist: timestamp without time zone ~~ unknown
LINE 2: WHERE order_date LIKE '2014-01%'
                         ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

[SQL: SELECT * FROM orders
WHERE order_date LIKE '2014-01%%'
LIMIT 3]
(Background on this error at: http://sqlalche.me/e/13/f405)

%%sql

SELECT * FROM orders 
WHERE order_status IN ('COMPLETE', 'CLOSED')
    AND to_char(order_date, 'yyyy-MM-dd') LIKE '2014-01%'
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

order_id	order_date	order_customer_id	order_status
25882	2014-01-01 00:00:00	4598	COMPLETE
25888	2014-01-01 00:00:00	6735	COMPLETE
25889	2014-01-01 00:00:00	10045	COMPLETE
25891	2014-01-01 00:00:00	3037	CLOSED
25895	2014-01-01 00:00:00	1044	COMPLETE
25897	2014-01-01 00:00:00	6405	COMPLETE
25898	2014-01-01 00:00:00	3950	COMPLETE
25899	2014-01-01 00:00:00	8068	CLOSED
25900	2014-01-01 00:00:00	2382	CLOSED
25901	2014-01-01 00:00:00	3099	COMPLETE

%%sql

SELECT count(1) FROM orders 
WHERE order_status IN ('COMPLETE', 'CLOSED')
    AND to_char(order_date, 'yyyy-MM-dd') LIKE '2014-01%'

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

count
2544

%%sql

SELECT * FROM orders 
WHERE order_status IN ('COMPLETE', 'CLOSED')
    AND to_char(order_date, 'yyyy-MM') = '2014-01'
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

order_id	order_date	order_customer_id	order_status
25882	2014-01-01 00:00:00	4598	COMPLETE
25888	2014-01-01 00:00:00	6735	COMPLETE
25889	2014-01-01 00:00:00	10045	COMPLETE
25891	2014-01-01 00:00:00	3037	CLOSED
25895	2014-01-01 00:00:00	1044	COMPLETE
25897	2014-01-01 00:00:00	6405	COMPLETE
25898	2014-01-01 00:00:00	3950	COMPLETE
25899	2014-01-01 00:00:00	8068	CLOSED
25900	2014-01-01 00:00:00	2382	CLOSED
25901	2014-01-01 00:00:00	3099	COMPLETE

%%sql

SELECT count(1) FROM orders 
WHERE order_status IN ('COMPLETE', 'CLOSED')
    AND to_char(order_date, 'yyyy-MM') = '2014-01'

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

count
2544

%%sql

SELECT count(1) FROM orders 
WHERE order_status IN ('COMPLETE', 'CLOSED')
    AND to_char(order_date, 'yyyy-MM-dd') ~ '2014-01'

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

count
2544

%%sql

SELECT count(1), min(order_date), max(order_date), count(DISTINCT order_date) 
FROM orders 
WHERE order_status IN ('COMPLETE', 'CLOSED')
    AND order_date BETWEEN '2014-01-01' AND '2014-03-31'

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

count	min	max	count_1
7594	2014-01-01 00:00:00	2014-03-31 00:00:00	89

%%sql

SELECT DISTINCT order_date
FROM orders
WHERE to_char(order_date, 'yyyy-MM') LIKE '2014-03%'
ORDER BY order_date

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
30 rows affected.

order_date
2014-03-01 00:00:00
2014-03-02 00:00:00
2014-03-03 00:00:00
2014-03-04 00:00:00
2014-03-05 00:00:00
2014-03-06 00:00:00
2014-03-07 00:00:00
2014-03-08 00:00:00
2014-03-10 00:00:00
2014-03-11 00:00:00
2014-03-12 00:00:00
2014-03-13 00:00:00
2014-03-14 00:00:00
2014-03-15 00:00:00
2014-03-16 00:00:00
2014-03-17 00:00:00
2014-03-18 00:00:00
2014-03-19 00:00:00
2014-03-20 00:00:00
2014-03-21 00:00:00
2014-03-22 00:00:00
2014-03-23 00:00:00
2014-03-24 00:00:00
2014-03-25 00:00:00
2014-03-26 00:00:00
2014-03-27 00:00:00
2014-03-28 00:00:00
2014-03-29 00:00:00
2014-03-30 00:00:00
2014-03-31 00:00:00

%%sql

DROP TABLE IF EXISTS users

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

[]

%%sql

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    user_first_name VARCHAR(30) NOT NULL,
    user_last_name VARCHAR(30) NOT NULL,
    user_email_id VARCHAR(50) NOT NULL,
    user_email_validated BOOLEAN DEFAULT FALSE,
    user_password VARCHAR(200),
    user_role VARCHAR(1) NOT NULL DEFAULT 'U', --U and A
    is_active BOOLEAN DEFAULT FALSE,
    create_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

[]

%%sql

INSERT INTO users (user_first_name, user_last_name, user_email_id)
VALUES ('Donald', 'Duck', 'donald@duck.com')

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

[]

%%sql

INSERT INTO users (user_first_name, user_last_name, user_email_id, user_role, is_active)
VALUES ('Mickey', 'Mouse', 'mickey@mouse.com', 'U', true)

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

[]

%%sql

INSERT INTO users 
    (user_first_name, user_last_name, user_email_id, user_password, user_role, is_active) 
VALUES 
    ('Gordan', 'Bradock', 'gbradock0@barnesandnoble.com', 'h9LAz7p7ub', 'U', true),
    ('Tobe', 'Lyness', 'tlyness1@paginegialle.it', 'oEofndp', 'U', true),
    ('Addie', 'Mesias', 'amesias2@twitpic.com', 'ih7Y69u56', 'U', true)

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
3 rows affected.

[]

%%sql

SELECT * FROM users

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
5 rows affected.

user_id	user_first_name	user_last_name	user_email_id	user_email_validated	user_password	user_role	is_active	create_ts	last_updated_ts
1	Donald	Duck	donald@duck.com	False	None	U	False	2020-11-14 15:38:53.352984	2020-11-14 15:38:53.352984
2	Mickey	Mouse	mickey@mouse.com	False	None	U	True	2020-11-14 15:38:54.369402	2020-11-14 15:38:54.369402
3	Gordan	Bradock	gbradock0@barnesandnoble.com	False	h9LAz7p7ub	U	True	2020-11-14 15:38:55.260250	2020-11-14 15:38:55.260250
4	Tobe	Lyness	tlyness1@paginegialle.it	False	oEofndp	U	True	2020-11-14 15:38:55.260250	2020-11-14 15:38:55.260250
5	Addie	Mesias	amesias2@twitpic.com	False	ih7Y69u56	U	True	2020-11-14 15:38:55.260250	2020-11-14 15:38:55.260250

Note

This will not return any thing and not the correct way to compare against NULL. NULL is specially treated by databases and it is not same as empty string.

%%sql

SELECT * FROM users
WHERE user_password = NULL

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
0 rows affected.

user_id	user_first_name	user_last_name	user_email_id	user_email_validated	user_password	user_role	is_active	create_ts	last_updated_ts

%%sql

SELECT * FROM users
WHERE user_password IS NULL

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
2 rows affected.

user_id	user_first_name	user_last_name	user_email_id	user_email_validated	user_password	user_role	is_active	create_ts	last_updated_ts
1	Donald	Duck	donald@duck.com	False	None	U	False	2020-11-14 15:38:53.352984	2020-11-14 15:38:53.352984
2	Mickey	Mouse	mickey@mouse.com	False	None	U	True	2020-11-14 15:38:54.369402	2020-11-14 15:38:54.369402

%%sql

SELECT * FROM users
WHERE user_password IS NOT NULL

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
3 rows affected.

user_id	user_first_name	user_last_name	user_email_id	user_email_validated	user_password	user_role	is_active	create_ts	last_updated_ts
3	Gordan	Bradock	gbradock0@barnesandnoble.com	False	h9LAz7p7ub	U	True	2020-11-14 15:38:55.260250	2020-11-14 15:38:55.260250
4	Tobe	Lyness	tlyness1@paginegialle.it	False	oEofndp	U	True	2020-11-14 15:38:55.260250	2020-11-14 15:38:55.260250
5	Addie	Mesias	amesias2@twitpic.com	False	ih7Y69u56	U	True	2020-11-14 15:38:55.260250	2020-11-14 15:38:55.260250

Previous Topic

Back to Lesson

Next Topic