JS Interview Related

Inner Joins

selects records that have matching results from 2 given tables

only Returns the matches

Outer Joins

Returns all records when there is a match in either left(table) or right(table2)

can be a huge data set

Left Join

Returns all the data from the left table even if no matches + all the matches from the Right table

Right Join

Returns all the data from the right table even if no matches from the left table...+ the matches from the left table

Stored Procedures

Subroutine that contains a collection of SQl statements

Saves time and resources...especially with complex logic

What is a Relational Database Management System (RDBMS)?

A RDBMS is a system that organizes data into tables called relations, which are further organized into columns (fields) and rows (often called tuples).

The relational model allows data to be queried in a nearly unlimited number of ways, making it great for sorting through large volumes of data.

Common SQL dialects include PL/SQL for Oracle, T-SQL for MS SQL, and JET SQL for MS Access. Look up any particular dialects used for your chosen RDBMS.

Data Manipulation Language (DML)

INSERT: Creates records. The “Create” in CRUD.

SELECT: Retrieves records. The “Read” in CRUD.

UPDATE: Modifies records. The “Update” in CRUD.

DELETE: Deletes records. The “Delete” in CRUD.

Data Definition Language (DDL)

CREATE: Creates a new object.

ALTER: Alters an existing object.

DROP: Deletes an existing object.

Data Control Language: (DCL)

GRANT: Grants privileges to users.

REVOKE: Revokes privileges previously granted to a user.

CRUD

CREATE

READ

UPDATE

DELETE

inner join vs. outer join

An inner join is when you combine rows from two tables and create a result set based on the predicate, or joining condition. The inner join only returns rows when it finds a match in both tables.

An outer join will also return unmatched rows from one table if it is a single outer join, or both tables if it is a full outer join.

SQL Constraints

Constraints are rules you can place on columns or tables to limit the type of data that can be entered into a table.

This prevents errors and can improve the accuracy and reliability of the database as a whole.

Common constraints:

• NOT NULL: Prevents a column from having a NULL value
• DEFAULT: Specifies a default value for a column where none is specified
• PRIMARY KEY: Uniquely identifies rows/records within a database table.
• FOREIGN KEY: Uniquely identifies rows/records from external database tables.
• UNIQUE: Ensures all values are unique
• CHECK: Checks values within a column against certain conditions
• INDEX: Quickly creates and retrieves data from a database

What is the purpose of database normalization and how does it work?

The primary purpose of normalization is to make databases more efficient by eliminating redundant data and ensuring data dependencies are coherent

Storing data logically and efficiently reduces the amount of space the database takes up and improves performance.

The set of guidelines used to achieve normalization are called normal forms, numbered from 1NF to 5NF. A form can be thought of as a best-practice format for laying out data within a database.

Advanced SQL Queries

Boxing vs. Unboxing

Boxing is the process of converting a value type to the type object (integer into an object)

Boxed values use more memory and require memory lookups to access.

Unboxing extracts the value from the object

A monolithic architecture means that your app is written as one cohesive unit of code whose components are designed to work together, sharing the same memory space and resources.

A microservice architecture means that your app is made up of lots of smaller, independent applications capable of running in their own memory space and scaling independently from each other across potentially many separate machines.

Monolithic Pros:

• The major advantage of the monolithic architecture is that most apps typically have a large number of cross-cutting concerns, such as logging, rate limiting, and security features such audit trails and DOS protection. When everything is running through the same app, it’s easy to hook up components to those cross-cutting concerns.
• There can also be performance advantages, since shared-memory access is faster than inter-process communication (IPC).

Monolithic Cons:

• Monolithic app services tend to get tightly coupled and entangled as the application evolves, making it difficult to isolate services for purposes such as independent scaling or code maintainability.
• Monolithic architectures are also much harder to understand, because there may be dependencies, side-effects, and magic which are not obvious when you’re looking at a particular service or controller.

Microservice Pros:

• Microservice architectures are typically better organized, since each microservice has a very specific job, and is not concerned with the jobs of other components.
• Decoupled services are also easier to recompose and reconfigure to serve the purposes of different apps (for example, serving both the web clients and public API).
• They can also have performance advantages depending on how they’re organized because it’s possible to isolate hot services and scale them independent of the rest of the app.

Microservice Cons:

• As you’re building a new microservice architecture, you’re likely to discover lots of cross-cutting concerns that you did not anticipate at design time. A monolithic app could establish shared magic helpers or middleware to handle such cross-cutting concerns without much effort.
• In a microservice architecture, you’ll either need to incur the overhead of separate modules for each cross-cutting concern, or encapsulate cross-cutting concerns in another service layer that all traffic gets routed through.
• Eventually, even monolthic architectures tend to route traffic through an outer service layer for cross-cutting concerns, but with a monolithic architecture, it’s possible to delay the cost of that work until the project is much more mature.
• Microservices are frequently deployed on their own virtual machines or containers, causing a proliferation of VM wrangling work. These tasks are frequently automated with container fleet management tools.

Positive attitudes toward microservices, despite the higher initial cost vs monolthic apps. Aware that microservices tend to perform and scale better in the long run.

Structure the app so that services are independent from each other at the code level, but easy to bundle together as a monolithic app in the beginning. Microservice overhead costs can be delayed until it becomes more practical to pay the price.