Database Administrator

Different types of Joins are :

INNER JOIN : The INNER JOIN keyword selects all rows from both the tables as long as the condition satisfies. This keyword will create the result-set by combining all rows from both the tables where the condition satisfies i.e value of the common field will be same.

Syntax :
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
INNER JOIN table2
ON table1.matching_column = table2.matching_column;
 

LEFT JOIN : This join returns all the rows of the table on the left side of the join and matching rows for the table on the right side of join. The rows for which there is no matching row on right side, the result-set will contain null. LEFT JOIN is also known as LEFT OUTER JOIN.

Syntax :
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;
 

RIGHT JOIN : RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join. The rows for which there is no matching row on left side, the result-set will contain null. RIGHT JOIN is also known as RIGHT OUTER JOIN.

Syntax :
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;
 

FULL JOIN : FULL JOIN creates the result-set by combining result of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both the tables. The rows for which there is no matching, the result-set will contain NULL values.

Syntax :
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
FULL JOIN table2
ON table1.matching_column = table2.matching_column;

An aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning.

Various Aggregate Functions

Count() :
Count(*): Returns total number of records 

Sum() :
sum(x) : Sum all Non Null values of Column x

Avg() :
Avg(x) = Sum(x) / count(x)

Min() :
Min(x): Minimum value in column x
Max(x): Maximum value in column x

The SQL WHERE clause is used to specify a condition while fetching the data from a single table or by joining with multiple tables. If the given condition is satisfied, then only it returns a specific value from the table. You should use the WHERE clause to filter the records and fetching only the necessary records.

1. Classification is all about predicting the output when input data is given. Clustering is all about grouping data points together based on similarities among them and difference from others.

2. Labeled data is provided in classification. In Clustering, Unlabeled data provided.
 

3. Classification model function classifies the data into one of defined definite classes. Clustering function maps the data into one of the multiple clusters where the arrangement of data items is relies on the similarities between them.
 

4. In classification data are grouped by analyzing data objects whose class label is known. Clustering analyzes data objects without knowing class label.
 

5. There is some prior knowledge of attributes of each classification. There is no prior knowledge of attributes of data to form clusters.
 

6. Classification is done by classifying output based on the value of input data. Clustering is done by grouping only the input data because output is not predefined.
 

7. The number of class are known before classification as there is predefined output based input data. The number of clusters are not known before clustering. These are identified after completion of clustering.

Tempdb is the name of a system database in Microsoft SQL Server. Database developers and the database engine use tempdb as a store for transient data. Tempdb stores data used in various active processing routines. The temporary data does not need persistence, and tempdb provides a functional "scratchpad" for the entire SQL Server instance.

SQL Server Integration Service (SSIS) is a component of the Microsoft SQL Server database software that can be used to execute a wide range of data migration tasks. SSIS is a fast & flexible data warehousing tool used for data extraction, loading and transformation like cleaning, aggregating, merging data, etc. It makes it easy to move data from one database to another database. SSIS can extract data from a wide variety of sources like SQL Server databases, Excel files, Oracle and DB2 databases, etc.

Tip 1 : The cross questioning can go intense some time, think before you speak.

Tip 2 : Be open minded and answer whatever you are thinking, in these rounds I feel it is important to have opinion.

Tip 3 : Context of questions can be switched, pay attention to the details. It is okay to ask questions in these round, like what are the projects currently the company is investing, which team you are mentoring. How all is the work environment etc.