Database Flashcards
Types of Database
1) SQL - Structured Query Language
2) NoSQL - Not only Structured Query Language
SQL characteristic
1// Pre defined Structure
2// Doesn’t like content to be going beyond whats defined - or else it will assign the other values to Null unless its specified not todo so.
3// Is great for keeping relations among tables.
4// Can scale vertically only.
5// As the tables scales vertically, sorting through information becomes harder and slower.
NoSQL characteristic
1// Doesn’t need a rigid structure. It is flexible.
2// No great at having relations among various tables and complex relationship makes NoSQL slow.
3// Its scalability is worlds better horizontally as you can easily break and divide the tables without any concerns of structure or slowing down.
4// The technology is pretty new - so its constantly developing and changing.
The most important thing to do in Database
CRUD
Create, Read, Update and Destroy.
SQL - Create Table
CREATE TABLE table_name (
column1 data-type, // amount INT
column2 data-type, // name STRING
….
)
As you note here, most operations and keywords are in ALL CAPS.
SQL - data-type
There are many, check them out here - https://www.w3schools.com/sql/sql_datatypes.asp
Commonly used one are INT, STRING, MONEY (// for currency), CHAR(size) (// to limit the letters getting input), TEXT.
SQL - PRIMARY KEY
PRIMARY KEY (column_name) is specified to make a row uniquely identify from the rest of the row.
Primary Key is assigned to one of the row that have been initialized.
SQL - NOT NULL
While initializing a column, we can force the value to be NOT NULL.
Example -
CREATE TABLE prices ( id INT NOT NULL, amount MONEY NOT NULL, name STRING, PRIMARY KEY (id) )
SQL - INSERT INTO
After initializing the table, we can insert data into the table using INSERT INTO.
For example -
INSERT INTO table_name VALUES (col1_val, col2_val, col3_val)
OR
INSERT INTO table_name (col1, col2, col3) VALUES (col1_val, col2_val, col3_val)
The second way of writing the code is chosen when we need to insert data in specific columns.
SQL - Read the table
SELECT command is used for reading database.
For example-
SELECT * FROM table_name // => Shows the whole table under the table_name
SELECT id, amount FROM table_name // => Shows the id and amount column under table_name
SELECT if, amount FROM table_name WHERE conditions // => Shows the id, amount column under table _name and condition is true.
SQL - UPDATE
UPDATE table_name SET column_name=value WHERE conditions
Without ‘WHERE’, everything in the column will be set to ‘value’. So, WHERE is important.
SQL - Update the Table colums
ALTER TABLE table_name ADD new_column_name data-type
Changes the table layout
SQL - Delete
DELETE FROM table_name //=> deletes everything from the table if no WHERE is specified
WHERE column=value //=> delete the row that satisfies the condition.
SQL - Relation between tables
We use FOREIGN KEY to establish some relation between the tables
For example -
CREATE TABLE table_name (
quantity INT,
customer _name STRING,
FOREIGN KEY (quantity) REFERENCES price_table(price)
)
SQL - JOIN tables
After having relations among tables, we can join the tables to make a new table.
SELECT table1_name.table1_column_name, table2_name.table2_column_name,
FROM table_where_join_occurs
INNER JOIN table1_name_to_join ON table_name.foreignkey = table_name.primarykey
