Taula de continguts

Introducció

Una consulta és una funció que es composa utilitzant 6 funcions que s’han d’escriure en un ordre concret, i que retorna 1 taula com a resultat.

Tingues en compte que només el select és obligatori, i que encara que s’escrigui primer, s’executa l'últim

select	Funció que filtra columnes
from	Funció que genera 1 taula a partir d’una o varies taules (join)
where	Funció que filtra files
group by	Funció que agrupa files pel valor d’una o varies columnes. El resultat són grups (no files)
having	Funció que filtra grups
order by	Funció que ordena les files pel valor d’una o varies columnes.

Per tant, quan comencem a crear una consulta començarem sempre pel from, després pel where, etc., seguint l'ordre d'execució.

Hem d'afegir select * perquè és obligatori que una consulta comenci per select:

select *
from departments join employees using(deparment_id)
where deparment_id = 100

Si utilitzem la funció group by, per seleccionar les columnes agrupades abans hem de transformar els grups en files utilitzant funcions d’agregació:

select deparment_id, count(*)
from departments natural join employees
group by deparment_id

A Beginner’s Guide to the True Order of SQL Operations. The SQL language is very intuitive. Until it isn’t. Over the years, a lot of people have criticised the SQL language for a variety of reasons because the lexical order of operations does not match the logical order of operations. We humans may sometimes (often) intuitively understand this ordering difference.

where

What Is the SQL WHERE Clause?. The WHERE clause is a fundamental part of SQL. This clause filters results and applies conditions to SQL queries. It is most commonly used alongside the SELECT statement.

How to Write a WHERE Clause in SQL. We explain how to use the SQL WHERE clause with practical examples. If you have just started learning SQL and want to know how to retrieve or work with only a specific portion of the data stored in your tables, then this article is for you!

Using AND, OR, and NOT Operators in SQL AND, OR, and NOT are important logical operators in SQL. They help you combine the conditions used to filter records. They are most commonly used in conjunction with the WHERE or HAVING clauses.

group by

group by s'utilitza per juntar aquelles files de la taula que tenen el mateix valor en totes les columnes seleccionades.

Quan comences a construir una consulta amb group by ja no pots utilitza `select * from t1p , sino que has de fer servir aquesta plantilla per començar a treballar (on x és la columna que vols fer servir per agrupa les files). D’aquesta manera, també podras veure el número de filas agrupades per cada valor de x.

select x, count(x)
from t1
group by x

Comencen amb un exemple. La función from ens retorna una taula

from test

  x   y
  a   3
  c   2
  b   5
  a   7

group by x

  x   y
  a   3,7
  c   2
  b   5

El resultat són tres grups.

Quines columnes podem seleccionar?

select *
ERROR! El valor y de la fila a no és correcte

El resultat han de ser files: els grups "b" i "c" es poden convertir en files, però com es fa amb el grup "a"?

select x

x a c b

Si elimino la columna y puc convertir tots els grups en files. select x, sum(y)

x sum a 10 b 5 c 2

Si agrego tots el valors y de cada grup, tambè puc convertir els grups en files. Per tant haig de fer servir una funció d'agregació, que rep una llista de valors i en retorna un.

En aquest cas, utilitzo sum.

Using GROUP BY in SQL Now that you know SQL’s core commands, power up your queries with the GROUP BY clause and aggregate functions.

GROUP BY Clause: How Well Do You Know It?. The database can do different kinds of computations: it can add and subtract, multiply and divide, it can do computations on dates, it can count rows and sum row values, and so on. It can also do quite sophisticated statistics. The GROUP BY clause is the most basic way to compute statistics in SQL. It can be quite tough for beginners but it is really powerful. Let's look at the details of the GROUP BY clause, starting with the basics.

5 Examples of GROUP BY. When you start learning SQL, you quickly come across the GROUP BY clause. Data grouping—or data aggregation—is an important concept in the world of databases. In this article, we’ll demonstrate how you can use the GROUP BY clause in practice. We’ve gathered five GROUP BY examples, from easier to more complex ones so you can see data grouping in a real-life scenario. As a bonus, you’ll also learn a bit about aggregate functions and the HAVING clause.

having

SQL HAVING Tutorial. Learn how to use the SQL HAVING clause to filter groups using your own specified conditions.

What Is the SQL HAVING Clause?. Are you learning SQL? Are you wondering what you can use the HAVING clause for? Or, perhaps, have you tried to use WHERE on your GROUP BY aggregates? You are in the right place! In this article, we explain how to use HAVING with plenty of examples.

HAVING vs. WHERE in SQL: What You Should Know. This article is about SQL’s WHERE and HAVING clauses. Both clauses are part of the foundations of the SQL SELECT command. They have similar uses, but there are also important differences that every person who uses SQL should know. Let’s see what’s behind the HAVING vs. WHERE debate.

order by

What Does ORDER BY Do?. When analyzing data, it often helps to have rows ordered in a specific way. In this article, I’ll use multiple examples to show how SQL ORDER BY sorts data according to one or more columns in ascending or descending order.

How ORDER BY and NULL Work Together in SQL. Do NULL values come first or last when you use ORDER BY? Are they considered higher or lower than non-NULL values? In this article, I’ll explain how different relational databases treat NULL values when sorting output and how to change the default behavior of the ORDER BY clause.

A Detailed Guide to SQL ORDER BY. It’s often necessary to display the output of SQL query in specific order rather than arbitrarily. In this article, I’ll explain the many ways you can do it with the SQL ORDER BY clause.

How to Sort Records with the ORDER BY Clause. Relational databases don't store records in alphabetical, numerical, ascending, or in any particular order. The only way to order records in the result set is to use the ORDER BY clause. You can use this clause to order rows by a column, a list of columns, or an expression. You can also order rows using the CASE expression.

join

7 SQL JOIN Examples With Detailed Explanations Do you need to join several tables to get the necessary result set? The SQL JOIN is a basic yet important tool used by data analysts working with relational databases. And I understand it can be difficult to choose from the zillions of introductory guides to joins. In this article, I will focus on real-world examples with detailed explanations.

An Illustrated Guide to the SQL CROSS JOIN. What is an SQL CROSS JOIN statement? When should you use it? When shouldn't you use it? This post will tell you what you need to know about CROSS JOIN.

An Illustrated Guide to the SQL INNER JOIN What is an SQL INNER JOIN, and how does it work? Let's find out!

An Illustrated Guide to the SQL Self Join What is an SQL self join and how does it work? When should it be used? We’ll provide answers to those questions!

How to Join Two Tables in SQL. Querying data from multiple tables is very common when working with relational databases. It is not difficult if you know how to use the dedicated SQL operators for doing this. In this article, you will learn how to join two tables by using WHERE and by using a special operator JOIN, and you will see how to filter rows in the result set.

Learning JOINs With Real World SQL Examples. The JOIN statement lets you work with data stored in multiple tables. In this article, I’ll walk you through the topic of JOIN clauses using real world SQL examples.

How Do You Get Rid of Duplicates in an SQL JOIN?. Do you have unwanted duplicates from your SQL JOIN query? In this article, I’ll discuss the possible reasons for getting duplicates after joining tables in SQL and show how to fix a query depending on the reason behind the duplicates.

What Is the Difference Between WHERE and ON in SQL JOINs. When you join tables in SQL, you may have conditions in an ON clause and in a WHERE clause. Many get confused by the difference between them. In this article, we will discuss this topic by first reminding you the purpose of the ON and WHERE clauses then by demonstrating with examples which types of conditions should be in each of these clauses.