SQL and NoSQL Databases Flashcards Preview

Don't delete this > SQL and NoSQL Databases > Flashcards

Flashcards in SQL and NoSQL Databases Deck (39)
Loading flashcards...
1
Q

What is Open-source?

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

What is relational databases?

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

What does SQL mean?

A
  • Standard query language
4
Q

What characterizes NoSQL databases?

A
  • 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.
5
Q

What are the pros and cons of SQL databases?

A
6
Q

What are the pros and cons of NoSQL databases?

A
7
Q

What are the 4 different aggregate data model families?

A
  • Key-value data models
  • Column-family
  • Document-based
  • Graph
8
Q

What are Key-value Data models about?

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

What are Column-Family Data models about?

A
  • 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
10
Q

What are Graph Data Models about?

A
  • Scale vertically
  • No clustering
  • Transactions
  • Acid
  • This is Neo4J
    *
11
Q

What are Document-based Data Models about?

A
  • 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
12
Q

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

A
  • 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.
13
Q

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

A

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

14
Q

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

A

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

15
Q

what is the mod-operator?

A

Mod means “remainder”

16
Q

what is the XOR operator?

A

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

17
Q

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

A

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

18
Q

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

A

select customerName from customers where customerNumber between 100 and 200;

19
Q

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

A

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

20
Q

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

A

select customerName from customers where salesRepEmployeeNumber IS NULL;

21
Q

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

A

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

22
Q

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

A

select customerNumber, checknumber from payments where amount > 50000;

23
Q

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

A

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

24
Q

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

A

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

25
Q

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

A

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

26
Q

In mySQL workbench, show average amount per transaction?

A

select avg( amount ) as AVG_amount from payments;

27
Q

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

A

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

28
Q

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

A

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

29
Q

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

A

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;

30
Q

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

A

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

31
Q

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

A

sub-query is best to do first

32
Q

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

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

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

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

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

A

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

35
Q

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

A

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;

36
Q

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

A

select * from customers, payments;

37
Q

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

A

SELECT * FROM customers INNER JOIN payments;

38
Q

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

A

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

39
Q
A