Lab 2

From bd_en
Jump to: navigation, search

Objectives:

  • The SELECT phrase: Operators, WHERE and ORDER BY clauses

In order to be able to run the examples inserted throughout the text, download and execute now [| this script]

Filtering and ordering data: WHERE and ORDER BY clauses in the SELECT phrase

Selecting data from one table

When one executes a SELECT command, he/she can decide which columns and which rows should be returned.

To obtain all the columns and all the rows from a table, run the following query:

SELECT * FROM <table_name>;

Exercise: return the content of table STUDENTS.

To return a subset of the columns, instead '*' you should supply a list of column names separated by comma. As an example, the following query extracts only students' first and last names:

SELECT fname, lname FROM students;


When, for any reason, you dislike the column name appearing in the result, you can modify it by using aliases in the SELECT phrase. The alias must be inserted immediately after the column name and, optionally (but recommended for legibility) may be preceded by the "AS" keyword. If the alias is composed of several terms containing thus spaces, it must be put between quotation marks. For example, the following query will show a table containing instructors' last names with the header renamed to "Instructor name"

SELECT lname AS "Instructor name" FROM instructors;

Observation: The AS keyword is optional. In case the alias consists of only one term, quotation marks are not necessary; quatation marks are necessary only when the alias contains spaces or special characters and when we want the characters to respect the case (to be case sensitive). As an example, try to run the following query:

SELECT lname AS Instructor name FROM instructors;


If there are several similar entries/records in the table, all of them will be shown. For example, extract our students' years of study by running the query:

SELECT year FROM students;

The year of study will be returned for each student/record in the table. If we want to reduce the output by showing only once each value, we will use the "DISTINCT" keyword:

SELECT DISTINCT year FROM students;

The DISTINCT keyword can appear only once in the SELECT list and acts at the tuple level, not at the attribute level. Run and analyze the output for the following query:

SELECT DISTINCT year, groupno FROM students;


Observation: Although SQL reserved keywords were written with uppercase letters, SQL is not a case-sensitive language. We will use on this wiki (and hopefully also when solving exercises during the labs) a simple convention: uppercase letters for reserved words and lowercase letters for identifiers. Be aware that SQL distinguishes between uppercase and lowercase letters in the case of strings stored in a table. For example, if you execute SELECT * FROM courses WHERE course_title='LOGICS'; on our table, it will not return any rows.

Observation: A SQL command can be written on several lines. It must end with a semicolon.

Sometimes, instead of showing row data stored in the table, we would like to process the data and show the results. These where operators come in. If for example, we would like to see how much each student would earn if the scholarship is increased by 10RON, you may use the following query:

SELECT fname, lname, scholarship + 10 FROM students;


Exercise: Show half of the scholarship which is firstly increased by 100 RON. Take care of operators precedence.

NULL values

Sometimes, for some records, some attributes/columns cannot be assigned a value (i.e. not all students receive scholarships; a 0 value is not always the same thing with a missing value, in our case it may indicate that the student was selected for a scholarship but for the moment there are insufficient funds; an unexpected thing would happen if all scholarships would be increased with 100 RON: a student not earning any scholarship and with the table field set to 0 would end-up earning 100 RON). If no value is set to a field of a record, this field/attribute will be assigned the NULL value. A null value used in an algebraic expression generate also a null value as result.

Concatenating strings

To concatenate two strings we can use the || operator. The result is a new string. Let's see some examples:

SELECT 'Student '||fname||' '||lname||' is enrolled in the year of study '|| year ||'.' AS info FROM students;

Observation: All strings mus be put between apostrophe marks. The only place where we use quotation marks is the alias.

Filtering rows

We already saw how to filter duplicate rows using the DISTINCT keyword. To restrict further the data that will be shown, we can use the WHERE clause of the SELECT statement.

This is optional and, when used, it will always follow after the name of the queried table and will specify a filtering condiction. For example, we could show only students in the first year of study by running:

SELECT fname, lname FROM students WHERE year = 1;

The conditions that will be used in the WHERE clause are logical expressions that must return one of two possible values: TRUE or FALSE. These expressions may use any arithmetic or string operator but also logical operators. The next example will return the students that earn scholarships.

SELECT fname, lname FROM students WHERE scholarship IS NOT NULL;

If we want to further filter the data and show only the first year students that receive scholarships, we make use of logical operators in order to use two filtering conditions:

SELECT fname, lname FROM students WHERE scholarship IS NOT NULL AND year = 1;

Observation: when evaluating complex logical expressions, the priority of the logical operators decreases in the enumeration: NOT, AND, OR.

A very useful operator for expressing filtering conditions for strings is LIKE. It allows us to formulate regular expressions to match strings stored in the database. These regular expressions may use symbol "%" (percent) which holds for any group of characters while "_" (underscore) holds for exactly one character. For example, if we want to select all the students whose names end with "escu", we can use:

