Skip to content

Latest commit

 

History

History
88 lines (63 loc) · 7.95 KB

SQL & DB Questions.md

File metadata and controls

88 lines (63 loc) · 7.95 KB

SQL & DB Questions

Questions

Questions & Answers

Q1: What are joins in SQL and discuss its types?

A JOIN clause is used to combine rows from two or more tables, based on a related column between them. It is used to merge two tables or retrieve data from there. There are 4 types of joins: inner join left join, right join, and full join.

  • Inner join: Inner Join in SQL is the most common type of join. It is used to return all the rows from multiple tables where the join condition is satisfied.
  • Left Join: Left Join in SQL is used to return all the rows from the left table but only the matching rows from the right table where the join condition is fulfilled.
  • Right Join: Right Join in SQL is used to return all the rows from the right table but only the matching rows from the left table where the join condition is fulfilled.
  • Full Join: Full join returns all the records when there is a match in any of the tables. Therefore, it returns all the rows from the left-hand side table and all the rows from the right-hand side table. alt text

Q2: Define the primary, foreign, and unique keys and the differences between them?

Primary key: Is a key that is used to uniquely identify each row or record in the table, it can be a single column or composite pk that contains more than one column

  • The primary key doesn't accept null or repeated values
  • The purpose of the primary key is to keep the Entity's integrity
  • There is only one PK in each table
  • Every row must have a unique primary key

Foreign key: Is a key that is used to identify, show or describe the relationship between tuples of two tables. It acts as a cross-reference between tables because it references the primary key of another table, thereby establishing a link between them.

  • The purpose of the foreign key is to keep data integrity
  • It can contain null values or primary key values

Unique key: It's a key that can identify each row in the table as the primary key but it can contain one null value

  • Every table can have more than one Unique key

Q3: What is the difference between BETWEEN and IN operators in SQL?

Answer:

The SQL BETWEEN operator selects values within a given range. It is inclusive of both the ranges, begin and end values are included. The values can be text, date, numbers, or other

For example, select * from tablename where price BETWEEN 10 and 100;

The IN operator is used to select rows in which a certain value exists in a given field. It is used with the WHERE clause to match values in a list.

For example, select COLUMN from tablename where 'USA' in (country);

IN is mainly best for categorical variables(it can be used with Numerical as well) whereas Between is for Numerical Variables alt_text

Q4: Assume you have the given table below which contains information on user logins. Write a query to obtain the number of reactivated users (Users who did not log in the previous month and then logged in the current month)

Alt_text

Answer: First, we look at all the users who did not log in during the previous month. To obtain the last month's data, we subtract an 𝐈𝐍𝐓𝐄𝐑𝐕𝐀𝐋 of 1 month from the current month's login date. Then, we use 𝐖𝐇𝐄𝐑𝐄 𝐄𝐗𝐈𝐒𝐓𝐒 against the previous month's interval to check whether there was login in the previous month. Finally, we 𝗖𝗢𝗨𝗡𝗧 the number of users satisfying this condition.

SELECT 
    DATE_TRUNC('month', current_month.login_date) AS current_month,
    COUNT(*) AS num_reactivated_users 
FROM 
    user_logins current_month
WHERE
    NOT EXISTS (
      SELECT 
        *
      FROM 
        user_logins last_month
      WHERE
        DATE_TRUNC('month', last_month.login_date) BETWEEN DATE_TRUNC('month', current_month.login_date) AND DATE_TRUNC('month', current_month.login_date) - INTERVAL '1 month'
)

Q5:Describe the advantages and disadvantages of relational database vs NoSQL databases

Answer:

Advantages of Relational Databases: Ensure data integrity through a defined schema and ACID properties. Easy to get started with and use for small-scale applications. Lends itself well to vertical scaling. Uses an almost standard query language, making learning or switching between types of relational databases easy.

Advantages of NoSQL Databases: Offers more flexibility in data format and representations, which makes working with Unstructured or semistructured data easier. Hence, useful when still the data schema or adding new features/functionality rapidly like in a startup environment to scale with horizontal scaling. Lends itself better to applications that need to be highly available.

Disadvantages of Relational Databases: Data schema needs to be known in advance. Ale schemas is possible, but frequent changes to the schema for large tables can cause performance issues. Horizontal scaling is relatively difficult, leading to eventual performance bottlenecks

Disadvantages of NoSQL Databases: As outlined by the BASE framework, weaker guarantees of data correctness are made due to the soft-state and eventual consistency property. Managing consistency can also be difficult due to the lack of a predefined schema that's strictly adhered to. Depending on the type of NoSQL database, it can be challenging for the database to handle its types of complex queries or access patterns. Alt_text