Explains how and why to limit the size of a query result set
- [Instructor] Whenever you run a query in SQL, the server devotes a bit of time just to you. A server is just a computer, remember, and it can be become overwhelmed and hang just like your home PC. This tends to happen under two circumstances. First, if a very large query is sent to it, or second, if there are too many demands made of it at the same time. This latter one is quite possible if you're at work. You might've noticed some systems being slow at peak periods. Well it's often the database that's slowing things down as lots of people are bombarding the system at the same time.
Now it's easy to write a query that returns a million rows, but it's often the case that you only need to see a few of them. If you're testing a query for example, or if you just need to see the type of data that you're going to be returned, you can help your colleagues and yourself by limiting the size of the queries you run. Basically, it speeds up the query. It's really easy to do. If we have a look at the payment table, you can see that there are 16,000 or so rows in the table, and you can also see that the query took naught point naught naught naught five seconds to run.
Now we're going to limit the query to 25 rows. And you can see that took about half the time, naught point naught naught naught three seconds in this case. Now the terminology differs for different versions of SQL, so if you're using MySQL, you'll use limit, and it's the same for PostGreSQL. For Oracle, you refer to rownum, which doesn't need to be a field in the table. It's kind of automatically assigned, and with Microsoft, you would say select top.
Join Emma Saunders as she shows you how to design and write simple SQL queries for data reporting and analysis. Review the different types of SQL, and then learn how to filter, group, and sort data, using built-in SQL functions to format or calculate results. Learn a bit about data types and database design. Discover how to perform more complex queries, such as joining data together from different database tables. Last but not least, Emma shows how to save your queries as views, so you can run them again and again.
- Using different versions of SQL
- Retrieving data with SELECT statements
- Filtering and sorting your results
- Transforming results with built-in SQL functions
- Grouping SQL results
- Merging data from multiple tables
- Identifying data types, and how to make sense of your database design
- Saving SQL queries