Top 10 SQL Interview Questions for Data Scientists

Ace your data scientist interview with these top ten SQL interview questions and answers-

1. How do clustered and non-clustered indexes differ from each other in SQL?

In SQL Server, the primary key constraint instantly generates a clustered index on a given column. According to the protocol, each database can only contain one clustered index. To determine the order, sort the table, or arrange the data in alphabetical order, one uses a clustered index, which functions similarly to a dictionary.

A non-clustered index stores data elsewhere than where it was originally gathered. The index includes references to the location of that data. A dataset may contain as many non-clustered indexes as the protocol allows. It has no impact on the sequence in which the data are stored in the table.

2. How is the DISTINCT clause implemented in MySQL? How is DISTINCT optimised by MySQL?

Typically, duplicate records are removed from tables using MySQL's DISTINCT clause so that only distinct records are returned. In MySQL, it is only applied to the SELECT query. A DISTINCT clause is typically regarded as a special case of GROUP BY. Due to this similarity, queries with a DISTINCT clause can benefit from the optimizations that apply to GROUP BY queries.

3. What distinguishes COUNT and COUNT DISTINCT functions?

The number of rows specified by the query, or those rows that are specified by a WHERE condition, is returned using the COUNT function. Prior to applying the count, COUNT DISTINCT removes duplicate values, or it gives the total number of distinct rows in a table.

4. How do shared and inclusive locks differ from one another?

• While the lock state is only in read operation in a shared lock, it is in both read and write operation in an inclusive lock.
• While an inclusive lock restricts anyone from reading or updating the data, a shared lock prevents anyone from updating the data.
• For a given object, any number of transactions may hold a shared lock, but only one transaction may hold an inclusive lock.

5. How do scalar functions work?

The term "scalar function" refers to a class of built-in SQL functions that always yield a single value as their output regardless of the input they are given. The scalar functions in SQL manage each record separately. The following scalar functions are often used: MID(), LENGTH(), ROUND(), NOW(), FORMAT, UCASE, LCASE ().

6. What do you understand by triggers in SQL?

A trigger is a specific type of stored procedure that starts running whenever a database server event occurs. DML triggers are triggered when an individual tries to modify data using a data manipulation language (DML) event. DML operations are statements that INSERT, UPDATE, or DELETE data from a database or view. These triggers are activated whenever an authorized event takes place, regardless of whether table rows are altered.

7. What does the COALESCE function do?

The first value from a sequence that is NOT NULL is returned using the COALESCE function. It evaluates the expressions in the correct sequence and returns the first non-null value.

8. How can timestamps be converted to date time in MySQL?

The FROM UNIXTIME() method can be used to convert a timestamp to a date time.

9. Write the query to create a new table that shares the same layout as another table.

CREATE TABLE new_table LIKE old_table;

10. Is a NULL value the same as a 0 or a blank space?

No, a NULL value is not the same as a 0 or a black space. A NULL value is defined as any value that is "unavailable, unassigned, unknown, or not relevant". Zero is an integer, while a blank space is a character.

Liked this article? Join our WhatsApp community for resources & career advice: https://jovian.com/whatsapp