SQL - Advanced

Question 1

What must you start-up in order to work with postgres?

Answer 1

PgAmin 4 – it’s in your programs - you can start it from the search bar

Question 2

Why are datatypes important?

Answer 2

data integrity - they ensure the right data is stored in each column

efficiency - process data quickly and store it with less space

when creating a new table you have to specify the datatype for each column and any relevant properties for that datatype.

Question 3

What are the 8 datatypes Luke likes to use?

Answer 3

INT - integer, whole number
BOOLEAN - true/false or null
NUMERIC (precision, scale)- can include decimals etc.
DATE
TIMESTAMP - when data was saved, changed etc.
TIMESTAMP with TIME ZONE
VARCHAR - strings w/character limit
TEXT - strings - no character limit

Question 4

how many datatypes are there?

Answer 4

Probably hundreds. You can view all of them in the Postgresql documentation

Question 5

What are the properties of the NUMERIC datatype?

Answer 5

NUMERIC data type has two properties: precision & scale

• Precision is how many total digits the number has
• Scale is how many digits to the right of the decimal

Example: 623.859 - this number has a precision of 6 and a scale of 3

This would be written:
NUMERIC(6,3)

Question 6

TEXT vs. VARCHAR

Answer 6

TEXT - variable length strings of unlimited length - often used for text that doesn’t fit within the VARCHAR constraints

VARCHAR - has a ‘character limit’ property. Can store strings of any length up to that character limit.

Looks like the character limit is defined by the user. The character limit is represented by N in the following example:
VARCHAR(N)

Question 7

What can you tell me about the BOOLEAN datatype?

Answer 7

accepts the values of true, false or null – he was using 0 and 1 and said this is effectively true or false

Question 8

DATE vs. TIMESTAMP vs. TIMESTAMP w/ TIMEZONE

Answer 8

DATE - date w/out time
format: YYYY-MM-DD

TIMESTAMP - date with time - can include seconds depending on precision
format: YYYY-MM-DD HH-MI-SS

TIMESTAMP w/TIMEZONE -
Adjusts stored time based on the timezone
FORMAT:YYYY-MM-DD HH-MI-SS+00:00
I think the 00:00 would be HH:MI

Question 9

What statement allows you to create a table?

Answer 9

CREATE TABLE

Question 10

What statement allows you to add data columns to your table

Answer 10

INSERT INTO

Question 11

What statement allows you to delete a table

Answer 11

DROP TABLE

Question 12

What are the 4 main commands you can use with a table?

Answer 12

CREATE TABLE,
INSERT INTO - add columns
ALTER TABLE add columns, rename column, change datatype of column, delete a column
DROP TABLE - delete table

Question 13

