Tuesday, July 1, 2014

MySQL Interview Questions


What's the default port for MySQL Server?
3306
How do you start and stop MySQL on Windows? 
net start MySQL, net stop MySQL 
How do you start MySQL on Linux? 
/etc/init.d/mysql start 

What does tee command do in MySQL?
tee followed by a filename turns on MySQL logging to a specified file. It can be stopped by command notee

Can you save your connection settings to a conf file?
Yes, and name it ~/.my.conf. You might want to change the permissions on the file to 600, so that it's not readable by others

What does myisamchk do?
It compressed the MyISAM tables, which reduces their disk usage

Use MySQLdump to create a copy of the database?
mysqldump -h mysqlhost -u username -p mydatabasename > dbdump.sql


If the value in the column is repeatable, how do you find out the unique values? 
Use DISTINCT in the query, such as SELECT DISTINCT user_firstname FROM users; You can also ask for a number of distinct values by saying SELECT COUNT (DISTINCT user_firstname) FROM users; 

How do you return the a hundred books starting from 25th? 
SELECT book_title FROM books LIMIT 25, 100. The first number in LIMIT is the offset, the second is the number. 

SELECT team_name FROM teams WHERE team_won IN (2, 4, 6, 8) 
SELECT user_name FROM users WHERE ISNULL(user_phonenumber);

When would you use ORDER BY in DELETE statement? 
When you’re not deleting by row ID. Such as in DELETE FROM techpreparation_com_questions 
ORDER BY timestamp LIMIT 1. This will delete the most recently posted question in the table 
techpreparation_com_questions

How would you change a column from VARCHAR(10) to VARCHAR(50)? 
ALTER TABLE techpreparation_questions CHANGE techpreparation_content 
techpreparation_CONTENT VARCHAR(50). 
How would you delete a column? 
ALTER TABLE techpreparation_answers DROP answer_user_id

How can you see all indexes defined for a table? 
SHOW INDEX FROM techpreparation_questions;

How would you change a table to InnoDB?
ALTER TABLE techpreparation_questions ENGINE innodb;

How do I find out all databases starting with ‘tech’ to which I have access to? 
SHOW DATABASES LIKE ‘tech%’; 

CONCAT (string1, string2, string3) 
SELECT SUBSTR(title, 1, 10) from techpreparation_questions; 

What do % and _ mean inside LIKE statement? 
% corresponds to 0 or more characters, _ is exactly one character. 
ADDDATE(techpreparation_publication_date, INTERVAL 3 MINUTE) 

http://www.tecmint.com/basic-mysql-interview-questions-for-database-administrators/
How would you check if MySql service is running or not?
Issue the command “service mysql status” in ‘Debian’ and “service mysqld status” in RedHat. 

If the service is running/stop how would you stop/start the service?

To start MySql service use command as service mysqld start and to stop use service mysqld stop.

To connect or login to MySQL service, use command: mysql -u root -p.

To list all currently running databases run the command on mysql shell as: show databases;
To list all the tables of a database use the command on mysql shell as: show tables;
To get the Field Name and Type of a table use the command on mysql shell as: describe table_name;
To delte a specific table use the command on mysql shell as: drop table table_name;
To delte a specific database use the command on mysql shell as: drop database database-name;

http://javarevisited.blogspot.sg/2010/10/frequently-used-mysql-commands-part-1.html
MySql Commands
Viewing mysql processlist and kiling offending mysql process
./mysqladmin -u root processlist
Kill processes 
./mysqladmin -u root kill ProcessID
./mysqladmin -u root shutdown

./mysqladmin --host=localhost --port=3305 -u root shutdown //for second instance listening on port 3305

How to see mysql help
mysql> help alter;

Getting mysql server version
select version();

Displaying index from a mysql table
SHOW INDEX FROM   database.ORDERS;

Adding Keys(Indexes) to a table
alter table ORDERS add KEY `BY_CLIENT` (`CLIENT_ID`) 

Increasing no of connections for mysql
You can increase this value in main config file (e.g., /etc/my.cnf) using this syntax:
[mysqld]
set-variable=max_connections=250

UNIX_TIMESTAMP function
SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');

Diff between 2 dates 
SELECT TIMEDIFF('1997-12-31 23:59:59.000001','1997-12-30 01:01:01.000002');

