mysql authentication
GRANT REPLICATION SLAVE ON *.* TO 'root'@'172.31.22.56' IDENTIFIED BY 'admin123';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.31.22.54' WITH GRANT OPTION;
GRANT REPLICATION SLAVE ON *.* TO 'root'@'172.31.22.54' IDENTIFIED BY 'admin123';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.31.22.54' IDENTIFIED BY 'admin123';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.31.22.54' IDENTIFIED BY 'admin123';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'admin123';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'admin123';
DROP USER 'root'@'%';
DROP USER 'root'@'172.31.22.58';
drop USER 'root'@'localhost' where Host='localhost'
CREATE USER 'root'@'%' IDENTIFIED BY 'admin123';
drop USER 'root'@'%' where Password='admin123'
CREATE USER 'root'@'%' IDENTIFIED BY 'Cyberlinks';
CHANGE MASTER TO MASTER_HOST='172.31.22.53', MASTER_USER='root', MASTER_PASSWORD='admin123', MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=66883888;
CHANGE MASTER TO MASTER_HOST='172.31.22.53', MASTER_USER='root', MASTER_PASSWORD='admin123', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=23713;
Mysql Questions
This article introduces MYsql Database queries:
Suppose we have to tables
1.Employee
2.Employee Salary
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`photo` varchar(100) DEFAULT NULL,
`first_name` varchar(100) DEFAULT NULL,
`last_name` varchar(100) DEFAULT NULL,
`gender` varchar(11) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
`mobile` varchar(100) DEFAULT NULL,
`password` varchar(100) DEFAULT NULL,
`active` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
employees
id name
1 Admin
2 Employee1
3 Employee2
4 Employee3
5 Employee4
CREATE TABLE `employee_salary` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`employee_id` int(10) DEFAULT NULL,
`salary` varchar(50) DEFAULT NULL,
`created_on` datetime DEFAULT NULL,
`created_by` int(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=32 DEFAULT CHARSET=latin1
id employee_id salary
1 1 10000
2 2 20000
3 3 10000
4 4 40000
5 5 30000
1. Find Second highest paid employees withou using LIMIT function.
Query:
SELECT MAX(salary) FROM employee_salary WHERE salary <(SELECT MAX(salary) FROM employee_salary)
2.Skip repeating values
Query:
SELECT salary FROM employee_salary GROUP BY salary
3.Reset Auto-increment next value
Query:
ALTER TABLE tablel SET AUTO_INCREMENT=val;
4.Find the Size of database
Query:
SELECT
s.schema_name AS 'database',
SUM(t.data_length) AS DATA,
SUM( t.index_length ) AS INDEXES,
SUM(t.data_length) + SUM(t.index_length) AS 'Mb Used',
IF(SUM(t.data_free)=0,'',SUM(t.data_free)) AS 'Mb Free',
COUNT(table_name) AS TABLES
FROM information_schema.schemata s
LEFT JOIN information_schema.tables t ON s.schema_name = t.table_schema
GROUP BY s.schema_name
5.First and Last Date of Month
Last day of next month:
SELECT LAST_DAY (DATE_ADD(CURDATE(), INTERVAL 30 DAY))
Last day of Previous month:
SELECT LAST_DAY (DATE_SUB(CURDATE(), INTERVAL 30 DAY))
First day of next month
SELECT CONCAT(LEFT(CURDATE() + INTERVAL 1 MONTH, 8), '01');
6.Display every Nth row
Query:
SELECT id
FROM employees
GROUP BY id
HAVING MOD(id, N) = 0;
7.Age in years
SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW()) - TO_DAYS('1991-07-01')), '%Y') + 0;
8.Difference between two dates
SELECT DATEDIFF('2002-07-15','2001-07-15')
9. Update Table using Join
UPDATE employee e LEFT JOIN employee_salary s ON e.id=s.employee_id
SET s.salary='50000'
where e.first_name='Admin'
10. DELETE Table using Join
Delete employee_salary FROM employee_salary LEFT JOIN employee ON employee.id=employee_salary.employee_id
WHERE employee.first_name='Admin'
11- How insert male and female table data in other table .
t1 table t2 table
id name id name
1 manoj 1 neha
2 ravi 2 rubi
3 satntosh 3 isha
Merge above tble in t3 like
Id Name Type
1 manoj M
2 ravi M
3 satntosh M
4 neha F
5 rubi F
Ans:-
INSERT INTO t3( name,TYPE ) (SELECT name, 'M' AS TYPE FROM t1) UNION (SELECT name, 'F' AS TYPE FROM female )
INSERT INTO t3( name, salery,TYPE ) (SELECT name, salery, 'm' AS TYPE FROM male)
UNION
(SELECT name, salery, 'f' AS TYPE FROM female )
Q:- select emp coming Birthday sql query
Ans: select replace(dob,Year(dob),Year(curdate())) as dob from emplist having dob <= date_add(curdate(), INTERVAL 15 DAY) and
dob >=
curdate();
ans : 1st create bubble sort ascending or descending then print 2 no of array
according to order :--
<?php
/*
function __autoload($class_name) {
include "test/".$class_name . '.php';
}
$obj = new MyClass1();
//$obj2 = new MyClass2();
*/
class bubchek {
function swap($items,$firstIndex,$secondIndex){
$temp =$items[$firstIndex];
$items[$firstIndex]=$items[$secondIndex];
$items[$secondIndex]= $temp;
return $items[$secondIndex];
}
function buble_sort($items){
//echo $items;
$len= count($items);
for($i=0;$i<$len;$i++){
for($j=0;$j<$len-$i-1;$j++){
//echo $items[$j];
//echo $items[$j+1];
if($items[$j] > $items[$j+1]){
//echo $j;
//echo $items[$j];
//$this->swap($items,$j,$j+1);
$temp =$items[$j];
$items[$j]=$items[$j+1];
$items[$j+1]= $temp;
}
}
}
return $items;
}
}
$obj = new bubchek();
//$vaarray=array(3,2,4,5,1);
$chkval =$obj->buble_sort(array(3,2,4,5,1));
print_r($chkval);
?>
==============================================================
<?php
//how to find highest and second highest number in an array without using max function
/*
$array = array('200', '15','69','122','50','201');
$max_1 = $max_2 = 0;
//$arraval= array();
for($i=0; $i<count($array); $i++)
{
if($array[$i] > $max_1)
{
$max_2 = $max_1;
$max_1 = $array[$i];
}
else if($array[$i] > $max_2)
{
$max_2 = $array[$i];
}
}
echo "Max=".$max_1;
echo "<br />";
echo "Smax 2=".$max_2;
*/
//Find string palindrome or not in PHP without using strrev()
$mystring="LEVEL";
$newstrarray= array();
$newstrarray=str_split($mystring);
$len=sizeof($newstrarray);
$newstring = "";
for($i=$len;$i >= 0;$i--){
$newstring.=$newstrarray[$i];
}
echo $newstring ;
echo "<br>";
if($mystring == $newstring){
echo "parldorme".$mystring;
}else{
echo "Not===parldorme".$mystring;
}
//Sort an array of integers without using PHP built-in sort functions
/*
$srtArray=array(2,8,9,5,6,3);
for ($i=0; $i<count($srtArray); $i++) {
for ($j=0; $j<count($srtArray); $j++) {
// Compare two elements of array
if ($srtArray[$j] > $srtArray[$i]){
$tmp = $srtArray[$i];
$srtArray[$i] = $srtArray[$j];
$srtArray[$j] = $tmp;
}
}
}
//Print an array after sorting
for($i=0;$i<count($srtArray);$i++){
echo $srtArray[$i]."<br>\n";
}
*/
?>
<?
//reverse string with out using any function.
$num=3456;
$rev=0;
$num=0;
while($num>0){
$rev = $rev*10;
$rev=$rev+$num%10;
$num = (int)($num/10);
}
echo $rev;
echo "==========================================================";
$a='Manoj';
for($i=0;$i<=5000;$i++)
if($a[$i])
$count++;
else
break;
for($j=$count;$j>=0;$j--){
echo $a[$j];
}
?>
finish
=============
Question site
https://www.toptal.com/sql/interview-questions
No comments:
Post a Comment