Trainee Scientist Interview Questions and Answers

Understanding SQL's GROUP BY, HAVING, and ORDER BY clauses for data aggregation and sorting.

• GROUP BY is used to aggregate data based on one or more columns.

• HAVING filters the results of a GROUP BY based on a condition.

• ORDER BY sorts the final result set based on specified columns.

• Example: SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 10 ORDER BY COUNT(*) DESC;

Joins in SQL are used to combine rows from two or more tables based on a related column between them.

• Joins are executed after the FROM clause and before the WHERE clause in SQL queries.

• Common types of joins include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

• Example: SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;

SQL order of execution determines the sequence in which different clauses are processed in a query.

• SQL order of execution: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY.

• Joins are processed before WHERE clause.

• Aggregate functions are processed after WHERE clause but before SELECT clause.

• Subqueries are processed from innermost to outermost.

Python code for data frames in pandas library

• Import pandas library

• Create a data frame using pd.DataFrame()

• Access and manipulate data using various methods like loc, iloc, and groupby