Mysql interview questions and answers


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

mysql> SELECT col1,col2 FROM tablename ORDER BY col2 ASC;
  
Questions : 19how to Return total number of rows.
Answers : 19mysql> SELECT COUNT(*) FROM tablename;
  
Questions : 20How to Join tables on common columns.
Answer : 20mysql> 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 : 21How 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 : 22How 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 : 23How 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 : 24How 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 : 25How to Set a root password if there is on root password.
Answer : 25# mysqladmin -u root password newpassword
  
Questions : 26How to Update a root password.
Answer : 26# mysqladmin -u root -p oldpassword newpassword
  
Questions : 27How 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 : 28How 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 : 29How To update info already in a table and Delete a row(s) from a table.
Answer : 29mysql> 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 : 30How to Update database permissions/privilages.
Answer : 30mysql> flush privileges;
  
Questions : 31How to Delete a column and Add a new column to database
Answer : 31mysql> alter table [table name] drop column [column name];
mysql> alter table [table name] add column [new column name] varchar (20);
  
Questions : 32Change column name and Make a unique column so we get no dupes.
Answer : 32mysql> alter table [table name] change [old column name] [new column name] varchar (50);
mysql> alter table [table name] add unique ([column name]);
  
Questions : 33How to make a column bigger and Delete unique from table.
Answer : 33mysql> alter table [table name] modify [column name] VARCHAR(3);
mysql> alter table [table name] drop index [colmn name];
  
Questions : 34How to Load a CSV file into a table
Answer : 34mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);
  
Questions : 35How 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 : 36How to dump one database for backup.
Answer : 36# [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql
  
Questions : 37How to dump a table from a database.
Answer : 37# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql
  
Questions : 38Restore database (or database table) from backup.
Answer : 38# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql
  
Questions : 39How to Create Table show Example
Answer : 39mysql> 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 : 40How 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 ;

Comments

Post a Comment

Popular posts from this blog

SQL Tutorial with HSQLDB

Image
This assumes that you have Java setup on your machine as illustrated in the "simple" Java tutorial. Q.  Why use  HSQLB ? A.  It can be useful in a number of ways. Easy to get started and if you are a beginner, you can acquire or sharpen your SQL and database skills. It is open-sourced, hence you can use it for your self-taught projects as a database. An alternative to MySQL  database. It is used in commercial projects to write unit tests for your data access layer. Apache Derby database is another alternative. It is used in the ensuing tutorials on  JDBC  and  Hibernate  as the database. Step 1 : Download the latest version of HSQLB from   http://hsqldb.org/ . I downloaded  hsqldb-2.2.9.zip  at the time of writing and extracted zip files. The HSQLDB is unpacked under  c:\Tools  as as hown below. Step 2 : You can start the server with the following command. ? 1 c:\Tools\hsqldb-2.2.9>java -cp ./h...
Read more

Hibernate tutorial with HSQLDB

Image
This tutorial is basically an extension to the  "simple" Java  and  JDBC tutorials . Hibernate is an ORM (Object to Relational Mapping) tool. Step 1 :  First step is to bring in the relevant  Hibernate  framework jar files like  hibernate-xxx.jar ,  hibernate-annotations-xxxx.jar , and other dependent jar files that are transitively brought in from hibernate framework's pom.xml file. Here is the pom.xml file for simple Java project extended for hibernate. ? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 <project xmlns= " http://maven.apache.org/POM/4.0.0 " xmlns:xsi= " http://www.w3.org/2001/XMLSchema-instance "   xsi:schemaLocation= " http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd " >   <modelVersion...
Read more

Java Interview Questions & Answers: user defined key class

Image
This is one of my favorite core Java interview questions as not knowing this can cause subtle and intermittent issues in Java. The issues that arise from not understanding equals( ) and hashCode( ) contract can be hard to debug.    Q.  When providing a user defined key class for storing objects in a  Map (e.g. HashMap) , what methods do you have to provide or override (i.e. method overriding)? A.  You should override the  equals( ... )  and  hashCode( .. )  methods from the  Object  class. The default implementation of the  equals( ... )  and  hashCode( ... )  methods are inherited from the  java.lang.Object  class  uses an object instance’s memory location (e.g. MyObject@6c60f2ea). This can cause problems when two instances of car objects have the same color but the inherited  equals( .. )  will return false because it uses the memory location as oppose to the color, which is different ...
Read more