Mysql interview questions and answers

Mysql interview questions and answers are below

Questions : 1     how to do login in mysql with unix shell
Answers :1     By below method if password is pass and user name is root
# [mysql dir]/bin/mysql -h hostname -u root -p pass
     
Questions : 2     how you will Create a database on the mysql server with unix shell
Answers : 2     mysql> create database databasename;
     
Questions : 3     how to list or view all databases from the mysql server.
Answers : 3     mysql> show databases;
     
Questions : 4     How Switch (select or use) to a database.
Answers : 4     mysql> use databasename;
     
Questions : 5     How To see all the tables from a database of mysql server.
Answers : 5     mysql> show tables;
     
Questions : 6     How to see table's field formats or description of table .
Answers : 6     mysql> describe tablename;
     
Questions : 7     How to delete a database from mysql server.
Answers : 7     mysql> drop database databasename;
     
Questions : 8     How we get Sum of column
Answers : 8     mysql> SELECT SUM(*) FROM [table name];
     
Questions : 9     How to delete a table
Answers : 9     mysql> drop table tablename;
     
Questions : 10     How you will Show all data from a table.
Answers : 10     mysql> SELECT * FROM tablename;
     
Questions : 11     How to returns the columns and column information pertaining to the designated table
Answers : 11     mysql> show columns from tablename;
     
Questions : 12     How to Show certain selected rows with the value "pcds"
Answers : 12     mysql> SELECT * FROM tablename WHERE fieldname = "pcds";
     
Questions : 13     How will Show all records containing the name "sonia" AND the phone number '9876543210'
Answers : 13     mysql> SELECT * FROM tablename WHERE name = "sonia" AND phone_number = '9876543210';
     
Questions : 14     How you will Show all records not containing the name "sonia" AND the phone number '9876543210' order by the phone_number field.
Answer : 14     mysql> SELECT * FROM tablename WHERE name != "sonia" AND phone_number = '9876543210' order by phone_number;
     
Questions : 15     How to Show all records starting with the letters 'sonia' AND the phone number '9876543210'
Answers : 15     mysql> SELECT * FROM tablename WHERE name like "sonia%" AND phone_number = '9876543210';
     
Questions : 16     How to show all records starting with the letters 'sonia' AND the phone number '9876543210' limit to records 1 through 5.
Answers : 16     mysql> SELECT * FROM tablename WHERE name like "sonia%" AND phone_number = '9876543210' limit 1,5;
     
Questions : 16     Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with r.
Answer : 16     mysql> SELECT * FROM tablename WHERE rec RLIKE "^r";
     
Questions : 17     How you will Show unique records.
Answer : 17     mysql> SELECT DISTINCT columnname FROM tablename;
     
Questions : 18     how we will Show selected records sorted in an ascending (asc) or descending (desc)
Answer : 18     mysql> SELECT col1,col2 FROM tablename ORDER BY col2 DESC;

mysql> SELECT col1,col2 FROM tablename ORDER BY col2 ASC;

     
Questions : 19     how to Return total number of rows.
Answers : 19     mysql> SELECT COUNT(*) FROM tablename;
     
Questions : 20     How to Join tables on common columns.
Answer : 20     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
     
Questions : 21     How to Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs.
Answer : 21     # mysql -u root -p

mysql> use mysql;

mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));

mysql> flush privileges;
     
Questions : 22     How to Change a users password from unix shell.
Answers : 22     # [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password 'new-password'
     
Questions : 23     How to Change a users password from MySQL prompt. Login as root. Set the password. Update privs.
Answer : 23     # mysql -u root -p

mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');

mysql> flush privileges;
     
Questions : 24     How to Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.
Answer : 24     # /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("newrootpassword") where User='root';
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start
     
Questions : 25     How to Set a root password if there is on root password.
Answer : 25     # mysqladmin -u root password newpassword
     
Questions : 26     How to Update a root password.
Answer : 26     # mysqladmin -u root -p oldpassword newpassword
     
Questions : 27     How to allow the user "sonia" to connect to the server from localhost using the password "passwd". Login as root. Switch to the MySQL db. Give privs. Update privs.
Answers : 27     # mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to sonia@localhost identified by 'passwd';
mysql> flush privileges;
     
Questions : 28     How to give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.
Answers : 28     # mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');
mysql> flush privileges;
or
mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;
     
Questions : 29     How To update info already in a table and Delete a row(s) from a table.
Answer : 29     mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';
mysql> DELETE from [table name] where [field name] = 'whatever';
     
Questions : 30     How to Update database permissions/privilages.
Answer : 30     mysql> flush privileges;
     
Questions : 31     How to Delete a column and Add a new column to database
Answer : 31     mysql> alter table [table name] drop column [column name];
mysql> alter table [table name] add column [new column name] varchar (20);
     
Questions : 32     Change column name and Make a unique column so we get no dupes.
Answer : 32     mysql> alter table [table name] change [old column name] [new column name] varchar (50);
mysql> alter table [table name] add unique ([column name]);
     
