Databases: SQL & NoSQL

SQL: Pros and Cons

1. Good for structured data w/ defined relationships. - really quick for complex queries because of well-defined relationships (complex joins) - NoSQL: queries are slower since there is not much rows / cols enforced - writing data is quick since there is not much structure 2. Data follow a specific format: data you get back is predictable - returned data is not always predictable (schema-less) Scalability Another big difference between SQL and NoSQL is their scalability. Most SQL databases are vertically scalable, which means that you can increase the load on a single server by increasing components like RAM, SSD, or CPU. Cannot handle when many connections are happening.

NoSQL: Pros and Cons

1. Scale pretty well for multiple people connecting to them at the same time - SQL can only have a few connections 2. Storing unstructured Data Scalability In contrast, NoSQL databases are horizontally scalable, which means that they can handle increased traffic simply by adding more servers to the database.

3. What is a FOREIGN KEY?

A FOREIGN KEY comprises of single or collection of fields in a table that essentially refers to the PRIMARY KEY in another table. Foreign key constraint ensures referential integrity in the relation between two tables. The table with the foreign key constraint is labeled as the child table, and the table containing the candidate key is labeled as the referenced or parent table.

2. What is a UNIQUE Constraint?

A UNIQUE constraint ensures that all values in a column are different. This provides uniqueness for the column(s) and helps identify each row uniquely. Unlike primary key, there can be multiple unique constraints defined per table. The code syntax for UNIQUE is quite similar to that of PRIMARY KEY and can be used interchangeabl

What are Databases?

A database is an organized collection of data, stored and retrieved digitally from a remote or local computer system. Databases can be vast and complex, and such databases are developed using fixed design and modeling approaches.

What is a Query?

A query is a request for data or information from a database table or combination of tables. A database query can be either a select query or an action query.

What is a Subquery? What are its types?

A subquery is a query within another query, also known as a nested query or inner query. It is used to restrict or enhance the data to be queried by the main query, thus restricting or enhancing the output of the main query respectively. For example, here we fetch the contact information for students who have enrolled for the maths subject:

What are Tables and Fields?

A table is an organized collection of data stored in the form of rows and columns. Columns can be categorized as vertical and rows as horizontal. The columns in a table are called fields while the rows can be referred to as records.

What is a NoSQL database?

A term used to describe high-performance, non-relational databases. NoSQL databases use a variety of data models, including document, graph, key/value, and columnar. NoSQL databases can be document based, key-value pairs, graph databases NoSQL is better for unstructured data like documents or JSON

✍️ Write a SQL statement to add primary key constraint 'pk_a' for table 'table_a' and fields 'col_b, col_c'.

ALTER TABLE table_a ADD CONSTRAINT pk_a PRIMARY KEY (col_b, col_c);"

✍️ Write a SQL statement to add a FOREIGN KEY 'col_fk' in 'table_y' that references 'col_pk' in 'table_x'.

ALTER TABLE table_y ADD FOREIGN KEY (col_fk) REFERENCES table_x (col_pk)

✍️ Write a SQL statement to add primary key 't_id' to the table 'teachers'.

ALTER TABLE teachers ADD PRIMARY KEY (t_id)

MySQL runs multiple queries at the same time.

All queries in MySQL connection are done one after another. It means that if you want to do 10 queries and each query takes 2 seconds then it will take 20 seconds to complete the whole execution. The solution is to create 10 connections and run each query in a different connection. This can be done automatically using a connection pool.

What are SQL Keys?

An SQL key is either a single column (or attribute) or a group of columns that can uniquely identify rows (or tuples) in a table. SQL keys ensure that there are no rows with duplicate information. Not only that, but they also help in establishing a relationship between multiple tables in the database.

What are Aggregate and Scalar functions?

