Querying Databases (SQL)

The main purpose of developing or designing a database is to be able to get information from the database to help in decision making. A database query is a piece of code that is sent to a database in order to get information back from the database. These queries are usually constructed using SQLs (structured query language). SQL is a special purpose programming language designed for managing data in a relational database management systems (RDMS).

Populating the Database

After creating the database in PostgreSQL schema using DDLs in the database design process, one needs to populate the it with the necessary data. The fish data was in a text file format (CSV), this text file had to be copied from the location it was stored on the computer into the database. An SQL code was used to achieve this, using a 'COPY' and 'FROM' command. The SQL code used is shown below;

COPY tbl_sites FROM 'location on computer/fish_csv/tbl_Sites.txt' DELIMITERS ',' CSV; 

COPY tbl_sitevisits FROM ''location on computer/fish_csv/R_SiteVisits.txt' DELIMITERS ',' CSV; 

COPY tbl_species FROM ''location on computer/fish_csv/W_FI_zdd_FISH_Species.txt' DELIMITERS ',' CSV;

COPY tbl_gamefish FROM ''location on computer/fish_csv/W_FI_Gamefish.txt' DELIMITERS ',' CSV; 

COPY tbl_nongamefish FROM 'location on computer/fish_csv/W_FI_Non_Gamefish.txt' DELIMITERS ',' CSV; 

the tbl_sites, sitesvisits, species, game-fish and non game-fish are the tables that were created using the DDL in the geodatabase design process, and the COPY command is just requesting that the fish data from the text file be copied FROM the location in is stored on the computer to the tables created in the database. A sample of one of the populated tables is shown in the image below;

Screenshot of the game-fish table created after populating it with data(click to enlarge)

Querying the Populated Database

Now that the database has been populated with data, queries can be run on the database to answer desired question. Various commands are used when running a query. The most commonly used commands includes SELECT, FROM and WHERE, others include JOIN, DISTINCT, ORDER BY etc. One can also CREATE tables or views using SQL commands. Different query command run on the database can be viewed here.

Back to Top