Once your database contains data, you can retrieve and view that data using a SELECT statement. Zen returns the data you request in a result table. Using SQL statements, you can do the following:
•Create temporary views or permanent (stored) views.
•Specify a selection list that lists the columns to retrieve from one or more tables in your database.
•Specify how to sort the rows.
•Specify criteria by which to group the rows into subsets.
•Assign a temporary name (alias) to a table.
•Retrieve data from one or more tables and present the data in a single result table (a join).
•Specify a subquery within a SELECT statement.
•Specify a restriction clause to restrict the rows Zen selects.
Views
A view is the mechanism for examining the data in your database. A view can combine data from multiple tables or can include only certain columns from a single table. Although a view looks like a table, it consists of a selected set of columns or calculations based on those columns from tables in your database. Thus, a view may contain data from columns in more than one table or data that is not actually in any table at all (for example, SELECT COUNT (*) FROM Person).
Features of Views
Following are some of the features of views:
•You can arrange the columns of a view in any order except that the variable-length column must be last. You can specify only one variable-length column.
•You can use a restriction clause to specify the set of rows that Zen returns in a view. The restriction clause lists criteria that the data must satisfy to be included in the view. For more information, see the section Restriction Clauses.
•You can design and customize views for each user and application that accesses the database. You can store these view definitions within the data dictionary for later recall.
•You can include any number of stored view names in a table list when retrieving, updating, or deleting data unless the view is a read-only view. In a read-only view, you can only retrieve data.
•In a stored view, you must provide headings for the view’s computed columns and constants and use those names in a list of column names when you retrieve data from the view.
Temporary and Stored Views
You can use SELECT statements to create temporary views or stored views. You use a temporary view only once and then release it. Zen places the definition of a stored view in the data dictionary (X$Proc) so you can recall the view later. You use CREATE VIEW statements to create and name stored views.
Each view name must be unique within a database and cannot exceed 20 characters. For more information about rules for naming views, see Inserting and Deleting Data.
Zen is case-sensitive when defining database element names. If you create a stored view named PhoNE, Zen stores the view name in the data dictionary as PhoNE. Zen is case-insensitive after you define the view name. After defining the stored view PhoNE, you can refer to it as phone.
Using stored views provides the following features:
•You can store frequently executed queries and name them for later use. The following example creates a stored view named Phones based on the Department table.
CREATE VIEW Phones (PName, PPhone) AS SELECT Name, Phone_Number FROM Department#
•You can specify the name of the stored view in table lists when retrieving, updating, and deleting data. The stored view behaves as though it is a table in the database, but it is actually reconstructed internally by the Zen engine each time it is used. The following example updates the phone number for the History Department in the Department table by referring to the stored view Phones.
UPDATE Phones SET PPhone = '5125552426' WHERE PName = 'History'#
•You can specify headings. A heading specifies a column name that is different from the name you defined for the column in the dictionary. The following example specifies the headings Department and Telephone for the stored view Phones.
CREATE VIEW Dept_Phones (Department, Telephone) AS SELECT Name, Phone_Number FROM Department#
You can use the headings in subsequent queries on the view, as in the following example.
SELECT Telephone FROM Dept_Phones#
If the selection list contains simple column names and you do not provide headings, Zen uses the column name as the column heading.
You must use headings to name constants and computed columns that you include in the view. The following example creates the headings Student and Total.
CREATE VIEW Accounts (Student, Total) AS SELECT Student_ID, SUM (Amount_Paid) FROM Billing GROUP BY Student_ID#
You must also use headings if you specify SELECT * from multiple tables that have any duplicate column names.
•You can create customized views for each user or application that accesses the database. You can store these view definitions within the data dictionary for later recall.
Read-Only Tables in Views
You cannotinsert, update, or delete rows from views that contain read-only tables. (Here the term update refers to insert, update, and delete; if a table is read-only, you cannot update it.) Some tables are read-only whether or not they are in views that are specified as such; such tables are intrinsically read-only, and you cannot update them. A table is read-only if it meets one of the following criteria:
•The database has security enabled, and the current user or user group has only SELECT rights defined for the database or the table.
•The data files have been flagged read-only at the physical file level (for example, using the ATTRIB command under DOS or Windows or the chmod command in Linux, macOS, or Raspbian).
•You execute a SELECT clause that creates a view and contains any of the following items:
•An aggregate function in the selection list.
•A GROUP BY or HAVING clause.
•A UNION.
•The DISTINCT keyword.
•You execute a SELECT statement that creates a view, and the table contains any of the following characteristics:
•It appears in a non-mergeable view that is in the SELECT statement’s FROM clause.
•It is a system table. System tables are always opened as read-only in a view, even if this overrides the view’s open mode.
•A column from the table appears in a computed column or a scalar function in the selection list.
•The table appears in the FROM clause of a subquery that is not correlated to the outermost query. A subquery can be directly or indirectly correlated to the outermost query. A subquery is directly correlated with the outermost query if it contains a reference to a column from a table and its specific occurrence in the outermost query’s FROM clause. A subquery is indirectly correlated to the outermost query if it is correlated to a subquery that is in turn directly or indirectly correlated to the outermost query.
•The open mode is read-only.
•You execute a Positioned UPDATE statement with any of the following keywords, without specifying FOR UPDATE:
ORDER BY
SCROLL
Mergeable Views
A view is mergeable if you can rewrite the SELECT statement using only base tables and columns.
For example, if you want to know how many students are in a class, you can define a view to calculate that. The view NumberPerClass is defined as follows:
CREATE VIEW NumberPerClass (Class_Name, Number_of_Students) AS SELECT Name, COUNT(Last_Name) FROM Person, Class, Enrolls WHERE Person.ID = Enrolls.Student_ID AND Class.ID = Enrolls.Class_ID
GROUP BY Name#
The view NumberPerClass is defined as follows:
SELECT * FROM NumberPerClass#
The view NumberPerClass is then mergeable because we can rewrite the SELECT statement as follows:
SELECT Name, COUNT(Last_Name) FROM Person, Class, Enrolls WHERE Person.ID = Enrolls.Student_ID AND Class.ID = Enrolls.Class_ID
GROUP BY NAME#
The view NumberPerClass is non-mergeable if you want to write a SELECT statement such as the following:
SELECT COUNT(Name) FROM NumberPerClass WHERE Number_of_Students > 50#
This statement is invalid for the view NumberPerClass. You cannot rewrite it using only base tables and base columns.
A view is mergeable if it does not contain any of the following characteristics:
•It refers to a non-mergeable view.
•It has an aggregate function in its selection list or a DISTINCT keyword, and it appears in the FROM clause of a SELECT statement that has an aggregate in its selection list.
•It has a DISTINCT keyword and appears in the FROM clause of a SELECT statement that has more than one item in its FROM clause, does not have an aggregate in its selection list, and does not have a DISTINCT keyword.
•It has an aggregate in its selection list and appears in the FROM clause of a SELECT statement that either has more than one item in its FROM clause or a WHERE clause restriction.
Selection Lists
When you use a SELECT statement to retrieve data, you specify a list of columns (a selection list) to include in the result table. To retrieve all the columns in a table or tables, you can use an asterisk (*) instead of a list of columns.
Note Avoid using * in place of the list. Using * can expose an application to potential problems if the number of columns or column sizes in a table changes. Also, it typically returns unnecessary data.
The following example selects three columns from the Class table.
SELECT Name, Section, Max_Size FROM Class;
The following example selects all columns from the Class table.
SELECT * FROM Class;
When retrieving data, Zen displays column names based on how you specify the names in the query.
•If you explicitly specify a column name, Zen returns it as you entered it. The following example specifies column names in all lowercase.
SELECT name, section, max_size FROM Class#
Zen returns the column names as follows:
"Name", "Section", "Max_Size"
These column names are headings for the returned data; they are not data themselves.
The following example defines aliases for the tables Department and Faculty.
SELECT d.Name, f.ID FROM Department d, Faculty f;
Zen returns the column names as follows:
"Name", "ID"
•If you use * to specify column names, they appear in all uppercase, as in the following example.
The following example defines aliases for the tables Department and Faculty.
SELECT * FROM Department d, Faculty f;
Zen returns the column names as follows:
"Name"
"Phone_Number"
"Building_Name"
"Room_Number"
"Head_Of_Dept"
"ID"
"Dept_Name"
"Designation"
"Salary"
"Building_Name"
"Room_Number"
"Rsch_Grant_Amount"
Sorted and Grouped Rows
Once you have decided what data to include in your result table, you can specify how to order the data. You can use the ORDER BY clause to sort the data, or you can use a GROUP BY clause to group rows by a certain column. When you group the data, you can also use aggregate functions to summarize data by group. For more information about aggregate functions, see Aggregate Functions.
The following example orders all rows by last name in the Person table of the sample database.
SELECT * FROM Person ORDER BY Last_Name#
The following example groups the results by the Building Name column in the Room table. This example also uses two aggregate functions, COUNT and SUM.
SELECT Building_Name, COUNT(Number), SUM(Capacity) FROM Room GROUP BY Building_Name;
Joins
A join results from a statement that combines columns from two or more tables into a single view. From this view, you can retrieve, insert, update, or delete data, provided it is not read-only.
Note This section primarily discusses joining tables using SELECT statements. However, you can also create joins with INSERT, UPDATE, and DELETE statements by applying a single statement to more than one table. SQL Engine Referencedescribes these SQL statements and how to optimize joined views.
You can retrieve data from tables by listing each table or view name in a FROM clause. Use a WHERE clause to specify one or more join conditions. A join condition compares an expression that references a column value from one table to an expression that references a column value from another table.
When data is properly normalized, most joins associate values based on some specified key value. This allows you to extract data in terms of referential integrity relationships. For example, if you want to know which professor teaches each class, you can create a join based on the Faculty ID, which is a foreign key in the Class table and a primary key in the Person table:
SELECT DISTINCT Class.Name, Person.Last_Name FROM Class, Person, Faculty WHERE Class.Faculty_ID = Person.ID AND Class.Faculty_ID = Faculty.ID;
This example joins two tables on the basis of common values in a common column: Faculty ID.
You can also join tables by making numeric comparisons between columns of like data types. For example, you can compare columns using <, >, or =. The following self-join on the Faculty table identifies all faculty members whose salary was higher than each faculty member (this would produce considerably more records than the faculty table contains):
SELECT A.ID, A.Salary, B.ID, B.Salary FROM Faculty A, Faculty B WHERE B.Salary > A.Salary;
Similar comparisons of dates, times, and so forth can produce many useful and meaningful results.
When joining columns, choose columns that are of the same data type when possible. For example, comparing two NUMERIC columns is more efficient than comparing a NUMERIC column with an INTEGER column. If the columns are not of the same data type but are both numeric or strings, Zen scans both the tables and applies the join condition as a restriction to the results.
When you use string type columns in a WHERE clause, one column in the join condition can be a computed string column. This allows you to concatenate two or more strings and use a join condition to compare them to a single string from another table.
The way in which Zen handles a join depends on whether the join condition contains an index column.
•If the join condition contains a column that is defined as an index, performance improves. Using the index to sort rows in the corresponding table, Zen selects only rows that meet the restriction clause condition.
•If the join condition does not contain a column that is defined as an index, performance is less efficient. Zen reads each row in each table to select rows that meet the restriction clause condition. To enhance performance, you can create an index in one of the tables before executing the join. This is especially helpful if the query is one that you perform often.
Joining Tables with Other Tables
To specify a join using a SELECT statement, use a FROM clause to list the relevant tables and a WHERE clause to specify the join condition and the restriction. The following example also uses aliases to simplify the statement.
SELECT Student_ID, Class_ID, Name FROM Enrolls e, Class cl WHERE e.Class_ID = cl.ID;
The next example joins three tables:
SELECT p.ID, Last_Name, Name FROM Person p, Enrolls e, Class cl WHERE p.ID = e.Student_ID AND e.Class_ID = cl.ID;
The next example retrieves a list of students who received a grade lower than a 3.0 in English.
SELECT First_Name, p.Last_Name
FROM Person p, Student s, Enrolls e, Class cl
WHERE s.ID = e.Student_ID
AND e.Class_ID = cl.ID
AND s.ID = p.ID
AND cl.Name = 'ACC 101'
AND e.Grade < 3.0;
In this example, the first three conditions in the WHERE clause specify the join between the four tables. The next two conditions are restriction clauses connected by the Boolean operator AND.
Joining Views with Tables
To join a view with one or more tables, include a view name in the FROM clause. The view you specify can include columns from a single table or from several joined tables.
Types of Joins
Zen supports equal joins, nonequal joins, null joins, Cartesian product joins, self joins, and left, right, and full outer joins.
For more information on the syntax of joins, see the following topics in SQL Engine Reference:
An equal join occurs when you define the two join columns as equal. The following statement defines an equal join.
SELECT First_Name, Last_Name, Degree, Residency FROM Person p, Student s, Tuition t WHERE p.ID = s.ID AND s.Tuition_ID = t.ID;
Nonequal Joins
You can join tables based on a comparison operation. You can use the following operators in nonequal joins:
<
Less than
>
Greater than
<=
Less than or equal
>=
Greater than or equal
The following WHERE clause illustrates a join that uses a greater than or equal operator.
SELECT Name, Section, Max_Size, Capacity, r.Building_Name, Number FROM Class cl, Room r WHERE Capacity >= Max_Size;
Cartesian Product Joins
A Cartesian product join associates each row in one table with each row in another table. Zen reads every row in one table once for each row in the other table.
On large tables, a Cartesian product join can take a significant amount of time to complete since Zen must read the following number of rows to complete this type of join:
(# of rows in one table) * (# of rows in another table)
For example, if one table contains 600 rows and the other contains 30, Zen reads 18,000 rows to create the Cartesian product join of the tables.
The following statement produces a Cartesian product join on the Person and Course tables in the sample database:
In a self join, you can specify a table name in the FROM clause more than once. When you specify a self join, you must assign aliases to each instance of the table name so that Zen can distinguish between each occurrence of the table in the join.
The following example lists all the people who have a permanent address in the same state as the person named Jason Knibb. The query returns the ID, first name, last name, current phone number, and e-mail address.
WHERE p1.First_Name = 'Jason' AND p1.Last_Name = 'Knibb' and p1.Perm_State = p2.Perm_State
Left, Right, Full Outer Joins
Information about outer joins can be found in SQL Engine Reference. See SELECT and JOIN.
Subqueries
A subquery (also known as a nested query) is a SELECT statement contained within one of the following:
•The WHERE clause or HAVING clause of another SELECT statement.
•The WHERE clause of an UPDATE or DELETE statement.
A subquery allows you to base the result of a SELECT, UPDATE, or DELETE statement on the output of the nested SELECT statement.
Except in correlated subqueries, when you issue a subquery Zen parses the entire statement and executes the innermost subquery first. It uses the result of the innermost subquery as input for the next level subquery, and so forth.
For more information about expressions you can use with subqueries, see SQL Engine Reference.
Subquery Limitations
A subquery in a WHERE clause becomes part of the search criteria. The following limits apply to using subqueries in SELECT, UPDATE, and DELETE statements:
•You must enclose the subquery in parentheses.
•The subquery cannot contain a UNION clause.
•Unless you use an ANY, ALL, EXISTS, or NOT EXISTS keyword in the WHERE clause of the outer query, the selection list of the subquery can contain only one column name expression.
•Either TOP or LIMIT is allowed within a single query or subquery, but not both.
You can nest several levels of subqueries in a statement. The number of subqueries you can nest is determined by the amount of memory available to Zen.
Correlated Subqueries
A correlated subquery contains a WHERE or HAVING clause that references a column from a table in the outer query’s FROM clause; this column is called a correlated column. To test the results from a subquery against the results from the outer query, or to test for a particular value in a query, you must use a correlated subquery.
Since the correlated column comes from the outer query, its value changes each time a row in the outer query is fetched. Zen then evaluates the expressions in the inner query based on this changing value.
The following example shows the names of courses that provide more credit hours than time actually spent in the class room.
SELECT c.Name, c.Credit_Hours FROM Course c WHERE c.Name IN (SELECT c1.Name FROM Class cl WHERE c.Name = cl.Name AND c.Credit_Hours > (HOUR (Finish_Time - Start_Time) + 1))#
To improve performance, you could rephrase the previous statement as a simple query.c.
SELECT c.Name, c.Credit_Hours FROM Class c1, Course c WHERE c1.Name = c.Name AND c.Credit_Hours > (HOUR (Finish_Time - Start_Time) + 1)#
Restriction Clauses
A restriction clause is an ASCII text string of operators and expressions. A restriction clause specifies selection criteria for the values in the columns of a view, limiting the number of rows the view contains. The syntax of certain clauses (such as WHERE or HAVING) requires using a restriction clause. A restriction clause can specify these conditions:
•Restriction condition – Compares an expression that references a column value to either a constant or another expression that references a column value in the same table.
•Join condition – Compares an expression that references a column value from one table to an expression that references a column value from another table.
A restriction clause can contain multiple conditions. It can also contain a SELECT subquery that bases search criteria on the contents of other tables in the database. The condition containing the subquery can contain the EXISTS, NOT EXISTS, ALL, ANY, and SOME keywords, or the IN range operator.
You can specify a restriction clause using a WHERE or HAVING clause in a SELECT, UPDATE, or DELETE statement.
Figure 1 provides an example restriction clause and illustrates restriction clause elements.
Figure 1 Example Restriction Clause
Restriction Clause Operators
Restriction clauses can use three types of operators:
•Boolean operators – Connect conditions in a restriction clause.
•Condition operators – Connect expressions to form a condition. A condition operator can be a relational or a range operator.
•Expression operators – Connect two expressions to form another expression. An expression operator can be an arithmetic or string operator.
Boolean Operators
Boolean operators specify logical conditions.
Table 38 Boolean Operators
Operator
Description
AND
If all search conditions connected with AND are true, the restriction passes.
OR
If at least one of the conditions connected by OR is true, the restriction passes.
NOT
If the condition is false, the restriction passes.
Condition Operators
A condition operator can be a relational or a range operator.
•Relational operator – Compares a column value with either another column value or a constant. If the value of the column is true, Zen selects the row.
•Range operator – Compares a column value with a specified range of values for the column. If the value of the column is true, the restriction passes, and Zen selects the row.
Value is not the defined NULL value for the column.
LIKE
Value matches specified string. You can substitute two wildcard characters for actual characters. The percent sign (%) represents any sequence of n characters (where n can be zero). The underscore ( _ ) represents a single character.
NOT LIKE
Value does not match specified string.
With the IN and NOT IN operators, the second expression can be a subquery instead of a column name or constant.
Expression Operators
Expression operators allow you to create expressions for computed columns using arithmetic or string operators. For more information, see Functions.
Restriction Clause Examples
The following examples demonstrate some of the restriction clause operators.
OR and Equal To (=)
The following example uses the relational EQUAL TO and boolean OR operators. It selects all rows in which the value of the State column is Texas or New Mexico.
SELECT Last_Name, First_Name, State FROM Person WHERE State = 'TX' OR State = 'NM'#
IN
The following example uses the IN operator. It selects the records from the Person table where the first names are Bill and Roosevelt.
SELECT * FROM Person WHERE First_name IN
(’Roosevelt’, ’Bill’)#
LIKE
The following example uses the LIKE operator:
SELECT ID, First_Name, Last_Name, Zip FROM Person WHERE Zip LIKE '787%';
This example retrieves records in the Person table where the zip code begins with ‘787’.
Functions
Once your database contains data, you can use functions on the data to return a result for a set of column values (using aggregate functions) or accept one or more parameters as input and return a single value (using scalar functions).
Aggregate Functions
An aggregate function is a function that returns a single result for a given set of column values. Zen supports the aggregate functions shown in Table 41.
Table 41 Aggregate Functions
Function
Description
AVG
Determines the average of a group of values. If the operand is not a DECIMAL, then AVG returns an 8-byte FLOAT. If the operand is a DECIMAL, AVG returns a 10-byte DECIMAL.
COUNT
Counts the number of rows in a specified group. COUNT always returns a 4-byte INTEGER.
DISTINCT
Include the DISTINCT keyword in your SELECT statement to direct the engine to remove duplicate values from the result. By using DISTINCT, you can retrieve all unique rows that match the SELECT statement's specifications.
MAX
Returns the maximum value of a group of values. MAX returns the same data type and size as the operand.
MIN
Returns the minimum value of a group of values. MIN returns the same data type and size as the operand.
SUM
Determines the sum of a group of values. If the operand is not a DECIMAL, then SUM returns an 8-byte FLOAT. If the operand is a DECIMAL, SUM returns a 10-byte DECIMAL.
For more information about each of these functions, see SQL Engine Reference.
Arguments to Aggregate Functions
For AVG and SUM functions, the argument to the function must be the name of a numeric column. The COUNT, MIN, and MAX functions can provide results on numeric or non-numeric columns.
You cannot nest aggregate function references. For example, the following reference is not valid:
SUM(AVG(Cost_Per_Credit))
You can use aggregate functions in an expression, as in the following example:
AVG(Cost_Per_Credit) + 20
You can also use an expression as an argument to a group aggregate function. For example, the following expression is valid:
AVG(Cost_Per_Credit + 20)
The aggregate functions treat null column values as significant. For example, on a table that contains 40 rows of data and 5 rows of null values, the COUNT function returns 45.
You can use the DISTINCT keyword to force Zen to treat all null column values as a single value. The following example calculates the average column value in the Grade column:
AVG(DISTINCT Grade)
The DISTINCT keyword affects the AVG, COUNT, and SUM functions. It has no effect on the MIN and MAX functions.
Aggregate Function Rules
You can use aggregate functions in a SELECT statement as follows:
•As items in the selection list.
•In a HAVING clause.
Generally, you use aggregate functions in a SELECT statement that contains a GROUP BY clause to determine aggregate values for certain groups of rows. However, if the SELECT statement does not contain a GROUP BY clause and you want to use aggregate functions in it, all the items in the selection list must be aggregate functions.
If the SELECT statement doescontain a GROUP BY clause, the column or columns specified in the GROUP BY clause must be select terms that are single columns, not aggregate functions. All the select terms that are not also listed in the GROUP BY clause, however, must be aggregate functions.
The following example returns a result table that allows you to determine the amount each student has paid.
SELECT Student_ID, SUM(Amount_Paid) FROM Billing GROUP BY Student_ID;
You can also include aggregate functions in HAVING clauses used with a GROUP BY clause. Using the HAVING clause with a GROUP BY clause restricts the groups of rows Zen returns. Zen performs the aggregate function on the column of each group of rows specified in the GROUP BY clause, and returns a single result for each set of rows that has the same value for the grouping column.
In the following example, Zen returns row groups only for students currently enrolled with more than 15 credit hours:
SELECT Student_ID, SUM(Credit_Hours) FROM Enrolls e, Class cl, Course c WHERE e.Class_ID = cl.ID AND cl.Name = c.Name GROUP BY Student_ID HAVING SUM(Credit_Hours) > 15;
Scalar Functions
Scalar functions such as CONCAT and CURDATE accept one or more parameters as input and return a single value. For example, the LENGTH function returns the length of a string column value. You can use scalar functions in Zen statements that allow computed columns in expressions.
The type of expression operator you can use depends on the type of result the function returns. For example, if the function returns a numeric value, you can use arithmetic operators. If the function returns a string value, you can use string operators.
You can nest scalar functions, but each nested function must return a result that is an appropriate parameter to the next level scalar function, as in the following example:
SELECT RIGHT (LEFT (Last_Name, 3), 1) FROM Person;
Zen executes the LEFT function first. If the value in the Last Name column is Baldwin, the string resulting from the LEFT function is Bal. This string is the parameter of the RIGHT function, which returns ‘l’ as the rightmost character of the string.
You can use scalar functions that return a numeric result within a computed column that calculates a numeric value. You can also use scalar functions that return a string value as an expression to another string function, but the total length of the string result cannot exceed 255 bytes.
In SQL Engine Reference, see Bitwise Operators for information on scalar functions you can use with Zen.
