Data Manipulation Language (DML)

Data Manipulation Language (DML) is responsible for carrying out the tasks of querying or manipulating the data, organized by the appropriate data model

Includes commands to query, modify, delete, or define tuples (rows) that are stored in the relationships (tables) of the database

There are four basic operations:

• SELECT
• INSERT
• UPDATE
• DELETE

Basic definition of schemes

Alias Operation

SQL provides an operation to rename using an alias for both relationships and attributes

It has the following syntax: original_name AS alias

The clause AS it is optional although if we write it will be clearer because it informs us that we are defining an alias

Situations where it is useful to use an alias:

• Where we have two relationships in the clause FROM who have the same name for their attributes

  In these situations there may be ambiguity and we can use the alias operation to disassemble the attributes and make it clear which relationship they belong to

• When we have a calculated operation that generates an attribute in the clause SELECT

• When we have a calculated operation that generates an attribute after applying a aggregation operation

To access its attributes, you must use the alias followed by a period and the attribute name

SELECT

You can use the command SELECT to query the data stored in a database relationship

It will return a set of tuples from that relationship

The most common form of consultation will be:

\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_1, r_2, \cdots, r_m\\ \text{WHERE} P

Where every A_i represents an attribute, each r_i a relationship and P it's a predicate

So an SQL query will have the following equivalent relational algebra expression:

\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma p(r_1 \Theta r_2 \Theta \cdots \Theta r_m) )

If the clause is omitted WHERE, the predicate P it's always True

But unlike relational algebra expression, the result of an SQL query can contain multiple repeated tuples

When we introduce more than one relationship into the aclausula FROM, SQL will form a Cartesian product of all included relationships

The selection of relational algebra is then carried out using the predicate of the WHERE and then projects the result on the attributes of the clause SELECT

SELECT clause

SELECT DISTINCT number_acount, name_branch, balance
FROM acount

The example resolved the query to get all the accounts in the relationship acount with attributes number_acount, name_branch, balance

In this case, when using DISTINCT we are forcing the repeated to be removed

SELECT ALL number_acount, name_branch, balance
FROM acount

The example resolved the query to get all the accounts in the relationship acount with attributes number_acount, name_branch, balance

In this case, when using ALL we are forcing the repeated ones not to be eliminated

SELECT *
FROM acount

The example replaced the command ALL by the symbol * and the attributes have been dispensed with, as the result is equivalent

SELECT number_acount, name_branch, balance * 1500
FROM acount

The example resolved the query to get all the accounts in the relationship acount with attributes number_acount, name_branch, with an increase of 1500 in the balance

As can be seen, we can also use arithmetic expressions that operate on attributes

SELECT number_acount, name_branch, balance * 1500 increase
FROM acount

The example created a calculated attribute (non relationship) from those operations, giving it a name or alias to that operation, in this case it's called increase

FROM clause

The clause FROM itself defines a Cartesian product of the relationships that appear in the consultation

Because natural meeting is defined in terms of a Cartesian product, a selection, and a projection, it is relatively easy to write an SQL expression for the natural meeting

We have to write it in relational algebra as:

\prod_{A_1, \cdots, A_2, \cdots, A_n} (r_1 \Theta r_2 \Theta \cdots \Theta r_m)

SELECT c.number_acount, c.balance
FROM acount c, client cl
WHERE c.number_acount = cl.number_acount AND c.name_branch = 'Navacerrada'

The example resolves the query to get all customers who have an account at the 'Navacerrada‘

For this purpose, relationships have been introduced acount (you have been assigned the alias c) and client (you have been assigned the alias cl)

Only with the clause FROM we couldn't solve the query

Since the Cartesian product is made on each of the possible couples that can be formed with the relationship acount and client

Giving us repeated results and also all those who were not from the branch of 'Navacerrada‘

That's why the condition that relates both relationships, the attribute, has been added number_acount

After adding it, we will have only the values that belong to the relationship, although it can still have been repeated

And equality for the attribute name_branch with the value 'Navacerrada‘

JOIN Clause

SELECT c.number_acount, c.balance
FROM acount c
JOIN client cl ON c.number_acount = cl.number_acount
WHERE c.name_branch = 'Navacerrada'

