YT YouTube2Text

YouTube2Text → Transcripts → SQL for Data Analytics - Learn SQL in 4 Hours

SQL for Data Analytics - Learn SQL in 4 Hours — Transcript

by Luke Barousse · 48,224 words · 6,515 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00data nerds welcome to this full course
0:01tutorial on how I use SQL for data
0:04analytics this is the course I wish I
0:06would have had when I first started
0:07learning this tool as it's a super easy
0:09to learn skill when taught properly and
0:11I feel like you can strongly master the
0:13basics by the end of this video now this
0:15tool is one of the most highly sought
0:16after skills in the data science
0:18Industry don't believe me let's check
0:19out the data for skills required in data
0:21analyst jobs SQL is the number one skill
0:25in almost half of job postings for data
0:27Engineers it's still number one and it's
0:29more than in half for data scientists
0:31it's the second most important skill
0:33right after python now SQL stands for
0:35structured query language and it can be
0:38spoken either SQL or SQL if I needed to
0:41perform analysis on my computer of a
0:43data set in a database sqls the
0:45programming language I'll be using to
0:47extract out any insights and this skill
0:49is something that I've been working on
0:50refining for years all the way from my
0:52first job as a data analyst in Corporate
0:54America to even my last role with Mr
0:57Beast squl is everywhere so what will'll
0:59be covering in this course well I've
1:01broken it up into three chapters we'll
1:03be starting at the very Basics and then
1:05building way up to an advanced section
1:08building our very own Capstone project
1:10for the basics chapter we're going to
1:11start at the very beginning including
1:13breaking down important Concepts behind
1:15SQL databases from there we're going to
1:17jump head first into actually practicing
1:19SQL queries focusing on the basics of
1:22common keywords simple analysis and even
1:24more advanced topics like joints now
1:27don't worry if you have no coding or
1:28analytical experience as this chapter is
1:31designed for you now after we get the
1:32basics down we're going to then jump
1:34into Advanced Techniques we're going to
1:36walk through creating and setting up
1:38your very own database locally on your
1:40own computer we're then going to move
1:42into more complex analysis using things
1:45like CTE and subqueries now you're
1:47easily going to forget these basic and
1:49advanced concepts unless you actually
1:51get into implementing what you learned
1:53and the best way to learn something is
1:54to build something and that's what we'll
1:56be doing in the Final Chapter we're
1:58going to be working to solve a real
1:59world problem problem analyzing the top
2:01skills and jobs in the data science
2:03Industry we'll be using the data set
2:05powering my app data nerd. Tech that
2:07provides insights on data science job
2:10postings and by the end of it you'll
2:11have your own custom project that you'll
2:14be able to showcase on GitHub for the
2:16world to see now I'm a firm believer in
2:18open sourcing education so this course
2:20is completely free and all the resources
2:24that you need for it are included so I'm
2:26going to be providing you locations to
2:28actually run your SQL queries along with
2:31the data sets when you build your
2:33project but I'll be honest YouTube isn't
2:35paying the bills like it used to so I
2:37have an option for those that want to
2:39actually support the course while also
2:41rewarding you for doing this for those
2:43using the link below to contribute I'm
2:45going to give you some extra perks that
2:47will help you learn SQL even faster
2:49you're going to have access to multiple
2:51practice problems within each section
2:53they break down the problems into easy
2:54medium and hard and even provide the
2:56solutions for completing the problems
2:58which will reinforce all the skills that
3:00we're learning in the video additionally
3:02I'm providing all the course notes for
3:04this video they're going to provide even
3:05more details and resources for the
3:07sections along with breaking down the
3:09queries in the videos a lot further and
3:12then once you've completed the course
3:14I'm going to award you a certificate of
3:15completion now one quick shout out
3:17before we jump into the first chapter on
3:19Basics explaining what is SQL I want to
3:21give a shout out to Kelly Adams She is
3:23the brains behind the majority of this
3:25content in this course she's a full-time
3:27data analyst that I've been following on
3:29LinkedIn for years now and I was beyond
3:31ecstatic whenever she said she'd team up
3:33with me to build this course I'll be
3:35linking Kelly's LinkedIn and also
3:37portfolio below the like button so be
3:39sure to check that out all right with
3:41that let's actually jump into the basics
3:43chapter all right welcome to this
3:45chapter on the basics and for this we're
3:47going to be focusing on the very
3:49fundamentals in this section and also
3:51the next so in this section we'll be
3:53breaking down what exactly is SQL and
3:56then after that I'm going to give you
3:57access to all the different data sets so
3:59you can work the problems right
4:01alongside me now what I'm showing here
4:03are the course notes and for those that
4:05have contributed to the course you'll
4:07have access to go right alongside me and
4:10see all the different notes as I'm
4:11walking you through this so with that
4:13let's actually dive into understanding
4:15what is SQL and for this we need to
4:18understand two major Concepts behind
4:20this the first concept is around where
4:22you'll be writing and executing your SQL
4:24queries and the other main concept is
4:26around databases where our data is
4:29actually stored so let's dive into this
4:31first a database is a collection of data
4:35and these things were made to hold
4:36massives amount of data an Excel file
4:39Can Only Hold around a million rows of
4:40data but a database can easily hold
4:43millions of Excel files so if you do the
4:46math that's a lot of rows of data now
4:47about those databases there's two major
4:49types of databases relational and
4:52non-relational relational databases
4:54include things like tables very
4:57structured data non-relational databases
4:59are for unstructured data we're going to
5:01dive into each in a little bit but first
5:03let's go back to where we're actually
5:05running and executing SQL now whenever
5:07we write out SQL code in order to
5:09request data from a database this is
5:12called a query and this SQL code can do
5:14a whole a heck lock more that just
5:16request data a common acronym associated
5:18with what it can do is called crud and
5:21it stands for create read update and
5:24delete with SQL keywords like create an
5:26insert into you can add new records then
5:29with keyword like select you can
5:31retrieve specific data you need next
5:33with the keyword update you can modify
5:35existing records and then finally one
5:37the scarest of alls if you need to
5:38remove records you can use delete now
5:40these are all commands we're going to be
5:41covering throughout the basic and
5:43advanced section so it's nothing you
5:44need to commit to memory right now so
5:46now we understand the basics of querying
5:48and also a database but where the heck
5:50are these databases even stored there's
5:52a couple major options you can either
5:54run it locally on your computer or you
5:57can use a server now for this course
6:00we're going to be running the database
6:01locally it's going to save a lot of
6:03costs and also I feel you learn a lot by
6:05running databases locally before you
6:07jump into something more complicated
6:09like a server local computers are also
6:11used during development and testing so
6:14you may find yourself from time to time
6:15actually downloading a database on your
6:17computer so you don't mess with the real
6:19one so where a real world databas is
6:21typically located and you have two major
6:23options one is a company server and
6:25that's something that's actually hosted
6:27internally and you have that the company
6:30full control of This Server this option
6:32is nicknamed on Prem now the other
6:35options like Cloud providers do this for
6:37you companies like AWS Google cloud and
6:39aure are known for this and frankly for
6:42a price they'll manage all the different
6:44headaches of managing your own server so
6:47this is conveniently called serverless
6:49and that doesn't mean there are no
6:50servers it just means you don't have to
6:52have the headaches so now that we
6:53understand the databases can be stored
6:55either on your computer or on a server
6:57what are the different databases we can
6:59store in these locations well there are
7:01two major types relational and
7:04non-relational databases relational
7:06databases store data in rows and columns
7:10this is very much used in transactional
7:13applications say I had a database of job
7:15postings that companies go to daily in
7:18order to transact with an update of what
7:21jobs are available you could have one
7:23table to keep track of all the different
7:25jobs and then you could have an
7:27Associated table to keep track of all
7:29the different different companies and
7:30their information there would then be
7:32things like IDs within the two in order
7:35for you to relate the different tables
7:37hence relational databases this course
7:39will be primarily focusing on using SQL
7:42to interact with relational databases
7:45but the other type of databases are
7:46non-relational databases commonly known
7:49as nosql but nosql doesn't stand for not
7:53SQL it stands for not only SQL these
7:56type of databases support a host of
7:58different options including unstructured
8:01data so not only can it include more
8:03structured data like column and row
8:05based data but also you can have other
8:07types like key value payers graph-based
8:10and even document based these are using
8:13your everyday applications to track your
8:15connections on LinkedIn or to manage
8:17your docs in Google doc so what are some
8:19popular database options well if we
8:22navigate to the stack Overflow developer
8:24survey from 2023 over 76,000 respondents
8:28voted on what is their database of
8:30choice if we look at the top five
8:32options four of these are relational
8:35databases and only one is a
8:37non-relational database and for this
8:39we're going to be focusing on the number
8:40one and three option the first one is
8:42pronounced postgress you don't have to
8:44pronounce the SQL at the end the other
8:46one per of the Creator is called SQ
8:48light but I'm pretty lazy so I'm just
8:50going to call it SQL light so we covered
8:52everything we needed to know for
8:54databases but where the heck are we
8:56going to be actually writing and running
8:58these queries well there's a few few
8:59popular options for this and it's
9:01commonly done in an editor whether
9:03that's from a database provider a cloud
9:05provider or even just a plain old code
9:08editor let's talk about database
9:09provided editors first for popular
9:12options like postgress and MySQL they'll
9:14actually provide you an application to
9:17use on your computer to access their
9:19databases inside of you'll be able to
9:21track status of your different tables
9:23and actually look at the progress of it
9:25and you can even do something as simple
9:26as writing SQL queries now if you're
9:28using something like a cloud provider
9:30like Google cloud or Azure they're also
9:33going to give you the option inside of a
9:34web browser in order to interact with
9:37your database I keep a lot of my data on
9:39Google cloud and it allows you to
9:41monitor all the different tables that
9:43you have along with an interface to go
9:45in and query the data now the third
9:47option is the one that we're actually
9:48going to be using but I want you to be
9:49aware of the other two and this is a
9:51code editor during the second and third
9:53chapters of this course we're going to
9:55be focusing on using a popular option
9:57VSS code which allows you to not only
10:00manage all your different SQL files that
10:02we'll be using for this but it will also
10:04allow us to run these queries for the
10:06beginner section we're going to be using
10:08a code editor right inside of your
10:10browser this bad boy is called sqlite
10:13viz and it's completely free to use this
10:15is a popular open- Source option that
10:17allows you to run queries right inside
10:20your browser and in the next video we're
10:22going to talk about more about what the
10:23data and databases we're using inside of
10:26things like sqlite viz and vs code all
10:29right so now you should have a basic
10:31understanding of how and where SQL
10:33queries are run and then what and how
10:36databases are used in order to store
10:39data so with that let's actually dive
10:40into seeing where you're going to be
10:42playing with all this data all right see
10:44you in the next one all right welcome to
10:46the section on an intro to a course
10:48where we're going to be focusing on
10:50understanding what data sets we're going
10:51to be using for this and how we're going
10:53to be running those data sets but before
10:55we even talk about any of these data
10:56sets we need to understand what problem
10:58we're going to be solving for the
11:00entirety of this course you're going to
11:02be taking the perspective of a job
11:04Seeker with the goal of identifying what
11:06are some of the highest paying jobs
11:08along with what are the most optimal
11:10skills to learn so in order to solve
11:12this problem you need a data set that
11:14has this type of data in it well luckily
11:16I've been collecting this type of data
11:19in my app data nerd. te and inside of
11:22this app it Aggregates job postings in
11:25the data science Industry in order for
11:27you to see things like what are the top
11:30skills for data analyst additionally it
11:32also includes pay requirement for the
11:34different jobs along with detailing how
11:37these skills are paid based on a certain
11:40job title so the data set that we're
11:42using for this course is from that app
11:44and specifically it's for data science
11:46job postings in 2023 here is an ER or
11:50entity relationship diagram about the
11:52different tables in this data set we
11:55have four major tables one fact table
11:58con containing all our different job
12:00postings then two Dimension tables that
12:03contain key information about the skills
12:05required for those jobs and then finally
12:08a dimension table around the different
12:09companies that are posting these jobs so
12:12what the heck are these fact Dimension
12:13tables you probably never heard of fact
12:15tables contain the core data for the
12:18analysis they measure and record actual
12:20events so in our case the different job
12:22postings because of this there's
12:25typically a high volume of records and
12:26then there's usually some sort of
12:28foreign key to associate it to a
12:30dimension table the dimension tables
12:33describe attributes or dimensions of the
12:35data so in our case skills or company
12:37data these are really important in
12:39supporting filtering and grouping
12:42different sets of data they usually have
12:44fewer rows of data but generally are
12:46more descriptive anyway going back to
12:47the ERD the job postings fact table has
12:50a job ID column in it and this is a
12:53unique ID to all the different job
12:55postings this ID is inside the skills
12:58job deal table as a foreign key and we
13:01have this in a separate table because
13:03there can be multiple skill IDs
13:05associated with the job ID and
13:07conversely this is associated to the
13:09skills dim table which actually has the
13:11list and the type of skills that are
13:14associated to the job back to that job
13:16postings fact table we also have a
13:18company ID column and this column is
13:21associated to the company dim table
13:24which in this table has key information
13:26about the companies such as its name and
13:29its URL that you can access anyway
13:31that's all theoretical talk let's
13:33actually dive into actually looking at
13:35the different data we're going to be
13:36using for this and for this you can
13:38navigate to this URL right here and it's
13:41could provide access via that tool SQL
13:44light viz in order to analyze and
13:46visualize our SQL queries we're going to
13:48dive more into this tool in the next
13:50section but for now understand that I'm
13:52running a query up at the top and the
13:54results are appearing below anyway this
13:57is the job postings f back table so for
14:00this analysis that we're going to be
14:01doing I'm going to be analyzing it from
14:03the perspective of a data analyst
14:05because I'm a data analyst and that's
14:07what I'm interested in but you actually
14:10have different job titles available to
14:12use here's a list of all the different
14:14options you can not only look at data
14:16analysts but also data scientists or
14:17data Engineers if you wanted to you can
14:19explore senior roles or even roles that
14:22are slightly related such as business
14:23analyst machine learning engineer
14:25software engineer and Cloud engineer so
14:27feel free during this course to modify
14:29any of my queries in order to adapt it
14:32to what job you care more about now the
14:34data set includes postings from around
14:36the world I'm primarily going to be
14:38searching it for remote jobs but you can
14:41adapt it to any location that you find
14:43fit and the last thing to app about is
14:45it also includes a lot of companies from
14:47diverse perspectives not only tech
14:49companies but also Commerce companies
14:51now there is one other data set used in
14:53this course and we're only going to use
14:54it from time to time specifically we
14:56need it for our arithmetic operations
14:58this this is a fictitious data set Based
15:01on data science job invoices there's
15:04only one table inside of it and it's
15:05conveniently named invoices fact we'll
15:08go over more of this data set in a
15:09future section so we covered what data
15:12we're going to be using for this but
15:14what database are we going to be using
15:16to actually host all this data well as
15:18mentioned in the last video we're going
15:20to be focusing on postgress and SQL
15:23light this beginner chapter will all be
15:26based on SQL light it's a lightweight
15:29file-based database and it's ideal for
15:31small to medium apps with zero
15:33configuration it's so small in fact that
15:35it's actually running right inside your
15:37browser whenever you access this tool
15:39SQL like Vis for the second and third
15:42chapters in this we'll be using
15:44postgress it's an advanced open-source
15:47relational database suited for large
15:49applications and it supports a lot more
15:51complex queries so because it's open
15:53source you'll be able to download this
15:55database on your computer for free and
15:57use it now I do want to call out there
15:59are some slight syntax differences
16:02between the two and syntax is the set of
16:04rules and structures you have to follow
16:06while programming for the majority of
16:08course commands like this are going to
16:10work in both these different databases
16:12it will only come to very Nuance
16:13situations where these two databases are
16:16going to have differences in it but
16:17really we're not going to find a lot now
16:19that we laid all this ground work let's
16:20actually jump into doing all these
16:22different queries for those that
16:23purchase the course notes whenever you
16:25navigate to a section it's not going to
16:27only showcase what databases you're
16:29using for this but also we'll have an
16:31overview of what we're covering for each
16:33of the Min sections within the chapter
16:34you'll have some basic notes on the
16:36topic along with the query and the
16:38expected results when we get to the
16:40practice problems within the chapter it
16:42will have similarly not only the data
16:44set but then also the questions and the
16:47solution all right so with that enough
16:49me yapping let's dive into it see you in
16:51the next one all right welcome to this
16:53basic section where we have this mini
16:55section on the basics we're going to
16:58focus on a handful of keywords that I
17:00use on a routine basis and I think you
17:03need to have committed to memory in
17:04order to know how to use and so we'll be
17:06actually implementing it and running
17:08queries with these commands to learn
17:09more about them first up it's actually
17:12instead of one keyword we're going to
17:13focus on three and this is going to be
17:15saying select star from and a database
17:19table select is a keyword that
17:21identifies a column or columns we want
17:23to connect to and from identifies the
17:25table or tables we want to connect to
17:28let me show you what I mean so routinely
17:29I'll need access to some sort of
17:31database for my job I'll have to reach
17:33out to something like a database
17:34administrator and they'll give me access
17:36anyway select and from is how I'm going
17:38to verify that I have access to the
17:40database so in this case let's say I got
17:42this email and it says I have now access
17:44to this job 2023 and it has all these
17:47tables in it I want to go now verify
17:48that I have access to it so let's verify
17:50we have access to this and you're going
17:52to navigate over to the URL that I have
17:55linked right here and this is going to
17:58be our little workspace that we're be
17:59working in in order to verify access now
18:02when you pop this open you should have
18:04two separate Windows one an upper window
18:07where you're going to write the query
18:08and then a lower window below this that
18:10is going to display all the different
18:12results that you have we can see here
18:14that this query is already executed I
18:17can open up this left side pane right
18:19here and actually look into the database
18:22that we have access to in this which is
18:23jobs
18:252023 then all of the tables within that
18:28database so these four tables and then
18:30you can further break it down or see
18:32what's in it by expanding this and then
18:35this displays the columns within that
18:38table so I'm going to go ahead and close
18:40that out cuz I don't want to really look
18:42at it and that first statement should
18:44already appear on your window right here
18:47you can go ahead and run it again if you
18:50want by either pressing run SQL query
18:53and it'll go through and fetch results
18:55looks like there's 33,500 available you
18:58can also as a quick shortcut press
19:01controll enter and it will also run the
19:04query now anytime I get access to the
19:06database this is one of the first
19:07commands that I'm running in order to
19:10see all the different columns that I
19:12have access to and to ensure actually
19:14that I have access to that table so in
19:17this case job posting is fact if I
19:19wanted to I could also see another table
19:22in this data set I can put in company
19:24dim and press control enter and see that
19:27hey yeah I also have access to this one
19:29now these commands must be written in
19:32this order you would think you would
19:34want to say from a table select these
19:39columns um because that would be the
19:41order but if you tried to actually run
19:43this control enter it's going to give
19:45you this error syntax it has to be in
19:47that specific order of Select and from
19:51and I'm going to be sprinkling also best
19:52practices in through this so you'll
19:54notice the select and from in this case
19:57are all capital letters these keywords
19:59are not case are not case sensitive so
20:04in this case I could have lowercase and
20:06also uppercase mixed in with each other
20:08they're still going to work the values
20:10for things like the table name are case
20:14sensitive depending on the database
20:16we're using SQL light in this case so if
20:19I were to use a Capital C it's still
20:21going to work but whenever we start
20:23using postgress in the basic or in the
20:25advaned section it is going to be case
20:27sensitive so it's best practice to just
20:30leave this lowercase as it was intended
20:33in general these uppercase keywords and
20:36then lowercase of things like the column
20:38names or table names makes it easier to
20:42read and so that's what you want to do
20:44especially whenever you have to get into
20:45troubleshooting later you want to be
20:46able to easily read it and able to edit
20:48it now in this example we selected all
20:52columns but there's going to be cases
20:53where you don't want to select all
20:54columns not argue in most cases you
20:56don't want to select all columns it's
20:58very resource intensive for the server
21:01that's hosting this database to provide
21:03all those columns so you'd want to
21:06actually fine-tune the specific columns
21:09you want to use so in this case let's
21:11say I wanted only the two columns of job
21:14title short and job location instead of
21:17using this asterisk which is used to
21:21select all the columns instead I would
21:24just specify the columns so I'd say job
21:27title short
21:29and also job location I'm going to put a
21:31comma between each one of those to say
21:34that hey we're moving on to the next one
21:36this case when I press control enter
21:38executing the query we can see we have
21:40now these two columns shown now moving
21:42in some best practice for this one I
21:45like to have it to where whenever I'm
21:47selecting multiple columns I put it on
21:50separate lines to make it just more
21:52readable now with specifying these
21:54column names from a table they can
21:57actually be specified
21:59by saying the table name itself and then
22:01using this dot operator to then showcase
22:05the column after it I can do this
22:08actually for both of the v's here
22:10because they're both within that same
22:12table and whenever I run this query
22:15going to get the same results now this
22:17is going to be more important later on
22:19whenever we're combining multiple tables
22:22and you actually need to specify where
22:24this column is coming from within a
22:26table but in this case because we're
22:28only using one table I'm going to say
22:31it's not necessary and I'm going to go
22:34ahead and remove it but I just want you
22:35to be aware of it for the time being and
22:37show you that it still works all right
22:39so we already understand that quering
22:42all the different Columns of data set
22:43can be intensive for a Ser ex actually
22:46for really big data sets another thing
22:48that we want to do besides limiting The
22:50Columns is actually limiting the amount
22:53of rows and this can be done via the
22:55limit statement we can specify any
22:59number of values after this and this
23:02will specify the number of rows we want
23:04it to return right now these results are
23:06returning around
23:0833,000 job postings back so in this case
23:11let's say I only want to provide back
23:14five I'd put limit five and that limit
23:17five needs to come after the select and
23:20from statements and it's the very last
23:22statement you'll ever be writing inside
23:24of a SQL statement I'll press contrl
23:27enter and now we have five rows
23:29retrieved we can also see that for the
23:31five rows retrieved it only took 026
23:35seconds running this without that limit
23:38we can see that it takes around three
23:40times longer to get all those different
23:43rows now we're working with a relatively
23:45small data set so you're like look this
23:47is only milliseconds yes whenever we
23:50start getting into millions or even
23:51billions of rows this is going to add up
23:54and limit is going to save you some time
23:56one note on best practices so I talked
23:59about indenting these lines to make it
24:03more readable typical best practice is
24:05to have anywhere between two and four
24:08spaces personally for me I just hit Tab
24:11and it automatically inserts four spaces
24:13into here I could also do multiple
24:16different tabs and if I were to press
24:18controll enter in this case it's still
24:21going to execute whenever your SQL
24:23statement is sent over to the database
24:25itself so effectively all that
24:27indentation is removed and in this case
24:29like it's one line although this is hard
24:30for a human to read the database itself
24:33can obviously go through it and
24:34understand what it needs to be next up
24:36is the distinct keyword and this one is
24:38going to follow select in order to
24:41select a distinct amount of values
24:44within the rows of a column this is
24:47going to be a very resource intense type
24:49of calculation because it has to go
24:51through all the values in a colum column
24:54and then Aggregate and find what are the
24:56distinct values so let's take for
24:59example this job title short column
25:01right here I'm going to go ahead and
25:04reset this to include all the different
25:06values if I scroll down it I can see a
25:09lot of the values in here such as data
25:11scientist are repetitive and then data
25:13engineer I've seen multiple times that
25:15analyst so let's say I want to get all
25:18the unique values from this in this case
25:21I'll remove that other column and then
25:23specify distinct and from here press
25:27control enter to run it now I also
25:29didn't have that limit command remember
25:31so now we have 10 rows retrieved and
25:34these are all the different values or
25:36unique values from this job title short
25:40column now this isn't only limited to
25:43categorical data like in this case let's
25:46say I wanted to get the unique values
25:48from the average salary column now I
25:51could do the same thing of Select
25:53distinct salary year average column from
25:56this database pressing control enter and
25:59then from here I now have all those
26:02different unique values within it looks
26:04like there's over 2700 unique values now
26:07you may notice I have two queries within
26:09here and you can see that each one of
26:12these queries I've added a semicolon at
26:14the end the semicolon sometimes you'll
26:16see me use it sometimes you won't it's
26:18used to symbolize that this is the end
26:21of the sequel statement that you want to
26:23execute this case we have two separate
26:25and distinct uh SQL statements uh no pun
26:28intended with that and in this case with
26:32our editor we can only show one result
26:36of a SQL statement mainly that last one
26:39in this window right here when we get to
26:41the Advance section and we start using a
26:43different code editor you'll see how you
26:45can actually run all these different
26:47queries and have them populate in
26:49different tabs anyway for the time being
26:51anytime you're running queries I like to
26:53just keep it one in there and whether
26:55you have a semicolon or not it's up to
26:57you I'm going to leave it semicolon free
27:00next is the wear statement and this is
27:02used in cases when we want to filter out
27:05particular data we already talked about
27:07specifically now you can do things like
27:09select columns or limit the amount now
27:12we can go down even further and actually
27:14filter into what we need all right going
27:16back to a query where we have the four
27:18Columns of interest that we're looking
27:20at from our job postings fact table and
27:23we can see we have a lot of different
27:25values from different job titles short
27:28so we can use the wear statement to
27:31filter down to something more I'm more
27:33interested in such as data analyst the
27:36wear statement will be always directly
27:39after the from statement and we want to
27:42specify a condition we want to specify
27:45that that the job title short is equal
27:48to data analyst now we can go into
27:50either other condition such as greater
27:52than less than all that we're going to
27:54keep it really simple for now now the
27:56main thing to note right is that data
27:58analyst is in quotes in this case
28:01because it's a string character and it's
28:04immediately following that column
28:06whether there's spaces in here or not
28:08that doesn't really matter I just do
28:09this for readability pressing control
28:11enter we can see that we've now filtered
28:14in on this data and it only has data
28:18analysts so previously we had around
28:1930,000 rows now we have less than 10,000
28:23rows and we're not just limited to
28:25categorical or that text data we could
28:28also do it for numerical data so in the
28:30case of that salary yearly average
28:33column we could say hey we want to have
28:35everything that's greater than
28:37$90,000 and then now on scrolling
28:40through it we're can see that we do we
28:41have around 16,000 values that meet this
28:43condition now now that we're adding all
28:45of these more complex conditions this
28:49adds a great use case of now adding
28:52comments comments are denoted by these
28:55two dashes or tax if you're from the
28:58military before something that follows
29:01it so anything to the right of these two
29:05dashes is ignored no matter where those
29:07two dashes are placed and this is the
29:11standard practice of documenting your
29:13code and if you do something complex
29:16keeping track of it typically a comment
29:17would come at the beginning of a query
29:19to specify what's going on in here and
29:22in this case it's right at the front
29:24executing this query you can see that it
29:26ignored it you can even put put it after
29:28a SQL command as you can see here it's
29:31grayed out so that means that basically
29:33showing to you that it's going to be
29:34ignored and so in this case when
29:36pressing control enter still executed
29:38and now I have a documentation for why I
29:41maybe filtered out this certain subset
29:43of data the other common use case of
29:45this is if you're debugging or
29:46troubleshooting say I didn't want to
29:48care about this column or this column
29:51right here I can then put those two
29:53dashes in front of it whenever I run the
29:55query it only Returns the columns I
29:57don't have have the dashes in front of
29:58it now the other comment besides a
30:00single line comment it's a multi-line
30:02comment and it's denoted by a forward SL
30:06Asis and then an asteris forward slash
30:09that forward or backs slash just forward
30:11slash we're good anyway this is a
30:12multi-line comment and it's commonly
30:14used whenever you have to use well
30:15multiple lines but when you have to be
30:17more robust in your description on what
30:19you're trying to convey let's say I
30:21wanted to convey to whoever's going to
30:23be using this query why do I keep on
30:25using these four Columns of Interest
30:27over and over again well I can leave a
30:29note section in here that says hey these
30:31are going to be the most common you're
30:32going to see throughout these queries
30:35and here's the reason why well they
30:37provide the most comprehensive view of
30:38the data and there's the common factors
30:40that most people are looking at um we
30:43also don't want to call all the columns
30:44because it will just take up too much
30:45processing anyway if we go and run this
30:49query we can see that hey it is in fact
30:52shown and just to show the point if I
30:56were to remove these op ators right here
30:58and try to actually try to run this
31:01query it would give me an error near the
31:03note because it's going to try to
31:04execute this and things it's SQL syntax
31:07so I just fix that up real quick all
31:09right and the last keyword that we're
31:10going to cover before we get into some
31:11practice is order by as you guessed it
31:15this is used to specify a column and
31:19order it by that value so let's go ahead
31:22and use this order buy is going to come
31:24almost directly last the only thing that
31:26would come after order buy is a limit
31:29keyword in this case let's say we want
31:31to actually Aggregate and be able to see
31:35the salary from lowest to high pressing
31:39control enter it's going to go ahead and
31:41execute now it's going to notice here
31:43that we have null values first and
31:46that's because null means that there is
31:49nothing there for that value so it's
31:52even less than if you will Zero but if
31:54we scrolled over some pages that I've
31:56done here we can see that now yes when
32:00we have values inside of here it is
32:03going from that low to high those values
32:06are slowly rising and in an ordered case
32:09now this is lowest to highest which
32:11actually you if you were write ASC and
32:15go ahead and execute this it's doing the
32:17same thing here it's doing it in
32:19ascending order but now we don't need to
32:22necessarily write uh ASC um especially
32:25as it's sort of repetitive but if you do
32:28want it in descending order from highest
32:30to lowest you would then specify
32:33DEC and executing this query we're now
32:37getting it from the highest to lowest
32:39this would be really good in this case
32:40we want to see what is the highest
32:42salary we can now see it 650,000 now you
32:45may be curious what order should you be
32:48writing these commands in well here's a
32:51little convenient little cheat sheet on
32:53what you should be following for this
32:55follow it goes select from where Group
32:57by
32:58having order bu and limit now I don't
33:01have these commands memorized I even
33:03asked chat gbt for pneumonic on how to
33:06memorize this and it said super fast
33:08works great beneath the surface and has
33:10outstanding balance and Leadership and I
33:13going to use that mainly we're going to
33:15go with trial and error that's how I've
33:17memorized this over the years you're
33:18going to make mistakes get out of order
33:20but you'll understand what the error
33:22message is and you'll adjust from there
33:23all right now that we cover all of those
33:25Basics keywords now it's your time to go
33:28in and actually practice this I would
33:30play around with all those different
33:31keywords that we have also exploring all
33:34those different tables that you have
33:36available right now now for those that
33:39purchase the course notes and
33:40certificates you have some specific
33:43practice problems available to you um
33:45there's about five for this section
33:47right now we'll be adding to this um and
33:50in it you'll have things like the
33:51solution and also results to make sure
33:54that you're on track and actually
33:55following along and doing it correctly
33:57all right good luck with these see you
33:59in the next
34:02one all right let's get into this
34:04section now on comparison and also
34:06logical operators and you've been
34:08exposed to this previously when we use
34:11that wear keyword before to filter for
34:15job titles of data analyst we used an
34:18equal operator this is called a
34:19comparison operator we're going to go
34:21over all the different types for this
34:23these type of operators are used after
34:25the wear or even the having Glock clause
34:28in this section we're going to be
34:29focusing on the wear Clause we'll move
34:31on to the having as we get more advanced
34:34now in addition to this also we'll be
34:36using the logical operators which allows
34:39even more advanced functionality to
34:42fine-tune how you want to filter data
34:45since we already understand the
34:46fundamentals of the equal operator we're
34:48going to move on to the not equal
34:50operator and we can us use this symbol
34:53of basically these two Pacman try need
34:54each other or just the keyword not and
34:57specifies that it's not equal to All
35:00Right Moving in back into the data let's
35:02say we're get this data right here and
35:04we want to filter it further let's say I
35:06have some Insider information that says
35:09those jobs that we get from the job
35:12platform AI Tech jobs.net is unreliable
35:15and I don't want to use it that's not
35:16necessarily true we're just giving it an
35:18example here well I conclude that wear
35:20keyword along with job via and that
35:23comparison operator of not equal to
35:26providing it what statement I want I
35:27meet on and then press control enter I
35:30can now see that they are removed now we
35:33can also use that not operator and
35:35that's used directly after the where
35:38keyword where not job via uh AI jobs.net
35:42this is sort of confusing but this in
35:44this case is going to rep return all the
35:47job Vias of this so in this case if we
35:49wanted to not use it I'd put that equal
35:52comparison operator in there run it and
35:55then we can see that it now has this
35:57like like this so not is a way to do
36:00basically opposite of what we want to do
36:02now besides something like the equal or
36:03not equal to operator for those that
36:06have numerical columns we can use things
36:08like greater than or less than to
36:11actually look inside of there and find a
36:13value and find things that meet that
36:15condition so in this case I'm going to
36:17filter for salaries that are going to be
36:19only greater than 50,000 it's also going
36:22to remove these null values whenever I
36:24do this pressing control enter and see
36:26that it is in Factor move in case like
36:29this I would include something like an
36:30order buy for that salary year average
36:33and I did this out of order uh because I
36:35don't have these memorized and putting
36:38this in after the wear statement and
36:40actually running it it runs correctly
36:43and now we can see okay now they are
36:45ordered in this order and it's starting
36:47at that 50,000 in this case now you're
36:49not just limited to greater than or
36:51equal to also you have things like less
36:52than or equal to I'm assume you have
36:54familiar RT with how this actually works
36:56so we're going to skip an example on
36:57that one
36:58and we're going to move into logical
37:00operators now starting with the first
37:01logical operator and this is great in
37:04conditions where we want to meet
37:05multiple filter conditions so the
37:08example shown if I want to meet a
37:10certain salary and job title I can now
37:12use the and operator to combine this in
37:15this case it's only going to show
37:17conditions where both of those
37:20conditions met are equal to true so it
37:23has to have a job title of data analyst
37:26and it has to have a salary greater than
37:29100,000 so let's actually test this
37:31query out by plugging that in for that
37:33wear and specifying those two conditions
37:36I'm also going to leave in that order bu
37:38after this so we can read it more easily
37:41pressing control enter we now have it
37:44now remember it's greater than 100,000
37:46not greater than or equal to so we don't
37:48have any 100,000 values in this and
37:50scrolling through we can see that like
37:52we expect it Returns the conditions that
37:54we're trying to meet for this now
37:56conversely to that and logical operator
37:59we also have the or logical operator and
38:02this would be used in a condition where
38:03we wanted to see if we meet any of the
38:07conditions so as the example shows it
38:09could be either data analyst or the
38:12salary is greater than 100,000 so we
38:16could have something like a business
38:18analyst that gets greater than 100,000
38:20or we could have something like a DAT
38:21analyst that technically has less than
38:23100,000 replacing that and with an or
38:26and then still keep that order by so we
38:28can get through this quickly I'm going
38:30to press controll enter and as we can
38:34see we have nothing but data analyst
38:36positions for the null values and for
38:40values that are greater than $100,000 in
38:43salary we have everything that's not
38:45necessarily a data analyst it could be a
38:47data analyst and here's one at 225,000
38:50so an or condition I'll be honest I
38:52don't use or as much as the and logical
38:55operator now let's say I wanted to
38:57search for salaries between 100,000 and
39:01200,000 technically I could use that and
39:04operator combined with those logical
39:06operators to say hey I want something
39:08that's $100,000 or greater than $100,000
39:11and less than
39:13$200,000 but there's actually a better
39:15way of doing this and that's using the
39:18between logical operator it's still
39:20going to be used within that wear
39:22statement in this case it's much more
39:24readable and much more concise we can
39:26say for a given column we want between
39:3060,000 and 990,000 in this case and this
39:33is not just limited to numerical data
39:35you can also technically use this for
39:36Text data or even dates so I've updated
39:38that previous query now to have that
39:40between statement specifying the values
39:43and using that and statement to join it
39:45I'm still doing that order bu to make it
39:46easy to look at pressing control enter
39:49bam we have everything between including
39:52that of which is 100,000 and 200,000 now
39:56the last logical oper to cover is in and
40:00this I find is more common in Text data
40:03so the example shown job location if I
40:06wanted to search maybe Boston
40:08Massachusetts and also anywhere I could
40:11do this with an instatement and then I
40:12would enclose all those values whether
40:15it's two or even more so let's say in
40:17our case we wanted to look for both data
40:20analyst jobs and also data engineer jobs
40:23written currently it's a little bit too
40:26robust in this too much wording as we're
40:29repetitive with using job title short
40:31and then combining it with that or
40:33statement it does however if we went
40:35ahead and execute it we can see that yep
40:37data engineers and data analysts are
40:39only in this but instead I'm going to
40:41modify this now to include this in
40:44logical operator and then have data
40:46analyst and data Engineers within
40:47parentheses separated by a comma and
40:50this is also great because now let's say
40:52I wanted to add something else like a
40:54data scientist I can just add that in I
40:58don't have to type in the keyword again
41:00pressing control enter you can see now
41:02we have data Engineers data analysts and
41:04data scientist all right now it's your
41:06turn to try out these comparison and
41:08logical operators for those that have
41:10purchased the course notes and
41:11certificates you have a host of
41:13different practice problems you can work
41:15through and test your skills for that
41:17all right see you in the next
41:22one all right let's actually combine
41:24everything we've learned from that basic
41:26section and the comparison logical
41:28operator section into a more advanced
41:30query we're going to do this with a
41:32practice problem that I feel would be
41:34applicable if I was actually job
41:36searching so let's say I'm looking for
41:38roles I work as a data analyst and I
41:41could technically also work as a
41:42business analyst so I want to look for
41:43both of these rules and for this though
41:45I have some conditions for data analyst
41:47I only want to search for jobs that are
41:49greater than 100,000 and I know from
41:52some market research business analyst
41:54pays even less so I want to look for
41:56those jobs that are greater than 70,000
41:59now in addition to this I also only want
42:01to include locations in we'll say that
42:04I'm located in Boston Massachusetts or I
42:07want any remote works I'll also include
42:09any of those that include the location
42:11of anywhere which are remote jobs for
42:13this we're going to continue to include
42:15those four main columns that we
42:16previously did so starting out with our
42:19core query we have our select statement
42:21of the four Columns of Interest we're
42:23want to focus on and then our from
42:25statement whenever I'm breaking a these
42:26queries down I like to actually iterate
42:29through it so the first thing I'm going
42:30to look for and actually query down on
42:33is the location CU that seemed like the
42:35easiest I want to get Boston and I want
42:37to get anywhere so I add this wear
42:40statement for this job location and
42:43executing the query control enter we can
42:45see now okay we're limited only anywhere
42:47in Boston all right the next easiest
42:49thing to move on to is instead of just
42:52getting both that analyst and business
42:54analyst let's actually go down even
42:56further I want to get data analysts that
42:58are greater than 100,000 so I also have
43:01to besides meeting the job locations I
43:02have to meet this new condition now I'm
43:05going to be putting both the job title
43:08and then also the salary condition
43:10within this so I can use parentheses to
43:14say hey I want you to meet all of this
43:18and verify this before moving on to
43:20verifying the and statement and what do
43:22I mean by that well you remember by our
43:24order of operations uh from math
43:27the parentheses are going to go first so
43:30we're going to meet the condition first
43:31of is a de analyst and is it greater
43:34than $100,000 are both these commission
43:37conditions met it's true and then in the
43:40next case it's going to check the job
43:42locations whether it's in Boston
43:44Massachusetts or anywhere so the
43:46parentheses help control this order now
43:49remember we also want to meet this
43:51condition of business analysts and
43:53salary year average greater than 8
43:56$80,000 so I can remove this comment
43:58here now I could put an or statement
44:01after this so we me this data analyst or
44:04this one but now we have this statement
44:08we're going to meet and job location and
44:11this statement and then it's going to be
44:13an or for this one so if I were to
44:14execute this because of this or
44:17statement now I have this business
44:20analyst right here and it's in D Texas
44:23and it doesn't meet our condition
44:25because I actually wanted it to be
44:26either in Boston or anywhere although it
44:29does meet our salary condition anyway
44:30I'm going to fix this by including this
44:33or statement now between the data
44:35analyst and the business analyst in its
44:38own parentheses to further specify hey I
44:41want you meet these two end conditions
44:43or these two end conditions then from
44:45there once that's met then verify the
44:47job location let's check this out and
44:49now we have everything we want looks
44:51like I have a typo here I did 880,000
44:53earlier I meant to say 70,000 from our
44:56instructions we had 61 oh now we have 64
44:59jobs available anyway this me you the
45:01condition of those data analyst or even
45:04business analysts anywhere with our
45:06salary conditions the key thing to
45:09understand from this is you have to
45:11iterate through this query I wouldn't
45:13never expect you to just write this out
45:16all in one shot and then try and test it
45:19you're going to run into so many errors
45:21iteration is the key to success for this
45:28all right in this section we're going to
45:29be covering wild cards and wild cards
45:32are used to substitute one or more
45:34characters within a string we can use
45:38this by one using this keyword of like
45:41along with some special operators like
45:43the percentage sign and underscore oh
45:46and all this is used within the wear
45:47Clause let's jump into some examples and
45:49the first wild card to cover is the
45:51percent sign which in this case shown
45:54here we're searching for analyst and
45:57this percent sign symbolizes zero one or
46:01more characters so let's say I add some
46:02white space or some words before analyst
46:05or after it would account for that and
46:07only look for those words of analyst all
46:10right so previously we've been focusing
46:11on a job title short column and this is
46:14just a shorten version when we actually
46:16look at these job postings more in
46:18detail we get to it further we'll see
46:20that the actual job title and a list in
46:22these is a lot longer and sometimes
46:25there's a lot of fluff and necessary
46:27data in it but it could be a condition
46:30for this where we want to actually
46:31filter down on something so in this case
46:34we have this business data analyst and
46:37also data analyst let's say we actually
46:39wanted to filter down for anything that
46:41says analyst within the job title column
46:44well adding this wear statement and then
46:47specifying the column using that like
46:49keyword and then I can put that as
46:51percentage signs before and after
46:55running this now every in this job title
46:59has analyst within it now it's important
47:02to understand right I would want it
47:04before and after let's say I removed it
47:05to the ending in that case it's not
47:08going to find anything with values after
47:10the analyst so if I run this I'm only
47:13going to find analyst on the end of all
47:16those statements and I want to add that
47:19back because I actually want to see all
47:20the different analyst rules now another
47:22way this could be used is let's say in
47:25this case right here where I have this
47:27it's classified as a data analyst but
47:29the job title is business data analyst I
47:32could put something instead let's say I
47:33wanted to search for business analyst
47:35roles I could put in business percent
47:38sign and then oops that's not a percent
47:40sign and then analyst running this now
47:45I'll get things like that will meet the
47:47business data analyst that may be
47:49categorized as dat analyst maybe
47:51categorized as a business analyst but it
47:53picks it up now the percent sign
47:54symbolizes 01 or more characters let's
47:58say I just wanted to represent a single
48:01space in between something in that case
48:03I would use an underscore going back to
48:06in our example instead of having this
48:08percent sign here I'll put an underscore
48:10in this case I only want to find those
48:12that directly say business analyst so
48:15I'll expect to see this one in there
48:16whenever I run this query pressing
48:18control enter yep 343 I can see that
48:21anything has business space analyst I
48:23meet this along with anything on the
48:26left and right using the that percentage
48:28sign all right now it's your turn to go
48:30ahead and try this like keyword along
48:32with testing out those different Wild
48:35Card operators of the percent sign and
48:37underscore those that paid for the
48:39course certificate and notes you have
48:40some practice problems you can work
48:41through all those that don't feel free
48:43to just try out and see how this
48:45actually tests out for these different
48:46wild cards all right see you in the next
48:53one all right this short section is on
48:55aliases and and I can relate to this cuz
48:58sometimes I want to be a different
48:59person and sometimes even column names
49:01want to be different names this can be
49:04very helpful especially when working
49:05with other people to pass off data with
49:07these really convoluted column names you
49:10can rename it before giving it to them
49:12so let's say I want to use this table
49:14this core table that we've been querying
49:16from the get-go in a presentation and I
49:19don't want to have this as you can see a
49:21lot of it starts with job which is sort
49:23of redundant and then it's just the
49:25nameing conventions are just really
49:27weird well at least weird for somebody
49:28that's not familiar with this database
49:30somebody that's not familiar with this
49:32database just wants the quick
49:33information about what it is real quick
49:35so I can go through and add these as
49:37statements after these column titles
49:40right here and then they're now rename
49:43this running this query right now
49:45pressing control enter I can see all of
49:47them are renamed now as or alas can also
49:50be used not only for column names but it
49:53can also be used for tables and
49:56frequently you will see whenever people
49:58post abbreviations for tables they'll
50:00use a single letter or multiple letters
50:02at the start of the table so in this
50:04case I'm renaming it JPC and then if I
50:07wanted to although not required in this
50:09case I could put it at the front of
50:12these different tables right here and
50:15when I execute this query still going to
50:17work now we haven't gotten into joining
50:19multiple tables and whenever you do this
50:21is a very common practice so I wanted to
50:24make sure that you're aware of it before
50:25we get to that so we're going to be
50:27doing that now the one last thing to
50:29note on this is sometimes you'll read
50:31other people's syntax especially when it
50:33comes to tables they won't necessarily
50:36put this as keyword in there instead
50:39they're just going to have a space in
50:40between the two and it's still whenever
50:42I go to execute this it's going to go
50:44ahead and work you can also do this for
50:47these keywords in here but this I argue
50:49makes it hard to read but you will
50:52frequently see in this case here you
50:54will see it done without that as key
50:56keyword all right now it's your turn to
50:58try out aliases I have a practice
51:00problem for you to go and try out feel
51:02free to experiment around not only with
51:04those column names but also those table
51:05names all right see you the next
51:10one all right let's now combine what we
51:13just learned with wild cards and also
51:15that alias in order to solve a problem I
51:18want to continue to build on that last
51:19problem that we're looking for data
51:22analyst or business analyst roles but
51:24also we're looking for those that are
51:26not senior additionally we'll make
51:29aliases for the columns but they don't
51:30make sense now before I attack a SQL
51:32problem like this I like to break it
51:35down further into what are the actual
51:37steps that I need to look for for this
51:40so what's common among this is or what's
51:42not common among this is we want to look
51:44for data or business in it so that's
51:47going to be using basically an or
51:48statement we do want to look for the
51:50word analyst in there and then we don't
51:53want to include things like senior so
51:57let's jump in the and try it out all
51:59right so here's the core query that
52:00we're going to be working with I'm going
52:02to go ahead and start with Alias this
52:03first I just rename the location as
52:06location and salary is salary so let's
52:09just start attacking each one of these
52:11filters one at a time and once again
52:13we're going to be needing to use that
52:15wear keyword the first one we're going
52:17to be looking at is we're going to look
52:19at only job tells that include either
52:21data or
52:23business so I built this or statement
52:26now that captures that of matching the
52:28data or business and anytime we want to
52:30iterate through this to make sure that
52:31we're on the right track press control
52:33enter it looks like okay we got Data
52:35Business data Data Business okay so it
52:39looks like we're on the right track now
52:40we want to include anything that would
52:43be basically data analyst or business
52:45analyst so we want to meet this or
52:48condition and then basically the analyst
52:51is an and condition so what I'm going to
52:52do is actually wrap this in parentheses
52:56cuz this is is the or state we want to
52:57meet and then from there I'm going to go
53:01in and put in the one about the
53:06analyst all right so now we have the
53:08analyst one executing this one looks
53:10like now I have anything that has
53:12business or data along with analyst in
53:16it all right so finally this last one
53:18don't include any Jes with senior
53:20followed by the character so this would
53:22be another and statement here and we're
53:25going to be still searching job
53:27title and for this one we don't want to
53:30use that is like senior we want to match
53:34basically a not condition and so for
53:37this we can put not right before like to
53:40say not like this um do we have any
53:43seniors in here oh we got this one right
53:45here so let's see if this one's still
53:47here after we refresh this
53:51query and Yep looks like it was taken
53:54away so we're meeting all those
53:55conditions all right all right we got a
53:56query now let's jump into learning some
53:59more about
54:05operations all right let's get now into
54:07operations or more specifically
54:10arithmetic operations we'll be using
54:12operators for this like addition
54:14subtraction and even things like modulus
54:17and for this we're going to be using the
54:18invoice database which is located at
54:20this URL as a reminder this is a
54:22fictitious data set on data science invo
54:26voices throughout the year of 2023 we're
54:29using it for this because this these
54:30columns that it has in it are really
54:33good for doing arithmetic operations on
54:35now we have a column specifically on
54:37hourly rate assigned to a specific data
54:41nerd in this data set and let's say that
54:44our accounting department is actually
54:45trying to figure out whether they should
54:48raise these rates or lower these rates
54:51so we need to provide them with a data
54:53set with these updated rates well we can
54:55use something like subtraction ction or
54:57even something like addition in order to
55:00increase that rate we can use this
55:03within the select statement so this is
55:05the core query we're going to be working
55:06with in this case and we want to see
55:10what would be the hourly rate if we say
55:12dropped it by $5 or if we raised it by
55:15$5 so to make sure I'm doing this
55:17operation correct first I keep that core
55:21one that we want to change in here still
55:23and I'm going to rename that as rate
55:25original then from there all I'm doing
55:27is I have this hours rate and a minus5
55:31and that's going to go through in every
55:33single column subtract five and then I'm
55:35renaming this as the rate drop so let's
55:38actually execute this so we can see now
55:41that it's going through and it's
55:43executed this for a rate drop
55:45subtracting all these different columns
55:47by five conversely I can add this last
55:49statement here for the rate hike of
55:52adding $5 and we have that now of now
55:56this band or whatever we have this whole
55:58table made we can send this all to
55:59accounting for them to Crunch their
56:00numbers now I demonstrated this
56:03operation use inside of the select
56:05statement but we're not just limited to
56:07that it can be used in a whole host of
56:09keywords including things like the wear
56:11order by and group by that we've used
56:13previously and even more that we're
56:15going to use in the future so let's
56:17actually look at a case of how we can
56:19use this even outside of that select
56:21statement and for this we're going to be
56:22using the multiplication operator
56:25figuring out
56:26basically a total pay using hour spent
56:29times an hourly rate so going back to
56:32that core query that we have let's say
56:34that accounting comes back now and they
56:36say hey we want to only have the
56:38projects that are going to cost a total
56:42of $1,000 and more after the rate hike
56:45and this project total after the hike is
56:48going to be equal to that rate hike
56:51times those hours spent we don't have
56:54hours spent right now so I'm actually
56:56add add that here so we can see it pring
56:59control enter got now the hour spent
57:02we're going to do it times the rate hike
57:04I'm also going to remove this rate drop
57:06because we're not concerned with that
57:07right now okay so we want a filter for
57:10this so we're going to make a wear
57:13statement and we want this where the
57:15rate hike times that hour spent is going
57:18to be meet this condition of greater
57:20than 1,000 okay executing this query
57:24looks like it's going through usually I
57:26like to double check something like this
57:28whenever I'm doing a calculation so I'm
57:30going to go ahead and add this up here
57:32where I have this rate hike of hours
57:34rate plus 5 times hour spent as the
57:37project total go ahead and execute this
57:39to actually see what it is and scrolling
57:41over can see that hey yep everything
57:44here looks like it's greater than a
57:46th000 now as a best practice I want to
57:49go ahead since it's defined here replace
57:52it down here and then now executing it
57:56we still have the same thing and it's
57:58more concise and easier to read all
57:59right next up is the modulus operator
58:02and this operator is used to return the
58:06remainder available after a division
58:09specifically let's say we have a number
58:11of hours say something like 10 if we
58:13wanted to find out how many hours past
58:16eight that they worked we could take 10
58:20and do the modulus of eight and this
58:23would give us two that's showing that
58:25somebody worked worked 2 hours past this
58:28so let's say that accounting comes back
58:29to us and they want to do some analysis
58:32mainly on projects that take between one
58:34in 2 days they want to find all the
58:36projects that don't necessarily exactly
58:38end within 8 hours or 16 hours so the
58:42modulus so the first thing I want to do
58:44is actually visualize this and see what
58:46this is going to look like so pressing
58:48control enter I have now this hour spent
58:51modulus 8 and assigned to this extra
58:53hours scrolling over a little bit just
58:56to double check my work we can see that
58:58at whenever it's 14 we would expect 8
59:02goes into 14 once and then it has a
59:04remainder of six so accting wants to
59:07analyze these where these conditions are
59:09true or where the modulus is not
59:11necessarily zero so from here I can just
59:14put this wear statement into parenthesis
59:17at an end and then from there Define
59:20when extra hours are basically not equal
59:22to zero or greater than zero and we got
59:26it all right now it's your turn to give
59:28it a try we have some practice problems
59:30for those that purchase course
59:31certificate and notes you can go ahead
59:32and go and try out these different
59:34operations within the query all right
59:37that see you in the next
59:41one let's now get into aggregation
59:44functions and after using those
59:47arithmetic operators previously probably
59:50notice that yeah it's nice to be able to
59:52do this arithmetic operation across a
59:54row but what happens if we want to do it
59:56down meaning on we want to sum a row or
59:59even count up an entire row on how many
1:00:01values it has in it well this is where
1:00:02aggregation functions come in we have
1:00:04things like sum count average Min and
1:00:06Max now I commonly use these within
1:00:09things like the select statement but
1:00:11they can also be used with keywords like
1:00:14group by and also having for group by we
1:00:17can actually aggregate depending on what
1:00:20value is selected a certain subset in
1:00:23order to sum it up or even average it or
1:00:25maybe we can also use things like the
1:00:27having function which we've been talking
1:00:29about from the get-go and finally
1:00:30getting introduced to which allows us to
1:00:32actually filter data by an aggregation
1:00:35for this portion we're going to be using
1:00:37the job postings data set specifically
1:00:40we're going to focus a lot of time on
1:00:42this salary column and doing a lot of
1:00:45aggregation methods to it and the first
1:00:46aggregation method to talk about is sum
1:00:49and this is a function that allows us to
1:00:52inside the parentheses next to sum place
1:00:54things like a column name and from there
1:00:57it will Aggregate and actually sum up
1:00:59all those values so if I want to sum up
1:01:02all those average yearly salaries I
1:01:05could do this and it also give it an
1:01:06alias of salary sum pressing control
1:01:09enter you can see we have over $2
1:01:13billion worth of salary and job postings
1:01:16here next aggregation method is count
1:01:19and similarly to sum you're going to
1:01:21place either a column value or in this
1:01:23case you can always do something like a
1:01:25specializ operator the asterisk or Star
1:01:28to actually select all the different
1:01:29columns so in our case say we wanted to
1:01:32see not only what is the sum of all the
1:01:34salaries but we also wanted to see what
1:01:38was the count of all the different rows
1:01:40I could do a new line sending in count
1:01:43specifying that star symbol and then put
1:01:45from there as count rows from here
1:01:50pressing control enter it's going to
1:01:52aggregate the same now I have the salary
1:01:53on the left and then the count of all
1:01:55the RADS on the right now I can also use
1:01:57count in conjunction with another
1:01:58keyword distinct in order to filter down
1:02:02on distinct values let's say in our case
1:02:05we want to get a distinct count of all
1:02:07the different job title short values
1:02:10that we have in here say count then add
1:02:14distinct and then from there the actual
1:02:17job title short and then I probably name
1:02:20it something appropriately like job type
1:02:22Turtle pressing control enter we can see
1:02:25that we have 10 different job types now
1:02:27besides sum and count the other major
1:02:30types of aggregation functions are
1:02:32average Min and Max and as you expect
1:02:35they work all the same we're placing the
1:02:37column value that we want to average
1:02:39inside the parenthesis and then for
1:02:40there it's going to provide either
1:02:41average Min or Max so I could use that
1:02:44average column across that salary year
1:02:48average column and from there we're
1:02:50going to get this pressing control enter
1:02:53sign an Al an alias of salary average we
1:02:55can see that the average salary inside
1:02:57this column right now is around
1:02:59123,000 now I can take this a step
1:03:01further adding we on to this and then
1:03:04from there specifying the job title
1:03:06short column of data analyst and from
1:03:10here pressing control enter we can see
1:03:11that oh unfortunately that analyst the
1:03:14average salary is little bit less than
1:03:16the real average of uh only
1:03:19$93,000 and if I want to see the spread
1:03:21of the data analyst salaries I could add
1:03:23in those Min and Max functions in order
1:03:26actually go through and do that and
1:03:28there we see we have a Min of 25,000 and
1:03:30that Max of 650,000 now we saw how that
1:03:33average salary went from
1:03:35125,000 down to whenever we filtered it
1:03:37just for data analyst down to around
1:03:4090,000 so we want to dive deeper into
1:03:42this well this is a great way of using
1:03:44the group by keyword and this allows us
1:03:48to specify a column of interest in this
1:03:51case we're going to do the job title
1:03:52short in order to now filter down
1:03:55further and in our case actually be able
1:03:57to see all those different job titles
1:04:00and see where the disparity is so I'm
1:04:02going to get rid of this where statement
1:04:04in that case and I'm going to add Group
1:04:07by specifying job title short column now
1:04:11I can run this but um it does group it
1:04:15but I don't know what these groupings
1:04:16are so we need to add that into here so
1:04:18I add it in at the top as Jobs pressing
1:04:22control enter we now have this in and we
1:04:24can now see the aage average men and Max
1:04:27I'm going to throw in a quick order bu
1:04:28in order to group this by the average
1:04:31salary and as expected with that average
1:04:34being around 125,000 we can see that
1:04:36machine learning Engineers are middle
1:04:38data analysts have some of the lowest
1:04:40whereas senior data scientists and
1:04:42Senior Engineers have some of the
1:04:43highest now another popular keyword
1:04:46besides Group by that need have inol
1:04:47belt is having and having allows us to
1:04:51use an aggregation method to then filter
1:04:54by unfortunately this one my complaints
1:04:56were sequel where you should be able to
1:04:58do this inside of the wear keyword but
1:05:00it doesn't allow it so let's say we want
1:05:02to only analyze this further but for job
1:05:06postings that have basically a certain
1:05:08amount of values if I insert into here I
1:05:10want to find out how many actual counts
1:05:13of these different jobs they have first
1:05:15so we're going to add that into here so
1:05:17I've added this job count column in here
1:05:19to basically total up how many there are
1:05:20in there all right so actually looking
1:05:22into this list further we see we have
1:05:24quite a bit for some of these but then
1:05:26something like Cloud engineer we don't
1:05:29really have a lot of values in it and it
1:05:32may be skewing the data so let's say we
1:05:34want to exclude anything that has less
1:05:35than a job count of 100 well we can
1:05:38insert a having keyword and this needs
1:05:40to go between group buy and Order buy
1:05:44and I can specify it in this case hey we
1:05:46want to do this for the count of job
1:05:47ties shorts that are greater than 100
1:05:50pressing control enter that cloud
1:05:52engineer disappears is no longer one of
1:05:55our problems
1:05:56now you may be like luk can't we just
1:05:57use a we using this job count cuz it's
1:06:00no longer using this aggregation
1:06:01function well removing this having
1:06:04function right here and then keeping in
1:06:06that we when I actually go to run this I
1:06:08get this error there's a misuse of
1:06:10aggregate count you can't use it inside
1:06:12of wear it's still trying to do this
1:06:14aggregation inside the wear that's what
1:06:16we have to use having all right now it's
1:06:17your turn to dive in and try some of
1:06:19these practice problems we have for
1:06:20aggregation functions for those that
1:06:22purchase the course certificates and
1:06:23notes a lot of different ones to test
1:06:25out and try and go through all right
1:06:27that see you in the next
1:06:32one let's get into a practice problem
1:06:34combining what we've learned previously
1:06:38with the aggregation functions and also
1:06:40with those arithmetic operations
1:06:42specifically we're going to be going
1:06:43back to that previous problem where I
1:06:45was talking about the accounting
1:06:46department asking for us to provide
1:06:49numbers around what would happen if we
1:06:51would increase the hourly rate by $5 and
1:06:55so for for this we're going to calculate
1:06:56not only the total earnings for a
1:06:59project but then also a theoretical
1:07:02situation of what would happen if we
1:07:04increased it by $5 so previously we were
1:07:07taking that hourly rate displaying it as
1:07:09that rate original and then also doing
1:07:11the additional 5 to show that rake hike
1:07:14right now I also have that project ID
1:07:16right there conveniently that we're
1:07:17going to be breaking it down further by
1:07:19so let's first go at tackling that first
1:07:21problem of calculate the total earnings
1:07:23per project which we're going to be
1:07:24using our spent times hours rate here
1:07:27I'm going to first start by just adding
1:07:28in that hour spent because I want to
1:07:29keep track of it to make sure I do the
1:07:31calculations right from there I'm going
1:07:33to use the sum function in order to sum
1:07:36up the hour spent times that hourly rate
1:07:40I'm going to rename this project
1:07:42original cost from here I'm going to
1:07:44iterate through this so I'm going to add
1:07:46this up right now now there's a mistake
1:07:48in my SQL query if you haven't caught it
1:07:50yet as you can see we should have
1:07:52multiple different project IDs right
1:07:54we're trying to sum it based on that
1:07:55project idea that's at least that's what
1:07:57we want so we need to put a group ey in
1:07:59there and now I'm specifying project ID
1:08:02doing this bam I have this for all the
1:08:05different project IDs and doing some
1:08:07rough math that you always should do
1:08:08whenever doing any of these queries to
1:08:10make sure it's doing correct looks like
1:08:12it's doing that hour spent times rate
1:08:13original to get that project original
1:08:15cost now I add in this for that
1:08:18projection of what it's going to be if
1:08:20we were to increase that hourly rate by
1:08:23$5 and putting it within that sum
1:08:25function and then labeling it as a
1:08:27project projected cost now with this we
1:08:30can actually see it but there's a lot of
1:08:32data here I'm going to go ahead and
1:08:34actually cling this up to actually make
1:08:36it more visible and control enter now we
1:08:40have all of the different comparisons
1:08:42right here in here all right now it's
1:08:44your turn to give it a
1:08:49try this short section is going to be
1:08:51going over null values and we previously
1:08:54encountered n n values whenever we were
1:08:57looking at the salary column of our job
1:08:59posting data set and we filter filtered
1:09:02ascending so from low to high those null
1:09:05values appeared first a null is a field
1:09:09with no value and that differs from
1:09:12something like a value where it's zero
1:09:15because that does have a value in it
1:09:17although it is zero or when we're using
1:09:20some sort of string character maybe in
1:09:21it that it contains like a space that
1:09:24still would not be a null value because
1:09:26something is actually there taking up
1:09:28bytes of data we're going to be using
1:09:30this with the wear and having caused in
1:09:32order to filter out and look at this
1:09:33type of data so going back to that
1:09:35previous example where we're actually
1:09:37pulling those common columns that we
1:09:39have then ordering it by that salary we
1:09:42can see that said null values are
1:09:45appearing first so we can use that we
1:09:47keyword in order to filter this so I can
1:09:50specify that the salary yearly average
1:09:52is null pressing control enter yep it's
1:09:54still null or I can change this to more
1:09:56specifically is not
1:09:59null all right told you this section was
1:10:01short on null values now I do have a
1:10:02couple practice problems for you now to
1:10:04go in and try it out all right see you
1:10:06next
1:10:10one all right we're coming almost to the
1:10:12end of this basic section on SQL queries
1:10:16specifically we're going to be focusing
1:10:18on this one on joins now these are the
1:10:21four most common types of joins and
1:10:24we're going to be diving into each one
1:10:25of these separately talking about what
1:10:27their use cases are and an example of
1:10:29each so if you recall back from the
1:10:31beginning of this video when we first
1:10:33introduce that job posting data set well
1:10:36the majority of the time in this section
1:10:37we've been primarily focus on quering
1:10:41this job postings fact table right here
1:10:44and there's actually other tables
1:10:46associated with this in the database and
1:10:49right now you have access to all these
1:10:51different tables that we just spoke
1:10:53about inside this job
1:10:552023 database which I go ahead here and
1:10:59I actually just showcase each of the
1:11:00different ones quering into it one quick
1:11:03note you may be wondering why in general
1:11:05would you even have these different
1:11:07tables well in the case of that skills
1:11:09dimensional table we can see we used a
1:11:12skill ID to relate it and we have a
1:11:15skills column and a type column we could
1:11:18technically have this all of the skills
1:11:21and type inside the jobs fact table but
1:11:24this is going to be very
1:11:27repetitive also in the case that we have
1:11:29the SK skills of job table or sorry
1:11:31skills job dim table it allows us to be
1:11:34able to Aggregate and put in more than
1:11:38one skill so there's multiple reasons
1:11:40why you would actually have tables
1:11:42external to another table so the first
1:11:44join and by far the most popular join
1:11:46that I find myself using is a left join
1:11:49and what this is going to be doing here
1:11:50is what it's trying to Showcase with A
1:11:52and B are two separate tables and in it
1:11:56for this it's going to whenever we use
1:11:57this left join it will return all of the
1:12:00contents of table a and then whatever
1:12:03we're matching A and B on it's only
1:12:06going to return the contents from B that
1:12:09it matches a on so going back to quering
1:12:11that job postings fact table I have on
1:12:14here the job ID job title short and then
1:12:16Company ID so we have a lot of different
1:12:19job title shorts and I want to see what
1:12:21the company ID or what is that company
1:12:24it is if I want over to that company dim
1:12:26table as we're quering it here I could
1:12:29see what it is but it's over here I want
1:12:31to combine it with that fact table so
1:12:33the first thing I'm going to do is add
1:12:34this keyword of left join and then
1:12:37specify the table company dim have a
1:12:40little typo right here and we're going
1:12:43to then give it an alias I don't have to
1:12:44type this dim every single time now in
1:12:46addition to this we need to specify how
1:12:49we're going to be actually connecting
1:12:52these two tables we need to use the
1:12:54company ID from this job posting facts
1:12:57table to the company ID of the company
1:13:00dim table so putting that in there and
1:13:04then going ahead and executing it
1:13:06they're now connected but I haven't
1:13:07really brought anything over so now I
1:13:09can add that column from companies now
1:13:13remember this is why I previously talked
1:13:15about you can put the table name in
1:13:18front of a column and this is very
1:13:20important to do in order to make sure
1:13:22you're not confusing different
1:13:26column names between the two because you
1:13:28can see from here companies also has a
1:13:31company ID name but more importantly we
1:13:33want to get that company name so I have
1:13:35it here included as name from here I'm
1:13:37going to go ahead and press control
1:13:39enter to load this all in and so now we
1:13:42see we've now used this left join in
1:13:44order to join that company name along
1:13:47with those job titles this company ID
1:13:50isn't really necessary at this point nor
1:13:52did you need to actually do it from the
1:13:53get-go I was just doing it for
1:13:55illustrative purposes I'm going to go
1:13:56ahead and run this and actually remove
1:13:58it and then also going in and go in to
1:14:01renaming those column titles okay pretty
1:14:04cool left joins now for those that would
1:14:06like a more illustrative view of how
1:14:07this is being done that job postings
1:14:10fact is that table a that we're joining
1:14:12we're keeping all rows in it so it's
1:14:14that yellow and then we have that
1:14:16company's DM which is that table B in
1:14:18our case we have all the different
1:14:19companies available here so whenever we
1:14:22put these two together we then have that
1:14:24company name right available inside of a
1:14:27results table all right and as you
1:14:28guessed it if there's a left join
1:14:30there's probably a right join and this
1:14:32is going to be somewhat completely
1:14:34opposite we're going to be basically for
1:14:36the B table we're going to say hey we
1:14:39want to match all the contents of this
1:14:41and keep all the contents of this but
1:14:43anything that matches from that a table
1:14:45we want to join it now similarly we're
1:14:47going to be connecting backwards we're
1:14:49going to be connecting the company's
1:14:51table to the job posting fact table the
1:14:54problem is though is there's more
1:14:56records within the job posting fact than
1:14:58there are in the company's table so only
1:15:01a subset of data is going to be returned
1:15:03so going back to that illustrative
1:15:04example from before of merging those job
1:15:07posting facts to the company table if
1:15:10we're using a right join in this case
1:15:11it's going to do a very similar thing
1:15:14now the thing to note is the company's
1:15:16dim table only has one occurrence of
1:15:20Netflix or meta Experian whatever it may
1:15:23be and there's multiple in the job
1:15:25posting facts what's going to actually
1:15:26happen is when we get a results table
1:15:28there's whenever we join this up we may
1:15:31have that company appear more than once
1:15:32now so in this case I can go ahead and
1:15:35just replace that left one with a right
1:15:38press control enter we'll see that we
1:15:39have right now 33585 rows whenever I do
1:15:42it again this one's a little bit longer
1:15:45takes a little bit more longer time to
1:15:47actually compile and we have it back
1:15:50with all those previous roles so you may
1:15:52be like Luke what's the purpose of this
1:15:54right join then if it's basically the
1:15:56exact opposite thing of this left join
1:15:57well let's say for example in this case
1:16:00if we had more companies listed in that
1:16:03company dim table than we did in our
1:16:06fact table itself well in this case we
1:16:08could spot those irregularities by
1:16:11allowing us to now do this right join
1:16:13and ensure we are actually providing all
1:16:16the key details of that company table so
1:16:19although not necessarily common all the
1:16:21time it does come up from time to time
1:16:23so you should be aware of it all next up
1:16:25is inter jooin what this is going to do
1:16:27is when we have an A and B table
1:16:29whenever we go to combine them it's only
1:16:32going to return the contents that appear
1:16:34in both so if there's something in a
1:16:36that's not in b not going to appear in a
1:16:39and not going to appear in B so let's
1:16:40say there's a scenario where I want to
1:16:42look at jobs but I only want to look at
1:16:45jobs where a skill is available this is
1:16:48where inner join is going to come in so
1:16:50this is a fictitious making of our data
1:16:52set but we have the job postings fact
1:16:55table along with the job IDs and let's
1:16:57say that this skill job dim table is
1:17:00connected to it and let's also say it's
1:17:02an order by that job ID there aren't any
1:17:06necessarily skills associated with this
1:17:08job ID of three and four so we don't
1:17:11want to necessarily see that now this
1:17:14combination of these two tables is a
1:17:16single inner join in itself but as we
1:17:19talked about earlier there's actually
1:17:20another table so we need another inner
1:17:22join in order to do the same thing of
1:17:26filtering for only skills that are
1:17:28available in this case whenever we look
1:17:30at these skill IDs we can see that 1
1:17:33three and two are used 1 two and three
1:17:35which correlate to python R and SQL so
1:17:37we're not going to return anything that
1:17:39has Scala or Java in this case so let's
1:17:42actually build this query out working
1:17:44from that left to right the first thing
1:17:45we want to do is an injoin on that skill
1:17:48jobs Dimension table which we're going
1:17:50to be connecting on that job ID so I
1:17:52specify in join specifying the table
1:17:54table of skill jobs demm I've shortened
1:17:57that table or changed the name to skills
1:17:58to job and then specify what I want to
1:18:01meet it on I want to meet it on that job
1:18:03ID of both of those pressing control
1:18:06enter making sure that it works still
1:18:08working and in now in this case we're
1:18:11seeing that in the case of this job Ida
1:18:14machine learning engineer they have
1:18:16quite a bit and we're actually whenever
1:18:19we get into job ID number one we skip
1:18:22over it's no longer included because
1:18:23apparently it does have any skills
1:18:25associated with it so we're already
1:18:27seeing good that we're meaning on what
1:18:28we need to do and if I want to actually
1:18:31see all of those different values or
1:18:33those skill IDs that I see I can see
1:18:35okay that's why the machine learning
1:18:37engineer has so many and then moving on
1:18:39to the business data analyst but we're
1:18:41not done with this because we also want
1:18:43to make sure that we have everything
1:18:45only including those skills inside of
1:18:47here that are relevant to the Javas
1:18:49table so we want to also do an inner
1:18:50join with this table so I'm going to go
1:18:53ahead and add in that inter join
1:18:56specifying the skills dim as a skills
1:18:58table and then specifying that hey I'm
1:19:00matching the skill ID of these tables
1:19:03I'll also go ahead and add the skill now
1:19:07pressing control enter to go ahead and
1:19:09execute it bam we have now the machine
1:19:11learning engineer with a lot of
1:19:14different skills that it's requiring um
1:19:16business de analyst and so on so inner
1:19:19join along with that left join are two
1:19:23of the most popular type typ of joins
1:19:25that I'm going to be doing on a frequent
1:19:26basis depending on whether I need to one
1:19:30return all the contents of a table or
1:19:32two for the inner join only return those
1:19:35where it meets in both those tables the
1:19:38last join to talk about is a full outer
1:19:39join basically if you're going to have
1:19:41two tables whenever we join these
1:19:44together it's going to combine them
1:19:45together no matter whether there are
1:19:47matching ones on the a table or the B
1:19:49table frankly as a data analyst I never
1:19:52have a need for this and don't really
1:19:54have an ex example use case for his go
1:19:55through and I think it's sort of a waste
1:19:57of your time so we're not going to
1:19:58really focus on this but mainly we're
1:20:00going to just have it in the back of
1:20:01your mind to know that this is available
1:20:03all right now it's your turn to give it
1:20:04a try if you've purchased the course
1:20:06notes and certificate you have some
1:20:07practice problems for you to go in and
1:20:09jump in and try out with a inner join
1:20:11and a left join all right bet see you
1:20:13the next
1:20:19one as we're coming to the end of this
1:20:21basic section there's a concept that you
1:20:23need to be aware of of especially as you
1:20:26become more advanced in SQL and that is
1:20:29the order of execution of SQL queries
1:20:32now whenever we send a SQL query via
1:20:35this flow path right here the statement
1:20:38itself into a database it goes through a
1:20:40few different processes before it then
1:20:42goes and actually is executed this
1:20:44execution is broken down into multiple
1:20:47steps once this is done within the
1:20:49database itself these results come back
1:20:51to you now if you notice we have this
1:20:53step first of the this parser now I
1:20:56don't want this to be confused with an
1:20:57earlier concept that we talked about
1:20:59that handles in the earlier steps of
1:21:01this in between the parser and Optimizer
1:21:03and that is the order to write commands
1:21:06as we've talked about multiple times you
1:21:08have to go or you have to write this
1:21:11query syntax in a certain order you
1:21:14can't have a where statement at the very
1:21:15beginning has to follow that from
1:21:17statement once our SQL query we've built
1:21:20has meet these conditions and has gone
1:21:22through that Optimizer it then gets into
1:21:24into the execution phase and that's
1:21:26where the order of this execution right
1:21:28here is comes into importance and it
1:21:31follows this general order that we're
1:21:33following right here now you don't need
1:21:35to have this order memorized mainly I
1:21:38just remind you of this because it's
1:21:39going to be important one day down the
1:21:41road when you're writing a SQL query
1:21:43well this order is actually very
1:21:45important because from the perspective
1:21:48of the database this ensures that it's
1:21:50processed efficiently and logically
1:21:51right now we're working with a
1:21:52relatively small database and every time
1:21:54you quer it notice it probably returns
1:21:56it in less than a second but if you get
1:21:58it to billions of rows of data this can
1:22:00take seconds upon minutes and by
1:22:03understanding this order we could
1:22:04potentially early in this phase filter
1:22:07down our data in order to speed up this
1:22:10query so just keep this tool in your
1:22:12tool belt whenever you're approaching a
1:22:14very complex query which we're going to
1:22:16be doing in the advanced section and
1:22:18also within the portfolio project if we
1:22:21need to speed up a query we're going to
1:22:23start here by looking at at the order of
1:22:25these and working earlier up in this
1:22:27sequence to filter down and make our
1:22:29data as small as possible to speed up
1:22:32those queries all right that see you the
1:22:34next
1:22:38one all right let's wrap up this section
1:22:41on the basics by doing a practice
1:22:43problem and for this we're going to be
1:22:45focusing on the joins in order to build
1:22:49a more complicated query using left
1:22:51joint specifically I'm going to find
1:22:53something I feel that's pretty
1:22:55interesting for a given skill I want to
1:22:58find the number of job postings for the
1:23:01skill itself and also the average salary
1:23:04as expected we're going to be using that
1:23:06left join to combine our skills table to
1:23:09our job postings table also have a few
1:23:11hints along the way go help us build
1:23:13upon this process let's jump into it
1:23:15first thing I'm going to do is query the
1:23:18skills table or the skills dim table
1:23:20rename a skills and then from there just
1:23:23get back all the different skills that
1:23:24we have available so right now we have
1:23:27225 skills let's build upon it further
1:23:30after now that we've gotten the skills
1:23:32from the skill gy table I want to count
1:23:34how many job postings mention each skill
1:23:37from the skills to job to gym table so
1:23:39the first thing I'm going to do is add
1:23:40in this left join in order to go in and
1:23:43combine that skills gy uh skills job dim
1:23:47table which I've renamed skills to job
1:23:49linking it on that skill ID now going to
1:23:53that table we can see have a skill ID
1:23:54and the job ID so with this we can get a
1:23:58count of the jobs in there okay and we
1:24:01have this back but we now we only have
1:24:02it for one python because now right
1:24:05we're using an aggregation function so
1:24:08we need to group it so I'm going to
1:24:09group it by the skills column and then
1:24:11we have this along with those number of
1:24:14job postings all right now following
1:24:16along my plan of action we've now have
1:24:20the skill names the number of job
1:24:21postings I want to now calc calate the
1:24:24average Yer salary for the job posting
1:24:27associated with each skill so we need to
1:24:29bring in another table in specifically
1:24:32back to that jobs fact table in order to
1:24:36get that salary that we need so once
1:24:38again I do another left join bring in
1:24:40that job posting facts importing it in
1:24:42as job postings linking it on that job
1:24:45ID making sure that it still executes
1:24:47it's running fine so now we have access
1:24:50to that average year salary so I can now
1:24:53go ahead and add in the aggregation
1:24:56function for that salary year average I
1:24:59renamed it average salary for skill and
1:25:03we'll go ahead and enter and I forgot a
1:25:06comma so we'll go ahead and add that and
1:25:09press control enter and Bam we got it
1:25:13all right now anytime I get any type of
1:25:14results like this um we pretty much have
1:25:17gone through our entire path um but
1:25:20anytime we get um something like this we
1:25:22want to order it by something I find the
1:25:25most for me I want to see it ordered by
1:25:27salary and for this I'm going to specify
1:25:29for the average salary for skill I'm
1:25:31going to put it in descending order and
1:25:33Bam there we have it actually opening
1:25:36this up all the way this is actually
1:25:38pretty insightful you get to see a lot
1:25:40of these skills especially around web
1:25:42development are more higher paying and
1:25:45then more basic skills like something
1:25:47like Microsoft list vb.net get out of
1:25:51here webx are uh lower paying all right
1:25:54sweet now it's your turn to give it a
1:25:55try and after this this wraps up the
1:25:59entire basic section and now we're going
1:26:00to be moving into more advanced concepts
1:26:03so take a second to reflect on what
1:26:05you've learned so far you really have
1:26:06come a long way I mean look at this we
1:26:09just wrote a 12 line query and hopefully
1:26:12if you're keeping up with this you
1:26:13understand everything that's going on in
1:26:15this that's pretty impressive all right
1:26:17with that see you in the advanced
1:26:19section then nerds welcome to the
1:26:22advanced section of this course and this
1:26:24portion will be on about what the
1:26:26advaned sections on and more
1:26:28specifically how we're going to be
1:26:30setting up a database locally on your
1:26:32computer using postgress now since we're
1:26:35going to be have this database locally
1:26:37you're going to be able to do a lot more
1:26:38with this specifically going be able to
1:26:40do things like manipulate it creating
1:26:42altering dropping tables doing a whole
1:26:45host of things and actually getting into
1:26:47the core of the power of SQL now also in
1:26:50this section we're going to be obviously
1:26:51covering a little bit more advanced
1:26:53topics such such as how to handle case
1:26:55Expressions subqueries CTS and even
1:26:57unions so it's a whole host of things
1:27:00that I use from time to time and it's
1:27:03who of you to know so previously in the
1:27:05beginner section we were using inside
1:27:07your browser sqlite viz and this
1:27:10actually loaded inside your web browser
1:27:13the SQL database that you were then
1:27:15writing these queries to and executing
1:27:17and then getting those results back now
1:27:19this tool is great especially when
1:27:21you're practicing and learning SQL in
1:27:23order get it up and running quick
1:27:25without any setup but this is not how
1:27:28you're actually going to be interacting
1:27:30with it in the real world so I wanted to
1:27:32provide a scenario similar to how you're
1:27:34going to be doing it in a real world
1:27:36scenario so instead of using this app
1:27:38we're going to be using popular editor
1:27:41option VSS code and VSS code allows you
1:27:45to track all your different SQL files
1:27:46that you're using and then also connect
1:27:49to a host of different SQL databases but
1:27:52we'll get to vs code in a little b as
1:27:55the more important thing that we need to
1:27:56actually get installed is the database
1:27:58itself and we're going to be using
1:28:00postgress with this which is an easy to
1:28:03download program which about to go
1:28:04through and able to get it running
1:28:06locally on your machine now as a quick
1:28:09refresher why are we using postgress for
1:28:11this well looking at the 2023 stack
1:28:15Overflow survey the results from this
1:28:18concluded that postgress is one of the
1:28:20most popular options among developers
1:28:23now there's a lot of other popular
1:28:24options in the datalux community
1:28:26especially things like MySQL SQL light
1:28:28like you've already used and SQL server
1:28:31and the concepts that you're learning in
1:28:33this course regarding specifically with
1:28:35the SQL syntax can be applied to these
1:28:38different databases so even if you don't
1:28:41use the number one popular option it's
1:28:43still going to be able to have and use
1:28:44those skills in other databases also
1:28:47last data point I promise before we get
1:28:49into the downloading with postest itself
1:28:51it is topping the charts at the most
1:28:54admired and also the most desired SQL
1:28:57database technology that most admired
1:28:59data point is the proportion of users
1:29:01that use postgress and want to continue
1:29:04to use it whereas that desired metric or
1:29:06the blue is the proportion of
1:29:08respondents who want to use this
1:29:10technology and in both of these cases it
1:29:13exceeds all of its competitors and so
1:29:15that's why I think it's a great database
1:29:17for you to get into all right let's get
1:29:19into downloading postest and for this
1:29:22you don't have to have a lot of computer
1:29:24it doesn't take up a lot at all and so
1:29:26you're going to navigate to this URL
1:29:28right here or feel free to just Google
1:29:29postgress and it will take you right
1:29:31here anyway I'm going to click download
1:29:33and from here we're going to select our
1:29:34operating system of choice I have a Mac
1:29:36right now but I've verified that the
1:29:39installation instructions are exactly
1:29:41the same for a Windows user so you're
1:29:43going to follow the exact same steps now
1:29:45I'm going to download the installer now
1:29:47this has a few different options
1:29:50available and also I'm not sure why you
1:29:51had to select the operating systems at
1:29:53the beginning cuz you're just selecting
1:29:54it here anyway we're going to go with
1:29:56the most current version so
1:29:5816.2 I've done this previously on older
1:30:00versions like 10 so don't worry if you
1:30:02have an older version this still should
1:30:04still work so navigate over to your
1:30:06download folder you should have some
1:30:07sort of installer package like this and
1:30:10just go ahead and click on it it may
1:30:11prompt you if you're sure you want to
1:30:13open from the internet yeah go ahead and
1:30:14open it now we're going to walk through
1:30:15the setup process for a lot of these
1:30:18things we going to be keeping it it's
1:30:19default such as this location right here
1:30:21and as far as installing all these
1:30:23different packages I do want them so
1:30:25we're going to continue once again
1:30:26default for the data location next is
1:30:29password and this is very important that
1:30:31you remember this password because this
1:30:33is going to have to be used in order to
1:30:35access the database and even start it up
1:30:37so if you need to write down what you're
1:30:39going to have for your password for the
1:30:41port number don't change it if you want
1:30:43to change it from this 5432 make sure
1:30:46you write it down as well for the local
1:30:48keep default I don't know what this is
1:30:50okay setup's not ready to install so
1:30:52let's get to installing it all right
1:30:54looks like it's installed and it's asked
1:30:55if it wants to launch stack Builder
1:30:57which can install additional modules to
1:30:59help you out we're not going to need
1:31:01this right now so I'm not going to
1:31:02launch it up and I'm going unclick that
1:31:03check mark So now let's launch postgress
1:31:07and anytime you're wanting to actually
1:31:09query this database that we're going to
1:31:11be building on here you need to do this
1:31:13so for some reason you restart your
1:31:15computer and bring it back up you need
1:31:17to restart postgress anyway going to
1:31:19navigate into here this folder postgress
1:31:21SQL 16 and go into to PG admin there's
1:31:25the admin dashboard of how you're going
1:31:27to control it and it's loading up so I
1:31:30have it up and running over on the left
1:31:33hand side this little pane is going to
1:31:34have all your different servers and
1:31:35database and all the configuration with
1:31:37it and then right hand side sort of like
1:31:38think of it like your editor where you
1:31:40can do a bunch of configurations anyway
1:31:43I'm going to go ahead and click this
1:31:44open and I have an older edition of
1:31:47postgress well databases at least on my
1:31:49system we're not going to worry about
1:31:51that if you only have one you should see
1:31:53that 16 we'll go ahead and open it up
1:31:55this is where you have to have your
1:31:57password memorized so I'm going to go
1:31:59ahead and put it in I'm also going to
1:32:00click save password and then go Fed so
1:32:03now that I've done that it's going ahead
1:32:05and started up my databases I can see
1:32:07this in here by navigating into
1:32:09databases and right now there's only one
1:32:13these are the contents of the database
1:32:14there's only one database in there and
1:32:16it's conveniently titled postgress
1:32:19anyway now Beyond this inside of PG
1:32:21admin is basically beyond the scope of
1:32:23this course as you can come in here and
1:32:26actually run SQL queries to thus
1:32:28interact with your database but then
1:32:31what happens if you want to use another
1:32:33about database such as like MySQL or SQL
1:32:35Server you're not going to use PG admin
1:32:36for this so we're not going to be
1:32:37focused on this for the remainder of
1:32:38this course just understand that you do
1:32:40have to launch PG admin in order to get
1:32:43your database running with that let's
1:32:45get into getting the code editor so we
1:32:46can actually run some SQL
1:32:51queries all right in this section we're
1:32:53going to get into installing vs code
1:32:56which going to be our code editor in
1:32:58order to run our SQL queries now what is
1:33:01a code editor and this is a location
1:33:04that you can go and organize any type of
1:33:06code you may have in our case we're
1:33:08going to have these SQL queries written
1:33:09out inside of a SQL file and so from
1:33:12there we want to keep it inside of a
1:33:13code Eder now another term to keep in
1:33:15mind is integrated development
1:33:17environment also known as idees Ides are
1:33:21basically a text editor on steroids it's
1:33:25debatable of what exactly VSS code is
1:33:27technically it's a code editor that
1:33:29we're going to be using but because of
1:33:30the additions we're going to add to it
1:33:32it functions like an IDE nonetheless the
1:33:35terms are going to come up and I want
1:33:36you to be aware of it so once again if
1:33:38we go over to stack Overflow to see what
1:33:40are the popular options among developers
1:33:42right now Visual Studio code also spoken
1:33:46vs code is by far the most popular
1:33:49option on the market so that's frankly
1:33:51why the reason why we're using it and I
1:33:53just frankly like it in general now VSS
1:33:55code is a code editor for a multitude of
1:33:58languages but there are editors that you
1:34:01can use that are specific to SQL and I
1:34:04want you to be aware of them in case
1:34:06down the road you want to decide to use
1:34:07it specifically down here if we scroll
1:34:09down data grip that's one from the team
1:34:12over at jet Brains it's a very popular
1:34:15option although paid and another popular
1:34:17option that didn't make that list there
1:34:19but I know about it from the DAT
1:34:21analytics Community is De Beaver this is
1:34:23is a free cross-platform database tool
1:34:25for developers and it supports things
1:34:28like postgress an app like this I would
1:34:31say is actually more powerful when it
1:34:33comes to running SQL queries as you can
1:34:36do a lot more functionality with it in
1:34:38regards to SQL but then if I want to run
1:34:41other different languages such as python
1:34:42or R I can't do it inside of this that's
1:34:45why as me as a python user I stick with
1:34:48vs code anyway let's get into
1:34:49downloading and setting up visual studio
1:34:51code so navigate to this URL right here
1:34:55where you're going to go and then
1:34:56download it you can download it for
1:34:58either Mac or Windows I would get the
1:35:01actual stable version and actually get
1:35:03it this installation process is a lot
1:35:05simpler as once you unzip that file that
1:35:08it gives you in like your downloads
1:35:09folder you'll have the app available
1:35:11from there you'll take it and you drop
1:35:12it into your applications folder from
1:35:14there actually navigate into the
1:35:16applications folder and then start this
1:35:18bad boy up so getting into a quick
1:35:20overview of how VSS code works
1:35:24over on the left hand side we have our
1:35:26activity bar and I have a few extra
1:35:28icons right here don't worry about that
1:35:30too much but overall I find that I'm
1:35:32using this right here this explore and
1:35:34this is going to display all my
1:35:36different files on the Le hand side I
1:35:38can also do things like search through
1:35:40my files and then even add extensions
1:35:42which we're going to get to in a second
1:35:43so let's actually create this project
1:35:45folder that we're going to be working
1:35:46within so I'm going navigate back here
1:35:48to the explore and click open folder I'm
1:35:50going to navigate to wherever I want
1:35:52this project folder to be I keep mine
1:35:54within a developer folder and I'm going
1:35:56to add this new folder of where we're
1:35:58going to title it I'm title it SQL
1:36:01project data job analysis I use
1:36:03underscore in between this it's just for
1:36:06coding purposes you don't have to if you
1:36:08want to but I like to now with this
1:36:10folder selected I'm going to select open
1:36:13now that we're inside of this folder
1:36:15right here titled SQL project. jobs I
1:36:18can access or add a file by directly
1:36:21just selecting this icon of adding a
1:36:23file and then naming it appropriately so
1:36:25I named this one test. SQL and that's
1:36:28going to create a SQL file now we have
1:36:31the file located on the right hand side
1:36:33and this is where the editor portion of
1:36:36it is so I just wrote some SQL code just
1:36:39to Showcase that you can do this and
1:36:41it's possible you may have the option
1:36:44down here or you should have the option
1:36:45down here to select your language mode
1:36:47depending on what language you're using
1:36:50you'll want it to actually check this if
1:36:53you don't happen to see this down here
1:36:54come down to this Bottom bar down here
1:36:56right click it and then from there you
1:36:58should be able to select editor language
1:37:01anyway we want SQL for this CU we want
1:37:03to check it to make sure that it's
1:37:04correct if I were to select something
1:37:06like python it's well it's not really
1:37:09checking anything but right here it's
1:37:11going to throw some errors now because
1:37:13it understands with this yellow and this
1:37:15red under statement that this isn't
1:37:17correct python so I'm going to select
1:37:19SQL and those errors go away anyway we
1:37:22can write SQL now in here but the
1:37:25problem is we don't have it connected to
1:37:28our actual database so we need to set it
1:37:31up to where VSS code inside of our
1:37:33editor now connects to this database
1:37:36that is running on our computer so
1:37:38navigate over to the activity bar and
1:37:40select extensions for this you're going
1:37:43to go into the search bar and look for
1:37:47SQL tools doesn't have any spaces in it
1:37:50so that's why it took me a little bit to
1:37:51find anyway we're going to go on ahead
1:37:53and select this this whenever we click
1:37:56install is installing an extension to
1:37:58thus supercharge this code editor of vs
1:38:02code to allow to connect to SQL so we're
1:38:04going to go ahead and click install now
1:38:05if you read the front print of this it
1:38:07has that to use SQL tools you'll also
1:38:09need to install the appropriate driver
1:38:12extension for your database a driver is
1:38:14now used to connect this extension to a
1:38:17database going back to the search bar
1:38:19I'm going to type in SQL tools and then
1:38:21also postgress SQL
1:38:24and pressing control enter we have it
1:38:27right here this is called SQL tools
1:38:29postgress SQL cockroach driver so this
1:38:32installs uh the driver for all these
1:38:33different things and click install so I
1:38:35can go ahead and close out of this no
1:38:37longer need extensions going to explore
1:38:39so SQL tools should be installed on here
1:38:42right now unfortunately if I zoom out by
1:38:44pressing uh command back I can actually
1:38:47see it it's located right here it's
1:38:49pretty important for me so I'm going to
1:38:51just put it right up there anyway I'm
1:38:53going to zoom back in and some other
1:38:56things are going to hide anyway click on
1:38:58SQL tools so it should have add new
1:39:00connection so we're going to be adding a
1:39:02connection of this postgress this
1:39:05initial database in here anytime you
1:39:07want to add a new database you have to
1:39:09use this or set up this add new
1:39:11connection which also can be done bya
1:39:12this icon right here so I'm selecting
1:39:15add new connection selecting that it's
1:39:17postgress this is just the core
1:39:19postgress database so I'll use the
1:39:21connection name of postgress I'll keep
1:39:23all the default settings the way they
1:39:24are for database I'll keep it postgress
1:39:27for username I'll also name it postgress
1:39:30it's going to be really easy for the
1:39:32password we're going to be using SQL
1:39:33tools the driver that we've installed
1:39:35before that way to verify the
1:39:37credentials scrolling on down I want to
1:39:40say test connection and it says hey this
1:39:43extension wants to sign in using the
1:39:45driver credentials I will allow it and
1:39:47then from here now we need to enter in
1:39:49that password from before that we access
1:39:52postgress with
1:39:54enter again in press enter boom so
1:39:56successfully connected I'm going to save
1:39:58connection now all right so closing out
1:40:01this now this postrest database is not
1:40:04the database you can see by this
1:40:05database icon right here um is not what
1:40:08we're actually going to be using for
1:40:09this course this is just a core one
1:40:11there I don't really want to mess with
1:40:13it let's actually create our own
1:40:15database that we're going to be using
1:40:17for this course so if we come up here
1:40:19selecting on the database itself and
1:40:21select new SQL file I can then use this
1:40:25to run a query on that database now this
1:40:28SQL file is connected to that postest
1:40:31database and I know this because if I
1:40:33come down here to this bar down here I
1:40:36can see that it says postest and this is
1:40:38because of the SQL tools if you don't
1:40:39have this rightclick it and make sure
1:40:41that SQL tools extensions is uh has a
1:40:44check mark next to it in order to show
1:40:46it so that's the shows what database
1:40:48you're connected to that you're about to
1:40:50run a query on that if you P this run on
1:40:52active connection it's going to do it
1:40:54side note real quick you may see during
1:40:56this this press command I to ask GitHub
1:40:58co-pilot chat to do something start
1:40:59typing dismiss just ignore that this is
1:41:02my GitHub co-pilot an AI coding
1:41:04assistant that I use to actually write
1:41:06SQL queries but that's for a whole
1:41:08another video just ignore that for the
1:41:10time being I may use it from time to
1:41:12time if I do I will explain it anyway
1:41:14I'm going to enter in this SQL command
1:41:17right here of create database SQL course
1:41:21and this is going to create a new
1:41:23database named SQL course so I can
1:41:26select run on active connection or I can
1:41:29highlight whatever SQL code I want to
1:41:32run right click it and then from there
1:41:35go into actually running the selected
1:41:38query which is conveniently on Mac the
1:41:41shortcut command e command D you have to
1:41:43press it twice anyway I'm just going to
1:41:45highlight it press command e it's going
1:41:47to say hey command e was pressed waiting
1:41:49for second key of chord press command e
1:41:51again anytime you run a query this SQL
1:41:54tools extension is going to have a popup
1:41:57on the right hand side if you're running
1:41:59a query asking for a table back or some
1:42:01results back it's going to display it
1:42:03here for us we just created a database
1:42:06so we're not going to actually see
1:42:08anything anyway I'm going to close this
1:42:10out now this new database is created I
1:42:15can go back to PG admin I can come up
1:42:18here and I can actually see that's
1:42:19created by right clicking postgress SQL
1:42:2216 and click cing refresh now we have
1:42:26two we have postgress and we have that
1:42:28SQL courses I need to go ahead and click
1:42:31that right now it was gray out and next
1:42:33out that means it wasn't running so now
1:42:36it is running and we have this we're
1:42:37able to connect to it so we're going to
1:42:40go ahead now and create a new connection
1:42:43once again we want to connect to this
1:42:44going to go through the same process I'm
1:42:46going to name it SQL course as far as
1:42:49the database name itself the name is SQL
1:42:52course for the username I'm going to
1:42:54keep it the same of postgress then from
1:42:57here once again I'm going to test
1:42:59connection it's asking it can it use the
1:43:02driver connections yes enter in the
1:43:04password successfully connected I want
1:43:07to save the connection all right so I'm
1:43:09going to close out of this I also don't
1:43:11need this create database anymore so I'm
1:43:12going to close out of this I don't want
1:43:14to save it and then now we have this
1:43:17when we click it it's available down and
1:43:21here's the database itself now notice
1:43:23now that I've clicked on it down at the
1:43:25bottom I have SQL course selected and I
1:43:29could navigate between the two we're
1:43:31going to leave it on SQL course for
1:43:33basically the remainder of this video
1:43:35all right we now have vs code completely
1:43:36set up for us I don't really care about
1:43:39this test. SQL file anymore I'm going to
1:43:41go ahead and delete it you also may see
1:43:43this vs code file this is like your
1:43:45configuration all this kind of crap in
1:43:47there don't worry too much about what's
1:43:49in there and how it gets updated just
1:43:51keep it there so this concludes the inst
1:43:53on vs code we're actually be moving into
1:43:55actually understanding more about
1:43:57creating deleting and dropping tables
1:44:00before that we need to cover some data
1:44:03types because it's sort of a
1:44:04prerequisite to understand creating
1:44:06tables so that see you in the next
1:44:12one all right in this short little
1:44:14section we're going to cover data types
1:44:17and it's really important to understand
1:44:19data types because we're about to be
1:44:21setting up and creating tables when to
1:44:23recreate those tables we have to specify
1:44:26the data type for each of the columns
1:44:27now if you recall previously whenever we
1:44:30were working with our job posting data
1:44:32set I didn't call it out specifically
1:44:34but the columns were already set up to
1:44:36handle a specific data type the job ID
1:44:40was only a numeric integer so in this
1:44:43case it was specifi as int or integer
1:44:46the job title column had strings in it
1:44:49and so that was characterized under
1:44:51varar job work from home was a false
1:44:54zero value or True Value which is one so
1:44:57therefore this was a Boolean job posted
1:45:00date was timestamp and then salary year
1:45:02average could have decimals in it so
1:45:04it's not an INT it's going to be numeric
1:45:07anyway why is this important well these
1:45:10are necessary when setting it up in
1:45:12order to have data Integrity within your
1:45:15database now if we were to go and add
1:45:18data to a database if it did not meet
1:45:21the conditions of being an integer to be
1:45:24inserted into something like the job ID
1:45:27then we wouldn't be able to insert it
1:45:29this is really good at just having a
1:45:30first line of defense of having clean
1:45:33data now additionally because of these
1:45:35characterizations of these data types it
1:45:38also makes these SQL databases a lot
1:45:41more efficient in processing queries it
1:45:43doesn't have to guess what the data type
1:45:45is inside of a column for integers it
1:45:48automatically knows and it can process
1:45:49this data a lot more efficiently now if
1:45:52I navigate over to the postgress
1:45:53documentation they have a whole host of
1:45:56data types that you can look at we're
1:45:58only going to just focus on a few for us
1:46:00and what we'll be working with only
1:46:02really think you need to focus on these
1:46:04top eight that I use on a common basis
1:46:06there's only a couple details I want to
1:46:08discuss about this so the first two int
1:46:11and numeric int is for an integer
1:46:13numeric is for a decimal so in the case
1:46:16of this we would specify in parentheses
1:46:17Precision which is the number before the
1:46:20decimal place and then scale the number
1:46:23after the decimal place that we're going
1:46:24to have in this next is text and then
1:46:27varar I'm just calling it varar I don't
1:46:29know what other people call it anyway
1:46:31text has an unlimited length for the
1:46:34amount of variables that we can put
1:46:35within it or the amount of string
1:46:36characters we can put in it I don't like
1:46:38to have just this type of Freedom so
1:46:40we're going to use varar mostly with
1:46:42this n variable where inside of it you
1:46:45typically specify a value such as
1:46:47something like 255 255 characters that
1:46:50it's going to allow for the maximum
1:46:51length the next is bul and this is going
1:46:53to accept either true false or null
1:46:56remember our case we had zero or one
1:46:59this is effectively true or false and
1:47:00then finally those around dates and
1:47:02times we have a date one where it's just
1:47:04date alone time stamp where it's a date
1:47:07and a time and then finally time stamp
1:47:10with time zone where we can go ahead and
1:47:12specify even further what time zone
1:47:15we're in all right so that covers the
1:47:16data types and this is important because
1:47:19as you can see from the SQL query right
1:47:20here whenever we go to create our tables
1:47:24we're going to need to go ahead and
1:47:26specify the data type that we want to
1:47:28make a column this is going to be very
1:47:31important like we said for data security
1:47:32and with that I'll see you in the next
1:47:39one all right let's now get into
1:47:41manipulating tables specifically we're
1:47:42going to be creating modifying and even
1:47:44deleting tables all within that core
1:47:47database that we created titled SQL
1:47:49courses now there's four main ways we
1:47:52can manipulate at a table first one's
1:47:54pretty easy just create a table we're
1:47:56creating it from stratch next is insert
1:47:58into so once we have a table actually
1:48:00inserting in Columns of data into it
1:48:03next is altering a table we can add
1:48:06additional columns even remove them
1:48:08change types and then finally just
1:48:11deleting a table whatsoever anytime
1:48:13you're doing any of these type of
1:48:14statements you need to be very careful
1:48:17and double check what you're about to do
1:48:18twice because once you do it you're not
1:48:22able to NE necessarily just click undo
1:48:24so picking up from where we left off
1:48:25last in the course first inside of PG
1:48:28admin make sure that your database is
1:48:30still running if it's not you just
1:48:32navigate into it and it'll start right
1:48:34up and here we have SQL courses the
1:48:36other thing to verify is inside of VSS
1:48:38code navigate over to SQL tools verify
1:48:42that that database is still listed there
1:48:44so it is SQL course and also that's
1:48:46clicked on so that way it's selected
1:48:48down here on the bottom for where we're
1:48:50going to be running all these queries or
1:48:52about to do all right so I'm going to
1:48:54come up here and we're going to create a
1:48:55new SQL file in order to run our queries
1:48:58on for me I notice this run on active
1:49:00connections may disappear or may stay up
1:49:02there don't worry about too much about
1:49:04that the first statement we're going to
1:49:05look at is actually creating a table the
1:49:08Syntax for this is relatively simple
1:49:10we're going to be using Create table
1:49:12along with that table name and then
1:49:13enclosed in within parentheses is the
1:49:17column names along with that data type
1:49:19and you can list as many column names as
1:49:21necessary to specify of the table here's
1:49:23our situation we're going to create a
1:49:25table called job applied and this is a
1:49:28table basically that we can track all
1:49:29the different jobs we've applied to in
1:49:31the past when I've been applying for
1:49:33jobs I've made a table similar to this
1:49:36usually in an Excel spreadsheet but
1:49:37we're going to do this in Excel and this
1:49:38includes things like maybe a resume I'm
1:49:41using cover letter a contact person at a
1:49:43company and then all those different
1:49:44companies I'm applying to so we're going
1:49:46to create this table called job applied
1:49:49and then I'm going to put those in
1:49:50closing parentheses around it with a
1:49:53semicolon at the end to signify the end
1:49:55of the statement from there we're going
1:49:56to be putting everything that we need to
1:49:58inside of it so anytime you have any
1:50:00type of database you usually want some
1:50:02sort of ID number so we're going to
1:50:03start with the Java ID number specify
1:50:05that it's an integer we'll also be
1:50:07specifying other portions of the column
1:50:09specifically application sent date
1:50:12custom resume whether we've modified it
1:50:14or not we can include what version it is
1:50:16the resume of the file name a cover
1:50:18letter whether it's sent or not and then
1:50:20the file name of that cover letter and
1:50:22then just a status in General on what is
1:50:24the job search going on with that
1:50:26specific job ID now that we have the SQL
1:50:28query generated let's actually navigate
1:50:29into SQL tools and then we're going to
1:50:32look down this first I want to show if I
1:50:34navigate into the SQL courses if I go
1:50:36down to this drop down schemas and then
1:50:38the tables there's nothing here right
1:50:40now so if we create this table it's
1:50:42going to go into there now for some
1:50:44strange reason this green icon
1:50:46indicating the active connection
1:50:48switched back to postgress I can also
1:50:50see it down here um so I'm going to go
1:50:52ahead and just press that right here to
1:50:54select
1:50:55it and now I can verify it's connected
1:50:58by this down here at the bottom also of
1:50:59the SQL courses all right so let's run
1:51:01this query to create that table we can
1:51:03either do this of run on a connections
1:51:06selecting that or you can highlight it
1:51:09all rightclick it do run select a query
1:51:12or finally my favorite just pressing
1:51:14command D and then command a and then
1:51:17anytime you run a query it's going to
1:51:19pop up on the right hand side right here
1:51:20promise you you'll have stuff here when
1:51:22you actually start querying for data
1:51:24back right now we don't so we're just
1:51:25going to close this out so right now
1:51:27there's still nothing here inside these
1:51:29tables what you need to do is come to
1:51:31this icon right here and you need to
1:51:33refresh them and now magically what
1:51:36appeared was the job applied table and I
1:51:38can navigate into it and I can see all
1:51:41those different values that we created
1:51:42right here were the same ones from this
1:51:45create table so if I wanted to I could
1:51:47just do a select all from java applied
1:51:49select it all command e command e run it
1:51:53and whenever we get a return back it
1:51:54says that there's no data available now
1:51:56that we have this empty table we need to
1:51:58actually insert into it the data we need
1:52:01and we do this with the statement of
1:52:03insert into and then from there include
1:52:06the table name after this we have a
1:52:09parentheses and we enclose all the
1:52:11different column names that we want to
1:52:13include for inserting the data now if
1:52:15we're using every single column name
1:52:16inside this table it's not necessary to
1:52:19include this it just makes the
1:52:20assumption that you're using every
1:52:21single one however you have to make sure
1:52:24that whenever you get to this values
1:52:25portion that where you're going to be
1:52:27specifying the different values put in
1:52:29that you do have the same order as the
1:52:30table or you're going R into issues so
1:52:33just for Best Practices I like to
1:52:35include both the column names and then
1:52:37no matter what whether it's all values
1:52:39or not and then the values themselves so
1:52:42in vs code I'm going to put in that
1:52:44insert into along with that table name I
1:52:46want to insert into now I'm going to
1:52:47specify all the different column names
1:52:49that i' specified previously next I'm
1:52:51going to specify value and then start a
1:52:53parentheses to put all those different
1:52:54values in now here's one entry notice
1:52:57it's enclosed inside a parentheses and
1:53:00then we have a one with a comma and then
1:53:01the next value just's date custom resume
1:53:04resume file name cover letter sent and
1:53:07cover letter file name and then finally
1:53:09status now if I wanted to do more than
1:53:11one entry I'm going to just put a comma
1:53:13and then insert more in so now in this
1:53:15case I have two three four five
1:53:18different entries put into this all
1:53:20right so let's now actually insert this
1:53:21into our table and I'm going to go ahead
1:53:24and just select it all and then from
1:53:26there command e and then command e again
1:53:30and boom get another popup not
1:53:32necessarily anything there but let's
1:53:34actually verify we inserted into this
1:53:36table so I have this select star from
1:53:38jaob applied to basically query it all
1:53:40make sure I did this pressing command e
1:53:43command e it's popping up here I'm
1:53:45actually going to move it over so we can
1:53:46see it fully on this screen so I have
1:53:49the jav ID the application date resume
1:53:52resume file name cover letter cover L
1:53:55file if we used it or not and then the
1:53:58status all right so sweet we now have
1:54:00created a table from scratch and
1:54:02inserted data into it let's not get into
1:54:04altering a table we're going to be doing
1:54:05this on this jav appli table the
1:54:07statement we're going to use for this is
1:54:09Alter table and then the table name then
1:54:11depending on what you want to do you're
1:54:13going to put one of these four different
1:54:15options that we're going to select from
1:54:16we can either add a column rename a
1:54:18column alter a column or basically
1:54:21change its data type and then drop a
1:54:23column all right so we're going to start
1:54:24off with that keyword of alter table and
1:54:26then specify job applied and we're going
1:54:29to be creating a table of contact names
1:54:33for whatever company we're reaching out
1:54:34to so I have add contact and then
1:54:37specify that it's going to be a data
1:54:39type of far car and allowing only 50
1:54:42characters just to verify opening SQL
1:54:44tools opening Java applied we can see
1:54:45that there's nothing there currently for
1:54:47this all right so I almost select it all
1:54:50command e command e and whenever we go
1:54:54back to it we should see after
1:54:56refreshing it now we have this contact
1:54:59appear but now you may be like Luke we
1:55:01quer that table there's going to be
1:55:02nothing in it and you be right so let's
1:55:05actually look into it command D command
1:55:07D moving it over so we can see it fully
1:55:09so we have all this information and then
1:55:11for the contact name it's all null now
1:55:14this is a bonus command we get to cover
1:55:17and that's update update allows us to
1:55:20modify existing data within a table for
1:55:23this we use the keyword of update
1:55:25specifying the name set the column name
1:55:28that we want to a certain value where we
1:55:31meet a condition and usually this
1:55:33condition is you specify a value within
1:55:36one of the rows so we'll start with that
1:55:38update job applied next we'll do set and
1:55:41then we're going to be doing this to
1:55:43that contact column name and adding erck
1:55:45Bachman and then finally where we're
1:55:48going to be just using that index column
1:55:49of jaob ID so we want to do this for the
1:55:52Java ID of one now we have multiple rows
1:55:54to fill in so we're going to do multiple
1:55:57statements in this case so I'm going to
1:55:59select this all command e and then
1:56:01command e again and it's going to go in
1:56:03and update it going to close out of this
1:56:06and now using that select star from java
1:56:08applied running this as
1:56:10well I can see that inside of the
1:56:13contact it actually inserted all those
1:56:15different contact names into it so now
1:56:18that we put these names into this column
1:56:20called Contact come to the realization
1:56:22that this name is not necessarily
1:56:24appropriate instead I want to rename it
1:56:26from contact to contact name this is
1:56:29where the rename column statement comes
1:56:31in after specifying alter table we're
1:56:33going to go in and specify the original
1:56:35column name to the new one so I'll start
1:56:37with alter table job applied and then
1:56:39rename column contact to contact name
1:56:43and then quering this table to actually
1:56:45inspect the contents of
1:56:47it we can see now that contact was
1:56:49changed to contact name now sometimes
1:56:52times now that I think of it names can
1:56:54be quite long and in this case we set
1:56:56that contact name actually Let me
1:56:58refresh this right here this contact
1:57:00name to 50 characters in length well I
1:57:04want to change this data type now from
1:57:05varar to text where text doesn't have a
1:57:09character limit that needs to be
1:57:11specified for it you can put any amount
1:57:12of characters into it so I use this
1:57:14alter column statement specifying the
1:57:15column name and then type to then
1:57:17specify the new data type so once again
1:57:19start with that alter table and then
1:57:21specify the name and then alter column
1:57:23column name and finally specifying the
1:57:25type as text so running this command
1:57:28pressing command D command D we have a
1:57:30success message and I find the easiest
1:57:32way just to check this data type is come
1:57:34back into here and we'll refresh this
1:57:36and now we see that it is text now I do
1:57:39want to call out there are certain
1:57:41limitations in changing this data so in
1:57:44this contact name example it has string
1:57:47characters in it if I wanted to actually
1:57:49change this data type to int and then
1:57:51try and to actually execute it by
1:57:53pressing command e command e I'm going
1:57:55to get this column contact name cannot
1:57:58be cast automatically to type integer
1:58:00because it has a string in there it's
1:58:01not going to be able to make any of
1:58:02those that are letters into an integer
1:58:04so it's not going to be able to do this
1:58:06so really it's important that you get
1:58:07this correct on the first time whenever
1:58:09building your table for the first time
1:58:11and now let's say this column of contact
1:58:13names we decided we're not going to go
1:58:15ahead forward with it because instead
1:58:17we're going to be using LinkedIn profile
1:58:19information to gather this information
1:58:21and we're going to be creating separate
1:58:22table for this so we don't need this
1:58:24contact name column anymore in this case
1:58:26we can use drop column which is the
1:58:28simplest and also probably the most
1:58:30dangerous in that you just specify the
1:58:31column name and remove it so opening up
1:58:34SQL tools I can see that refreshing it
1:58:36that we have that contact name right now
1:58:38is a text so entering in that keyword of
1:58:40drop column and then specifying that
1:58:42contact name I can then see how we can
1:58:45remove it by running this query command
1:58:47e command e okay refreshing this we can
1:58:50see that contact name no more and it's
1:58:53gone all right last command of dropping
1:58:55the tables let's say that we're now
1:58:56tired of having to go into SQL and
1:58:58insert our records using all these
1:59:00different commands we're going to just
1:59:01use a spreadsheet software instead which
1:59:03I probably should use anyway anyway we
1:59:04can go in now and specify drop table and
1:59:07then specifying the name itself be
1:59:10extremely careful anytime you're doing
1:59:12these drop columns or drop tables as
1:59:15this is very much permanent so
1:59:17specifying drop table and then job
1:59:20applied let's go ahead and Drop It Like
1:59:23It's Hot bam we ran this refreshing this
1:59:26query right now where job is applied is
1:59:28right here it's still there got to wait
1:59:30a second okay it took about a minute for
1:59:33me on my computer but refreshing this
1:59:35now we can see that nothing is here
1:59:37sometimes especially if you're going to
1:59:38be doing large changes to databases or
1:59:41dropping them or removing them there's a
1:59:43lot of code that goes on in the
1:59:44background besides these three simple
1:59:47keywords right here that we're able to
1:59:48do this with so you got to wait a little
1:59:50bit of time all right now it's your turn
1:59:51get give it a try feel free to walk
1:59:53through this example that I just took
1:59:55you through making up any relative
1:59:56values that you would rather use instead
1:59:59all right with that see you in the next
2:00:01one all right let's now get into loading
2:00:04the database that we're going to be
2:00:05using for this continuation of the
2:00:08advanced section and we'll be also be
2:00:09using it as well in the portfolio
2:00:12project section this data is going to be
2:00:15of the same schema using the same tables
2:00:17and column names that we were using
2:00:19previously in SQL light F however this
2:00:22data set is a lot more robust and
2:00:24includes a lot more details from 2023
2:00:28and for those that are maybe a little
2:00:29lazy and want to continue just stay
2:00:31inside of sqlite viz and enter queries
2:00:34from time to time you can continue to do
2:00:36so but I can't guarantee you that any of
2:00:38the queries that we go forward with will
2:00:40work inside of here I highly recommend
2:00:42you just follow along what we're doing
2:00:44so how are we're going to do this all
2:00:45well three simple steps first we're
2:00:47going to download all the data which is
2:00:50in CSV files and also SQL files from
2:00:53there we're going to move into VSS code
2:00:54and create the tables using some SQL
2:00:57files that I'm going to give you and
2:00:59finally now that we have all these empty
2:01:00tables we need to load the data all into
2:01:03it that'll be our final step so let's
2:01:04jump into it so if you navigate to the
2:01:06URL in the screen right here you're
2:01:08going to be directed to this Google
2:01:10Drive where it has a couple of contents
2:01:12inside of it first is a folder with all
2:01:14the different CSV files in it and these
2:01:17are conveniently named after the four
2:01:19different tables we're going to be
2:01:20creating inside of our database and you
2:01:23can even peek inside of there and see
2:01:25where the contents are overall it's just
2:01:27Comm of separate variables inside of it
2:01:29all right so back in the main folder the
2:01:31other main file in there is the SQL load
2:01:34folder and it has the SQL files that
2:01:36we're going to be using to not only
2:01:38create our tables but also modify our
2:01:40tables pecking into it we can see that
2:01:42this create tables one it's pretty long
2:01:45so now we're going to just download
2:01:46these now you can download these folders
2:01:49individually or I actually just zip them
2:01:52into their own little zip file to make
2:01:55this quicker for you to download so you
2:01:57can do either or the one issue with the
2:01:59zip file is you can't scan it for
2:02:01viruses so if you're not comfortable
2:02:03with it just download the other two
2:02:04files exact same thing all right so
2:02:06navigate back inside of a VSS code we're
2:02:08going to be now adding those folders
2:02:09navigate into that project folder that
2:02:11you created from previously you may have
2:02:13it open if not open a new window and it
2:02:15should pop under recent and you can just
2:02:17select it there now I'm going to go
2:02:19ahead and uh select the two files that
2:02:21we've downloaded I unzipped them and
2:02:23then I have them right here I'm going to
2:02:24take them and I'm going to actually just
2:02:25drop them right inside of here it says
2:02:28do you want to copy the folders or add
2:02:30the folders to your workspace I'm just
2:02:32going to select copy folders over and
2:02:34it's going to place them right in I did
2:02:35add folders workspace previously and it
2:02:37did something funky I want it inside my
2:02:40project itself so that's why I did the
2:02:41copy over now since we added a copy just
2:02:43going to do some cleanup you don't need
2:02:45this ZIP file or this original folder
2:02:48that I have right here I'm just going to
2:02:49select it and delete it and remove it
2:02:51from my uh remove it all right so the
2:02:53first step is done now let's actually
2:02:55get into creating these tables for the
2:02:57database so back of vs code I'm going to
2:02:59navigate into this SQL load folder and I
2:03:03have this one here already on create
2:03:05database if you haven't done this
2:03:07already you can go ahead and execute
2:03:08this in order to run and ex and create
2:03:11this database that we're going to need
2:03:12for this but let me just verify real
2:03:14quick that we have this set up so I'm
2:03:16going to click on SQL course I logged
2:03:18out recently so it just asked me to
2:03:20verify my credentials and enter in the
2:03:22password and then I see I have the SQL
2:03:24course database already so I don't need
2:03:26to do this create database now as a
2:03:28reminder again make sure that we
2:03:29maintain this SQL course connection
2:03:32established for this is where we're
2:03:33wanting to create all these tables I'm
2:03:35going go ahead and close out of these so
2:03:38navigating back into the explore back
2:03:40into the create tables in this one is
2:03:44four different SQL queries for creating
2:03:46tables we have one for creating the
2:03:48company dim one for creating the skills
2:03:50dim one for job postings fact and then
2:03:53the fourth one for skills job dim these
2:03:57four commands are nothing different than
2:03:59what we learned in the last section for
2:04:01creating table the only difference now
2:04:03is we added a keyword in here for
2:04:06specifying when we're using a primary
2:04:08key and then when we're using uh
2:04:11something like a foreign key so in this
2:04:13case right here as a quick refresher on
2:04:16primary and foreign keys so if we go
2:04:18back to that core database schema that
2:04:20we have job post postings fact so we
2:04:23have this job ID in there and that's
2:04:25unique to this column or this data set
2:04:28right here that is the primary key in
2:04:30job postings fact however in something
2:04:33like skills job dim the job ID is now
2:04:36the foreign key conversely skills dim is
2:04:39the main table when it comes to skills
2:04:42so that skill ID is unique value in
2:04:44there so that's the primary key in this
2:04:46one and in skills job dim it's the
2:04:49foreign key so back inside of our SQL
2:04:51file we can see that the job ID itself
2:04:54is that primary key in the job postings
2:04:57fact table and then in that skills job
2:05:00dim table it is a foreign key then we
2:05:03also have to specify references
2:05:06specifying what table is the primary key
2:05:08in along with that value we expected to
2:05:11be for that primary key conversely that
2:05:13skills dim table has that skill ID as
2:05:16the primary key and then the skills job
2:05:18dim has that skill ID Associated as a
2:05:21boring key and we also have that company
2:05:23dim table which has that primary key and
2:05:26then Associates it into that job posting
2:05:28fact table as a foreign key right
2:05:31there's two major last statements I want
2:05:32to go over first is the owner we go
2:05:35through and actually establish the owner
2:05:37as postgress for this because we've
2:05:40already set up our connections already
2:05:41within vs code using postgress we want
2:05:43to make sure it uses this and then
2:05:45finally in order to speed up performance
2:05:47we have this create index and we're not
2:05:50going to be using this at all don't
2:05:52worry about it it just speeds up the
2:05:53queries a little bit more and it
2:05:55basically specifies the different
2:05:57foreign keys and helps with actually
2:06:00aggregating or creating as an index to
2:06:01speed up the query performance so now
2:06:03that everybody's all comfortable and
2:06:05knows each other let's actually get into
2:06:06executing this query I'm going to go
2:06:08ahead and select it all and then from
2:06:11there verify that SQL courses is in fact
2:06:14the connection that I'm connected to and
2:06:16then from there press command D command
2:06:19D now this is going to take some time
2:06:20like previously in order for these
2:06:22tables to show up navigating over to SQL
2:06:25courses schemas public and then tables
2:06:27oh actually looks like they popped in
2:06:30already and just do an inspection
2:06:32everything everything's looking good
2:06:34conveniently we can also see based on
2:06:37this that like job ID has that primary
2:06:40key which is has this gold icon for a
2:06:42key and then Company ID has this foreign
2:06:45key of this silver one all right we're
2:06:47almost done we've now downloaded all the
2:06:49files created tables now we need to load
2:06:51this down add it in to the tables that
2:06:53we just created so navigate back into
2:06:55our files looking at the explore menu
2:06:57we're going to go into this one now of
2:06:59three of modifying tables this is going
2:07:02to be a new SQL command that we haven't
2:07:05seen before and it consists of three
2:07:07things in order to copy the contents
2:07:10into a certain table based on a certain
2:07:12file so this starts first with the
2:07:14keyword copy and then we're specifying
2:07:16the table we want to copy data into next
2:07:20is from we're going to then specify the
2:07:23file path location which you're going to
2:07:25have to update all these for your file
2:07:27path location and I will too actually
2:07:29and this is going to specify where the
2:07:31CSV is living and then finally we need
2:07:34to specify that we're copying into here
2:07:37this CSV file and I'm going to specify
2:07:40this with delimiter and then specifying
2:07:42what it's using to separate all the
2:07:44different variables in there which is a
2:07:46comma and then the keyword CSV header
2:07:49header is going to specify there is a
2:07:51header or basically column names at the
2:07:53top row of this data set okay so we need
2:07:56to update the file location for all four
2:07:59of these so I'm going to navigate back
2:08:01into the explore into the CSV files
2:08:05themsel we're going to start with that
2:08:06company DM what you can do is you can
2:08:08rightclick this and then select copy
2:08:12path now inside of these parentheses
2:08:15here I'm going to go ahead and just
2:08:18paste this into here and then I'm going
2:08:20to do this for all the remaining being
2:08:23very careful that we're using whatever
2:08:25the name of the CSV file is for the file
2:08:28path
2:08:33itself all right so I've gone through
2:08:36and actually updated them all to make
2:08:38sure that they match I'm going to go
2:08:39ahead and actually save it by pressing
2:08:41command s all right so now it's ready
2:08:43last chance to verify that you have all
2:08:45those correct CSV files underneath the
2:08:48correct table names select it all
2:08:51command e command
2:08:54D and this is going to take a little bit
2:08:56of time to load all this data into it
2:09:00all right so that was about a minute for
2:09:02me for this to all load I'm going to go
2:09:04ahead once again anytime I do any of
2:09:06these there's nothing really appearing
2:09:07here so I want to verify that I got this
2:09:09data into it I inserted it in correctly
2:09:11I'm going to just create this simple
2:09:13statement real quick of selecting all
2:09:15columns from job posting fact a lot of
2:09:18data in there I don't want to query
2:09:19everything just yet so so I'm just going
2:09:21to limit this to 100 values selecting it
2:09:24all pressing command D command D moving
2:09:27this window over here it looks like all
2:09:30of the data loaded into it so now we're
2:09:33cooking feel free to also go through and
2:09:36check any other tables as well verifying
2:09:38that they all loaded but based on this
2:09:40other one I'm pretty confident that all
2:09:42the other ones are loaded just fine as
2:09:45well all right we did it just created
2:09:47our entire database from scratch using
2:09:50CSV files but then loaded in using some
2:09:52SQL queries and it's all lited up all
2:09:54right next we're going to be jumping
2:09:55into some date functions and uh some
2:09:58more advanced features so with that see
2:10:00you in the next
2:10:05one all right in this section we're
2:10:07going to be going over dates and also
2:10:09times this is a very critical component
2:10:12as an analyst be able to understand how
2:10:14to manipulate dates and times because on
2:10:16depending where in the world you're
2:10:18working on data from you may have to
2:10:20convert it now for for this section
2:10:21we're going to be working inside that
2:10:23table job postings fact and specifically
2:10:26we're going to be working with a column
2:10:28of job posted date this not only has a
2:10:31date but it also has a Time associated
2:10:33with it so it's a timestamp value and
2:10:36this value in each one of these rows it
2:10:39correlates to the date and time that a
2:10:41job was posted so for this we're going
2:10:43to be focusing on three main keywords or
2:10:45operators in order to handle dates the
2:10:48first is how to cast timestamps as a
2:10:50date next is how can we work with time
2:10:53zones and convert to different time
2:10:54zones and then finally we're going to
2:10:56work with my favorite extract being able
2:10:58to pull out things like year month out
2:11:00of a date all right the first thing
2:11:02we're going to look at is how to cast
2:11:04different values or types to a different
2:11:06data type so in the case of our posted
2:11:10date this is a timestamp and we want to
2:11:14if we wanted to cast it as a date we
2:11:17would use this double colon which allows
2:11:19us to specify the data type we want to
2:11:22cast something to this is typically used
2:11:25within the select statement in order to
2:11:27assign it to maybe a column name and we
2:11:30haven't seen this before which you can
2:11:31actually run queries without actually
2:11:33using a from statement to select a
2:11:35database so in this case I can say just
2:11:36select a string value in this case I'm
2:11:39selecting the string value of a date and
2:11:41I'm going to press command e command e
2:11:43to run it so right now it provided this
2:11:46query of basically the string inside of
2:11:48it but instead I can actually cast it by
2:11:51using this double colon and then from
2:11:54there specifying the data type date from
2:11:57here I'm going to just go ahead and
2:11:59command e command e and run it and now
2:12:02it has this as a date now to show this
2:12:05for values we can actually see inside of
2:12:08here that we've actually converted to a
2:12:09different data type I did some other
2:12:11examples converting this one two three
2:12:13string to an integer true as a string to
2:12:16booing and then 3.14 string to real in
2:12:20this case we can actually see the
2:12:21conversion the iner it has this gray box
2:12:23around it Boolean it actually has the
2:12:26green around it to signify that it's a
2:12:28true value and then same with the float
2:12:30the gray around it so let's actually see
2:12:32this in action we're going to get back
2:12:33into actually running queries on our
2:12:35database and remember be sure that you
2:12:37have it set up through SQL tools that
2:12:39you're connected to that SQL course that
2:12:41it's actually active and you have SQL
2:12:43courses as the connection down here at
2:12:45the bottom so I'm going to go ahead and
2:12:46run this query and for it we can see
2:12:50that we have the title the location and
2:12:52then the date and with this date we also
2:12:54have this time stamp associated with it
2:12:57because it is in fact a time stamp value
2:12:59let's say we only needed that date value
2:13:03from this column and we don't really
2:13:05care about that time stamp well I can
2:13:07convert this data type of timestamp to
2:13:11date and we do this by specifying that
2:13:13double call-in and then the data type of
2:13:15date running this all together pressing
2:13:18command D command D we can now see that
2:13:20we have the title location and then date
2:13:22it automatically cleaned it up for us
2:13:24and removed that time all right next up
2:13:27is at time zone a keyword in order to
2:13:30convert timestamps to as you guessed it
2:13:33different time zones now it can be used
2:13:35with timestamp data whether it has a
2:13:38time zone specified or not as we refresh
2:13:41it from our data type section timestamp
2:13:43alone includes things like the date and
2:13:45time whereas time stamp with time zone
2:13:48includes all that and then includes
2:13:50either a plus or minus to adjust the
2:13:53time zone based on where it is now if we
2:13:56go back in to see the data that we
2:13:58imported by specifically going to that
2:14:00job posting fact table CSV that was
2:14:02imported into our table and then scroll
2:14:05over to see the job posted date column I
2:14:09have this convenient coloring scheme so
2:14:11you can actually see it but anyway
2:14:13here's one of the dates right here right
2:14:14it's just a uh where the time stamps
2:14:17it's a date and then a time there is no
2:14:20time stamp on the end into this so the
2:14:22data in our database does not include
2:14:24time zone information so here I have
2:14:26that similar query from before where
2:14:28we're looking at title location but also
2:14:30that date time we're going to go ahead
2:14:32and actually investigate what the column
2:14:33looks like and similar to before that
2:14:36date time includes things like date time
2:14:38and we want to pay attention to these
2:14:41top values right here these aren't going
2:14:43to change from query to query on the top
2:14:4610 values actually I'll just leave it to
2:14:48five we're going to leave these up here
2:14:50and actually pay particular attention to
2:14:52these as we go through this example to
2:14:54understand this more and still the same
2:14:56ones up here but remember that 1746 now
2:14:59if our data came as a time stamp with
2:15:01time zone whenever we use this at time
2:15:04zone keyword we'd only have to specify
2:15:07at once so you'd specify the column name
2:15:09add time zone and then from there
2:15:11specify the time zone you want to go to
2:15:13in this case we're showing Eastern
2:15:15Standard time now in our situation is
2:15:18different so it makes it a little bit
2:15:19more complicated because we don't have
2:15:21time zone information we need to First
2:15:24specify the time zone that this value
2:15:26actually is by saying at time zone and
2:15:29then from there use at time zone again
2:15:32to specify the time zone we want to go
2:15:34to in this example we're showing from
2:15:36UTC converting it to Eastern Standard
2:15:39Time so going back to that previous
2:15:41query that date time these values here I
2:15:44know from actually collecting the data
2:15:47this value is UTC so I'm going to first
2:15:51start by specifying at time zone and
2:15:54then specifying UTC and then we're going
2:15:56to do it again to go to the time we want
2:15:59to go to let's go to Eastern Standard
2:16:01Time so now I have at time zone EST we
2:16:05can see it fully here now Eastern
2:16:08Standard Time is 5 hours prior to UTC so
2:16:12whenever I run this I should expect it
2:16:14to adjust this appropriately and Bam we
2:16:17went from that 1746 to 1246 so 5 hours
2:16:22prior now if you Google the postrest
2:16:24documentation on different time zones
2:16:27you can see that they have a whole host
2:16:29of time zone basically every single time
2:16:31zone available for you to use here in
2:16:34our case we were converting that UTC
2:16:36which is at 0 to that Eastern Standard
2:16:40Time which is that -5 the last keyboard
2:16:43we're going to be looking at is extract
2:16:45and this is used to extract things out
2:16:48of the date such as the year month or
2:16:50even day this is used like a function
2:16:53within the select statement so I would
2:16:55do something like select and from there
2:16:57specify extract function and then inside
2:17:00of the parentheses specify what I want
2:17:03to get from the column of interest in
2:17:06this case I want to get the month from
2:17:08the column name and then I'm just
2:17:10renaming it as column month so going
2:17:12back to that previous query we have the
2:17:13title location and then also date time
2:17:15let say we want to extract the month out
2:17:18of that job posting date I would first
2:17:20start with that extract function from
2:17:22there i' would specify what the value I
2:17:24want from it is then specify the keyword
2:17:28from and then finally the actual column
2:17:31of interest and then we'll name this one
2:17:34date month selecting it all and then
2:17:37running it we can see we have from this
2:17:40the month values from that date time so
2:17:43977 43 I could even take it a step
2:17:46further and add something like the year
2:17:48to this and from here command e command
2:17:51D we can see from this we got the year
2:17:53into this column now you may be like
2:17:55Luke what is this actually useful for
2:17:57well when we actually use this something
2:18:00like this in combination with something
2:18:02like the group by function I could do
2:18:05larger Trend analysis with SQL
2:18:08specifically let's say we want to look
2:18:10at how job postings are trending from
2:18:13month to month so let's start with a
2:18:15simple query and build on it further I
2:18:17want to first start by just getting
2:18:19things like the job ID and then the
2:18:21month from each of the job posted date
2:18:24columns running this query to double
2:18:26check that it's working I can see that
2:18:28I'm getting it right here now I want to
2:18:31aggregate it so I want to do a count of
2:18:34these different job IDs for each month
2:18:39so I'll start by putting a count around
2:18:42job ID and then from there add a group
2:18:45bu to then specify we're going to group
2:18:48bu this new month column running this
2:18:51all command D command D we're getting
2:18:53these first five values now we're down
2:18:55to 12 so I'm just going to go remove
2:18:57ahead this uh limit column and then run
2:18:59this again to actually see all of them
2:19:01so bam it's shown us all the different
2:19:03months and then all the different values
2:19:05now personally I only really care about
2:19:06data analyst roles so I'm just going to
2:19:08take it even a step further and then use
2:19:10a wear
2:19:13Clause specifying the job title short of
2:19:16data analyst and then just for a little
2:19:18cherry on top we're going to do an order
2:19:21bu and then in this case we want to
2:19:23obviously do the count so just not to be
2:19:26repetitive I'm going to rename this job
2:19:29posted count and then specify this down
2:19:32here of job posted count okay running
2:19:36this all command e command D we can see
2:19:39that this is ordered from low to high
2:19:41and I obviously don't like that so we're
2:19:43going to just change this to descending
2:19:45and then rerun this query again command
2:19:47e command D and then from this we can
2:19:49see that there looks like there's a
2:19:52trend that earlier in the year so
2:19:54January feary March have higher job
2:19:56posting counts specifically with January
2:19:58having some of the highest and then
2:20:00later in the year like December November
2:20:03and September are lower on the list and
2:20:06this pretty much tracks with what I
2:20:08would expect all right now it's your
2:20:09turn to give it a try I have a few
2:20:11practice problems aligned with not only
2:20:14using the different datetime functions
2:20:15that we just went over but also
2:20:17aggregating it with some previous
2:20:19functions that we used in order to look
2:20:21at things like salary and counts of jobs
2:20:24all right with that see you in the next
2:20:30one all right in this section we're
2:20:32going to get into a practice problem
2:20:34using what we just learned on the dates
2:20:36and also we learned previously on create
2:20:39tables specifically we want to create
2:20:42tables for each month of these job
2:20:46postings so I want all of the data for
2:20:48say January in its own individual table
2:20:52a little bit of foreshadowing here we're
2:20:53going to be using all these different
2:20:56months in upcoming practice problems
2:20:58when we go over even more advanced
2:21:01operations so anytime I'm doing
2:21:02something like this I want to just start
2:21:04the very Basics so let's start very
2:21:06basic first with the query to actually
2:21:08connect to the database so we'll start
2:21:10with this of Select star from job
2:21:12posting fact and then I want to speed up
2:21:15these queries for the time being so I'm
2:21:16going to just put a limit statement on
2:21:17the time being to speed it up all right
2:21:21so we have all our information so the
2:21:22next thing we want to focus on is this
2:21:24job posted date remember we want to make
2:21:27tables for every single month or at
2:21:30least the first three months January
2:21:31febrary March so let's start by
2:21:34filtering this job posted date column
2:21:37and only get values that have January in
2:21:40it so in this case I can use that wear
2:21:43statement and then we're going to
2:21:45specify extract and we're going to use
2:21:47the function we want to remember we want
2:21:49to specify that we're gonna be using
2:21:50month from this job posted date now we
2:21:55need to specify what this condition
2:21:57actually meets of extract month from job
2:22:00date in our case when it equals one so
2:22:03I'm going go ahead and select this all
2:22:05here and press command e command e again
2:22:09and scrolling over to that job posted
2:22:12date column I can now see that we have
2:22:15nothing but January dates in this column
2:22:18okay we go ahead and close this x I
2:22:20don't need need this anymore now we're
2:22:21trying to create a table for this job
2:22:24post to date of only January and
2:22:28remember if we go back to our SQL tools
2:22:30extension on the left hand side and
2:22:32actually go into our tables folder we
2:22:35can see we have these four folders right
2:22:36now so we want to create a table an
2:22:38additional one inside of here so first
2:22:40I'm going to remove this limit statement
2:22:42and once again make sure that it runs
2:22:45it's going to take a little bit longer
2:22:46to run and Bam we looks like we have
2:22:49around 92 th000 values the next thing I
2:22:52want to do is now use a statement of
2:22:57create table we're going to specify the
2:23:00name of this table so in their case
2:23:02January jobs and then we'll use the as
2:23:06alas to assign this and just to format
2:23:09this better to make it look more
2:23:11appropriate I'm going tab this over bam
2:23:13so that's how we'd want to do this and
2:23:15I'll put a little uh semicolon at the
2:23:17end of this so we have this for January
2:23:19we also need this for February and March
2:23:22I'm going to show you a little trick
2:23:23that I do this so I'm going to copy all
2:23:25this by pressing command C and then if
2:23:27you go into any AI assistant in this
2:23:29case we're using chat GPT specifically
2:23:31the chatbot for this course I can then
2:23:33specify in there to make this for other
2:23:35months so I specify make this query for
2:23:38all months in the year and then from
2:23:40there paste in that SQL query let's see
2:23:43what it does boom okay so in this case
2:23:46Chad gbt went through and did this all
2:23:49anytime you find very rep competive work
2:23:50you need to jump into chat chv to do
2:23:52this now remember we only need these
2:23:54three months right here and I have gone
2:23:56through and verified that it does in
2:23:58fact use the right syntax chat BT coming
2:24:00wrong sometimes so make sure you're
2:24:01always double checking it anyway I'm
2:24:03going to copy it by pressing command C
2:24:05and then going back in here and pressing
2:24:07command V I also went in and added some
2:24:09indentation all right let's create all
2:24:11these different Tables by pressing
2:24:13command e command e okay so it looks
2:24:16like once again we got this blank screen
2:24:17that the tables were made I can come
2:24:20over here and I'm I'm going to press
2:24:22refresh inside of SQL tools and Bam
2:24:25there it is January February and March
2:24:27jobs are now inside of here so we
2:24:29created tables using the extract
2:24:32function now I always like to double
2:24:34check my work so I'm going to run this
2:24:35select statement right here where we
2:24:37select job posted date from March jobs
2:24:41running this pressing command e command
2:24:43D we can see that all these different
2:24:45jobs in here looks like we're around
2:24:4764,000 different values and they're all
2:24:49for Mar so I'm pretty confident that all
2:24:52the other values should be correct all
2:24:54right it's your turn to give it a try we
2:24:55need to get these tables built inside of
2:24:57your database cuz like I said we will be
2:24:58using this in some future examples with
2:25:01that see you in the next
2:25:06one all right in this section we're
2:25:08going over case expressions and this is
2:25:10very common where if we want to create a
2:25:12column based on a condition we can do
2:25:15this through this case now if you have
2:25:17any experience with something like
2:25:19spreadsheets or even python on this is
2:25:21very similar to an if statement where an
2:25:23if a statement usually have some sort of
2:25:26condition whether you're trying to test
2:25:27whether it meets it true or false and
2:25:29then from there it assigns it a value
2:25:32whether it is in fact true or false so
2:25:34let's go over the basic syntax on how to
2:25:37use this and commonly it's used within a
2:25:40select statement and that's we're going
2:25:41to show here but you can use it in a
2:25:43whole host of other things such as where
2:25:44or even Group by to list an expression
2:25:47to satisfy by case we start out with
2:25:49case and then we end it with end and at
2:25:52the end you can also use an alias so we
2:25:54can have in this case as column
2:25:56description so that's we're going to
2:25:57name that column as whatever we build
2:25:59with this now this is going to go in
2:26:01logical order so for the first one of
2:26:03when column name equals value one we're
2:26:05going to check whether it does meet that
2:26:07and if it does then we'll provide it
2:26:10this value which will go inside that
2:26:11column description column from there
2:26:14we'll move to that next line of when
2:26:16column name equals value two and then
2:26:18description for column 2 and if it
2:26:19doesn't meet any of those two conditions
2:26:22it will meet the else condition of then
2:26:24other and that will be what it will be
2:26:27assigned so let's start simple with this
2:26:29query where we're just going to look at
2:26:30the job title short column and also the
2:26:33job location column so let's look at a
2:26:35condition where we would maybe want to
2:26:38reclassify where a job is located at and
2:26:42for this we need to look at the job
2:26:43location column so I'm going to go ahead
2:26:45and run this query by pressing command D
2:26:47command D and inside of this job
2:26:49location column we can see we have
2:26:51things like either like a state city or
2:26:54even a country City and then we even
2:26:56have things for like remote jobs where
2:26:59we specify that the job location is
2:27:01anywhere so for the scenario I have
2:27:04three different conditions I want to
2:27:06look at I want to create a new column
2:27:09and let's say I'm job searching I'm
2:27:11located in New York and I want to label
2:27:14things like anywhere jobs as remote if
2:27:17they are in New York I want to label
2:27:19them as as local and then otherwise I
2:27:22just want to label it as onsite that you
2:27:24have to be in that location so I'm just
2:27:26going to disregard them so with any case
2:27:28statement I'm going to start with that
2:27:29case and then I'm also have the end and
2:27:32the Alias we're going to use for this
2:27:34one is location category now let's go
2:27:37for that first statement of for anywhere
2:27:40we want to label it as remote so when
2:27:43job location is equal to anywhere I'm
2:27:48going to go ahead and move this over a
2:27:49little bit get this out of the way then
2:27:51we'll label it as remote next when job
2:27:55location equals New York New York then I
2:27:58want it to label it as local and then
2:28:00finally with everything else because
2:28:02this goes in logical order and satisfies
2:28:04whether it meets it I'm going to then
2:28:06specify an else statement of onsite all
2:28:09right let's go ahead and run this all
2:28:11pressing command e command e okay I got
2:28:13an error right here and that's because
2:28:15we don't have a comma after job location
2:28:18because it's starting that new column
2:28:20so make sure you have that in there all
2:28:22right command D command D expand this
2:28:24out a little bit all right now we can
2:28:26see the anywhere rows are now labeled as
2:28:29remote the New York is located as local
2:28:32and then everything else is on site so
2:28:35this is great that know we have this
2:28:36done but from an analysis standpoint I
2:28:38want to dive into this further I want to
2:28:41analyze how many jobs I have I can apply
2:28:44to specifically the local ones and the
2:28:46remote ones also look at the onsite as
2:28:48well so we can use something like the
2:28:50group by function in order to aggregate
2:28:53all these different values so I'm going
2:28:55to start by how we're going to aggregate
2:28:56this and that's going to be using a
2:28:58count and I'm going to use that job ID
2:29:00column making sure to include a comma
2:29:03also with this count job ID I'm just
2:29:04going to label this appropriate as
2:29:06number of jobs so let's first start by
2:29:08just starting simple of doing a group ey
2:29:10and we're going to be doing this using
2:29:12that location category let's actually
2:29:15run this query to see what we have so
2:29:17far command D command D all right a lot
2:29:19more onsite verse remote verse local not
2:29:23bad for local 8,000 jobs in New York
2:29:25anyway if you recall I specifically care
2:29:28about the data analyst jobs so we'll
2:29:31specify a filter for where and we'll TI
2:29:34Go with the job title short equal to
2:29:38data
2:29:40analysis running this again seeing how
2:29:43little we declined went from 8,000 jobs
2:29:46to 3,000 jobs local and down to 13,000
2:29:50what was it previously 69,000 okay now I
2:29:53normally put an order Buy in here but it
2:29:55looks like for some reason it
2:29:56automatically sorted it from highest to
2:29:58lowest so I'll consider this good enough
2:30:00for now anyway we wanted to I could Dow
2:30:02dive deeper into these 3,000 local jobs
2:30:06that I could then apply to all right
2:30:08with that it's your turn to give it a
2:30:09try with case expressions for those that
2:30:11bought the course certificate and notes
2:30:13you now have a practice problem
2:30:15specifically that goes into bucketing
2:30:17different jobs specifically around
2:30:20salaries so assigning different values
2:30:22for each all right with that see you in
2:30:24the next
2:30:28one all right in this section we're
2:30:30going to be going over both subqueries
2:30:32and CTE and the concept behind both of
2:30:36these are that we're going to create
2:30:38basically temporary tables inside of our
2:30:41SQL query that we're performing and then
2:30:43perform and Analysis on this temporary
2:30:46table this is very useful whenever we're
2:30:48getting into more and more complex
2:30:50queries that we need to be able to do as
2:30:52we want to break it up into sections and
2:30:55subqueries and CTE allow us to do this
2:30:58now in order to show the power of
2:30:59subqueries and CTE if you recall back
2:31:03from one of our previous practice
2:31:05problems of creating tables for each of
2:31:07the different months we use this
2:31:10statement of create table and then
2:31:11underneath it for it we specify that we
2:31:14wanted to only extract out those values
2:31:17where the job posting date fell in
2:31:19January in this case for the January
2:31:20jobs and then from there it created a
2:31:22table inside of our database this table
2:31:25is sort of like permanent it's there now
2:31:27the only way you get rid of it as we
2:31:28drop it let's tackle subqueries first
2:31:30because they're used for simpler queries
2:31:33let's say in this case we wanted to
2:31:36create a temporary table of January jobs
2:31:39well a subquery is a query as you
2:31:42guessed it inside of another query so in
2:31:44this case we have a select star
2:31:46statement and then from and then Within
2:31:49paren es we're specifying that subquery
2:31:53in our case we want to select only the
2:31:55jobs where the job posting month is
2:31:58January and then we rename this table as
2:32:02January jobs navigating back over to VSS
2:32:05code to actually see this in operation I
2:32:07can select this all press command e
2:32:09command e and when we we scroll over to
2:32:13that job posted date we can see that
2:32:16this is all the January so we did that
2:32:18select star on our sub query now the
2:32:20other popular way to create a temporary
2:32:22table are common table expressions or
2:32:25spoken as CTE and they can be used in
2:32:29even more locations such as select
2:32:31insert update or even delete with this
2:32:34one CTE are defined first using the with
2:32:38statement and then you're saying with
2:32:41this new table name and then the Alias
2:32:43as and then from there within a
2:32:45parentheses we're then specifying the
2:32:48entire query that we want to run to put
2:32:51in this new table called January jobs
2:32:54from there we can then run the next
2:32:56query of Select star from January jobs
2:33:00all right to show this actually in vs
2:33:01code let's actually run this one so
2:33:03command e command D and once again
2:33:06rolling over to that job posting date to
2:33:08make sure that it did it correctly and
2:33:10yeah we can see all of them are from
2:33:11January for this so let's jump into some
2:33:14harder practice problems for each of
2:33:16these first one we're going to focus on
2:33:18is subquery remember that's a query with
2:33:21inside another query and we can use it
2:33:23in things like select from where or
2:33:25having we're going to be doing an
2:33:26example where using it inside of the
2:33:28wear Clause because it's in this
2:33:30parentheses think of order of operations
2:33:33the inside the parentheses will be
2:33:34executed first and then everything
2:33:36around it will be operated second so
2:33:38let's say I wanted to get a list of
2:33:41companies that are offering jobs that
2:33:43don't have any requirements for a degree
2:33:46currently in our column of job no
2:33:49mentioned we have a true or false value
2:33:52and this says whether a degree is going
2:33:54to be required or mentioned in the job
2:33:57posting and it's located inside the job
2:33:59posting fact table so let's actually go
2:34:01ahead and just look at it real quick so
2:34:02right now I'm pulling company IDs and
2:34:04then the job no degree mentioned and for
2:34:09this all these are going to be true now
2:34:11if we remember back from our diagram of
2:34:14the table going on here we have that job
2:34:16postings fact table itself that has
2:34:19whether a degree is mentioned or not and
2:34:20we have that Company ID however we don't
2:34:22have the company name in this that's in
2:34:24a separate table so that's why actually
2:34:26in this case the subquery is going to be
2:34:28so powerful because we're can to run a
2:34:31subquery to get the jobs that have the
2:34:34associated Company ID for no degree
2:34:35mention and then from there filter
2:34:37inside of the company dim table so let's
2:34:40make this into a subquery and we're
2:34:42going to start with very simple first
2:34:44we're going to add a select statement
2:34:46and then we'll say company name which is
2:34:49the column name that we want to look at
2:34:51naming it as name for the table we're
2:34:53going to be selecting that company name
2:34:55from we want to use that company dim and
2:34:58now this is we're going to enter in that
2:35:00subquery we're going to specify where
2:35:02that Company ID is in this subquery
2:35:07itself now if you recall whenever we ran
2:35:10just this I'm going to just select this
2:35:11and run the query again command D we're
2:35:14getting back in this a company ID and
2:35:17then whether job note agree mention is
2:35:19true in this case realistically this
2:35:21column is not even necessary so I'm
2:35:23actually going to clean it out and we're
2:35:25going to go ahead and run this again
2:35:27command D command e and same numbers 1 n
2:35:3010 yeah this is what we saw previously
2:35:32here so I know it's pulling the right
2:35:34things so we're trying to say hey where
2:35:37the company ID is within this table so
2:35:39within this table we're going to only
2:35:41want to return those company names that
2:35:44are associated with it so let's run this
2:35:47all pressing command e command e
2:35:50uh company name does not exist oops I
2:35:53have this backwards it's actually name
2:35:56as company name my bad okay we'll run
2:36:00this again command D command D and Bam
2:36:02we can see all these different things I
2:36:04just want to show this further that we
2:36:06are actually getting the right data so
2:36:07I'm going to insert in that company ID
2:36:09and I'm going to do some clean up real
2:36:11quick anyway let's run this again
2:36:14command e command e okay we can see in
2:36:18here 1 3 4 6 7 8 9 so these are all the
2:36:20ones that are accepting it and I realize
2:36:23now whenever we look at this one we're
2:36:25not seeing those other numbers and I
2:36:26think this is just by an order by an
2:36:28issue so I'm going to fix this real
2:36:29quick by although it doesn't really
2:36:31matter it's just inside of here putting
2:36:33an order bu and then specifying Company
2:36:35ID then from there just running this
2:36:38subquery inside of here pressing command
2:36:40e command D okay we can see all the one
2:36:43the same one so 1 3 4 6 and then
2:36:48navigating over to the other one see 1 3
2:36:504 6 so this is correlating and checking
2:36:53out right and this is how you actually
2:36:54should go through troubleshooting it
2:36:56let's wrap up this section with a final
2:36:57example on using CTE or comment table
2:37:01Expressions this is used similar to a
2:37:03subquery to create a temporary result
2:37:06set which in this video previously I may
2:37:09have been referring to it as a temporary
2:37:10table that was a mistake it's a
2:37:11temporary result set they're two
2:37:13separate things anyway the results of
2:37:15this temporary result set can be then
2:37:17used in things like Cas select statement
2:37:20insert update or even delete this only
2:37:22exists during the execution of the query
2:37:25and it's defined using a with statement
2:37:28before it then defining the table and
2:37:30then using as and then in closing all of
2:37:32the in parenthesis how we want to create
2:37:34this temporary result set we can then
2:37:36call this within a query below it so
2:37:40let's actually work a problem to see how
2:37:41CDs are used for this we're going to be
2:37:44finding the companies with the most job
2:37:46openings now we need to break this up
2:37:48into two parts and that's why CTS are
2:37:50perfect for this because first we get
2:37:52the need to get the total number of job
2:37:54postings per Company ID which is located
2:37:57inside of our fact table of job postings
2:37:59fact but then once we have this total
2:38:02number we then need to combine it with
2:38:04the company name which was in the
2:38:06company dim column so we're going to
2:38:08start by building our query first for
2:38:11that first bullet of getting the total
2:38:13number of job postings per Company ID so
2:38:15for any of these queries I want to just
2:38:16start small we're going to be selecting
2:38:18the company ID and looking at the what
2:38:20comes back from this so we can see that
2:38:22it has multiple different IDs in so now
2:38:24we need to go into actually aggregating
2:38:26it for this we're going to be using the
2:38:28count function and we can just put in
2:38:30there the asteris symbol is that's going
2:38:31to count the number of rows but anytime
2:38:33we're do an aggregation function we need
2:38:35to specify the group by on how we're
2:38:38going to be grouping it and in this case
2:38:40we want to group it by what's given in
2:38:42that table right there of that company
2:38:44ID all right let's run this
2:38:46one okay so now we have these counts so
2:38:49we can actually just go back and verify
2:38:51for zero we had four right here and it
2:38:54looks like we have four returning so
2:38:56this is the core statement that we're
2:38:57going to be using inside of our CTE so
2:39:00we can go ahead and create that now so I
2:39:02Define this using a wi statement and
2:39:04then give it a table name of company job
2:39:07count using the Alias as and then inside
2:39:09parentheses all the query that we want
2:39:11to do and then just to get started and
2:39:13making sure that this works properly I'm
2:39:15just going to do a simple select from
2:39:17statement to actually Define this and
2:39:19this will just query this temporary
2:39:21result set that we've defined up here
2:39:24and running it we have the same results
2:39:27that we had before now it's just through
2:39:29that temporary result set so just
2:39:31refresher on the schema we have that job
2:39:34postings fact data that is connected to
2:39:36our company dim table using a company ID
2:39:40so we needed to use a join method in
2:39:43order to combine these two tables
2:39:45together to combine these two tables
2:39:47we're going to be using a left join and
2:39:50for this we want to use our a table make
2:39:53sure we have everything from it as the
2:39:55company dim table because maybe there
2:39:57may be some companies that don't
2:39:59necessarily have job postings that we
2:40:01aggregated from the B table so we want
2:40:03everything to be listed there so way if
2:40:04there isn't there's a zero associated
2:40:06with it that there's no job postings so
2:40:08as you guessed it B is going to be the
2:40:10fact table that we're going to be
2:40:12combining to this so let's start simple
2:40:15with this basic query down here and
2:40:16we're just going to be looking at first
2:40:18that comp company dim table and so just
2:40:21running this query right here command D
2:40:23command D we can see all the different
2:40:25names from it so now that we have this
2:40:27let's actually move into joining this
2:40:30I'm going to do a left join specify that
2:40:33temporary result set and then what we're
2:40:35going to match these two left joins on
2:40:38which is the company ID from each table
2:40:40we're not going to necessarily get any
2:40:41different results with this but I'm
2:40:42going to go ahead and exate execute this
2:40:43entire query to make sure it's actually
2:40:45working properly okay it's working
2:40:47properly now if you remember we want to
2:40:48get the total number of job postings per
2:40:50Company ID and have it basically
2:40:53associated with a company name so this
2:40:56is from the company dim table and I'm
2:40:58going to rename this as company name so
2:41:01I want this value from this count star
2:41:03statement right now I don't have an Al
2:41:05should have done that before so we're
2:41:06just going to name this as total jobs
2:41:09and then we want to have it appear in
2:41:11this new query that we have here so I'm
2:41:13going to then Define it okay let's
2:41:16actually run this entire query Now
2:41:19command e command e bam and now we have
2:41:22the company names along with their total
2:41:25number of jobs right now remember we
2:41:28want to get at the highest who who has
2:41:31the most so we need to now do an order
2:41:32buy and I'll just add this here at the
2:41:34bottom order bu total jobs in descending
2:41:38order let's now rerun this query boom
2:41:42all right now we got it and it looks
2:41:44like umgo is one of the highest amount
2:41:48along with other popular companies like
2:41:49city capital 1 Walmart and centure so
2:41:53yeah all right it's now your turn to
2:41:54give it a try and I have for those that
2:41:57purchas the course certificat notes I
2:41:58have multiple different practice
2:41:59problems that you can go to three for
2:42:01subqueries and three also for CTE if
2:42:04you're still not feeling confident on
2:42:06actually tackling these problems on your
2:42:07own just stand by because in the next
2:42:09section we're going to do uh go into
2:42:11another problem of using CTE and a
2:42:15pretty fun example so feel free to even
2:42:17hold off until after that section all
2:42:19right with that see you in the next
2:42:25one all right let's get into a practice
2:42:27problem of how to use CTS even further
2:42:30than the previous examples for this we
2:42:33have a pretty exciting example that
2:42:34Kelly came up with and this problem was
2:42:36inspired by my app. nerd. te that
2:42:39Aggregates job posting skills depending
2:42:42on a different job title you want to
2:42:44look at so if I wanted to look at
2:42:45something like data analyst we could see
2:42:47what are the top results here anyway
2:42:49Kelly came with this problem find the
2:42:50count of the number of remote job
2:42:53postings per skill so we're going to be
2:42:55focusing specifically on this cuz say in
2:42:57my case I'm a data analyst looking for
2:42:59remote jobs and that's what I care about
2:43:01most we'll display the top five skills
2:43:03by their demand and then include other
2:43:05attributes like skill ID name and the
2:43:07count of job postings now how are we
2:43:09going to tackle this well remember the
2:43:12job postings fact table has all of our
2:43:14different job postings but it doesn't
2:43:16have necessarily all the different
2:43:17skills in here instead we have to use
2:43:20that skills job dimm table to get the
2:43:22all the different correlated jobs to
2:43:24skills and then our final skills
2:43:26dimensional table that includes the name
2:43:29of those skills so the first thing we're
2:43:31going to do is build a CTE that
2:43:34basically collects the number of job
2:43:36postings per skill so we're going to
2:43:38have to do some sort of join between our
2:43:40job posting fact table and our skills
2:43:42job dim table once we have this
2:43:44temporary result set we can then take
2:43:47this a step further and then combine it
2:43:49with our skills dim table to actually
2:43:52give us our final results that have the
2:43:54skill name in it for all the joins
2:43:56within this portion we are trying to get
2:43:59a count of jobs that actually exist we
2:44:02don't really necessarily care about if
2:44:04there's values that don't exist so for
2:44:06this we're going to find that inner join
2:44:08is the best method to use for this so
2:44:10let's start with the very Basics and
2:44:12then build upon there for the CTE I just
2:44:14want to look at first the skill ID
2:44:17column of the skill from the skills to
2:44:19job dim table which we're going to just
2:44:21conveniently rename skills to job
2:44:25command e command e all right so we're
2:44:27already seeing repeats in here I'm going
2:44:29to go ahead and just actually showcase
2:44:31the job ID column as well put a common
2:44:34in there run this query again so we can
2:44:36actually see this better because I don't
2:44:38think it's like showing fully what we
2:44:39want to see all right this one's much
2:44:41better this one has so for the job ID
2:44:43let's say zero which is one of the job
2:44:45IDs it has the skill ID Associated of it
2:44:48of zero and one so there's multiple
2:44:51skills associated with this job so let's
2:44:53go ahead and actually join this on our
2:44:56job postings fact table since that we
2:44:58know that they're correlated through
2:44:59that job ID column so the first thing we
2:45:01do is specify in join after that from
2:45:03statement soing the table and we're
2:45:05going to rename it just simply as job
2:45:07postings from there we're going to
2:45:08specify how we're going to connect this
2:45:10and we're connecting it on the job ID I
2:45:12want to make sure this works so I'm
2:45:13going to just do command e command D and
2:45:15job ID is ambiguous so I need to specify
2:45:18right because it's both of the skills to
2:45:20job and the job postings doesn't really
2:45:22matter which one we specify we're going
2:45:24to just specify this one right here and
2:45:26then run this query again all right so
2:45:29the query is running there's nothing
2:45:31necessarily new in here that we've
2:45:33included but if you recall we want to
2:45:37look at or filter for the jobs that have
2:45:40work from home as true or remote jobs so
2:45:44the first thing I do is just add to the
2:45:46select statement to actually see it
2:45:48shown in here
2:45:49and it looks like we got a lot of false
2:45:51results I'm sure we'll eventually get
2:45:54some true oh we got a True Result right
2:45:55here so let's actually now Define that
2:45:57where to meet this portion of the
2:45:59question of remote jobs and we want to
2:46:01specify where job postings of job work
2:46:05from home is equal to True ring this
2:46:08query again all right so they're all
2:46:10true in this case we don't need this
2:46:12column anymore listed here so I'm going
2:46:14to go ahead and delete it I just
2:46:16showcase it to make sure that we were
2:46:17building the query correctly
2:46:19now getting back to that core problem we
2:46:20want to get the count of the number of
2:46:22remote job postings per skill so we have
2:46:25this skill ID already we need to now get
2:46:29a count and we're going to be doing that
2:46:32count star method we'll sign this at an
2:46:34alas of skill count anytime we do an
2:46:38aggregation well we need to do a group
2:46:40by and we're wanting to group it by
2:46:42obviously that skill ID now since we're
2:46:46combining by this skill ID and do this
2:46:47aggregation this job ID column is no
2:46:50longer useful and actually we'll throw
2:46:51off our aggregation so we're going to go
2:46:53ahead and get rid of it and we're going
2:46:54to go ahead and run this query all right
2:46:57so now we're seeing that skill ID and
2:46:58skill count I can see here that skill
2:47:01count these this zero and one Whatever
2:47:03skills they are associated with the IDS
2:47:05are this high right here at
2:47:0740,000 so we pretty much have our CTE
2:47:10built so let's go ahead and build that
2:47:12CT out using that wi statement defining
2:47:15it as remote job skills and then using
2:47:18the alien operator to put that all
2:47:20inside of there so now that we've
2:47:22combined that job postings fact with
2:47:24that skills job dim table and we have
2:47:27this in a common table with a skill ID
2:47:31for it we can now go further and do
2:47:33another inner join with this CTE to that
2:47:37skills dim table so the first thing I'm
2:47:39going to do is just make sure that this
2:47:40CTE works by just doing a select star of
2:47:43this remote job skills running this
2:47:45query I can see that it works so let's
2:47:48move into actually doing that inner join
2:47:50with the skills dim table I specify
2:47:52inner join and then the table assigning
2:47:55the Alias of skill and we're going to
2:47:58connect both of these on that skill ID
2:48:01column and now that it connected we need
2:48:03to specify the columns we want for this
2:48:05so the first we'll just keep it simple
2:48:07with only the skill ID but obviously
2:48:09next we want the skill name with that
2:48:11defined we need to now have the most
2:48:13important value skill count so let's go
2:48:15ahead and see if this entire query works
2:48:19then looks like I have an error here I
2:48:21referred to the table in join as skill
2:48:24when I reference it up here as skill so
2:48:26we're just going to go ahead and add
2:48:27Nest that whoopsies and we going to
2:48:29select this whole statement again and
2:48:31run it again all right so now we have
2:48:33that skill ID skill name and then the
2:48:35skill count couple things we are left to
2:48:37do now is actually order this and then
2:48:40remember we only need the top five
2:48:42results so we'll add an order by
2:48:44statement right here specifying that we
2:48:46want to order it by that skill count in
2:48:49descending order and then we only want
2:48:51the top five results so I'm just going
2:48:53to throw in a limit statement right here
2:48:55selecting all this query and then
2:48:57pressing command e command D bam now we
2:49:00got it and we can see what were those
2:49:0140,000 values now it was obviously
2:49:04Python and then SQL followed next by AWS
2:49:07Azure and Spark now I'm going to throw
2:49:09one simple caveat to this question
2:49:12because frankly I really care about data
2:49:14analyst jobs so I'm going to filter our
2:49:16CTE further for only data analyst jobs
2:49:18JS using within that and within the wear
2:49:21statement and and specifically where
2:49:24that job title short equals data analyst
2:49:27all right let's go ahead and run this
2:49:29all to see what it looks like for data
2:49:31analyst and Bam these are more of
2:49:33results that I would expect for data
2:49:36analyst CU well that's the query anyway
2:49:38we got SQL Excel and then python Tableau
2:49:40and powerbi so regardless of what you're
2:49:43filtering for it goes to show the
2:49:44importance of SQL this is the top skill
2:49:48and so I think it's beoo of you that
2:49:49you've spent the time to learn this all
2:49:51right for those that purchased the
2:49:52course certificates and notes feel free
2:49:54to go ahead if you haven't already to
2:49:56work those practice problems working
2:49:58through those example problems for CTE
2:50:00and also subqueries and once you're done
2:50:03with that we'll be moving into the last
2:50:04major topic of this Advanced section
2:50:07focusing on unions with that see you in
2:50:10the next
2:50:15one all right welcome to this last major
2:50:18topic we'll be covering in the advanced
2:50:19section on unions this is very important
2:50:23for combining tables it's directly if
2:50:25you will opposite of how we're doing
2:50:27joins so joins are used in the case
2:50:30whenever we want to combine tables that
2:50:34maybe relate on a single value such as
2:50:37in the case of combining like the job
2:50:39posting fact table with the company dim
2:50:41table we're going to combine this on the
2:50:43company ID column now if you remember
2:50:45previously we created three taable
2:50:49for those job postings in January
2:50:52February and March I'm moving this over
2:50:55here so we can actually see it better
2:50:57the January table has the same columns
2:51:00as that February table along with that
2:51:03March table so in this case if we wanted
2:51:06to combine these basically rowwise we
2:51:10could use a union operator to do this so
2:51:13the first we're going to cover is Union
2:51:15and it comines the results from two or
2:51:18more or select statements you would use
2:51:20this operator by first defining a
2:51:23statement such as select column name
2:51:24from table one and then specifying Union
2:51:28and then underneath it specifying the
2:51:30next table you want to do it select
2:51:31column name from table two and now in
2:51:33order to Union this there is one
2:51:35specific condition this has to meet they
2:51:37have to have the same number amount of
2:51:39columns in this case we're only doing
2:51:41one column here but in our January
2:51:44February March jobs they can have a
2:51:46multitude of columns they just all have
2:51:48to match and they all have to be the
2:51:50same data type and the last thing to
2:51:52note is it gets rid of all duplicate
2:51:54rows whenever you combine this unlike
2:51:56the next operator Union all so let's
2:51:58start with this simple query I want to
2:52:01look into seeing all the different job
2:52:04titles and Company IDs and job locations
2:52:07for the month of January these are the
2:52:10main although they're not all the
2:52:11columns are the main attributes that I
2:52:12care about I'm going go ahead and run
2:52:15this query just make sure that it runs
2:52:16perfectly fine and we can see here here
2:52:18all the different results that we're
2:52:20seeing from it so now let's actually
2:52:22join this with our February jobs table
2:52:24first I'm going to specify Union then
2:52:27I'm going to do another select statement
2:52:29for the February jobs and I'm making
2:52:31sure that I have the exact same columns
2:52:33listed as the January jobs right now
2:52:36from January jobs we have around 92,000
2:52:39jobs if we run this all together we can
2:52:42see now we have over
2:52:45107,000 jobs so let's just take this a
2:52:48step further and now add in all of the
2:52:51March jobs so we have now January
2:52:54February March and we're specifying all
2:52:55the different columns how many jobs are
2:52:57we going to have return and for this one
2:52:59now we're up to 143,000 jobs so that's
2:53:03the union keyword moving on to Union all
2:53:06it's pretty much used in the same exact
2:53:08way we're still using two or more select
2:53:11statements in order to combine different
2:53:13tables and they need to have whenever
2:53:15we're doing this the same amount of
2:53:16columns and the same data type now the
2:53:18thing about the keyword all of Union all
2:53:20is that it returns all rows even
2:53:22duplicates and Kelly and I have talked
2:53:24about this further and we find that we
2:53:26use mostly this one in our jobs as we
2:53:29typically want to get all the data back
2:53:31to make sure we're looking at everything
2:53:33so going back to that previous query
2:53:34that we built here all this takes is
2:53:37adding in all to both of these now
2:53:40remember the previous query we got
2:53:42around 143,000 results so let's see what
2:53:46we're going to get back with this Union
2:53:48all we should be getting back more
2:53:50values and we do looks like
2:53:54220,000 so quite a bit more than this
2:53:58almost 880,000 more values or 880,000
2:54:00duplicates if you will all right now
2:54:02it's your turn to give it a try we have
2:54:04a few practice problems built in order
2:54:07to implement both the union and the
2:54:09union all if you're still not
2:54:11comfortable with either of these I do
2:54:13have a practice problem coming up where
2:54:14I'm using Union also in combination with
2:54:17some other things like subqueries and
2:54:19CTE so you can feel free to hold off
2:54:21until after that practice problem if
2:54:23you're not exactly comfortable yet with
2:54:25unions all right with that see you in
2:54:27the next
2:54:33one all right let's dive into the last
2:54:35practice problem of this Advanced
2:54:37section before we actually dive into our
2:54:39project for this I want to do an
2:54:41analysis of the job postings from the
2:54:43first quarter that have salary greater
2:54:46than $70,000 that's like my target range
2:54:49right now so we have those tables
2:54:50already on January February March we're
2:54:52going to use our Union operators to
2:54:55combine them all and then we're going to
2:54:57be using it within a subquery to then
2:55:00analyze it allowing us to thus filter it
2:55:02for those jobs greater than 7,000 and
2:55:05get a snapshot of the jobs we actually
2:55:06want so anytime we're building a
2:55:07subquery or CTE I like to just tackle it
2:55:10first for this we're going to be
2:55:11selecting all the different columns from
2:55:14each of these different months tables so
2:55:17I'm going to go ahead and just run right
2:55:18here to show what it looks like okay and
2:55:21we can see that it has all the different
2:55:23columns associated with it so the next
2:55:25thing we need to do is specify that
2:55:27Union and we're going to use all because
2:55:29we don't want to remove any duplicates
2:55:31and then once again specify the next
2:55:33select statement for the February jobs
2:55:35and then our final Union all statement
2:55:37so we can combine this finally with our
2:55:39March jobs let's actually combine this
2:55:40all and see how many jobs we actually
2:55:44have here all right and it looks like we
2:55:47have around 200 200
2:55:4912,000 which checks with what we did
2:55:52last time so let's go ahead and start
2:55:54building this into a subquery so I'm
2:55:57must do select star and then from and
2:56:00then build this Union all portion into
2:56:03the subquery now because we defined this
2:56:06table I'm going to give it an alias of
2:56:08quarter 1 job postings let's go ahead
2:56:11and run this again make sure that it's
2:56:13working just properly okay sweet still
2:56:15returning all the different jobs all
2:56:17right I don't want all of these
2:56:19different columns here so instead what
2:56:22I'm going to do is I'm going to define
2:56:23the four main Columns of interest for
2:56:25this we're going to be doing job tile
2:56:26short job location job via and the job
2:56:29posted date specifically only the date
2:56:32and then remember the last portion of
2:56:34this question is that we want to find
2:56:37the job postings that have an average
2:56:38yearly salary greater than 70,000 so
2:56:41with all of this I need to add a filter
2:56:43on it using a wear statement we're going
2:56:45to specify all this for greater than 70
2:56:48,000 going ahead and running this all
2:56:51command e command e boom we now have all
2:56:53this information I'm going to modify
2:56:56this further mainly two things one I
2:56:58want to see the salary and two I really
2:57:01only care about that iist jobs so the
2:57:03first thing I add is quarter one job
2:57:05postings and then I'm going to specify
2:57:06that salary year average and then inside
2:57:09of our weer statement I'm going to use
2:57:11that and keyboard and specify where the
2:57:15job title short is equal to data analyst
2:57:19also one other bonus I'm just going to
2:57:21throw in an order bu so that way we have
2:57:23this listed for the salaries from
2:57:26highest to lowest okay let's go ahead
2:57:29and run this bad
2:57:32boy bam now we have all these different
2:57:35jobs and I can see that I need to apply
2:57:38for y combinator and it's a remote job
2:57:40that pays
2:57:43$650,000 now I went ahead and removed
2:57:45this table name prior to to these column
2:57:49names some of you may run into problems
2:57:50if you don't include this but I didn't
2:57:52run into problems with it so I believe
2:57:54shorter and brevity is better in all the
2:57:56cases if I go ahead and remove that all
2:57:58for all those different cases make this
2:57:59look a little bit more concise running
2:58:01this query I still get those same
2:58:03results all right now it's your turn
2:58:05we're going wrap up this final problem
2:58:06so we can finally moved into that
2:58:08portfolio project we're building with
2:58:10that see you in the next
2:58:11one then nerds welcome to the project
2:58:14section of this course so you've already
2:58:17learned so much in the basics and also
2:58:19Advanced section with using SQL queries
2:58:22but now it's actually time to put all
2:58:25that knowledge to the test by building a
2:58:27Capstone project now there's a few goals
2:58:30with this project we're going to be
2:58:32working with that same data set that we
2:58:35built inside of the advanced section and
2:58:37exploring it further and your goal for
2:58:39this is twofold you're going to be
2:58:41looking to analyze what are some of the
2:58:43most top paying roles and also skills
2:58:47but there's going to be a list catch to
2:58:48this similar to the previous queries you
2:58:50don't have to necessarily limit this
2:58:52search and follow exactly like I do
2:58:54searching for data analyst remote jobs
2:58:56you can actually fine-tune it to your
2:58:59job of choice Additionally you can
2:59:01change it to things like the location
2:59:03you are within the world you should need
2:59:05to research into the data itself and
2:59:07find a close enough location or
2:59:09locations so for this there's going to
2:59:10be a few different deliverables that
2:59:12you're going to be able to Showcase as
2:59:14experience with the work that you've
2:59:15done I'm going to show it here within
2:59:17the project SQL folder that you're going
2:59:20to be building and I have five files
2:59:22here for the five different queries
2:59:23we're actually going to be diving into
2:59:25and finding further insights about not
2:59:28only roles but also skills for top
2:59:31paying jobs additionally with this we're
2:59:33going to be building out your own
2:59:35personal read me file and this is
2:59:37basically a descriptor of the project
2:59:39itself and it's going to go in and
2:59:41actually showcase all the different
2:59:43analysis you did and besides just
2:59:45analysis we can also go as far to show
2:59:48show some of the results that we find
2:59:49from it and we're going to be using
2:59:51everything that we find in order to draw
2:59:53some conclusions and help you out in
2:59:55finding out what are the most optimal
2:59:57roles and skills you should be pursuing
3:00:00so this project can not only be
3:00:01rewarding for those looking to learn SQL
3:00:03but also maybe if you're job searching
3:00:05or looking for promotion you may able to
3:00:07find insights within this data set and
3:00:10use it in your real life now all this
3:00:12work you did is great but if you just
3:00:13keep it on your computer and don't tell
3:00:15anybody about it does really no good so
3:00:17we're going to be working on this also
3:00:19by showcasing this to things like GitHub
3:00:22and Linkedin both of these sections
3:00:24using GitHub and Git along with LinkedIn
3:00:27are completely optional so whenever we
3:00:29get to these one of these sections I'll
3:00:31call it out and let you know that hey
3:00:33this section we're working on building a
3:00:35repository you don't need to pay
3:00:36attention to it if you don't want to but
3:00:38I highly recommend that you do anyway
3:00:40with GitHub we're going to be actually
3:00:42able to Showcase not only all the
3:00:44different SQL that we have which are all
3:00:46these different files right here but
3:00:48we'll be able to Showcase mainly that
3:00:50readme that we built to Showcase all the
3:00:52different work that we did and our
3:00:53findings and you may be wondering what
3:00:55questions are we actually going to be
3:00:56exploring for this well they all revolve
3:00:59around top paying jobs and also skills
3:01:02and so we'll be building on each one of
3:01:04these with each of the queries we do and
3:01:07finally ending up with probably the most
3:01:09exciting one trying to identify the most
3:01:11optimal skill something that's a high
3:01:13demand and also high paying we're going
3:01:16to dive much further into this after the
3:01:18next section we're going to be actually
3:01:20creating our project repository to thus
3:01:22upload into GitHub one quick note for
3:01:25those that bought the course notes and
3:01:26certificates inside of your notes you're
3:01:29going to have access to the questions
3:01:32and all the different problems that
3:01:33we're trying to solve with it in
3:01:35addition to this we're going to break it
3:01:37down even further inside of here by
3:01:39including things like the reasoning so
3:01:41what's going on behind each step of the
3:01:44query and why we're doing each step
3:01:46along with the final query itself and
3:01:49what expected results you should be
3:01:51finding all right in the next video
3:01:52we're going to be diving into actually
3:01:54setting up your GitHub account and
3:01:56setting up the project repository so we
3:01:58can get this thing started to host
3:02:00online so we after we get it completely
3:02:01built we'll be able to put it all there
3:02:04like I said at the beginning this
3:02:06portion of the GitHub integration is
3:02:08completely optional but you learn a
3:02:11highly valuable skill of git so I do
3:02:13recommend that you do it all right with
3:02:16that I'll see you in the next one
3:02:22so what is a repository well it's a
3:02:26personal library for your project where
3:02:27you can keep manage and record every
3:02:29change if you know of track changes in
3:02:32Microsoft Word this is very similar to
3:02:35that so how does this version control
3:02:37work well there's a special folder
3:02:41inside of your project that we're going
3:02:42to be installing that's going to track
3:02:44all the changes that go through it here
3:02:47I have another data science project
3:02:49inside of my finder window and it has
3:02:51all the different contents that are
3:02:52necessarily visible to me but in this
3:02:55one there's actually some hidden files
3:02:57there's this one for vs code we don't
3:02:58really care about we care about this one
3:03:00right here onget and that's how we're
3:03:03going to be maintaining our Version
3:03:04Control inside of this folder it tracks
3:03:07all the different changes that we're
3:03:09going to be doing to our project
3:03:11exploring the contents of this is sort
3:03:13of out of the scope and probably above
3:03:14my PR grade but it's just important to
3:03:16understand the purpose of this folder so
3:03:19on our local computer ourself we have
3:03:21this project or if you will a working
3:03:24directory and then as we're working on
3:03:26it and collecting new documents it's
3:03:28going to go into a staging area and once
3:03:30we have something we want to save or
3:03:32commit we can then do that and it will
3:03:35save to our local repository which is in
3:03:37that dogit file now all of this is on
3:03:40our local machine or our computer
3:03:43locally but let's say we wanted to
3:03:45collaborate with others or even share
3:03:47this like we're going to be sharing with
3:03:48this project we could then host it to
3:03:51something like GitHub in this case it
3:03:53will then host a copy of all of the
3:03:56different content that we have locally
3:03:59on our machine so in that case where I
3:04:00showed you my previous project that
3:04:02located here on my machine I can also go
3:04:05up to GitHub and this is where I'm
3:04:07actually hosting that same content at
3:04:10this is would be my remote repository so
3:04:13let's actually get into doing this for
3:04:14our project and there's a few steps
3:04:16we're going to be walking through for
3:04:18this the first if you haven't done it
3:04:20already we're going to go through and
3:04:21actually install git git is the most
3:04:24popular version control system so we're
3:04:26going to be using it for this next we're
3:04:28going to be diving into setting up a
3:04:29GitHub account if you don't have one
3:04:31already and it's pretty simple once we
3:04:33have that we can now get into actually
3:04:36initializing a repository within our
3:04:39project so basically we're going to be
3:04:40creating that dogit folder inside of our
3:04:43project and then now that we have this
3:04:45local repository we're going to want to
3:04:48push this repository into GitHub and
3:04:51thus we're going to have a remote
3:04:53repository for the world to see so let's
3:04:55get into download and git don't be
3:04:56afraid if you're unsure if you have git
3:04:59installed or not as reinstalling it's
3:05:02not going to cause any issues anyway
3:05:04navigate to this URL that's on the
3:05:06screen right here or just simply Google
3:05:09get download and it should navigate you
3:05:11to this page from here select the
3:05:14operating system of choice that has
3:05:16yours and download it for Windows it's
3:05:19pretty simple as you're going to walk
3:05:21through a similar download process that
3:05:23you did like postgress and it'll take
3:05:25you through all the steps of the process
3:05:26and you'll have it installed for Mac
3:05:28users like me it's a little bit more
3:05:30complicated you're going to want to open
3:05:32up your
3:05:35terminal and then from there insert this
3:05:38Brew install git and you're going to
3:05:41press enter and execute it now if you
3:05:43don't have home brew you're going to
3:05:44navigate to the link here that it has
3:05:47and and once again it's very simple
3:05:49you're going to just copy this command
3:05:51that it gives you right here for
3:05:52installing home brew and then inside of
3:05:55your terminal paste it and run this
3:05:57command then once you have home brew
3:05:59installed you run this other command of
3:06:00Brew install G anyway I promise you
3:06:02that's the last time you'll see the
3:06:03command line for this course now that
3:06:05gets installed we need to move into
3:06:07creating your GitHub account if you
3:06:09don't have one already because you need
3:06:11an account in order to host your
3:06:13repository here's my GitHub account
3:06:14right here has information about me and
3:06:17all my different Social Links and then
3:06:18from there it has all the different
3:06:20projects I've worked on here along with
3:06:22um more of them in this one right here
3:06:25so you're going to navigate over to
3:06:26github.com
3:06:28signup and it will walk you through the
3:06:31setup process to set up your account
3:06:33inside of GitHub once you have this
3:06:35account set up I'd go in and actually
3:06:37edit your profile putting a picture in
3:06:40and then updating all the relevant
3:06:41information that you want to include on
3:06:43your profile all right so we have git
3:06:45installed on our computer and we have
3:06:47have our GitHub account now we need to
3:06:50move into actually creating that local
3:06:53repository and then pushing it to GitHub
3:06:55for our remote repository now there's
3:06:58four major Ops you can do for this we're
3:07:00going to only be going through the vs
3:07:02code option which I feel is the simplest
3:07:05for those that buy the course notes and
3:07:07certificate I have detailed instructions
3:07:10for the second and third option as well
3:07:12if you want to do that all right so
3:07:14let's get into creating that repository
3:07:15using vs code so inside of here you
3:07:18should have navigated or have your
3:07:20project open for me the project is SQL
3:07:22project data jobs analysis I'm going go
3:07:25ahead over to the activity bar and
3:07:27select Source control and for this we
3:07:31have two different options the first is
3:07:34just initialize a repository so
3:07:36basically create that local repository
3:07:38that dogit folder for you then to have
3:07:41locally but this doesn't push to GitHub
3:07:44so we actually want to do the second
3:07:45option of publish to GitHub as it not
3:07:47only initializes a repository locally
3:07:50but also it pushes all of your different
3:07:53content that you have here to GitHub now
3:07:56I've already logged into GitHub so
3:07:58whenever you click this button it may
3:08:00prompt you to go through the login steps
3:08:02to set up your GitHub account to be
3:08:05associated with your vs code profile
3:08:07right here walk through that process and
3:08:09then you'll get to the next step that
3:08:10I'm going to be at so I'll click publish
3:08:12to GitHub it's going to ask do I want to
3:08:14publish to a private repository or a
3:08:16public Repository
3:08:18I want this to be available for the
3:08:19world to see so I'm going to do public
3:08:21then it's going to ask me which files
3:08:23you should include in this repository
3:08:26this is very important that you get this
3:08:27step right as I had to repeat this
3:08:28earlier I'm going I made a mistake so
3:08:31for one we don't care about these dot
3:08:34files right here these are hidden files
3:08:36that have no effect on showing your work
3:08:38so I'm going to go ahead and uncheck
3:08:40them additionally these are the other
3:08:42folders I have right now I have that SQL
3:08:44load that you had that CSV files of all
3:08:47the different CSV file data that we
3:08:48Import in our database and then I
3:08:50created another folder for advaned SQL
3:08:52that had all those different problems
3:08:53that we work so far in the advance
3:08:55section this SQL files folder is
3:08:58extremely large and so because of that
3:09:01GitHub isn't going to allow you to
3:09:03upload all that data to it Additionally
3:09:06you don't really need to be keeping all
3:09:07that stuff on a GitHub anyway to show
3:09:09your work so I'm going to go ahead and
3:09:11actually uncheck it so for you you
3:09:13should only have one folder and maybe
3:09:16two actually created this point that you
3:09:19want to leave checked you're going to go
3:09:20ahead and click okay it should give you
3:09:23this message of successfully published
3:09:25this project to GitHub I'm going to go
3:09:27ahead and open on GitHub and Bam here we
3:09:30have it inside of GitHub we have our
3:09:32folder that I have for advanced SQL of
3:09:34all our different files that I created
3:09:36our SQL load for the files to create our
3:09:39database itself and then this dog ignore
3:09:43file and I'm going make this a little
3:09:44bit bigger if you recall these are the
3:09:47same files that we basically unchecked
3:09:50from before they were put into thisg
3:09:53ignore file hence they're not being
3:09:55tracked in git and because they're not
3:09:57being tracked in git they're not getting
3:09:59uploaded to GitHub so we just did all
3:10:01four of the steps necessary to get git
3:10:04and GitHub set up for this project and
3:10:07just to Showcase this is our project
3:10:09that we have right here I'm going to go
3:10:11ahead and show our hidden folders that
3:10:13we have inside of here such as that get
3:10:15ignore and also get so we can actually
3:10:18see that we're tracking all the changes
3:10:20inside of here don't check don't touch
3:10:21any of these files right here but I just
3:10:23wanted to Showcase where all these are
3:10:25and that they're actually in here in the
3:10:26project now when I navigate back into
3:10:29VSS code I'm going to see that yes it
3:10:31has that new do ignore file then you're
3:10:33going to also notice that these two
3:10:36folders here are grayed out and that's
3:10:39because they're no longer being tracked
3:10:41by Source control and because anytime we
3:10:44do any changes inside of them they're
3:10:45not going to be tracked so they're gray
3:10:46it out to let you know so we're going to
3:10:48walk through two different ways to
3:10:50actually Implement changes inside of
3:10:52your working directory and local
3:10:54repository and then have it updated
3:10:56inside of your remote repository so the
3:10:58first thing we're going to be looking at
3:10:59are pushing changes and this is
3:11:02basically uploading your local content
3:11:05that's been adapted or updated and then
3:11:08pushing it up to GitHub so I'm going to
3:11:10create a folder called project and then
3:11:13SQL this is the folder or directory
3:11:15we're going to be using to store all the
3:11:18different queries that we're going to be
3:11:19working on during this project section
3:11:22inside of this project SQL I'll go ahead
3:11:24and create a file and I'll name this
3:11:26we'll keep it simple right now query
3:11:291sq now after creating it it should open
3:11:32up here on the right and you're going to
3:11:33notice that these contents are green and
3:11:36if we navigate over to our source
3:11:38control tab we can see that it's
3:11:41actually tracking that it not only it
3:11:43sees that we have created this new
3:11:44folder but also this new file anyway I'm
3:11:46going to just just come inside of here
3:11:48um I'm going to put in a comment delete
3:11:51this later and I'm going to save this
3:11:54I'm going close out of this file so
3:11:56let's go ahead and now push these
3:11:58changes so inside of source control I'm
3:12:00going to come up here and give this
3:12:03commit that we're going to be doing a
3:12:05name specifically I'm going to just say
3:12:07name this push example because we're
3:12:08doing a push example from there we're
3:12:10going to go into actually committing it
3:12:12so we'll press commit and now these
3:12:15changes have been updated inside of our
3:12:18local repository and these are stage
3:12:21changes if you will so now we want to
3:12:23get these changes that are on our local
3:12:26repository up to the remote repository
3:12:28so we're going to then sync changes so I
3:12:30can see that there's nothing here in
3:12:31changes staged anymore everything should
3:12:34be UPS to date let's go into GitHub and
3:12:36see if it was updated so inside of
3:12:38GitHub I'm just going to refresh this
3:12:39page to see if it has and now we have
3:12:42this folder of project SQL which has our
3:12:45query inside of it saying like this
3:12:47layer so that was example of how we can
3:12:50push changes from local repository up to
3:12:52our remote repository now let's actually
3:12:55look at an example of pulling changes
3:12:57we're going to make an alteration on our
3:13:00remote repository specifically inside of
3:13:02GitHub and then from there pull it onto
3:13:05our local machine to be in our local
3:13:06repository so you can also make changes
3:13:09inside of GitHub itself in this case
3:13:12right here it's saying hey you want to
3:13:14add a readme and we do want to
3:13:15eventually create a readme so we can
3:13:17just do it here if we wanted to so I'm
3:13:20going to select add a readme and so I'm
3:13:23going to put a message in here to update
3:13:24the contents of this later and now we're
3:13:27going to commit these changes to our
3:13:29remote repository we have our commit
3:13:31message right here of creating a read me
3:13:33then you should have your email that
3:13:35you're using for this when to commit it
3:13:36directly to the main branch so I'll
3:13:38commit changes all right so now we have
3:13:41this read me here inside of here and I
3:13:43can see the read me down at the bottom D
3:13:45update contents to this later now back
3:13:47on my local machine inside of vs code
3:13:51this file is not there that's because we
3:13:53haven't done a pull yet to get that
3:13:56information or get that new file here so
3:13:58we can do that by going over to Source
3:14:01control and then selecting these three
3:14:03ellipses right here and then going under
3:14:05pull push and specifically selecting
3:14:07pull now when I navigate over to the
3:14:10explore menu we now have this read me
3:14:13file right here which the two do update
3:14:16content toist layer all right so now we
3:14:18have our repository all set up not only
3:14:20locally but also remotely and we're now
3:14:23ready to go ahead forward with actually
3:14:25doing our queries and getting everything
3:14:27done for building our project now at
3:14:29this point you know everything that you
3:14:30need to know for git however you'd like
3:14:33to learn more I have this video right
3:14:35here where I go into further detail on
3:14:38how I use git as a data analyst and
3:14:41break it down even further so feel free
3:14:43to check that video out I'll link it
3:14:45with all the other resources I'll be
3:14:46providing for this course all right with
3:14:48that I'll see you in the next one where
3:14:49we jumping into the
3:14:54queries all right so let's move into
3:14:56that first question that we're going to
3:14:58be solving using a SQL query in order to
3:15:01understand what are the top paying jobs
3:15:03for my role in my case I'm a using data
3:15:05analyst as a reminder we're going to be
3:15:06walking through five separate questions
3:15:08and these all really build on each other
3:15:11so we're going be gaining insights from
3:15:13each and then by the end we're going to
3:15:15have a more holistic picture of what
3:15:17roles and what skills we should be
3:15:19targeting so if you haven't done so
3:15:21already create that folder where we
3:15:23putting all this I created this one
3:15:25called projector SQL and then from there
3:15:28start a new file I named this one query
3:15:311 initially I'm going to actually go in
3:15:33and rename it specifically I'm going to
3:15:35give it that one to make sure it's up at
3:15:37the top whenever we actually save these
3:15:38files sequentially and then also name it
3:15:41top paying jobs cuz that's what we're
3:15:42trying to solve so at the top of my file
3:15:45I'm going ahead and put what question
3:15:47we're going to be solving and then also
3:15:49breaking it down into what are some
3:15:52deliverables I want from this so the
3:15:54deliverables are I want to identify the
3:15:56top 10 highest paying data analyst roles
3:15:59that are available remotely on top of
3:16:02this I want to focus on job postings
3:16:04with a salary so I want to remove
3:16:06anything that doesn't include a salary
3:16:08because obviously we don't know if it's
3:16:09top paying or not and finally I just put
3:16:11in there why we're doing this overall
3:16:13it's going to offer insights into our
3:16:15final problem question and answer we're
3:16:18going to get into of finding the most
3:16:20optimal skills and most optimal roles to
3:16:22be pursuing as a data analyst so let's
3:16:25start building our base query and we're
3:16:28going to start with these six columns
3:16:30first they target things like the job ID
3:16:33the title location what type of job job
3:16:36it is whether it's contract or whether
3:16:38it's full-time the salary what we hear
3:16:41about and then actually a job posting
3:16:43date additionally we want to do this
3:16:45from the most important table that we've
3:16:47been doing is that job posting fact
3:16:49table to make sure that we did this
3:16:51right as always as we're inating along
3:16:53I'll be running these queries to see
3:16:55that they're running correctly so
3:16:56pressing command D command D and then
3:16:59selecting what actual database you're
3:17:01going to be using for this if you have
3:17:02to uh navigate out of this I'm going to
3:17:04go through and set it back
3:17:07up and boom we're getting these results
3:17:09for the different job tiles job
3:17:11locations job schedule type job posted
3:17:13date okay this looking good so moving
3:17:16into that first bullet point we want to
3:17:18identify data analyst roles and those
3:17:21that are available remotely so for this
3:17:24we'll be using a where keyword and the
3:17:26first thing I'm going to specify is job
3:17:27title short equal to data analyst and
3:17:30then we want to do an and statement
3:17:32because we also want to get those that
3:17:33are available remotely so we have to
3:17:35specify a location so for location we'll
3:17:38specify it as anywhere so going to go
3:17:41ahead and run this Now command D command
3:17:43D and boom looks like we're getting all
3:17:45these data analysts r rolls anywhere and
3:17:48then oh we're getting a lot of null
3:17:49values for that salary year average
3:17:52column so that's probably what we're
3:17:53going to want to tackle next which is
3:17:56conveniently our second bullet point
3:17:57that we need to do we need to remove all
3:17:58those null values so we're going to do
3:18:00this add to this sare statement more by
3:18:02adding an and statement and then adding
3:18:04that salary year average equal to or
3:18:08sorry is not null so let's try this
3:18:12again command D make sure that we're
3:18:13working and yep we removed all those
3:18:16null values all right so the last things
3:18:18for this are we need to one we want to
3:18:21get the top 10 so how do we do that well
3:18:24first we know we want to do a limit
3:18:26statement we're going to limit it to the
3:18:28top 10 and the other thing is we
3:18:31actually need an order buy and for this
3:18:34we'll do the salary year average column
3:18:37again and remember this is initially or
3:18:40by default it's going to do ascending so
3:18:41we want to do descending running this
3:18:44query we're getting all the different
3:18:46job
3:18:47and then they're going from high to low
3:18:49and if you remember craw from earlier we
3:18:52had one that was around 650,000 and that
3:18:55was here now I'm noticing with this I
3:18:57actually I want to know one other thing
3:19:01uh with this and that's company because
3:19:02this is really great that it tells us
3:19:04these type of roles but I'm actually
3:19:06curious that not just these are remote
3:19:08and it looks like that most of these are
3:19:09all full-time but also what company so
3:19:12we're going to take this one a little
3:19:13bit of a step further I'm going go ahead
3:19:15and close this out I'm going to add
3:19:17after that from statement I'm going to
3:19:19add a left join and I'm going to specify
3:19:22that we want to connect this to that
3:19:24company dim table that has the company
3:19:26name so and both of these are connected
3:19:30on that Company ID column so I list the
3:19:33the table name in front of the column
3:19:35for both of these and then set them
3:19:37equal to each other to get that left
3:19:38join so now I can come in include a
3:19:40comma that column of the company name is
3:19:43name and we'll actually give it an alias
3:19:45uh to make sure it's not
3:19:47sort of like confusing of company name
3:19:49okay let's go ahead and run this bad boy
3:19:53and moving this over here so we can see
3:19:55it a little better we have all the
3:19:57different things oh and now we get to
3:19:59see the different companies and so we
3:20:02have things like meta so like a tech
3:20:04company AT&T also kind of a tech company
3:20:07Pinterest a tech company it looks like
3:20:09some other Healthcare type companies all
3:20:12right so this is a great start to our
3:20:14first problem we've identified the
3:20:16different jobs that we have and we've
3:20:17also been able to see what could we
3:20:19expect at some of the top paying roles
3:20:22so now I have a good frame of mind of
3:20:23what we should be targeting for and when
3:20:25you're working through this Feel Free as
3:20:26I mentioned before to modify this to
3:20:29your need whether you don't have to do
3:20:30data analyst you can do something data
3:20:32scientist data engineers and you can
3:20:33change up to that job location to where
3:20:35you are as we have locations from around
3:20:37the world inside of this data set all
3:20:40right for that see you in the next one
3:20:47all right so let's get into the second
3:20:49query that we're going to be focusing on
3:20:51and it's going to really be building a
3:20:53lot on the previous query that we just
3:20:56did in analyzing the top paying jobs for
3:20:59a data analyst specifically here's the
3:21:02results of that query again and I feel
3:21:05like it's still lacking I know I added
3:21:07that company name into it to dive into
3:21:09it deeper but I don't feel like that
3:21:12really answers the question that I'm
3:21:13truly trying to find out of what skills
3:21:15are also inil important so that's what
3:21:18we're going to be doing with this diving
3:21:19in to find out what are the top skills
3:21:22inside of each of these roles that are
3:21:24the main drivers behind why this salary
3:21:27yearly average value is so high since
3:21:30we're going to be building on this
3:21:31further I'm going to go ahead and copy
3:21:34this query on over into our new SQL file
3:21:38that're going to doing for this and I'm
3:21:39going to create a new file started with
3:21:42two and I'm going to name it top paying
3:21:44job skills and also make get a SQL file
3:21:47press enter and then I'm going to
3:21:49actually go ahead and paste that query
3:21:51in and additionally I added in what
3:21:54question we're going to be answering
3:21:55this which are around what skills
3:21:56required for the top hang roles we need
3:21:59to be really doing two main things one
3:22:02using that top 10 highest paying roles
3:22:05that we identified previously query and
3:22:07somehow combine it in and join it with
3:22:10all of our skills now this because we've
3:22:13previously built a query this is a
3:22:15perfect opportunity for something
3:22:16something like a subquery or CTE since
3:22:19this is a little bit more complex we're
3:22:21going to be going with a CTE for this
3:22:23once again let's make sure that this
3:22:24query Works while running it I'm going
3:22:27to move it over here and we can see from
3:22:29this there's a few columns we can
3:22:31actually remove from this specifically I
3:22:33don't really care about that job
3:22:34location or job schedule type and the
3:22:38job post to date isn't really providing
3:22:39much value either I'm going to go ahead
3:22:42and clean that up and move those out of
3:22:45here so let's start by making this into
3:22:47a CTE we do that with that with keyword
3:22:51and we'll specify this as the top paying
3:22:54jobs result set and we'll put the
3:22:56parentheses to enclose all of this in
3:22:59I'm actually going to select it all tab
3:23:01it over to make it have a nice little
3:23:04formatting and then put some closing
3:23:06parentheses right there make sure the CT
3:23:09is working properly I'm going to just go
3:23:11ahead and do a select all from this
3:23:14topang jobs query right here or top
3:23:17paying jobs result set and I'm going to
3:23:20go ahead and run it to make sure that
3:23:21it's running properly and looks like
3:23:23it's good if you're call from our
3:23:25diagram of the actual database itself
3:23:28we're going to need to connect two
3:23:30tables of this so not only the skills
3:23:32job dim table but also the skills dim
3:23:35because that's actually what's going to
3:23:36have the names inside of it but what
3:23:38join method are we going to be using to
3:23:41join this to our job postings fact table
3:23:44well remember from a join section there
3:23:46there's typically only two joins that
3:23:48we're going to do it's going to be a
3:23:49left join or an inner join now in this
3:23:52case that a table is going to be our job
3:23:54postings fact table and we really only
3:23:57care about skills associated with a
3:24:01salary so if there's a job that doesn't
3:24:04have any skills we don't really care
3:24:07about that too much so left join is not
3:24:09going to be applicable in this case
3:24:11instead we're going to be going with
3:24:12inner join so I'm going to start with an
3:24:14inner join on the skills job dim
3:24:17specifying for this one we want to
3:24:19connect the job ID from our top paying
3:24:21jobs result set with the skills job dim
3:24:24job ID additionally we want to do an in
3:24:27join on our skills dim table connecting
3:24:29both of these on their skill ID column
3:24:32now right now I have a select star for
3:24:35all the different columns we're just
3:24:36going to go ahead and run this to make
3:24:37sure that it works pressing command D
3:24:40command D and then moving this over here
3:24:43looks like we were able to join this all
3:24:45on but we have a a few columns in here
3:24:47that we don't really care about so we're
3:24:48going to remove those so first for that
3:24:51top paying jobs result set that we have
3:24:53here I want all of the columns from that
3:24:56and I could go ahead and write out all
3:24:57of these columns but instead I'm just
3:25:00going to do this of dot top paying jobs
3:25:03and then do a DOT and Then star and this
3:25:05is going to select all the columns from
3:25:07that table additionally I want the
3:25:10skills from this skills dim table okay
3:25:14let's go ahead and run this
3:25:17all right so it looks like the quer is
3:25:18working I do want to do one last thing
3:25:21before we dive in and that's make sure
3:25:22that we actually organize this by the
3:25:24salary so I'm going to start looking at
3:25:26those first so we're going to put a last
3:25:29statement of order by and specify that
3:25:33we're going to do this for salary year
3:25:35and the same thing putting in in that
3:25:36descending order this order buy may or
3:25:39may not be necessary depending on how it
3:25:41queries it may come already in an
3:25:44ordered fashion but you should just do
3:25:46this for best practices in case that
3:25:49this does happen to you and you want to
3:25:50get in the order and it doesn't anyway
3:25:51let's select all this and actually run
3:25:53this query and start analyzing it all
3:25:57right so this is pretty interesting I
3:25:58took a peek through all these different
3:26:00jobs and the skills that are requiring
3:26:03now overall there's a lot of postings
3:26:05and we're going to actually use some
3:26:06other tools to analyze this real quick
3:26:07but from what I'm seeing actually
3:26:09looking at it there's a very much a
3:26:11commonality of seeing SQL and python in
3:26:15almost every single one of these rules
3:26:18but with all these results with how much
3:26:19it is you'd really be better off using
3:26:22it in your analytical tool of choice
3:26:24could be something like Excel python
3:26:26whatever and you could do this by
3:26:28extracting this data out by Saving
3:26:31results and you can save the results as
3:26:32a CSV so I'm saving it inside my desktop
3:26:35and exporting it out anyway this portion
3:26:37is not required but I went ahead and
3:26:39gave chbt this CSV and told it these are
3:26:43the top 10 diis roles I found in job
3:26:46postings in 2023 can you analyze the
3:26:48skill column and display the insights
3:26:51and after I used some python code to go
3:26:52in and actually analyze it it found that
3:26:54that sqls leading and eight of the
3:26:57different roles python is in seven and
3:26:59Tableau is in six he even went a step
3:27:01further and had it make this
3:27:03visualization which shows a lot better
3:27:06visually what is going on here with all
3:27:09this data that we just collected anyway
3:27:11I'm going to go ahead and copy this
3:27:12Insight right here on the skill
3:27:15breakdown for that
3:27:16and actually go in and paste it down
3:27:19here
3:27:20underneath our query that we did
3:27:23additionally if we want to use these
3:27:25results later I'm going to go ahead also
3:27:27and click this icon here to open the
3:27:29results as a Json file I'm going select
3:27:32all of this by pressing command a and
3:27:34then coming underneath here making sure
3:27:36I'm inside that comment right there
3:27:37actually pasting all these different
3:27:39results so if we wanted to we can go
3:27:41back later and verify what results I had
3:27:43from this query anyway just be clear
3:27:46this chat gbt portion completely
3:27:48optional I just wanted to dive into it
3:27:50further and pasting the results
3:27:51completely optional the main portion is
3:27:53we wanted to make sure that we have this
3:27:55query right here as this was solving
3:27:58what we were trying to find all right
3:28:00now it's your turn to give it a try I'm
3:28:01curious to see from your perspective
3:28:03especially if you're using something
3:28:04different from data analyst or a remote
3:28:06location how those skills are different
3:28:09well with that see you in the next one
3:28:16all right in this portion we're going to
3:28:17be diving into what are the most in
3:28:19demand skills particularly for me I'm be
3:28:22looking at data analyst now if we look
3:28:24back to what question are be doing for
3:28:26this we've already answered the first
3:28:28two already we're now on to the third
3:28:30and this one actually we answered
3:28:33previously and well you can actually go
3:28:36ahead and use those results previously
3:28:38or I'm going to actually work at a
3:28:40different method specifically in problem
3:28:43number seven we found the count of the
3:28:45number number of remote job postings per
3:28:47skill and whenever you run this query
3:28:50command e command e we found that for
3:28:53data analyst and also for work for home
3:28:55these were the top skills with surprise
3:28:57surprise SQL and also python topping
3:29:00this list anyway if you have these query
3:29:03results saved you can go ahead probably
3:29:05just copy and paste this then move on to
3:29:07this next section of query 4 but I'm
3:29:09going to go ahead now and work this
3:29:11problem slightly different is this one
3:29:14right here how we have a written is a
3:29:17little bit longer than I'd like it for a
3:29:19query to be I want it to be short and
3:29:20simple as possible because we get into
3:29:22bigger data sets it's going to take more
3:29:25time to run these type so let's try to
3:29:27rewrite this so I'm going to start by
3:29:29creating a new file I'm going to name it
3:29:31top demanded skills and dsql all right
3:29:34so for this one we're going to be
3:29:35focusing on what are the most in demand
3:29:37skills and to do this we only have a few
3:29:40steps for this we're going to be joining
3:29:41our job posting tables to our skill
3:29:44tables like we previously did we're
3:29:46going to reuse some code from that one
3:29:48and then from there we're going to limit
3:29:49it down to those top five skills and for
3:29:51this one I want to focus on all job
3:29:54postings not just those remote ones
3:29:56really just see if there's any
3:29:57difference anyway I'm going to go ahead
3:29:59and open up that last one that we did
3:30:01previously and I'm going to go ahead and
3:30:03copy this on over and then mainly
3:30:07pasting in that inner join this is
3:30:11really a lot to type in so I'm going to
3:30:13I'm going to just streamline this a
3:30:14little bit so we'll do it a simple
3:30:16select star just to start out from this
3:30:19and we'll do from the job postings fact
3:30:23table and we're going to be doing once
3:30:24again that injin to that skills table to
3:30:27the skills job dim table and then
3:30:29finally to the skills dim table let's
3:30:30just go ahead and run this and see if
3:30:32this actually works and I already made
3:30:35my first there as I didn't rename the
3:30:38table right here it I still had top
3:30:40paying jobs uh for that result set
3:30:43anyway let's actually try to run this
3:30:44again hopefully it works
3:30:46all right this is running for a little
3:30:48bit too long of a time and I understand
3:30:50that cuz we're combining all these
3:30:51different tables together we're going to
3:30:53be limiting anyway these uh to find the
3:30:56top five jobs anyway so I'm going to go
3:30:58ahead and just throw this limit five
3:30:59statement here to speed up this query I
3:31:02was waiting about a minute and it wasn't
3:31:04uh loading all right lot quicker
3:31:06whenever I do that limit five in there
3:31:08don't do what I just did there and it
3:31:10looks like it's combining all these
3:31:11different tables I can scroll the way to
3:31:12end and see that the skills got combined
3:31:14into this so remember what we're trying
3:31:16to do we're trying to find what are the
3:31:18most in demand skills basically we're
3:31:21going to find a aggregation of the sum
3:31:24of skills so we're going to need to
3:31:26provide a count in order to find out how
3:31:29many SQL entries are how many python
3:31:31Excel and so on so first thing I'm going
3:31:33to specify is that skills column because
3:31:36I want to return that and then we're
3:31:38going to do that aggregation method
3:31:39we're going to be doing a count for this
3:31:42let's use the job ID for this and I'll
3:31:45just put in sales job dim. job ID and
3:31:48we'll put this as the demand count now
3:31:52remember anytime we do some sort of
3:31:54aggregation we have to do a group buy so
3:31:57for this we'll do the group buy and we
3:31:59obviously want to group buy the skills
3:32:01let's go ahead and run this to see where
3:32:03we're at right this make sure it's
3:32:04running properly okay this is looks like
3:32:07it's tabulating right now right now
3:32:09we're not having any type of sorting so
3:32:11that's what we're going to need to fix
3:32:12next but we can at least see that we're
3:32:13getting those demand counts of those
3:32:15different skills looks like they're
3:32:17actually doing this in alphabetical
3:32:18order so let's go ahead and put that
3:32:20order by in there now so order by and
3:32:24we're going to order it by the demand
3:32:27count run this again command e command
3:32:30e and obviously we got to do it
3:32:33descending run this again all right so
3:32:35now we're getting all the skills right
3:32:37now and it looks like sequels topping
3:32:39the list at around 380,000 python very
3:32:42close by now remember if we go back to
3:32:45our original question we want to find
3:32:46out what are the most in demand skills
3:32:48for data analyst so we need to modify
3:32:51this and insert in a we statement to
3:32:54filter by this so let's use that weer
3:32:56keyword to filter this by we're going to
3:32:58be using that job title short column and
3:33:02then specifying that we want to filter
3:33:03it by data analyst so selecting it all
3:33:07and running it command e command e boom
3:33:10now we got it and it looks like we have
3:33:13SQL Excel python tableau and powerbi
3:33:16Topping the list now I am curious to see
3:33:19how it compares to if it was only remote
3:33:22jobs so I'm going to add I'm take this a
3:33:24little bit step further just to compare
3:33:25it I'm going to add the and statement
3:33:27for the wear specify job work from home
3:33:30as true ring this command e command e we
3:33:34can see when we compare these we still
3:33:36have so on the left hand side we have
3:33:38those that table that is not uh that is
3:33:41that allows any type of work whereas the
3:33:44one on the right is only allowing remote
3:33:46work or work from home and the general
3:33:49trend is the same with SQL Excel python
3:33:52Tableau powerbi and it looks like it's
3:33:54very much in the same proportion so
3:33:56there's not a lot of difference between
3:33:57the two all right now it's your turn to
3:33:59give it a try feel free to once again
3:34:01modify this for your specific role but
3:34:03also you don't have to stick to this
3:34:05thing where I just did remote you can do
3:34:07your specific location or really
3:34:09anything particular to you that you want
3:34:11to filter down further on with that see
3:34:13you in the next one
3:34:19all right so let's get into the second
3:34:20last skill of analyzing what are the top
3:34:24skills based on salary now this is all
3:34:27in progression to learning what is the
3:34:29most optimal skill if you recall earlier
3:34:32we've already analyzed to find what were
3:34:34the most highest paying jobs with one at
3:34:36around 650,000 for all those high paying
3:34:39jobs that we found we then dived into
3:34:41the skills themselves and we found
3:34:43popular tools like SQL and python are
3:34:46common in a lot of these rules and so we
3:34:48dived in further to look at how popular
3:34:50they were not just among the top paying
3:34:52jobs but also all jobs and there was
3:34:55still a similar trend of that SQL and
3:34:57Python and even things like Excel
3:34:58Tableau and powerbi for data analyst so
3:35:01our ultimate goal which will be in the
3:35:03next query of what is the most optimal
3:35:04skill looking at what is not only high
3:35:07paying but also highly in demand we need
3:35:10to actually answer that question of what
3:35:12is a high-paying skill so for this query
3:35:15we're going to be looking at what is the
3:35:18average salary associated with a skill
3:35:22specifically fine tuning this for me for
3:35:24data analyst positions and we want to
3:35:27make sure this data actually does
3:35:28include salary data we're going to
3:35:29exclude any null values and then finally
3:35:32we're going to do this initially
3:35:34regardless of any location but then I'm
3:35:35also going to modify it to me for my
3:35:38remote case where I want to look for
3:35:39remote jobs and what would I expect it
3:35:41to be so back at vs code we're going to
3:35:43start with a new blank SQL document that
3:35:45we we working from and have the problem
3:35:47outlined up at the top so where are we
3:35:50going to start with this one so this
3:35:51one's very similar to the last query in
3:35:54fact because we need the names of the
3:35:56skills from that skills dim table and
3:35:59also we need the salary data from the
3:36:02job postings fact table and previously
3:36:05we did a count of values of this column
3:36:08but now we just need to do an
3:36:09aggregation method basically doing the
3:36:11average of these average salaries so
3:36:14navigating back to our third query that
3:36:17we did I'm actually go ahead and copy
3:36:20all of this as a lot of this is going to
3:36:21be reusable and paste that in right here
3:36:24so if you remember from this one we were
3:36:26doing the count of the skills I'm going
3:36:28to go ahead and actually just execute it
3:36:30to show this was doing account of the
3:36:32skills basically we want a very similar
3:36:34thing but we want the average salary
3:36:36right here so for the time being I'm
3:36:38going to remove this and we have the
3:36:41from job postings fact our in Joins
3:36:44where data analyst is true remember
3:36:46we're going to maintain this to look
3:36:48regardless location so I'm just going to
3:36:50put a comment in here right now and move
3:36:52this and right before this so we'll just
3:36:54move it out of the way for right now but
3:36:56we can put it back whenever we want to
3:36:58analyze it for emote jobs from there we
3:37:00have group by skills and then an order
3:37:02bu we'll have to actually update this as
3:37:04well um I'm going to actually change
3:37:07this limit to a little bit heftier value
3:37:10of just 25 so like I said the lot of
3:37:12this core query is already good enough
3:37:15and you could feel free to start over
3:37:17again with it but I really don't like to
3:37:19repeat myself so the first thing I'm
3:37:21going to do with modifying this query is
3:37:22previously we had the count up here I'm
3:37:24going to add in an average function to
3:37:27actually average that salary and I'll
3:37:29specify the column of salary year
3:37:32average now because we have an
3:37:33aggregation as always we need to group
3:37:36by the appropriate thing in this case we
3:37:38do want to group by the skills so we'll
3:37:40keep this as is and remember from our
3:37:42core things we want to focus on roles
3:37:44with specified salary so I'm going to
3:37:46add an and keyword right here and then
3:37:48for this I'm going to remove any null
3:37:51values that are in that salary column
3:37:54and also help speed up this query a lot
3:37:56faster so I specify salary year average
3:37:59is not null and the last thing to do is
3:38:01we have this order bu here that needs to
3:38:03be filled in so I'm going to go ahead
3:38:05and just put this well we need to assign
3:38:07an alias for this so I'm going to do an
3:38:10as and name this average salary pretty
3:38:14original and then whenever we go down
3:38:16here to the order by we'll order it by
3:38:19that average salary okay let's actually
3:38:22run this and see if it works fingers
3:38:24cross oh and it works okay and okay like
3:38:28usual I messed up and uh the order by I
3:38:31did it in ascending because I did the
3:38:33default but we at least have all the
3:38:35values here okay and it looks like it's
3:38:38working correctly also I'm noticing
3:38:41these decimal places I don't really
3:38:43there's a little bit too much data for
3:38:45for me so first thing to fix the order
3:38:47bu I'm going to change this to
3:38:49descending run this
3:38:52again and then let's clean up this
3:38:54salary so there's a function we didn't
3:38:55talk about yet and that is the round
3:38:59function and we can put that outside
3:39:01that average function and with round we
3:39:04need to actually specify not only the
3:39:07column or the aggregation of interest
3:39:09but then also after this we have to use
3:39:11a comma and we have to specify the
3:39:13amount of digits want to round to I
3:39:17don't really care about any of these
3:39:19digits um so I'm just going to make this
3:39:22zero you could make it two for like two
3:39:24c uh for um down to the 100th place
3:39:27anyway let's go ahead and run this make
3:39:29sure this works okay we have all our
3:39:32values now all right so this is the top
3:39:3425 and looking through it unfortunately
3:39:37Python and SQL aren't topping the list
3:39:40but it looks like it's more focused
3:39:43specifically for data analyst on web
3:39:46development tools so like things like
3:39:49terraform um and then more Niche tools
3:39:52like data robot so this does make sense
3:39:56anyway just out of curiosity I want to
3:39:57see how this Compares I'm going to close
3:39:59out these other windows I want to see
3:40:01how this Compares for remote work and
3:40:05I'm going to remove this comment right
3:40:07here and then run this section
3:40:10again and looking at this for remote
3:40:13jobs there's a lot more familiar names
3:40:15that I'm seeing on here such is Jupiter
3:40:17that's like a notebook where you can run
3:40:19Python and probably the most surprising
3:40:22of all or coolest of all is postgress at
3:40:24least made the top 25 anyway similar to
3:40:26what we did in query 2 because this is a
3:40:28lot of text Data here a lot of analysis
3:40:31of what is actually going on or what all
3:40:34these different tools are used for I
3:40:36actually went up here and select this
3:40:38floppy disc right here and had copy
3:40:40results as Json to clipboard and I've
3:40:43been having an ongoing conversation with
3:40:45gbt got a lot of the results from this
3:40:47and I just said hey here are the top
3:40:48paying skills for D analyst the top 25
3:40:51can you provide some quick insights into
3:40:52some Trends into these top paying jobs
3:40:54and then providing all this Json value
3:40:56anyway it provided three insights into
3:40:59what are these top paying roles
3:41:01consisting of for data analyst
3:41:03specifically big data and ml skills are
3:41:06a high priority next up comes software
3:41:08development and deployment and then
3:41:10finally cloud computing so a lot of
3:41:13specialized skills that require not only
3:41:16SQL but also python anyway I went ahead
3:41:18and copi and pasted this into underneath
3:41:21this query to basically break down what
3:41:23we found out in this query all right
3:41:25next one we're going to be diving into
3:41:27what is the most optimal skill so you
3:41:29haven't done this one already do it for
3:41:30you make sure you're adapting it to your
3:41:32need and then we'll be diving into the
3:41:34final query which I'm super excited to
3:41:36get into all right see you in the next
3:41:42one all right finally moving into what
3:41:44is the most optimal skill I should be
3:41:47focusing on as a data analyst or remote
3:41:50jobs now if you recall from our third
3:41:52query we found out what is the demand
3:41:55for certain skills granted we only
3:41:57limited to Five results shown but
3:41:59basically we can get all the different
3:42:00results or the demands for the skills in
3:42:02the last query we analyzed the skills
3:42:04from a different perspective and found
3:42:06what was the average salary for each of
3:42:09those skills for a data analyst so
3:42:11honestly the easiest solution to
3:42:14basically build on the code that we've
3:42:15already built is to use a CTE basically
3:42:19a CTE for query 4 a CTE for query 3 and
3:42:21then combine these two results together
3:42:24on something like the skill ID so inside
3:42:26VSS code I'm going to create a new file
3:42:28called optimal skills give it that
3:42:30number CL five the fifth query and up at
3:42:33the top of the file I put in what we're
3:42:35actually trying to solve for what are
3:42:36the most optimal skills to learn and
3:42:38we're going to be focus on high demand
3:42:40which we've already calculated and then
3:42:42High salaries which is already
3:42:44calculated as well in query 4 so the
3:42:46first thing I'm going to do is going to
3:42:47go back and get the query from query 3
3:42:51I'm just going to copy that all right
3:42:52here and then paste that in remember
3:42:54we're going to want to put this inside
3:42:56of a CTE we're going to be putting both
3:42:58three and four inside of a CTE so I'm
3:43:00going to title this with skills demand
3:43:04as and then start that CTE right there
3:43:07and then from there tab this over remove
3:43:10that semicolon and close the parentheses
3:43:14Okay so so I have the skills demand
3:43:17coming in its own result set using the
3:43:19CTE the next thing I want to bring in is
3:43:22the number 4 query that we have so I'm
3:43:24going to copy this one also and paste it
3:43:27over here I'll name this one average
3:43:31salary for the temporary result set and
3:43:34then start a parenthesis here tab this
3:43:37over remove that parenthesis and then
3:43:40from there add in another closing
3:43:42parentheses right here okay so we have
3:43:44our two CTE located right here so I'm
3:43:47going go ahead and close out of this and
3:43:49close this side window we have what we
3:43:50need for this we now need to move into
3:43:53actually combining these and if you look
3:43:57at it we could technically combine it on
3:44:01the skills because each of those skills
3:44:04are unique if you will but that's not
3:44:07really best practice we need to
3:44:09typically whenever you want to combine
3:44:11anything you want to combine it on the
3:44:13actual um the key at itself the either
3:44:16primary or foreign key so we're going to
3:44:17be using the skills ID for this so I'm
3:44:19going to specify skill ID here and then
3:44:23also skill ID here as well now since
3:44:26we're going to be combining these I
3:44:28don't want to limit this to five and I
3:44:30don't want to limit this one to 25 I
3:44:32want to combine all of them that way
3:44:34they do the agation also I want to speed
3:44:36up this query I don't care about an
3:44:38order by so I'm going to remove that in
3:44:40this case and similarly I'm going to
3:44:42remove that as well here and then the
3:44:44last thing I'm going to modify is the
3:44:46group bu so like I said previously we're
3:44:48going to be connecting these two tables
3:44:50on the skill ID we can do groupy skills
3:44:54it's just not bre practice so we're
3:44:56going to change that to the group ey of
3:44:58skill ID same thing for the groupy down
3:45:01here on skill ID so that way we know for
3:45:04sure whenever we do this that we're
3:45:06actually aggregating it correctly the
3:45:08last minor thing to note is for my
3:45:10things we're going to concentrate on
3:45:12remote positions with specified salary
3:45:15so previously for our query 3 we were
3:45:19looking at all the jobs and not
3:45:21necessarily those that have or are
3:45:24missing a salary value so I'm going to
3:45:26select this from query 4 selecting
3:45:29salary year average is not null and I'm
3:45:31going to also insert it up here as well
3:45:34anyway now we have both of these CTE
3:45:37built we need to actually move into
3:45:39combining them so we're going to move
3:45:41into our select statement we're going to
3:45:42first identify the skill ID we have this
3:45:44in both
3:45:45so we're going to just specify the first
3:45:47one of skill demand. skill ID next we
3:45:51need to identify the skills so we'll do
3:45:54similarly with this specifying the skill
3:45:57then we need to bring in from query 3
3:45:59that demand count along with that
3:46:02average salary from query 4 so now when
3:46:05you move into combining these we'll use
3:46:07a from statement specifying the skills
3:46:11demand table or temporary result set and
3:46:14then doing an inner join because we only
3:46:17care about what exists in both of these
3:46:19tables and we're doing this with the
3:46:21average salary temporary result set and
3:46:24both of these are combined using the
3:46:27skills ID so this has the bulk of what
3:46:29we need already let's go ahead and run
3:46:32this because it's already been too long
3:46:34already that I could have made a mistake
3:46:36and not caught it so command D command D
3:46:39and I did run into an error and it
3:46:40resolves around this with statement
3:46:42right here so anytime we're doing
3:46:44multiple CTE you actually group these
3:46:48together so this with keyword transfers
3:46:51this into a temporary result set of the
3:46:53skills demand and then you put a comma
3:46:55and then similarly we do this average
3:46:56salary so we're D two CTS right here so
3:46:59going ahead and running this command e
3:47:01command e and when I added in those
3:47:03skill IDs it was ambiguous sometimes
3:47:06you'll notice in if you bought the quz
3:47:08notes and certificates I'll just include
3:47:10all the different table names in front
3:47:12of these and that's just so you resolve
3:47:15this ambiguity and don't run into these
3:47:17different errors like I'm running into
3:47:19right now so let's try this one more
3:47:23time to see actually if this works
3:47:24pressing command e command D and spoke
3:47:27too soon again I didn't get all the
3:47:29skill IDs again so we got another error
3:47:32and saying skills must appear in the
3:47:33group by Clause so I'm going go ahead
3:47:36actually we already have skills up here
3:47:38with our count and that one should be
3:47:40fine so I'm going to remove it here
3:47:44let's see if if this works all right so
3:47:46I've been troubleshooting this skills
3:47:49must appear in the group ey and I know
3:47:50it can be done and anyway I come to
3:47:54realize that compared to our I got to
3:47:56close out this so we can actually see it
3:47:58so in our first CT We Have Skills ID and
3:48:01also skills we want that skills to be in
3:48:03there right because we're going to be
3:48:05using this and actually displaying this
3:48:07in our final table and I don't want to
3:48:08do another left joint down the road
3:48:10anyway I was specifying the wrong table
3:48:12earlier it should be skills job dim and
3:48:15in this case I specified now skills di
3:48:17to clear up that ambiguity and so now we
3:48:21should actually get this to work command
3:48:25D command D okay and finally hopefully
3:48:28the last error I'm now getting this
3:48:29relation average does not exist I'm like
3:48:32I know I can do average in here I got a
3:48:34dang typo right here on the inner join
3:48:37and I specify average space salary so
3:48:41wasn't necessarily
3:48:42working oh my gosh this is on me right
3:48:45now one other typo I don't have skills
3:48:51properly in there all right so we're
3:48:54finally through that after quite a bit
3:48:56of troubleshooting but honestly that's
3:48:58what I find myself doing from time to
3:49:00time with SLE queries especially when
3:49:02combining it sometimes it looks like
3:49:04it's easy just to combine two queries
3:49:06and put it into a final one and you run
3:49:09up to a lot of hiccups along the way so
3:49:11I wanted to include that to show that
3:49:13sometimes you're going to have to go
3:49:13through these troubleshoo shooting steps
3:49:15with this anyway we have our results
3:49:18back I'm going to move this over to this
3:49:19window and we can see from it we have
3:49:22our skills our demand count and then the
3:49:25average salary associated with it right
3:49:28now it's not really in a particular
3:49:30order so we need to clean this up it
3:49:32looks like it's ordered by skill ID so
3:49:34I'm going go down to the bottom of the
3:49:36query after the inter join and put in an
3:49:38order by and really I care about demand
3:49:42first so we're going to put in demand
3:49:44and count put in this descending order
3:49:48and also you can put more than one
3:49:50things to order by so in case ever they
3:49:52have the same value we'll go to the
3:49:54second value to thus order that one so
3:49:56in this case we're going to do the
3:49:58average salary and then also do this in
3:50:00descending order and finally we're going
3:50:02to limit this similar to last time of
3:50:04just 25 values so I'm going to go ahead
3:50:07and run this query now to get this all
3:50:11right so here we have it moving on over
3:50:14here here we have our results and right
3:50:18now it's ordered by that demand count
3:50:20but then we can still see based on what
3:50:22we know about some of the higher
3:50:23salaries for skills upwards of 150 to
3:50:27200,000 we're seeing these high demand
3:50:29skills are around 100,000 so I also want
3:50:32to see this this table ordered from the
3:50:35average salary perspective first so I'm
3:50:37going move this around with average
3:50:39salary up before the demand count and
3:50:42then run this aggregation method again
3:50:45command e command e and this time as we
3:50:47can see whenever we do it from this
3:50:50perspective we are getting these higher
3:50:52salaries right here but this demand
3:50:55count is so low for these higher salary
3:50:59jobs based on what I'm seeing from that
3:51:01previous table I'm wondering if we could
3:51:04do a wear Clause to limitate by the
3:51:07demand count to make sure that we still
3:51:09have these high-paying jobs in there and
3:51:11ordering it similarly but have a demand
3:51:13count grade than 10 so I'm going to add
3:51:16in this of where and then specify the
3:51:20demand count greater than 10 okay I'm
3:51:24going to go ahead and run this new query
3:51:26to see what it looks like for this one
3:51:29all right and I'm liking this one a lot
3:51:30better it's a lot more represen istic of
3:51:33what we would expect because there's
3:51:34actually values quite AET bit of job
3:51:36data values for each of these so just
3:51:39analyzing this roughly doing a scan
3:51:41through it I can see that once again
3:51:44we're seeing that one Cloud tools and
3:51:47specifically cloud-based databases are
3:51:51some of the highest in here and then
3:51:53down here near the middle of this list
3:51:55of only 25 we're seeing uh programming
3:51:58languages like Python and R now although
3:52:01this was a great way to demonstrate the
3:52:03use of CTE I would recommend actually
3:52:06making this more concise and so that's
3:52:08what I did here I went through and
3:52:10actually rewrote this entire query
3:52:13keeping it uh pretty concise and all it
3:52:15basically did was actually combine in
3:52:17all those select statements into one
3:52:21that were previously spread out among
3:52:23those two CTS and I still had the same
3:52:25wear group eyes and ordering the only
3:52:28thing I'll add is that little last
3:52:29caveat at the end where I wanted to have
3:52:31the demand count greater than 10 which
3:52:34is just an arbitrary number you can't
3:52:36put an aggregation method inside of a we
3:52:39so I had to do a having keyword here to
3:52:42actually do that anyway running this all
3:52:44I can do command e command e and
3:52:47comparing it to our previous table
3:52:49there's our previous tier table on the
3:52:50left and then our new table it's the
3:52:52exact same results anyway mainly just
3:52:54show you this that there's multiple ways
3:52:56to actually go through and analyze and
3:52:59what can be done and what can't be done
3:53:01all right next thing we're doing is
3:53:02going to be actually packaging up all of
3:53:04our project that we have right here
3:53:06generating a readme and then actually
3:53:08going out and sharing this project on
3:53:11How I would go about doing this all
3:53:13right with that I'll see in the next
3:53:19one all right so now it's time to
3:53:21actually showcase all the different work
3:53:23we did and doing this by uploading this
3:53:25into GitHub but before we can do that we
3:53:28actually need to streamline and put all
3:53:31of our analysis into one document so
3:53:33inside of vs code right now I have a few
3:53:35different folders you may not have this
3:53:37Advanced SQL that is all the problems
3:53:39that we worked dur in the advanced SQL
3:53:40section but you should at least have
3:53:42this project SQL folder which has all
3:53:44your different SQL files that we worked
3:53:46on previously along with that SQL load
3:53:50table on how we actually loaded the data
3:53:52into the database so let's actually
3:53:54upload this all on GitHub and see how it
3:53:57looks right now and going into on the
3:53:59activity bar on Source control see that
3:54:01it has all of our new files I renamed
3:54:03one of my files why it's scratched out
3:54:05here and I give it this commit message
3:54:07of upload SQL files and then press
3:54:10commit and then I want to sync changes
3:54:12navigating into GitHub can see my
3:54:14projects located right here at SQL
3:54:16project. job analysis and this right
3:54:18here is what everybody's going to be
3:54:20seeing when they first get to the
3:54:22project and overall there's not a lot
3:54:25here as you can see that read me that
3:54:26created there's nothing really detailing
3:54:28it and you have to navigate into all
3:54:30these different folders to even see what
3:54:33is going on here like how do you even
3:54:34navigate it so here's an example I want
3:54:36to work off of I have a course on chat
3:54:38GPT for data analytics and then you go
3:54:40through and use chat GPT to generate
3:54:43code basically gener a project anyway in
3:54:46this project here I have for the read me
3:54:49it details all the different analysis we
3:54:52do for that project there going through
3:54:55describing it all and describing all the
3:54:56different code that's actually located
3:54:58inside the repository so that's what
3:55:00we're going to be doing is building out
3:55:02a readme in order to handle this so for
3:55:05this readme file I want it structured in
3:55:07a logical manner to actually go through
3:55:10and so somebody can read it and actually
3:55:12follow along in all the analysis we did
3:55:14so we're going to start with simple
3:55:15things like an introduction a background
3:55:17and then tools I used from there we're
3:55:18going to move into the analysis the
3:55:20analysis will be the bulk of the session
3:55:22of actually capturing all five of those
3:55:24different queries that we analyzed and
3:55:26then finally we're going to wrap it up
3:55:27with what you learned or what I learned
3:55:30during the analysis and then any final
3:55:32conclusions that we came to or Drew as
3:55:35far as insights so back inside of vs
3:55:38code let's actually get to work I have
3:55:40the read me here open and I'm going to
3:55:42drop in all those different sections
3:55:44that we're going to be working on for
3:55:46this now conveniently inside of here
3:55:48this is this is a text version right
3:55:50here so you can insert all this kind of
3:55:52text right there but we're going to want
3:55:54to open on the right hand side this open
3:55:56preview and this allows us to see what
3:55:59it's going to look like in the markdown
3:56:01version or the final version whenever
3:56:03we're actually inside of giip Hub
3:56:05specifically if I navigate back to that
3:56:07chat gbt one and I navigate here I can
3:56:09see like hey it's all formatted stuff
3:56:11but if I actually go inside of the read
3:56:13me itself and look at the code basically
3:56:18it's just a bunch of text and so there's
3:56:21certain format that you need to do in
3:56:23order to get things like headers here or
3:56:26links or whatnot let's go over a few
3:56:28brief ones that you should know about as
3:56:30we're building this out so the first
3:56:32thing is headers I actually all of these
3:56:35need to be headers and what do I mean by
3:56:37that I'm put a little pound sign right
3:56:39here in a space whenever you use that
3:56:41that is a header one I can also do two
3:56:44pound signs to make a header two or
3:56:46header three whatnot for each of these
3:56:48I'm going to make them individual header
3:56:51ones all right so I have all the
3:56:52different headers now I'm going to be
3:56:54going through and actually filling this
3:56:55out let's start with that introduction
3:56:57section first so I make this intro of
3:56:59dive into the data job market focusing
3:57:01on data analyst roles this project
3:57:03explores top paying jobs and demand
3:57:05skills and where high demand meets High
3:57:07salary in data analytics and then I give
3:57:10it this of SQL queries check them out
3:57:13more here so need to insert a link so
3:57:15for this I want to provide a basically a
3:57:18clickable link so I know that it's going
3:57:21to be going to this Advanced sorry it's
3:57:23going to be going to this project SQL
3:57:25folder when they navigate to it I want
3:57:27them to see this so I'm going to start
3:57:29with some brackets and then from there
3:57:30actually Define the folder that we're
3:57:32going to and that's project SQL folder
3:57:35and that's just what's going to be
3:57:36actual viewable then from there I'm
3:57:37going to put a parentheses next to it as
3:57:39you can notice right here well let me
3:57:40close this out right here as you can
3:57:42notice it's now again clickable but it's
3:57:45not going anywhere so we want to put
3:57:47some sort of Link inside of it so I
3:57:50press forward slash and then inside of
3:57:53here conveniently all the different
3:57:54folders pop up that I want to link to
3:57:57link to I put project SQL and now it
3:58:00should be good so whenever I click this
3:58:02it should want to navigate me over as
3:58:04you're seeing here but whenever I have
3:58:06this in GitHub it will actually navigate
3:58:08them over to that folder next up is
3:58:11background and I'm not going to bore you
3:58:13by reading through all this but
3:58:14basically I went through summarized what
3:58:16the background for the course was also
3:58:17put a link to the course and then from
3:58:20there defined the five questions that I
3:58:23wanted to be diving into that we did in
3:58:25our final project analysis next up is
3:58:28tools I used and I wanted to summarize
3:58:31four or five main tools of SQL postgress
3:58:35vs code and then also git and GitHub and
3:58:37I put a little disclaimer across each
3:58:39I'm noticing right now so you can use
3:58:41something like a Tac or a dash to get it
3:58:44in bullet point within here but I'm
3:58:46still having a hard time actually
3:58:47reading what are these different tools I
3:58:49have I can actually bold these different
3:58:52values by putting double asteris before
3:58:56and after any value it then goes ahead
3:58:58and bold cases it all right next up is
3:59:00the analysis we're going to start first
3:59:03with just putting a blanket statement of
3:59:04what we're actually doing here that
3:59:06we're aiming at investigating specific
3:59:08aspects this is can to be broken up into
3:59:09multiple sections so the first one I
3:59:11used a header three and then specified
3:59:13hey it's number one one one that we did
3:59:15of top paying data l jobs by the way
3:59:17we're not going to go through all five
3:59:18we're going to do one of these from
3:59:20there I'm going to put a quick summary
3:59:21in and so for this one it was just hey
3:59:24to identify the highest paying roles and
3:59:26then what I filtered it by and
3:59:27everything like that now I want to
3:59:29showcase my code from this query so that
3:59:31way they don't necessarily have to go
3:59:33back to this file right here and
3:59:35actually see it instead I'm going to
3:59:37copy this code right here I'm going to
3:59:39close this panel we're get a little
3:59:40smoed and I'm going to put it into here
3:59:43the problem is if you look at it it
3:59:45looks like a hot mess over here so what
3:59:47we can actually do is format this as a
3:59:49code block so for this I'm going to put
3:59:51three back ticks one at the beginning
3:59:54and then one at the end and so if you
3:59:57look inside of here now we can see that
3:59:59this is all formatted correctly and it
4:00:01actually looks like SQL code
4:00:03additionally it's good practice inside
4:00:05of here to specify what language this is
4:00:09and so inside of this markdown file
4:00:11right here it actually if you notice
4:00:13that it form formatted it for what it is
4:00:15here and similarly it's doing that here
4:00:18as well with this color highlighting so
4:00:19making it a lot easier to read so
4:00:21definitely recommend doing that
4:00:22following this clo boock I wanted to top
4:00:25this all off with the findings so I put
4:00:27in here hey here's the breakdown of the
4:00:29top. UN list jobs in 2023 and then had
4:00:32three different insights of the results
4:00:34that we had found earlier now if you
4:00:36recall from earlier during that second
4:00:38query when we dived into it I had chat
4:00:40gbt visualized the data that was
4:00:43provided well I also had Chad gbt
4:00:46visualize the results from this as well
4:00:49to showcase the different salaries
4:00:52associated with the top 10 jobs anyway I
4:00:55want to show some of these
4:00:56visualizations inside of here to better
4:00:59convey some of the analysis that we did
4:01:01here so anytime we need to include these
4:01:04type of images inside of marktown we
4:01:06actually have to include the images
4:01:08inside of the project file itself so I'm
4:01:11going to create a new folder called
4:01:13assets
4:01:14now this option of including an image
4:01:17inside of here is completely optional
4:01:19whether you want to do it or not but it
4:01:20shows even more functionality of how to
4:01:22use markdown anyway I can take that
4:01:25folder or take that file itself and then
4:01:28go ahead and drop it into here and so we
4:01:30can see it's right here in top hang
4:01:33roles I'm going to go ahead and then go
4:01:36and select copy relative path now in
4:01:39order to display an image in markdown
4:01:41you need to use this nomenclature here
4:01:43where you're going to have an
4:01:44exclamation point and then brackets
4:01:46around some sort of alternate text for
4:01:48this I'm going to name this top paying
4:01:51rules next we need to do that URL so I
4:01:54copied that relative path I'm going to
4:01:56go ahead and paste it in you notice
4:01:59whenever I do that now it's accessing it
4:02:01right here from our folder so then I
4:02:03just end this off with a little comment
4:02:05at the end in italics that hey this is
4:02:08the bar graph visualizing these results
4:02:10and also that chat GPT generated this
4:02:12graph for my SQL query results all right
4:02:14so now I'm going to repeat this for all
4:02:17five of those other areas as well all
4:02:20right so I've gone through and put all
4:02:22this in this took me about uh like 30
4:02:24minutes to go through and do right so
4:02:27here's what I did very similar to that
4:02:28first section as I continue the same
4:02:31layout of just outlining it and then
4:02:33providing an a quick snapshot of what
4:02:35the breakdown is and then if I had a
4:02:37graph I included it now when I got down
4:02:40here into queries 3 4 and 5 I found
4:02:43visualization were less uh helpful for
4:02:46this so I started inserting tables did
4:02:48that for both four and then also for
4:02:51five proving that final analysis now
4:02:53tables themselves are pretty easy to put
4:02:56into markdown you can put it in with
4:02:58this format right here I found that
4:03:00actually just copying and pasting the
4:03:03results from here in vs code into
4:03:05something like Chachi BT and having to
4:03:07get format for me saves a lot of time so
4:03:10that's what I did for a lot of this so
4:03:13last two sections that I'm going to
4:03:15include the one is what I learned this
4:03:18really going to be dependent on your
4:03:19situation where you are in your Learning
4:03:21Journey I just put in these three
4:03:23examples of hey we worked on querying
4:03:26data aggregation and then also just
4:03:28getting into actually analyzing so I
4:03:30named it analytically wizardy all right
4:03:32the last section to move into is the
4:03:33conclusions and I'm going to break this
4:03:35into two separate sections first is
4:03:40insights and I'm going to just capture
4:03:42all the different ins sites that we had
4:03:44up here from before and captured into
4:03:47just five main different value points
4:03:49that I found from this analysis and then
4:03:52finally I'm going to have some closing
4:03:54thoughts for this section I'd really be
4:03:56looking at what you actually took away
4:04:00holistically from this project for me
4:04:02just working on this alone really built
4:04:05up my sequel skills although I'm already
4:04:07you know pretty confident in those
4:04:09skills I feel actually going through and
4:04:10teaching you through this I learned a
4:04:12lot myself so so I put a lot of that in
4:04:14the closing thoughts here anyway so this
4:04:16read me has everything we need inside of
4:04:18it I'm going to go ahead and save it and
4:04:21then I'm going to upload it into GitHub
4:04:23for this it's going to be committing
4:04:24those two images that I had in the
4:04:26readme along with the changes to the
4:04:28readme so now when I actually go to this
4:04:32project here I actually have a fullblown
4:04:35layout of all the different work we did
4:04:39and all different analysis here I'm
4:04:41pretty blown away this is a lot of work
4:04:42we put into this you should be super
4:04:44proud of this the other thing I'd
4:04:46suggest is actually pinning this
4:04:48repository to your profile so you can
4:04:50come up here to customize your pins and
4:04:53I'm going to come and select this SQL
4:04:55project data analysis and save those
4:04:57pins and then from here if I wanted to I
4:04:59can drag it around and even put it up at
4:05:01the top all right now that we built this
4:05:04entire file and this readme we need to
4:05:06go forward with actually showcasing it
4:05:08so that's what we'll be doing in the
4:05:09next section of actually getting this
4:05:11onto something like your LinkedIn to
4:05:13show case as experience all right with
4:05:16that I'll see you in the next
4:05:21one de nerds congratulations on making
4:05:23it to the end of the course been nothing
4:05:25short of your hard work and there's a
4:05:28couple steps I'd recommend taking
4:05:30further now and actually showcase your
4:05:32work so it's been great that you put
4:05:33this work on GitHub but now you need to
4:05:35get the word out and I recommend doing
4:05:37this via LinkedIn there's three main
4:05:39things you can do for this one add the
4:05:41certificate of completion for those that
4:05:42purchased course no certificates which
4:05:44it's not too late to do that you can add
4:05:46this to your profile another thing you
4:05:48can do is with your GitHub project now
4:05:50on the internet you can also showcase
4:05:52this project on your LinkedIn profile
4:05:55and the last and third thing that we'll
4:05:56cover is a social media post so after
4:05:59you complete the end of course survey I
4:06:02will send you an email via an Automation
4:06:05and you'll receive the certificate of
4:06:07completion with this email you can go
4:06:09ahead and then download that certificate
4:06:11to your computer after that you should
4:06:13never over to LinkedIn specifically to
4:06:15your profile and in it make sure that
4:06:18you have your certificate section and
4:06:19also your project section enabled within
4:06:22your profile by going underneath the
4:06:23recommended and adding these two for the
4:06:26certificate you just navigate to that
4:06:28section click the plus icon and then
4:06:30from there fill it out most of the
4:06:32information here is self-explanatory for
4:06:34the issuing organization you'll be able
4:06:35to put in my name of Luke barus for the
4:06:38skills I would list these five core
4:06:40skills of SQL postgress and SQL light
4:06:43now if you did the GitHub portion of
4:06:44this you can also put in git and GitHub
4:06:46finally it has an option to add media
4:06:48and you can go through and actually
4:06:50upload that certificate that you had
4:06:52from previously from there click apply
4:06:54this obviously in my certificate so I'm
4:06:56not going to save it now for sharing
4:06:57your project you're going to navigate
4:06:59down to the project section and then
4:07:01click similarly that add icon now this
4:07:03one's also pretty self-explanatory
4:07:05you're going to put in that project name
4:07:06a short little description I have about
4:07:08what I did and what I investigated and
4:07:10what I found similarly I put in the same
4:07:11skills that I had in the certificate
4:07:13section
4:07:14and if you obviously you did the project
4:07:16and you listed a GitHub you can list get
4:07:17in GitHub when it gets to add media
4:07:20that's where you want to actually go in
4:07:21and add the link to your GitHub
4:07:24repository and this way it can be
4:07:26actually directed them right to it from
4:07:29there put that start and end date
4:07:30everything else can pretty much be left
4:07:31blank and for this one I'm going to go
4:07:33ahead since I actually did do this
4:07:35project I'm going to go ahead and save
4:07:36it so now that we've both showcased our
4:07:38certificate and also this project the
4:07:40last thing we have to do is just make a
4:07:42social media post you prompt you either
4:07:44after the certificate or even this
4:07:45project to maybe start a post now for
4:07:47this post feel free to tag both me and
4:07:49also Kelly the co-creator of this course
4:07:52in your post I love seeing everybody's
4:07:54progress and specifically diving in and
4:07:56actually checking out your different
4:07:57projects so I'm looking forward to
4:07:59seeing that all right once again
4:08:01congratulations on wrapping up this
4:08:03pretty hefty course on SQL I feel like
4:08:05it captures everything that you need to
4:08:07know to dive in and be confident in your
4:08:09skills no matter what the field you're
4:08:11working on in data science is for what
4:08:13to learn next you probably learn from
4:08:15the data that python is another popular
4:08:18skill and I'll have a tutorial for that
4:08:20coming real soon so stay tuned but until
4:08:23then I find that chat gbt speeds up my
4:08:26process a lot in data analytics so if
4:08:29you're curious about that check out this
4:08:31video right here also for those that
4:08:33didn't buy the course notes certificate
4:08:35still not too late check out this link
4:08:37right here with that I'll see you in the
4:08:39next one

About this transcript

This page contains the full transcript of SQL for Data Analytics - Learn SQL in 4 Hours by Luke Barousse, generated from the public captions YouTube serves with the video. The transcript has 48,224 words across 6,515 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.