# 50 SQL Interview Questions

## Q1. What is the difference between DELETE and TRUNCATE statements?

`Delete: 	1. Delete command is used to delete a row in a table 	2. You can rollback data after using delete statement 		i. Need to begin a transaction in order to rollback 	3. It is a DML command 	4. It is slower than truncate statement Truncate:	1. Truncate is used to delete all the rows from a table 	2. You cannot rollback data 	3. It is a DDL command	4. It is fasterBegin {transaction} Delete {statement}Rollback {transaction}`
• DML is Data Manipulation Language: used to manipulate data itself
• DDL is Data Definition Language: used to define data structures
`1…`

# Time Series Learning Notes

This is a notebook learning Time Series on Coursera course Sequences, Time Series, and Prediction hold by Laurence Moroney. The Couse link is https://www.coursera.org/learn/tensorflow-sequences-time-series-and-prediction/home/welcome

Basic sequence and prediction

The time series problem consists of three dimensions: trends, seasonalities, and noise. We are trying to predict the future value based on the trends and seasonalities. The noise is unpredictable. There are some basic methods that are straight forward, without using machine learning techniques.

The practice below is using univariate synthetic data. Univariate data can be found in reality as predicting the temperature/customer growth/price etc. …

# Some useful tips in Python

1. Extract numbers from a string

Some data comes in a string format with some critical numbers we need to calculate and modify. Below is how we can extract the number from the string no matter if the number is an integer or decimal number. Most of the time, the number will separate with the words with space, but sometimes they are adjacent.

`# first step is to insert a space between number and the words. Using regular expression to do so. import reb = (re.sub(r"([0-9]+(\.[0-9]+)?)",r" \1 ", a).strip())# second step is to creat a list for all the…`

# Using Python as SQL with Pandas DataFrame

SQL is very easy to use, but what if you need to achieve the same operations in Python? Here are the corresponding operations in Python using Pandas DataFrame(DF). All examples are written as in SQL Server.

Below including DISTINCT, WHERE, LIKE, IN/NOT IN, ORDER BY, GROUP BY, CASE WHEN, ROW_NUMBER(), JOIN, UNION ALL/UNION, COUNT, DROP COLUMN, and RENAME COLUMN.

• Distinct. This operation drops the duplicates in the specified columns in the DF.
`SQL:    select distinct col1, col2 from dfPython: df_dedup = df.drop_duplicates(subset= [‘col1’, ‘col2’])`
• Where
`SQL:    select * from df where col1=value1 and col2=value2Python: df[ (df[‘col1’]==’value1’) & (df[‘col2’]=='value2’)]` 