Skip to main content

8. DATATYPES

Image
By

A datatype defines which kind of values a column can contain.

Data type
Description

CHARACTER(n)
Character string. Fixed-length n
VARCHAR(n) or
CHARACTER VARYING(n)
Character string. Variable

Length. Maximum length n
BINARY(n)
Binary string. Fixed-length n
BOOLEAN
Stores TRUE or FALSE values
VARBINARY(n) or
BINARY VARYING(n)
Binary string. Variable length. Max length n
INTEGER(p)
Integer numerical (no decimal). Precision p
SMALLINT
Integer numerical (no decimal). Precision 5
INTEGER
Integer numerical (no decimal). Precision 10
BIGINT
Integer numerical (no decimal). Precision 19
DECIMAL(p,s)
Exact numerical, precision p, scale s.
NUMERIC(p,s)
Exact numerical, precision p, scale s. (Same as DECIMAL)
FLOAT(p)
Approximate numerical, mantissa precision p. A floating number in base 10 exponential notation. The size argument for this type consists of a single number specifying the minimum precision
REAL
Approximate numerical, mantissa precision 7
FLOAT
Approximate numerical, mantissa precision 16
DOUBLE PRECISION
Approximate numerical, mantissa precision 16
DATE
Stores year, month, and day values
TIME
Stores hour, minute, and second values
TIMESTAMP
Stores year, month, day, hour, minute, and second values
INTERVAL
Composed of a number of integer fields, representing a period of time, depending on the type of interval
ARRAY
A set-length and ordered collection of elements
MULTISET
A variable-length and unordered collection of elements
XML
Stores XML data












<<Prev                                                                                    Next>>









Labels:

Comments

Post a Comment

Popular posts from this blog

5. INDEX

By
§ Indexes are special lookup tables that the database search engine can use to speed up data retrieval. § An index can be created in a table to find data more quickly and efficiently. § An index helps speed up SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements. § Indexes can be created or dropped with no effect on the data. § Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). Clustered indexes § Uses physical memory. § Easy to retrieve data. § Data is in stored form. Non-clustered indexes § No use of physical memory is needed. § More complicated, data retrieval is not that easy. § We can too createuniqueindex . Duplicate values are not allowed . CREATEINDEX index_name ON table_name ( column_name ) CREATEINDEX index_name ON table_name ( column_name ) CREATEINDEX Pindex Difference between clustered index and no...
Post a Comment
Read more

6. VIEW

By
§ A view is a virtual table. § Database views are created using the CREATE VIEW statement. § Views can be created from a single table, multiple tables, or another view. § When we update a view it will update underline base table. CREATEVIEW view_name AS SELECT column1 , column2 ..... FROM table_name WHERE [condition] Example : CREATEVIEW CUSTOMERS_VIEW AS SELECT name , age FROM   CUSTOMERS SELECT * FROM CUSTOMERS_VIEW                        Advantages § Reduce complexity of database schema. § Mechanism to implement row and column level security. § Represent aggregate data and hide detailed data. Limitations § Cannot pass parameter to view. § Rules and default cannot be associated with the view. § Order by clause is invalid in a view unless TOP or XML is also specified. § Cannot be created on the bases of temporary tables. Indexed View § Create u...
Post a Comment
Read more

13. Filtering Results

By
WHERE and HAVING are filters. They specify a series of search conditions, and only those rows that meet the terms of the search conditions are used to build the result set. Where SELECT * FROM Customers WHERE CustomerID = 1 ; GROUP BY The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns. SELECT column_name , aggregate_function ( column_name ) FROM table_name WHERE column_name operator value GROUPBY column_name ; § Example SELECT Shippers . ShipperName , COUNT ( Orders . OrderID ) AS NumberOfOrders FROM Orders LEFTJOIN Shippers ON Orders . ShipperID = Shippers . ShipperID GROUPBY ShipperName ; Having § The HAVING clause is typically used together with the GROUP BY clause to filter the results of aggregate values. § The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. SELECT column_name , aggregate_function ( colu...
Post a Comment
Read more