The example used the clause JOIN that will give us back an equivalent result

The clause JOIN allows meeting operations, which take two relationships and return another relationship

The clause can normally be used FROM for external meetings or for any situation where a meeting can be used

To distinguish whether the meeting is external, the keyword will be used OUTER and for the intern INNER

Both are optional, although it makes it clearer what kind of meeting we're using

There are four types of meeting:

• INNER JOIN

  Returns all tuples when there is at least one match in both relationships

  SELECT c.number_acount, c.balance
  FROM acount c
  INNER JOIN client cl ON c.number_acount = cl.number_acount
  WHERE c.name_branch = 'Navacerrada'

  The example used the clause INNER JOIN that will give us back an equivalent result

  Because as we said, the keyword INNER was optional

  However, we now have clearer that the meeting was internal

• LEFT OUTER JOIN

  Returns all tuples in the left relationship, and the matching tuples of the right relationship

  SELECT c.number_acount, c.balance
  FROM acount c
  LEFT JOIN client cl ON c.number_acount = cl.number_acount
  WHERE c.name_branch = 'Navacerrada'

  The example used the clause LEFT JOIN that will return a result that is no longer equivalent, as they will appear nulls values

  We've dispensed with the keyword OUTER because the result is equivalent

  In this case LEFT JOIN It keeps all tuples in the left relation (acount)

  The tuples of the right relationship (client) will be displayed if there is a match with those on the left

  If values exist in the left relationship but not in the right relationship, it will display null

• RIGHT OUTER JOIN

  Returns all tuples in the relationship on the right, and the matching tuples of the relationship on the left

  SELECT c.number_acount, c.balance
  FROM acount c
  RIGHT JOIN client cl ON c.number_acount = cl.number_acount
  WHERE c.name_branch = 'Navacerrada'

  The example used the clause RIGHT JOIN that will return a result that is no longer equivalent, as they will appear nulls values

  We've dispensed with the keyword OUTER because the result is equivalent

  In this case RIGHT JOIN keeps us all the tuples of the right relationship (client)

  The tuples of the left relationship (acount) will be displayed if there is a match with those on the right

  If there are values in the right relationship but not in the left relationship, it will display null

• FULL OUTER JOIN

  Returns all tuples of the two relationships, left and right

  SELECT c.number_acount, c.balance
  FROM acount c
  FULL JOIN client cl ON c.number_acount = cl.number_acount
  WHERE c.name_branch = 'Navacerrada'

  The example used the clause FULL JOIN that will return a result that is no longer equivalent, as they will appear nulls values

  We've dispensed with the keyword OUTER because the result is equivalent

  In this case FULL JOIN returns all rows in the left table (acount) and the right table (client)

  It combines the outcome of the LEFT JOIN and RIGHT JOIN

  Null will appear on each of the tables alternately when there is no match

The type of assembly defines how to treat tuples of each relation that do not marry the tuple of the other relation

There are three types of conditions that apply to the meetings:

• meeting NATURAL

  It is used when the attributes which meet relationships have the same name

  SELECT c.number_acount, c.balance
  FROM acount c
  NATURAL JOIN client cl
  WHERE c.name_branch = 'Navacerrada'

  The example used the clause NATURAL JOIN which will return an equivalent result without using the condition ON

  Since the attribute number_acount It called equal relations acount and client

• condition ON

  Its use is similar to that of the clause WHERE, but it specializes in comparing attributes of both relations

• condition USING

  Its use is similar to that of the meeting NATURAL, because it is used when the attributes which meet relationships have the same name

  However, only return a result of the meeting, not all as the meeting does NATURAL

  SELECT c.number_acount, c.balance
  FROM acount c
  JOIN client cl USING(number_acount)
  WHERE c.name_branch = 'Navacerrada'

  The example used condition USING with the attribute number_acount, which will return a result that is no longer equivalent

  It will only return only the first match of each pair of the relationship, not all

Using conditions is mandatory meeting at external meetings, but is optional on the internal (if omitted, a Cartesian product is obtained)

WHERE clause