SELECT fname, lname FROM students WHERE lname LIKE '%escu';

Observation: when you need to use % or _ as part of a string, these must be preceded by the escape character \ (backslash).

Ordering the results of a query

To order the rows returned by a query, we can use the ORDER BY clause followed by a list of columns (or expressions built over column_names). Optionally, after each column name we can use one keyword from {ASC, DESC} specifying increasing/decreasing order; by default the order is ascending.

Let's order the students by the year of study:

SELECT fname, lname, year FROM students ORDER BY year ASC;

When there are several students in an year of study we would like to order them by their study_group:

SELECT fname, lname, year FROM students ORDER BY year ASC, groupno DESC;

Let's build a query that has both the WHERE and the ORDER By clauses:

SELECT fname, lname, groupno FROM students WHERE year=1 ORDER BY groupno ASC;

Also, we can specify the field/attribute to be used for ordering by its position in the table definition:

SELECT * FROM students ORDER BY 2; -- orders the returned records by the family name

Operators

Usual arithmetic operators

Operator Description Example
* multiplication - for all numerical types SELECT value / 10 FROM grades;
/ division - for all numerical types SELECT value* 5 FROM grades;
summation - for all numerical types and DATE SELECT value + 1 FROM grades;
- subtraction - for all numerical types and DATE SELECT value-1 FROM grades;

If one of the operands is NULL the result is NULL

String operators

|| - concatenation

Ex: SELECT 'The name of the student is: ' || fname FROM students;

If one of the operands is NULL, the result consists of the other operand; if both operands are NULL the result is NULL.

Comparison operators

- return TRUE or FALSE, are applicable to all data types

Operator Description Example
=
>
>=
<
<=
<>
Binary operators, the usual meaning SELECT fname AS "Student" FROM students WHERE year = 2;
ANY/
SOME
Operator applicable to a list of values or to the result of a query in conjunction with one of the common comparison operators above; the meaning is as follows: the common operator takes as a second operand each of the values in the list; it returns TRUE if at least one of the values in the list returns TRUE; otherwise the result is FALSE. SELECT * FROM students WHERE year = SOME (2,3);
ALL Same as ANY/SOME, with the difference that the result is TRUE only if the common operator returns TRUE for all the values in the list. SELECT * FROM students WHERE year >= ALL (1, 2);
IN Checks if the first operand belongs to the list/set corresponding to the second operand. It is equivalent to „=ANY” SELECT * FROM students WHERE first_name IN ('Adrian', 'Alex');
NOT IN Returns FALSE if the first operand does not belong to the list/set corresponding to the second operand. It is equivalent to „<>ANY” SELECT * FROM students WHERE year NOT IN (1,2);
BETWEEN x AND y We can consider it as a ternary operator: it returna TRUE if the first operand satisfies in the same time the two conditions: >=x and <=y, where x and y correspond to the other two operands. SELECT fname, lname FROM students WHERE year BETWEEN 1 AND 3;
LIKE Binary operator, checks if the first operand satisfies the pattern expressed by the second operand. The pattern is a string that may contain one of the following special characters::
% any string, even the empty string (of length 0)
_ exactly one character
SELECT * FROM students WHERE fname LIKE '%andr%';
IS [NOT] NULL The only way to check for NULL SELECT * FROM students WHERE scholarship IS NOT NULL AND scholarship > 200;
EXISTS Checks if the subquery returns at least one row SELECT * FROM students WHERE NOT EXISTS (SELECT * FROM grades WHERE students.id_stud = grades.id_stud);
SELECT * FROM students WHERE EXISTS(SELECT* FROM grades WHERE value=10 AND students.id_stud=grades.id_stud);

Logical operators

Operator Description Example
NOT negation SELECT * FROM students WHERE NOT (scholarship IS NULL);
AND logical AND SELECT * FROM students WHERE year='3' AND scholarship IS NOT NULL;
OR logical OR SELECT * FROM students WHERE year='3' OR scholarship IS NOT NULL;

Set operators

- The operands are queries (query results – therefore tables), their definition and restrictions of applicability are those specified in the relational algebra.

Operator Description Example
UNION [ALL] Sets union. If specifying ALL the duplicates are not eliminated. SELECT lname FROM students WHERE year = '2'
UNION
SELECT lname FROM students WHERE year = '3';
INTERSECT [ALL] Sets intersection, duplicates are eliminated. SELECT last_name FROM students WHERE year = 2
INTERSECT
SELECT lname FROM students WHERE scholarship IS NOT NULL;
MINUS Distinct rows from the first query which do not exist in the second query. SELECT lname FROM students WHERE year = 3
MINUS
SELECT lname FROM students WHERE scholarship IS NULL;

Exercises