Database and SQL

A PreparedStatement represents a precompiled SQL statement that is compiled once by the database management system and can be executed multiple times with different parameters. The standard Statement compiles SQL on every execution, while ResultSet holds query results and CallableStatement is for stored procedures.

Accessing a materialized view requires explicitly querying the view by name in your SQL statement. You must write queries against the materialized view directly - the database does not automatically rewrite queries to use the materialized view instead of base tables. Query rewrite is an optional feature that must be explicitly enabled and configured.

The ! meta-command in nzsql is used to pause the current query and escape to the shell to execute operating system commands. This allows users to run shell commands without exiting nzsql. To exit nzsql, use \q instead.

Exception Cells in SAP BW queries require two structures: one in the rows and one in the columns. Exception cells are defined at the intersection of these structures. A single structure or no structures are insufficient - the intersection point is necessary for exception cell definition.

In SQL Server, the COLLATE property at the database level affects permanent objects but has special behavior with temporary tables. Temporary tables are created in tempdb and inherit its collation, which can differ from the database collation. Stored procedures, views, and triggers can use collation settings within the database context.

SELECT is the SQL statement used for data retrieval - it queries and returns data from database tables. Insert adds new data, Create defines structures, Delete removes data, Grant gives permissions, and Commit saves transactions - none of these are primarily for retrieval.

Data Manipulation Language (DML) statements modify data: Insert adds new rows, Delete removes rows, Merge combines insert/update/delete operations, and Update modifies existing data. Create is DDL (defines structure), Select is for retrieval not manipulation.

Transaction Control Language (TCL) commands manage transactions: Rollback undoes changes, Commit permanently saves changes, and Savepoint sets intermediate rollback points. Alter and Rename are DDL structure commands, Select is for retrieval.

Data Control Language (DCL) commands manage permissions and access: Grant gives privileges to users, Revoke removes privileges. Alter and Rename are DDL structure commands, Select retrieves data, Update modifies data - none are access control commands.

Column names in RDBMS are scoped to their individual tables, so multiple tables can have columns with identical names without conflict. When referencing columns in queries, you use dot notation (table_name.column_name) to disambiguate which column you mean.

A table in RDBMS follows the relational model's two-dimensional structure: rows (also called tuples or records) represent individual entries, and columns (attributes or fields) represent properties. This grid-like structure is fundamental to how relational databases organize and store data.

In SQL SELECT statement syntax, ORDER BY is indeed the final clause. The standard order is: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY. This makes sense logically because you must first determine which rows and columns you're working with before sorting them.

The default sort order for ORDER BY in SQL is ascending (ASC), not descending. If you omit ASC/DESC, the database assumes you want ascending order. To sort in descending order, you must explicitly specify the DESC keyword.

In SQL, the SELECT clause is processed before the ORDER BY clause in the logical query processing phase. Consequently, column aliases defined in the SELECT clause are visible and can be used to sort the result set in the ORDER BY clause.