limited time offer

MySQL Data Types Interview Questions

Showing 1 - 4 of 4 results

What is meant by a decimal (5,2) in MySQL?

Here, 5 is the precision and 2 is the scale. The precision represents the number of significant digits that are stored for values, and the scale represents the number of digits that can be stored following the decimal point.

Example: Create Table

CREATE TABLE staff(
name VARCHAR(50) PRIMARY KEY,
email VARCHAR(50) NOT NULL,
salary DECIMAL(5, 2) NOT NULL);

Now Insert Data

INSERT INTO Shopping.staff (name, email, salary) VALUES ('Ram', 'ram@pb.com', 189.56);

Standard SQL requires that DECIMAL(5,2) be able to store any value with five digits and two decimals, so values that can be stored in the salary column range from -999.99 to 999.99.

What is the purpose of using the TIMESTAMP data type in MYSQL?

A TIMESTAMP data type is used to store the combination of date and time value which is 19 characters long.

The format of TIMESTAMP is YYYY-MM-DD HH:MM:SS. It can store data from "1970-01-01 00:00:01" UTC to "2038-01-19 03:14:07" UTC. By default, the current date and time of the server get inserted in the field of this data type when a new record is inserted or updated.

Example:

 CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

What are the differences between CHAR and VARCHAR data types in MySQL?

Both CHAR and VARCHAR data types are used to store string data in the field of the table. The differences between these data types are mentioned below:

  • CHAR data type is used to store fixed-length string data and the VARCHAR data type is used to store variable-length string data. For example: Use CHAR data type to store the values that has fixed length, like country code. For values that has variable length like names or titles use VARCHAR to save the space.
  • The CHAR data type allows you to store fixed-length strings with a maximum size of 255 characters. Whereas the VARCHAR data type allows you to store variable-length strings with a maximum size of 65,535 characters (it was limited to 255 characters prior to MySQL 5.0.3).
  • The storage size of the CHAR data type will always be the maximum length of this data type and the storage size of VARCHAR will be the length of the inserted string data. Hence, it is better to use the CHAR data type when the length of the string will be the same length for all the records.
  • CHAR is used to store small data whereas VARCHAR is used to store large data.
  • CHAR works faster and VARCHAR works slower.
  • When values are stored in a CHAR column, they are right-padded with spaces to the specified length, but in VARCHAR column values are not padded when they are stored. This means if you store the value 'ab' in a CHAR(4) column the value will be stored as 'ab  ', whereas the same value will be stored in VARCHAR(4) column as 'ab'.

Example:

CREATE TABLE location (
id INT AUTO_INCREMENT PRIMARY KEY,
address VARCHAR(50),
country_code CHAR(3));

What are the purposes of using ENUM and SET data types in MySQL?

ENUM data type is used in the MySQL database table to select any one value from the predefined list. The value of a particular field can be restricted by defining the predefined list as the field which is declared as ENUM will not accept any value outside the list.

The SET data type is used to select one or more or all values from the predefined list. This data type can also be used to restrict the field for inserting only the predefined list of values like ENUM.

An ENUM value must be one of those listed in the column definition, or the internal numeric equivalent thereof. The value cannot be the error value (that is, 0 or the empty string). For a column defined as ENUM('a','b','c'), values such as '', 'd', or 'ax' are illegal and are rejected.

A SET value must be the empty string or a value consisting only of the values listed in the column definition separated by commas. For a column defined as SET('a','b','c'), values such as 'd' or 'a,b,c,d' are illegal and are rejected.

Example: First create new table using below command:

CREATE TABLE clients (id INT AUTO_INCREMENT PRIMARY KEY, 
name VARCHAR(50), 
membership ENUM('Silver', 'Gold', 'Diamond'), 
interest SET('Movie', 'Music', 'Concert'));

Now use below command to insert data:

INSERT INTO Shopping.clients (name, membership,interest) VALUES ('Ram','Silver', 'Movie,Music');

this will return no error and it will store Movie,Music in interests column.

Now use below command to insert data:

INSERT INTO Shopping.clients (name, membership,interest) VALUES ('Ram','Silver,Gold', 'Movie,Music');

This will return error:

ERROR 1265 (01000): Data truncated for column 'membership' at row 1

 

Subscribe to MySQL Data Types Interview Questions