The art of query building is the art of using Structured Query Language to formulate correct, efficient database questions and commands. In SELECT queries, you can use JOIN, WHERE and HAVING clauses to scope the result to specific rows and columns, GROUP BY to combine result rows into analytic summaries, and UNION to combine the results of multiple queries. INSERT, DELETE and UPDATE commands may reference JOINs. INSERT … SELECT inserts a query result into another table. DELETEs and UPDATEs may be scoped by WHERE clauses.
1. Age in years
You have a birth date and need to calculate how old is the guy. Assume the @dateofbirth is this date:
SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(now()) - TO_DAYS(@dateofbirth)), '%Y') + 0;
2. Difference between two dates
Find the difference between two datetime values in seconds, minutes, hours or days. If dt1 and dt2 are datetime values of the form ‘yyyy-mm-dd hh:mm:ss’, the number of seconds between dt1 and dt2 is
UNIX_TIMESTAMP( dt2 ) - UNIX_TIMESTAMP( dt1 )
3. Display column values which occur N times
SELECT id
FROM tbl
GROUP BY id
HAVING COUNT(*) = N;
4. Count business days between two dates
The simplest support for counting business days between any two dates is a calendar table with columns d date and holiday bool populated for all days in all possibly relevant years. Then the following query gives the inclusive number of business days between dates Start and Stop:
SELECT COUNT(*)
FROM calendar
WHERE d BETWEEN Start AND Stop
AND DAYOFWEEK(d) NOT IN(1,7)
AND holiday=0;
5. Find primary key of the table
SELECT k.column_name
FROM information_schema.table_constraints t
JOIN information_schema.key_column_usage k
USING (constraint_name,table_schema,table_name)
WHERE t.constraint_type='PRIMARY KEY'
AND t.table_schema='db'
AND t.table_name='tbl'
Read more: Codeforest
1. Age in years
You have a birth date and need to calculate how old is the guy. Assume the @dateofbirth is this date:
SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(now()) - TO_DAYS(@dateofbirth)), '%Y') + 0;
2. Difference between two dates
Find the difference between two datetime values in seconds, minutes, hours or days. If dt1 and dt2 are datetime values of the form ‘yyyy-mm-dd hh:mm:ss’, the number of seconds between dt1 and dt2 is
UNIX_TIMESTAMP( dt2 ) - UNIX_TIMESTAMP( dt1 )
3. Display column values which occur N times
SELECT id
FROM tbl
GROUP BY id
HAVING COUNT(*) = N;
4. Count business days between two dates
The simplest support for counting business days between any two dates is a calendar table with columns d date and holiday bool populated for all days in all possibly relevant years. Then the following query gives the inclusive number of business days between dates Start and Stop:
SELECT COUNT(*)
FROM calendar
WHERE d BETWEEN Start AND Stop
AND DAYOFWEEK(d) NOT IN(1,7)
AND holiday=0;
5. Find primary key of the table
SELECT k.column_name
FROM information_schema.table_constraints t
JOIN information_schema.key_column_usage k
USING (constraint_name,table_schema,table_name)
WHERE t.constraint_type='PRIMARY KEY'
AND t.table_schema='db'
AND t.table_name='tbl'
Read more: Codeforest