PIVOT with MySQL

DROP TABLE IF EXISTS tbl_sales_revenue;
CREATE TABLE tbl_sales_revenue(dept_id int, year int, revenue double);
INSERT INTO tbl_sales_revenue(dept_id, year, revenue)
VALUES
 (1,2007,1200),
 (1,2008,1000),
 (1,2009,2000),
 (2,2007,3000),
 (2,2008,4000),
 (2,2009,5000),
 (3,2007,500),
 (3,2008,1200),
 (3,2009,1500);
 
SELECT
 IFNULL(dept_id,'Total') AS 'Department',
 a.2007, a.2008, a.2009,
 a.2007 + a.2008 + a.2009 AS Total
FROM (
 SELECT
   dept_id,
   SUM(IF(year=2007, revenue, 0)) As '2007',
   SUM(IF(year=2008, revenue, 0)) As '2008',
   SUM(IF(year=2009, revenue, 0)) As '2009'
 FROM tbl_sales_revenue
 GROUP BY dept_id WITH ROLLUP
 ) AS a; 

Check all linked servers connectivity status

Applied to SQL Server 2005 or greater.
I often work with a lot of linked servers (over 100), so I created this script to check their connectivity.

DECLARE @SRV_NAME NVARCHAR(50);
DECLARE @Msg NVARCHAR(4000);
DECLARE @retval INT;
DECLARE @LinkedServers
TABLE(SRV_NAME NVARCHAR(50)
 , SRV_PROVIDERNAME VARCHAR(50)
 , SRV_PRODUCT VARCHAR(50)
 , SRV_DATASOURCE VARCHAR(50)
 , SRV_PROVIDERSTRING VARCHAR(50)
 , SRV_LOCATION VARCHAR(50)
 , SRV_CAT VARCHAR(50));
DECLARE @ErrorLog
TABLE(LogDate SMALLDATETIME
 , SRV_NAME NVARCHAR(50)
 , ErrorMsg NVARCHAR(4000));
INSERT INTO @LinkedServers
EXEC sp_linkedservers
DECLARE Cur CURSOR FOR
SELECT SRV_NAME FROM @LinkedServers
OPEN Cur
FETCH FROM Cur
INTO @SRV_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
 PRINT @SRV_NAME;
 EXEC @retval = sys.sp_testlinkedserver @SRV_NAME; 
END TRY
BEGIN CATCH
 SET @Msg = ERROR_MESSAGE();
 INSERT INTO @ErrorLog(LogDate, SRV_NAME, ErrorMsg)
 VALUES(GETDATE(), @SRV_NAME, @Msg);
END CATCH
FETCH FROM Cur
INTO @SRV_NAME
END
CLOSE Cur
DEALLOCATE Cur
SELECT * FROM @ErrorLog;

Restore a MySQL database from backup file

First, create an empty database:
CREATE DATABASE new_database_name;

Use this syntax to restore:
$mysql -u root -p new_database_name < backup_file_name.sql

Note that this mysql command run from the folder you install MySQL, for example: C:\Program Files\MySQL\MySQL Server 5.1\bin
The following command I run from my computer to restore vtigercrm510 database from its backup file vtigercrm510bk.sql:
CREATE DATABASE vtigercrm;
C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -u root -p vtigercrm < rm510bk.sql

Get SQL with most disk read

SELECT t1.USERNAME,
DISK_READS,
EXECUTIONS,
ROUND(DISK_READS / DECODE(EXECUTIONS, 0, 1, EXECUTIONS)) "Reads/Execs",
SQL_TEXT
FROM dba_users t1, v$session, v$sqlarea
WHERE PARSING_USER_ID = USER_ID
AND ADDRESS = SQL_ADDRESS(+)
AND DISK_READS > 10000
ORDER BY DISK_READS DESC, EXECUTIONS DESC

Get tablespaces usage

