Q: Describe BLOB in MySQL. What is it used for?
Answer
BLOB or Binary Large Object can be used to store binary data in MySQL. Sometimes binary data like images need to be stored in SQL databases. For example you might want to store user photos along with other user details in the database table. Binary data of the user photo can be saved as a BLOB. By using BLOB, we will not require separate storage for images. BLOB helps in removing complexity and providing portability in such cases.
Q: What is self referencing foreign key? Give an example.
Answer
A foreign key which is stored in a table itself is called to be self referencing foreign key.
For example consider an Employee database table. It has employee_id as primary key as well as a manager_id which is employee_id of his manager. If we create a foreign key constraint, as a manager is also an employee, manager_id will reference to empolyee_id in the same table. The Employee table with self referencing foreign key manager_id can be created using below statement.
CREATE TABLE `Employee`(
`name` VARCHAR(25) NOT NULL,
`employee_id` CHAR(9) NOT NULL,
`manager_id` CHAR(9) NOT NULL,
`salary` decimal(10,2) NULL,
PRIMARY KEY(`employee_id`),
FOREIGN KEY (manager_id) REFERENCES employee(employee_id) ON DELETE CASCADE
);
Q: Both TIMESTAMP and DATETIME are used to store data and time. Explain difference between them and when should one be used?
Answer
Both TIMESTAMP and DATETIME store date time in YYYY-MM-DD HH:MM:SS format. While DATETIME stores provided date time, TIMESTAMP first converts provided time to UTC while storing and then again converts it back to server time zone upon retrieval. So if you need to serve different users in different countries using same time data, TIMESTAMP facilitates it. DATETIME simply stores provided date time without making any time zone related conversion.
Q: What are different TEXT data types in MySQL. What is difference between TEXT and VARCHAR?
Answer
Different text data types in MySQL include:
TINYTEXT,
TEXT,
MEDIUMTEXT and
LONGTEXT.
These data types have different maximum size. While TINYTEXT can hold string up to 255 characters, TEXT can hold up to 65,535 characters, MEDIUMTEXT can hold up to 16,777,215 characters and LONGTEXT can hold up to 4,294,967,295 characters.
VARCHAR is also a variable text data type with some difference. VARCHAR is stored inline in the database table while TEXT data types are stored elsewhere in storage with its pointer stored in the table. A prefix length is must for creating index on TEXT data types. TEXT columns do not support default values unlike VARCHAR.
Q: What different stored objects are supported in MySQL?
Answer
Different stored objects in MySQL include VIEW, STORED PROCEDURE, STORED FUNCTION, TRIGGER, EVENT.
VIEW - It is a virtual table based on a result set of a database query.
STORED PROCEDURE - It is a procedure stored in database which can be called using CALL statement. Stored procedure does not return a value.
STORED FUNCTION - It is like function calls which can contain logic. It returns a single value and can be called from another statement.
TRIGGER - Trigger is program which is associated with a database table which can be invoked before or after insert, delete or update operations.
EVENT - Event is used to run a program or set of commands at defined schedule.
Q: Explain the use of FEDERATED tables in MySQL
Answer
FEDERATED tables are tables through which MySQL provides a way to access database tables located in remote database servers. Actual physical data resides in remote machine but the table can be accessed like a local table. To use a federated table ENGINE=FEDERATED and a connection string containing user, remote hostname, port, schema and table name are provided in CREATE TABLE command something like below.
CREATE TABLE table_fed (
...
)
ENGINE=FEDERATED
CONNECTION='mysql://user@remote_hostname:port/federated_schema/table';
------------------------------------------------------------
#viastudy
#erdurgeshsingh
#durgeshbooks
.gif)
0 Comments