Database Worksheet

database_copy.accdb

Home >Computer Science homework help >Database Worksheet

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
003D	DVD	Comedy	The Mechanic	93 mins	2011	Follows an elite hit man as he teaches his trade to an apprentice.
004B	DVD	Documentary	Embrace	90 mins	2016	Your Body. The Movement. Global Change.
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.

Disk Type Name	Description
Blu-Ray	Blu Ray
CD	Compact Disc
DVD	Digital Versatile Disc
VCD	Video Compact Disc

Genre Name	Description
Comedy	Comedy
Documentary	Documentary
Drama	Drama

ID	Caption	Form or Report
1	Borrowers	frmListBorrowers
2	Disks	frmListMedia
3	Disk Types	frmListDiskTypes
4	Genres	frmListGenres
5	Rentals	frmListRentals
6	Sorted Inventory	rptMediaList
7	Overdue Items	rptPastDue

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/24/16
3	2	1	8/18/15	8/26/16

ID	ShowWelcome
1	true

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 DISTINCT Contacts.Company FROM Contacts;

SELECT DISTINCT Contacts.[Job Title] FROM Contacts;

SELECT GENRE.[Genre Name], GENRE.Description FROM GENRE;

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 GENRE;

SELECT DISTINCTROW * FROM CUSTOMER;

SELECT DISTINCTROW * FROM DISKTYPE;

SELECT DISTINCTROW * FROM Settings;

SELECT DISTINCTROW * FROM DISK;

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 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];