SQL Basics

Switch Case:
Following is the syntax of switch case in MySql.

Example:
Employee {
       id int,
       name string,
}

• Employee table has 2 columns called as id and name. 
• For ex, if you want string 'First' to be displayed for employee with id = 1 & 'Second' to be displayed for employee id = 2 then the query can be written as follows:

SELECT string,
CASE WHEN id = 1 THEN 'First' WHEN id = 2 THEN 'Second' END
From Employee

So every time id=1 is a part of result, it will display 'First'.

Indexing
Indexing could result in increasing the performance of the database. If indexes are not set then it impacts the performance. Here are some best practices of selecting the indexes:

1. Pick a good clustering key: Not varchar but an int. 
2. Int is narrow, stable, ever-increasing, and unique.
3. An index can be added to any column that is being used as foreign key on other table.
4. Avoid overlapping indexes. 
5. Avoid indexing on too many columns. 
6. Have a baseline number for testing. Before adding any columns your queries are taking 10 seconds to provide a response. Now after adding the indexes find out the time it takes to run the same queries. If its less then 10 seconds then you have added indexes on right columns. So observe, measure, and compare the baseline.

How to convert a string to a date: 
Its very common in business world to convert a string to a date for reporting purposes. To achieve those results here is an operation:
STR_TO_DATE('2003/07/09', 'yyyy/mm/dd')
Result: date value of July 9, 2003

Popular Sql Operations:

1. COALESCE: Returns first non-null value from a list of strings. 
1. Example 1: Select COALESCE(NULL, 'Interview questions', NULL, 'Product interview questions'); 
1. Result: Interview questions
2. Example 2: Select COALESCE(NULL, NULL, NULL, NULL, 'Interview', NULL, 'Product interview questions'); 
1. Result: Interview
2. CURRENT_TIMESTAMP: Returns current date and timestamp. "YYYY-MM-DD HH-MM-SS" format if used in a string context
1. Example: Select CURRENT_TIMESTAMP
1. Result: 2018-08-13 01:09:13

You might also like
Estimation Questions for Technical Product Manager
Behavioral Questions and answers