SQL - Syntax

Question 1

List and Describe: categories of SQL syntax

Answer 1

SQL syntax falls into five main categories:

Identifiers: names for database objects, such as databases, tables, constraints on tables, columns in tables, views, etc.

Literals: strings or values that are not identifiers or keywords.

Operators: Symbols specifying an action to be performed on one or more expressions.

Reserved Words: Words with special meaning to the database SQL parser. Reserved words (such as words used in commands and SQL statements) are words that cannot be used as identifiers.

Keywords: Words with special meaning to the database SQL parser. The term keywords is often used to refer to non-reserved words that only have a special meaning in particular contexts and can be used as identifiers in other contexts (though this is still generally best avoided)

Question 2

What is an ‘identifier’ in SQL?

Answer 2

In its simplest terms, an identifier is the name of an object you create on your database platform.

Each object (whether a database, table, view, column, index, key, trigger, stored procedure, or constraint) in an RDBMS must be identified. When issuing the command that creates a database object, you must therefore specify an identifier (i.e., a name) for that new object.

Question 3

What are two categories of rules when choosing an Identifier in SQL?

Answer 3

Naming Conventions & Identifier Rules

Question 4

What are ‘naming conventions’ and what are their purpose?

Answer 4

Naming conventions establish a standard baseline for choosing object identifiers. The SQL standard has no comment on naming conventions outside of the uniqueness of an identifier, its length, and the characters that are valid within the identifier.

There two reasons for naming conventions:

1. SQL code becomes, in a sense, self-documenting, because the chosen names are meaningful and understandable to other users.
2. SQL code and database objects are easier to maintain—especially for other users who come later—because the objects are consistently named.

Question 5

List and Describe: some naming conventions

Answer 5

Naming Conventions:

1. Select a name that is meaningful, relevant, and descriptive.
2. Choose and apply the same case throughout.
3. Use abbreviations and spellings consistently.
4. Use complete, descriptive, meaningful names with underscores for reading clarity.
5. Do not put company or product names in database object names.
6. Do not use overly obvious prefixes or suffixes.
7. Do not fill up all available space for the object name.
8. Do not use quoted identifiers.

Question 6

What are ‘Identifier Rules’ and what are their purpose?

Answer 6

Identifier rules are rules for identifying objects within the database that are rigidly enforced by the database platforms. Rules specified by the SQL standard generally differ somewhat from those of specific database vendors.

Question 7

List and Describe: some Identifier Rules for the SQL Standard.

Answer 7

Identifier Rules for the SQL Standard (note: identifier rules differ per vendor)

1. Identifier size: 128 characters.
2. Identifier may contain: any number or character, and the underscore (_) symbol.
3. Identifier must begin with: a letter.
4. Identifier cannot contain: spaces or special characters.
5. Allows quoted identifiers: yes.
6. Quoted identifier symbol: double quotes ( “ “).
7. Identifier may be reserved: no, unless as a quoted identifier.
8. Schema Addressing: <catalog>.<schema>.<object></object></schema></catalog>
9. Identifier mus be unique: yes.
10. Case Sensitive: no

Question 8

Research: ‘scope’ in programming languages in general.

Answer 8

The scope of a identifier name is the part of a program where the identifier name is valid; that is, where the name can be used to refer to the entity.

Scope helps prevent name collisions by allowing the same name to refer to different objects – as long as the names have separate scopes.

Question 9

What naming rules for identifiers in regards to scope?

Answer 9

Identifiers must be unique within their scope.
. database names must be unique on a particular instance of a database server
. names of tables, views, functions, triggers, and stored procedures must be unique within a particular schema.
. a table and a stored procedure can have the same name since they are different types of objects
. names of columns, keys, and indexes must be unique in a single table or view

Question 10

What is a ‘literal’ in the SQL language?

Answer 10

SQL defines a literal value as any explicit numeric value, character string, temporal value (e.g., date or time), or Boolean value that is not an identifier or a keyword.

In other words, a literal is a raw data value.

Question 11

What is an ‘operator’ in the SQL Language?

Answer 11

An operator is a symbol specifying an action to be performed on one or more expressions.

Question 12

List and Describe: the seven categories of operators.

Answer 12

1. Arithmetic operators
   Arithmetic operators perform mathematical operations on two expressions of any data type in the numeric data type category.
2. Assignment operators
   Except in Oracle, which uses :=, the assignment operator (=) assigns a value to a variable or the alias of a column heading.
3. Bitwise operators
   All databases support bitwise operators as a shortcut to perform bit manipulations between two-integer expressions.
4. Comparison operators
   Comparison operators test whether two expressions are equal or unequal.
5. Logical operators
   Logical operators are commonly used in a WHERE clause to test for the truth of some condition. They return a Boolean value of either TRUE or FALSE.
6. Set operators
   Set operators manipulate the results of two or more SELECT statements and return a single result set based on the function of the set operator itself.
7. Unary operators
   Unary operators perform an operation on only one expression of any of the data types in the numeric data type category.

Question 13

Research: bitwise operations

Answer 13

In computer programming, a bitwise operation operates at the level of its individual bits. A bitwise operation can change bit strings, a bit arrays or a binary numerals.

Question 14

What are bitwise operators in the SQL Language?

Question 15

What are Comparison Operators in the SQL Language?

Question 16

List and Describe: the nine Comparison Operators in the SQL Language.

Answer 16

Comparison operator Meaning:

= Equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to
!= Not equal to (not SQL standard)
!< Not less than (not SQL standard)
!> Not greater than (not SQL standard)