SQL Data Types

6 Key excerpts on "SQL Data Types"

• 

  eBook - ePub

  SQL Interview Questions

  A complete question bank to crack your ANN SQL interview with real-time examples

  • Prasad Kulkarni(Author)
  • 2019(Publication Date)
  • BPB Publications

    (Publisher)

  HAPTER 2

  SQL Statements, Keywords, and Datatype

  W e know database is important not only to store data but also to secure it and manage it. SQL keeps data storage very clear and simple with its different datatypes. The datatypes are also very simple and user-friendly. It helps to arrange, store data separately, and speed up the data searching as well. According to data operations, SQL is further divided into different sublanguages like DDL, DML, DCL, and DTL. In this chapter we will see the different SQL datatypes, SQL statements, and SQL keywords.

  Prerequisite for this chapter

  Form this chapter onwards, we will also concentrate on SQL practical, and so you should have a laptop/desktop with SQL server installed on it. There is no specific knowledge required to learn this chapter, just start reading this with interest in database concepts.

  Structure

  After studying this chapter, you will learn the following points:

  • SQL keywords
  • SQL datatypes
    • Numerical SQL datatypes ◆ Exact numeric ◆ Approximate numeric
    • The string and char SQL datatypes ◆ Character string ◆ Unicode string ◆ Binary string
    • Date and time SQL datatypes
    • Miscellaneous SQL datatypes
  • Different types of SQL statements
  • DDL statements
  • DML statements
  • Conclusion
  • Rapid fire questions and answers
  • Do you know (lights on fact?)

  So, let's begin

  SQL keywords

  Every programming language has its own set of reserved words that are used by its compiler to perform various operations. Similarly, in SQL, we have a set of reserved words and it is recommended to not use those reserved words as table name, column, and identifiers. If you want to use them, then enclose them in square braces [] , these words are known as keywords. Basically, database uses this keyword for definition, manipulation, and accessing data.

  SQL has a very large list of keywords, but here I am picking up some important keywords for you:

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Learn SQL Database Programming

  Query and manipulate databases from popular relational database servers using SQL

  • Josephine Bush(Author)
  • 2020(Publication Date)
  • Packt Publishing

    (Publisher)

  Understanding Data Types

  In this chapter, we will learn what data types and their usage . We will go through the specific data types and understand what data can be stored in each of them. The data types include string, numeric, date and time, and other data types. String data types include char and varchar, binary and varbinary, blob, enum, set, and text. Numeric data types include bit, int, float, double, and decimal. Date and time data types include date, time, datetime, timestamp, and year.

  We will learn from the perspective of MySQL Data Types, and will keep noting the differences between Oracle, PostgreSQL, and SQL Server wherever needed.

  We will also go through some examples of types and values of data to see how to assign them correctly to data types, including an explanation of why you need to be careful when selecting a data type and how it can impact database performance.

  In this chapter, we will cover the following topics:

  • Understanding string data types
  • Understanding numeric data types
  • Understanding date and time data types
  • Understanding other data types
  • Choosing the right data type

  Passage contains an image

  Understanding string data types

  String data types can hold plain text and binary data. This section walks you through the different string data types in MySQL. This section also shows you the differences between Oracle, PostgreSQL, and SQL Server string data types.

  Passage contains an image

  String data types in MySQL

  Let's discuss the string data types in MySQL:

  • CHAR: It is a fixed-length string, which can contain letters, numbers, and special characters. This type is blank-padded and contains trailing blanks in the field. This field can range from 0 to 255, and the default (if nothing is specified, that is, just using CHAR instead of CHAR(10)) is 1. The size in parenthesis denotes the maximum length of the char specified by the user when creating the data field with this type. The storage requirement for CHAR is size * n , where n <= size <= 255 , where n

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  MySQL 8 Administrator's Guide

  • Chintan Mehta, Ankit K Bhavsar, Hetal Oza, Subhash Shah(Authors)
  • 2018(Publication Date)
  • Packt Publishing

    (Publisher)

  MySQL 8 Data Types

  In the previous chapter, we learned how to use MySQL 8 command-line programs and utilities to perform various operations on the MySQL 8 database. It is always good to have hold on command-line tools. It provides flexibility to work in non-GUI environments. The focus of this chapter is data types. Isn't it fascinating to know the type of data the programming language supports or the storage engine can store? It is a fundamental feature of any programming language or database. At the same time, it is the most ignored topic, as well. The majority of programmers don't spend enough time assessing the storage requirements for variables used in a piece of code. Actually, it is extremely important to understand the basic and custom data types that the database supports, which is why this chapter exists.

  The following is a list of topics to be covered in this chapter:

  • Overview of MySQL 8 data types
  • Numeric data types
  • Date and time data types
  • String data types
  • JSON data type
  • Storage requirements for data types
  • Choosing the right data type for columns

  Passage contains an image

  Overview of MySQL 8 data types

  All standard SQL Data Types are supported in MySQL. These data types are classified in a few categories, such as numeric types, string types, date and time types, and the JSON data type. When we assign a data type to a column, certain conventions must be followed. These conventions are necessary for MySQL to allow values to be stored in a column:

  • M
    denotes the maximum display width for integer types. For floating point and fixed point types, it is the total number of digits that can be stored. For string types, it is the maximum length. The maximum value allowed depends on the data type.
  • D is applicable to floating points and fixed point types. It denotes the number of digits after the decimal point. The maximum allowed value is 30, but must be less than or equal to M-2.
  • fsp is applicable to date and time types. It denotes the fractional seconds precision, which means the number of digits following the decimal point for the fractional part of seconds.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  The Real MCTS SQL Server 2008 Exam 70-433 Prep Kit

  Database Design

  • Mark Horninger(Author)
  • 2009(Publication Date)
  • Syngress

    (Publisher)

  As database tables, SQL queries, and stored procedures are designed and implemented, a crucial task will need to be undertaken to define the appropriate data type for each column, local variable, expression, or parameter. Careful consideration must be given when choosing data types to ensure that they fit the data requirements appropriately and help provide for the most effective storage and efficient queries. A database administrator may strive to keep the use of data types consistent for each database or across multiple databases, and a developer may want to use data types that work best with the application module that he or she may be working on at that moment. It is important to define data types that work best in both situations, but consistency when assigning data types is paramount for database scalability and supportability. This chapter will start by taking a look at the available built-in data types; then describe a few string functions that are available for converting data types and working with strings; and finish up examining computed columns, filestream data, and the cost of boxing and unboxing data types.

  Be sure to review the built-in data types and become familiar with how they are categorized and how and when to use each type.

  Built-in Data Types

  SQL Server provides built-in data types that are organized into the following categories:

   Exact Numbers

   Approximate Numbers

   Date and Time

   Character Strings

   Unicode Character Strings

   Binary Strings

   Other Data Types

   CLR Data Types

   Spatial Data Types

  See Table 10.1 for descriptions of the built-in data types for each of these categories.

  Table 10.1 Built-in Data Types

  * New in SQL Server 2008.

  Be aware of the new data types in SQL Server 2008 and how they are used.

  New & Noteworthy…

  New Data Types in SQL Server 2008

  SQL Server 2008 has expanded the available built-in data types to include: Date, Time, DateTimeOffset, Datetime2, Hierarchyid, Geometry, and Geography.

  Functions

  We’ll now discuss various functions that SQL Server 2008 offers.

  Cast

  SQL Server provides string functions that can be used to perform an operation on a string and return either a string or a numeric value. The first of these functions that we will cover will be the CAST function. The CAST function casts an expression of one data type to another data type. CAST is ANSI compliant, providing better compatibility when working with data across different database brands than the CONVERT function that we will be covering in the next section. CAST will also preserve decimal places better than the CONVERT

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Joe Celko's SQL for Smarties

  Advanced SQL Programming

  • Joe Celko(Author)
  • 2014(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  Part 2 Data Types Passage contains an image

  Chapter 10

  Numeric Data in SQL

  Abstract

  SQL is not a computational language; the arithmetic capability of the SQL is weaker than that of almost any other language you have ever used. But there are some tricks that you need to know working with numbers in SQL and when passing them to a host program.

  Keywords NULL Exact numeric data types INTEGER BIGINT SMALLINT NUMERIC(p, s) DECIMAL (p, s) BIT Byte BOOLEAN Approximate numeric FLOAT(p) REAL DOUBLE PRECISION IEEE floating point Type conversions INCITS/H2 Database Standards Committee NULLIF() COALESCE() Mathematical functions MOD() IP addresses

  Sql is not a computational language; the arithmetic capability of the SQL is weaker than that of almost any other language you have ever used. But there are some tricks that you need to know working with numbers in SQL and when passing them to a host program. Much of the arithmetic and the functions are implementations defined, so you should experiment with your particular product and make notes on the default precision, and tools in the math library of your database.

  This section deals with the arithmetic that you would use across a row instead of down a column; they are not quite the same.

  The SQL Standard has a very wide range of numeric types. The idea is that any host language can find an SQL numeric type that matches one of its own. Remember that SQL is meant to be used with a host language and not by itself.

  Numbers in SQL are classified as either exact or approximate. An exact numeric value has a precision, p, and a scale, s. The precision is a positive integer that determines the number of significant digits in a particular radix. The standard says the radix can be either binary or decimal, so you need to know what your implementation does. The scale is a nonnegative integer that tells you how many radix places the number has.

  Today, there are not many base-ten platforms, so you almost certainly have a binary machine. However, a number can have one of many binary representations — twos-complement, ones-complement, high end or low end and various word sizes. The proper mental model of numbers in SQL is not to worry about the “bits and bytes ” level of the physical representation, but to think in abstract terms.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  RDBMS In-Depth

  Mastering SQL and PL/SQL Concepts, Database Design, ACID Transactions, and Practice Real Implementation of RDBM (English Edition)

  • Dr. Madhavi Vaidya(Author)
  • 2021(Publication Date)
  • BPB Publications

    (Publisher)

  Transaction Control Language (TCL): This is used to commit the transaction or to roll-back the transaction. The transaction is saved after execution or the transaction can be undone.

  Data definition language

  By using this component of SQL, it is possible to create the table. Tables consist of rows and columns. It is also known as an entity in relational DBMS. There are objects, which store the user data. Tables can be permanent or temporary. Syntax for creating a table in SQL is as follows: SQL>create table <tablename> (column1 datatype(size), column2 datatype(size)); A table can have a maximum of 1000 columns. Only one column of type LONG is allowed per table. SQL>create table student(rolno number(3),name varchar(10),total long,avg1 long) * ERROR at line 1: ORA-01754: a table may contain only one column of type LONG Actual query can be interpreted as follows: SQL>create table student(rollno number(3),name varchar(10),test_marks number(2));

  Constraints

  Constraints implemented on a table: Data integrity is one of the very important functions of DBMS.

  In this query we have seen how the data types are used. Following are the data types used while creating table: Data types supported in SQL:

  Data Type	Description
  Char(n)	To store fixed length string. Max length = 2000 bytes. For example. Name char(15)
  Varchar2(n)	To store variable length string. Max length = 4000 bytes. For example, Remark varchar(100)
  Long(n)	To store variable length string. Max length = 2 GB. For example, Project_doc long(5000)
  Number(p, s)	To store numeric data. Max number of significant digits = 38 For example, Salary number(9,2). The meaning of this (9,2) is that the total number of bytes will be 9. They will be placed as 7 digits before decimal point & 2 digits after decimal point.
  Date	To store Date data: Both date and time can be stored. For example, Hiredate Date. Generally, the value in date in stored in ‘dd-mon-yy’ format

  Sign up to read

  Learn more about book