From a DML perspective, SQL foresees four constructs:
- The select construct allows retrieving data from the relational database.
- The update and insert construct allows to modify and up date.
- The delete construct allow removing data.
SQL SELECT
By using the options we can ask specific and complex questions to the database. The SELECT construct allows retrieving date from the database. The result is a multi-set and not a set. In a set there are no duplicates and the elements are not ordered. In a multi-set the elements are not ordered but there can be duplicates elements in a multi-set.
SQL will not eliminate duplicates for many reasons:
- Duplicates elimination is an expensive operation.
- The user may also want to see duplicate tuples in a query result.
- Duplicates may also be considered by aggregate functions.
Simple queries
Simple queries are SQL statements that retrieve data from only one table. The FROM component in the SELECT instruction contains only one table name. The SELECT components then extract the columns required. It can contain many expressions which generally refer to the names of the columns we want to manipulate.
It selects SUPNR, SUPNAME, SUPADDRESS, SUPCITY and SUPSTATUS
FROM SUPPLIER. This query selects all information from the supplier table. So it provides a complete table doc. In case all columns of a table
are requested, we can use a short cut: SELECT*FROM SUPPLIER.
As mentioned before, the result of a SQL is a multi-set. There is an option which allows removing the duplicates from a multi-set: distinct select. SELECT DISTINCT SUPNR FROM PURCHASE_ORDER.
A missing WHERE clause indicates no condition on tuple selection. However, when a WHERE clause is added to the SQL statement, it specifies selection conditions to indicate which table rows could be selected. A number of operates could be used in the where clause such as comparison operators, Boolean, BETWEEN, IN, LIKE and NULL operator.
Queries with aggregate function
Several expressions can be specified in the SELECT clause. This may also include aggregate function which are used to summarize information from database tuples. Ex : count, min, max, sum, average, standard deviation and variance.
Queries with GROUP BY/HAVING
Here, we want to apply the aggregate functions to subgroups of tuples in a table, where each subgroup consists of the tuples that have the same value for one or more columns. In other words, by using the GROUP BY clause, rows will be grouped when they have the same value for a specific column. The HAVING class can be added to retrieve the values of only groups of rows that satisfy certain conditions. It can only be used in a combination with a GROUP BY class and it can also include aggregate functions as the ones seen before.
Queries with ORDER BY