Questions : 33     How to make a column bigger and Delete unique from table.
Answer : 33     mysql> alter table [table name] modify [column name] VARCHAR(3);
mysql> alter table [table name] drop index [colmn name];
     
Questions : 34     How to Load a CSV file into a table
Answer : 34     mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);
     
Questions : 35     How to dump all databases for backup. Backup file is sql commands to recreate all db's.
Answer : 35     # [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql
     
Questions : 36     How to dump one database for backup.
Answer : 36     # [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql
     
Questions : 37     How to dump a table from a database.
Answer : 37     # [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql
     
Questions : 38     Restore database (or database table) from backup.
Answer : 38     # [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql
     
Questions : 39     How to Create Table show Example
Answer : 39     mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));
Questions : 40     How to search second maximum(second highest) salary value(integer)from table employee (field salary)in the manner so that mysql gets less load?
Answers : 40    

By below query we will get second maximum(second highest) salary value(integer)from table employee (field salary)in the manner so that mysql gets less load?
SELECT DISTINCT(salary) FROM employee order by salary desc limit 1 , 1 ;
(This way we will able to find out 3rd highest , 4th highest salary so on just need to change limit condtion like LIMIT 2,1 for 3rd highest and LIMIT 3,1 for 4th
some one may finding this way useing below query that taken more time as compare to above query SELECT salary FROM employee where salary < (select max(salary) from employe) order by salary DESC limit 1 ;

OOPs interview questions and answers

Top OOPs interview questions and answers are below
Questions : 1     What is Object Oriented Programming ?
Answers : 1    

It is a problem solving technique to develop software systems. It is a technique to think real world in terms of objects. Object maps the software model to real world concept. These objects have responsibilities and provide services to application or other objects.
     
Questions : 2     What is a Class ?
Answers : 2    

A class describes all the attributes of objects, as well as the methods that implement the behavior of member objects. It is a comprehensive data type which represents a blue print of objects. It’s a template of object.
     
Questions : 3     What is an Object ?
Answers : 3    

It is a basic unit of a system. An object is an entity that has attributes, behavior, and identity. Objects are members of a class. Attributes and behavior of an object are defined by the class definition.
     
Questions : 4     What is the relation between Classes and Objects?
Answers : 4    

They look very much same but are not same. Class is a definition, while object is instance of the class created. Class is a blue print while objects are actual objects existing in real world. Example we have class CAR which has attributes and methods like Speed, Brakes, Type of Car etc.Class CAR is just a prototype, now we can create real time objects which can be used to provide functionality. Example we can create a Maruti car object with 100 km speed and urgent brakes.
     
Questions : 5     What are different properties provided by Object-oriented systems ?
Answers : 5    

Following are characteristics of Object Oriented System’s:-
Abstraction
It allows complex real world to be represented in simplified manner. Example color is abstracted to RGB.By just making the combination of these three colors we can achieve any color in world. It’s a model of real world or concept.
Encapsulation
The process of hiding all the internal details of an object from the outside world.
Communication
Using messages when application wants to achieve certain task it can only be done using combination of objects. A single object can not do the entire task. Example if we want to make order processing form. We will use Customer object, Order object, Product object and Payment object to achieve this functionality. In short these objects should communicate with each other. This is achieved when objects send messages to each other.
Object lifetime
All objects have life time. Objects are created, initialized, necessary functionalities are done and later the object is destroyed. Every object have there own state and identity, which differ from instance to instance.
     
Questions : 6     What is an Abstract class ?
Answers : 6    

Abstract class defines an abstract concept which can not be instantiated and comparing o interface it can have some implementation while interfaces can not. Below are some
points for abstract class:-
=>We can not create object of abstract class it can only be inherited in a below class.
=> Normally abstract classes have base implementation and then child classes derive from the abstract class to make the class concrete.
     
Questions : 7     What are Abstract methods?
Answers : 7    

Abstract class can contain abstract methods. Abstract methods do not have implementation. Abstract methods should be implemented in the subclasses which inherit them. So if an abstract class has an abstract method class inheriting the abstract class should implement the method or else java compiler will through an error. In this way, an abstract class can define a complete programming interface thereby providing its subclasses with the method declarations for all of the methods necessary to implement that programming interface. Abstract methods are defined using "abstract" keyword. Below is a sample code snippet.
abstract class pcdsGraphics
{
abstract void draw();
}
Any class inheriting from "pcdsGraphics" class should implement the "draw" method or else the java compiler will throw an error. so if we do not implement a abstract method the program will not compile.
     
Questions : 8     What is the difference between Abstract classes and Interfaces ?
Answers : 8    

Difference between Abstract class and Interface is as follows:-
Abstract class can only be inherited while interfaces can not be it has to be implemented.
Interface cannot implement any methods, whereas an abstract class can have implementation.
Class can implement many interfaces but can have only one super class.
Interface is not part of the class hierarchy while Abstract class comes in through inheritance.
Unrelated classes can implement the same interface.
     
Questions : 9     What is difference between Static and Non-Static fields of a class ?
Answers : 9    

Non-Static values are also called as instance variables. Each object of the class has its own copy of Non-Static instance variables. So when a new object is created of the same class it will have completely its own copy of instance variables. While Static values have only one copy of instance variables and will be shared among all the objects of the class.
     
Questions : 10     What are inner classes and what is the practical implementation of inner classes?
Answers : 10    

Inner classes are nested inside other class. They have access to outer class fields and methods even if the fields of outer class are defined as private.
public class Pcds
{
class pcdsEmp
{
// inner class defines the required structure
String first;
String last;
}
// array of name objects
clsName personArray[] = {new clsName(), new clsName(), new clsName()};
}
Normally inner classes are used for data structures like one shown above or some kind of helper classes.
     
Questions : 11     What is a constructor in class?
Answers : 11    

Constructor has the same name as the class in which it resides and looks from syntax point of view it looks similiar to a method. Constructor is automatically called immediately after the object is created, before the new operator completes. Constructors have no return type, not even void. This is because the implicit return type of a class' constructor is the class type itself. It is the constructor's job to initialize the internal state of an object so that the code creating an instance will have a fully initialized, usable object immediately.
     
Questions : 12     Can constructors be parameterized?
Answers : 12    

Yes we can have parameterized constructor which can also be termed as constructor overloading. Below is a code snippet which shows two constructors for pcdsMaths class one with parameter and one with out.
class pcdsMaths
{
double PI;
// This is the constructor for the maths constant class.
pcdsMaths()
{PI = 3.14;}
pcdsMaths(int pi)
{
PI = pi;
} }
     
Questions : 13     What is the use if instanceof keyword? and How do refer to a current instance of object?
Answers : 13    

"instanceof" keyword is used to check what is the type of object.
we can refer the current instance of object using "this" keyword. For instance if we have class which has color property we can refer the current object instance inside any of the method using "this.color".
     
Questions : 14     what is Bootstrap, Extension and System Class loader? or Can you explain primordial class loader?
Answers : 14    

There three types of class loaders:-
BootStrap Class loader also called as primordial class loader.
Extension Class loader.
System Class loader. Let’s now try to get the fundamentals of these class loaders.

Bootstrap Class loader
Bootstrap class loader loads those classes those which are essential for JVM to function properly. Bootstrap class loader is responsible for loading all core java classes for instance java.lang.*, java.io.* etc. Bootstrap class loader finds these necessary classes from “jdk/ jre/lib/rt.jar”. Bootstrap class loader can not be instantiated from JAVA code and is implemented natively inside JVM.
Extension Class loader
The extension class loader also termed as the standard extensions class loader is a child of the bootstrap class loader. Its primary responsibility is to load classes from the extension directories, normally located the “jre/lib/ext” directory. This provides the ability to simply drop in new extensions, such as various security extensions, without requiring modification to the user's class path.
System Class loader
The system class loader also termed application class loader is the class loader responsible for loading code from the path specified by the CLASSPATH environment variable. It is also used to load an application’s entry point class that is the "static void main ()" method in a class.
     
Questions : 15     what’s the main difference between ArrayList / HashMap and Vector / Hashtable?
Answers : 15    

Vector / HashTable are synchronized which means they are thread safe. Cost of thread safe is performance degradation. So if you are sure that you are not dealing with huge number of threads then you should use ArrayList / HashMap.But yes you can still
synchronize List and Map’s using Collections provided methods :-
List OurList = Collections.synchronizedList (OurList);
Map OurMap = Collections.synchronizedMap (OurMap);
     
Questions : 16     What are access modifiers?
Answers : 16    

Access modifiers decide whether a method or a data variable can be accessed by another method in another class or subclass.
four types of access modifiers:
Public: - Can be accessed by any other class anywhere.
Protected: - Can be accessed by classes inside the package or by subclasses ( that means classes who inherit from this class).
Private - Can be accessed only within the class. Even methods in subclasses in the same package do not have access.
Default - (Its private access by default) accessible to classes in the same package but not by classes in other packages, even if these are subclasses.
     
Questions : 17     Define exceptions ?
Answers : 17    

An exception is an abnormal condition that arises in a code sequence at run time. Basically there are four important keywords which form the main pillars of exception handling: try, catch, throw and finally. Code which you want to monitor for exception is contained in the try block. If any exception occurs in the try block its sent to the catch block which can handle this error in a more rational manner. To throw an exception manually you need to call use the throw keyword. If you want to put any clean up code use the finally block. The finally block is executed irrespective if there is an error or not.
     
Questions : 18     What is serialization?How do we implement serialization actually?
Answers : 18    

Serialization is a process by which an object instance is converted in to stream of bytes. There are many useful stuff you can do when the object instance is converted in to stream of bytes for instance you can save the object in hard disk or send it across the network.

In order to implement serialization we need to use two classes from java.io package ObjectOutputStream and ObjectInputStream. ObjectOutputStream has a method called writeObject, while ObjectInputStream has a method called readObject. Using writeobject we can write and readObject can be used to read the object from the stream. Below are two code snippet which used the FileInputStream and FileOutputstream to read and write from harddisk.