MySQL Interview Questions – Set 09

by

What is the difference between NOW() and CURRENT_DATE()?

NOW() command is used to show current year, month, date with hours, minutes, and seconds while CURRENT_DATE() shows the current year with month and date only.

What are the different column comparison operators in MySQL?

The =, , <=, =, >, <>, , AND, OR or LIKE operator are the comparison operators in MySQL. These operators are generally used with SELECT statement.

How do you determine the location of MySQL data directory?

The default location of MySQL data directory in windows is C:mysqldata or C:Program FilesMySQLMySQL Server 5.0 data.

How to insert data in MySQL?

We can insert data in a MySQL table using the INSERT STATEMENT. This statement allows us to insert single or multiple rows into a table. The following is the basic syntax to insert a record into a table:

INSERT INTO table_name ( field1, field2,…fieldN )
VALUES ( value1, value2,…valueN );
If we want to insert more than one rows into a table, use the below syntax:

INSERT INTO table(field1, field2,…fieldN)
VALUES
(value1, value 2, …),
(value1, value2, …),
…
(value1, value2, …);

How to create a new user in MySQL?

A USER in MySQL is a record in the USER-TABLE. It contains the login information, account privileges, and the host information for MySQL account to access and manage the databases. We can create a new user account in the database server using the MySQL Create User statement. It provides authentication, SSL/TLS, resource-limit, role, and password management properties for the new accounts.

The following is the basic syntax to create a new user in MySQL:

CREATE USER [IF NOT EXISTS] account_name IDENTIFIED BY ‘password’;

What is the difference between CHAR and VARCHAR?

  1. CHAR and VARCHAR have differed in storage and retrieval.
  2. CHAR column length is fixed, while VARCHAR length is variable.
  3. The maximum no. of character CHAR data types can hold is 255 characters, while VARCHAR can hold up to 4000 characters.
  4. CHAR is 50% faster than VARCHAR.
  5. CHAR uses static memory allocation, while VARCHAR uses dynamic memory allocation.

What is ISAM?

It is a system for file management developed by IBM, which allows records to access sequentially or even randomly.

What are the different tables present in MySQL?

There are many tables that remain present by default. But, MyISAM is the default database engine used in MySQL. There are five types of tables that are present:

  • MyISAM
  • Heap
  • Merge
  • INNO DB
  • ISAM

What is MySQL?

MySQL is a multithreaded, multi-user SQL database management system which has more than 11 million installations. It is the world’s second most popular and widely-used open source database. It is interesting how MySQL name was given to this query language. The term My is coined by the name of the daughter of co-founder Michael Widenius’s daughter, and SQL is the short form of Structured Query Language. Using MySQL is free of cost for the developer, but enterprises have to pay a license fee to Oracle.

Formerly MySQL was initially owned by a for-profit firm MySQL AB, then Sun Microsystems bought it, and then Oracle bought Sun Microsystems, so Oracle currently owns MySQL.

MySQL is an Oracle-supported Relational Database Management System (RDBMS) based on structured query language. MySQL supports a wide range of operating systems, most famous of those include Windows, Linux & UNIX. Although it is possible to develop a wide range of applications with MySQL, it is only used for web applications & online publishing. It is a fundamental part of an open-source enterprise known as Lamp.

What is the Lamp?

The Lamp is a platform used for web development. The Lamp uses Linux, Apache, MySQL, and PHP as an operating system, web server, database & object-oriented scripting language. And hence abbreviated as LAMP.

How to delete a row in MySQL?

We can delete a row from the MySQL table using the DELETE STATEMENT within the database. The following is the generic syntax of DELETE statement in MySQL to remove one or more rows from a table:

DELETE FROM table_name WHERE Condition_specified;
It is noted that if we have not specified the WHERE clause with the syntax, this statement will remove all the records from the given table.