employeeId | firstname | lastname | branchId | gender | birthdate | employmentDate |
---|---|---|---|---|---|---|
38396 | Brad | Vander Zanden | B003 | M | 1964-02-03 | 1990-08-01 |
53986 | Smiley | Hound | B002 | F | 2005-11-28 | 2007-04-27 |
14983 | Winnie | The Pooh | B002 | NULL | 1940-10-06 | 2010-12-25 |
00576 | Ebby | Lab | B003 | F | NULL | 2002-8-15 |
Employee(employeeId, firstname, lastname, branchId, gender, birthdate, employmentDate) Branch(branchId, streetAddress, city, state, zipcode, name)employeeId in Employee and branchId are primary keys while branchId in Employee is a foreign key. the branchId allows the Employee and Branch relations to be joined, so that, for example, we can discover the name of the branch at which "Brad Vander Zanden" works.
SELECT firstname, lastname, employmentDate FROM Employee WHERE employmentDate < '1990-01-01';
SELECT E.firstname, E.lastname, B.name FROM Branch B NATURAL JOIN Employee E WHERE E.firstname = "Brad" AND E.lastname = "Vander Zanden";The NATURAL JOIN command tells the RDBMS to join the two tables on columns with identical names, which in this case is branchId.
SELECT branchId, COUNT(employeeID) as staffCount FROM Employee GROUP BY branchId HAVING COUNT(employeeID) > 50 ORDER BY branchId;
{ knoxville : [78, 81, 80, 69], nashville : [86, 82, 93, 95, 94, 78], memphis : [93, 92] }A more complicated example might be a contacts database:
{ "brad vanderzanden" : { street : "4381 Suffolk Ln", city : "Columbus", state: "OH", zipcode: 42321, email: "bvanderzanden@utk.edu" } "storm" : { apartment: "Steeplechase Apartments", unit: 5218, city : "Knoxville", state: "TN", zipcode: 37919, poBox: 481, phone: "865-315-9398" } }Notice that there are some shared fields and some distinctly different fields in the two key-value pairs.
File 1: (knoxville, 81), (nashville, 95), (memphis, 93) File 2: (chattanooga, 75), nashville, 83), (franklin, 62), (memphis, 88) File 3: (maryville, 69), (johnson city, 78), (knoxville, 89) File 4: (nashville, 98), (memphis, 83), (alcoa, 75), (maryville, 78) File 5: (knoxville 48), (franklin, 49), (nashville, 53)We could then use a Reduce operation to find the maximum daily high for each city:
(alcoa, 75), (chattanooga, 75), (franklin, 62), (johnson city, 78) (knoxville, 89), (maryville, 78), (memphis, 93), (nashville, 98)
db.contacts.find( { apartment: "Steeplechase Apartments" } );These query languages tend to be much more low-level than the RDBMS query languages and they also do not tend to support joins.