SQL and NoSQL Databases

Question 1

What is Open-source?

Answer 1

• type of computer software in which the copyright holder grants users the right to study, change, and distribute the software for any purpose

Question 2

What is relational databases?

Answer 2

• designed for all purposes
• ACID (Atomicity, Consistency, Isolation, Durability)
• Mathematical background
• Vertically scalable (but not horizontally = over multiple computers)

Question 3

What does SQL mean?

Answer 3

• Standard query language

Question 4

What characterizes NoSQL databases?

Answer 4

• 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.

Question 5

What are the pros and cons of SQL databases?

Answer 5

Question 6

What are the pros and cons of NoSQL databases?

Answer 6

Question 7

What are the 4 different aggregate data model families?

Answer 7

• Key-value data models
• Column-family
• Document-based
• Graph

Question 8

What are Key-value Data models about?

Answer 8

• use of hash table
• you access data/values by strings called keys
• data has no required format

Question 9

What are Column-Family Data models about?

Answer 9

• 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

Question 10

What are Graph Data Models about?

Answer 10

• Scale vertically
• No clustering
• Transactions
• Acid
• This is Neo4J
  *

Question 11

What are Document-based Data Models about?

Answer 11

• 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

Question 12

What are the two ways of finding relations in a database within MySQL Workbench?

Answer 12

• 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.

Question 13

In MySQL workbench, how would you find # the addresses and name of the customers who live in Paris?

Answer 13

select customerName, addressLine1 from customers where city=’Paris’;

Question 14

In MySQL workbench, how would you find # the addresses and name of the customers who live in Paris OR Frankfurt?

Answer 14

select customerName, addressLine1 from customers where (city=’Paris’ OR city=’Frankfurt’);

Question 15

what is the mod-operator?

Answer 15

Mod means “remainder”

Question 16

what is the XOR operator?

Answer 16

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

Question 17

In MySQL workbench, how would you find # the top 10 customers and their country BY credit limit?

Answer 17

SELECT customerName,country,creditLimit FROM customers order by creditLimit DESC limit 10;

Question 18

In MySQL workbench, how would you find the customers with a customer number ranging from 100 to 200?

Answer 18

select customerName from customers where customerNumber between 100 and 200;

Question 19

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

Answer 19

select customerName from customers where city LIKE ‘S%’ AND country= ‘USA’;

Question 20

In mySQL workbench, how do you find all those customers who have no customer sales representative

Answer 20

select customerName from customers where salesRepEmployeeNumber IS NULL;

Question 21

In mySQL workbench, how do you find the worst 10 customers and their country of origin according to their credit limit?

Answer 21

select customerName, country, creditLimit from customers order by creditLimit ASC limit 10;

Question 22

In mySQL workbench, print the customer number and checknumber from all those payments that are higher than 50000?

Answer 22

select customerNumber, checknumber from payments where amount > 50000;

Question 23

In mySQL workbench, print the customer number and checknumber from all those payments that are higher than 50000 ORDERED by amount?

Answer 23

select customerNumber, checknumber from payments where amount > 50000 order by amount;

Question 24

In mySQL workbench, # print all the customers whose address has RUE?

Answer 24

select customerNumber from customers where addressLine1 LIKE ‘%rue%’;

Question 25

In mySQL workbench, # count all the customers whose address has RUE?

Answer 25

select count(*) from customers where addressLine1 LIKE ‘%rue%’;

Question 26

In mySQL workbench, show average amount per transaction?

Answer 26

select avg( amount ) as AVG_amount from payments;

Question 27

In mySQL workbench, how many customers do we have in different countries?

Answer 27

select country, count(*) as total from customers group by country order by count(*);

Question 28

In mySQL workbench, # list the total and average credit per each country

Answer 28

select country, AVG(creditLimit) AS average_credit ,SUM(creditLimit) AS total_credit FROM customers GROUP BY country;

Question 29

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

Answer 29

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;

Question 30

In mySQL workbench, # the ID and the total credit of the 3 sales representatives with more accumulated credit limit excluding Italy?

Answer 30

SELECT salesRepEmployeeNumber, SUM(creditLimit) AS total_credit FROM customers WHERE country <>‘Italy’ GROUP BY salesRepEmployeeNumber ORDER BY SUM(creditLimit) DESC LIMIT 3;

Question 31

Should you make sub-query or mother-query first?

Answer 31

sub-query is best to do first

Question 32

In mySQL workbench, how many transactions were done that were higher than the average? The data must be referred to year 2004.

Answer 32

• 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’));*

Question 33

In mySQL workbench, show all (customer ID and when they did it) transactions that were higher than average

Answer 33

• SELECT customerNumber AS customerID, paymentDate*
• FROM payments*
• where amount > (select AVG(amount) from payments);*

Question 34

In mySQL workbench, # how many transactions were done that were higher than average from the prior year? the data must be referred to 2005

Answer 34

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’));

Question 35

In mySQL workbench, # now finding the number of customers and average credit of italian, spanish and us customers LESS than the french avg

Answer 35

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;

Question 36

In mySQL workbench, do the cross join-cartesian product of customers and payments (PRODUCT)

Answer 36

select * from customers, payments;

Question 37

In mySQL workbench, show a table with each amount that each customer spent (PRODUCT)

Answer 37

SELECT * FROM customers INNER JOIN payments;

Question 38

In mySQL workbench, show for each office the name of the employees that work there (PRODUCT)

Answer 38

SELECT * FROM offices LEFT JOIN employees ON offices.officeCode = employees.officeCode