Web Technology Experts Notes

Monday, 6 April 2015

MySQL Interview Questions and Answers for Experienced

Question: Can I concatenate multiple MySQL rows into one field?
use GROUP_CONCAT function
For Example

SELECT group_concat( city, '--' ) AS all_city FROM `cities`WHERE 1 LIMIT 0 , 30

Output:
New York--,London--,Chicago--,Frankfurt--,Paris--

Question: How to output MySQL query results in csv format?
Its quite simple.

First get the queries with column which you want to write.
Seconds Use OUTFILE (filename with location), FIELDS TERMINATED BY (field separator), ENCLOSED BY and LINES TERMINATED (Line Separator) BY for format.

SELECT city from cities INTO OUTFILE 'city.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'

Question: How do you set a default value for a MySQL Datetime column?
You can add default time, when data type is timestamp.
See Example:

CREATE TABLE `mytable` (`id` INT NULL ,`created_at` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL ,PRIMARY KEY ( `id` ) ) ENGINE = InnoDB;

Question: Difference between utf8_general_ci and utf8_unicode_ci?
utf8_unicode_ci is more accurately as compare to utf8_general_ci.
utf8_unicode_ci is support more lanaguages as compare to utf8_general_ci.
utf8_general_ci is faster at comparisons and sorting as compare to utf8_general_ci.

Question: How to make multiple column as unique?
use UNIQUE Constraint for creating multiple column as unique.
For Example

ALTER TABLE `mytable` ADD UNIQUE `unique_index`(`user`, `email`, `address`);

Question: How to find duplicate email records in MySQL?

SELECT u1.first_name, u1.last_name, u1.email FROM users as u1
INNER JOIN (
    SELECT email FROM users GROUP BY email HAVING count(id) > 1
    ) u2 ON u1.email = u2.email;

Question: How can you reset AUTO_INCREMENT for user table?

ALTER TABLE users AUTO_INCREMENT = 1

Question: How to make duplicate table in MySQL?

CREATE TABLE users_new LIKE users; //Copy the structure
INSERT users_new SELECT * FROM users; //copy the table rows

Question: How to Group by multiple column(i.e email and ssn)?

select * from users group by concat(email,"-",ssn);

Question: MySQL: @variable vs. variable. Whats the difference?
@variable are user-defined variable used in statements which retain its values during the same session is active.
variable are procedure variable used in store procedure only. It never retain its value. Each time store procedure called its value set to null automatic.

Question: What is query to show list of Stored Procedures?

SHOW PROCEDURE STATUS;

Question: What is query to show list of functions?

SHOW FUNCTION STATUS;

Question: How to check last queries executed on MySQL?
check mysql.log

Question: How to get the sizes of the tables of a mysql database?

SELECT table_name AS "Table", 
round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" 
FROM information_schema.TABLES 
WHERE table_schema = "geob2cdev" ORDER BY (data_length + index_length) DESC

Question: How to check specific table details?

SHOW TABLE STATUS WHERE Name = 'users';

Question: How to rename a table?

RENAME TABLE `mytable` TO `geob2cdev`.`mytable1` ;

Question: How to rename a column?

ALTER TABLE `mytable1` CHANGE `created_at` `created_at1` TIMESTAMP;

Question: How to insert current datetime in column?
Use Now() function

INSERT INTO `users` (`id`,`name`,create_time) VALUES (NULL,  'this is test\'s message',now());

Question: Can we have multiple order by in single Query?
Yes, We can have

select * from users where upper(name) = upper('Hello') order by name asc, id desc

Question: How to remove stored PROCEDURE?

DROP PROCEDURE storedProcedureName

Question: How to remove stored PROCEDURE if exist?

DROP PROCEDURE IF EXISTS storedProcedureName

Sunday, 5 April 2015

Ajax Interview Questions and Answers for 3 Year Experienced

Question: How to send ajax request to another domain using JSONP?
JSONP is simple way to overcome XMLHttpRequest same domain policy. With use of JSONP you can send the request to another domain. JSONP is "JSON with Padding", In JSONP we include another domain URL with a parameter i.e callback.
callback's value have a function name which must be present in your webpage.
See Example:

<script>
function myCalllbackFunction(data){
  console.log(data);
} 
</script>

 <script src="//twitter.com/status/user_timeline/padraicb.json?count=10&amp;callback=myCalllbackFunction" type="text/javascript"></script>

Question: CanAJAX requests retain PHP login info?
Yes, I can be.

Question: How can I detect that the Internet connection?
Very simple.
Make a ajax call to reliable server which always return data. To check the Internet connection, request the data to the server. If ajax call return data, internet connection is working else not. But make sure, ajax response must not come from browser cache. For this set the cache:false in ajax call.

Question: How to send Query string(If query string is full URL) in Ajax call?
use encodeURIComponent() before sending query string.
var url=encodeURIComponent(url);

Question: How to send ajax call with https on http page (Suppose, both https and http are same domain)?
If both protocall have same domain, then you can send all ajax request with https on both (https and https page).
Add Following in ajax Header

Access-Control-Allow-Origin: https://www.youwebsitedomain.com

Question: How to get response header of AJax Call?

$.ajax({
    url: '/ajax',
    success:function(data, textStatus, request){
      console.log(request.getResponseHeader('some_header'));
    
    }
});

Question: Can I handle the redirection of xmlhttprequest in Ajax Call?
No,

Question: How can I get the values of all sected checkbox?

$("input:checkbox[name=type]:checked").each(function()
{
//JS array of selected checkbox
    
});

Question: How to pass entire form data in Ajax?

   function onSubmitFunction(){
    $.ajax({
        type: "POST",
        url: "/ajax",
        data: $('#mytestForm').serialize(),
        success: function(msg){
            //console.log(msg); 
        }
    });
    return false;
}

Question: How to get the error messages in jQuery Ajax?

   function onSubmitFunction(){
    $.ajax({
        type: "POST",
        url: "/ajax",
        data: $('#mytestForm').serialize(),
        success: function(msg){
            //console.log(msg); 
        },
       error: function (request, error) {
              console.log(arguments);              
          }
    });
    return false;
}