Showing posts with label MYSQL Interview Questions. Show all posts
Showing posts with label MYSQL Interview Questions. Show all posts

Friday 5 September 2014

Mysql Interview Questions And Answers - Atomicity, Dedlock, Client Program, Mysql Mode, Mysql Sensitivity Of Identifiers And Mysql Errors

Question: what is the difference between where and having clause in mysql?
Answer:
where can restrict each row or record
having clause restrict group of records.



Question: What is Atomicity?
Answer:
This means whether execution of "all" statements or "no" statements.



Question: What is Deadlock?
Answer:
A failure or inability to proceed due to two transactions having some data that the other needs.



Question: What is mysql client program ?
Answer:
It is a command-line program that acts as a text-based front end for the MySQL Server. It's used for issuing queries and viewing the results in the terminal window.

mysql --help
mysql --version
mysql --V
In many cases, a given option has both a long and a short form. Long options consist of a word preceded by double dashes(e.g. --) whereas short options consist of a single letter preceded by a single dash(e.g. -).


Question: What is difference between Interactive Mode and Batch Mode of mysql client?
Answer: In Interactive modes, you put simple query to get the result, In this results is return in window.
In Batch Mode, you put your all the queries in sql file and run it from mysql client OR from scheduler job.  



Question: What is difference between \g and \G?
Answer: Both are the statement terminator alike semicolon(;). 

\g end the query statement and output the result in horizontal.
\G end the query statement and output the result in vertically and mainly used when output is large. 


Question: How to manage MySQL case sensitivity of identifiers?
Answer: Database name and table name are stored as file, So In window its case-insenstive and in unix it is case senstive.
Column name, index, and trigger are not case sensitive.
Column aliases are not case sensitive
 