What are the 4 main commands INSIDE the ALTER TABLE command?`

Answer 13

ADD - add a column
RENAME COLUMN
ALTER COLUMN - change the datatype of a column
DROP COLUMN - delete a column

Question 14

After you’ve created the database shell and you’re ready to work with your tables, what do you do?

Answer 14

1. Go into pgAdmin4 and make sure your connection to the desired database is up and running - just click on it and it will start up
2. Go into VS Code and click on databases (cylinder on left) and click on the desired database and make sure it has the name of your desired database on the very bottom - in the footer of the window.
3. Click on the database name in the left pane - and then click on the new sql file icon - which is has a plus sign with “SQL” next to it.

Question 15

How do you initially create a table with columns?

Answer 15

CREATE TABLE table_name (
column_name datatype,
column_name datatype,
. . .
);

Question 16

how do you insert data into a table?

Answer 16

INSERT INTO table_name (column_name1, column_name2, column_name3 . . . . )
VALUES (value1, value2, . . . )

Question 17

How do you alter a table?

Answer 17

ALTER TABLE table_name

ADD column_name datatype;

RENAME COLUMN column_name TO new_name;

ALTER COLUMN column_name TYPE datatype;

DROP COLUMN column_name;

Not sure if you can do multiple of these separated by a comma, or if you can only do one action per ALTER TABLE command.

Question 18

How do you update a table?

Answer 18

UPDATE table_name
SET column_name = ‘new_value’
WHERE condition;

in this instance you use WHERE to filter which rows to update based on a condition.

Example:
UPDATE job_applied
SET contact = ‘Prince Harry’
WHERE job_id = 3;

Question 19

How do you rename a column?

Answer 19

ALTER TABLE table_name
RENAME COLUMN column_name TO new_name;

Question 20

How do you change the datatype of a column?

Answer 20

ALTER TABLE table_name
ALTER COLUMN column_name TYPE new_data_type;

example:
-ALTER TABLE job_applied
–ALTER COLUMN contact_name TYPE TEXT;

Question 21

How do you drop (delete) a column?

Answer 21

ALTER TABLE table_name
DROP COLUMN column_name

Always check and double check before you drop anything!!!

Question 22

How do you drop a table?

Answer 22

DROP TABLE table_name

Always be extremely careful when you do this! Check and double check!

Question 23

How do you create a database (3 steps)

Answer 23

1. download CSV and SQL files
2. create tables in the database
3. load data into tables

Question 24

Primary Key vs. Foreign Key

Answer 24

A primary key is a unique identifier for each row within a table. Every table in the database has a primary key.

A foreign key is a column in one table that references the primary key in another table. It creates a link between the two tables.

Example
Customer_ID could be the primary key in the Customer table. Order_ID could be the primary key in the Orders table.
A Customer_ID column in the orders table would link each order to a customer. Customer_ID would be a foreign key in the ORDERS table and Order_ID could be a foreign key in the Customers table.

in VS Code - left side bar - gold key is primary key and gray key signifies the Foreign Key.

Question 25

What is a primary key in a database?

Answer 25

A primary key is a unique identifier for each record (row) in a table. --It ensures that each row is distinct and prevents duplicate entries -- Primary keys cannot contain NULL value - every row must have a valid, unique value -- allow for efficient data retrieval -- enable relationships between tables in a database Usually a primary key is composed of 1 column, but it can be composed of multiple columns which is called a composite key

Question 26

What is a foreign key in a database

Answer 26

A foreign key in a table is a primary key from another table. You add a foreign key to a table so that you can relate the two tables (part of the reason it's called a relational database). It establishes a link between the two tables. ibid - a foreign key is a column (or set of columns) in one table that refers to the primary key of another table. Foreign keys are CRUCIAL for performing JOIN operations in SQL

Question 27

How do you use the EXTRACT keyword? And what does it do?

Answer 27

EXTRACT gets a field (like year, month or day) from a date/time value Example: SELECT EXTRACT(MONTH FROM column_name) AS new_column_name FROM table_name

Question 28

How do you take data from a large table and put a subsection of that data into a smaller table?

Answer 28

This gets all the job postings from the month of January and puts it in a new table. CREATE TABLE january_jobs AS SELECT * FROM table_name WHERE EXTRACT (MONTH FROM column_name) = 1;

Question 29

what is CASE expression?

Answer 29

CASE expression is a way to apply conditional logic within your SQL queries. Similar to an IF statement in other programming languages. Example: SELECT CASE WHEN column_name = 'value1' THEN 'description for value 1' WHEN column_name = 'value2' THEN 'description for value 2' ELSE 'output if condition is not true' END AS new_column_you're_creating FROM table_name; CASE - begins the expression WHEN - specifies conditions to look at THEN - what to do when the condition is true ELSE - provides output if none of the conditions are met You could use this to find jobs that are remote, or find jobs that are local and disregard the rest.

Question 30

using a case statement, how would you group job postings into 3 locations: local, remote and everything else?

Answer 30

SELECT job_title_short, job_location, CASE WHEN job_location = 'New York, NY' THEN 'local' WHEN job_location = 'Anywhere' THEN 'Remote' ELSE 'Move required' END AS location_category FROM job_postings_fact

Question 31

What is a CTE and what does CTE stand for?

Answer 31

Common Table Expressions They are used for organizing and simplifying complex queries. Breaks the query into smaller more manageable parts. - Define a temporary result set that you can reference - Must use the keyword WITH - enclosed in parenthesis - Can reference the temporary result set within a SELECT, INSERT, UPDATE, DELETE statement Example - this selects all the jobs postings posted in January and puts it in it's own "table" (january_jobs) but it's really a temporary result set. So you can use it like a table, but you don't have to create another table: WITH january_jobs AS ( SELECT * FROM job_postings_fact WHERE EXTRACT (MONTH FROM job_posted_date) = 1 ) SELECT * FROM january_jobs

Question 32

What is a subquery?

Answer 32

A subquery, also known as a nested query or inner query, is used to organize and simplify complex queries - Breaks the query into smaller more manageable parts. - A subquery is a query that's NESTED inside of another query. - It can be used in the SELECT, FROM and WHERE and HAVING clauses. - allows you to use the result of one query as input in another query. -- It's executed first and then those results are passed to the outer query. -- Can be used when you want to perform a calculation before the main query performs it's calculation. kind like order of operations - does the subquery first, then the outer query. Example he gave: If you wanted to find all the jobs where there was not a degree mentioned and then you wanted to know the name of the company that listed that job posting. Since the subquery makes a 'temporary result set' you may be able to use it like a (temporary) table. Create the subquery and then put it in the FROM clause like you would a table.

Question 33

What is an example of a CTE?

Answer 33

This query finds the skills that are mentioned most often in job postings and orders them by most mentioned to least mentioned. Then it matches the skill_id with the skill name using a join. Because a CTE makes a new temporary collection of data, you put the CTE in the FROM clause, just like you were using it as a table. For example, below I make a 'temporary result set' named skill_counts and I use it like a table, the left table, when I make a join. WITH skill_counts AS (SELECT COUNT (skill_id) AS molly, skill_id FROM skills_job_dim GROUP BY skill_id ORDER BY molly DESC) SELECT skill_counts.molly, skill_counts.skill_id, skills_dim.skills FROM skill_counts LEFT JOIN skills_dim ON skill_counts.skill_id = skills_dim.skill_id You also have a copy of this in VS Code.

Question 34

What is the UNION operator and what does it do?

Answer 34

It combines the result sets of two or more select statements into a single result set. UNION - removes all duplicate rows UNION ALL - includes all duplicate rows each SELECT statement within the UNION must have the same number of columns and those columns must have the same datatype For example - we used UNION and UNION all to combine the results of the January, February and March tables. They all had the same columns and datatypes.

Question 35

What is the syntax for a UNION or UNION ALL statement

Answer 35

SELECT column_name FROM table_name1 UNION (or UNION ALL) SELECT column_name FROM table_name2 (You could also add another one!) UNION (or UNION ALL) SELECT column_name FROM table_name3 etc. etc. etc.