Let us understand different aspects of projecting data. We primarily use SELECT to project the data.
- We can project all columns using * or some columns using column names.
- We can provide aliases to a column or expression using AS in SELECT clause.
- DISTINCT can be used to get the distinct records from selected columns. We can also use DISTINCT * to get unique records using all the columns.
- As part of SELECT clause, we can have aggregate functions such as count, sum, etc.
%load_ext sql
%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db
%sql SELECT * FROM orders LIMIT 10
%%sql
SELECT * FROM information_schema.columns
WHERE table_catalog = 'itversity_retail_db'
AND table_name = 'orders'
%%sql
SELECT order_customer_id, order_date, order_status
FROM orders
LIMIT 10
%%sql
SELECT order_customer_id, to_char(order_date, 'yyyy-MM'), order_status
FROM orders
LIMIT 10
%%sql
SELECT order_customer_id, to_char(order_date, 'yyyy-MM') AS order_month, order_status
FROM orders
LIMIT 10
%%sql
SELECT DISTINCT to_char(order_date, 'yyyy-MM') AS order_month
FROM orders
%sql SELECT count(1) FROM orders
%%sql
SELECT count(DISTINCT to_char(order_date, 'yyyy-MM')) AS distinct_month_count
FROM orders
Hands-On Tasks
Here are some hands-on tasks for you to practice:
- Select all the columns from the ‘orders’ table.
- Select only the ‘order_customer_id’, ‘order_date’, and ‘order_status’ columns from the ‘orders’ table.
Conclusion
In this article, we have covered the basics of selecting or projecting data using the SELECT command. We looked at various aspects such as projecting all columns, using aliases, distinct values, and aggregate functions. Practice these concepts and engage with the community for further learning.