A sample SQL database project that contains 3 sample queries in the queries.py file, written in Python.
To connect to the database, make sure you have psql
(https://launchschool.com/blog/how-to-install-postgresql-on-a-mac)
installed on your terminal as a command. Then, unzip the newsdata.zip
to get a newsdata.sql
file, and move that
file to your directory. cd
into the directory, and run the following commands:
psql
-d news
-f newsdata.sql
This connects to the installed database, creates the tables from the file, and populates
them with data. From there, you can run many psql
commands (https://www.postgresql.org/docs/8.4/static/tutorial-select.html)
to query the data, or to explore the relations and their respective columns of data. The following relations contained in the news database are:
log
authors
articles
psql
commands to explore the database:
\d [insert relation name here]
to fetch the columns contained in the relation, and their respective datatypes\dt
to fetch all the relations in the database\dt+
to fetch all the relations in the database with disk storage informationselect * from [insert relation name here]
explores all the populated data as tuples in a relation. NOTE: Some relations take a while to query all the data, especiallylog
. This is okay as it takes time to load a large amount of populated tuples!
To run this application, change directory into this application via bash and run the command: "python queries.py" from bash. You are then given 3 different queries to choose from:
- Find the top 3 articles of all time based on views.
- Find the top authors of all time on an aggregation count of all article views by the same actor.
- Find the day(s) where the number of errors a website encountered is greater than 1%.
- Exit the program.
For the results on those queries, kindly refer to the results.txt file.
I hope you have a kick ass time with psql
!
Special thanks to the Udacity team for fostering incredible education and passion into the hearts of their students.