SQL/Databases
No description yet. This curated deck is ready for daily study.
Card preview (6)
What is the ACID property in a database?
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.
What is normalization? Explain the 5 different types of normalization?
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
Write a SQL statement to know which salesman are working for which customer 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 salesman table salesman_id | name | city | commission ————-+————+———-+———— 5001 | James Hoog | New York | 0.15 5002 | Nail Knite | Paris | 0.13 5005 | Pit Alex | London | 0.11 5006 | Mc Lyon | Paris | 0.14 5007 | Paul Adam | Rome | 0.13 5003 | Lauson Hen | San Jose | 0.12
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;
Write a query to find those customers with their name and those salesmen with their name and city who lives in the same city. salesman table: salesman_id | name | city | commission ————-+————+———-+———— 5001 | James Hoog | New York | 0.15 5002 | Nail Knite | Paris | 0.13 5005 | Pit Alex | London | 0.11 5006 | Mc Lyon | Paris | 0.14 5007 | Paul Adam | Rome | 0.13 5003 | Lauson Hen | San Jose | 0.12 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
SELECT customer.cust_name, salesman.name, salesman.city FROM salesman, customer WHERE salesman.city = customer.city;
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
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;
Write a SQL statement to display all those orders by the customers not located in the same cities where their salesmen live. salesman table salesman_id | name | city | commission ————-+————+———-+———— 5001 | James Hoog | New York | 0.15 5002 | Nail Knite | Paris | 0.13 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 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
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