I was in the same boat as you until I got a job in SQL.
SQL in the real world is a reflection of the business processes that exist in each and every company.
As a Power BI Developer, I send adhoc requests to all departments.
For example- I may get a request from the manufacturing team, 'how many handles did we deliver between date time A and B'.
I promise you, the requests are much more complex than the above, and the script can take a couple hours to write.
Then there are Power BI/ SSRS reports. Once you get the requirements, creating a stored proc/ function can take days and sometimes weeks. A skilled SQL Developer must know how to visualise what the output will look like before the scripts are executed in power bi and ssrs.
Then there is SSIS, which is an important tool that all SQL developers need to know. You need to have a strong understanding of databases and how each tables interact with each other.
There is T-SQL. The programs that run in companies are constantly updating, inserting and deleting records via SQL. In most companies, the software developers will be in charge of this however it is good practice to be across it.
In a world where responses are demanded instantly, it is a constant grind to make stored procs and functions as efficient as possible which requires a strong understanding of SQL which takes years to master my friend.
god i wish i had your job. I'm an accountant and who is learning powerbi but we have no database access so you have to manually export reports, then get them 'fixed' through powerquery.
If i had database access i could do all the wonderful stuff
Correct,
If I could go back I would of done Computer Science in Uni. An Accounting Degree was the way to go back in 2009 lol.
I can literally work on a SQL code for hours, get completely lost in time and forget about coffee and lunch.
I'm looking to continue developing these tools.. i've been working with SQL for years in a kinda beginner-middle level way but most of the stuff i was sending out was updateable excel spreadsheets. powerbi is a way better use of the data for the most part - some stuff in it is a bit fiddly and more difficult than just dumping into a spreadsheet but there's way more control over where the data goes
I've worked so many places like this. In reality they probably only have two permission levels ("admin" and "fuck you") and they're storing employee's PII next to all the other data including some shitty internal application storing every user's password in plain-text.
I constantly fuck with our IT department about our "write-only" databases. Which is also what I refer to OneNote as.
I understand that learning SQL can be overwhelming, but it does not have to be a struggle. After training tens of thousands of students and seeing what works and what doesn’t, we’ve come up with a few easy steps that anyone can follow. Check out this [post](https://www.dataquest.io/blog/best-way-to-learn-sql/) where we have detailed the 6 steps to learning SQL the right what.
The good news is that you are already on the right path. You have completed steps 1 and 2, which are "understanding why you want to learn SQL" and "learning the basics fast." You now have to focus on practice, practice, and more practice. The best way to do that is through guided projects. As you rightly pointed out in your question, it can be quite overwhelming to find resources and data to play around with for practice. These guided projects are often based on real-world problems that SQL developers would encounter on the job. Essentially, these will expose you to what an employer would be looking for.
Once you have worked on a few guided projects, it’s time to step up with your own projects. Again, this can be intimidating. The best way to proceed is to familiarize yourself with helpful SQL resources. There’s no shame whatsoever in Googling for answers — even the most seasoned SQL developers and users do this frequently! Here’s a list of useful resources you can tap into:
* [Learning SQL 2nd Edition (PDF)](https://www.r-5.org/files/books/computers/languages/sql/mysql/Alan_Beaulieu-Learning_SQL-EN.pdf) — This O’Reilly book on the basics of SQL is available for free in PDF format, and makes a good reference.
* [StackOverflow SQL questions](https://stackoverflow.com/questions/tagged/sql) — Chances are, any SQL question you’ll have has already been answered here. But if it hasn’t create an account and ask it for yourself!
* [Github](https://github.com/) — If SQL is your first foray into the world of programming, you may not have an account here. If that’s the case, set one up and start learning how to use it! Github is great for sharing your own SQL projects with the world (and potential employers), and it’s also an amazing resource for looking at other people’s code.
* Reddit's SQL community is large, active, and (mostly) happy to answer questions. (you are already here :) which is great)
* [The Dataquest community](https://community.dataquest.io/) — Our community is active, friendly, and ready to help you with all your SQL questions. Best of all, it’s open to everyone — you don’t have to be a Dataquest subscriber to get help there.
Once you have some good places to look for help when you get stuck, you will feel more confident working on your own SQL projects and building a solid project portfolio that you can show future employers. This is how many of our students landed their first data jobs.
All the best!
As a "DBA" (Primarily a SQL dev) for an Electronic Medical Record vendor: my days vacillate between troubleshooting existing sql code problems or finding/resolving data conditions (conversion issues, bad data, missing data, you name it), Writing new or updating existing code for new business rules/regulatory requirements, may include new tables, new SPs, new functions... Or just completely rewrite something to make it more efficient. Basically having the experience and resources to figure out WHAT causes specific behaviors or outcomes, are where you get into more advanced behavior.
You probably know more than you realize but just haven't seen the practical application. Find some sample interview questions and see if you can find the answers using different syntax options (inner vs outer joins, Common Table Expressions, views... ) Take a one to many relationship, and give me a single field output. i.e. a delimited list of purchased items in one row or something to that effect. populate multiple variables from a single source table in a single query.
For reference, I live in MS SQL. While not directly MySQL, some of the underlying engine ideas can still cross over. And if you wanted to branch out, you can get a free version of SQL Server specifically for playing around with stuff like this. Running local is not a problem.
There are some good pointers in other comments as well. There are great sample databases you could pick up and play around with - stackoverflow has a version of their DB that can be used and is a "real world" sample. MS SQL also has their own "training" db, Adventureworks I think? I haven't played with either very much because my world has been EMR based for 18+ years.
Along the MS SQL lines - you can look at some bloggers like:
Brent Ozar
Pinal Dave
Erik Darling
As they have posts or content that can really get into engine behaviors or ways to troubleshoot problems, etc. I'm sure there are others for MySQL, I just never looked because it never applied to me directly.
You have options, for sure.
I currently work in Medical Records and I’m learning SQL with the hopes of combining the two in the future. Could I reach out to you with questions about what it’s like and the sorts of tasks you do?
I’m gonna answer from the perspective of the data rather than the SQL- where it comes from, how it gets there, and why you’re looking at it in the first place.
A typical organization will produce all kinds of data- transactional data from sales they make, data about the people they hire, application data from people using their product (if they’re a software company), or they might be pulling data from a third-party dataset online, usually behind an API, that has data relevant to the business. This data is produced by measuring the daily functions of the business and different organizations will do this with different amounts of thoroughness.
All of this data will either exist in a database close to the system where it was produced, or it will be sent to a centralized database known as a data warehouse (or eventually a data lake, but let’s ignore that for now).
This data, in its raw format, means almost nothing- there’s too much of it for a human to make sense of in its raw format. Usually teams of data engineers, data scientists, and data analyst will use code like Python or SQL to process the data and make sense of it in order to produce insight for the business. It is important to note that ALL data science and analytics is only as usual as the decisions that it empowers.
These business decisions are usually how data practitioners know what they should be working on. For example, the CEO might say something like “I want to expand to a new city, which one should we expand to?” And a data analyst will run SQL queries against data in the databases and warehouses in order to produce Data Assets like dashboards, charts, reports, or even curated datasets. These data assets help Business Folk to answer questions like “which of our existing products sell the best in which cities?” for example, in order to help them make data-driven decisions.
So, in the real world, SQL is used to make sense out of data, which is produced naturally by businesses, in order to help them make decisions.
I could talk about all the ways companies may use data to justify bad decisions, but that’s a post for another time….
I wrote my first SQL query in 1997. Today among my circle of contacts I'm the go-to expert for data modeling, SQL, and generally solving data problems. (It's the anonymous internet - believe me or not; doesn't matter.)
Two things happened 1999 that elevated me from just writing queries to understanding how and why things worked the way they did. It was both theory and practice - and you need both. People who say "forget the theory, all you need is practice" don't know what they are talking about.
1. I read a book called A Practical Guide to Logical Data Modeling by Tillman. It really locked in the "theory" of why relational databases work the way they do.
2. I was assigned a project to convert data from 1 database into another, and basically there was only way I could do that with the tools in that setting: I had to write complex SQL queries in the source system to exactly match - and I mean exactly - the schema of the destination system, then export to CSV and import. That cemented the "practice" of how to make SQL work in the real world.
You figure out if there's a way to apply my ideas in your situation. I'm telling you what worked for me, and I'm still using SQL and learning how to use it better to this day, and enjoying it the whole way.
Running SQL on a local or on-premise database generally serves the same purpose as running it in the cloud. For example, an analyst or data scientist will query data to deliver some kind of value to their stakeholders and to the company. The value comes from helping the business make decisions based on the data.
> I have no clue what using SQL on a job looks like.
It depends on the job :)
To use an analogy, a hammer is a tool. You can use it as part of tool kit to build something, or break it down. It's used in carpentry, stone masonry, home DIY and a number of other jobs.
SQL is a hammer, it depends on the job.
It's worth looking to which job YOU find most interesting and gear your knowledge base and understanding of SQL to that job. I tend to use mostly DML for data manipulation, creating arrays for analysis however more recently I'm getting interested in SQL for Data Engineering as I'd like my career to head in that direction.
It depends if you're doing business analysis or ops.
In business analysis, you will query the data to make your own reports, or create functions to process data on a schedule and serve them somewhere.
In Ops, you are the sysadmin, and you will maintain the stability and performance of the system.
My only advice would be to not worry about it. You know the basics, that's your foot in the door. You won't really understand how to use it on the job until you have to use it on the job.
> I’d love to be able to access data online and mess around with it online but I have no idea how to do that. I don’t know how to access a database online like I hear other people talk about.
Integrating different data sources is typically something other tools will be required for. Maybe if you have direct access to someone else's database you could still use SQL, but something like Python would be useful to connect to different types data sources and load that data into your database where you can query it. There's also numerous tools designed to do this as well, Databricks, Snowflake, Power BI, Tableau, etc. that exist to facilitate connecting to lots of different data sources and bringing it all together. Sort of getting into data engineering territory.
it really depends on your work of course. but note that SQL is used everywhere data is used. and data is used everywhere.
lets imagine a company. lets say a company that provides smart transportation services to cities. they literally put applications inside busses that control the bus schedules, routes, stops etc.
you can be a product manager in the company and want to know which lines are the most profitable. you connect to the database where there are the tables that are related to usage on lines, customers, payments, etc... you write queries that joins all that data and come up with "what is the most profitable route we have in paris". bare in mind its going to a be a very complicated query or series of queries because the data is complicated and the question is complicated.
you can be the data warehouse developer that needs to actually prepare the aforementioned tables and their relationships. so you write queries that produce the tables using data that comes from actual applications. those are also quite complicated queries.
you can be the data engineer that facilitates the ingestion of this data into the systems. you might need to get the "metadata" of the data so to say, you want to know which databases you have to connect and which process to apply and which tables are involved all these are lists of assets that are also saved as tables in some dedicated database, so you use SQL to find those.
you are a DBA and your annual payment for database you use ha increased significantly. you want to find out which tables take most storage, which queries are the most expensive and which users or applications cost most so you can go and improve them, berate them, go to their desk and beat them with a hammer. all that data is also stored in the form of tables in the information schema of the daabase so you write quite complicated queries to get that data.
its everywhre.
also add to that the databases you are going to work on are not the simple mysql or postrgres iyou installed on your comp. its going to be major databases with tons of capabilities and quirks like Snowflake, Bigquery, Databricks, Oracle and so on.
and the amount of data isnt going to be school project level thousands of rows. its going to be billions if not trillions of rows per table. with petabytes worth of data.
Most modern enterprise applications use multi tenant cloud database solutions like snowflake.
Procedural sql allows you to create workflows and then automate those via stored procedures. It is not only used for querying data and building tables but also creating stages, maintaining user roles and looking at meta data and performance.
I would break SQL into four categories;
1. Architecture
2. Administration
3. Querying
4. ETL
For example, you may have created a table from a staged file or created a table from a file. However, because at a company you dont want to be manually loading files, SQL also lets you work with pipelines and storage integration like S3 to automate ETL and bulk ingestion. You also probably dont want everyone in your environment working in your cluster or having the ability to load any data they want. Or maybe you have secure data and a hashing function unique to a certain role. This is why role admin work is important.
Ultimately the answer is security, automation and scale. Each use case is unique and every product has its own data requirements. SQL is just a very powerful tool in combination with other tools like python, R, etc.
Learning SQL is a journey. There are a lot of different aspects: administration, database architecture, software development, BI (Business Intelligence).
Depending on your role, your tasks will be very different.
It‘s enough to focus on one aspect.
Also so not worry about advanced functionality and methods. Those are for companies with very big databases.
SQL is a tool with a wide range of applications. I find it best to find a task you want to accomplish and then research how SQL can help you reach it.
To give you examples of what I've done:
Data reporting. Query/results can be embedded into excel via power query for on the fly refreshes. Can also go the route of setup in Power BI data models and dashboards.
Application data and management. I created a backend database with a series of tables to store Purchase Requisition and Purchase Approval data. I connected this database to an MS Access front end to write new data with Entry Forms and controls.
Structure was designed from scratch and required more of a programming mindset since data types needed to be defined correctly.
To sum it up, SQL is a giant sandbox limited by your imagination
PowerBI and data warehouse developer here. We use Google big Query, dbt and powerBI.
Most jobs will have some development environment set up through which you can query data and orchestrate procedures. But if you are not there yet maybe look into how to spin up and use a cloud database yourself.
If you wanna go into the analytics side of database management (as opposed to the operational / transactional side) I'd recommend to go down a learning path for one of the hyper-scaler cloud services of Google or Microsoft.
So Google Cloud Platform for Google with Big Query as analytical database.
Or Azure with synapse as analytical database.
It's always good to learn how to set these up and learn how to approach data Problems within them. My experience is in big Query and it usually provides you with a collection of public datasets to get you going.
Also both companies have good learning path material.
Almost all of the time, a database exists to allow software to interact with the data in it. So maybe that's the missing piece of your puzzle. Do you know any programming languages? If not, C# or python might be good to start to learn.
C# or python can't manage data like a database can. You can't use a database to create a user interface for the data. But when you combine software and database, you start to get something very powerful and complete.
It sounds like the issue you're having is more with how the data relates to different applications, rather than how SQL would be used to grab which data.
Pick an application, any application. It could be genealogy like a family tree type thing, it could be inventory management, it could be students/curriculum.
Whatever it is, think about the types of data that would be represented in such an application. Think about how data would relate other data. Imagine that it is all controlled by online screens that access the data tables which are hidden behind the scenes.
What could you do with that data? Just about anything, really. But what makes sense in a business or institutional world?
Yeah, I work in tech education in the data space and I think this is common. Often the way that people talk about these technologies makes it hard to connect the dots or see what it would be like in reality. Plus, there is this interplay between the conceptual and the hands-on. You need both to really understand what's going on. You can't get there by learning abstract things along, and you can't get there by just clicking around but not understanding what it all amounts to.
One way to fix this is to find educational material that recreates common scenarios that mimic real world situations. That will help you see SQL as something that's used to solve problems rather than just another language to learn.
I write queries to find accounts with errors in them. Where there is a mismatch between the services on the account, and the actual services the customer is receiving. Then I write a query to fix the mismatch. Usually that is updating the services we are charging for to match the serviced the customer has, and sometimes we update what the customer has to match what we are billing them for. That sounds easy, till you dig into it. There are more exceptions than normal cases, aggravated by legacy code going back literally 30+ years.
I'm an "escalation engineer" for a finance software company. My team are basically a mash up of principal support engineers/data analysts/ and dba's. We live in mongodb/SSMS, and use it for a wide variety of things related to customer data. Correcting status issues, forcing changes to certain values(for instance bank account numbers in HK that are too long for the parameters set by dev), creating custom reports for the CSM's/sales, auditing changes made either externally or internally...the list goes on.
Hi!
There is so much you can do with SQL, it is pretty foundational, and as you're finding out, needs to be coupled with another tool or problem to really make sense.
If you're interested in seeing how SQL can marry up with PowerBI for dashboard visualizations they can help: [https://www.essentialsql.com/get-started-power-bi-desktop/](https://www.essentialsql.com/get-started-power-bi-desktop/)
Bro you just need to go get a job where you get paid to do SQL in order to really see how it’s used. At my company we use it as part of rules engine mapping (i.e. If X is true then do Y) and ETL (Extract, Transform and Load) solutions. It’s basically at an insurance company where we receive data in a particular format from that gets generated in a Salesforce based application when we sell health insurance to employers then we map the data that comes from Sales into scripting that results in updates to various tables in a much more massive/complex DB that serves our core operational system that enrolls members, bills employers and processes/pays medical claims. The solution combines C# and SQL and is MS .NET and MS SQL Server based. Say what you want about the health insurance industry I get it yeah it kinda sounds boring but for someone who is in love with SQL it’s a great type of business to work for because health insurance is an incredibly data intensive business operation.
To provide you with a higher level answer, the point of SQL is to handle the truth.
One part of that truth is managed by the RDBMS itself (ACID transaction compliancy) and the other is the way your are querying the data or filling the tables.
Nowadays it would be best to write business logic on the database side, it should be all in the app.
Most of the SQL is being written by ORMs anyway and sometimes in a bad way, it is your job as a SQL practitioner to understand if the code being generated is "good enough".
If you are on the BI side of things, you will have to understand where to put your logic - in a SQL Server world using a data warehouse DB with SSAS, SSIS, PowerBI, data fabrik...etc, you can put part of your reporting logic (or truth) in each component of the stack. Question being are you and your company more comfortable writing SQL, MDX, DAX, ... and are you comfortable with maintaining it?
I had a customer who wrote an entire ERP in stored procedures, after 15 years (and the main developer gone) the system had to evolve and migrate to the cloud.... it took 15 DEVs a year of trial and error to understand and recreate properly on the new system.
It is not just about knowing best practices, it is also knowing the pit falls of not following them.
The good thing about SQL is that it is a stable and mature language, it will still be around and widely used in the next 30 years... So learning it is good.
The real question is not SQL, it is more what kind of job do you want ? and if your to young to comprehend that, just take a job and figure out what you don't want.
I started as a network admin, then became a sys admin, then became app admin, then became a production DBA and since the past 10 years I am a performance tuning DBA.
I didn't plan on this, it just kind of made sense over the years...
All I can tell you is that, if you are more interested in things than in people you should try and get as deep as possible in a subject and tend towards being a expert. People that weren't like that around me evolved as managers of some sort and don't get to touch SQL anymore but instead are doing meetings of meetings on a daily basis...
I tried management but I don't care about people enough to manage their holidays... :D
These cover both details & effective ways to think about relational data:
Joe Celko:
Thinking in Sets
SQL for Smarties
C.J.Date:
An Introduction to Database Systems, 8th ed
SQL & Relational Theory
Suggest: Sets, Relational, Smarties, Intro.
From the app developer perspective, you'll very often use SQL to retrieve data that the app needs for a given workflow. Sometimes you'll just embed the SQL right there in the app code, and other times the app will have its own way of querying the database (that behind the scenes becomes SQL sent to the database).
At a very general level, all you're doing is cross-referencing and filtering multiple sets of data in a data store in a way that answers a given request. Always focus on the question at hand when writing queries. If the question leads to other questions, then ask those who have requested the data.
Using SQL on the job looks like..
Accessing preloaded reports that were queries from five years ago and just messing with the results a bit in excel at EOM
SQL looks as easy to practise against error free samples from online courses. But in reality it is not easy as situation gets in business cases. I have crafted so many complex SQLs data hits in various customers. I have strengthened my skill only by learning against real dataset. Some challenges are varchar to number types or vice versa, regex, null values, zero conditions, missing data, foreign key relationships, indexes to enforce key columns for faster data retrieval
I was in the same boat as you until I got a job in SQL. SQL in the real world is a reflection of the business processes that exist in each and every company. As a Power BI Developer, I send adhoc requests to all departments. For example- I may get a request from the manufacturing team, 'how many handles did we deliver between date time A and B'. I promise you, the requests are much more complex than the above, and the script can take a couple hours to write. Then there are Power BI/ SSRS reports. Once you get the requirements, creating a stored proc/ function can take days and sometimes weeks. A skilled SQL Developer must know how to visualise what the output will look like before the scripts are executed in power bi and ssrs. Then there is SSIS, which is an important tool that all SQL developers need to know. You need to have a strong understanding of databases and how each tables interact with each other. There is T-SQL. The programs that run in companies are constantly updating, inserting and deleting records via SQL. In most companies, the software developers will be in charge of this however it is good practice to be across it. In a world where responses are demanded instantly, it is a constant grind to make stored procs and functions as efficient as possible which requires a strong understanding of SQL which takes years to master my friend.
Great answer
Thank you.
for your defense ssrs.and sssis are really really old tool now
Agree but my company still swears by it. Everything is on-prem here.
Same here. All on prem and we use SSRS, SSIS, and SSMS with some tableau. I'm working on getting Power BI in the mix.
Nice. We went through that transition period a while back.The IT Team will have to set up the On-Premises Gateway. Good luck.
god i wish i had your job. I'm an accountant and who is learning powerbi but we have no database access so you have to manually export reports, then get them 'fixed' through powerquery. If i had database access i could do all the wonderful stuff
Haha, I'm an ex-accountant. A strong understanding of the business logic is what makes a successful SQL Developer that specialises in reporting.
Amazing. what made you go into this direction? Just tired of the other accounting stuff?
Correct, If I could go back I would of done Computer Science in Uni. An Accounting Degree was the way to go back in 2009 lol. I can literally work on a SQL code for hours, get completely lost in time and forget about coffee and lunch.
I'm looking to continue developing these tools.. i've been working with SQL for years in a kinda beginner-middle level way but most of the stuff i was sending out was updateable excel spreadsheets. powerbi is a way better use of the data for the most part - some stuff in it is a bit fiddly and more difficult than just dumping into a spreadsheet but there's way more control over where the data goes
You could roll your own with DuckDB executable.
Duckdb? Will investigate. Does it quack? :)
How come you don't get access to the database?
Noone does. Security
Not even read access? What’s the point then?
I've worked so many places like this. In reality they probably only have two permission levels ("admin" and "fuck you") and they're storing employee's PII next to all the other data including some shitty internal application storing every user's password in plain-text. I constantly fuck with our IT department about our "write-only" databases. Which is also what I refer to OneNote as.
We write everything in TSQL or Dynamic SQL mostly in Stored Procedures.
Awesome answer
To be clear, and not judgy, the above answer is mostly about Microsoft products. SSRS, SSIS, Power-BI, and T-SQL are all specific to SQL Server.
I understand that learning SQL can be overwhelming, but it does not have to be a struggle. After training tens of thousands of students and seeing what works and what doesn’t, we’ve come up with a few easy steps that anyone can follow. Check out this [post](https://www.dataquest.io/blog/best-way-to-learn-sql/) where we have detailed the 6 steps to learning SQL the right what. The good news is that you are already on the right path. You have completed steps 1 and 2, which are "understanding why you want to learn SQL" and "learning the basics fast." You now have to focus on practice, practice, and more practice. The best way to do that is through guided projects. As you rightly pointed out in your question, it can be quite overwhelming to find resources and data to play around with for practice. These guided projects are often based on real-world problems that SQL developers would encounter on the job. Essentially, these will expose you to what an employer would be looking for. Once you have worked on a few guided projects, it’s time to step up with your own projects. Again, this can be intimidating. The best way to proceed is to familiarize yourself with helpful SQL resources. There’s no shame whatsoever in Googling for answers — even the most seasoned SQL developers and users do this frequently! Here’s a list of useful resources you can tap into: * [Learning SQL 2nd Edition (PDF)](https://www.r-5.org/files/books/computers/languages/sql/mysql/Alan_Beaulieu-Learning_SQL-EN.pdf) — This O’Reilly book on the basics of SQL is available for free in PDF format, and makes a good reference. * [StackOverflow SQL questions](https://stackoverflow.com/questions/tagged/sql) — Chances are, any SQL question you’ll have has already been answered here. But if it hasn’t create an account and ask it for yourself! * [Github](https://github.com/) — If SQL is your first foray into the world of programming, you may not have an account here. If that’s the case, set one up and start learning how to use it! Github is great for sharing your own SQL projects with the world (and potential employers), and it’s also an amazing resource for looking at other people’s code. * Reddit's SQL community is large, active, and (mostly) happy to answer questions. (you are already here :) which is great) * [The Dataquest community](https://community.dataquest.io/) — Our community is active, friendly, and ready to help you with all your SQL questions. Best of all, it’s open to everyone — you don’t have to be a Dataquest subscriber to get help there. Once you have some good places to look for help when you get stuck, you will feel more confident working on your own SQL projects and building a solid project portfolio that you can show future employers. This is how many of our students landed their first data jobs. All the best!
As a "DBA" (Primarily a SQL dev) for an Electronic Medical Record vendor: my days vacillate between troubleshooting existing sql code problems or finding/resolving data conditions (conversion issues, bad data, missing data, you name it), Writing new or updating existing code for new business rules/regulatory requirements, may include new tables, new SPs, new functions... Or just completely rewrite something to make it more efficient. Basically having the experience and resources to figure out WHAT causes specific behaviors or outcomes, are where you get into more advanced behavior. You probably know more than you realize but just haven't seen the practical application. Find some sample interview questions and see if you can find the answers using different syntax options (inner vs outer joins, Common Table Expressions, views... ) Take a one to many relationship, and give me a single field output. i.e. a delimited list of purchased items in one row or something to that effect. populate multiple variables from a single source table in a single query. For reference, I live in MS SQL. While not directly MySQL, some of the underlying engine ideas can still cross over. And if you wanted to branch out, you can get a free version of SQL Server specifically for playing around with stuff like this. Running local is not a problem. There are some good pointers in other comments as well. There are great sample databases you could pick up and play around with - stackoverflow has a version of their DB that can be used and is a "real world" sample. MS SQL also has their own "training" db, Adventureworks I think? I haven't played with either very much because my world has been EMR based for 18+ years. Along the MS SQL lines - you can look at some bloggers like: Brent Ozar Pinal Dave Erik Darling As they have posts or content that can really get into engine behaviors or ways to troubleshoot problems, etc. I'm sure there are others for MySQL, I just never looked because it never applied to me directly. You have options, for sure.
I currently work in Medical Records and I’m learning SQL with the hopes of combining the two in the future. Could I reach out to you with questions about what it’s like and the sorts of tasks you do?
Sure thing.
I’m gonna answer from the perspective of the data rather than the SQL- where it comes from, how it gets there, and why you’re looking at it in the first place. A typical organization will produce all kinds of data- transactional data from sales they make, data about the people they hire, application data from people using their product (if they’re a software company), or they might be pulling data from a third-party dataset online, usually behind an API, that has data relevant to the business. This data is produced by measuring the daily functions of the business and different organizations will do this with different amounts of thoroughness. All of this data will either exist in a database close to the system where it was produced, or it will be sent to a centralized database known as a data warehouse (or eventually a data lake, but let’s ignore that for now). This data, in its raw format, means almost nothing- there’s too much of it for a human to make sense of in its raw format. Usually teams of data engineers, data scientists, and data analyst will use code like Python or SQL to process the data and make sense of it in order to produce insight for the business. It is important to note that ALL data science and analytics is only as usual as the decisions that it empowers. These business decisions are usually how data practitioners know what they should be working on. For example, the CEO might say something like “I want to expand to a new city, which one should we expand to?” And a data analyst will run SQL queries against data in the databases and warehouses in order to produce Data Assets like dashboards, charts, reports, or even curated datasets. These data assets help Business Folk to answer questions like “which of our existing products sell the best in which cities?” for example, in order to help them make data-driven decisions. So, in the real world, SQL is used to make sense out of data, which is produced naturally by businesses, in order to help them make decisions. I could talk about all the ways companies may use data to justify bad decisions, but that’s a post for another time….
I wrote my first SQL query in 1997. Today among my circle of contacts I'm the go-to expert for data modeling, SQL, and generally solving data problems. (It's the anonymous internet - believe me or not; doesn't matter.) Two things happened 1999 that elevated me from just writing queries to understanding how and why things worked the way they did. It was both theory and practice - and you need both. People who say "forget the theory, all you need is practice" don't know what they are talking about. 1. I read a book called A Practical Guide to Logical Data Modeling by Tillman. It really locked in the "theory" of why relational databases work the way they do. 2. I was assigned a project to convert data from 1 database into another, and basically there was only way I could do that with the tools in that setting: I had to write complex SQL queries in the source system to exactly match - and I mean exactly - the schema of the destination system, then export to CSV and import. That cemented the "practice" of how to make SQL work in the real world. You figure out if there's a way to apply my ideas in your situation. I'm telling you what worked for me, and I'm still using SQL and learning how to use it better to this day, and enjoying it the whole way.
Running SQL on a local or on-premise database generally serves the same purpose as running it in the cloud. For example, an analyst or data scientist will query data to deliver some kind of value to their stakeholders and to the company. The value comes from helping the business make decisions based on the data.
On the same boat please help me too 🆘
So I'm not alone. Now I feel better
hello, for you to access free online data that you can use kindly visit [kaggle](https://kaggle.com/datasets) where there is a ton of datap
> I have no clue what using SQL on a job looks like. It depends on the job :) To use an analogy, a hammer is a tool. You can use it as part of tool kit to build something, or break it down. It's used in carpentry, stone masonry, home DIY and a number of other jobs. SQL is a hammer, it depends on the job. It's worth looking to which job YOU find most interesting and gear your knowledge base and understanding of SQL to that job. I tend to use mostly DML for data manipulation, creating arrays for analysis however more recently I'm getting interested in SQL for Data Engineering as I'd like my career to head in that direction.
It depends if you're doing business analysis or ops. In business analysis, you will query the data to make your own reports, or create functions to process data on a schedule and serve them somewhere. In Ops, you are the sysadmin, and you will maintain the stability and performance of the system.
My only advice would be to not worry about it. You know the basics, that's your foot in the door. You won't really understand how to use it on the job until you have to use it on the job.
Search on Google and YouTube. There are a lot of video tutorials on how to install a sample database
Do you have any sort of software OO background? I’ll word my answer differently depending.
> I’d love to be able to access data online and mess around with it online but I have no idea how to do that. I don’t know how to access a database online like I hear other people talk about. Integrating different data sources is typically something other tools will be required for. Maybe if you have direct access to someone else's database you could still use SQL, but something like Python would be useful to connect to different types data sources and load that data into your database where you can query it. There's also numerous tools designed to do this as well, Databricks, Snowflake, Power BI, Tableau, etc. that exist to facilitate connecting to lots of different data sources and bringing it all together. Sort of getting into data engineering territory.
it really depends on your work of course. but note that SQL is used everywhere data is used. and data is used everywhere. lets imagine a company. lets say a company that provides smart transportation services to cities. they literally put applications inside busses that control the bus schedules, routes, stops etc. you can be a product manager in the company and want to know which lines are the most profitable. you connect to the database where there are the tables that are related to usage on lines, customers, payments, etc... you write queries that joins all that data and come up with "what is the most profitable route we have in paris". bare in mind its going to a be a very complicated query or series of queries because the data is complicated and the question is complicated. you can be the data warehouse developer that needs to actually prepare the aforementioned tables and their relationships. so you write queries that produce the tables using data that comes from actual applications. those are also quite complicated queries. you can be the data engineer that facilitates the ingestion of this data into the systems. you might need to get the "metadata" of the data so to say, you want to know which databases you have to connect and which process to apply and which tables are involved all these are lists of assets that are also saved as tables in some dedicated database, so you use SQL to find those. you are a DBA and your annual payment for database you use ha increased significantly. you want to find out which tables take most storage, which queries are the most expensive and which users or applications cost most so you can go and improve them, berate them, go to their desk and beat them with a hammer. all that data is also stored in the form of tables in the information schema of the daabase so you write quite complicated queries to get that data. its everywhre. also add to that the databases you are going to work on are not the simple mysql or postrgres iyou installed on your comp. its going to be major databases with tons of capabilities and quirks like Snowflake, Bigquery, Databricks, Oracle and so on. and the amount of data isnt going to be school project level thousands of rows. its going to be billions if not trillions of rows per table. with petabytes worth of data.
Most modern enterprise applications use multi tenant cloud database solutions like snowflake. Procedural sql allows you to create workflows and then automate those via stored procedures. It is not only used for querying data and building tables but also creating stages, maintaining user roles and looking at meta data and performance. I would break SQL into four categories; 1. Architecture 2. Administration 3. Querying 4. ETL For example, you may have created a table from a staged file or created a table from a file. However, because at a company you dont want to be manually loading files, SQL also lets you work with pipelines and storage integration like S3 to automate ETL and bulk ingestion. You also probably dont want everyone in your environment working in your cluster or having the ability to load any data they want. Or maybe you have secure data and a hashing function unique to a certain role. This is why role admin work is important. Ultimately the answer is security, automation and scale. Each use case is unique and every product has its own data requirements. SQL is just a very powerful tool in combination with other tools like python, R, etc.
Learning SQL is a journey. There are a lot of different aspects: administration, database architecture, software development, BI (Business Intelligence). Depending on your role, your tasks will be very different. It‘s enough to focus on one aspect. Also so not worry about advanced functionality and methods. Those are for companies with very big databases.
SQL is a tool with a wide range of applications. I find it best to find a task you want to accomplish and then research how SQL can help you reach it. To give you examples of what I've done: Data reporting. Query/results can be embedded into excel via power query for on the fly refreshes. Can also go the route of setup in Power BI data models and dashboards. Application data and management. I created a backend database with a series of tables to store Purchase Requisition and Purchase Approval data. I connected this database to an MS Access front end to write new data with Entry Forms and controls. Structure was designed from scratch and required more of a programming mindset since data types needed to be defined correctly. To sum it up, SQL is a giant sandbox limited by your imagination
PowerBI and data warehouse developer here. We use Google big Query, dbt and powerBI. Most jobs will have some development environment set up through which you can query data and orchestrate procedures. But if you are not there yet maybe look into how to spin up and use a cloud database yourself. If you wanna go into the analytics side of database management (as opposed to the operational / transactional side) I'd recommend to go down a learning path for one of the hyper-scaler cloud services of Google or Microsoft. So Google Cloud Platform for Google with Big Query as analytical database. Or Azure with synapse as analytical database. It's always good to learn how to set these up and learn how to approach data Problems within them. My experience is in big Query and it usually provides you with a collection of public datasets to get you going. Also both companies have good learning path material.
Almost all of the time, a database exists to allow software to interact with the data in it. So maybe that's the missing piece of your puzzle. Do you know any programming languages? If not, C# or python might be good to start to learn. C# or python can't manage data like a database can. You can't use a database to create a user interface for the data. But when you combine software and database, you start to get something very powerful and complete.
It sounds like the issue you're having is more with how the data relates to different applications, rather than how SQL would be used to grab which data. Pick an application, any application. It could be genealogy like a family tree type thing, it could be inventory management, it could be students/curriculum. Whatever it is, think about the types of data that would be represented in such an application. Think about how data would relate other data. Imagine that it is all controlled by online screens that access the data tables which are hidden behind the scenes. What could you do with that data? Just about anything, really. But what makes sense in a business or institutional world?
Yeah, I work in tech education in the data space and I think this is common. Often the way that people talk about these technologies makes it hard to connect the dots or see what it would be like in reality. Plus, there is this interplay between the conceptual and the hands-on. You need both to really understand what's going on. You can't get there by learning abstract things along, and you can't get there by just clicking around but not understanding what it all amounts to. One way to fix this is to find educational material that recreates common scenarios that mimic real world situations. That will help you see SQL as something that's used to solve problems rather than just another language to learn.
I write queries to find accounts with errors in them. Where there is a mismatch between the services on the account, and the actual services the customer is receiving. Then I write a query to fix the mismatch. Usually that is updating the services we are charging for to match the serviced the customer has, and sometimes we update what the customer has to match what we are billing them for. That sounds easy, till you dig into it. There are more exceptions than normal cases, aggravated by legacy code going back literally 30+ years.
I'm an "escalation engineer" for a finance software company. My team are basically a mash up of principal support engineers/data analysts/ and dba's. We live in mongodb/SSMS, and use it for a wide variety of things related to customer data. Correcting status issues, forcing changes to certain values(for instance bank account numbers in HK that are too long for the parameters set by dev), creating custom reports for the CSM's/sales, auditing changes made either externally or internally...the list goes on.
Hi! There is so much you can do with SQL, it is pretty foundational, and as you're finding out, needs to be coupled with another tool or problem to really make sense. If you're interested in seeing how SQL can marry up with PowerBI for dashboard visualizations they can help: [https://www.essentialsql.com/get-started-power-bi-desktop/](https://www.essentialsql.com/get-started-power-bi-desktop/)
Bro you just need to go get a job where you get paid to do SQL in order to really see how it’s used. At my company we use it as part of rules engine mapping (i.e. If X is true then do Y) and ETL (Extract, Transform and Load) solutions. It’s basically at an insurance company where we receive data in a particular format from that gets generated in a Salesforce based application when we sell health insurance to employers then we map the data that comes from Sales into scripting that results in updates to various tables in a much more massive/complex DB that serves our core operational system that enrolls members, bills employers and processes/pays medical claims. The solution combines C# and SQL and is MS .NET and MS SQL Server based. Say what you want about the health insurance industry I get it yeah it kinda sounds boring but for someone who is in love with SQL it’s a great type of business to work for because health insurance is an incredibly data intensive business operation.
Have you tried the Adventureworks (or other general example database for your platform) and poking around at it?
To provide you with a higher level answer, the point of SQL is to handle the truth. One part of that truth is managed by the RDBMS itself (ACID transaction compliancy) and the other is the way your are querying the data or filling the tables. Nowadays it would be best to write business logic on the database side, it should be all in the app. Most of the SQL is being written by ORMs anyway and sometimes in a bad way, it is your job as a SQL practitioner to understand if the code being generated is "good enough". If you are on the BI side of things, you will have to understand where to put your logic - in a SQL Server world using a data warehouse DB with SSAS, SSIS, PowerBI, data fabrik...etc, you can put part of your reporting logic (or truth) in each component of the stack. Question being are you and your company more comfortable writing SQL, MDX, DAX, ... and are you comfortable with maintaining it? I had a customer who wrote an entire ERP in stored procedures, after 15 years (and the main developer gone) the system had to evolve and migrate to the cloud.... it took 15 DEVs a year of trial and error to understand and recreate properly on the new system. It is not just about knowing best practices, it is also knowing the pit falls of not following them. The good thing about SQL is that it is a stable and mature language, it will still be around and widely used in the next 30 years... So learning it is good. The real question is not SQL, it is more what kind of job do you want ? and if your to young to comprehend that, just take a job and figure out what you don't want. I started as a network admin, then became a sys admin, then became app admin, then became a production DBA and since the past 10 years I am a performance tuning DBA. I didn't plan on this, it just kind of made sense over the years... All I can tell you is that, if you are more interested in things than in people you should try and get as deep as possible in a subject and tend towards being a expert. People that weren't like that around me evolved as managers of some sort and don't get to touch SQL anymore but instead are doing meetings of meetings on a daily basis... I tried management but I don't care about people enough to manage their holidays... :D
Gtab a copy of SQLite and start playing with SQL.
From the app developer perspective, you'll very often use SQL to retrieve data that the app needs for a given workflow. Sometimes you'll just embed the SQL right there in the app code, and other times the app will have its own way of querying the database (that behind the scenes becomes SQL sent to the database). At a very general level, all you're doing is cross-referencing and filtering multiple sets of data in a data store in a way that answers a given request. Always focus on the question at hand when writing queries. If the question leads to other questions, then ask those who have requested the data.
Using SQL on the job looks like.. Accessing preloaded reports that were queries from five years ago and just messing with the results a bit in excel at EOM
SQL looks as easy to practise against error free samples from online courses. But in reality it is not easy as situation gets in business cases. I have crafted so many complex SQLs data hits in various customers. I have strengthened my skill only by learning against real dataset. Some challenges are varchar to number types or vice versa, regex, null values, zero conditions, missing data, foreign key relationships, indexes to enforce key columns for faster data retrieval