Returns Time to seconds
SELECT TIME_TO_SEC('22:23:00');


Running mysql query from unix command prompt
mysql -u root -h <hostname>  <database name >-e "UPDATE ORDERT SET TYPE ='PARTIAL' WHERE TYPE='FULL'

Viewing mysql command history

There is one hidden file called .mysql-history ,on which all commands are stored which we typed in  MySQL console. its generally resides in home directory.

copying data from one table to another
insert into ORDERS_TMP select * from ORDERS 

Rename Table
ALTER TABLE new_table RENAME old_table;

modifying a column
alter table ORDERS modify column BRAND varchar(15) default NULL

Dropping columns from the table
ALTER TABLE `database`.`ORDERS` DROP COLUMN `BRAND`;

Repair the table 
REPAIR TABLE TableNAme

Adding Keys(Indexes) to a table
alter table ORDERS add KEY `BY_CLIENT` (`CLIENT_ID`) (here CLIENT_ID is a column in ORDers table)

What is Heap table?
Tables that are present in the memory are called as HEAP tables. These tables are commonly known as memory tables. These memory tables never have values with data type like “BLOB” or “TEXT”. They use indexes which make them faster.

How many TRIGGERS are possible in MySql?
There are only six triggers are allowed to use in MySQL database and they are.
Before Insert, After Insert, Before Update, After Update, Before Delete, After Delete

What is BLOB?
- BLOB stands for binary large object.
- It that can hold a variable amount of data.
There are four types of BLOB based on the maximum length of values they can hold:
- TINYBLOB
- BLOB
- MEDIUMBLOB
- LONGBLOB

What is TEXT?
TEXT is case-insensitive BLOB. The four types of TEXT are:
- TINYTEXT
- TEXT
- MEDIUMTEXT
- LONGTEXT

What is the difference between BLOB and TEXT?
- In BLOB sorting and comparison is performed in case-sensitive for BLOB values
- In TEXT types sorting and comparison is performed case-insensitive.

How is MyISAM table stored?

MyISAM table is stored on disk in three formats.
- ‘.frm’ file – storing the table definition
- ‘.MYD’ (MYData) - data file
- ‘.MYI’ (MYIndex) – index file

Explain advantages of MyISAM over InnoDB?

- MyISAM follows a much more conservative approach to disk space management – storing each MyISAM table in a separate file, which can be further compresses, if required.
- InnoDB stores the tables in tablespace. Further optimization is difficult with them.

How would you get the current date in Mysql?
By using SELECT CURRENT_DATE();

How would concatenate strings in MySQL?
With the use of - CONCAT (string1, string2, string3)
- MySQL time stamps are seen to a user in a readable format : YYYY-MM-DD HH:MM:SS.

MySQL Transaction
CREATE TABLE EMPLOYEE
(
  EMPLOYEE_ID   CHAR(5)  NOT NULL,
  EMPLOYEE_NAME VARCHAR(50) NOT NULL,
PRIMARY KEY (EMPLOYEE_ID))
ENGINE = INNODB;

Autocommit mode
SET AUTOCOMMIT=0;
SET AUTOCOMMIT=1;
Implicit mode:
COMMIT
ROLLBACK
SAVEPOINT
START TRANSACTION

References
http://www.tecmint.com/basic-mysql-interview-questions-for-database-administrators/
http://javarevisited.blogspot.sg/2010/10/frequently-used-mysql-commands-part-1.html
http://javarevisited.blogspot.com/2010/10/frequently-used-mysql-commands-part-2.html

No comments:

Post a Comment

Labels

