MySQL Interview Questions – Set 01

by

Syntax and Queries

MySQL Commands:
Show databases;
Create database db_name;
Use dbname;
Show tables;
Create table tb_name(id int, name varchar(20));
Desc tb_name;
Insert into tb_name values(101 , ‘name of person’);
Insert into tb_name (id) values(102);
Update tb_name set name=’person name’ where id=102;
Select * from tb_name;
Delete from tb_name where id=102;
Drop table tb_name;
Drop database db_name;
Rename table tb_old_name to tb_new_name;
Alter table customer add (remark varchar(20));
Alter table customer modify remark varchar(25);
Alter table customer modify remark varchar(20);
Alter table customer drop remark;
Alter table customer add remark varchar(20) first;
Alter table customer add remark varchar(20) after customer_name;

Import data into MySQL from any file

How to Import data into MySQL from any file:
Mysql –u root <db.sql (for database and tables)
Mysql –u root <data.sql (for data into tables)

Interview Scenario on MySQL:

Interview Scenario on MySQL:
There is a table named SAMPLE, and we want to delete all the data from the
table. Which is better option?

  1. delete * from SAMPLE
  2. truncate table SAMPLE

In delete cursor is on the current location, data is deleted from the table but memory is not released by the table, by which searching and sorting operation may take so much time.

While, in truncate cursor is on the starting location, data is deleted permanently and memory is released for other entries, by which searching and sorting operation does not take so much time than delete the data by delete command.

to delete all records 2 is best (only if there is not any other effect related to primary key).

to delete few records 1 is best.

What is a Left outer join?

This deals with SQL. Left outer join preserves the unmatched rows from the first (left) table, joining them with a NULL row in the shape of the second (right) table.

What is the difference between UNION and UNION ALL?

This deals with SQL. UNION only selects distinct values, UNION ALL selects all values.

Introduction to MySQL

Introduction

Note: “MySQL” it third party (“sun micro system”)
C:mysql –u root

Types of Table (Engine)

MyISAM: Foreign key constraint does not support
InnoDB: used to support foreign key constraint
BDB: support for UNIX environment
Heap: it is temporary or virtual table, which is created only in memory not in
hard disk
Merge: it is used, if we want to merge more than one table (it is also temporary
or virtual table)

Syntax:

Create table list ( — , — , — )engine=InnoDB;

How to Update a root password.

# mysqladmin -u root -p oldpassword newpassword

How you will Show all records not containing the name “sonia” AND the phone number ‘9876543210’ order by the phone_number field.

mysql> SELECT * FROM tablename WHERE name != “sonia” AND phone_number = ‘9876543210’ order by phone_number;

How to Delete a column and Add a new column to database

mysql> alter table [table name] drop column [column name];

mysql> alter table [table name] add column [new column name] varchar (20);

How to Join tables on common columns.

mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id