Section 1
Preview this deck
The source of the rows for the query is specified after the ___ keyword.
Front
| 5 | 0 | ||
| 4 | 0 | ||
| 3 | 0 | ||
| 2 | 0 | ||
| 1 | 0 |
Active users
1
All-time users
1
Favorites
0
Last updated
6 years ago
Date created
Mar 1, 2020
Cards (109)
Section 1
(50 cards)
The source of the rows for the query is specified after the ___ keyword.
FROM
Simply speaking, DML has only four types of statements:
- INSERT is used to put new data into the database - UPDATE is used to change the data - DELETE is used to delete the data - SELECT is used to retrieve the data
The source of the rows for the query can be any of:
- Table - View - Function - Subquery - Values
Data Definition Language (DDL)
used to create and manage the structure of the data
SQL expressions can contain:
- Column names (most of the cases) - Constants - Operator invocations - Parenthesis to control operations precedence - Function calls - Aggregate expressions (we will discuss them later) - Scalar subqueries - Type casts - Conditional expressions
If the name of the schema is included in the ___ setting or if the object belongs to the current user's schema, then it is not required to use the schema name when referring to the object.
search_path
There is ___ of the SELECT statement that is always mandatory.
no part
ALL returns ___ the rows, this is the default.
all
The FROM clause is ___ in PostgreSQL but in other RDBMS such as Oracle the FROM keyword may be ___.
optional, required
Structured Query Language (SQL) is used to
- set up the structure of the database - set up the structure of the database - query the database
Comments are quoted in pairs of ___ . Also, the whole line of code can be commented by using ___.
/ and /, double dash ( -- )
Inner join can be shown as a filled area on the diagram, like this:
It is possible to nest commands, using a result of one command as an input for another command. That nested queries are called ___.
subqueries
Right outer join example:
SELECT * FROM car_portal_app.a RIGHT JOIN car_portal_app.b ON a_int=b_int;
SQL is a ___ language.
declarative
SQL ___ case sensitive.
is not
SQL statements can contain:
- Keywords determine what exactly it is required from the database to be done - Identifiers refer to the objects in the database—tables, their fields, functions, and so on - Constants are parts of expressions whose values are specified directly in the code - Operators determine how the data is processed in the expressions - Special characters, such as parenthesis, brackets, commas, and so on, which have other meanings than simply being an operator - Whitespaces separate words from each other - Comments are used to describe a particular line of code
Full outer join:
Asterisk (*) represents ___ from all the tables specified in the FROM clause.
all the fields
Scalar subqueries or scalar queries are queries that return exactly ___ column and ___ records.
one, one or zero
It is ___ to use the same name for several columns in a single SELECT query.
possible
conditional expression example
SELECT CASE WHEN now() > date_trunc('day', now()) + interval '12 hours' THEN 'PM' ELSE 'AM' END;
Data Manipulation Language (DML)
used to manage the data itself
Identifiers are the names of the ___.
database objects
It is possible to join a table with itself. It is called ___.
self-join
In general, SQL consists of three parts:
- Data Definition Language (DDL) - Data Manipulation Language (DML) - Data Control Language (DCL)
There are two kinds of syntax of string constants in PostgreSQL:
- single quoted constants like in SQL standard - PostgreSQL-specific dollar-quoted constants
The joining conditions could be specified in the ___ clause but in most cases it is better to put them into a ___ clause to make it more visible that they are here for joining and not for filtering the result of the join, though there is no formal difference.
WHERE, FROM
Full outer join example:
SELECT * FROM car_portal_app.a FULL JOIN car_portal_app.b ON a_int=b_int;
It ___ possible to join more than two tables.
is
Type casts have several syntax patterns with the same meaning:
- CAST (<value> AS <type>) - <value>::<type> - <type> '<value>' (only applicable for string constants) - <type> (<value>)
In many cases, after the rows are taken from the input tables, they should be filtered. It is done via the ___ clause.
WHERE
After the SELECT keyword, one should specify the list of fields or expressions to get from the database. This list is called ___.
Select-list
When DISTINCT is specified, only ___ rows from the input dataset will be returned.
unique
Data Control Language (DCL)
used to control access to the data
Objects such as tables or views can be referred by the name of ___ where it belongs to.
the schema
When records are selected from both tables, we get the ___:
combinations of all their rows
PostgreSQL supports three types of implicitly typed constants: ___, ___ and ___.
numbers, strings, bit strings
SQL commands are terminated by a ___.
semicolon ( ; )
Right outer join:
In general, SELECT queries do not change the data in the database and could be considered as read-only, but there is an exception. If a ___ function is used in the query, then the data can be changed by the function.
volatile
Cross join example:
SELECT * FROM car_portal_app.a CROSS JOIN car_portal_app.b;
Left outer join:
SELECT queries can have different sources:
- tables - views - functions - VALUES command
Left outer join example:
SELECT * FROM car_portal_app.a LEFT JOIN car_portal_app.b ON a_int=b_int;
The JOIN condition may be specified using any of these three ways:
- <first table> JOIN <second table> ON <condition> - <first table> JOIN <second table> USING (<field list>) - <first table> NATURAL JOIN <second table>
Inner join example:
SELECT * FROM car_portal_app.a JOIN car_portal_app.b ON a_int=b_int;
SELECT statements or SELECT queries or just queries are used to ___ the database.
retrieve data from
The logic of SQL implies that the whole algorithm is executed ___, as ___ statement.
all at once, one
If one tries to concatenate a string with a number, PostgreSQL will convert the ___ to a ___ automatically.
number, string
Section 2
(50 cards)
There is a special construct, that can be used to check the equivalence of expressions taking NULL into account:
IS NOT DISTINCT FROM
NULL means the ___.
absence of any value
All set operations by default remove duplicated records as if ___ is used. To avoid this and return all the records, the ___ keyword should be used, which is specified after the name of the set operation.
SELECT DISTINCT, ALL
___ and ___ check for pattern matching using regular expressions.
SIMILAR, ~
HAVING clause is very similar to the WHERE clause but only ___ functions are allowed there.
aggregating
B-tree indexes which are most commonly used, ___ NULL values.
do not index
DEFAULT keyword in the VALUES list example (2 options)
INSERT INTO car_portal_app.a (a_text) VALUES (default); INSERT INTO car_portal_app.a DEFAULT VALUES;
There are three set operations:
- UNION - INTERSECT - EXCEPT
The syntax of the WHERE condition is ___ as in the expressions in the Select-list. This is ___ for PostgreSQL.
the same, specific
It is possible to use several set operations in one statement:
SELECT a, b FROM t1 UNION SELECT c, d FROM t2 INTERSECT SELECT e, f FROM t3;
How to skip several rows at the beginning of the output?
This is done by the using keyword OFFSET.
INSERT INTO ... VALUES example
INSERT INTO car_portal_app.a (a_int) VALUES (6);
NULL = NULL
NULL
To check the expression for having NULL value, a special operator is used:
IS NULL
When subqueries are used ___ they must have an alias.
in the FROM clause
In Oracle EXCEPT is called ___.
MINUS
between example
x BETWEEN a AND b
Only two wildcards can be used in the pattern: ___ for exactly one character (or number) and ___ for any number of any characters, including an empty string.
underscore, percent sign
In contrast to some other databases in PostgreSQL, empty string ___ NULL.
is not
When the records to insert are taken from another table or view, a SELECT query is used instead of the VALUES clause:
INSERT INTO car_portal_app.a SELECT * FROM car_portal_app.b;
The ___ statement is used to insert new data into tables in the database.
INSERT
It ___ possible to refer to the internal elements of one subquery from inside of another.
is not
Subqueries ___ refer to the elements of main query.
can
LIKE and ILIKE are the same except that the first is ___ and second is not.
case-sensitive
By default, the INSERT statement returns the number of inserted records. But it is also possible to return the inserted records themselves, or some of their fields. The output of the statement is then similar to the output of the SELECT query. The RETURNING keyword, with the list of fields to return, is used for this:
INSERT INTO car_portal_app.a SELECT * FROM car_portal_app.b RETURNING a_int;
Sometimes it is necessary to limit the output of the query to a certain number of rows and discard the rest. That is done by specifying that number after the ___ keyword.
LIMIT
The most frequently used aggregating functions:
- count - sum - max - min - avg - corr - stddev - string_agg
group by example
SELECT a.marke, a.model FROM car_portal_app.car_model a INNER JOIN car_portal_app.car b ON a.car_model_id=b.car_model_id GROUP BY a.marke, a.model;
having example
SELECT a.marke, a.model FROM car_portal_app.car_model a INNER JOIN car_portal_app.car b ON a.car_model_id=b.car_model_id GROUP BY a.marke, a.model HAVING count(*)>5;
It is possible to insert multiple records using VALUES syntax:
INSERT INTO car_portal_app.a (a_int, a_text) VALUES (7, 'seven'), (8, 'eight');
The ___ clause is used for grouping.
GROUP BY
In many cases it is required to have the result set sorted. This is done with the ___ clause.
ORDER BY
IS NOT DISTINCT FROM example
SELECT * FROM t WHERE a IS NOT DISTINCT FROM b
The syntax of set operations is as follows:
<query1> UNION <query2>; <query1> INTERSECT <query2>; <query1> EXCEPT <query2>;
To insert several records one should use ___ clause.
VALUES
SQL allows the use of array types that mean several elements as a whole in one single value:
a > ANY (ARRAY[x, y, z])
UNION
This appends the result of one query to the result of another query.
After each item of the ORDER BY list it is possible to specify if the order should be ascending or descending. That is done by specifying the keywords ___ or ___ after the expression.
ASC, DESC
There are some other operators or expressions returning Boolean values that are used in conditional expressions:
- Comparison operators - Pattern matching operators - The OVERLAPS operator - Row and array comparison constructs - Subquery expressions - Any function returning Boolean or convertible to Boolean values
The COALESCE function takes any number of arguments of the same data type or compatible types. It returns the value of ___:
the first of its arguments that is not null
In general, aggregate functions ___ NULL.
ignore
INTERSECT
This returns the records that exist in the results of both queries.
The expression IN is used for checking if a value ___.
equals to any of the values from a list
In fact, in PostgreSQL the VALUES clause is a standalone SQL command. Therefore, it can be used as a subquery in any SELECT query:
SELECT * FROM (VALUES (7, 'seven'), (8, 'eight')) v;
EXCEPT
This returns the records from the first query that do not exist in the result of the second query—the difference.
The difference between them is that SIMILAR uses regular expression syntax defined in ___, while ~ uses ___ regular expressions.
SQL standard, Portable Operating System Interface (POSIX)
The filtering of the groups is done by the ___ clause.
HAVING
NULLIF takes two arguments and returns NULL if ___.
they are equal
The ___ statement is used to change the data in the records of a table without changing their number.
UPDATE
overlaps example
SELECT 1 WHERE (date '2014-10-15', date '2014-10-31') OVERLAPS (date '2014-10-25', date '2014-11-15');
Section 3
(9 cards)