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

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.

Monday, 15 October 2012

MySQL Expressions

MySQL Expressions

Expressions are a common element of SQL statements( statements are select, delete and update).
they often occur in the where clause of select, delete & update.

For Example:
DELETE FROM users WHERE id=5;
Here id=5 are expressions.

Following are the different components of SQL Expression:

  •  Numeric Expressions
    Exact value Literal are: 11,12.33, 44.333
    Approximate Literal are 1233E2, 44333E3
    Operations on decimal value have precision upto 64 digits, currently its 30 character.
    In Approximate, mantissa allows upto 53 bit of precision which is about 15 decimal digits.
  •  String Expressions
    String like 'sql' or mysql
  • Temporal Expressions
    It include dates, times and datetime values like Select ‘2012-01-01’ + interval 10 day
  • Functions like current(), version(), avg(), sum()
  • Regular Express (Regex)

Sunday, 14 October 2012

MySQL Delete Table Example

MySQL Delete Table Example

Create a table
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `company` varchar(150) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;


Delete record(s) from table
DELETE FROM users WHERE id=5;
It will delete a record where id is 5

Delete record from two table when they are in join.
DELETE users1,users2 FROM users1,users2,users3 WHERE tusers1.id=users2.id AND users2.id=users3.id


Keep in Mind following points..


  • If we specify the LOW_PRIORITY keyword in query, execution of the DELETE is delayed until no other clients are reading from the table.
  • For MyISAM tables, if we specify the QUICK keyword in query, then the storage engine will not merge index leaves during delete, which  will speed up certain kind of delete.
  • If we specify the IGNORE causes MySQL to ignore all errors during the process of deleting rows. 


Replace Table - Update Field, Move Records to another table MySQL

Replace Table - Update Field, Move Records to another table MySQL

Create a table
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `company` varchar(150) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


search for 'arun' in 'arun kumar' and replace with 'pardeep'
select replace('arun kumar','arun','pardeep') 
It will print "pardeep kumar"

search for 'php' in company field and replace with 'php-tutorial-php'
UPDATE users SET company = REPLACE(company, 'php', 'php-tutorial-php');

Replace a table
REPLACE INTO T SELECT * FROM T;

Note:
If you run a replace on existing keys on table "users", and table "profile" references "users" with a forgein key constraint ON DELETE CASCADE,
If you replace table "users"
then table "users" will be updated - but table "profile" will be emptied due to the DELETE before INSERT.


Mysql Update Syntax - Update Multiple Records

Mysql Update Syntax - Update Multiple Records

How to create a new table? 

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `company` varchar(150) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


Following are different ways to update table.
update `users` set `company`='php tutorial';
update `users` set `company`='php tutorial',`name` ='myname'
update `users` set `company`='php tutorial',`name` ='myname' where id>100


You can also update multiple table in single query.
UPDATE users,profile SET users.company='php-tutorial-php' WHERE profile.id=users.id and profile.name='php';



Saturday, 30 June 2012

MySQL interview Questions and Answers For Experienced

MySQL interview Questions and Answers For Experienced

Question: What's MySQL ?
Answer: MySQL is an open source relational database management system (RDBMS) that uses Structured Query Language, the most popular language for adding, accessing, and processing data in a database. Because it is open source, anyone can download MySQL and tailor it to their needs in accordance with the general public license. MySQL is noted mainly for its speed, reliability, and flexibility.



Question: How many columns we can update in single mysql query?
Answer:we can update 64 columns in single mysql query.



Question: What is maximum length of column name, table name and database name?
Answercolumn name can be upto 64 chars, table name can be upto 64 and database name can be upto 64 chars.


Question: How will you export tables as an XML file in MySQL?AnswerMYSQL’s query browser has a provision called “Export Result Set” which allows the tables to be exported as XML.



Question: What is DDL, DML and DCL ? AnswerData 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 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?
AnswerSELECT COUNT (user_id) FROM users 
would only return the number of user_id’s.