Question: What is difference between identifier-quoting- character and string-quoting-character?
Answer:
backtick(`) are identifer quoting character and used  quote for dbname, tableName and filedName
Single Quote('), Double Quote(") are string quoting character

 

Question: What is SQL Query to print the warnings?
Answer: show warnings;


Question: What is SQL Query to print the warnings in Vertically?

Answer: show warning\G;display the warnings in vertical tabs;


Question: How to use limit in mysql warning?

Answer: show warning limit 1,2 \G;display the warnings in vertical tabs for 2nd & 3rd;



Question: In Mysql, How to disable the sql notes?
Answer: set sql_notes=0;



Question: How to get the detail of error in mysql?
Answer: perror 13; 
show the error message detail




Monday 18 August 2014

Difference Between Primary Key And Unique Key And Foreign Key And Composite Key

Difference Between Primary Key And Unique Key And Foreign Key And Composite Key

Primary Key
  1. Primary Key can identify a row as uniquely 
  2. A table can have  only one primary key
  3. It can't be Null
  4. Indexing added automatically to Primary key


Foreign Key
  1. A FOREIGN KEY in one table reference to a Primary Key in another table
  2. A table can have  one OR more foreign key.
  3. It can be Null 
  4. Indexing not added automatically to Foreign key


Unique Key
  1. Unique Key can identify a row as uniquely.
  2. A table can have  one OR more unique key.
  3. It can be Null
  4. Indexing not added automatically to Unique Key


Composite Key
  1. A composite key contains at least one compound key and one more attribute. Composite keys may also include simple keys and non-key attributes.
  2. A table can have one OR more composite Key
  3. It can also be null.

See Example:
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `user_profile` (
  `user_id` int(11) NOT NULL,
  `address1` varchar(100) NOT NULL,
  `address2` varchar(100) NOT NULL,
  KEY `user_id` (`user_id`),
FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Primary Key: users.id
Foreign Key: user_profile.user_id
Unique Key:  users.email



Monday 21 July 2014

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation - Multilingual Website

In MySqL Database, we stored lots of data like city address, city description and city full description etc. Many times we stored different language's character like Chinese OR Japanese which take multibyte to single single character.
When we stored multibyte-character then it means we can also search the data from multibyte-character string.


So, Basically while we are working with multilingual character then we face two common type of problems.
  1. Multibyte Character not storing properly in Database.
  2. Search with multibyte character, MySql send error like "Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation"
Both are different issues,  but solution is Simple and same.




Problem 1: Character not storing properly: Collation must be "utf8_general_ci" to stored multibyte character properly in database.

Sample Table for store multi characters
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) CHARACTER SET utf8 DEFAULT NULL,
  `detail` text CHARACTER SET utf8,
  `created_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;



Problem 2: Search with multi character, Db is sending error: While Searching multicharacter, your database sending below error "Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation OR similar issues"

To get rid of this, you have to update the collation of your search fieldCollation must be utf8_general_ci for search multicharacter
ALTER TABLE `users1` CHANGE `name` `name` VARCHAR(256) CHARACTER SET utf8 COLLATE utf8_general_ci;

If you don't want to change the Collation of field we can also pass the utf8_general_ci explicity explicitly in search query.
select * from users WHERE name = "gdańsk" COLLATE utf8_unicode_ci


5 Best Related Posts are Following:1. Mysql Privileges - Types of privileages in MySql - How do I grant privileges in MySQL
2. Calculate a Sum of Time using MySQL
3. Mysql Interview Questions And Answers - Atomicity, Dedlock, Client Program, Mysql Mode, Mysql Sensitivity Of Identifiers And Mysql Errors
4. Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation - Multilingual Website
5. Difference Between MyISAM and innoDB - MySQL

Friday 17 January 2014

MySQL Interview Questions and Answers

MySQL Interview Questions and Answers

Question: Can you give me an MySQL Self Join Example?
SELECT l1.city, COUNT(l1.city) AS cityview,  l2.link_count FROM LOGS AS l1 LEFT JOIN (
    SELECT city,COUNT(city) AS link_count FROM LOGS WHERE TYPE="city_blurb_click" GROUP BY city 
    ) AS l2 ON l1.city=l2.city WHERE AND l1.TYPE="low_rate" GROUP BY city ORDER BY cityview DESc

Question: What is DDL, DML and DCL ? 
Data Definition Language deals with database schemas and descriptions of how the data should reside in the database, therefore language statements like CREATE TABLE or ALTER TABLE belong to DDL.
DML deals with data manipulation, and therefore includes most common SQL statements such SELECT, INSERT and Update etc.
Data Control Language includes commands such as GRANT, and mostly concerns with rights, permissions and other controls of the database system.


Question: How do you get the number of rows affected by query? 
SELECT COUNT (user_id) FROM users would only return the number of user_id’s.

Question: 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;


Question: 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.

Question: You wrote a search engine that should retrieve 10 results at a time, but at the same time you’d like to know how many rows there’re total. How do you display that to the user? 
SELECT SQL_CALC_FOUND_ROWS page_title FROM web_pages LIMIT 1,10; SELECT FOUND_ROWS(); The second query (not that COUNT() is never used) will tell you how many results there’re total, so you can display a phrase "Found 13,450,600 results, displaying 1-10". Note that FOUND_ROWS does not pay attention to the LIMITs you specified and always returns the total number of rows affected by query.


Question: How would you write a query to select all teams that won either 2, 4, 6 or 8 games? 
SELECT team_name FROM teams WHERE team_won IN (2, 4, 6, 8)


Question: How would you select all the users, whose phone number is null? 
SELECT user_name FROM users WHERE ISNULL(phonenumber);


Question: What does this query mean: SELECT user_name, user_isp FROM users LEFT JOIN isps USING (user_id) ?
It’s equivalent to saying
SELECT user_name, user_isp FROM users LEFT JOIN isps WHERE users.user_id=isps.user_id


Question: How do you find out which auto increment was assigned on the last insert?
SELECT LAST_INSERT_ID() will return the last value assigned by the auto_increment function. Note that you don’t have to specify the table name.


Question: What does –i-am-a-dummy flag to do when starting MySQL? 
Makes the MySQL engine refuse UPDATE and DELETE commands where the WHERE clause is not present.


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


Question: How can you see all indexes defined for a table? 
SHOW INDEX FROM questions;


Question: How would you change a column from VARCHAR(10) to VARCHAR(50)? 
ALTER TABLE questions CHANGE content CONTENT VARCHAR(50).


Question: How would you delete a column? 
ALTER TABLE answers DROP answer_user_id.

Question: How do I get the current time zone of MySQL?
SELECT @@global.time_zone, @@session.time_zone

Monday 13 January 2014

MySQL Functions - MySQL tutorial for beginners

MySQL Functions -  MySQL tutorial for beginners

Following are functions which is most command used in mysql

Least: Return Least value amont all
SELECT LEAST(4,3,8,-1,5);

Greatest: Return greatest value amont all
SELECT GREATEST(4,3,8,-1,5);

Interval(): takes a comparison value as its first argument. The remaining arguments should be a set of values in sorted order. INTERVAL() compares the first argument to the others and returns a value to indicate how many of them are less than or equal to it.
SELECT INTERVAL(2,1,2,3,4);

BETWEEN: The BETWEEN operator takes the two endpoint values of the range and returns true if a comparison value lies between them and The comparison is inclusive.
SELECT * FROM `products` WHERE id between 1 and 10

ISNULL(0): return true if value is NULL
select ISNULL(1)
Group By: The MySQL GROUP BY statement is used along with the SQL aggregate functions like SUM to provide means of grouping the result. IN: This is a clause, which can be used along with any MySQL query to specify a condition.

BETWEEN: This is a clause, which can be used along with any MySQL query to specify a condition.

UNION: Use a UNION operation to combine multiple result sets into one.

COUNT:  COUNT aggregate function is used to count the number of rows in a database table.

MAX: The MAX aggregate function allows us to select the highest (maximum) value for a certain column.

MIN: The MIN aggregate function allows us to select the lowest (minimum) value for a certain column.

AVG: The AVG aggregate function selects the average value for certain table column.

SUM: The SUM aggregate function allows selecting the total for a numeric column.

SQRT: This is used to generate a square root of a given number.

RAND: This is used to generate a random number using MySQL command.

CONCAT: This is used to concatenate any string inside any MySQL command.

Switch Case in Mysql
CASE  case_expression
   WHEN when_expression_1 THEN execute_statement1
   WHEN when_expression_2 THEN execute_statement1 
   ELSE execute_statement
END CASE;

Example 1
set @variable='10';
select (case when (@variable = '10') then "Equal to 10" else "Not Equal to 10" end)

Example 2
set @variable=10;
select (case when (@variable <=10) then "Less Than Equal to 10" else "Greater Than 10" end)

Example 3
set @variable=10;
select (case
            when (@variable <10) then "Less Than Equal to 10"
            when (@variable =10) then "Equal to 10"
        else "Greater Than 10" end)




mysqlsubqueries examples Scalar Subqueries Row Subqueries Column Subqueries Table Subqueries

mysqlsubqueries examples Scalar Subqueries Row Subqueries Column Subqueries Table Subqueries

It is query very similar to Normal queries in databases like
It may return one column
it may return more column
It may return single rows
It may return multiple rows

OR
any of above combination.

SubQuries are those queries which are used within Normal Queries.


For Example
select * from users where user_id =(select user_id from profile where type='user');
Here queries used within bracket is known as sub queris

There are four types of sub-queries.
a. Scalar Subqueries: return single columan of single row
select * from users where user_id =(select user_id from profile where type='user' limit 1);

b. Row Subqueries: Return single row
select u1.* from users As u1 left join (select user_id,country from profile limit 1) as u2 on u2.user_id=u1.user_id and u2.country=u1.country

c. Column Subqueries: Return one column of one or more rows
select * from users where user_id in(select user_id from profile where type='user');

d. Table Subqueries: return one more col of one/more rows
select u1.* from users As u1 left join (select user_id,country from profile) as u2 on u2.user_id=u1.user_id and u2.country=u1.country





Friday 21 June 2013

Stored Procedure - Advantage of Stored Procedure - Disadvantage of Stored Procedure

Advantage of  Stored Procedure

  • Stored procedures can be used to maintain data integrity and enforce database policy without relying on an external program to do so.
  • the DBA can add behavior that the application doesn't care about. For example, storing a modify date on each row.
  • You do not need to deploy to make a change.
  • Much faster than dynamic queries
  • Easier to expand a system
  • A small functionalists can be added in SP (Store Procedure)



Dis-Advantage of  Stored Procedure
  • Debugging is hard.
  • Source control can be a pain.
  • If you have a lot of functionality in store procedure it will making swapping between different database systems harder - It also creates more work if you want to support different database systems.
  • Developing stored procedures can be a fairly specialized task, especially as they get more complex.
  • Refactoring is harder. Renaming or changing where the store proc is might produce a bad effect.

Saturday 8 June 2013

Pending Interview Questions and Answers

Who is the Father of PHP?
Rasmus Lerdorf


Which programming language does PHP resemble to?
PHP resemble to pearl and C


How can we create a database using PHP and MySQL?
We can create MySQL database in php with the use of
mysql_create_db ("db_name");


Is variable name case sensitive?
Yes, In PHP variable name case sensitive. We cannot start a variable with number like $777name as a valid variable name starts with a letter or underscore, followed by any number of letters, numbers, or underscores.


How can we execute a php script using command line?
Just run the PHP CLI (Command Line Interface) program and provide the PHP script file name as the command line argument. For example, "php myScript.php", assuming "php" is the command to invoke the CLI program.


Differentiate between in_array() and array_search() in php?
in_array : Checks if a value exists in an array. array_search() : Searches an array for a given value and returns the corresponding key if successful.


How do you call a constructor for a parent class?
parent::constructor($value);


How to delete file in PHP ?
unlink($filename);


How to delete variable in PHP ?
unset($variable);


What is the urlencode and urldecode in php ?
Returns a string in which all non-alphanumeric characters except -_. have been replaced with a percent (%) sign followed by two hex digits and spaces encoded as plus (+) signs.


What is the use of the function htmlentities?
htmlentities Convert all applicable characters to HTML entities This function is identical to htmlspecialchars() in all ways, except with htmlentities(), all characters which have HTML character entity equivalents are translated into these entities.


Differentiate between strstr and stristr in php?
strstr() and stristr both are used to find the first occurence of the string only difference is stristr( ) is case insensitive.


How can we know the total number of elements of Array?
sizeof($array);
count($array);


How to secure your website?
  1. Validate Input.
  2. Use Auth & ACL.
  3. Protect my Session ID
  4. Preventing Cross Site Scripting (XSS) flaws
  5. SQL injection vulnerabilities
  6. Turning off error reporting and exposing to the site for hackers
  7. Protect secure data of website


What is PHP configuration file called?
php.ini.


What is a persistent cookie?
A persistent cookie is a cookie which is stored in a cookie permanently on the browser's computer. By default, cookies are created as temporary cookies which stored only in the browser's memory. When the browser is closed, temporary cookies will be erased automatically.


What are non-key columsn in database?
Answer: A non key attribute in sql server is a columns which can not be used to identify a record uniquely for example name or age columns in customer table


Wednesday 29 May 2013

MySQL Indexing

MySQL Indexing

Indexing

It is a data structure that improves the speed of search in database. We add the indexing on columns of table. We can one or more indexing on table.

Advantage of Indexing
It improve the speed of search. If we add indexing on any column, It will search faster.

Disdvantage of Indexing

INSERT and UPDATE statements take little more time on tables.


Following are different types of MySQL indexes

Column Index: In this type, we add indexing on single column
ALTER TABLE table_name ADD INDEX (field_name);

Concatenated Index: In this type, we add indexing on two OR more columns.
ALTER TABLE table_name ADD INDEX (field_name1, field_name2);

Use only when you are using query in below way
SELECT * FROM table_name  WHERE field_name1='text1' AND field_name2='text2';


Partial Index : In this type, we add indexing on single columan and on few number of character like first 10 character.
ALTER TABLE table_name ADD INDEX (field_name(10));

Following are some examples of indexing

create a table cities 
CREATE TABLE IF NOT EXISTS `cities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Add Indexing
alter table `cities` add index name (name)

List all the Indexing in cities table
show index from `cities`

Drop Indexing
alter table `cities` drop index name


Tuesday 28 May 2013

Mysql Update Statement with Join

Mysql Update Statement with Join

Yes, You can use update statement with mysql join also.

See Below Example

1) Create users Table
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `address` text,
  PRIMARY KEY (`id`)
);

2) Add records in users table.
INSERT INTO `users` (`id`, `name`, `address`) VALUES
(1, 'name 1', 'address 1'),
(2, 'name 2', 'address 2'),
(3, 'name 3', 'address 3'),
(4, 'name 4', 'address 4'),
(5, 'name 5', 'address 5'),
(6, 'name 6', 'address 6'),
(7, 'name 7', 'address 7'),
(8, 'name 8', 'address 8'),
(9, 'name 9', 'address 9'),
(10, 'name 10', 'address 10'),
(11, 'name 11', 'address 11'),
(12, 'name 12', 'address 12'),
(13, 'name 13', 'address 13'),
(14, 'name 14', 'address 14'),
(15, 'name 15', 'address 15');

3) Create salary Table
CREATE TABLE IF NOT EXISTS `salary` (
  `id` int(11) NOT NULL DEFAULT '0',
  `user_id` int(11) NOT NULL,
  `salary` float(10,2) NOT NULL,
  PRIMARY KEY (`id`)
);

4) Add Record in salary Table
INSERT INTO `salary` (`id`, `user_id`, `salary`) VALUES
(1, 9, 5000.00),
(2, 6, 3500.00);

Requirement : update the user record whose salary is 5000.

Solution with Inner join
update users As u INNER JOIN salary As s on s.user_id=u.id set u.name='my salary is 5000'  where s.salary=5000

Solution with LEFT join
update users As u LEFT JOIN salary As s on s.user_id=u.id set u.name='my salary is 5000'  where s.salary=5000

Solution with Right join
update users As u RIGHT JOIN salary As s on s.user_id=u.id set u.name='my salary is 5000'  where s.salary=5000


Wednesday 23 January 2013

MySQL Constraint Example

Q1: Table restrict for delete, if child table have records.

Q2: Mysql constraint restrict.

Q3. How to prevent from delete a parent record, if child exists.

Above all Questions have only one answer that is below with fine example.

Create a table products
CREATE TABLE products (
    id int(11) unsigned not null primary key AUTO_INCREMENT,
    name varchar(255) default null
)

Create a table reviews, as each products have one or more reviews

CREATE TABLE reviews (
    id int unsigned not null primary key AUTO_INCREMENT,
    review_by int(11) unsigned not null,
    product_id int(11) unsigned not null,
    FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE RESTRICT  ON UPDATE RESTRICT
  )


Now, if you try to delete a product having one or more review(s), you will get an mysql error similar to following.

Error
SQL query:
DELETE FROM `products` WHERE `products`.`id` =1
MySQL said:

#1451 - Cannot delete or update a parent row: a foreign key constraint fails (`abc`.`reviews`, CONSTRAINT `reviews_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`))

You can only delete the product, after deleted its reviews.



You can also add constraints after table creation, Use following query as example.
alter table `reviews` add constraint `FK_reviews_check` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE RESTRICT ON DELETE RESTRICT;
MySQL constraints Requirement
1. Both FOREIGN KEY table and REFERENCE table must have same Storage Engine.
2. Both FOREIGN KEY and Reference Id must have same datatype.
3. Both FOREIGN KEY and Reference Id must have same attributes means either both un-signed OR signed.

Tuesday 22 January 2013

MySQL Cascade Example

MySQL Cascade Example

Q1: When parent record deleted, child must be deleted automatically. But if child record deleted, parent should not effected.

Q2: I want to understand the mysql constraint cascade.

Q3. How a child record deleted automatically when parent deleted.

Above all Questions have only one answer that is below with fine example.

Create a table products
CREATE TABLE products (
    id int unsigned not null primary key AUTO_INCREMENT,
    name varchar(255) default null 
); 

Create a table reviews, as each product have one or more reviews

CREATE TABLE reviews (
    id int unsigned not null primary key AUTO_INCREMENT,
    review_by int(11) unsigned not null,
    product_id int(11) unsigned not null,
    points int(11),
    FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE  ON UPDATE CASCADE
  )


Now, if you delete a product, then its review (product's review) will be automatically deleted. but if you delete the child parent record will be untouched/uneffected.