cop 4540 spring2010 - users.cs.fiu.educhens/courses/cop4540/2011/spring/home… · crazy for the...
TRANSCRIPT
MICROSOFT ACCESS TUTORIAL
COP 4540Database Management
Hsin-Yu Ha
� Create Database
� Create Table
� Set up attribute type, primary key, foreign key
� Query
� SQL Language
SQL Template
Example: Library Database
� Tables
List of Queries
� 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 less than 5 days
Example: Library Database
� Query 1. Retrieve the names of all the male Resident
SELECT Resident_L_Name,Resident_F_NameFROM ResidentWHERE Sex="M"
Example: Library Database
� Query 2. Retrieve the number of female and male residents
� Important Function
� Aggregation functions: SUM, COUNT, MAX…etc.
SELECT Sex, count(*) as NumberofRecordFROM ResidentGROUP BY Sex
Example: Library Database
� Query 3. Retrieve the names of residents who did not borrow any book in our record
� Important Function
� Nested Select
� Please notice that you can always use inner join or EXIST as an alternative resolution.
SELECT Resident_L_Name,Resident_F_NameFROM ResidentWHERE LibraryCard_ID not in (SELECT LibraryCard_ID
FROM Record)
Example: Library Database
� Query 4. Retrieve the Library card id/names of residents who borrow more than once in record
� Important Function� HAVING: The HAVING clause is used in combination with the GROUP BY
clause
� Step1
� Step2
SELECT LibraryCard_IDFROM RecordGROUP BY LibraryCard_IDHaving count(*) > 1
SELECT Resident_L_Name,Resident_F_NameFROM ResidentWHERE LibraryCard_ID in (SELECT LibraryCard_ID
FROM Record GROUP BY LibraryCard_IDHaving count(*) > 1)
Example: Library Database
� Query 5. Retrieve the names of residents who borrow books less than 5 days
� Option1 SELECT Resident_L_Name,Resident_F_NameFROM ResidentWHERE LibraryCard_ID in (SELECT LibraryCard_ID
FROM Record WHERE Date_of_return - Date_of_borrow<5)
Example: Library Database
� Query 5. Retrieve the names of residents who borrow books less than 5 days
� Important Function
� Datediff
� Option2
SELECT Resident_L_Name,Resident_F_NameFROM ResidentWHERE LibraryCard_ID in (SELECT LibraryCard_ID
FROM Record WHERE datediff(“d”,Date_of_return, Date_of_borrow)< 5
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
Books_title Books_price
Let the Great World Spin 45
Brooklyn 20
The City & the City 33
Stitches 24
Crazy for the Storm 19
Books_title Books_price
Let the Great World Spin 20
The Lost Symbol 30
The City & the City 29
Little Oink 18
Crazy for the Storm 23
INNER JOIN(cont.)
� Inner Join
Books_title Books_price
Let the Great World Spin 45
Brooklyn 20
The City & the City 33
Stitches 24
Crazy for the Storm 19
Books_title Books_price
Let the Great World Spin 20
The Lost Symbol 30
The City & the City 29
Little Oink 18
Crazy for the Storm 23
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
� 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
Books_title Books_price
Let the Great World Spin 45
Brooklyn 20
The City & the City 33
Stitches 24
Crazy for the Storm 19
Books_title Books_price
Let the Great World Spin 20
The Lost Symbol 30
The City & the City 29
Little Oink 18
Crazy for the Storm 23
LEFT JOIN(cont.)
� Left Join
Books_title Books_price
Let the Great World Spin 45
Brooklyn 20
The City & the City 33
Stitches 24
Crazy for the Storm 19
Books_title Books_price
Let the Great World Spin 20
The Lost Symbol 30
The City & the City 29
Little Oink 18
Crazy for the Storm 23
A_Books B_Books
Let the Great World Spin Let the Great World Spin
Brooklyn
The City & the City The City & the City
Stitches
Crazy for the Storm Crazy for the Storm
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
Books_title Books_price
Let the Great World Spin 45
Brooklyn 20
The City & the City 33
Stitches 24
Crazy for the Storm 19
Books_title Books_price
Let the Great World Spin 20
The Lost Symbol 30
The City & the City 29
Little Oink 18
Crazy for the Storm 23
RIGHT JOIN (cont.)
� Right Join
Books_title Books_price
Let the Great World Spin 45
Brooklyn 20
The City & the City 33
Stitches 24
Crazy for the Storm 19
Books_title Books_price
Let the Great World Spin 20
The Lost Symbol 30
The City & the City 29
Little Oink 18
Crazy for the Storm 23
A_Books B_Books
Let the Great World Spin Let the Great World Spin
The Lost Symbol
The City & the City The City & the City
Little Oink
Crazy for the Storm Crazy for the Storm