Question: What are the differences between MySQL_fetch_array(), MySQL_fetch_object(), MySQL_fetch_row()?
AnswerMysql_fetch_object returns the result from the database as objects while mysql_fetch_array returns result as an array. This will allow access to the data by the field names. E.g. using mysql_fetch_object field can be accessed as $result->name and using mysql_fetch_array field can be accessed as $result->[name]. mysql_fetch_row($result):- where $result is the result resource returned from a successful query executed using the mysql_query() function.




Question: If the value in the column is repeatable, how do you find out the unique values? AnswerUse 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? AnswerSELECT book_title FROM books LIMIT 25, 100. 
The first number in LIMIT is the offset, the second is the number.




Question: How to create case insensitive query in mysql? 
Answeruse binary for case insensitive query. For Example
select * from users where name= binary 'bob';



Question: What is name of collation, for case sensitive? 
Answerlatin1_general_cs.
When you want to search data as case sensitive, use latin1_general_cs



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? AnswerSELECT user_name FROM users WHERE ISNULL(user_phonenumber);



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: On executing the DELETE statement I keep getting the error about foreign key constraint failing. What do I do? AnswerWhat it means is that so of the data that you’re trying to delete is still alive in another table. Like if you have a table for universities and a table for students, which contains the ID of the university they go to, running a delete on a university table will fail if the students table still contains people enrolled at that university. Proper way to do it would be to delete the offending data first, and then delete the university in question. Quick way would involve running SET foreign_key_checks=0 before the DELETE command, and setting the parameter back to 1 after the DELETE is done. If your foreign key was formulated with ON DELETE CASCADE, the data in dependent tables will be removed automatically.


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


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


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


Question: How would you change a table to InnoDB?
ALTER TABLE questions ENGINE innodb;


Question: When you create a table, and then run SHOW CREATE TABLE on it, you occasionally get different results than what you typed in. What does MySQL modify in your newly created tables?
1. VARCHARs with length less than 4 become CHARs
2. CHARs with length more than 3 become VARCHARs.
3. NOT NULL gets added to the columns declared as PRIMARY KEYs
4. Default values such as NULL are specified for each column


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


Question: How do you concatenate strings in MySQL?
CONCAT (string1, string2, string3)



Question: How do you get a portion of a string?
SELECT SUBSTR(name, 1, 10) from questions;


Question: What’s the difference between CHAR_LENGTH and LENGTH?
The first is, naturally, the character count. The second is byte count. For the Latin characters the numbers are the same, but they’re not the same for Unicode and other encodings.



Question: How do you convert a string to UTF-8?

SELECT (question USING utf8);


Question: What do % and _ mean inside LIKE statement?
% corresponds to 0 or more characters, _ is exactly one character.


Question: What does + mean in REGEXP?
At least one character. Appendix G. Regular Expressions from MySQL manual is worth perusing before the interview.



Question: How to get the second highest salary of an employee from a employee table?

You can do this in two ways1) select salary from employee order by salary desc limit 1,1
2) select max(salary) from employee where max(salary)<(select max(salary) from employee)


Question: How do you get the month from a timestamp?
SELECT MONTH(timestamp) from questions;



