Lecture notes on SQL pdf

SQL Fundamentals and sql fundamentals exam questions and answers pdf free download
FrankRoberts Profile Pic
FrankRoberts,France,Researcher
Published Date:11-07-2017
Your Website URL(Optional)
Comment
Teradata Database SQL Fundamentals Release 13.0 B035-1141-098A March 2010CHAPTER 1 Objects This chapter describes the objects you use to store, manage, and access data in Teradata Database. Databases and Users Definitions A database is a collection of related tables, views, triggers, indexes, stored procedures, user- defined functions, and macros. A database also contains an allotment of space from which users can create and maintain their own objects, or other users or databases. A user is almost the same as a database, except that a user has a password and can log on to the system, whereas the database cannot. Defining Databases and Users Before you can create a database or user, you must have sufficient privileges granted to you. To create a database, use the CREATE DATABASE statement. You can specify the name of the database, the amount of storage to allocate, and other attributes. To create a user, use the CREATE USER statement. The statement authorizes a new user identification (user name) for the database and specifies a password for user authentication. Because the system creates a database for each user, the CREATE USER statement is very similar to the CREATE DATABASE statement. Difference Between Users and Databases The difference between users and databases in Teradata Database has important implications for matters related to privileges, but neither the differences nor their implications are easy to understand. This is particularly true with respect to understanding fully the consequences of implicitly granted privileges. Formally speaking, the difference between a user and a database is that a user has a password and a database does not. Users can also have default attributes such as time zone, date form, character set, role, and profile, while databases cannot. You might infer from this that databases are passive objects, while users are active objects. That is only true in the sense that databases cannot execute SQL statements. However, a query, macro, or stored procedure can execute using the privileges of the database. SQL Fundamentals 13Chapter 1: Objects Tables Tables Definitions A table is what is referred to in set theory terminology as a relation, from which the expression relational database is derived. Every relational table consists of one row of column headings (more commonly referred to as column names) and zero or more unique rows of data values. Formally speaking, each row represents what set theory calls a tuple. Each column represents what set theory calls an attribute. The number of rows (or tuples) in a table is referred to as its cardinality and the number of columns (or attributes) is referred to as its degree or arity. Defining Tables Use the CREATE TABLE statement to define base tables. The CREATE TABLE statement specifies a table name, one or more column names, and the attributes of each column. CREATE TABLE can also specify datablock size, percent freespace, and other physical attributes of the table. The CREATE/MODIFY USER and CREATE/MODIFY DATABASE statements provide options for creating permanent journal tables. Defining Indexes For a Table An index is a physical mechanism used to store and access the rows of a table. When you define a table, you can define a primary index and one or more secondary indexes. If you define a table and you do not specify a PRIMARY INDEX clause, NO PRIMARY INDEX clause, PRIMARY KEY constraint, or UNIQUE constraint, the default behavior is for the system to create the table using the first column as the nonunique primary index. (If you prefer that the system creates a NoPI table without a primary index, use DBS Control to change the value of the PrimaryIndexDefault General field. For details, see Utilities.) For details on indexes, see “Indexes” on page 33. For details on NoPI tables, see “No Primary Index (NoPI) Tables” on page 17. Duplicate Rows in Tables Though both set theory and common sense prohibit duplicate rows in relational tables, the ANSI standard defines SQL based not on sets, but on bags, or multisets. A table defined not to permit duplicate rows is called a SET table because its properties are based on set theory, where set is defined as an unordered group of unique elements with no duplicates. 14 SQL FundamentalsChapter 1: Objects Tables A table defined to permit duplicate rows is called a MULTISET table because its properties are based on a multiset, or bag, model, where bag and multiset are defined as an unordered group of elements that may be duplicates. For more information on … See … rules for duplicate rows in a table CREATE TABLE in SQL Data Definition Language. the result of an INSERT operation that INSERT in SQL Data Manipulation Language. would create a duplicate row the result of an INSERT using a SELECT subquery that would create a duplicate row Temporary Tables Temporary tables are useful for temporary storage of data. Teradata Database supports three types of temporary tables. Type Usage Global A global temporary table has a persistent table definition that is stored in the data temporary dictionary. Any number of sessions can materialize and populate their own local copies that are retained until session logoff. Global temporary tables are useful for stroring temporary, intermediate results from multiple queries into working tables that are frequently used by applications. Global temporary tables are identical to ANSI global temporary tables. Volatile Like global temporary tables, the contents of volatile tables are only retained for the duration of a session. However, volatile tables do not have persistent definitions. To populate a volatile table, a session must first create the definition. Global Global temporary trace tables are useful for debugging external routines (UDFs, temporary UDMs, and external stored procedures). During execution, external routines can trace write trace output to columns in a global temporary trace table. Like global temporary tables, global temporary trace tables have persistent definitions, but do not retain rows across sessions. Materialized instances of a global temporary table share the following characteristics with volatile tables:  Private to the session that created them.  Contents cannot be shared by other sessions.  Optionally emptied at the end of each transaction using the ON COMMIT PRESERVE/ DELETE rows option in the CREATE TABLE statement.  Activity optionally logged in the transient journal using the LOG/NO LOG option in the CREATE TABLE statement.  Dropped automatically when a session ends. SQL Fundamentals 15Chapter 1: Objects Tables For details about the individual characteristics of global temporary and volatile tables, see “Global Temporary Tables” on page 18 and “Volatile Tables” on page 23. Queue Tables Teradata Database supports queue tables, which are similar to ordinary base tables, with the additional unique property of behaving like an asynchronous first-in-first-out (FIFO) queue. Queue tables are useful for applications that want to submit queries that wait for data to be inserted into queue tables without polling. When you create a queue table, you must define a TIMESTAMP column with a default value of CURRENT_TIMESTAMP. The values in the column indicate the time the rows were inserted into the queue table, unless different, user-supplied values are inserted. You can then use a SELECT AND CONSUME statement, which operates like a FIFO pop:  Data is returned from the row with the oldest timestamp in the specified queue table.  The row is deleted from the queue table, guaranteeing that the row is processed only once. If no rows are available, the transaction enters a delay state until one of the following occurs:  A row is inserted into the queue table.  The transaction aborts, either as a result of direct user intervention, such as the ABORT statement, or indirect user intervention, such as a DROP TABLE statement on the queue table. To perform a peek on a queue table, use a SELECT statement. For details about creating a queue table, see “CREATE TABLE (Queue Table Form)” in SQL Data Definition Language. For details about the SELECT AND CONSUME statement, see SQL Data Manipulation Language. Error Logging Tables You can create an error logging table that you associate with a permanent base table when you want Teradata Database to log information about the following:  Insert errors that occur during an SQL INSERT … SELECT operation on the permanent base table  Update and insert errors that occur during an SQL MERGE operation on the permanent base table To enable error logging for an INSERT … SELECT or MERGE statement, specify the LOGGING ERRORS option. 16 SQL FundamentalsChapter 1: Objects Tables IF a MERGE operation or INSERT … SELECT operation generates errors that … AND the request … THEN the error logging table contains … the error logging completes  an error row for each error that the facilities can handle operation generated.  a marker row that you can use to determine the number of error rows for the request. The presence of the marker row means the request completed successfully. aborts and rolls back when  an error row for each USI or RI violation referential integrity (RI) or that was detected. unique secondary index  an error row for each error that was (USI) violations are detected prior to index maintenance. detected during index The absence of a marker row in the error maintenance logging table means the request was aborted. reach the error limit aborts and rolls back an error row for each error that the specified by the operation generated, including the error that LOGGING ERRORS caused the error limit to be reached. option the error logging aborts and rolls back an error row for each error that the facilities cannot operation generated until the error that the handle error logging facilities could not handle. You can use the information in the error logging table to determine how to recover from the errors, such as which data rows to delete or correct and whether to rerun the request. For details on how to create an error logging table, see “CREATE ERROR TABLE” in SQL Data Definition Language. For details on how to specify error handling for INSERT … SELECT and MERGE statements, see SQL Data Manipulation Language. No Primary Index (NoPI) Tables For better performance when bulk loading data using Teradata FastLoad or through SQL sessions (in particular, using the INSERT statement from TPump with the ArraySupport option enabled), you can create a No Primary Index (NoPI) table to use as a staging table to load your data. Without a primary index (PI), the system can store rows on any AMP that is desired, appending the rows to the end of the table. By avoiding the data redistribution normally associated with loading data into staging tables that have a PI, NoPI tables provide a performance benefit to applications that load data into a staging table, transform or standardize the data, and then store the converted data into another staging table. SQL Fundamentals 17Chapter 1: Objects Global Temporary Tables Applications can also benefit by using NoPI tables in the following ways:  As a log file  As a sandbox table to store data until an appropriate indexing method is determined A query that accesses the data in a NoPI table results in a full-table scan unless you define a secondary index on the NoPI table and use the columns that are indexed in the query. For more information on … See … creating a NoPI table the NO PRIMARY INDEX clause for the CREATE TABLE statement in SQL Data Definition Language. loading data into staging tables from FastLoad Teradata FastLoad Reference. loading data into staging tables from TPump Teradata Parallel Data Pump Reference. primary indexes “Primary Indexes” on page 38. secondary indexes “Secondary Indexes” on page 42. Global Temporary Tables Introduction Global temporary tables allow you to define a table template in the database schema, providing large savings for applications that require well known temporary table definitions. The definition for a global temporary table is persistent and stored in the data dictionary. Space usage is charged to login user temporary space. Each user session can materialize as many as 2000 global temporary tables at a time. How Global Temporary Tables Work To create the base definition for a global temporary table, use the CREATE TABLE statement and specify the keywords GLOBAL TEMPORARY to describe the table type. Although space usage for materialized global temporary tables is charged to temporary space, creating the global temporary table definition requires an adequate amount of permanent space. Once created, the table exists only as a definition. It has no rows and no physical instantiation. When any application in a session accesses a table with the same name as the defined base table, and the table has not already been materialized in that session, then that table is materialized as a real relation using the stored definition. Because that initial invocation is generally due to an INSERT statement, a temporary table—in the strictest sense—is usually populated immediately upon its materialization. 18 SQL FundamentalsChapter 1: Objects Global Temporary Tables There are only two occasions when an empty global temporary table is materialized:  A CREATE INDEX statement is issued on the table.  A COLLECT STATISTICS statement is issued on the table. The following table summarizes this information. WHEN this statement is issued on a global THEN a local instance of the global temporary temporary table that has not yet been materialized … table is materialized and it is … INSERT populated with data upon its materialization. CREATE INDEX … ON TEMPORARY … not populated with data upon its materialization. COLLECT STATISTICS … ON TEMPORARY … Note: Issuing a SELECT, UPDATE, or DELETE on a global temporary table that is not materialized produces the same result as issuing a SELECT, UPDATE, or DELETE on an empty global temporary table that is materialized. Example For example, suppose there are four sessions, Session 1, Session 2, Session 3, and Session 4 and two users, User_1 and User_2. Consider the scenario in the following two tables. Step Session … Does this … The result is this … 1 1 The DBA creates a global temporary The global temporary table definition table definition in the database is created and stored in the database scheme named globdb.gt1 according schema. to the following CREATE TABLE statement: CREATE GLOBAL TEMPORARY TABLE globdb.gt1, LOG (f1 INT NOT NULL PRIMARY KEY, f2 DATE, f3 FLOAT) ON COMMIT PRESERVE ROWS; SQL Fundamentals 19Chapter 1: Objects Global Temporary Tables Step Session … Does this … The result is this … 2 1 User_1 logs on an SQL session and Session 1 creates a local instance of the references globdb.gt1 using the global temporary table definition following INSERT statement: globdb.gt1. This is also referred to as a materialized temporary table. INSERT globdb.gt1 (1, 980101, 11.1); Immediately upon materialization, the table is populated with a single row having the following values. f1=1 f2=980101 f3=11.1 This means that the contents of this local instance of the global temporary table definition is not empty when it is created. From this point on, any INSERT/ DELETE/UPDATE statement that references globdb.gt1 in Session 1 maps to this local instance of the table. 3 2 User_2 logs on an SQL session and No rows are returned because Session issues the following SELECT 2 has not yet materialized a local statement. instance of globdb.gt1. SELECT FROM globdb.gt1; 4 2 User_2 issues the following INSERT Session 2 creates a local instance of the statement: global temporary table definition globdb.gt1. INSERT globdb.gt1 (2, 980202, 22.2); The table is populated, immediately upon materialization, with a single row having the following values. f1=2 f2=980202 f3=22.2 From this point on, any INSERT/ DELETE/UPDATE statement that references globdb.gt1 in Session 2 maps to this local instance of the table. 5 2 User_2 logs again issues the A single row containing the data (2, following SELECT statement: 980202, 22.2) is returned to the application. SELECT FROM globdb.gt1; 6 1 User_1 logs off from Session 1. The local instance of globdb.gt1 for Session 1 is dropped. 7 2 User_2 logs off from Session 2. The local instance of globdb.gt1 for Session 2 is dropped. 20 SQL FundamentalsChapter 1: Objects Global Temporary Tables User_1 and User_2 continue their work, logging onto two additional sessions as described in the following table. Step Session … Does this … The result is this … 1 3 User_1 logs on another SQL session No rows are returned because Session 3 and issues the following SELECT 3 has not yet materialized a local statement: instance of globdb.gt1. SELECT FROM globdb.gt1; 2 3 User_1 issues the following INSERT Session 3 created a local instance of the statement: global temporary table definition globdb.gt1. INSERT globdb.gt1 (3, 980303, 33.3); The table is populated, immediately upon materialization, with a single row having the following values. f1=3 f2=980303 f3=33.3 From this point on, any INSERT/ DELETE/UPDATE statement that references globdb.gt1 in Session 3 maps to this local instance of the table. 3 3 User_1 again issues the following A single row containing the data (3, SELECT statement: 980303, 33.3) is returned to the application. SELECT FROM globdb.gt1; 4 4 User_2 logs on Session 4 and issues An empty local global temporary table the following CREATE INDEX named globdb.gt1 is created for statement: Session 4. CREATE INDEX (f2) ON This is one of only two cases in which a TEMPORARY globdb.gt1; local instance of a global temporary table is materialized without data. The other would be a COLLECT STATISTICS statement—in this case, the following statement: COLLECT STATISTICS ON TEMPORARY globdb.gt1; 5 4 User_2 issues the following SELECT No rows are returned because the local statement: instance of globdb.gt1 for Session 4 is empty. SELECT FROM globdb.gt1; SQL Fundamentals 21Chapter 1: Objects Global Temporary Tables Step Session … Does this … The result is this … CREATE SET GLOBAL TEMPORARY 6 4 User_2 issues the following SHOW TABLE globdb.gt1, FALLBACK, TABLE statement: LOG SHOW TABLE globdb.gt1; ( f1 INTEGER NOT NULL, f2 DATE FORMAT 'YYYY-MM-DD', f3 FLOAT) UNIQUE PRIMARY INDEX (f1) ON COMMIT PRESERVE ROWS; CREATE SET GLOBAL TEMPORARY 7 4 User_2 issues the following SHOW TABLE globdb.gt1, FALLBACK, TEMPORARY TABLE statement: LOG SHOW TEMPORARY TABLE ( globdb.gt1; f1 INTEGER NOT NULL, f2 DATE FORMAT 'YYYY-MM-DD', f3 FLOAT) UNIQUE PRIMARY INDEX (f1) INDEX (f2) ON COMMIT PRESERVE ROWS; Note that this report indicates the new index f2 that has been created for the local instance of the temporary table. With the exception of a few options (see “CREATE TABLE” in SQL Data Definition Language for an explanation of the features not available for global temporary base tables), materialized temporary tables have the same properties as permanent tables. After a global temporary table definition is materialized in a session, all further references to the table are made to the materialized table. No additional copies of the base definition are materialized for the session. This global temporary table is defined for exclusive use by the session whose application materialized it. Materialized global temporary tables differ from permanent tables in the following ways:  They are always empty when first materialized.  Their contents cannot be shared by another session.  The contents can optionally be emptied at the end of each transaction.  The materialized table is dropped automatically at the end of each session. Limitations You cannot use the following CREATE TABLE options for global temporary tables:  WITH DATA  Permanent journaling  Referential integrity constraints This means that a temporary table cannot be the referencing or referenced table in a referential integrity constraint. References to global temporary tables are not permitted in FastLoad, MultiLoad, or FastExport. 22 SQL FundamentalsChapter 1: Objects Volatile Tables The Table Rebuild utility and the Archive/Recovery utility (with the exception of online archiving) operate on base global temporary tables only. Online archiving does not operate on temporary tables. Non-ANSI Extensions Transient journaling options on the global temporary table definition are permitted using the CREATE TABLE statement. You can modify the transient journaling and ON COMMIT options for base global temporary tables using the ALTER TABLE statement. Privileges Required To materialize a global temporary table, you must have the appropriate privilege on the base global temporary table or on the containing database or user as required by the statement that materializes the table. No access logging is performed on materialized global temporary tables, so no access log entries are generated. Volatile Tables Creating Volatile Tables Neither the definition nor the contents of a volatile table persist across a system restart. You must use CREATE TABLE with the VOLATILE keyword to create a new volatile table each time you start a session in which it is needed. What this means is that you can create volatile tables as you need them. Being able to create a table quickly provides you with the ability to build scratch tables whenever you need them. Any volatile tables you create are dropped automatically as soon as your session logs off. Volatile tables are always created in the login user space, regardless of the current default database setting. That is, the database name for the table is the login user name. Space usage is charged to login user spool space. Each user session can materialize as many as 1000 volatile tables at a time. Limitations The following CREATE TABLE options are not permitted for volatile tables:  Permanent journaling  Referential integrity constraints This means that a volatile table cannot be the referencing or referenced table in a referential integrity constraint.  Check constraints  Compressed columns SQL Fundamentals 23Chapter 1: Objects Volatile Tables  DEFAULT clause  TITLE clause  Named indexes References to volatile tables are not permitted in FastLoad or MultiLoad. For more information, see “CREATE TABLE” in SQL Data Definition Language. Non-ANSI Extensions Volatile tables are not defined in ANSI. Privileges Required To create a volatile table, you do not need any privileges. No access logging is performed on volatile tables, so no access log entries are generated. Volatile Table Maintenence Among Multiple Sessions Volatile tables are private to a session. This means that you can log on multiple sessions and create volatile tables with the same name in each session. However, at the time you create a volatile table, the name must be unique among all global and permanent temporary table names in the database that has the name of the login user. For example, suppose you log on two sessions, Session 1 and Session 2. Assume the default database name is your login user name. Consider the following scenario. Stage In Session 1, you … In Session 2, you … The result is this … 1 Create a volatile table Create a volatile Each session creates its own copy of named VT1. table named VT1. volatile table VT1 using your login user name as the database. 2 Create a permanent — Session 1 creates a permanent table table with an unqualified named VT2 using your login user name table name of VT2. as the database. 3 — Create a volatile Session 2 receives a CREATE TABLE table named VT2. error, because there is already a permanent table with that name. 4 Create a volatile table — Session 1 creates a volatile table named named VT3. VT3 using your login user name as the database. 24 SQL FundamentalsChapter 1: Objects Columns Stage In Session 1, you … In Session 2, you … The result is this … 5 — Create a permanent Session 2 creates a permanent table table with an named VT3 using your login user name unqualified table as the database. name of VT3. Because a volatile table is known only to the session that creates it, a permanent table with the same name as the volatile table VT3 in Session 1 can be created as a permanent table in Session 2. 6 Insert into VT3. — Session 1 references volatile table VT3. Note: Volatile tables take precedence over permanent tables in the same database in a session. Because Session 1 has a volatile table VT3, any reference to VT3 in Session 1 is mapped to the volatile table VT3 until it is dropped (see Step 10). On the other hand, in Session 2, references to VT3 remain mapped to the permanent table named VT3. 7 — Create volatile table Session 2 receives a CREATE TABLE VT3. error for attempting to create the volatile table VT3 because of the existence of that permanent table. 8 — Insert into VT3. Session 2 references permanent table VT3. 9 Drop VT3. — Session 2 drops volatile table VT3. 10 Select from VT3. — Session 1 references the permanent table VT3. Columns Definition A column is a structural component of a table and has a name and a declared type. Each row in a table has exactly one value for each column. Each value in a row is a value in the declared type of the column. The declared type includes nulls and values of the declared type. Defining Columns The column definition clause of the CREATE TABLE statement defines the table column elements. A name and a data type must be specified for each column defined for a table. SQL Fundamentals 25Chapter 1: Objects Columns Here is an example that creates a table called employee with three columns: CREATE TABLE employee (deptno INTEGER ,name CHARACTER(23) ,hiredate DATE); Each column can be further defined with one or more optional attribute definitions. The following attributes are also elements of the SQL column definition clause:  Data type attribute declaration, such as NOT NULL, FORMAT, TITLE, and CHARACTER SET  COMPRESS column storage attributes clause  DEFAULT and WITH DEFAULT default value control clauses  PRIMARY KEY, UNIQUE, REFERENCES, and CHECK column constraint attributes clauses Here is an example that defines attributes for the columns in the employee table: CREATE TABLE employee (deptno INTEGER NOT NULL ,name CHARACTER(23) CHARACTER SET LATIN ,hiredate DATE DEFAULT CURRENT_DATE); System-Derived and System-Generated Columns In addition to the table columns that you define, tables contain columns that Teradata Database generates or derives dynamically. Column Description Identity A column that was specified with the GENERATED ALWAYS AS IDENTITY or GENERATED BY DEFAULT AS IDENTITY option in the table definition. Object Identifier For a table that has LOB columns, OID columns store pointers to subtables (OID) that store the actual LOB data. PARTITION For a table that is defined with a partitioned primary index (PPI), the PARTITION column provides the partition number of the combined partitioning expression associated with a row, where the combined partitioning expression is derived from the partitioning expressions defined for each level of the PPI. This is zero for a table that does not have a PPI. For more information on PPIs, see “Partitioned and Nonpartitioned Primary Indexes” on page 36. PARTITIONL1 For tables that are defined with a multilevel PPI, these columns provide the through partition number associated with the corresponding level. These are zero for a PARTITIONL15 table that does not have a PPI and zero if the level is greater than the number of partitions. ROWID Contains the row identifier value that uniquely identifies the row. For more information on row identifiers, see “Row Hash and RowID” on page 33. 26 SQL FundamentalsChapter 1: Objects Data Types Restrictions apply to using the system-derived and system-generated columns in SQL statements. For example, you can use the keywords PARTITION and PARTITIONL1 through PARTITIONL15 in a query where a table column can be referenced, but you can only use the keyword ROWID in a CREATE JOIN INDEX statement. Related Topics For more information on … See … data types “Data Types” on page 27. CREATE TABLE and the column definition clause SQL Data Definition Language. system-derived and system-generated columns Database Design. Data Types Introduction Every data value belongs to an SQL data type. For example, when you define a column in a CREATE TABLE statement, you must specify the data type of the column. The set of data values that a column defines can belong to one of the following groups of data types:  Numeric  Byte  Character  UDT  Datetime  Period  Interval  Geospatial Numeric Data Types A numeric value is either an exact numeric number (integer or decimal) or an approximate numeric number (floating point). Use the following SQL data types to specify numeric values. Type Description BIGINT Represents a signed, binary integer value from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. INTEGER Represents a signed, binary integer value from -2,147,483,648 to 2,147,483,647. SMALLINT Represents a signed binary integer value in the range -32768 to 32767. BYTEINT Represents a signed binary integer value in the range -128 to 127. SQL Fundamentals 27Chapter 1: Objects Data Types Type Description REAL Represent a value in sign/magnitude form. DOUBLE PRECISION FLOAT DECIMAL (n,m) Represent a decimal number of n digits, with m of those n digits to the right of the decimal point. NUMERIC (n,m) Character Data Types Character data types represent characters that belong to a given character set. Use the following SQL data types to specify character data. Type Description CHAR(n) Represents a fixed length character string for Teradata Database internal character storage. VARCHAR(n) Represents a variable length character string of length n for Teradata Database internal character storage. LONG VARCHAR LONG VARCHAR specifies the longest permissible variable length character string for Teradata Database internal character storage. CLOB Represents a large character string. A character large object (CLOB) column can store character data, such as simple text, HTML, or XML documents. DateTime Data Types DateTime values represent dates, times, and timestamps. Use the following SQL data types to specify DateTime values. Type Description DATE Represents a date value that includes year, month, and day components. TIME WITH TIME Represents a time value that includes hour, minute, second, fractional ZONE second, and optional time zone components. TIMESTAMP WITH Represents a timestamp value that includes year, month, day, hour, TIIME ZONE minute, second, fractional second, and optional time zone components. Interval Data Types An interval value is a span of time. There are two mutually exclusive interval type categories. 28 SQL FundamentalsChapter 1: Objects Data Types Category Type Description Year-Month  INTERVAL YEAR Represent a time span that can include a number of years and months.  INTERVAL YEAR TO MONTH  INTERVAL MONTH Day-Time  INTERVAL DAY Represent a time span that can include a number of days, hours, minutes, or  INTERVAL DAY TO HOUR seconds.  INTERVAL DAY TO MINUTE  INTERVAL DAY TO SECOND  INTERVAL HOUR  INTERVAL HOUR TO MINUTE  INTERVAL HOUR TO SECOND  INTERVAL MINUTE  INTERVAL MINUTE TO SECOND  INTERVAL SECOND Byte Data Types Byte data types store raw data as logical bit streams. For any machine, BYTE, VARBYTE, and BLOB data is transmitted directly from the memory of the client system. Type Description BYTE Represents a fixed-length binary string. VARBYTE Represents a variable-length binary string. BLOB Represents a large binary string of raw bytes. A binary large object (BLOB) column can store binary objects, such as graphics, video clips, files, and documents. BLOB is ANSI SQL:2008-compliant. BYTE and VARBYTE are Teradata extensions to the ANSI SQL:2008 standard. UDT Data Types UDT data types are custom data types that you define to model the structure and behavior of data that your application deals with. Teradata Database supports distinct and structured UDTs. Type Description Distinct A UDT that is based on a single predefined data type, such as INTEGER or VARCHAR. Structured A UDT that is a collection of one or more fields called attributes, each of which is defined as a predefined data type or other UDT (which allows nesting). SQL Fundamentals 29Chapter 1: Objects Data Types For more details on UDTs, including a synopsis of the steps you take to develop and use UDTs, see “User-Defined Types” on page 74. Period Data Types A period data type represents a set of contiguous time granules that extends from a beginning bound up to but not including an ending bound. Type Description PERIOD(DATE) Represents an anchored duration of DATE elements that include year, month, and day components. PERIOD(TIME(n) Represents an anchored duration of TIME elements that include WITH TIME ZONE) hour, minute, second, fractional second, and optional time zone components. PERIOD(TIMESTAMP(n) Represents an anchored duration of TIMESTAMP elements that WITH TIME ZONE) include year, month, day, hour, minute, second, fractional second, and optional time zone components. Geospatial Data Types Geospatial data types provide a way for applications that manage, analyze, and display geographic information to interface with Teradata Database. Use the following data types to represent geographic information. 30 SQL FundamentalsChapter 1: Objects Data Types Type Description ST_Geometry A Teradata proprietary internal UDT that can represent any of the following geospatial types: Type Description ST_Point 0-dimensional geometry that represents a single location in two-dimensional coordinate space. ST_LineString 1-dimensional geometry usually stored as a sequence of points with a linear interpolation between points. ST_Polygon 2-dimensional geometry consisting of one exterior boundary and zero or more interior boundaries, where each interior boundary defines a hole. ST_GeomCollection Collection of zero or more ST_Geometry values. ST_MultiPoint 0-dimensional geometry collection where the elements are restricted to ST_Point values. ST_MultiLineString 1-dimensional geometry collection where the elements are restricted to ST_LineString values. ST_MultiPolygon 2-dimensional geometry collection where the elements are restricted to ST_Polygon values. ST_Geometry (continued) Type Description GeoSequence Extension of ST_LineString that can contain tracking information, such as time stamps, in addition to geospatial information. The ST_Geometry type supports methods for performing geometric calculations. MBR A Teradata proprietary internal UDT that provides a way to obtain the minimum bounding rectangle (MBR) of a geometry for tessellation purposes. The implementation of the ST_Geometry type closely follows ISO/IEC 13249-3, Information technology — Database languages — SQL Multimedia and Application Packages — Part 3: Spatial, referred to as SQL/MM Spatial. The GeoSequence and MBR types are extensions to SQL/MM Spatial. Related Topics For detailed information on data types, see SQL Data Types and Literals and SQL Geospatial Types. SQL Fundamentals 31

Advise: Why You Wasting Money in Costly SEO Tools, Use World's Best Free SEO Tool Ubersuggest.