What are data types in SQL?
In SQL, data types are used to define the type of data that can be stored in a column of a database table. Data types specify the format of the data, which helps the database management system (DBMS) understand how to store, retrieve, and manipulate the data. Here are some common data types used in SQL:
INTEGER (INT)
Used for storing whole numbers, both positive and negative. It typically uses 4 bytes of storage.
SMALLINT: Similar to INTEGER but uses less storage (2 bytes) and is used for smaller whole numbers.
BIGINT
Used for very large whole numbers. It typically uses 8 bytes of storage.
NUMERIC(P, S) or DECIMAL(P, S)
Used for storing fixed-point numbers with a specified number of digits before and after the decimal point. P represents the total number of digits, and S represents the number of digits after the decimal point.
FLOAT: Used for storing approximate numeric values with a floating-point representation.
REAL: Similar to FLOAT but uses less storage.
DOUBLE PRECISION
Used for storing double-precision floating-point numbers with high precision.
CHAR(N): Fixed-length character strings where N specifies the number of characters to be stored. It’s padded with spaces if the data is shorter than N.
VARCHAR(N): Variable-length character strings where N specifies the maximum number of characters to be stored. It doesn’t pad with spaces.
TEXT: Used for storing large amounts of text data.
DATE: Used for storing date values (e.g., ‘YYYY-MM-DD’).
TIME: Used for storing time values (e.g., ‘HH:MM:SS’).
TIMESTAMP: Used for storing date and time values (e.g., ‘YYYY-MM-DD HH:MM:SS’).
BOOLEAN: Used for storing Boolean values, typically ‘TRUE’ or ‘FALSE’.
BINARY: Used for storing binary data, such as images or files.
VARBINARY: Variable-length binary data.
BLOB (Binary Large Object): Used for storing large binary data.
CLOB (Character Large Object)
Used for storing large text data.
ENUM: A user-defined data type that consists of a static, ordered set of values.
ARRAY: A user-defined data type that allows you to store an array of values of the same data type.
JSON: Used for storing JSON data.
The choice of data type for a column in a table depends on the nature of the data that will be stored in it. Using the appropriate data type ensures efficient storage and retrieval of data and helps maintain data integrity. It’s important to choose data types carefully to avoid wasting storage space and to handle data accurately.
What are types of functions in SQL?
SQL course in Chandigarh It provides several types of functions that enable you to perform various operations on data. These functions can be categorized into the following types:
Scalar Functions
String Functions: These functions operate on character strings and include operations like concatenation, substring extraction, case conversion, and more. Examples include CONCAT, SUBSTRING, UPPER, LOWER, and LENGTH.
Numeric Functions
Numeric functions perform operations on numeric data types. They include mathematical operations (e.g., addition, subtraction), rounding, absolute value, and others. Examples include SUM, AVG, ABS, and ROUND.
Date and Time Functions
These functions work with date and time data types and include operations like date arithmetic, date formatting, and extracting parts of a date. Examples include DATEADD, DATEDIFF, and FORMAT.
Conversion Functions: Conversion functions allow you to change data types. Common conversion functions include CAST and CONVERT.
Comparison Functions: Comparison functions are use to compare values and return a Boolean result. For example, IF, CASE, and COALESCE.
Aggregate Functions
Aggregate functions operate on a set of values and return a single value summarizing the set. Common aggregate functions include:
SUM: Calculates the sum of a set of values.
AVG: Calculates the average of a set of values.
COUNT: Counts the number of rows in a result set.
MIN: Returns the minimum value in a set.
MAX: Returns the maximum value in a set.
Window Functions
Window functions perform a calculation across a set of table rows related to the current row. They are often use in conjunction with the OVER clause to define the window or partition of rows over which the calculation is perform. Examples of window functions include ROW_NUMBER, RANK, and LAG.
User-Defined Functions (UDFs)
SQL training in Chandigarh It allows you to define your own functions, which can encapsulate complex logic and reuse within SQL statements. UDFs can be either scalar or table-valued functions.
Built-In System Functions
Most database management systems have built-in system functions that provide information about the database or perform system-related tasks. Examples include GETDATE() to get the current date and time and CURRENT_USER to get the current user.
Ranking Functions
Ranking functions assign a rank or row number to each row within a result set based on a specified criterion. Common ranking functions include RANK(), DENSE_RANK(), and NTILE().
String Aggregation Functions
These functions are use to concatenate values from multiple rows into a single string. An example is STRING_AGG.
Geospatial Functions (for databases that support geospatial data):
Geospatial functions are use to work with geographic data types, allowing you to perform operations like distance calculations and geometric analysis.
Cryptographic Functions (for secure data handling)
These functions allow you to perform encryption and decryption of data, hash data, and manage security-related operations.
The availability and specific functions may vary depending on the database management system you are using (e.g., SQL Server, MySQL, PostgreSQL, Oracle). Be sure to refer to your DBMS’s documentation for a comprehensive list of functions and their usage.
Read more article:- Hnadown