We are back with our queries! ๐
We have learned some super useful query operators, that will help us to make much better queries to retrieve the data we need. For this exercise we will use the Crunchbase database.
Practice and write queries to mongo using Mongo CLI.
- Fork this repo
- Clone this repo
- Upon completion, run the following commands
$ git add .
$ git commit -m "done"
$ git push origin master
- Create Pull Request so your TAs can check up your work.
Write your answers in the file queries.md
below each iteration.
Test the mongoDb queries in the command line.
> db.companies.find( { name:"Babelgum" }, { name:1, _id:0 } )
First, we need to import the database we will be using for the lab
. We will use the Crunchbase database. Crunchbase is the premier destination for discovering industry trends, investments, and news about hundreds of thousands of companies globally. From startups to Fortune 500s, Crunchbase is recognized as the primary source of company intelligence by millions of users globally.
The database contains more than 18k documents, and each of them has a lot of information about each of the companies. A document looks like the following:
- You will find the
.zip
file of the Database on the lab folder. - Unzip the file
unzip <nombre del archivo>
- From the terminal, import the database to Mongo using the following command:
$ mongoimport --db companies --collection companies --file companies.json
mongoimport
you should be located in the same folder as the companies.json
file.
- Check in Mongo CLI if everything goes ok:
> show dbs
> use companies
> show collections
You already know how this goes, so let's start working:
- All the companies that it's name match 'Babelgum'. Retrieve only their
name
field. - All the companies that have more than 5000 employees. Limit the search to 20 companies and sort them by number of employees.
- All the companies founded between 2000 and 2005, both years included. Retrieve only the
name
andfounded_year
fileds. - All the companies that had a Valuation Amount of more than 100.000.000 and have been founded before 2010. Retrieve only the
name
andipo
fields. (valutaion_amount is inside an object calledipo
, you will have to search what is the notation to acces objects) - All the companies that have less than 1000 employees and have been founded before 2005. Order them by the number of employees and limit the search to 10 companies.
- All the companies that don't have
partners
. (tip:partners
is an array) - All the companies that have a null type of value on the
category_code
field. - All the companies that have at least 100 employees but less than 1000. Retrieve only the
name
andnumber of employees
fields. - Order all the companies by their IPO valuation_amount descendently. You have to use limit.
- Retrieve the 10 companies with more employees, order by the
number of employees
- All the companies founded on the second semester of the year. Limit your search to 1000 companies.
- All the companies that have been 'deadpooled' after the third year.
- All the companies founded before 2000 that have and acquisition amount of more than 10.000.000
- All the companies that have been acquired after 2005, order by the acquisition amount, and retrieve only their
name
andacquisiton
field. - Order the companies by their
founded year
, retrieving only theirname
andfounded year
. - All the companies that have been founded on the first seven days of the month, including the seventh. Sort them by their
aquisition price
descendently. Limit the search to 10 documents. - All the companies on the 'web'
category
that have more than 4000 employees. Sort them by the amount of employees in ascendant order. - All the companies which their acquisition amount is more than 10.000.000, and currency are 'EUR'.
- All the companies that have been acquired on the first trimester of the year. Limit the search to 10 companies, and retrieve only their
name
andacquisition
fields. - All the companies that have been founded between 2000 and 2010, but have not been acquired before 2011.
Happy Coding! โค๏ธ