MySQL Flashcards

1
Q

What is the default port for MySQL Server

A

3306

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What do DDL, DML, and DCL stand for

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What is the difference between CHAR and VARCHAR

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What are HEAP Tables

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Is there an object oriented version of MySQL library functions

A

MySQLi in PHP

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What is the storage engine for MySQL

A

The data is stored in the files using multiple techniques such as indexing, locking levels, capabilities and functions.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What is the difference between primary key and candidate key

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What are the different types of tables in MySQL

A

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.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What is the use of ENUM in MySQL

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What are the TRIGGERS that can be used in MySQL tables

A

The following TRIGGERS are allowed in MySQL:• BEFORE INSERT

    AFTER INSERT
    BEFORE UPDATE
     AFTER UPDATE
     BEFORE DELETE
     AFTER DELETE
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What is trigger

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What is Stored Procedure

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What is the difference between DELETE TABLE and TRUNCATE TABLE commands in MySQL

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What are the storage models of OLAP

A

The storage models in OLA are MOLAP, ROLAP, and HOLAP

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is the difference between primary key and unique key

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What is meant by transaction and ACID properties

A

Transaction is logical unit of work where either all or none of the steps should be performed. ACID is the abbreviation for Atomicity, Consistency, Isolation, and Durability that are properties of any transaction.

17
Q

What is the difference between BLOB and TEXT

A

BLOBs are binary large object holding huge data. 4 types of BLOB are TINYBLOB, BLOB, MEDIBLOB, and LONGBLOB. TEXT is case-sensitive BLOB. 4 types of TEXT are TINY TEXT, TEXT, MEDIUMTEXT, and LONG TEXT