COP 4540 Database Management

1. COP 4540Database Management Microsoft Access Tutorial Hsin-Yu Ha

2. Create Database • Create Table • Set up attribute type, primary key, foreign key • Query • SQL Language

3. Query 1. Retrieve the names of all the male Resident • Query 2. Retrieve the number of female and male residents • Query 3. Retrieve the names of residents who did not borrow any book in our record • Query 4. Retrieve the Library card id/names of residents who borrow more than once in record • Query 5. Retrieve the names of residents who borrow books more than 10 days • Query 6. What is the total price of books each residents have borrowed?

4. INNER JOIN • Inner Join • SELECT A.Books_title, A.Books_price as A_price , B.Books_price as B_price • FROM Amazon as A INNER JOIN FIU_bookstore as B • ON A.books_title = B.Books_title

5. INNER JOIN(cont.) • Inner Join

6. LEFT JOIN • Left Join • SELECT A.Books_title as A_Books , B.Books_title as B_Books • FROM Amazon as A LEFT JOIN FIU_bookstore as B • ON A.books_title = B.Books_title

7. LEFT JOIN(cont.) • Left Join

8. RIGHT JOIN • Right Join • SELECT A.Books_title as A_Books , B.Books_title as B_Books • FROM Amazon as A RIGHT JOIN FIU_bookstore as B • ON A.books_title = B.Books_title

9. RIGHT JOIN (cont.) • Right Join