SELECT *
FROM acount
WHERE balance < 100

In the example, the query get resolved only accounts of the relationship acount that meet the condition introduced in this case in its attribute balance have a value less than 100

To achieve this we have used the clause WHERE, thanks to which we can filter the query to admitting a condition using the logical comparison operator <

SELECT *
FROM acount
WHERE name_branch = 'Navacerrada' AND balance < 100

In the example, the query get resolved only accounts of the relationship acount that meet the condition introduced in this case in its attribute balance have a value equal to 'Navacerrada' and its attribute balance have a value less than 100

To achieve this we have used the logical connector AND

BETWEEN Operator

SELECT *
FROM acount
WHERE name_branch = 'Navacerrada' AND balance >= 100 AND balance <= 1000

In the example, the query get resolved only accounts of the relationship acount that meet the condition introduced in this case in its attribute name_branch have a value equal to 'Navacerrada' and its attribute balance they have a value between 100 and 1000

SELECT *
FROM acount
WHERE name_branch = 'Navacerrada' AND balance BETWEEN 100 AND 1000

The example used an equivalent query, using the comparison operator BETWEEN, which simplifies us some clauses WHERE

SELECT *
FROM acount
WHERE name_branch = 'Navacerrada' AND balance NOT BETWEEN 100 AND 1000

In the example it used a query negating the comparison operator NOT BETWEEN, which will return those values ​​outside that range

LIKE operator

SELECT *
FROM acount
WHERE name_branch = 'Navacerrada'

In the example, the query get resolved only accounts of the relationship acount that meet the condition introduced in this case in its attribute name_branch have a value equal to 'Navacerrada‘

SELECT *
FROM acount
WHERE name_branch LIKE 'Navacerrada'

The example used an equivalent query, using the comparison operator LIKE, which simplifies us some clauses WHERE on allowing the use of patterns with strings

For the description of patterns we can use two special characters:

• %

  The percentage matches any substring

• _

  Underlining matches any character

These patterns can be combined to obtain different types of queries:

SELECT *
FROM acount
WHERE name_branch LIKE 'Nava%'

In the example it used the comparison operator LIKE using the pattern % after the text, which will return the values ​​that begin with the string 'Nava‘

SELECT *
FROM acount
WHERE name_branch LIKE '%Nava%'

In the example it used the comparison operator LIKE using the pattern % before and after the text, which will return values ​​that include as substring 'Nava‘

SELECT *
FROM acount
WHERE name_branch LIKE '___'

In the example it used the comparison operator LIKE using the pattern _ repeated three times, which will return values ​​that are exactly three characters

SELECT *
FROM acount
WHERE name_branch LIKE '___%'

In the example it used the comparison operator LIKE using the pattern _ repeated three times and pattern %, Which will return to those values ​​that have the least three characters

SELECT *
FROM acount
WHERE name_branch LIKE 'Nava\%rrete' escape '\'

In the example it used the comparison operator LIKE using the pattern % together with the special escape character '\' which will return those values ​​containing 'Nava%rrete‘

The escape character (which can be anything we choose) is used before special characters pattern to indicate that this character be treated as a normal character and not as a pattern

It is defined using the keyword escape

SELECT *
FROM acount
WHERE name_branch NOT LIKE 'Nava%

In the example it used the comparison operator NOT LIKE using the pattern %, which will return those values ​​that do not contain 'Nava%rrete‘

In this way we can also look for inconsistencies, but denying the operator LIKE

Nulls values

SQL allows the use of null values ​​to indicate the absence of information on the value of an attribute

You can use the special keyword NULL in a predicate to check if a value is null

SELECT *
FROM acount
WHERE name_branch IS NULL

In the example, the query get resolved all accounts whose name NO branch has been filled

SELECT *
FROM acount
WHERE name_branch IS NOT NULL

The example resolved the query to get all accounts whose branch name has been filled in

As can be seen, it can also be checked the absence of nulls using the logical operator NOT

Since the predicate clauses WHERE may include Boolean operations such as AND, OR and NOT on the results of the comparisons, values NULL Boolean operations expand

ORDER BY clause

