DQL – Data Query Language¶
Let us go through the details related to DQL.
Here are some of the queries we typically run against database tables.
Selecting or projecting the data
Filtering the data
Performing aggregations
Joining multiple tables
Sorting the data
Let us run some queries to be a bit comfortable with the queries.
%load_ext sql
%env DATABASE_URL=postgresql://itversity_sms_user:itversity!23@localhost:5433/itversity_sms_db
%sql TRUNCATE TABLE users
%%sql result_set <<
INSERT INTO users (user_first_name, user_last_name, user_email_id, user_role, created_dt)
VALUES ('Gordan', 'Bradock', 'gbradock0@barnesandnoble.com', 'A', '2020-01-10')
%%sql result_set <<
INSERT INTO users (user_first_name, user_last_name, user_email_id, created_dt)
VALUES
('Tobe', 'Lyness', 'tlyness1@paginegialle.it', '2020-02-10'),
('Addie', 'Mesias', 'amesias2@twitpic.com', '2020-03-05'),
('Corene', 'Kohrsen', 'ckohrsen3@buzzfeed.com', '2020-04-15'),
('Darill', 'Halsall', 'dhalsall4@intel.com', '2020-10-10')
%sql SELECT * FROM users
%sql SELECT * FROM users WHERE user_role = 'A' AND created_dt BETWEEN '2020-01-01' AND '2020-03-31'
%sql SELECT user_first_name, user_last_name, user_email_id, user_role FROM users WHERE user_role = 'A' AND created_dt BETWEEN '2020-01-01' AND '2020-03-31'
%sql SELECT * FROM users WHERE user_role != 'A' AND created_dt BETWEEN '2020-01-01' AND '2020-03-31'
%sql SELECT user_role, count(1) FROM users GROUP BY user_role ORDER BY user_role