Showing posts with label Complicated Queries in MySQL. Show all posts
Showing posts with label Complicated Queries in MySQL. Show all posts

Thursday, April 30, 2009

Complicated Queries with examples

1. To show the firstname, middlename, lastname as a fullname

SELECT CONCAT (`first_name`, ' ', `middle_name`, ' ', `last_name`) AS fullname FROM `student_users` ORDER BY fullname ;

2. Add 31 days to the date

SELECT ADDDATE( '1998-02-28', INTERVAL 31 DAY ) OR
SELECT ADDDATE('1998-01-02', 31);

3. Add Time
SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');

4. Differentiate two dates
SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');

5. Extract the year from date
SELECT EXTRACT(YEAR FROM '1999-07-02');

6. Return as time for the given second
SELECT SEC_TO_TIME(2378);


7. Find the age
SELECT name, birth, CURDATE(), (YEAR(CURDATE())-YEAR(birth)) - (RIGHT(CURDATE(),5) < RIGHT(birth,5)) AS age FROM pet ORDER BY name;

8. Find name starts with either of the following words ex: b or s or p
Now we want to select the persons with a last name that starts with "b" or "s" or "p" from the "Persons" table.

SELECT * FROM Persons WHERE LastName LIKE '[bsp]%'
more info.. http://evergreenphp.blogspot.com

Complicated Queries in LIKE

SELECT *
FROM `zen_tax_rates`
ORDER BY tax_rates_id = (
SELECT tax_rates_id
FROM zen_tax_rates
WHERE tax_description
LIKE '%sample%'
LIMIT 1 ) ASC

by this query the selection field value in the ORDER clause is chosen by dynamic using the WHERE clause value and ASC is used here because the selection value is need to be displayed as last record in the fetch. If we want to display the selection record need to be displayed a first record in the fetch we need simply to put DESC in the ORDER BY in this query.

more info.. http://evergreenphp.blogspot.com

Complicated Queries in MySQL

SELECT match_id AS m1
FROM `match_info`
ORDER BY match_id =18 DESC

in this query "match_id=18" is given in the ORDER BY because when we need to fetch the "match_id=18" as the first record, then we can write the query like this and rest of the records in the table "match_info" are fetched as 2nd , 3rd and etc records ..

more info.. http://evergreenphp.blogspot.com