Week 20 (CST363 - Week 2)

 

Prompt for week 2:

1. SQL has the flexibility to join tables on any column(s) using any predicate (=, >, < ).    Most of the time the join will use equality between a primary and foreign key.   Think of example where joining on something other than keys would be needed.  Write the query both as an English sentence and in SQL.  If you can't think of your own example, search the textbook or internet for an example.
2. What is your opinion of SQL as a language?  Do you think it is easy to learn and use?  When translating from an English question to SQL, what kinds of questions do you find most challenging?

 

 1. Find all customer orders where the order total exceeds the customer’s credit limit.

-- Customers table
create table customers (
  customer_id int primary key,
  name varchar(50),
  credit_limit numeric(10,2)
);

-- Orders table
create table orders (
  order_id int primary key,
  customer_id int,
  order_total numeric(10,2),
  order_date date,
  foreign key (customer_id) references customers(customer_id)
);

select o.order_id, o.customer_id, c.name, o.order_total, c.credit_limit
from orders o
join customers c on o.customer_id = c.customer_id
where o.order_total > c.credit_limit;
 

2. So far I'm really enjoying it. I find it very intuitive so far to use. I find that the translation from English to SQL, and vice versa fairly straight forward. It's starting to get a little harder to read to the naked eye with the sub-queries, but without knowing much about it, it's easy to speculate what the queries and the tables mean. I think the most I've struggled so far is following what the main query is gathering from the sub-queries, and how that will resolve in the data.