An aggregate function performs operations on a collection of values to return a single scalar value. Aggregate functions are often used with the GROUP BY and HAVING clauses of the SELECT statement. Following are the widely used SQL aggregate functions: The SQL COUNT function is used to count the number of rows returned in a SELECT statement. A scalar function returns a single value based on the input value. Following are the widely used SQL scalar functions:

What is an Index? Explain its different types.

An index is an optional structure, associated with a table or table cluster, that can sometimes speed data access. By creating an index on one or more columns of a table, you gain the ability in some cases to retrieve a small set of randomly distributed rows from the table.

Scalability of SQL

Because of the relational nature of SQL databases, they are difficult to scale horizontally. For read-heavy systems, it's straightforward to provision multiple read-only replicas (with master-slave replication), but for write-heavy systems, the only option oftentimes is to vertically scale the database up, which is generally more expensive than provisioning additional servers. It's not impossible to horizontally scale write-heavy SQL databases, looking at Google Spanner and CockroachDB, but it's a very challenging problem and makes for a highly complex database architecture.

What are Constraints in SQL?

Constraints are used to specify the rules concerning data in the table. It can be applied for single or multiple fields in an SQL table during the creation of the table or after creating using the ALTER TABLE command. The constraints are:

What is DBMS?

DBMS stands for Database Management System. DBMS is a system software responsible for the creation, retrieval, updation, and management of the database. It ensures that our data is consistent, organized, and is easily accessible by serving as an interface between the database and its end-users or application software. Examples: MySQL, Microsoft Access, Oracle. PostgreSQL, SQLite.

DELETE:

DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

SQL summary

Data is laid out in tables and are related to each other using keys - primary, ids, foreign keys. Links together different data - relational.

Inner Join, Lef & Right Outer, Full Outer Joins

Inner: Retrieves records that have matching values in both tables involved in the join. This is the widely used join for queries. - Joining Martian and Base using Base ID - Each Martian has a Base ID, and each Base has a Base ID Left Outer: Retrieves ALL the records/rows from the left and the matched records/rows from the right table. Right Outer: Retrieves ALL the records/rows from the right and the matched records/rows from the left table. Full Outer: Retrieves all the records where there is a match in either the left or right table.

What is an ORM?

Introduction Object-Relational Mapping (ORM) is a technique that lets you query and manipulates data from a database using an object-oriented paradigm. When talking about ORM, most people are referring to a library that implements the Object-Relational Sequelize Mapping technique, hence the phrase "an ORM". An ORM library is a completely ordinary library written in your language of choice that encapsulates the code needed to manipulate the data, so you don't use SQL anymore; you interact directly with an object in the same language you're using.

What is MongoDB?

MongoDB is a schema-less NoSQL document database. It means you can store JSON documents in it, and the structure of these documents can vary as it is not enforced like SQL databases. MongoDB Atlas takes the responsibility of hosting, patching, managing and securing your db's MongoDB cluster. MongoDB users are needed to connect to these clusters of Databases. NoSQL are not relational data. No pointing and such,.

What is Mongoose?

Mongoose is an Object Data Modeling (ODM) library for MongoDB and Node.js. It manages relationships between data, provides schema validation, and is used to translate between objects in code and the representation of those objects in MongoDB. It acts as an interface to MongoDB by letting you define Models. In this instance, a Model is like a blueprint of a Mongo Index. It maps the properties of the Documents in that Index and lets you interact with them in meaningful ways. It abstracts Mongo query language into an API that is typically more intuitive for a lot of common uses. ODMs are most useful at doing the common CRUD database functions, but they can often fail to cover more advanced queries or aggregations.

MySQL Connection

MySQL Connection: connects to a MySQL server, defined by hostname, user, password, db - hostname defines the location of your MySQL server and database - if you want to connect to the information in a MySQL database, you'll need to know the hostname.

MySQL WorkBench

MySQL Workbench is a Visual database designing and modeling access tool for MySQL server relational database. It facilitates creation of new physical data models and modification of existing MySQL databases with reverse/forward engineering and change management functions. The purpose of MySQL workbench is to provide the interface to work with databases more easily and in a more structured way.

