Vertabelo Database Modeler

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:

sql

A Gentle Introduction to Common SQL Window Functions

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).

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.

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

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

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?

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

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:

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.

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

1
2