COP 4540 Database Management MICROSOFT ACCESS TUTORIAL HsinYu
COP 4540 Database Management MICROSOFT ACCESS TUTORIAL Hsin-Yu Ha
Create Database Create Table Set up attribute type, primary key, foreign key Query SQL Language
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?
INNER JOIN Books_title Books_ price Let the Great World Spin 45 Let the Great World Spin 20 Brooklyn 20 The Lost Symbol 30 The City & the City 33 The City & the City 29 Stitches 24 Little Oink 18 Crazy for the Storm 19 Crazy for the Storm 23 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
INNER JOIN(cont. ) Books_title Books_ price Let the Great World Spin 45 Let the Great World Spin 20 Brooklyn 20 The Lost Symbol 30 The City & the City 33 The City & the City 29 Stitches 24 Little Oink 18 Crazy for the Storm 19 Crazy for the Storm 23 Inner Join Books_title A_price B_price Let the Great World Spin 45 20 The City & the City 33 29 Crazy for the Storm 19 23
LEFT JOIN Books_title Books_ price Let the Great World Spin 45 Let the Great World Spin 20 Brooklyn 20 The Lost Symbol 30 The City & the City 33 The City & the City 29 Stitches 24 Little Oink 18 Crazy for the Storm 19 Crazy for the Storm 23 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
LEFT JOIN(cont. ) Books_title Books_ price Let the Great World Spin 45 Let the Great World Spin 20 Brooklyn 20 The Lost Symbol 30 The City & the City 33 The City & the City 29 Stitches 24 Little Oink 18 Crazy for the Storm 19 Crazy for the Storm 23 Left Join A_Books B_Books Let the Great World Spin Brooklyn The City & the City Stitches Crazy for the Storm
RIGHT JOIN Books_title Books_ price Let the Great World Spin 45 Let the Great World Spin 20 Brooklyn 20 The Lost Symbol 30 The City & the City 33 The City & the City 29 Stitches 24 Little Oink 18 Crazy for the Storm 19 Crazy for the Storm 23 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
RIGHT JOIN (cont. ) Books_title Books_ price Let the Great World Spin 45 Let the Great World Spin 20 Brooklyn 20 The Lost Symbol 30 The City & the City 33 The City & the City 29 Stitches 24 Little Oink 18 Crazy for the Storm 19 Crazy for the Storm 23 Right Join A_Books B_Books Let the Great World Spin The Lost Symbol The City & the City Little Oink Crazy for the Storm
- Slides: 9