Aqt Query Tool Tutorial



AQT is a fast and powerful multi-database query tool. The present version has a new function to rewrite your SQL queries. This is the equialent of importing your SQL into the Query Builder and back again; the SQL will be rewritten as per the options in the Query Builder. This Cheat Sheet consists of several helpful tables and lists, containing information that comes up repeatedly when working with SQL. In one place, you can get a quick answer to a number of different questions that frequently arise during an SQL development effort. Pika Software Builder is a Windows application development tool for beginners and professionals alike. Create your project using the WYSIWYG designer and export your app as a native Win32 executable. With Pika, database support comes standard: build apps that interact with your Access, SQL Server, MySQL, PostgreSQL or Oracle database.

Excel

Does your company write an API for its software? If the answer is yes, then you absolutely need to test it — and fortunately for you, this tutorial explains step-by-step how to conduct automated API testing using tools like Postman, Newman, Jenkins and Tricentis qTest. But first, let’s take a lay of the land.

This Cheat Sheet consists of several helpful tables and lists, containing information that comes up repeatedly when working with SQL. In one place, you can get a quick answer to a number of different questions that frequently arise during an SQL development effort.

SQL Criteria for Normal Forms

To ensure that database tables are designed in such a way that they will hold your data reliably, you need to be sure that they are not subject to modification anomalies. Normalizing your databases will give you that assurance. Compare the SQL criteria in the following list to the tables in your database. Doing so will alert you to the possibility of anomalies, when you find that your database is not sufficiently normalized.

Aqt Query Tool Tutorial

First Normal Form (1NF):

  • Table must be two-dimensional, with rows and columns.

  • Each row contains data that pertains to one thing or one portion of a thing.

  • Each column contains data for a single attribute of the thing being described.

  • Each cell (intersection of row and column) of the table must be single-valued.

  • All entries in a column must be of the same kind.

  • Each column must have a unique name.

  • No two rows may be identical.

  • The order of the columns and of the rows does not matter.

Second Normal Form (2NF):

  • Table must be in first normal form (1NF).

  • All non-key attributes (columns) must be dependent on the entire key.

Third Normal Form (3NF):

  • Table must be in second normal form (2NF).

  • Table has no transitive dependencies.

Domain-Key Normal Form (DK/NF):

  • Every constraint on the table is a logical consequence of the definition of keys and domains.

Tutorial

SQL Data Types

Here’s a list of all the formal data types that ISO/IEC standard SQL recognizes. In addition to these, you may define additional data types that are derived from these.

Exact Numerics:

  • INTEGER

  • SMALLINT

  • BIGINT

  • NUMERIC

  • DECIMAL

Approximate Numerics:

  • REAL

  • DOUBLE PRECISION

  • FLOAT

  • DECFLOAT

Binary Strings:

  • BINARY

  • BINARY VARYING

  • BINARY LARGE OBJECT

Boolean:

  • BOOLEAN

Character Strings:

  • CHARACTER

  • CHARACTER VARYING (VARCHAR)

  • CHARACTER LARGE OBJECT

  • NATIONAL CHARACTER

  • NATIONAL CHARACTER VARYING

  • NATIONAL CHARACTER LARGE OBJECT

Datetimes:

  • DATE

  • TIME WITHOUT TIMEZONE

  • TIMESTAMP WITHOUT TIMEZONE

  • TIME WITH TIMEZONE

  • TIMESTAMP WITH TIMEZONE

Intervals:

  • INTERVAL DAY

  • INTERVAL YEAR

Collection Types:

  • ARRAY

  • MULTISET

Other Types:

  • ROW

  • XML

SQL Value Functions

These SQL value functions perform operations on data. There are all kinds of operations that could conceivably be performed on data items, but these are some that are needed most often.

String Value Functions
FunctionEffect
SUBSTRINGExtracts a substring from a source string
SUBSTRING SIMILARExtracts a substring from a source string, using POSIX-based
regular expressions
SUBSTRING_REGEXExtracts from a string the first occurrence of an XQuery
regular expression pattern and returns one occurrence of the
matching substring
TRANSLATE_REGEXExtracts from a string the first or every occurrence of an
XQuery regular expression pattern and replaces it or them with an XQuery replacement string
UPPERConverts a character string to all uppercase
LOWERConverts a character string to all lowercase
TRIMTrims off leading or trailing blanks
TRANSLATETransforms a source string from one character set to
another
CONVERTTransforms a source string from one character set to
another
Numeric Value Functions
FunctionEffect
POSITIONReturns the starting position of a target string within a
source string
CHARACTER_LENGTHReturns the number of characters in a string
OCTET_LENGTHReturns the number of octets (bytes) in a character string
EXTRACTExtracts a single field from a datetime or interval
Datetime Value Functions
FunctionEffect
CURRENT_DATEReturns the current date
CURRENT_TIME(p)Returns the current time; (p) is precision of seconds
CURRENT_TIMESTAMP(p)Returns the current date and the current time; (p) is precision of seconds

SQL Set Functions

Aqt

The SQL set functions give you a quick answer to questions you may have about the characteristics of your data as a whole. How many rows does a table have? What is the highest value in the table? What is the lowest? These are the kinds of questions that the SQL set functions can answer for you.

Aqt Query Tool Tutorial Excel

FunctionEffect
COUNTReturns the number of rows in the specified table
MAXReturns the maximum value that occurs in the specified able
MINReturns the minimum value that occurs in the specified table
SUMAdds up the values in a specified column
AVGReturns the average of all the values in the specified column
LISTAGGTransforms values from a group of rows into a delimited string

Trigonometric and Logarithmic Functions

sin, cos, tan, asin, acos, atan, sinh, cosh, tanh, log(<base>, <value>), log10(<value>). ln( <value>)

JSON Constructor Functions

JSON_OBJECT

JSON_ARRAY

JSON_OBJECTAGG

JSON_ARRAYAGG

JSON Query Functions

JSON_EXISTS

JSON_VALUE

Aqt Query Tool Tutorial For Beginners

JSON_QUERY

Aqt Query Tool Tutorial

JSON_TABLE

Aqt Query Tool Tutorial C++

SQL WHERE Clause Predicates

Predicates boil down to either a TRUE or a FALSE result. You can filter out unwanted rows from the result of an SQL query by applying a WHERE clause whose predicate excludes the unwanted rows.

Aqt Query Tool Tutorial Android Studio

Comparison Predicates
=Equal
<>Not equal
<Less than
<=Less than or equal
>Greater than
>=Greater than or equal
Other Predicates
ALLBETWEEN
DISTINCTEXISTS
INLIKE
MATCHNOT IN
NOT LIKENULL
OVERLAPSSOME, ANY
UNIQUE