SELECT t1.TABLESPACE_NAME AS "Tablespace Name",
 t1.BYTES AS "Bytes Used",
 t2.BYTES AS "Bytes Free",
 t2.largest AS "Largest",
 ROUND(((t1.BYTES - t2.BYTES)/t1.BYTES)*100,2) AS "% Used"
FROM
 (
  SELECT TABLESPACE_NAME,
   SUM(BYTES) BYTES
  FROM  dba_data_files
  GROUP BY TABLESPACE_NAME
 ) t1,
 (
  SELECT TABLESPACE_NAME,
   SUM(BYTES) BYTES ,
   MAX(BYTES) largest
  FROM  dba_free_space
  GROUP BY TABLESPACE_NAME
 ) t2
WHERE  t1.TABLESPACE_NAME = t2.TABLESPACE_NAME
ORDER BY ((t1.BYTES - t2.BYTES)/t1.BYTES) DESC

Get numbers of objects belong to each user

SELECT USERNAME,
COUNT(DECODE(o.TYPE#, 2,o.OBJ#,'')) "TableS",
COUNT(DECODE(o.TYPE#, 1,o.OBJ#,'')) "Indices",
COUNT(DECODE(o.TYPE#, 5,o.OBJ#,'')) "Synonyms",
COUNT(DECODE(o.TYPE#, 4,o.OBJ#,'')) "Views",
COUNT(DECODE(o.TYPE#, 6,o.OBJ#,'')) "Sequences",
COUNT(DECODE(o.TYPE#, 7,o.OBJ#,'')) "Procedures",
COUNT(DECODE(o.TYPE#, 8,o.OBJ#,'')) "Functions",
COUNT(DECODE(o.TYPE#, 9,o.OBJ#,'')) "Packages",
COUNT(DECODE(o.TYPE#,12,o.OBJ#,'')) "Triggers",
COUNT(DECODE(o.TYPE#,10,o.OBJ#,'')) "Dependencies"
FROM obj$ o, dba_users u
WHERE u.USER_ID = o.OWNER# (+)
GROUP BY USERNAME
ORDER BY USERNAME

sqlite-net - Project Hosting on Google Code

"sqlite-net is an open source, minimal library to allow .NET and Mono applications to store data in SQLite 3 databases. It is written in C# 3.0 and is meant to be simply compiled in with your projects. It was first designed to work with MonoTouch on the iPhone, but should work in any other CLI environment"
Read more on sqlite-net - Project Hosting on Google Code

Get tables without primary key

SELECT OWNER, TABLE_NAME
FROM dba_tables t1
WHERE   NOT EXISTS(
       SELECT  'TRUE'
       FROM    dba_constraints t2
       WHERE   t1.TABLE_NAME = t2.TABLE_NAME
         AND t2.CONSTRAINT_TYPE='P')
AND OWNER NOT IN ('SYS','SYSTEM')
ORDER BY OWNER, TABLE_NAME

Rename multiple tables in MySQL

DROP TABLE IF EXISTS tbl_test;
CREATE TABLE tbl_test(a int, b int);
DROP TABLE IF EXISTS tbl_test2;
CREATE TABLE tbl_test2(a int, b int);
RENAME TABLE
tbl_test TO tbl_test1,
tbl_test2 TO tbl_test3;

Zuora Object Query Language - API Documentation

Zuora supports a simple SQL-like query language for use with the query call. A basic familiarity with SQL is needed in order to construct queries; therefore, that basic knowledge is assumed in the following discussions.

Syntax
The basic query syntax is:

select field_names from object

select field_names from object where filter_statements

Read more on Zuora Object Query Language - API Documentation

Copy a table in MySQL

Use structure CREATE TABLE new_table LIKE old_table

The following queries copy table vtiger_account in vtigerCRM
First, create an empty table with the same structure
CREATE TABLE vtiger_account_bk LIKE vtiger_account;

Second, copy data
INSERT INTO vtiger_account_bk SELECT * FROM vtiger_account;

Get indexes of a MySQL table

Get all indexes
SHOW INDEXES FROM your_table_name;

Get primary keys only
SHOW INDEXES FROM your_table_name
WHERE key_name = "PRIMARY";

Get indexes of vtiger_account table in vtigerCRM
SHOW INDEXES FROM vtiger_account;

Insert multiple rows into a table

DROP TABLE IF EXISTS tbl_test;
CREATE TABLE tbl_test(a varchar(10), b int);
INSERT INTO tbl_test(a, b)
VALUES
('Name 1', 1),
('Name 2', 2),
('Name 3', 3),
('Name 4', 4);
SELECT * FROM tbl_test;

Cross Table Delete

Delete all accounts with their statuses are inactive and delete related contacts belong to deleted accounts.
Be careful when executing this script on your database.

DELETE account.*, contact.*
FROM account a
INNER JOIN contact c
ON a.account_id = c.account_id
WHERE a.is_active = 0

Drop several tables

Be very careful when executing this script on your database.
You should create test tables such as tbl_test1, tbl_test2, and tbl_test3 and practice.

DROP TABLE tbl_test1, tbl_test2, tbl_test3;

Generate script for MySQL table

The following script gets create table script of vtiger_account table in vtigerCRM.

SHOW CREATE TABLE vtiger_account;

Find current queries

SELECT sqltext.text,
 req.session_id,
 req.status,
 req.command,
 req.cpu_time,
 req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

Get database clusters information

SELECT t1.OWNER,
TABLESPACE_NAME,
t1.CLUSTER_NAME,
TABLE_NAME,
TAB_COLUMN_NAME,
CLU_COLUMN_NAME
FROM dba_clusters t1, dba_clu_columns t2
WHERE t1.CLUSTER_NAME = t2.CLUSTER_NAME
ORDER BY t1.OWNER, TABLESPACE_NAME, t1.CLUSTER_NAME, TABLE_NAME

Transaction with MySQL

SET autocommit=0;
START TRANSACTION;
DROP TABLE IF EXISTS tbl_test;
CREATE TABLE tbl_test(a int, b int);
INSERT INTO tbl_test(a,b) VALUES(1,2);
COMMIT;
INSERT INTO tbl_test(a,b) VALUES(3,4);
ROLLBACK;
SELECT * FROM tbl_test;
Return:
a b
1 2

Get all tables/views in a database

The following query returns all tables/views in database vtigercrm510 and numbers of records for each table.

SELECT table_name, table_type, engine, table_rows
FROM information_schema.tables t1
WHERE table_schema = 'vtigercrm510'
ORDER BY table_name DESC;

Get all vtigerCRM quotes

The following query gets vtigerCRM Quote List

SELECT t1.quote_no AS 'Quote No'
, t1.subject AS 'Subject'
, t1.quotestage AS 'Quote Stage'
, t4.potentialname AS 'Potential Name'
, t5.accountname AS 'Account Name'
, t1.total AS 'Total'
, t3.user_name AS 'Assigned To'
FROM vtiger_quotes t1
INNER JOIN vtiger_crmentity t2 ON t1.quoteid = t2.crmid AND t2.setype='Quotes'
INNER JOIN vtiger_users t3 ON t2.smownerid = t3.id
LEFT JOIN vtiger_potential t4 ON t1.potentialid = t4.potentialid
LEFT JOIN vtiger_account t5 ON t1.accountid = t5.accountid

Get all vtigerCRM Services

The following query gets vtigerCRM Service List
SELECT t1.service_no AS 'Service No'
, t1.servicename AS 'Service Name'
, t1.service_usageunit AS 'Usage Unit'
, t1.unit_price AS 'Price'
, t1.qty_per_unit AS 'No of Units'
, t1.servicecategory AS 'Category'
, t3.user_name AS 'Owner'
FROM vtiger_service t1
INNER JOIN vtiger_crmentity t2 ON t1.serviceid = t2.crmid AND t2.setype='Services'
INNER JOIN vtiger_users t3 ON t1.handler = t3.id

Backup MySQL Database

Use mysqldump to backup your MySQL db, you can find it in MySQL Server bin folder.
Syntax:
$mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]

For example, the following command backup vtigerCRM database:
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysqldump -u root -p vtigercrm510 >vtigercrm510bk.sql

Get all vtigerCRM invoices

The following query gets vtigerCRM Invoice List
SELECT t1.invoice_no AS 'Invoice No'
, t1.subject AS 'Subject'
, t3.subject AS 'Sales Order'
, t1.invoicestatus AS 'Status'
, t1.total AS 'Total'
, t4.user_name AS 'Assigned To'
FROM vtiger_invoice t1
INNER JOIN vtiger_crmentity t2 ON t1.invoiceid = t2.crmid AND t2.setype='Invoice'
LEFT JOIN vtiger_salesorder t3 ON t1.salesorderid = t3.salesorderid
INNER JOIN vtiger_users t4 ON t2.smownerid = t4.id

Get all vtigerCRM Purchase Orders

The following query gets vtigerCRM Purchase Order List

SELECT t1.purchaseorder_no
, t1.subject
, t3.vendorname
, t1.tracking_no
, t1.total
, t4.user_name
FROM vtiger_purchaseorder t1
INNER JOIN vtiger_crmentity t2 ON t1.purchaseorderid = t2.crmid AND t2.setype='PurchaseOrder'
INNER JOIN vtiger_vendor t3 ON t1.vendorid = t3.vendorid
INNER JOIN vtiger_users t4 ON t2.smownerid = t4.id

Get all vtigerCRM Sales Order

The following query gets vtigerCRM Sales Order List

SELECT t1.salesorder_no AS 'SalesOrder No'
, t1.subject AS 'Subject'
, t4.accountname AS 'Account Name'
, t1.total AS 'Total'
, t3.user_name AS 'Assigned To'
FROM vtiger_salesorder t1
INNER JOIN vtiger_crmentity t2 ON t1.salesorderid = t2.crmid AND t2.setype = 'SalesOrder'
INNER JOIN vtiger_users t3 ON t2.smownerid = t3.id
INNER JOIN vtiger_account t4 ON t1.accountid = t4.accountid

Get all vtigerCRM products

The following query gets all vtigerCRM Products

SELECT t1.product_no AS 'Product No'
, t1.productname AS 'Product Name'
, t1.productcode AS 'Part Number'
, t1.commissionrate AS 'Commission Rate'
, t1.qtyinstock AS 'Quality In Stock'
, t1.qty_per_unit AS 'Qty/Unit'
, t1.unit_price AS 'Unit Price'
FROM vtiger_products t1
INNER JOIN vtiger_crmentity t2 ON t1.productid = t2.crmid AND t2.setype = 'Products'

Get all vtigerCRM vendors

This query gets all vtigerCRM vendors

SELECT t1.vendor_no
, t1.vendorname
, t1.phone
, t1.email
, t1.category
FROM vtiger_vendor t1
INNER JOIN vtiger_crmentity t2 ON t1.vendorid = t2.crmid AND t2.setype = 'Vendors'

Get all vtigerCRM Leads

This query get all leads in vtigerCRM

SELECT t1.lead_no AS 'Lead No'
, t1.lastname AS 'Last Name'
, t1.firstname AS 'First Name'
, t1.company AS 'Company'
, t4.phone AS 'Phone'
, t5.website AS 'Website'
, t1.email AS 'Email'
, t3.user_name AS 'Assigned To'
FROM vtiger_leaddetails t1
INNER JOIN vtiger_crmentity t2 ON t1.leadid = t2.crmid AND t2.setype = 'Leads'
INNER JOIN vtiger_users t3 ON t2.smownerid = t3.id
LEFT JOIN vtiger_leadaddress t4 ON t1.leadid = t4.leadaddressid
LEFT JOIN vtiger_leadsubdetails t5 ON t1.leadid = t5.leadsubscriptionid

Get all vtigerCRM campaigns

This query gets all vtigerCRM Campaigns

SELECT t1.campaign_no AS 'Campaign No'
, t1.campaignname AS 'Campaign Name'
, t1.campaigntype AS 'Campaign Type'
, t1.campaignstatus AS 'Campaign Status'
, t1.expectedrevenue AS 'Expected Revenue'
, t1.closingdate AS 'Expected Closde Date'
, t3.user_name AS 'Assigned To'
FROM vtiger_campaign t1
INNER JOIN vtiger_crmentity t2 ON t1.campaignid = t2.crmid AND t2.setype = 'Campaigns'
INNER JOIN vtiger_users t3 ON t2.smownerid = t3.id

Get vtigerCRM Contact List

The following query gets vtigerCRM Contact List

SELECT t1.contact_no
, t1.firstname
, t1.lastname
, t1.title
, t4.accountname
, t1.email
, t1.phone
, t3.user_name
FROM vtiger_contactdetails t1
INNER JOIN vtiger_crmentity t2 ON t1.contactid = t2.crmid AND t2.setype = 'Contacts'
INNER JOIN vtiger_users t3 ON t2.smownerid = t3.id
LEFT JOIN vtiger_account t4 ON t1.accountid = t4.accountid

Get vtigerCRM Account List

The following query get vtigerCRM Account List

SELECT t1.account_no AS 'Account No'
, t1.accountname AS 'Account Name'
, t4.bill_city AS 'City'
, t1.website AS 'Website'
, t1.phone AS 'Phone'
, t3.user_name AS 'Assigned To'
FROM vtiger_account t1
INNER JOIN vtiger_crmentity t2 ON t1.accountid = t2.crmid AND t2.setype = 'Accounts'
INNER JOIN vtiger_users t3 ON t2.smownerid = t3.id
LEFT JOIN vtiger_accountbillads t4 ON t1.accountid = t4.accountaddressid

Find all accounts in vtiger_crmentity

This query is applied to vtigerCRM database structure
Table vtiger_cmentity contains information about Accounts, Contacts, Vendors, etc. and some other information for tracking.

SELECT * FROM vtiger_crmentity
WHERE setype = 'Accounts'

Find tables contain 'accountid' or 'account_id'

I'm using vtiger CRM, I used this query to find which tables contain column accountid or account_id so that I can understand the relationship among them.

SELECT * FROM information_schema.columns
WHERE column_name LIKE 'account_id'
OR column_name LIKE 'accountid'

List all tables contain 'account'

I'm using vtiger CRM, I used this query to find which tables contain account so that I can understand the relationship among them.

SELECT * FROM information_schema.tables
WHERE table_name LIKE '%account%'

Rank order by using simple query

In SQL Server 2005, we can use RANK() to display rank order in the result set.
However, assume that we don't know that, we can use the simple query as the following:
SELECT COUNT(*) AS Rank, t1.EmployeeID, t1.EmployeeName
FROM Employee t1, Employee t2
WHERE t1.EmployeeID >= t2.EmployeeID
GROUP BY t1.EmployeeID, t1.EmployeeName

PL/SQL block

DECLARE 
  Declaration statements
BEGIN
  Executable statements
EXCEPTION
  Exception-handling statements
END;

Dynamic query in MySQL

This script gets all users from localhost in mysql.user table by using dynamic query.

SET @table = 'mysql.user';
SET @host = '''%localhost%''';
SET @s = CONCAT('SELECT * FROM ', @table, ' WHERE host LIKE ', @host);
PREPARE stmt3 FROM @s;
EXECUTE stmt3;
DEALLOCATE PREPARE stmt3;