SQL and NoSQL Databases Flashcards
What is Open-source?
- type of computer software in which the copyright holder grants users the right to study, change, and distribute the software for any purpose
What is relational databases?
- designed for all purposes
- ACID (Atomicity, Consistency, Isolation, Durability)
- Mathematical background
- Vertically scalable (but not horizontally = over multiple computers)
What does SQL mean?
- Standard query language
What characterizes NoSQL databases?
- Rather called “not only SQL” than NoSQL
- Non-relational
- Cluster friendly, Horizontal scaling
- Schema-less = No burden of up-front schema design
- 21 century web
- Open source
- Minimum overhead
- Solution to impedance mismatch
- Examples: Redis, MongoDB, Cassandra etc.
What are the pros and cons of SQL databases?

What are the pros and cons of NoSQL databases?

What are the 4 different aggregate data model families?
- Key-value data models
- Column-family
- Document-based
- Graph

What are Key-value Data models about?
- use of hash table
- you access data/values by strings called keys
- data has no required format

What are Column-Family Data models about?
- the column is the smallest instance of data
- A tuple containing a name, a value and a timestamp
- Facebook went from reading 50gb data in 350ms to 15ms by rewriting using Cassandra instead of MySQL

What are Graph Data Models about?
- Scale vertically
- No clustering
- Transactions
- Acid
- This is Neo4J
*
What are Document-based Data Models about?
- Usually JSON like interchange model
- Query model: javascript-like or custom
- Indexes are done via B-trees
- Unlike simple key-value stores, both keys AND values are fully searchable in document databases

What are the two ways of finding relations in a database within MySQL Workbench?
- Check the script for “KEY”s / Foreign keys
- Go ask MySQL Workbench to generate the table for a particular table and you can see the contents.
In MySQL workbench, how would you find # the addresses and name of the customers who live in Paris?
select customerName, addressLine1 from customers where city=’Paris’;
In MySQL workbench, how would you find # the addresses and name of the customers who live in Paris OR Frankfurt?
select customerName, addressLine1 from customers where (city=’Paris’ OR city=’Frankfurt’);
what is the mod-operator?
Mod means “remainder”
what is the XOR operator?
Exclusive OR
When one is true AND the other is false
Arrived with train OR car but not with both. It is one of the other
In MySQL workbench, how would you find # the top 10 customers and their country BY credit limit?
SELECT customerName,country,creditLimit FROM customers order by creditLimit DESC limit 10;
In MySQL workbench, how would you find the customers with a customer number ranging from 100 to 200?
select customerName from customers where customerNumber between 100 and 200;
In MySQL workbench, how would you find the customers and the city they live whose city name start with S and live in the USA
select customerName from customers where city LIKE ‘S%’ AND country= ‘USA’;
In mySQL workbench, how do you find all those customers who have no customer sales representative
select customerName from customers where salesRepEmployeeNumber IS NULL;
In mySQL workbench, how do you find the worst 10 customers and their country of origin according to their credit limit?
select customerName, country, creditLimit from customers order by creditLimit ASC limit 10;
In mySQL workbench, print the customer number and checknumber from all those payments that are higher than 50000?
select customerNumber, checknumber from payments where amount > 50000;
In mySQL workbench, print the customer number and checknumber from all those payments that are higher than 50000 ORDERED by amount?
select customerNumber, checknumber from payments where amount > 50000 order by amount;
In mySQL workbench, # print all the customers whose address has RUE?
select customerNumber from customers where addressLine1 LIKE ‘%rue%’;