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