Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

MySQL study terms

memorize.ai (lvl 286)

(0)

31 cards

()

Section 1

Preview this deck

What is 3NF in data normalization?

Front

1 / 31

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

6 years ago

Date created

Mar 1, 2020

Cards (31)

Section 1

(31 cards)

What is 3NF in data normalization?

Front

Table is in 2NF and all of its columns are not transitively dependent on primary key

Back

What is an inner join?

Front

Used to return all rows from multiple tables where join condition is satisfied

Back

What does a join statement look like?

Front

select columns from table1 inner join table2 on table1.column = table2.column

Back

What is InnoDB?

Front

InnoDB is the default MySQL 8.0 storage engine * Follows the ACID model

Back

What is a left outer join?

Front

Left outer join returns all rows from the left hand table specified in the ON condition and only those rows from the other table where the join condition is fulfilled

Back

What is a right outer join?

Front

Returns all rows from the RIGHT-HAND table specified in the ON condition and only those rows from the other table where the join is fulfilled

Back

What is an outer join?

Front

all rows are retained, regardless of matching the join condition (may return rows with null fields)

Back

How do you insert in MySQL?

Front

insert into db_name.table_name (c1, c2,..., cn) values(v1,v2,..,vn);

Back

What does it mean for SQL DBs to be vertically scalable?

Front

* Can increase the load on a single server by increasing things like CPU, RAM or SSD

Back

What is the USING clause in MySQL?

Front

USING clause is used if several columns share the same name but you don't want to join using all of these common columns. If USING clause is used, can't have a WHERE clause in the query

Back

What is the ON Clause in MySql?

Front

ON Clause is used to join tables where the column names don't match in both tables. The join conditions are removed from the filter conditions in the WHERE clause.

Back

What is a database schema

Front

A set of fields in a table. May contain multiple tables with multiple fields

Back

What is a transaction?

Front

Transactions are atomic units of work that can be committed or rolled back

Back

What is a MySQL Join and its purpose?

Front

MySQL Join is used in MySQL queries to combine information from 2 tables to form a search result

Back

What is ACID?

Front

Atomicity: SQL transactions are atomic units of work that can be committed or rolled back Consistency: after each commit or rollback, while transactions are in progress... queries either see all old values or all new values, not a mix of both! Isolation: Transactions are protected from each other while they are in progress; they cannot interfere with each other or see other's uncommitted data. Achieved through locking mechanism Durability: Once a commit operation success changes made by that transaction are safe from power failures, system crashes, race conditions, or potential dangers

Back

What is a MySQL Transaction?

Front

Are atomic units of work that can be committed or rolled back. When ____ makes multiple changes to the database, either all changes succeed when transaction is committed or all the changes are undone when transaction is rolled back. Example: BEGIN SELECT * FROM table1 WHERE type=1; UPDATE table2 SET summary='a' WHERE type=1; COMMIT

Back

What does it mean for NOSQL DBs to be horizontally scalable?

Front

* Can handle more traffic by sharding or adding more servers in your NoSQL db

Back

What is 1NF in Data Normalization?

Front

Info is stored in a relational table with each column containing atomic values. There are no repeating groups of columns

Back

What are advantages of InnoDB?

Front

* follows ACID model * Row level locking * InnoDB tables arrange your data on disk to optimize queries based on primary keys. InnoDB has a primary key called clustered index that organizes the data to minimize I/O for primary key lookups * Supports FOREIGN KEY constraints. with foreign keys, inserts, updates, and deletes are checked to ensure they do not result in inconsistencies across different tables

Back

What is a primary key?

Front

It is a column in a table used to uniquely identify its row. It cannot be null

Back

Name all MySQL aggregate functions

Front

1. count() <- returns the count of expression 2. sum() returns the total summed value 3. avg() returns the average value 4. min() returns minimum value 5. max() returns max value

Back

What does NULL in MySQL mean?

Front

NULL means missing value but e.g NULL+0 = NULL or 0+NULL=0

Back

What is a foreign key?

Front

A column or group of columns used to link between 2 data tables

Back

Why do Database Normalization?

Front

1. Minimize duplicate data 2. Minimize or avoid data modification issues 3. Simplify queries

Back

What is the difference between primary key and foreign key

Front

Primary key is used to identify a row in a table it belongs to whereas foreign key is a field in another table that is used to reference the original table

Back

What is 2NF in Data Normalization

Front

The table is in first normal form and all columns depend on the table's primary key

Back

Relational DB Traits

Front

* MySQL is compatible for all platforms including Linux, Windows, Mac, BSD. Supports languages like Nodejs, Ruby, C#, C++, Java.. via connectors * MySQL Cost Effective: DB is open source and free * MySQL is Replicable: increases availability of the application

Back

T/F Sharding Can be done on MySQL servers

Front

True -> do not mix this up with Can sharding be done on most SQL databases and the answer to that is no

Back

Database Normalization?

Front

3 common forms 1st, 2nd and 3rd normal form 1NF, 2NF, 3NF 3nf needs to also satisfy 1nf and 2nf 2nf needs to also satisfy 1nf

Back

How do you update a row in MySQL?

Front

update db_name.table_name set employee_name = "tester" where employee_id = 1;

Back

MongoDB Traits

Front

* Dynamic Schema: flexible data schema without modifying any of your existing data * Horizontally Scalable: helps reduce workload by introducing more servers to hold parts of the load across. * Does not require a db admin since it can be used by both devs and admins

Back