“Copy”: “583/750”
}]
“CustomerID”: 1000,
“LastName”: “Janes”,
“FirstName”: “Jeffrey”,
“EmailAddress”: “Jeffrey.Janes@somewhere.com”,
“State”: “WA”,
“ZIPorPostalCode”: 98005,
“Country”: “USA”
},
}]
},
{
“ArtistID”: 18,
“LastName”: “Horiuchi”,
“FirstName”: “Paul”,
“Nationality”: “United States”,
“DateOfBirth”: 1906,
“DateDeceased”: 1999,
]
},
{
“ArtistID”: 19,
“LastName”: “Graves“,
“FirstName”: “Morris”,
“Nationality”: “United States”,
“DateOfBirth”: 1920,
“DateDeceased”: 2001,
{
“CustomerID”: 1015,
“LastName”: “Twilight”,
“FirstName”: “Tiffany”,
“EmailAddress”: “Tiffany.Twilight@somewhere.com”,
“State”: “WA”,
“ZIPorPostalCode”: 98260,
“Country”: “USA”
}
]
}
]
C. Create a database named VRG3 associated with this user and insert the data
from Figure L-17 into a Works collection.
Creating the database is self-explanatory. Follow the instructions on pages L-15 to L-19. To
“Customers“: [
{
“CustomerID”: 1001,
“LastName”: “Smith”,
“FirstName”: “David”,
“EmailAddress”: “David.Smith@somewhere.com“,
“State”: “CO”,
“ZIPorPostalCode”: 81201,
“Country”: “USA”
}
]
}
},
{
“CustomerID”: 1001,
“LastName”: “Smith”,
“FirstName”: “David”,
“EmailAddress”: “David.Smith@somewhere.com“,
“State”: “CO”,
“ZIPorPostalCode”: 81201,
“Country”: “USA”
}
]
}
},
{
“CustomerID”: 1001,
“LastName”: “Smith”,
“FirstName”: “David”,
“EmailAddress”: “David.Smith@somewhere.com“,
“State”: “CO”,
“ZIPorPostalCode”: 81201,
“Country”: “USA”
}
]
}
},
}
},
{
“WorkID”: 524,
“Title”: “Woman With a Hat”,
“Medium”: “High Quality Limited Print”,
“Description”: “A very colorful Impressionist piece“,
“Copy”: “596/750“,
“Artist”: {
“ArtistID”: 4,
“LastName”: “Matisse”,
“FirstName“: “Henri”,
]
}
},
{
“CustomerID”: 1001,
“LastName”: “Smith”,
“FirstName”: “David”,
“EmailAddress”: “David.Smith@somewhere.com“,
“State”: “CO”,
“ZIPorPostalCode”: 81201,
“Country”: “USA”
}
]
}
},
{
“CustomerID”: 1001,
“LastName”: “Smith”,
“FirstName”: “David”,
“EmailAddress”: “David.Smith@somewhere.com“,
“State”: “CO”,
“ZIPorPostalCode”: 81201,
“Country”: “USA”
}
]
}
},
“Customers“: [
{
“CustomerID”: 1001,
“LastName”: “Smith”,
“FirstName”: “David”,
“EmailAddress”: “David.Smith@somewhere.com“,
“State”: “CO”,
“ZIPorPostalCode”: 81201,
“Country”: “USA”
}
]
}
},
{
“CustomerID”: 1001,
“LastName”: “Smith”,
“FirstName”: “David”,
“EmailAddress”: “David.Smith@somewhere.com“,
“State”: “CO”,
“ZIPorPostalCode”: 81201,
“Country”: “USA”
},
}
]
}
},
{
“WorkID”: 564,
“Title”: “Farmer’s Market #2″,
“Medium”: “High Quality Limited Print”,
“Description”: “Northwest School Abstract Expressionist Style”,
“Copy”: “267/500“,
“FirstName”: “Jeffrey”,
“EmailAddress”: “Jeffrey.Janes@somewhere.com”,
“State”: “WA”,
“ZIPorPostalCode”: 98005,
“Country”: “USA”
},
L.41 Write the following queries on the VRG1 database:
A. Retrieve all customers.
FOR C IN CUSTOMERS
RETURN C
The results in JSON format are:
[
{
“State”: “WA”,
“ZIPorPostalCode“: 98005,
“Country”: “USA“,
“Interests”: [
17,
18,
19
]
},
{
“EmailAddress”: “Tiffany.Twilight@somewhere.com”,
“State”: “WA”,
“ZIPorPostalCode“: 98260,
“Country”: “USA“,
19
]
},
{
“_key”: “1056049″,
“_id”: “Customers/1056049″,
“_rev”: “_WSe1_z—-“,
1,
2,
4,
5,
11
]
}
B. Retrieve the last name and first name of all customers whose state is
Washington (WA).
FOR C IN Customers
C. Retrieve the last name and email address of all customers interested in the artist
whose ArtistID is 11.
FOR C IN Customers
},
{
“Lname”: “Smith”,
D. Retrieve the last name, state, and artist last name of all customers who are
interested in a French artist.
FOR C IN Customers
RETURN {
This query returns the following results, in JSON format:
[
{
“CLname”: “Janes”,
},
{
“CLname”: “Smith”,
“CState”: “CO”,
“ALname”: “Matisse”
}
]
}
]
There are two interesting things to note here: one is that the query ensures that the artist names
are nested within the customer in the results, as they are, in some sense, in the actual data. The
other point is that every customer is included in the result, even if they are not interested in any
French artists. One way to alleviate this is to construct a more “relational” result, as in this
version of the query:
FOR C IN Customers
FOR AC in C.Interests
The results of this query in JSON format are:
[
{
“CLname”: “Smith”,
“CState”: “CO”,
E. Retrieve the last name of all customers who are either from Washington state (WA)
or are interested in fewer than four artists.
FOR C IN Customers
L.42 Write the following queries on the VRG2 database:
A. Retrieve the last name and date of birth for all artists from the United States.
FOR A in Artists
B. For each artist born after 1890, retrieve the last name and the number of
customers interested in that artist. Sort the results in alphabetical order by artist
last name.
FOR A in Artists
FILTER A.DateOfBirth > 1890
L.43 Write the following queries on the VRG3 database:
A. Group art works by artist last name and retrieve, for each group, the artist last
name and the title of the work that comes first alphabetically for that artist.
FOR W in Works
[
{
“Country”: “Chagall”,
“MINTitle”: “I and the Village”
},
{
“Country”: “Klee”,
“MINTitle”: “Angelus Novus”
},
{
]
B. For each work whose artist was born before 1890, retrieve the WorkID, Title,
artist LastName, and all last names of customers from Washington state (WA)
interested in the artist. The customers’ last names should be nested within the
document retrieved for each work.
FOR W IN Works
FILTER “WA” IN W.Artist.Customers[*].State AND W.Artist.DateOfBirth
The results of this query in JSON format are:
[
{
“ID”: 563,
“Title”: “Spanish Dancer”,
}
]
C. Retrieve the last names of all customers, with no duplicates and no nested
arrays.
RETURN UNIQUE (FLATTEN (FOR W IN Works
]
]
Note that DISTINCT is not allowed here because the RETURN is not preceded by a FOR loop,
so we must use the UNIQUE function