CST 363 – Week 2 Learning Journal

by

Week 2 Prompt

SQL has the flexibility to join tables on any column(s) using any predicate (=, >, < ). As long the column(s) are type compatible. 98% of the time the join uses primary key of one table, foreign key of the other table and equal predicate. Think of example where joining on something other than keys would be needed. Write the query both as English sentence and SQL. If you can’t think of your own example, search the internet for an example.

I found an example on w3resource where two tables are joined using the WHERE clause in SQL. This is needed in the example when a business needs a table that shows the date of a job from a table that lists all dispatches, and the amount of money made from that job from an orders table. The SQL code provided:

SELECT a.des_num,a.des_date,
SUM(b.ord_amount)
FROM despatch a, orders b
WHERE a.ord_amount=b.ord_amount
GROUP BY a.des_num,a.des_date;

In plain English, this could be said “Get the date and dispatch number, and the sum of the order amount from the dispatch and orders tables, where the order amount in the dispatch tables equals the order amount in the order table, and sort them by date and order number”.

Leave a Comment