Top SQL Interviews Questions & Answers

                                         Top SQL Interviews Questions & Answers

1. What are the different types of SQL joins and when would you use each one?

There are four types of SQL joins:

INNER JOIN: Returns only the rows that have matching values in both tables being joined.

LEFT JOIN: Returns all the rows from the left table and matching rows from the right table, and null values for non-matching rows in the right table.

RIGHT JOIN: Returns all the rows from the right table and matching rows from the left table, and null values for non-matching rows in the left table.

FULL OUTER JOIN: Returns all the rows from both tables, with null values for non-matching rows on either side.

You would use INNER JOIN when you only want to retrieve the rows that have matching values in both tables, LEFT JOIN when you want to retrieve all the rows from the left table and matching rows from the right table, RIGHT JOIN when you want to retrieve all the rows from the right table and matching rows from the left table, and FULL OUTER JOIN when you want to retrieve all the rows from both tables, with null values for non-matching rows on either side.

2. Explain the concept of normalization in database design and why it's important.

Normalization is the process of organizing a database to reduce redundancy and dependency. It involves breaking down a large table into smaller, more manageable tables and establishing relationships between them. Normalization helps to eliminate data inconsistencies and makes the database more efficient and easier to maintain.

3. What is a primary key and why is it used in a database?

A primary key is a unique identifier for each record in a table. It is used to enforce data integrity and ensure that each record can be uniquely identified. Primary keys are also used to establish relationships between tables, as foreign keys in other tables reference the primary key in the main table.

4. How do you create a new table in SQL? Provide an example.

To create a new table in SQL, you would use the CREATE TABLE statement, like this:

CREATE TABLE customers (

id INT PRIMARY KEY,

name VARCHAR(50) NOT NULL,

email VARCHAR(50) UNIQUE,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

This creates a table called "customers" with four columns: "id", "name", "email", and "created_at". The "id" column is the primary key, "name" is a required field, "email" must be unique, and "created_at" has a default value of the current timestamp.

5. What is a subquery in SQL and how can it be used in a query?

A subquery is a SELECT statement nested inside another SELECT statement, used to retrieve data from one or more tables. It can be used to filter or manipulate data in a query, or to provide data for another query.

Here is an example of a subquery:

SELECT name, price

FROM products

WHERE price > (

SELECT AVG(price)

FROM products

);

This query selects the name and price of all products where the price is greater than the average price of all products.