SQL - Data types

SQL - Data types

Learning Objectives​

​

​At the end of this session, the learners will be able to

• ​understand what are data types

• ​apply proper data types while creating tables in SQL

• ​differentiate between CHAR and VARCHAR data types

• Out of INT and CHAR, ​which data type to prefer when no calculations are to be perfomed?

Let's quickly revise what we have learnt so far...​

​

Multiple Choice

SQL stands for ...

• Simple Query Language

• Sqmple Query Language

• Structured Query Language

• Simple Query Learning

Multiple Choice

The sub-language of SQL that deals with the structure of the table is ...

• Data Control Language

• Data Manipulaiton Language

• Data Definition Language

• None of these

Multiple Choice

The SQL command to define the structure of the table is ...

• Make Table

• Draw Table

• Show Table

• Create Table

​Syntax of Create Table command

​CREATE TABLE student

(

adm_no INT,

name CHAR (20),

class CHAR (10),

marks INT

) ;

Now, let's learn about​

​Data Types

​Data Types

​They are used to identify the type of data e.g. consider the following table:

​

​

​

​

​​What type of value we are storing in these attributes?

​​Let's discuss.

​CHAR(n)

• ​Specifies character type data of length n where n could be any value from 0 to 255.

• CHAR is of fixed length, means, declaring CHAR (10) implies to reserve spaces for 10 characters.

• If data does not have 10 characters (for example, ‘city’ has four characters), MySQL fills the remaining 6 characters with spaces padded on the right.

​VARCHAR(n)

• ​Specifies character type data of length ‘n’ where n could be any value from 0 to 65535.

• But unlike CHAR, VARCHAR is a variable-length data type. That is, declaring VARCHAR (30) means a maximum of 30 characters can be stored but the actual allocated bytes will depend on the length of entered string.

• So ‘city’ in VARCHAR (30) will occupy the space needed to store 4 characters only.

​INT

• ​INT specifies an integer value. Each INT value occupies 4 bytes of storage.

​

• The range of values allowed in integer type are -2147483648 to 2147483647. For values larger than that, we have to use BIGINT, which occupies 8 bytes.

​FLOAT

• ​Holds numbers with decimal points.

​

• Each FLOAT value occupies 4 bytes.  

​DATE

• ​The DATE type is used for dates in 'YYYY-MM-DD' format. YYYY is the 4 digit year, MM is the 2 digit month and DD is the 2 digit date.

​

• The supported range is '1000-01-01' to '9999-12-31'.

​Think and Reflect

• ​Which data type out of Char and Varchar will you prefer for storing mobile number?

• ​How can I change the data type after creating the table?

Poll

Have I understood the concept and can I apply it practically?

• Confident

• I can try

• I have not understood

• I have doubts.

Let's do it practically ...​

​https://extendsclass.com/mysql-online.html