Details for Chinmaykumar

Query_Sol. for MySQL users

SELECT EventName,
CONCAT(DAYNAME(EventDate),' ',DAY(EventDate),
CASE    WHEN DAY(EventDate) IN (1,21,31) THEN 'st'
        WHEN DAY(EventDate) IN (2,22) THEN 'nd'
        WHEN DAY(EventDate) IN (3,23) THEN 'rd'
        ELSE 'th'
END,' ',MONTHNAME(EventDate),' ',YEAR(EventDate)) AS 'FULL date' 
FROM tblevent
ORDER BY EventDate ASC;

Query_Sol. for MySQL buddies

-- Creating temp. table for multiple output from same table

-- In MySQL, 'DATENAME' substitute is DAYNAME() function; also check DAY(),DAYOFMONTH(),DAYOFYEAR()

CREATE TEMPORARY TABLE TEMP_13thFriday(EventName varchar(8000),EventDate date,Day_of_week varchar(100),Day_number int);
-- now time to insert data into our temp table
INSERT INTO TEMP_13thFriday
(SELECT EventName, EventDate,
DAYNAME(EventDate) Day_of_week, DAY(EventDate) Day_number
FROM tblevent);
-- 1) there weren't any events on Friday the 13th
SELECT * FROM TEMP_13thFriday
WHERE Day_of_week = 'Friday' AND Day_number = 13;
-- 2) there was one event on Thursday the 12th
SELECT * FROM TEMP_13thFriday
WHERE Day_of_week = 'Thursday' AND Day_number = 13-1;
-- 3) there were two events on Saturday the 14th
SELECT * FROM TEMP_13thFriday
WHERE Day_of_week = 'Saturday' AND Day_number = 13+1;

Query_Sol. for MySQL users

-- number of offset days since your birthday

-- In date format, "%b" is used to show "Abbreviated month name (Jan to Dec)"

SELECT EventName, DATE_FORMAT(EventDate,'%d %b %Y') Event_Date,
DATEDIFF(EventDate,'1964-03-04') 'Days offset'
FROM tblevent
ORDER BY DATEDIFF(EventDate,'1964-03-04') DESC;

-- this exercise is to see what was happening in the world around the time when you were born

SELECT EventName, DATE_FORMAT(EventDate,'%d %b %Y') Event_Date,
DATEDIFF('1964-03-04', EventDate) 'Days offset',
ABS(DATEDIFF('1964-03-04', EventDate)) 'Days difference'
FROM tblevent
ORDER BY ABS(DATEDIFF('1964-03-04', EventDate)) ASC;

Query_Sol. for MySQL users

SELECT EventName, EventDate AS NotFormatted,
DATE_FORMAT(EventDate,"%d/%m/%Y") AS UsingFormat,
CONVERT(DATE_FORMAT(EventDate,"%d/%m/%Y"),CHAR) AS UsingConvert
FROM tblevent
WHERE YEAR(EventDate) = 1996;

Query_Sol. for MySQL users

-- In MySQL, the equivalent function to the CHARINDEX is LOCATE

SELECT EventName, EventDate,
    LOCATE('this', EventDetails) AS thisPosition,
    LOCATE('that', EventDetails) AS thatPosition,
    (LOCATE('that', EventDetails) - LOCATE('this', EventDetails)) AS 'Offset'
FROM tblevent
WHERE
    LOCATE('this', EventDetails) > 0
    AND LOCATE('that', EventDetails) > 0
    AND LOCATE('that', EventDetails) > LOCATE('this', EventDetails);

Query_Sol. for MySQL users

SELECT 
    Country,
    KmSquared,

/* In MySQL, you can use the 'DIV' to perform integer division in a SELECT statement */
    (KmSquared DIV 20761) AS WalesUnits,

/* To find the remainder of division use '%' (modulo) operator. Ex: 10 % 3 is 1 */
    (KmSquared % 20761) AS AreaLeftOver,
    
    CASE
        WHEN
            (KmSquared DIV 20761) > 0
        THEN
            CONCAT((KmSquared DIV 20761),' x Wales plus ',(KmSquared % 20761),' sq. km.')
        ELSE 'Smaller than Wales'
    END AS WalesComparison
FROM
    countriesbyarea

/* Important: To sort column by the specific nearest values to the specific value 
use 'ABS(column_name - target_value)' */
ORDER BY ABS(KmSquared - 20761);

Query_Sol. for MySQL users

-- Important: in MySQL, '[]' wildcard do not work with LIKE function
-- used left(), right() function to extracts a number of characters from a string

SELECT EventName, 
CASE 
    WHEN LEFT(EventName, 1) IN('A','E','I','O','U') AND RIGHT(EventName, 1) IN('a','e','i','o','u') THEN 'Begins and ends with vowels'
    WHEN LEFT(EventName, 1) = RIGHT(EventName, 1) THEN 'Same letter'
END AS Verdict
FROM tblevent
HAVING Verdict IS NOT NULL
ORDER BY EventDate;

Query_Sol. for MySQL users

-- In MySQL, ISNULL() just returns null values with 0,1
-- To replace values of null I used IFNULL(expression, alt_value)

SELECT ContinentName, Summary, 
IFNULL(Summary,'No summary') AS 'Using ISNULL',
COALESCE(Summary,'No summary') AS 'Using COALESCE',
CASE 
    WHEN Summary IS NULL THEN 'No summary'
    ELSE Summary
END AS 'Using CASE'
FROM tblcontinent;
 

Query_Sol. for MySQL users

-- in MySQL, LEN() function is different as LENGTH() 
-- used 'char_length' to avoid pound symbol bytes count

SELECT EventName, CHAR_LENGTH(EventName) AS 'Length of name'
FROM tblevent
ORDER BY CHAR_LENGTH(EventName);

Query for MySQL users

-- For MySQL, '+' is only addition, not concatenation

SELECT CONCAT(EventName,' (Category ',CAST(CategoryID AS nchar(10)), ')') AS 'Event (category)', EventDate 
FROM tblevent
WHERE CountryID = 1;