So im looking to get into a career change. From stay at home dad to anything else. Ive just finished up Khan Academy's SQL course. But Im still having major imposter syndrome. I feel like if I just had a database and some questions so I can start pulling queries it would definitely help. Is there any place like that? Im going through Code Academy's tutorial right now and its pretty easy. Any ideas are much appreciated.
November 8th, 2005 The greatest night in the history of GD!
1. "I work with SQL on a daily basis " In response to Reply # 0
It depends; I would say stick with Code Academy or Udemy if you have any programming background. But UCLA and Berkley's extensions offer SQL courses. I would also start learning Tableau since you can take the data from your SQL queries and put it into Tableau to visualize the data.
Generally, SQL is easy; you can learn it in a few weeks if you have programming experience. If not, it takes a bit longer, but the key is taking that SQL data and doing something with it, which is why I suggest learning Tableau to compliment your SQL skills.
sometimes u gotta leave ur inner nigger in the bank vault. - desus
3. "You can setup PostgreSQL & MySQL on your computer" In response to Reply # 0 Thu Feb-16-23 02:03 PM by flipnile
If you have Python installed, you already have SQLite installed. SQLite you can access via Python, while Postgres and MySQL come with a command line interface.
8. "RE: You can setup PostgreSQL & MySQL on your computer" In response to Reply # 3
Ive got SQLite installed, and have done a few projects with it. Its my first programming language. I mean Ive toyed around with HTML and Python just to see what they were about. This is the first thing I've really hit hard and will hopefully get hired in.
so far its been pretty straight forward. Joins are a bit confusing but im getting it. CTE's and window functions feel like mandarin to me right now. But from what ive read they are not used often.
November 8th, 2005 The greatest night in the history of GD!
15. "Try to spend more time on joins if they're confusing you tho " In response to Reply # 8
bc they're pretty critical SQL operations (ime).
It'll help your confidence (and troubleshooting, and credibility) a lot if you can assure yourself (and others) that you've used the correct join(s). Especially if/when working with huge datasets.
Maybe seek out accompanying resources online for that topic, so you can study and practice them beyond what your online course is demanding and really drill them in.
Not playing down how powerful other SQL operations can be, but joins come up a lot in this type of work and the consequences of propagating the wrong data can be especially dire depending on the nature of your job/sector.
Bad joins are insidious bc even in a best case scenario they can "look" like the correct number of rows or columns but contain wrong information. Legit software engineers use all sorts of best practices/tricks to catch shit like that as they go but for those of us who start programming without that training such tricks aren't intuitive right out the gate (it gets better tho). Good luck!
>Ive got SQLite installed, and have done a few projects with >it. Its my first programming language. I mean Ive toyed around >with HTML and Python just to see what they were about. This is >the first thing I've really hit hard and will hopefully get >hired in. > >so far its been pretty straight forward. Joins are a bit >confusing but im getting it.
17. "*Imposter syndrome triggered*" In response to Reply # 8 Fri Feb-17-23 09:23 AM by flipnile
>CTE's and window functions feel >like mandarin to me right now. But from what ive read they are >not used often.
Been working with SQL databases for over 20 years, don't know what those terms are. Had to google, lol.
WebDev SQL can pretty-much be 100% CRUD (Create, Read, Update & Delete). Designing databases and normalizing the tables is the fun part for me.
Have you set up a development environment (on a computer) for yourself yet? Being able to play around with code and ideas, IMHO, is essential to learning.
4. "kind of have to get in there and poke around" In response to Reply # 0 Thu Feb-16-23 02:50 PM by bearfield
sql is very prevalent in my position as a banking software support rep. we use sql all the time but it's mostly limited to queries and on-the-fly editing (as in changing a few values as upposed to a proper update -> set command.) as i was exploring moving to this position — having no experience with sql previously — i went through a handful of online sql tutorials including mysql and w3schools. those tutorials gave me an idea of what using sql might be like but nothing stuck until i actually got the position and had to run several queries daily for several months
imo sql isn't too complicated until you get into joins and unions. even complex queries that reference discrete tables aren't that bad. you just need to get the syntax down; the logic is easy
10. "RE: kind of have to get in there and poke around" In response to Reply # 4
> those tutorials gave >me an idea of what using sql might be like but nothing stuck >until i actually got the position and had to run several >queries daily for several months
this right here is where Im at. But since its my first programming language. Its a terrifying feeling.
November 8th, 2005 The greatest night in the history of GD!
========================================= I'm an advocate for working smarter, not harder. If you just focus on working hard you end up making someone else rich and not having much to show for it. (c) mad
it's a subscription based but they always seem to have a sale. They have some pretty cool projects in there that allow you to download the data.
If you like Python, you can learn a tool like Panda to format the data and move it into a database (installed on your laptop or virtual machine). You can use SQL to do the inserting for more practice.
I have a small virtual machine on Linode that I use as my play area and my website is hosted there.
Since I've been learning some stuff in AWS, I may move there to get more experience and exposure to AWS.
But yeah, in regard to imposter syndrome, I understand. I've been there, just keep pushing don't let it get to you.
21. "luckily the bootcamp I am going through had me building my portfolio" In response to Reply # 20
so Ive got a decent one going on github. Working on a final project through the bootcamp. Then I have a practice test and final exam. After that Ill jump into kaggle and work that while im working on tableau. Thanks
November 8th, 2005 The greatest night in the history of GD!
23. "Sounds like you got bit by the progreamming the bug..." In response to Reply # 0
Your journey may start with SQL but don't be surprised if ends up elsewhere
SQL is definitely powerful and deceptively simple in terms of language complexity vs. real-world use cases (i.e. you can do a lot of really complex things with it). I find it great for some things, terrible for others (like most languages).
You asked re: sample data sets -- w3C schools has some tables which you can practice on via their online tutorials, e.g. joins: https://www.w3schools.com/sql/sql_join.asp
I don't think they are downloadable or remotely accessible outside of the web UI though.
You can always populate your own tables, e.g. create tables with dummy data using Excel or Google Sheets, export to .csv and then create a table from that (I believe SQL workbench will allow you to import from csv). This can be a good way to go as you will then be really familiar with the makeup of your data, and can easily spot if there are errors in your code (e.g. you know there are 3 rows that should match a certain condition but your query is only returning 1).
as far as marketable skills mine are pretty low. I dont have a college education and ive been out of the workforce for 13 years now. This was the plan. I got let go from my job, the wife wanted to move up in her company which required us to move further away from everyone in the family that helped us, and enabled us to both still have full time jobs and take care of our son. But this mufucka is getting more expensive the older he gets. There is no way I would be able to function doing some low paying mindless job.
So here we are. I have finished the SQL portion of the bootcamp. It went well. JOINs are still getting to me. Mainly the difference between them all. When to use which one. I have a portfolio on github with the projects I did through the bootcamp.
Ive moved onto tableau and im progressing through that decent enough. Its alot easier since its just a program to learn and not a language. Ive always been good at clicking around and figuring software out.
I am bout 70% through with the bootcamp. Then its on to getting a resume going and my linkedin. Also trying to tighten up on those areas I need tightening up in. Maybe doing a few more projects.
November 8th, 2005 The greatest night in the history of GD!
25. "JOINS are always a problem" In response to Reply # 24
Inner vs. Outer vs. Left and Right. Shit does get confusing, keep at it, and it'll start to make sense. Don't worry if you don't have it down packed, I've been doing this for years, and I still refer to W3 schools or go to StackOverflow if I can quite figure something out.
sometimes u gotta leave ur inner nigger in the bank vault. - desus
so one thing that is still throwing me off on JOINs is when I am joining two tables together is how do I determine which table is the left table and which is the right table? When I have an ERD I usually get them in the correct order. But lots of times the data sets im working with dont come with an ERD. Also an example, say I am SELECTing FROM customers and I want to JOIN invoices. But I go FROM invoices and JOIN customers. I usually get what looks like the same results just ordered differently. OR does it just look that way?
November 8th, 2005 The greatest night in the history of GD!
28. "RE: regarding JOINS" In response to Reply # 27
so for me I have a query like this:
select cc.course_number as "Course Number", cc.section_number as "Section Number", courses.course_name as "Course Name",
CASE WHEN s_ca_crs_x.contentcode = 154 THEN 'Y' ELSE ' ' END as "CTE",
teachers.lastfirst as "Teacher",
CASE WHEN cc.schoolid = 72 THEN 'John Glenn High School' WHEN cc.schoolid = 73 THEN 'La Mirada High School' WHEN cc.schoolid = 75 THEN 'Norwalk High School' WHEN cc.schoolid = 87 THEN 'Southeast Academy High School' WHEN cc.schoolid = 91 THEN 'El Camino High School' ELSE ' ' End as "School", cc.termid,
students.student_number, students.LastFirst as "Student Name", students.grade_level as "Grade"
FROM CC JOIN courses on cc.course_number = courses.course_number JOIN students on cc.studentid = students.id RIGHT OUTER JOIN s_ca_crs_x on courses.dcid = s_ca_crs_x.coursesdcid
WHERE ....
So in my Join statement, I have a right to join where I want data from the s_ca_crs_x table if there's a match to the courses table.
DM me and I can send you a word doc that breaks it down pretty easy.
sometimes u gotta leave ur inner nigger in the bank vault. - desus
30. "RE: regarding JOINS" In response to Reply # 27
select * from customers left join invoice on customers.id = invoice.id where blah blah blah;
in this case the customers table is the left table (since you called it first). All the records on the left table (customers) will be returned with the matching invoices based on the primary (unique) key on the left and the foreign key on the right table (invoices). I'm also assuming this is a one (customer) to many (invoices) table relationship.
If the other way around:
select * from invoices left join customers on customers.id = invoice.id where blah blah blah;
All the records on the left (in this case invoices) will be returned with the matching customer based on the key. customer.id is the primary and invoices.id is the foreign key. again, I'm also assuming this is a one (customer) to many (invoices) table relationship.
31. "The From will be the left table, The Join will be the right table" In response to Reply # 27
SELECT xyz FROM customers LEFT JOIN invoices ON customers.id = invoices.id
This will produce a dataset that contain all the data from the customers data and link it with id matches from the invoices data.
So every id on from the customer dataset will be in the new one. But not necessarily every id from the invoice dataset. Non matching ids from invoices data will be dropped.
The reverse is true if you did right join.
SELECT xyz FROM customers RIGHT JOIN invoices ON customers.id = invoices.id
This will produce a dataset that contain all the data from the inovices data and link it with id matches from the customers data.
So every id on from the invoices dataset will be in the new one. But not necessarily every id from the customers dataset. Non matching ids from customers data will be dropped.
32. "you're right that you'll usually get the same results" In response to Reply # 27 Thu Mar-30-23 08:57 AM by Triptych
with different column orderings.
It's pretty rare that you need to do a RIGHT JOIN or RIGHT
OUTER JOIN.
When you're matching on ids in a one-to-one or one-to-many
relationship, you can usually avoid thinking about LEFT vs
RIGHT.
As others have mentioned, LEFT/RIGHT is determined by the
order of the JOIN statements.
-- NORMAL JOIN
-- A customer has many invoices (one-to-many)
-- This will return duplicate rows for each customer
-- according to how many invoices they have.
SELECT *
FROM customer
JOIN invoice
ON customer.invoice_id = invoice.id
-- SWAPPED JOIN
-- This returns the same results as the NORMAL JOIN above,
-- but columns are reordered. Note that the JOIN condition is
-- exactly the same.
SELECT *
FROM invoice
JOIN customer
ON customer.invoice_id = invoice.id
-- NO JOIN
-- Sometimes it's helpful to remember that you almost never
-- need actual JOIN statements. This query is exactly
-- equivalent to the NORMAL JOIN example above.
SELECT *
FROM customer, invoice
WHERE customer.invoice_id = invoice.id
-- OUTER JOIN
-- As above, but include customers that do NOT have a
-- matching invoice, and just include NULLs for the
-- invoice columns.
-- A RIGHT OUTER JOIN would include invoices with no matching
customers.
SELECT *
FROM customer
LEFT OUTER JOIN invoice
ON customer.invoice_id = invoice.id
The theory and math behind most of this is in the area of set
theory and discrete math, which if you're pursing a long term
career in databases or programming are both worth study.
Conceptually, JOINs work by creating a giant virtual table
where each row in the left table is joined with each row in
the right. So if you had 100 customers and 1000 invoices, this
table would initially have 100,000 rows. It can be
surprisingly helpful to remember this as a starting point when
trying to reason about the rest of your query.
The JOIN condition (e.g. ON customer.invoice_id = invoice_id)
is what filters rows in that huge table and narrows down what
is actually returned to you.
This graphic shows the correspondence between JOINs and
SELECT/WHERE styles:
https://commons.wikimedia.org/wiki/File%3ASQL_Joins.svg
33. "holy shit you guys are amazing." In response to Reply # 27
thanks for all the help on this. All really good information. Gonna dive into it but just from skimming through it ive picked up some great tips. Much appreciated
November 8th, 2005 The greatest night in the history of GD!
34. "running into an issue I can't figure out in MySQL" In response to Reply # 0
I have a CSV file of my sons baseball stats for last season. I opened that up in excel and cleaned up the data. There are about 30 columns. Of those 30, around 8 don't have any data. They are just categories we didn't pay attention to. So I deleted them on my CSV(deleting them would not affect any of the other data that I am using) I created my database in MySQL and created my table as well. I am using the import wizard to save me a shit load of time. When I do that and it gives me a preview of what the data will look like. The deleted columns are present and now throwing off my labeling and numbers. Any ideas on why they are still present? Yes I deleted them and didnt hide them.
November 8th, 2005 The greatest night in the history of GD!
35. "Does your MySQL table still have the unused columns?" In response to Reply # 34
Did you use the wizard to create the MySQL table? If so, maybe the unused columns were somehow created automatically (like from the column header in the csv).
Printer-friendly copy 
Email this topic to a friend
General Discussion
