SQL/Databases Flashcards

Question 1

Q

What is the ACID property in a database?

Answer

A

Atomicity: entire transactions or doesn’t happen at all

Consistency: Data must meet all validation rules and be consistent before/after a transaction is made

Isolation: each and every transaction is independent from one another

Durability: committed transaction will never be lost.

Question 2

Q

What is normalization? Explain the 5 different types of normalization?

Answer

A

1nf: there are not multiple values in a row, single valued attribute
solution: create more rows to represent the attribute

2nf: must be in 1nf and relation has NO PARTIAL DEPENDENCY (attributes are not part of any candidate key) dependent on any proper subset of any candidate key of the table
solution: split table into 2 tables

3nf: must be dependent solely on the primary key and no other non key (supporting) column values
solution: split into separate tables so each column is only dependent on the primary key

bcnf(Boyce Codd Normal Form): must in 3nf, for any dependency a -> b, a should be a super key. for a dependency a -> b, a cannot be a non prime attribute if b is a prime attribute

4nf: must be in bcnf and should not have any multi-valued dependency.
multi valued dependency:
1. for a dependency a -> b, if for a single value of a, multiple value of b exists then table may have multi valued dependency
2. table should have at least 3 columns for it to have a multi valued dependency
3. for a relation R(A,B,C) if there’s a multi valued dependency between A and B, then B and C should be independent of each other

Question 3

Q

Write a SQL statement to know which salesman are working for which customer

Answer

A

SELECT a.cust_name AS "Customer Name", 
a.city, b.name AS "Salesman", b.commission 
FROM customer a 
INNER JOIN salesman b 
ON a.salesman_id=b.salesman_id;

Question 4

Q

Write a query to find those customers with their name and those salesmen with their name and city who lives in the same city.

customer table:
customer_id | cust_name | city | grade | salesman_id
————-+—————-+————+——-+————-
3002 | Nick Rimando | New York | 100 | 5001
3007 | Brad Davis | New York | 200 | 5001
3005 | Graham Zusi | California | 200 | 5002
3008 | Julian Green | London | 300 | 5002
3004 | Fabian Johnson | Paris | 300 | 5006
3009 | Geoff Cameron | Berlin | 100 | 5003
3003 | Jozy Altidor | Moscow | 200 | 5007
3001 | Brad Guzan | London | | 5005

Answer

A

SELECT customer.cust_name,
salesman.name, salesman.city
FROM salesman, customer
WHERE salesman.city = customer.city;

Question 5

Q

Create an SQL query that shows the TOP 3 authors who sold the most books in total!

author table:
author_name | book_name
author_1 | book_1
author_1 | book_2

books table:
book_name sold_copies
book_1 1000
book_2 1500

Answer

A

select a.author_name, sum(b.sold_copies) as sold_sum
from authors as a 
join books as b
on b.book_name = a.book_name
order by sold_sum DESC
LIMIT 3;

Question 6

Q

Write a SQL statement to display all those orders by the customers not located in the same cities where their salesmen live.

orders table:
ord_no purch_amt ord_date customer_id salesman_id
———- ———- ———- ———– ———–
70001 150.5 2012-10-05 3005 5002
70009 270.65 2012-09-10 3001 5005

Answer

A

select o.ord_no, c.customer_id, o.customer_id, o.salesman_id
from salesman as s, customer as c, orders as o
where c.city <> s.city
AND o.customer_id = c.customer_id
AND o.salesman_id = s.salesmann_id

Brainscape's Knowledge GenomeTM

SQL/Databases Flashcards

Brainscape's Knowledge Genome^TM