Question: What are the techniques for query optimization? 
Following are the some techniques of query optimization
1) Use Indexes
2) User limit (Don't pull extra records)
3) Use only required fields (Don't pull extra fields which you are not going to use)


Question: How do you offload the time/date handling to MySQL?
SELECT DATE_FORMAT(timestamp, ‘%Y-%m-%d’) from questions; A similar TIME_FORMAT function deals with time.



Question: Get Highest salary from each department with employee name?
select e.name, e.salary, e.department from employee as e INNER join(select max(salary) as m_s, department from employee as me group by department) on e.salary=me.m_s and e.department=me.department. 



Question: What you can use Regular Expression for in MySQL? Support your answer with an example.

Regular expressions in MySql are used in queries for searching a pattern in a string.
* Matches 0 more instances of the string preceding it.
+ matches 1 more instances of the string preceding it.
? Matches 0 or 1instances of the string preceding it.
. Matches a single character.
[abc] matches a or b or z
| separates strings
^ anchors the match from the start.

REGEXP can be used to match the input characters with the database.




Question: What’s the difference between Unix timestamps and MySQL timestamps?
Internally Unix timestamps are stored as 32-bit integers, while MySQL timestamps are stored in a similar manner, but represented in readable YYYY-MM-DD HH:MM:SS format.



Question:  What is difference between mysql_connect and mysql_pconnect?

Mysql_connect:

- Opens a new connection to the database
- The database connection can be closed
- Opens the page every time the page is loaded.

Mysql_pconnect:

- Opens a persistent connection to the database.
- The database connection can not be closed.
- The page need not be opened every time the page is loaded.


Question: What are ENUMs used for in MySQL?
You can limit the possible values that go into the table. CREATE TABLE months (month ENUM ‘January’, ‘February’, ‘March’,…); INSERT months VALUES (’April’);


Question: How to get Second Highest salary of an employee?
   select salary from employee order by salary desc limit 1,1



Question: What are the data type supported by mysql?

Type
Size
Description
CHAR[Length]
Length bytes
A fixed-length field from 0 to 255 characters long.
VARCHAR(Length)
String length + 1 bytes
A fixed-length field from 0 to 255 characters long.
TINYTEXT
String length + 1 bytes
A string with a maximum length of 255 characters
TEXT
String length + 2 bytes
A string with a maximum length of 65,535 characters.
MEDIUMTEXT
String length + 3 bytes
A string with a maximum length of 16,777,215 characters.
LONGTEXT
String length + 4 bytes
A string with a maximum length of 4,294,967,295 characters.
TINYINT[Length]
1 byte
Range of -128 to 127 or 0 to 255 unsigned.
SMALLINT[Length]
2 bytes
Range of -32,768 to 32,767 or 0 to 65,535 unsigned.
MEDIUMINT[Length]
3 bytes
Range of -8,388,608 to 8,388,607 or 0 to 16,777,215 unsigned



INT[Length]
4 bytes
Range of -2,147,483,648 to 2,147,483,647 or 0 to 4,294,967,295 unsigned
BIGINT[Length]
8 bytes
Range of -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 or 0 to 18,446,744,073,709,551,615 unsigned
FLOAT
4 bytes
A small number with a  floating decimal point.
DOUBLE[Length, Decimals]
8 bytes
A large number with a floating decimal point.
DECIMAL[Length, Decimals]
Length +1 bytes or
Length + 2 bytes
A DOUBLE stored as a string, allowing for a fixed decimal point.
DATE
3 bytes
In the format YYYY-MM-DD
DATETIME
8 bytes
In the format YYYY-MM-DD HH:MM:SS.
TIMESTAMP
4 bytes
In the format YYYYMMDDHHMMSS; acceptable range ends in the year 2037.
TIME
3 bytes
In the format of HH:MM:SS.
ENUM
1 or 2 bytes
Short for enumeration, that is, each column can have one of several possible values.
SET
1, 2, 3, 4, or 8 bytes
Like ENUM except that each column can have more than one of several possible values.


Question:How to get current MySQL version?
SELECT VERSION();


Question: What are the drivers in MySQL?
  • PHP Driver
  • JDBC Driver
  • ODBC Driver
  • C WRAPPER
  • PYTHON Driver
  • PERL Driver
  • RUBY Driver
  • CAP11PHP Driver


Question: How do you login to MySql using Unix shell?
mysql -h hostname -u username -p password
After pressing enter key, It will prompt for password, Now again type the password.




Question: What does myisamchk do?
myisamchk compress the MyISAM tables, which reduces their disk or memory usage.



Question: What are federated tables?
federated tables which allow access to the tables located on other databases on other servers.



Question:How can we get the number of rows affected by query?
SELECT COUNT (user_id) FROM users;



Question: How to display top slary of 5 employeees?
SELECT * FROM users order by salary desc limit 0,5






5 Best Related Posts are Following:
1.Multiple column ordering in Zend Framework
2. Mysql Privileges - Types of privileages in MySql - How do I grant privileges in MySQL
3. Difference Between Primary Key And Unique Key And Foreign Key And Composite Key
4. Difference Between MyISAM and innoDB - MySQL
5. MySQL Functions - MySQL tutorial for beginners