Sometimes we need to return the information of the tuples in a certain order

SQL allows you to sort the attributes by using the clause ORDER BY

SELECT *
FROM acount
ORDER BY name_branch ASC

In the example it used the comparison operator ASC with clause ORDER BY for the attribute name_branch, Which will return the values ​​sorted in ascending order (in alphabetical order because it was an alphanumeric attribute) by the attribute name_branch

SELECT *
FROM acount
ORDER BY name_branch DESC

In the example it used the comparison operator DESC with clause ORDER BY for the attribute name_branch, Which will return the values ​​sorted in descending order (in alphabetical order because it was an alphanumeric attribute) by the attribute name_branch

The sort type returned by the comparison operators ASC and DESC depend on the type of the attribute because not ordered in the same way a number, a string or a date

If you need to order more than one attribute, we'll separated by commas and followed the comparison operator (ASC or DESC )

The default sorting method is ASC, is optional and could do without writing

Operations on sets

SQL allows operations on sets of relational algebra union \cup, intersection \cap and difference \neg

To use them we will have the only restriction that the names of the attributes of the relationships involved must be equal

UNION operation

The most common form of consultation will be:

\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_1\\ \text{WHERE} P\\ \text{UNION}\\ \text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_2\\ \text{WHERE} Q

Where every A_i represents an attribute, each r_i a relationship and P, Q are predicates

So a SQL query that contains a union will have the following equivalent expression of relational algebra:

\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma p (r_1) ) \cup \prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma q (r_2) )

If the clause is omitted WHERE, the predicate P or Q (It depends on what is omitted), it is always true

SELECT number_acount
FROM acount
UNION
SELECT number_acount
FROM client

The example query resolved all account numbers that exist in the bank or have a customer associated with them

Unlike the clause SELECT the operation UNION eliminates duplicate values

For this reason it is good idea to use operation UNION to create keys, because with SELECT would not get unique keys (allows repeated)

SELECT number_acount
FROM acount
UNION ALL
SELECT number_acount
FROM client

The example query resolved all account numbers that exist in the bank or have a customer associated with them

In the use operator ALL in conjunction with the operation UNION, we are allowing it to behave like a SELECT, as it supports repeated

INTERSECT operation

The most common form of consultation will be:

\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_1\\ \text{WHERE} P\\ \text{INTERSECT}\\ \text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_2\\ \text{WHERE} Q

Where every A_i represents an attribute, each r_i a relationship and P, Q are predicates

So a SQL query that contains an intersection will have the following equivalent expression of relational algebra:

\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma p (r_1) ) \cap \prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma q (r_2) )

If the clause is omitted WHERE, the predicate P or Q (It depends on what is omitted), it is always true

SELECT number_acount
FROM acount
INTERSECT
SELECT number_acount
FROM client

The example query resolved all account numbers that exist in the bank and have a customer associated with them

The operation INTERSECT eliminates duplicate values ​​and returns only those values ​​that match the relationship acount and in client at once, not just one of them

SELECT number_acount
FROM acount
INTERSECT ALL
SELECT number_acount
FROM client

The example query resolved all account numbers that exist in the bank and have a customer associated with them

In the use operator ALL in conjunction with the operation INTERSECT, we are allowing it to behave like a SELECT, as it supports repeated

EXCEPT operation

The most common form of consultation will be:

\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_1\\ \text{WHERE} P\\ \text{EXCEPT}\\ \text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_2\\ \text{WHERE} Q

Where every A_i represents an attribute, each r_i a relationship and P, Q are predicates

So a SQL query that contains a difference will have the following equivalent expression of relational algebra:

\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma p (r_1) ) \cap \neg (\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma q (r_2) ) )

If the clause is omitted WHERE, the predicate P or Q (It depends on what is omitted), it is always true

SELECT number_acount
FROM acount
EXCEPT
SELECT number_acount
FROM client

The example query resolved all account numbers that exist in the bank and do not have a customer associated with them

The operation EXCEPT eliminates duplicate values ​​and returns only those values ​​that match the relationship acount but do not match client

