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%’;
In mySQL workbench, # count all the customers whose address has RUE?
select count(*) from customers where addressLine1 LIKE ‘%rue%’;
In mySQL workbench, show average amount per transaction?
select avg( amount ) as AVG_amount from payments;
In mySQL workbench, how many customers do we have in different countries?
select country, count(*) as total from customers group by country order by count(*);
In mySQL workbench, # list the total and average credit per each country
select country, AVG(creditLimit) AS average_credit ,SUM(creditLimit) AS total_credit FROM customers GROUP BY country;
In mySQL workbench, for each country, show the number of sales representative, total credit and their average credit. We only want to show those countries whose total credit exceed 100,000
select country, count(distinct salesRepEmployeeNumber) AS total_sales_rep, AVG(creditLimit) AS average_credit, SUM(creditLimit) AS total_credit FROM customers GROUP BY country HAVING SUM(creditLimit)>100000;
In mySQL workbench, # the ID and the total credit of the 3 sales representatives with more accumulated credit limit excluding Italy?
SELECT salesRepEmployeeNumber, SUM(creditLimit) AS total_credit FROM customers WHERE country <>‘Italy’ GROUP BY salesRepEmployeeNumber ORDER BY SUM(creditLimit) DESC LIMIT 3;
Should you make sub-query or mother-query first?
sub-query is best to do first
In mySQL workbench, how many transactions were done that were higher than the average? The data must be referred to year 2004.
- Will use the “between” syntax to get the dates here:*
- SELECT column_name(s)*
- FROM table_name*
- WHERE column_name BETWEEN value1 AND value2;*
- so…*
- SELECT AVG(amount) FROM payments where paymentDate BETWEEN ‘2004-01-01’ AND ‘2004-12-31’;*
- now we need to find the number of transactions higher than this average value found….*
- SELECT count(checkNumber)*
- FROM payments*
- WHERE ((paymentDate BETWEEN ‘2004-01-01’ AND ‘2004-12-31’)*
- and amount >*
- (SELECT AVG(amount) FROM payments where paymentDate BETWEEN ‘2004-01-01’ AND ‘2004-12-31’));*
In mySQL workbench, show all (customer ID and when they did it) transactions that were higher than average
- SELECT customerNumber AS customerID, paymentDate*
- FROM payments*
- where amount > (select AVG(amount) from payments);*
In mySQL workbench, # how many transactions were done that were higher than average from the prior year? the data must be referred to 2005
SELECT count(checkNumber)
FROM payments
WHERE ((paymentDate BETWEEN ‘2005-01-01’ AND ‘2005-12-31’)
and amount >
(SELECT AVG(amount) FROM payments where paymentDate BETWEEN ‘2004-01-01’ AND ‘2004-12-31’));
In mySQL workbench, # now finding the number of customers and average credit of italian, spanish and us customers LESS than the french avg
first finding the avg credit of french customers
SELECT avg(creditLimit)
FROM customers
WHERE country=’France’;
SELECT count(*) as NumberCustomers, AVG(creditLimit), country FROM customers
WHERE (country IN(‘Italy’, ‘Spain’, ‘USA’))
AND (creditLimit) <
(SELECT avg(creditLimit)
FROM customers
WHERE country=’France’)
group by country;
In mySQL workbench, do the cross join-cartesian product of customers and payments (PRODUCT)
select * from customers, payments;
In mySQL workbench, show a table with each amount that each customer spent (PRODUCT)
SELECT * FROM customers INNER JOIN payments;
In mySQL workbench, show for each office the name of the employees that work there (PRODUCT)
SELECT * FROM offices LEFT JOIN employees ON offices.officeCode = employees.officeCode
