PostgreSQL Flashcards
What steps do you want for cleaning string data?
Restrict capitalization in column names
Remove extra divider spaces in column names
make column names uniform
INITCAP()
SELECT INITCAPT(column)
Output: Hello Friend!
Fixes capitalization
REPLACE()
SELECT REPLACE(column, string_to_replace, replacement)
SELECT REPLACE(streets, ‘Main Street’, ‘St’);
Output: Main St
LPAD(input_string, length, fill_value)
Left-Pad a Column
SELECT LPAD(‘column’, 7, ‘X’);
OUTPUT: XXXX123
Prepending text values to a string. The fourth value has to be a string.
SELECT LPAD(‘123’, 5, ‘0’); – Output: ‘00123’
Like
Like is used for pattern matching using wildcards such as % or _
SELECT *
FROM employees
WHERE firstname LIKE ‘Ali%’ or firstname LIKE ‘_li’
%
Matches 0 or more characters
_
Matches exactly 1 character
Can use multiple _ to represent more than 1 character
REGEXP_REPLACE()
REGEXP_REPLACE(column, ‘regrex’, replacement, ‘g’)
‘g’ = global
DIFFERENCE()
DIFFERENCE is used to compare the SOUNDEX values of two strings and returns a score between 0 and 4:
DIFFERENCE(vehicle_color, ‘GRAY’) = 4;
SOUNDEX()
SOUNDEX is a phonetic algorithm used to match words or strings that sound similar in English. It converts a word to a code based on its pronunciation.
Example:
SOUNDEX(‘GRAY’) → G600
SOUNDEX(‘GREY’) → G600
EXPLAIN ANALYZE
can be used to provide insight into the execution plan of a SQL query. actually runs the query. use with caution.
EXPLAIN ANALYZE
SELECT * FROM patient WHERE age = 60;
EXPLAIN
the EXPLAIN command alone can be used to review the planned execution path without the associated overhead of query execution.
EXPLAIN
SELECT * FROM patient WHERE age = 60;
INDEX
CREATE INDEX index_title
ON table(column);
example:
CREATE INDEX idx_patient_age_cholesterol_restingbp
ON patient(age,cholesterol,restingbp);
Visualize how to query to confirm index exists
SELECT * FROM pg_indexes
WHERE tablename = ‘patient’
AND indexname = ‘idx_patient_age’;
Visualize how to extract a row data from a column, and create a new table, inserting the column data from an old table.
SELECT DISTINCT(chestpaintype) FROM patient;
CREATE TABLE IF NOT EXISTS chestpain (
id serial PRIMARY KEY,
type TEXT
);
INSERT INTO chestpain(type)
SELECT DISTINCT(chestpaintype) FROM patient;
Visualize how to alter a table with a foreign key
ALTER TABLE patient
ADD CONSTRAINT patient_chestpain_fk
FOREIGN KEY (chestpaintypeid) REFERENCES chestpain(id);
Visualize how to use INFORMATION_SCHEMA
is used to retrieve metadata about the columns of a table from the database.
Example:
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = ‘public’ #this is a default
AND table_name = ‘table’;
Visualize how to check for columns using INFORMATION_SCHEMA
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = ‘integer’;
Database transactions
A transaction is like a package of operations that are grouped together. Either all operations within the transaction succeed (committed) or none of them take effect (rolled back). It ensures ACID (Atomicity, Consistency, Isolation, Durability).
How do you create a database transaction?
Use BEGIN and COMMIT
BEGIN starts the transaction, and all statements are part until transaction is ended with COMMIT.
What is ACID?
Atomicity, Consistency, Isolation, Durability
ROLLBACK
If an error occurs or if you decide not to save the changes, you can use ROLLBACK instead of COMMIT.
ROLLBACK undoes all changes made during the transaction.
SERIAL
Create surrogate keys
CREATE TABLE table_name(
column_number SERIAL PRIMARY KEY
)
How to designate a foregin key
CREATE TABLE table_name(
column_name INT REFERENCES table(column) ON DELETE CASCADE)
When the column is deleted in the reference table, it automatically deletes when using ON DELETE CASCADE.
How do you use a foreign key explicity and set to NULL on delete?
CREATE TABLE orders (
ord_id SERIAL PRIMARY KEY,
prod_sku INT,
FOREIGN KEY (prod_sku) REFERENCES product(prod_sku) ON DELETE SET NULL
);
Break up a table to normalize it?
INSERT INTO normalized_table (col 1, col 2, col3)
SELECT DISTINCT col1,col2,col3 FROM big_table;
How do you update an existing constraint on a column?
1️⃣ Find the existing constraint name
SELECT conname FROM pg_constraint WHERE conrelid = ‘orders’::regclass AND contype = ‘c’;
2️⃣ Drop the old constraint
ALTER TABLE orders DROP CONSTRAINT chk_ord_priority;
3️⃣ Add the updated constraint
ALTER TABLE orders ADD CONSTRAINT chk_ord_priority CHECK (ord_priority IN (‘L’, ‘M’, ‘H’, ‘C’, ‘X’));
4️⃣ Fix invalid values if needed
UPDATE orders SET ord_priority = ‘L’ WHERE ord_priority NOT IN (‘L’, ‘M’, ‘H’, ‘C’, ‘X’);
What is pg_constraint?
pg_constraint is a system catalog table in PostgreSQL that stores all constraints for all tables in the database
What are the constraints stored in pg_constraint?
Primary Keys (p)
Foreign Keys (f)
Unique Constraints (u)
Check Constraints (c)
Importing data into PostgreSQL
- create the table
- Use the copy command
COPY my_table (col1, col2, col3)
FROM ‘/path/to/file.csv’
DELIMITER ‘,’
CSV HEADER;
INTEGER GENERATED ALWAY AS IDENTITY
CREATE TABLE table_name (
col1 INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 100 INCREMENT BY 10) PRIMARY KEY,
col2 VARCHAR(100)
);
What happen when you join two tables where the column names are the same?
You have to use dot notation?
ex.
– Select name fields (with alias) and region
SELECT cities.name AS city , countries.name AS country, region
FROM cities
INNER JOIN countries
ON cities.country_code = countries.code;
USING
a join operations when both tables share a column with the same name.
Instead of:
SELECT * FROM table1 JOIN table2 ON table1.col = table2.col;
you can write:
SELECT * FROM table1 JOIN table2 USING(col);
Visualize using multiple joins
SELECT A., B., C.*
FROM A
JOIN B ON A.id = B.a_id
JOIN C ON B.id = C.b_id;
T or F: You can mix different types of joins (INNER, LEFT, RIGHT, FULL) depending on your needs.
True.
SELECT A., B., C.*
FROM A
LEFT JOIN B ON A.id = B.a_id
INNER JOIN C ON B.id = C.b_id;
T or F: Order affects your result when using an INNER or LEFT/RIGHT join.
False. For inner joins, the order is generally interchangeable (thanks to the associative property of inner joins). However, for outer joins, the order can affect which rows are retained.
Visualize using on multiple joins
SELECT *
FROM A
JOIN B USING(common_column)
JOIN C USING(common_column);
Using AND for multiple joins
SELECT*
FROM left_table
INNER JOIN right_table
ON left_table.id = right_table.id
AND left_table.date = right_table.date
T or F: When converting a Left to a Right Join, you only have to change the join.
False. when converting a LEFT JOIN to a RIGHT JOIN, change both the type of join and the order of the tables to get equivalent results. You would get different results if you only changed the table order. The order of fields you are joining ON still does not matter.
What query
Removes dead tuples (deleted rows still taking up space).
Reclaims disk space.
Updates query statistics at the same time
VACUUM ANALYZE;
How do you update statistics?
ANALYZE;
Where do you place the foreign key on a table?
1-to-1 (1:1): In the table that depends on the other.
1-to-Many (1:M): In the table on the “Many” side.
Many-to-Many (M:M): Create a junction table with two foreign keys.
How do you decide if you should add indexes onto a table?
If it has low sparsity (fewer unique values, meaning more repeated values).
If it is frequently used in joins and aggregations.
UNION
UNION takes two tables as input, and returns all records from both tables, excluding duplicates
SELECT* FROM left_table
UNION
SELECT* FROM right_table;
UNION ALL
UNION ALL takes two tables and returns all records from both tables, including duplicates
SELECT* FROM left_table
UNION ALL
SELECT* FROM right_table;
Begin your query by selecting all fields from economies2015.
Create a second query that selects all fields from economies2019.
Perform a set operation to combine the two queries you just created, ensuring you do not return duplicates.
– Select all fields from economies2015
SELECT * FROM economies2015
– Set operation
UNION
– Select all fields from economies2019
SELECT * FROM economies2019
ORDER BY code, year;
INTERSECT
INTERSECT is a set operation that returns only the common rows between two queries without duplicates
SELECT col FROM table1
INTERSECT
SELECT col FROM table2;
What is the difference between JOIN and INTERSECT
INTERSECT requires the tables to have the same number of columns and matching data types. While JOIN does not require queries to have the same columns or data types.
EXCEPT
Returns only the tables in the left table that aren’t in the right table.
SELECT col FROM table1
EXCEPT
SELECT col FROM table2;
Semi Join
it only checks for existence of the columns and data in another table. Think of it as a JOIN that doesn’t include extra columns from the second table.
What would this Semi Join return?
SELECT e.name, e.department
FROM employees e
WHERE EXISTS (
SELECT 1 FROM managers m
WHERE e.manager_id = m.id
);
Selects employees who have a manager in the managers table.
Returns only data from employees (no extra columns from managers).
If a manager exists, the row is included.
Anti-Join
returns rows from the first table that have NO match in the second table.
SELECT e.name, e.department
FROM employees e
WHERE NOT EXISTS (
SELECT 1 FROM managers m
WHERE e.manager_id = m.id
);
Subquery
subquery is a query inside another query.
It is enclosed in parentheses.
It can return a single value (scalar subquery) or multiple rows (table subquery).
Visualize a subquery with IN (Semi Join)
SELECT name, department
FROM employees
WHERE department IN (
SELECT dept_name FROM departments
);
Visualize a subquery NOT IN (anti join
SELECT name, department
FROM employees
WHERE department NOT IN (
SELECT dept_name FROM departments
);
Identify the languages spoken in the Middle East. The languages table contains information about languages and countries, but not the region. Use a semi join on the languages and countries table to get the “name” for the languages spoken in the “region” Middle East
SELECT DISTINCT name
FROM languages
– Add syntax to use bracketed subquery below as a filter
WHERE code IN
(SELECT code
FROM countries
WHERE region = ‘Middle East’)
ORDER BY name;
Write a query that returns the names from the countries table where the continent is Oceania that are not in the currencies table using the country code
SELECT code, name
FROM countries
WHERE continent = ‘Oceania’
– Filter for countries not included in the bracketed subquery
AND code NOT IN
(SELECT code
FROM currencies);