SELECT number_acount
FROM acount
EXCEPT ALL
SELECT number_acount
FROM client

The example query resolved all account numbers that exist in the bank and do not have a customer associated with them

In the use operator ALL in conjunction with the operation EXCEPT, we are allowing it to behave like a SELECT, as it supports repeated

IN operation

SQL lets you check the membership of tuples to a relation which is equivalent to check the membership of a set of relational algebra

The most common form of consultation will be:

\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_1\\ \text{WHERE} P\\ \text{IN}\\ (\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_2\\ \text{WHERE} Q)

Where every A_i represents an attribute, each r_i a relationship and P, Q are predicates

So a SQL query that contains a membership will have the following equivalent expression of relational algebra:

\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma p (r_1) ) \in \prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma q (r_2) )

If the clause is omitted WHERE, the predicate P or Q (It depends on what is omitted), it is always true

SELECT number_acount
FROM client
WHERE number_acount IN (SELECT number_acount
                        FROM acount
                        WHERE name_branch = 'Navacerrada')

The example resolves the query to get all customers who have an account at the 'Navacerrada‘

Previously we solved it using the clause JOIN

But as you can see, we can also solve it using set theory

Membership will return the values and if there are none, it will show null

SELECT number_acount
FROM client
WHERE number_acount NOT IN (SELECT number_acount
                        FROM acount
                        WHERE name_branch = 'Navacerrada')

Resolves example query to get all customers who do not have accounts on the branch 'Navacerrada‘

To deny membership will return all other values ​​and should be none, will show null

SELECT number_acount
FROM acount
WHERE name_branch IN ('Logroño', 'Navacerrada')

The example resolves the query to get all customers who have an account at the 'Logroño‘ ó ‘Navacerrada‘

As can be seen, belonging or not belonging apart from a subquery as in the previous examples, also it supports sets listed

Whenever these sets are listed in the same domain as the attribute that you want to check membership

Comparison operators

SQL provides the ability to compare sets using subqueries

SOME operator

The most common form of consultation will be:

\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_1\\ \text{WHERE} P\\ \text{SOME}\\ (\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_2\\ \text{WHERE} Q)

Where every A_i represents an attribute, each r_i a relationship and P, Q are predicates

So a SQL query that contains a membership will have the following equivalent expression of relational algebra:

\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma p (r_1) ) \approx \prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma q (r_2) )

If the clause is omitted WHERE, the predicate P or Q (It depends on what is omitted), it is always true

SELECT number_acount
FROM client
WHERE number_acount = SOME (SELECT number_acount
                        FROM acount
                        WHERE name_branch = 'Navacerrada')

Example resolves the query to get a client who has an account on the branch 'Navacerrada‘

Previously we solved using the operation IN

But as can be seen, we can also solve using a comparison of sets

In this case using the symbol = in front of the operator SOME to denote that they are equal

SELECT number_acount
FROM client
WHERE number_acount <> SOME (SELECT number_acount
                        FROM acount
                        WHERE name_branch = 'Navacerrada')

Example resolves the query to get a client who does not have an account at the branch 'Navacerrada‘

Although it might seem that is equivalent to the previous example NOT INT, the result may not be the same

We can also use the rest of the logical comparison operators

ANY operator

The operator can also be used ANY since it is equivalent to SOME

Early versions of SQL only allowed the operator ANY, but was added SOME to be more asemejase to natural language, as created any ambiguity in the English language

The most common form of consultation will be:

\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_1\\ \text{WHERE} P\\ \text{ANY}\\ (\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_2\\ \text{WHERE} Q)

Where every A_i represents an attribute, each r_i a relationship and P, Q are predicates

So a SQL query that contains a membership will have the following equivalent expression of relational algebra:

\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma p (r_1) ) \approx \prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma q (r_2) )

If the clause is omitted WHERE, the predicate P or Q (It depends on what is omitted), it is always true

We can also use the rest of the logical comparison operators

SELECT number_acount
FROM client
WHERE number_acount = ANY (SELECT number_acount
                        FROM acount
                        WHERE name_branch = 'Navacerrada')

Example resolves the query to get a client who has an account on the branch 'Navacerrada‘