What is MySQl?

MySQL is a relational database management system, like SQL Server, Oracle or IBM DB2, that is used to manage SQL databases.

When to use NoSQL.

NoSQL is the best database to use for large amounts of data or ever-changing data sets. It is also best to use when you have flexible data models or needs that don't fit into the relational model. If you are working with large amounts of unstructured data, "document databases" (e.g., CouchDB, MongoDB, Amazon DocumentDB) are a good fit. If you need quick access to a key-value store without strong integrity guarantees, Redis is a great fit. In need of a complex or flexible search across a lot of data? Elasticsearch is a perfect fit. Horizontal scalability is a core tenet of many NoSQL data stores. Unlike in SQL, their built-in sharding and high availability requirements ease horizontal scaling (i.e., "scaling out"). Furthermore, NoSQL databases like Cassandra have no single point of failure, so applications can easily react to underlying failures of individual members.

What is RDBMS? How is it different from DBMS?

RDBMS stands for Relational Database Management System. DBMS: The data storage in DBMS is done in the form of a file. RDMS: Tables are used to store data in RDBMS. The key difference here, compared to DBMS, is that RDBMS stores data in the form of a collection of tables, and relations can be defined between the common fields of these tables. Most modern database management systems like MySQL, Microsoft SQL Server, Oracle, IBM DB2, and Amazon Redshift are based on RDBMS.

What is the SELECT statement?

SELECT * FROM myDB.students;

COUNT Example

SELECT COUNT(*) FROM orders

SELECT: SQL Query

SELECT CustomerName, City FROM Customers;

✍️ Write a SQL query to select the field "app_id" in table "applications" where "app_id" less than 1000.

SELECT app_id FROM applications WHERE app_id < 1000

✍️ Write a SQL query to fetch the field "app_name" from "apps" where "apps.id" is equal to the above collection of "app_id".

SELECT app_name FROM apps WHERE app_id < 1000

When to use SQL.

SQL is the best database to use for relational data, especially when the relationship between data sets is well-defined and highly navigable. It is also best for assessing data integrity. If you need flexible access to data, SQL allows for high-level ad-hoc queries, and, in most cases, SQL databases are vertically scalable (i.e., increase a single server workload by increasing RAM, CPU, SSD, etc.). Some SQL databases support NoSQL-style workloads via special features (e.g., native JavaScript Object Notation (JSON) data types). If you don't need the horizontal scalability found in NoSQL data stores, these databases are also good for many non-relational workloads. This makes them useful for working with relational and unstructured data without the complexity of different data stores. Though NoSQL is simple, users must consider the implications of the data stores when building applications. They must also consider write consistency, eventual consistency and the impact of sharding on data access and storage. On the other hand, these concerns do not apply to SQL databases, which makes them simpler to build applications on. In addition, their wide usage and versatility simplifies complex queries.

What is SQL?

SQL stands for Structured Query Language, which is a programming language used to communicate with relational databases. SQL stands for Structured Query Language. It is the standard language for relational database management systems. It is especially useful in handling organized data comprised of entities (variables) and relations between different entities of the data.

What is sharding?

Sharding is a method of splitting and storing a single logical dataset in multiple databases. By distributing the data among multiple machines, a cluster of database systems can store larger dataset and handle additional requests. Sharding is necessary if a dataset is too large to be stored in a single database. Sharding is a scale-out technique in which database tables are partitioned and each partition is hosted on its own RDBMS server. In the case of MySQL, this means that each node is its own MySQL RDBMS, with its own set of data partitions.

SQL vs. NoSQL

