Order Entry Statements (SQL Part 2)

March 20, 2022

For the second part of the project, I again used the Customer, Order, and Employee tables found in Database design, application development and administration (pgs. 73-74). The tables were created in the Relational Data Model entry; therefore, this project focuses on writing SQL statements to insert the data and then view the data using the GUI and psql shell.  Below are screenshots of the process, along with key outcomes.

Figure 1

SQL Statements and Data for Customer Table

Figure 2

SQL Statements and Data for Employee Table

Figure 3

SQL Statements and Data for Order Table.

Figure 4

Queried Tables from psql Shell

Key Outcomes

There were two challenges I encountered in this project. The first one was with regard to the integer element in the Customer table. The integers were creating errors due to the data type, which was set as small integer rather than numeric. Once this data type was changed, there were no issues. The second challenge came in the Ordertbl table with regard to null values.  I repeatedly received an error given the lack of data in those couple of rows without an associated employee ID.  It was because I was not typing in the NULL value, which can be used in any field that is not marked as forbidden (Juba & Volkhov, 2017). Once NULL was added, there were no further issues.  Other than those, this was a straightforward project, and the pgAdmin GUI made it easier to write and view the SQL statements alongside the tables. 

References

Juba, S., & Volkov, A. (2017). Learning PostgreSQL 10 (2nd ed.). Packt Publishing

Mannino, M. V. (2019). Database design, application development and administration, 7e. http://platform.virdocs.com/r/s/0/doc/592088/sp/176800379/mi/566374934?cfi=%2F4%2F4