Previously we solved using the operation SOME

As seen is fully equivalent and would use only for compatibility with older versions of SQL

ALL operator

The most common form of consultation will be:

\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_1\\ \text{WHERE} P\\ \text{ALL}\\ (\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_2\\ \text{WHERE} Q)

Where every A_i represents an attribute, each r_i a relationship and P, Q are predicates

So a SQL query that contains a membership will have the following equivalent expression of relational algebra:

\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma p (r_1) ) \forall \prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma q (r_2) )

If the clause is omitted WHERE, the predicate P or Q (It depends on what is omitted), it is always true

We can also use the rest of the logical comparison operators

SELECT number_acount
FROM client
WHERE number_acount > ALL (SELECT number_acount
                        FROM acount
                        WHERE name_branch = 'Navacerrada')

Resolves example query to get all customers who have accounts in the following branches'Navacerrada' (alphabetical)

Actually the use the symbol > in front of the operator ALL we are denoting that we look for all values that have an alphabetical order greater than 'Navacerrada‘

Repeated control

SQL lets you control whether a subquery has repeated tuples in its result

The most common form of consultation will be:

\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_1\\ \text{WHERE UNIQUE}\\ (\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_2, \cdots, r_n\\ \text{WHERE} P)

Where every A_i represents an attribute, each r_i a relationship and P is preached

So a SQL query that contains a membership will have the following equivalent expression of relational algebra:

\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma \exists \prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma p (r_2, \cdots, r_n) ) (r_1) )

If the clause is omitted WHERE, the predicate P, es siempre cierto

Formally assessment UNIQUE on a relationship is defined as false if and only if the ratio of two tuples r_i and r_k such that r_ik

Like checking r_i = r_k it is false if any attribute r_i or r_k is zero, it is possible that the outcome of UNIQUE is true even if several duplicates of the same tuple

Provided that at least one of the attributes of the tuple is null

SELECT cl.number_acount
FROM client cl
WHERE UNIQUE (SELECT c.number_acount
              FROM acount c 
              WHERE cl.number_acount = c.number_acount AND
                    c.name_branch = 'Navacerrada')

Resolves example query to get all customers who have at most one account at the branch 'Navacerrada‘

This has been introduced relations account (has been assigned the alias c) and client (has been assigned the alias cl)

Only with the clause FROM we couldn't solve the query

We could have used the clause JOIN, although there may still be repeated

Why it has added the operator UNIQUE relating the two relationships with the attribute number_acount and equality for the attribute name_branch with the value 'Navacerrada‘

After adding them, we only values ​​pertaining to the relationship

SELECT cl.number_acount
FROM client cl
WHERE NOT UNIQUE (SELECT c.number_acount
              FROM acount c 
              WHERE cl.number_acount = c.number_acount AND
                    c.name_branch = 'Navacerrada')

Resolves example query to get all customers who have more than one account at the branch office 'Navacerrada‘

As can be seen, the denial of UNIQUE we can check more than one repeated

Aggregation functions

The aggregation functions are functions that take a collection (a set or multiset) value as input and return a single value

SQL allows for five aggregation functions:

• AVG

  Allows the arithmetic mean of the collection, which must be numeric

  If there are nulls values at the entrance, they are ignored

  SELECT AVG(balance) average
  FROM acount

  In the example query is resolved to obtain the arithmetic mean of the balance of all accounts

  It was created a calculated attribute (not belonging to the relation) from these operations, assigning a name or alias to that operation, in this case it's called average

• MIN

  It allows determine minimum value of the collection, which is not required to be numerical

  If there are nulls values at the entrance, they are ignored

  SELECT MIN(balance) less
  FROM acount

  In the example query is resolved to obtain the lowest balance of all accounts

  It was created a calculated attribute (not belonging to the relation) from these operations, assigning a name or alias to that operation, in this case it's called less

• MAX

  It allows determine the maximum value of the collection, which is not required to be numerical

  If there are nulls values at the entrance, they are ignored

  SELECT MAX(balance) greater
  FROM acount

  In the example query is resolved to obtain the highest balance of all accounts

  It was created a calculated attribute (not belonging to the relation) from these operations, assigning a name or alias to that operation, in this case it's called greater