Storage SQL: store data in tables. NoSQL: have different data storage models. Schema SQL: 1. Each record conforms to a fixed schema. 2. Schema can be altered, but it requires modifying the whole database. NoSQL: 1. Schemas are dynamic. Querying SQL: 1. Use SQL (structured query language) for defining and manipulating the data. NoSQL: 1. Queries are focused on a collection of documents. 2. UnQL (unstructured query language). 3. Different databases have different syntax. Scalability SQL: 1. Vertically scalable (by increasing the horsepower: memory, CPU, etc) and expensive. 2. Horizontally scalable (across multiple servers); but it can be challenging and time-consuming. NoSQL 1. Horizontally scalable (by adding more servers) and cheap ACID SQL: 1. ACID-compliant 2. Data reliability 3. Guarantee of transactions NoSQL: 1. Most sacrifice ACID compliance for performance and scalability

INSERT: SQL Insert Into

The INSERT INTO statement is used to insert new records in a table.

1. What is a Primary Key?

The PRIMARY KEY constraint uniquely identifies each row in a table. It must contain UNIQUE values and has an implicit NOT NULL constraint. A table in SQL is strictly restricted to have one and only one primary key, which is comprised of single or multiple fields (columns).

What is a Join? List its different types.

The SQL Join clause is used to combine records (rows) from two or more tables in a SQL database based on a related column between the two. Inner Join, Lef & Right Outer, Full Outer Joins

How do you use SQL in Node/Express Server?

This is a node.js driver for MySQL. It is written in JavaScript.

What does ACID Mean?

Together, ACID is a set of guiding principles that ensure database transactions are processed reliably. A database transaction is any operation performed within a database, such as creating a new record or updating data within one. Changes made within a database need to be performed with care to ensure the data within doesn't become corrupted. Applying the ACID properties to each modification of a database is the best way to maintain the accuracy and reliability of a database. (A) when you do something to change a database the change should work or fail as a whole (C) the database should remain consistent (this is a pretty broad topic) (I) if other things are going on at the same time they shouldn't be able to see things mid-update (D) if the system blows up (hardware or software) the database needs to be able to pick itself back up; and if it says it finished applying an update, it needs to be certain

UPDATE: SQL Udate

UPDATE Customers SET ContactName = 'Alfred Schmidt' City= 'Frankfurt' WHERE CustomerID = 1;

✍️ Write a SQL query to update the field "status" in table "applications" from 0 to 1.

UPDATE application SET status = 1 WHERE status = 0

MySQL createConnection vs. createPool

Using a MySQL database in your application naturally requires a connection to that database. For small-scale development or experimenting, simply creating a single connection may work fine. More extensive operations like production or complex queries, however, may require more flexibility. In order to see the difference between a single connection and a connection pool, let's start with a simple example of the former: Summary: createConnection When you create a connection with mysql.createConnection, you only have one connection and it lasts until you close it OR connection closed by MySQL. A single connection is blocking. While executing one query, it cannot execute others. hence, your application will not perform good. createConnection mysql.createPool is a place where connections get stored. When you request a connection from a pool,you will receive a connection that is not currently being used, or a new connection.If you're already at the connection limit, it will wait until a connection is available before it continues. A pool while one connection is busy running a query, others can be used to execute subsequent queries. Hence, your application will perform good. ....makes a pool of connections ready to be borrowed

What is On Cascade Update?

When we create a foreign key using UPDATE CASCADE the referencing rows are updated in the child table when the referenced row is updated in the parent table which has a primary key.

What is On Delete Delete?

When we create a foreign key using this option, it deletes the referencing rows in the child table when The referenced row is deleted in the parent table which has a primary key.

Scalability of NoSQL

Without table relationships, data in NoSQL databases can be shared across different data stores, allowing for distributed databases. This makes horizontal scaling much easier, and very large amounts of data can be stored without having to purchase a single, expensive server. NoSQL databases can flexibly support both read-heavy and write-heavy systems. With data spread out across multiple shards/servers, hashing and consistent hashing are very important techniques for determining which shard(s) to route application queries to.