MySQL Flashcards
What is the default port for MySQL Server
3306
What do DDL, DML, and DCL stand for
DDL is the abbreviation for Data Definition Language dealing with database schemas as well as the description of how data resides in the database. An example is CREATE TABLE command. DML denotes Data Manipulation Language such as SELECT, INSERT etc. DCL stands for Data Control Language and includes commands like GRANT, REVOKE etc.
What is the difference between CHAR and VARCHAR
When the table is created, CHAR is used to define the fixed length of the table and columns. The length value could be in the range of 1-255. VARCHAR command is given to adjust the column and table length as required.
What are HEAP Tables
Basically HEAP tables are in-memory and used for high speed temporary storages. But TEXT or BLOB fields are not allowed within them. They also do not support AUTO INCREMENT.
Is there an object oriented version of MySQL library functions
MySQLi in PHP
What is the storage engine for MySQL
The data is stored in the files using multiple techniques such as indexing, locking levels, capabilities and functions.
What is the difference between primary key and candidate key
Primary key in MySQL is use to identify every row of a table in unique manner. For one table there is only one primary key. One of the candidate keys is the primary key and the candidate keys can be used to reference the foreign keys.
What are the different types of tables in MySQL
MyISAM is the default table that is based on the sequential access method.
HEAP is the table that is used for fast data access but data will be lost if the table or system crashes. InoDB is the table that supports transactions using the COMMIT and ROLL BACK commands. BDB can support transactions similar to InnoDB but the execution is slower.
What is the use of ENUM in MySQL
Use of ENUM will limit the values that can go into a table. For instance; the user can create a table giving specific month values and other month values would not enter into the table.
What are the TRIGGERS that can be used in MySQL tables
The following TRIGGERS are allowed in MySQL:• BEFORE INSERT
AFTER INSERT
BEFORE UPDATE
AFTER UPDATE
BEFORE DELETE
AFTER DELETE
What is trigger
By definition, a trigger or database trigger is a stored program executed automatically to respond to a specific event e.g., insert, update or delete occurred in a table. The database trigger is powerful tool for protecting the integrity of the data in your MySQL databases.
What is Stored Procedure
A Stored Procedure (“SP”) is a procedure (written in SQL and other control statements) stored in a database which can be called by the database engine and connected programming languages.
What is the difference between DELETE TABLE and TRUNCATE TABLE commands in MySQL
Basically DELETE TABLE is logged operation and every row deleted is logged. Therefore the process is usually slow. TRUNCATE TABLE also deletes rows in a table but it will not log any of the rows deleted. The process is faster in comparison. TRUNCATE TABLE can be rolled back and is functionally similar to the DELETE statement using no WHERE clause.
What are the storage models of OLAP
The storage models in OLA are MOLAP, ROLAP, and HOLAP
What is the difference between primary key and unique key
While both are used to enforce uniqueness of the column defined but primary key would create a clustered index whereas unique key would create non-clustered index on the column. Primary key does not allow ‘NULL’ but unique key allows it.
