CST 363 - Introduction to Database - 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 an 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. An example for this is when a company wants to analyze its employee's performance relative to their start dates. The query in English could be something like: "Find each employee's total sales and compare it to the average sales of employees who started in the same year." The query will display the employee's name, their total sales, and the average sales of their start-year cohort. Let's assume we have two tables, employees and sales. The employee's table contains employee_id, name, and start_date. The sales table contains the employee_id, sale_amount and sale_date. So the query could be written as: 
   
   
   
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? What I think about SQL is that it is a useful and powerful tool to learn as a computer science student. I know that in the future I will probably be working with data and programming applications based on data, so a strong knowledge about it is so beneficial. The fundamentals of SQL are easy to learn, but it gets harder once you start using subqueries and predicates. Translating from an English question to SQL is not easy and most of the time I have to think about how to write the query and rewrite the query one more time in order to get the result that I want. The most challenging thing for me is when I have to merge (use joins) multiple tables and use predicates in order to find the answer.