Main content
Course: Computer programming - JavaScript and the web > Unit 3
Lesson 2: More advanced SQL queries- More complex queries with AND/OR
- Challenge: Karaoke song selector
- Querying IN subqueries
- Challenge: Playlist maker
- Restricting grouped results with HAVING
- Challenge: The wordiest author
- Who issues SQL queries?
- Calculating results with CASE
- Challenge: Gradebook
- Project: Data dig
© 2024 Khan AcademyTerms of usePrivacy PolicyCookie Notice
Who issues SQL queries?
Just with this simple data set of exercise logs, we've managed to come up with many different and useful queries. Now I want to take a break from querying to talk about who would usually be doing all this querying, and for what purpose.
Let's imagine that we have an exercise app with thousands of users, storing data similar to the exercise logs table we've been using. That app lets users enter daily logs and view their progress on a personal dashboard:
For an app like that, there are a few folks on the team that might be issuing SQL queries:
Software engineers:
They are the ones building the backend (the server-side logic) and the frontend (the HTML/CSS/JS that renders data and forms). The software engineers would use SQL to communicate on the server-side with the database that's storing all of the user's data. They would need to know how to do any queries needed by the frontend. For example, if users saw a dashboard when they logged on of how much exercise they did that day, the engineer would need to figure out how to do a
SELECT filtered by date and user. They would need to figure out how to insert data and update it (which we'll discuss much later)Data scientists:
They are the ones analyzing the data, trying to learn more about users, maybe coming up with suggestions about how to help them exercise more. They need a deep understanding of
SELECT statements, because they need to do very complex queries to do the analysis they're interested in. For example, they might use a SELECT to analyze what percentage of users were more likely to do more exercise if they started off exercising in the morning, maybe using CASE and GROUP BY.Product management:
They are the decision makers at a company, the people that look at the data, talk to users, look at the market, and try to understand how to improve a product to get more users, make users happier, or make more money. They often need an understanding of SQL queries so they can look at usage statistics and try to understand what parts of a product are being used the most, which parts are being used in surprising ways, and what isn't being used at all. They might use a
SELECT to look at how many users even use the "heart_rate" field at all, if they're debating getting rid of it.Since all these people are working at the same company, they can and should be sharing knowledge with each other about how to use SQL on their databases. Not everyone needs to be a SQL expert, but in my experience, many people at a company can benefit from having a basic understanding of SQL, and some of them may want to go into more advanced features as they get more sophisticated.
On that note, keep going to learn more advanced features...!
Want to join the conversation?
How is data security and data base integrity?(36 votes)
Did you mean how is the data integrity and data security requirements implemented in SQL using databases ? Data security is taken care of by the communication stack. That's just a fancy way of saying that the speaker and listener (of data here) are required to ensure they talk in a manner that no one else understands them. The speaker and listener here are the user and the Database. The methods used include various types of convoluted ways of information transport mechanisms. Over the internet, that's usually HTTPS and SSL. Over normal systems connected by a network, that might be SSH. It depends what the communication channel is.
Data integrity is ensured by enforcing a set of rules while designing the database schema. Since we are only learning how to query SQL here, this hasn't really been taken up. The closest we have been to data integrity requirements is the Primary Key that we associate with the 'id' column. That column ensures that no two rows of data in a table are the exact same. Please note that the only 'requirement' the Primary key places on the rows is that the primary key itself can't have exact duplicates anywhere in the table. All other columns might as well be the same on all rows. If you want to understand how data integrity works, please look up Triggers and Constraints for your database software of choice.(175 votes)
What skills do I need to learn to be a data-scientist? (And does someone want to give me a job? Ha!)(25 votes)
Data scientist needs to have excellent knowledge of Mathematics and Statistics. The person should be fluent with numbers and have sharp memory while playing around with them. Should posses quick calculative skills. He should have ready to solve a problem attitude. Now in order to get a job one needs excellent coding skills for data analysis such as R or Python and have good excel capabilities as well. The person should learn Machine learning modelling and solve challenges including Regression and Classification datasets. Kaggle is the best platform for such learning.(95 votes)
Is there any way to program a data-base localy on your PC, so it acts like a exl sheet? and save it as a file out send out side of the PC? to a harddive, then put the harddive a way, then run it after some time and have the same data in it?(14 votes)
Hmm, I believe if you set up a local host on your computer (ex:
WAMP for Windows
MAMP for Mac
LAMP for Linux
XAMPP for all), you can make a database with phpMyAdmin, and everything is stored locally on your machine, and it will be saved to your hard drive, and you might even be able to export it although I'm not 100% sure.(25 votes)
can u combine SQL with other languages. if so, how?(15 votes)
You can use PHP or ASP (PHP is more common) to access SQL databases, and that is the common way to access SQL from webpages, although some have started using AJAX (JavaScript-ish).(16 votes)
- What am I doing wrong on the last exercise?
SELECT author, SUM(words) AS "total_words" FROM books GROUP BY total_words HAVING total_words >1000000;(0 votes)- select author, sum(words) as total_words from books GROUP BY author
HAVING total_words>1000000;(29 votes)
Do project managers get training in SQL or do they do a sort of learn on the job type basis? If they don't have previous SQL or database background is it more desirable for employers when someone does have that knowledge?(5 votes)
There are project managers that just manage projects (Money, Time and Resources), they need only project management skills.
But if you are involved in any project that contain databases I'd say it wouldn't be bad to know some SQL. Because that tells that you understand how databases work.(14 votes)
- What are the differences between the many database softwares using SQL, like MySQL and PostgreSQL, for example? Thanks!(12 votes)
Does this mean that Computer Scientists are obliged to play all these roles?(7 votes)- What other skills do I need to develop to become a business analyst apart from SQL?(4 votes)
- As far as programming goes, consider learning other flavors of SQL, such as MySQL or SQL Server. Then, maybe a noSQL database, such as Mongo, Cloudant, Dynamo, or Cosmos. You could also look into Python or R.
In addition to programming, you will need to be able to think critically and analytically about data. You will need to communicate effectively with colleagues and customers. You will need to create comprehensible reports and dashboards (perhaps learn Tableau, Excel, Google Sheets, Crystal Reports, and PowerBI).
You may be called upon to present your findings. So, public speaking and presentation software like PowerPoint, Google Slides, or Canva would also be good skills to have.(9 votes)
- What about data analysts? is ther eany diference with data scientists?(8 votes)
