Database
svince1884
week_3.accdb| Customer Number | First Name | Last Name | Telephone | Street | City | State | Zip |
|---|---|---|---|---|---|---|---|
| 1 | Tom | Cruise | 1-541-754-3010 | 4567 Main St | Beverly Hills | CA | 90210 |
| 2 | Michael | Hunt | 1-404-753-8700 | 764 Beaver Ln | Kansas City | MO | 64057 |
| 3 | Oscar | Grouch | 1-001-355-4121 | 123 Sesame St | Hollywood | CA | 90210 |
| Serial Number | Disk Type Name | Genre Name | Title | Length | Year Made | Description |
|---|---|---|---|---|---|---|
| 1 | DVD | Comedy | The Dictator | 83 mins | 2012 | The heroic story of a dictator who risked his life to ensure that democracy would never come to the country he so lovingly oppressed. |
| 004B | DVD | Documentary | Embrace | 90 mins | 2016 | Your Body. The Movement. Global Change. |
| 003D | DVD | Comedy | The Mechanic | 93 mins | 2011 | Follows an elite hit man as he teaches his trade to an apprentice. |
| Disk Type Name | Description |
|---|---|
| CD | Compact Disc |
| DVD | Digital Versatile Disc |
| VCD | Video Compact Disc |
| Blu-Ray | Blu Ray |
| Genre Name | Description |
|---|---|
| Drama | Drama |
| Comedy | Comedy |
| Documentary | Documentary |
| Rental Number | Customer Number | Serial Number | Rental Date | Return Date |
|---|---|---|---|---|
| 1 | 1 | 003D | 8/17/15 | 8/25/16 |
| 2 | 2 | 004B | 8/17/15 | 8/25/16 |
| 3 | 2 | 004B | 8/18/15 | 8/26/16 |
| ID | ShowWelcome |
|---|---|
| 1 | true |
SELECT DISTINCT Contacts.Company
FROM Contacts;
SELECT DISTINCT Contacts.[Job Title]
FROM Contacts;
SELECT [ReservationCode], [FlightNumber], [FlightDate], [PassengerNumber], [AgentNumber]
FROM Reservations;
SELECT [Flights].[FlightNumber]
FROM Flights
ORDER BY [FlightNumber];
SELECT [Passengers].[PassengerNumber]
FROM Passengers
ORDER BY [PassengerNumber];
SELECT [Agents].[AgentNumber]
FROM Agents
ORDER BY [AgentNumber];
SELECT DISTINCTROW *
FROM DISKTYPE;
SELECT CUSTOMER.[First Name], CUSTOMER.[Last Name], CUSTOMER.Telephone, CUSTOMER.Street, CUSTOMER.City, CUSTOMER.State, CUSTOMER.Zip
FROM CUSTOMER;
SELECT DISTINCTROW *
FROM DISK;
SELECT DISTINCTROW *
FROM RENTAL;
SELECT CUSTOMER.[Customer Number], CUSTOMER.[First Name], DISK.Title, RENTAL.[Rental Date], RENTAL.[Return Date], CUSTOMER.[Last Name]
FROM (CUSTOMER INNER JOIN RENTAL ON CUSTOMER.[Customer Number] = RENTAL.[Customer Number]) INNER JOIN DISK ON RENTAL.[Serial Number] = DISK.[Serial Number];
SELECT [ReservationCode], [FlightNumber], [FlightDate], [PassengerNumber], [AgentNumber]
FROM Reservations;
SELECT [Agents].[AgentNumber]
FROM Agents
ORDER BY [AgentNumber];
SELECT [Passengers].[PassengerNumber]
FROM Passengers
ORDER BY [PassengerNumber];
SELECT [Flights].[FlightNumber]
FROM Flights
ORDER BY [FlightNumber];
SELECT DISTINCTROW *
FROM GENRE;
SELECT DISKTYPE.[Disk Type Name], DISKTYPE.Description
FROM DISKTYPE;
SELECT DISTINCTROW *
FROM DISKTYPE;
SELECT GENRE.[Genre Name], GENRE.Description
FROM GENRE;
SELECT DISTINCTROW *
FROM GENRE;
SELECT DISTINCTROW *
FROM CUSTOMER;
SELECT DISTINCTROW *
FROM CUSTOMER;
SELECT DISK.[Serial Number], DISK.[Disk Type Name], DISK.[Genre Name], DISK.Title, DISK.Length, DISK.[Year Made], IIf(RENTAL.[Return Date]<=Date(),'In-house',IIf(RENTAL.[Return Date]>Date(),'Checked Out')) AS Status, IIf(RENTAL.[Return Date]<=Date(),(SELECT TOP 1 [First Name]&" "&[Last Name] FROM CUSTOMER INNER JOIN RENTAL ON CUSTOMER.[Customer Number] = RENTAL.[Customer Number]),IIf(RENTAL.[Return Date]>Date(),'N/A')) AS Borrower
FROM DISK INNER JOIN RENTAL ON DISK.[Serial Number] = RENTAL.[Serial Number];