• SUM

  Allows the sum of the collection, which must be numeric

  If there are nulls values at the entrance, they are ignored

  SELECT SUM(balance) liquid
  FROM acount

  In the example query is resolved to obtain the total balance of all accounts

  It was created a calculated attribute (not belonging to the relation) from these operations, assigning a name or alias to that operation, in this case it's called liquid

• COUNT

  Counts the number of elements that make up the collection, which is not required to be numerical

  If there are nulls values at the entrance, they are taken as 0

  SELECT COUNT(balance) total
  FROM acount

  In the example, the query get resolved many accounts are there altogether

  It was created a calculated attribute (not belonging to the relation) from these operations, assigning a name or alias to that operation, in this case it's called total

GROUP BY clause

There are circumstances in which it would be desirable to apply the aggregation functions not only to a single set of tuples, but also a group of sets of tuples

In SQL we can specify by clause GROUP BY

The most common form of consultation will be:

\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r\\ \text{WHERE} P\\ \text{GROUP BY} A_1, \cdots, A_2, \cdots, A_n\\ \text{HAVING} Q

Where every A_i represents an attribute, each r_i a relationship and P, Q are predicates

So a SQL query containing a group will have the following equivalent expression of relational algebra:

\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma q (\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma p (r) ) )

If the provisions are omitted WHERE or HAVING, the predicate P or Q (It depends on what is omitted), it is always true

SELECT name_branch, COUNT(balance) total
FROM acount
GROUP BY name_branch, balance

In the example, the query get resolved there are many accounts in total for each branch

It was created a calculated attribute (not belonging to the relation) from these operations, assigning a name or alias to that operation, in this case it's called total

In order for the grouping operation can be performed without errors we use the same order of attributes in both the clause SELECT as in GROUP BY

In case you are not going to intervene in the group, we can omit these attributes placing them on the right of those that will be used

HAVING clause

Sometimes we need a condition that applies to groups rather than tuples

To express such queries use the clause HAVING

SELECT name_branch, COUNT(balance) total
FROM acount
GROUP BY name_branch, balance
HAVING name_branch LIKE 'Navacerrada'

In the example, the query get resolved there are many accounts in total branch 'Navacerrada‘

It was created a calculated attribute (not belonging to the relation) from these operations, assigning a name or alias to that operation, in this case it's called total

It has been applied on the attribute condition name_branch with the value 'Navacerrada' using clause HAVING to apply on the grouping and not on the relationship

In the event that a clause would WHERE and one HAVING:

1. SQL first apply the predicate clause WHERE
2. The result will be divided into groups that satisfy the predicate clause HAVING

SELECT name_branch, COUNT(balance) total
FROM acount
GROUP BY name_branch, balance
HAVING name_branch LIKE 'Navacerrada' AND COUNT(balance) > 100

In the example, the query get resolved there are many accounts in total branch 'Navacerrada' and have a balance greater than 100

It was created a calculated attribute (not belonging to the relation) from these operations, assigning a name or alias to that operation, in this case it's called total

It has been applied on the attribute condition name_branch with the value 'Navacerrada‘

Additionally the condition applied on the attribute balance applying the grouping operation COUNT with the proviso that they are older than 100

Both have been on the clause aplicacadas HAVING

As can be seen, they can be applied without problems aggregation operations within clause HAVING but also they appear in clause SELECT

INSERT

Newly created relationships are initially empty

You can use the command INSERT to add data to the relationship

The most common way to insert query will be:

\text{INSERT INTO r}\\ \text{VALUES} (A_1, A_2, \cdots, A_n)

Where every A_i It represents an attribute, r relationship and each P_i it's a predicate

INSERT INTO acount
VALUES ('C-9732', 'Navacerrada', 1200)

The example has solved the query to add the C-9732 has the branch 'Navacerrada' with a balance of 1200 €

The values are specified in the order in which the corresponding attributes are related in the relationship schema

INSERT INTO acount (balance, number_acount, name_branch)
VALUES (1200, 'C-9732', 'Navacerrada')

