23 Advanced SQL Interview Questions (SOLVED) Data Scientists Must Know

Normalization is basically to design a database schema such that duplicate and redundant data is avoided. If the same information is repeated in multiple places in the database, there is the risk that it is updated in one place but not the other, leading to data corruption.

There is a number of normalization levels from 1. normal form through 5. normal form. Each normal form describes how to get rid of some specific problem.

By having a database with normalization errors, you open the risk of getting invalid or corrupt data into the database. Since data "lives forever" it is very hard to get rid of corrupt data when first it has entered the database.

• Data definition language (DDL) commands are the commands which are used to define the database. CREATE, ALTER, DROP and TRUNCATE are some common DDL commands.

• Data manipulation language (DML) commands are commands which are used for manipulation or modification of data. INSERT, UPDATE and DELETE are some common DML commands.

• Atomicity: It ensures all-or-none rule for database modifications.
• Consistency: Data values are consistent across the database.
• Isolation: Two transactions are said to be independent of one another.
• Durability: Data is not lost even at the time of server failure.

Since all emails are in lowercase we can simply group by email and print those that have a count > 1.

SELECT EMAIL
FROM PERSON
GROUP BY EMAIL
HAVING COUNT(*)>1

Simply group on both of the columns:

SELECT
    name, email, COUNT(*) as CountOf
FROM
    users
GROUP BY
    name, email
HAVING 
    COUNT(*) > 1

• With a Clustered index the rows are stored physically on the disk in the same order as the index. Therefore, there can be only one clustered index. A clustered index means you are telling the database to store close values actually close to one another on the disk.
• With a Non Clustered index there is a second list that has pointers to the physical rows. You can have many non clustered indices, although each new index will increase the time it takes to write new records.
• It is generally faster to read from a clustered index if you want to get back all the columns. You do not have to go first to the index and then to the table.
• Writing to a table with a clustered index can be slower, if there is a need to rearrange the data.

It is the process of improving the performance of the database by adding redundant data.

In database systems, Collation specifies how data is sorted and compared in a database. Collation provides the sorting rules, case, and accent sensitivity properties for the data in the database.

For example, when you run a query using the ORDER BY clause, collation determines whether or not uppercase letters and lowercase letters are treated the same.

An inner join retrieve the matched rows only.

Whereas an outer join retrieve the matched rows from one table and all rows in other table ....the result depends on which one you are using:

• Left: Matched rows in the right table and all rows in the left table
• Right: Matched rows in the left table and all rows in the right table or
• Full: All rows in all tables. It doesn't matter if there is a match or not

Inner Join

Retrieve the matched rows only, that is, A intersect B.

SELECT *
FROM dbo.Students S
INNER JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

Left Outer Join

Select all records from the first table, and any records in the second table that match the joined keys.

SELECT *
FROM dbo.Students S
LEFT JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

Full Outer Join

Select all records from the second table, and any records in the first table that match the joined keys.

SELECT *
FROM dbo.Students S
FULL JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

UNION puts lines from queries after each other, while JOIN makes a cartesian product and subsets it -- completely different operations. Trivial example of UNION:

mysql> SELECT 23 AS bah
    -> UNION
    -> SELECT 45 AS bah;
+-----+
| bah |
+-----+
|  23 |
|  45 |
+-----+
2 rows in set (0.00 sec)

similary trivial example of JOIN:

mysql> SELECT * FROM
    -> (SELECT 23 AS bah) AS foo
    -> JOIN
    -> (SELECT 45 AS bah) AS bar
    -> ON (33=33);
+-----+-----+
| foo | bar |
+-----+-----+
|  23 |  45 |
+-----+-----+
1 row in set (0.01 sec)

UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.

There is a performance hit when using UNION instead of UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).

UNION Example:

SELECT 'foo' AS bar UNION SELECT 'foo' AS bar

Result:

+-----+
| bar |
+-----+
| foo |
+-----+
1 row in set (0.00 sec)

UNION ALL example:

SELECT 'foo' AS bar UNION ALL SELECT 'foo' AS bar

Result:

+-----+
| bar |
+-----+
| foo |
| foo |
+-----+
2 rows in set (0.00 sec)