ALMT Legal Interview Questions and Answers

ROW_NUMBER() and PARTITION BY are used in SQL to assign a unique row number to each row within a partition.

• Use ROW_NUMBER() function along with PARTITION BY clause to assign a unique row number to each row within a partition.

• To find duplicates in a table, you can use a combination of ROW_NUMBER() and PARTITION BY to identify rows with row numbers greater than 1.

• Example: SELECT * FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY column_name ORDER BY column_name) AS row_num FROM t...read more

Joins in SQL are used to combine rows from two or more tables based on a related column between them. A self join is when a table is joined with itself.

• Joins in SQL are used to retrieve data from multiple tables based on a related column.

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

• A self join is when a table is joined with itself to combine rows based on a related column within the same table.

Optimising SQL queries involves using indexes, avoiding unnecessary joins, and limiting the amount of data retrieved.

• Use indexes on columns frequently used in WHERE clauses

• Avoid using SELECT * and only retrieve necessary columns

• Limit the use of DISTINCT and ORDER BY clauses

• Avoid unnecessary joins and use INNER JOIN instead of OUTER JOIN when possible

• Use WHERE clause to filter data before joining tables

Char is fixed length data type while varchar is variable length data type in SQL.

• Char stores fixed length strings, while varchar stores variable length strings.

• Char is padded with spaces to reach fixed length, while varchar does not pad with spaces.

• Char is faster for fixed length data, while varchar is more flexible for variable length data.

Group by is a clause in SQL used to arrange identical data into groups based on one or more columns.

• Group by is used with aggregate functions like COUNT, SUM, AVG, etc.

• It is used to summarize data and perform calculations on grouped data.

• For example, SELECT department, COUNT(*) FROM employees GROUP BY department;