The example has changed the order in which the corresponding attributes are related in the relationship schema and that is why the data has been entered in a mandatory manner, in the new order that we have specified

In this way we can choose in what order we enter the data

In the case of not introducing an attribute, SQL will try to enter a default or null value and if the primary key, notify the error and prevent the update

INSERT INTO cliente
       SELECT number_acount, 'Pedro Almodovar'
       FROM acount
       WHERE name_branch = 'Navacerrada'
       EXCEPT
       SELECT number_acount
       FROM client

In the example, the query has decided to add the client 'Pedro Almodovar' for all account numbers that exist in the branch'Navacerrada' and they do not have a client associated with them

Instead of specifying a tuple it has been used as a subquery clause SELECT will allow us to specify a set of tuples

The most common form of subquery for insertion will be:

\text{INSERT INTO} r_1\\ \text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_2\\ \text{WHERE} P

Where every A_i It represents an attribute, r_i relationships and P it's a predicate

The clause is evaluated first SELECT and are inserted in the relation client tuples obtained from the subquery

We must be careful to the nested subqueries use, since abusing them, could create an infinite loop subqueries and insertion would be made infinitely, being locked data base

Luckily management systems databases have mechanisms or tools for mass insertion in a controlled manner avoiding blocking of the database

UPDATE

You can use the command UPDATE to modify the values ​​of a set of tuples existing relationship

The most common form of update query is:

\text{UPDATE r}\\ \text{SET} A_1 = P_1, A_2 = P_2, \cdots, A_n = P_n

Where every A_i It represents an attribute, r relationship and each P_i it's a predicate

UPDATE acount
SET balance = 100

In the example it has been updated relationship called acount, in particular to the attribute balance we have changed their value 100

The command will update all tuples of the relationship acount

UPDATE acount
SET balance = 100
WHERE number_acount LIKE 'C-9732'

In the example they have been updated only tuples that satisfy the condition introduced in this case in its attribute number_acount have a value equal to 'C-9732‘

Case operator

The order of two instructions UPDATE It is important because if the order of the upgrade instructions changes we can get inconsistent data

To solve this problem we have the operator CASE

The most common form of update query is:

\text{UPDATE r}\\ \text{SET} A_1 = P_1, A_2 = P_2, \cdots, A_n = P_n

Where every A_i It represents an attribute, r relationship and each P_i is a predicate which can be replaced by the following predicate Q:

\text{CASE}\\ \text{WHEN} P_1 \text{THEN} r_1\\ \text{WHEN} P_2 \text{THEN} r_2\\ \cdots\\ \text{WHEN} P_n \text{THEN} r_n\\ \text{ELSE} r_0\\ \text{END}

Where every P_i each predicate can be satisfied, each r_i the result to be used in updating

If not met any of the predicates, the result would apply r₀

UPDATE acount
SET balance = CASE
                WHEN balance <= 100 THEN balance * 0.05
                WHEN balance = 500 THEN balance * 0.07
                WHEN balance > 1000 THEN balance * 0.25
                ELSE balance * 0.01
            END

In the example it has been resolved update query to add 0.05 to the balance when it is less than or equal to 100 with a modification of 0.05, when it is equal to 500 with a change of 0.07 when it is greater than 1000 with a modification of 0.25 and a change of 0.01 in any other case

Clause has been used CASE to solve for the equate with the attribute balance which it was the value update

DELETE

You can use the command DELETE to erase tuples from a relationship

DELETE FROM acount

The example has deleted the relationship called acount

The command will clear all tuples from the relationship acount

And only in this relationship, because it is not able to work on several

The most common form of consultation will be deleted:

\text{DELETE FROM r}\\ \text{WHERE} P

Where r It represents the relationship and each P it's a predicate

DELETE FROM acount
WHERE balance < 100

In the example, only tuples that meet the introduced condition have been deleted, in this case those in their attribute balance have a value less than 100

It should be borne in mind that the command DELETE first check each tuple of the relation acount and then erase all tuples that meet the condition

It is important to perform all checks before erasing since the order in which tuples are processed can vary the erase operation