Review (554) System Design (293) System Design - Review (189) Java (178) Coding (75) Interview-System Design (65) Interview (60) Book Notes (59) Coding - Review (59) to-do (45) Knowledge (39) Linux (39) Interview-Java (35) Knowledge - Review (32) Database (30) Design Patterns (29) Product Architecture (28) Big Data (27) Soft Skills (27) Miscs (25) MultiThread (25) Concurrency (24) Cracking Code Interview (24) Career (22) Interview - Review (21) Java - Code (21) Operating System (21) Distributed (20) Interview Q&A (20) OOD Design (20) System Design - Practice (19) Security (17) Algorithm (15) How to Ace Interview (15) Brain Teaser (14) Google (13) Linux - Shell (13) Spark (13) Spring (13) Code Quality (12) How to (12) Interview-Database (12) Interview-Operating System (12) Redis (12) Tools (12) Architecture Principles (11) Company - LinkedIn (11) Testing (11) Resource (10) Solr (10) Amazon (9) Cache (9) Search (9) Web Dev (9) Architecture Model (8) Better Programmer (8) Company - Uber (8) Interview - MultiThread (8) Java67 (8) Math (8) OO Design principles (8) SOLID (8) Scalability (8) Cassandra (7) Git (7) Interview Corner (7) JVM (7) Java Basics (7) Machine Learning (7) NoSQL (7) C++ (6) Design (6) File System (6) Highscalability (6) How to Better (6) Kafka (6) Network (6) Restful (6) Trouble Shooting (6) CareerCup (5) Code Review (5) Company - Facebook (5) Hash (5) How to Interview (5) JDK Source Code (5) JavaScript (5) Leetcode (5) Must Known (5) Be Architect (4) Big Fata (4) C (4) Company Product Architecture (4) Data structures (4) Design Principles (4) Facebook (4) GeeksforGeeks (4) Generics (4) Google Interview (4) Hardware (4) JDK8 (4) Optimization (4) Product + Framework (4) Shopping System (4) Source Code (4) Web Service (4) node.js (4) Back-of-Envelope (3) Company - Pinterest (3) Company - Twiiter (3) Company - Twitter (3) Consistent Hash (3) GOF (3) Game Design (3) GeoHash (3) Growth (3) Guava (3) Interview-Big Data (3) Interview-Linux (3) Interview-Network (3) Java EE Patterns (3) Javarevisited (3) Map Reduce (3) Math - Probabilities (3) Performance (3) Puzzles (3) Python (3) Resource-System Desgin (3) Scala (3) UML (3) geeksquiz (3) AI (2) API Design (2) AngularJS (2) Behavior Question (2) Bugs (2) Coding Interview (2) Company - Netflix (2) Crawler (2) Cross Data Center (2) Data Structure Design (2) Database-Shard (2) Debugging (2) Docker (2) Elasticsearch (2) Garbage Collection (2) Go (2) Hadoop (2) Html (2) Interview - Soft Skills (2) Interview-Miscs (2) Interview-Web (2) JDK (2) Logging (2) POI (2) Papers (2) Programming (2) Project Practice (2) Random (2) Software Desgin (2) System Design - Feed (2) Thread Synchronization (2) Video (2) ZooKeeper (2) reddit (2) Ads (1) Advanced data structures (1) Algorithm - Review (1) Android (1) Approximate Algorithms (1) Base X (1) Bash (1) Books (1) C# (1) CSS (1) Chrome (1) Client-Side (1) Cloud (1) CodingHorror (1) Company - Yelp (1) Counter (1) DSL (1) Dead Lock (1) Difficult Puzzles (1) Distributed ALgorithm (1) Eclipse (1) Facebook Interview (1) Function Design (1) Functional (1) GoLang (1) How to Solve Problems (1) ID Generation (1) IO (1) Important (1) Internals (1) Interview - Dropbox (1) Interview - Project Experience (1) Interview Tips (1) Interview-Brain Teaser (1) Interview-How (1) Interview-Mics (1) Interview-Process (1) Jeff Dean (1) Joda (1) LeetCode - Review (1) Library (1) LinkedIn (1) LintCode (1) Mac (1) Micro-Services (1) Mini System (1) MySQL (1) Nigix (1) NonBlock (1) Process (1) Productivity (1) Program Output (1) Programcreek (1) Quora (1) RPC (1) Raft (1) RateLimiter (1) Reactive (1) Reading (1) Reading Code (1) Refactoring (1) Resource-Java (1) Resource-System Design (1) Resume (1) SQL (1) Sampling (1) Shuffle (1) Slide Window (1) Spotify (1) Stability (1) Storm (1) Summary (1) System Design - TODO (1) Tic Tac Toe (1) Time Management (1) Web Tools (1) algolist (1) corejavainterviewquestions (1) martin fowler (1) mitbbs (1)

Popular Posts