Features
Pricing
Academy
Learn SQL
Log in
Sign up
MENU
CLOSE
Home
Features
Pricing
Academy
Learn SQL
Log in
Sign up
All Articles
Design Fundamentals
Design Patterns
Example Models
Database Internals
SQL
Others
sql
Grouping, Rolling, and Cubing Data
by Jeffrey J. Keller
25 Jan 2017
The first two articles in this series highlighted SQL Server’s ability to organize data into user-defined windows and its aggregate functions . Part 3 will focus on other methods of aggregating and organizing data using built-in SQL Server features – specifically, its grouping functions.For consistency, the same base data will be used as in the first two parts of this series.SQL Server’s Grouping FunctionsThe GROUP BY clause is the basis for SQL Server’s grouping functions: ROLLUP, CUBE, and GROUPING SETS. At its simplest, GROUP BY summarizes rows based on user-expressed conditions:
Read more
sql
A Gentle Introduction to Common SQL Window Functions
by Aldo Zelen
27 Sep 2016
Mastering SQL analytical functions is a necessity for an aspiring BI/DWH professional. In this article, we’ll explore the history of SQL in a BI environment. We’ll also introduce some of the more common SQL analytical functions, including RANK, LEAD, LAG, SUM, and others.SQL can be used for business intelligence (BI) applications that combine data retrieval, analysis, and computation. These computations are more complex than what we see in OLTP systems. If you’re interested in getting into data warehousing (DWH) or business intelligence, you need to go beyond plain SQL and start digging into the analytical functions (also known as window functions, windowing functions, or OVER clauses).
Read more
sql
Window Functions: Part Two: Aggregating Data
by Jeffrey J. Keller
9 Aug 2016
In part one of this series on SQL Server window functions, the functionality of the OVER() clause was discussed . This article will focus on aggregate functions. The majority of these can be used in conjunction with OVER. Some, like SUM() and AVG(), are widely used. Others — including VARP() and CHECKSUM_AGG() — are not as well-known, but are potentially quite useful.OVER defines the user-specified range on which a function is applied. This can be combined with many SQL functions, allowing easy separation of data into defined windows within a single SQL query.
Read more
sql
Window Functions in SQL Server: Part One: The OVER() Clause
by Jeffrey J. Keller
21 Jun 2016
Window functions were first introduced in standard SQL 2003 and then extended in SQL 2008. Since SQL Server 2005, Microsoft has been steadily improving window functions in the database engine. These functions perform an aggregate operation against a user-defined range of rows (the window) and return a detail-level value for each row.Well-known aggregate functions includeSUM,AVG,MIN,MAX, and many others. Recent versions of SQL Server have introduced ranking and analytic functions such as
Read more
sql
5 Tips to Optimize Your SQL Queries
by Adrian Więch
5 May 2015
The SQL Language seems easy to learn – the commands follow a simple syntax and don’t describe the specific algorithms used to retrieve data. The simplicity might, however, be deceptive. Not all the database functions operate with the same efficiency. Two very similar queries can vary significantly in terms of the computation time. This article presents some of the best practices that can greatly boost your SQL queries.1. Learn How to Create Indexes Properly
Read more
sql
SQL Subqueries
by Patrycja Dybka
18 Sep 2014
The article describes what a subquery is and what these useful statements look like. We will cover basic examples with the IN, EXISTS, ANY, and ALL operators, look at subqueries in from and where clauses, and explore the difference between correlated and nested subqueries.First, let’s start with an example database. To present some of these statements we need to have an example table and fill it with some data.What is a Subquery?
Read more
sql
S.Q.L or Sequel: How to Pronounce SQL?
by Patrycja Dybka
3 Sep 2014
SQL has been around for decades and supports a many billion dollar market. However, many people still struggle with just how to pronounce the term SQL. Is it “S.Q.L” [ˈɛs kjuː ˈɛl] or is it “sequel” [ˈsiːkwəl]?SQL… Where it all startedLet’s start at the beginning.Relational databases came into existence with E.F. Codd’s 1970 publication “A Relational Model of Data for Large Shared Data Banks.” While Codd’s ideas were remarkable for the time, in San Jose, California, two colleagues named
Read more
sql
SQL vs. Mongo query
by Patrycja Dybka
26 Aug 2014
In recent times, NoSQL databases have become a hot topic and have gained a crowd of advocates. Indeed, when NoSQL database started to arise, SQL-to-NoSQL converters arose with them as well. But unfortunately, thanks to the code conversion possibility, SQL supporters gained unquestionable evidence that in many cases the good, old SQL is much easier to use. Especially when it comes to queries.I performed a quick Google search and found plenty of nice converter tools. Here is one of them:
Read more
sql
Can SQL Help Solve Crossword Puzzles?
by Patrycja Dybka
21 Aug 2014
Everyone has solvedcrossword puzzlesand has certainly had some problems finding an appropriate word. Thanks to SQL, it is ridiculously simple to quickly dispel your crossword doubts and give you the correct answers. Of course, Google is commonly known as a universal cure for many doubts, but handling the problem yourself is much more rewarding.Recently I came across some simple and interesting examples from Andrew Cumming’s book “SQL Hacks.” These examples won’t make a huge impression on those who are programming experts, but less-experienced code wranglers could find them interesting. In this article I will try to present a funny approach to solving casual problems using the power of SQL according to Andrew Cumming’s book.
Read more
sql
How to Select the First Row in a Group?
by Agnieszka Kozubek
21 May 2014
Often you want to select a single row from each GROUP BY group. PostgreSQL has a statement especially for that: SELECT DISTINCT ON.Let’s say I want to select one weather report for each location.The query retrieves one weather report for each location.You can use several expressions in SELECT DISTINCT ON statement.For each resource the query retrieves a single backup data for each week.SELECT DISTINCT ON with ORDER BYThe undecorated SELECT DISTINCT ON selects one row for each group but
Read more
««
«
1
2
»
»»