Photo by Scott Graham on Unsplash
Hi All!! Hope you all had a great weekend!! Previously, I started the interview topics series for SQL and In Part 1 we saw topics related CASE WHEN, Self Joins, Distinct vs. Group By, Left join vs. Outer Join, and Union. For Part 2, we will look into other most commonly asked sql topics for data analyst/data science interview. Please subscribe to keep updated. Hope you like the content!
Most Commonly Asked SQL Interview Topics:
1. Similarity and Difference between SUM and COUNT:
SUM and COUNT are both an aggregation function. We can make use of calculation of sum and count of the data based on the analysis.
SUM: sum of the column which needs to be calculated
Example:
SELECT SUM(column_name)
FROM table_name
WHERE condition; COUNT: calculate count of the non-empty valuesbased on the select query.
Example:
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
Here you can also make use of case when Use Cases:
Imagine the table has 5 records entries (non empty):
Select count(*) from tab1; will give 5, whereas
Select sum(sales) from tab1;will give total sum of sales.
Using CASE WHEN statements:
Similarity:
The below code snippet returns the same output i.e. total male count.
Select count(case when gender = ‘M’ then 1 else 0) end as male_count from tab1;
Select sum(case when gender = ‘M’ then 1 else 0) end as total_males from tab1; Difference:
The below code snippet returns different output i.e. the 1st query shows male count whereas 2nd query shows sales from males.
Select count(case when gender = ‘M’ then 1 else 0) end as male_count from tab1;
Select sum(case when gender = ‘M’ then sales else 0) end as male_sales from tab1; 2. Date Manipulation (including Teradata):
Using SysDate:
SELECT SYSDATETIME() AS 'DateAndTime'; -- return datetime2(7)
SELECT SYSDATETIMEOFFSET() AS 'DateAndTime+Offset'; -- datetimeoffset(7)
SELECT SYSUTCDATETIME() AS 'DateAndTimeInUtc'; -- returns datetime2(7) Using Current Date and Timestamp:
SELECT CURRENT_TIMESTAMP AS 'DateAndTime'; -- note: no parentheses
SELECT GETDATE() AS 'DateAndTime';
SELECT GETUTCDATE() AS 'DateAndTimeUtc'; Using DATENAME to return parts of Date:
SELECT DATENAME(YEAR, GETDATE()) AS 'Year';
SELECT DATENAME(QUARTER, GETDATE()) AS 'Quarter';
SELECT DATENAME(MONTH, GETDATE()) AS 'Month Name';
SELECT DATENAME(DAYOFYEAR, GETDATE()) AS 'DayOfYear';
SELECT DATENAME(DAY, GETDATE()) AS 'Day';
SELECT DATENAME(WEEK, GETDATE()) AS 'Week';
SELECT DATENAME(WEEKDAY, GETDATE()) AS 'Day of the Week';
SELECT DATENAME(HOUR, GETDATE()) AS 'Hour';
SELECT DATENAME(MINUTE, GETDATE()) AS 'Minute';
SELECT DATENAME(SECOND, GETDATE()) AS 'Second';
SELECT DATENAME(MILLISECOND, GETDATE()) AS 'MilliSecond';
SELECT DATENAME(MICROSECOND, GETDATE()) AS 'MicroSecond';
SELECT DATENAME(NANOSECOND, GETDATE()) AS 'NanoSecond';
SELECT DATENAME(ISO_WEEK, GETDATE()) AS 'Week'; Using DATEPART:
SELECT DATEPART(YEAR, GETDATE()) AS 'Year';
SELECT DATEPART(QUARTER, GETDATE()) AS 'Quarter';
SELECT DATEPART(MONTH, GETDATE()) AS 'Month';
SELECT DATEPART(DAYOFYEAR, GETDATE()) AS 'DayOfYear';
SELECT DATEPART(DAY, GETDATE()) AS 'Day';
SELECT DATEPART(WEEK, GETDATE()) AS 'Week';
SELECT DATEPART(WEEKDAY, GETDATE()) AS 'WeekDay';
SELECT DATEPART(HOUR, GETDATE()) AS 'Hour';
SELECT DATEPART(MINUTE, GETDATE()) AS 'Minute';
SELECT DATEPART(SECOND, GETDATE()) AS 'Second';
SELECT DATEPART(MILLISECOND, GETDATE()) AS 'MilliSecond';
SELECT DATEPART(MICROSECOND, GETDATE()) AS 'MicroSecond';
SELECT DATEPART(NANOSECOND, GETDATE()) AS 'NanoSecond';
SELECT DATEPART(ISO_WEEK, GETDATE()) AS 'Week';
SELECT DAY(GETDATE()) AS 'Day';
SELECT MONTH(GETDATE()) AS 'Month';
SELECT YEAR(GETDATE()) AS 'Year'; Using DATEFROMPARTS:
SELECT DATEFROMPARTS(2019,1,1) AS 'Date'; -- returns date
SELECT DATETIME2FROMPARTS(2019,1,1,6,0,0,0,1) AS 'DateTime2'; -- returns datetime2
SELECT DATETIMEFROMPARTS(2019,1,1,6,0,0,0) AS 'DateTime'; -- returns datetime
SELECT DATETIMEOFFSETFROMPARTS(2019,1,1,6,0,0,0,0,0,0) AS 'Offset'; -- returns datetimeoffset
SELECT SMALLDATETIMEFROMPARTS(2019,1,1,6,0) AS 'SmallDateTime'; -- returns smalldatetime
SELECT TIMEFROMPARTS(6,0,0,0,0) AS 'Time'; -- returns time Using DATEDIFF:
SELECT DATEDIFF(DAY, 2019-31-01, 2019-01-01) AS 'DateDif' -- returns int
SELECT DATEDIFF_BIG(DAY, 2019-31-01, 2019-01-01) AS 'DateDifBig' -- returns bigint Modify the Date:
SELECT DATEADD(DAY,1,GETDATE()) AS 'DatePlus1'; -- returns data type of the date argument
SELECT EOMONTH(GETDATE(),1) AS 'LastDayOfNextMonth'; -- returns start_date argument or date
SELECT SWITCHOFFSET(GETDATE(), -6) AS 'NowMinus6'; -- returns datetimeoffset
SELECT TODATETIMEOFFSET(GETDATE(), -2) AS 'Offset'; -- returns datetimeoffset Validate Date:
SELECT ISDATE(GETDATE()) AS 'IsDate';
SELECT ISDATE(NULL) AS 'IsDate'; The above date manipulations are relatively used in SQL Server however, the syntax differ with respect to the type of database software used. In my experience, I have extensively used Teradata and below are the date manipulations often used in Teradata:
Examples:
Add one month to current date in Teradata:
select ADD_MONTHS(current_date,1);
ADD_MONTHS(Date, 1)
2017-11-25
select current_date + INTERVAL '1' MONTH;
(Date+ 1)
2017-11-25 Add one hour to current timestamp in Teradata:
select current_timestamp + INTERVAL '1' hour;
(Current TimeStamp(6)+ 1)
2017-10-25 13:28:20.75 Subtract one month from current date in Teradata:
select ADD_MONTHS(current_date,-1);
ADD_MONTHS(Date, -1)
2017-09-25
select current_date - INTERVAL '1' MONTH;
(Date- 1)
2017-09-25 Extract day, month, year from current date in Teradata
select EXTRACT(DAY FROM current_date);
EXTRACT(DAY FROM Date)
25
select EXTRACT(MONTH FROM current_date);
EXTRACT(MONTH FROM Date)
10
select EXTRACT(YEAR FROM current_date);
EXTRACT(YEAR FROM Date)
2017
Get months between two date values in Teradata:
SELECT MONTHS_BETWEEN(DATE'2017-03-01', DATE'2017-01-01');
MONTHS_BETWEEN(2017-03-01,2017-01-01)
2 Rround up date to first day of the week in Teradata:
SELECT TRUNC(CAST('2017/01/05' AS DATE), 'D');
TRUNC('2017/01/05','D')
2017-01-01 Resources For SQL Practice:
Part 3 will be out next week. If you like this content, please like, share and subscribe!!
Part 1 of the SQL series
Comentarios