Group Rows With Conditions WHERE filters rows before grouping (i.e. GROUP BY) while HAVING filters rows after grouping. Create Table – Create table called adventurers CREATE TABLE adventurers ( – string variable name varchar(255), – integer variable age int, – string variable race varchar(255), – string variable weapon varchar(255) ) Insert Rows – Insert into the table adventurers INSERT …
PostgreSQL Example – How to Group Rows.
Examine A Query Create Table – Create table called adventurers CREATE TABLE adventurers ( – string variable name varchar(255), – integer variable age int, – string variable race varchar(255), – string variable weapon varchar(255) ) Insert Row – Insert into the table adventurers INSERT INTO adventurers (name, age, race, weapon) VALUES (‘Fjoak Doom-Wife’, 28, ‘Human’, …
Delete View Create Table – Create table called adventurers CREATE TABLE adventurers ( – string variable name varchar(255), – integer variable age int, – string variable race varchar(255), – string variable weapon varchar(255) ) Insert Rows – Insert into the table adventurers INSERT INTO adventurers (name, age, race, weapon) VALUES (‘Fjoak Doom-Wife’, 28, ‘Human’, ‘Axe’), …
Create View Create Table – Create table called adventurers CREATE TABLE adventurers ( – string variable name varchar(255), – integer variable age int, – string variable race varchar(255), – string variable weapon varchar(255) ) Insert Rows – Insert into the table adventurers INSERT INTO adventurers (name, age, race, weapon) VALUES (‘Fjoak Doom-Wife’, 28, ‘Human’, ‘Axe’), …
Create Subquery Create Table – Create table called adventurers CREATE TABLE adventurers ( – string variable name varchar(255), – integer variable age int, – string variable race varchar(255), – string variable weapon varchar(255) ) Insert Rows – Insert into the table adventurers INSERT INTO adventurers (name, age, race, weapon) VALUES (‘Fjoak Doom-Wife’, 28, ‘Human’, ‘Axe’), …
Create PostgreSQL Database With Python Preliminaries /* Load libraries */ from sqlalchemy import create_engine from sqlalchemy_utils import create_database, database_exists, drop_database /* Create PostgreSQL connection */ engine = create_engine(“postgres://localhost/notes_db”) /* Load sql_magic so we can write SQL in Jupyter Notebooks */ %load_ext sql_magic /* Setup SQL connection to the postgreSQL engine we created */ %config SQL.conn_name …
Create Column Index Create Table Of Elves – Create table called elves CREATE TABLE elves ( – string variable name varchar(255), – integer variable age int, – string variable race varchar(255), – string variable alive varchar(255) ) Insert Rows Into Elf Table INSERT INTO elves (name, age, race, alive) VALUES (‘Dallar Woodfoot’, 25, ‘Elf’, ‘Yes’), …
Count Unique Values Create Table – Create table called adventurers CREATE TABLE adventurers ( – string variable name varchar(255), – integer variable age int, – string variable race varchar(255), – string variable weapon varchar(255) ) Insert Rows – Insert into the table adventurers INSERT INTO adventurers (name, age, race, weapon) VALUES (‘Fjoak Doom-Wife’, 28, ‘Human’, …
Count Rows Create Table – Create table called adventurers CREATE TABLE adventurers ( – string variable name varchar(255), – integer variable age int, – string variable race varchar(255), – string variable weapon varchar(255) ) Insert Rows – Insert into the table adventurers INSERT INTO adventurers (name, age, race, weapon) VALUES (‘Fjoak Doom-Wife’, 28, ‘Human’, ‘Axe’), …