Computer Science BS Journal (CST363) : Week 2

 Class: CST363-30_2253 ( Intro to Database Systems)

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.

English Sentence:

Imagine you are working with a database that stores information about employees and their salary performance. You may want to find the employees who are earning more than the average salary in their department. In this case, you would join the employee table with the salary data table using the salary amount, not the keys, in order to compare each employee's salary with the department's average.

Query: 

SELECT e.employee_name, e.department_id, s.salary

FROM employees e

JOIN salaries s ON e.salary > (SELECT AVG(salary) FROM salaries WHERE department_id = e.department_id)

Explanation:

• In this query, the employees table is being joined with the salaries table, but not on primary or foreign keys. Instead, the join is based on the condition that an employee's salary must be greater than the average salary for their department.

• This type of join is useful when comparing attributes or performing calculations (like averages) that go beyond simple equality between keys

1. 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?

I think SQL is a very powerful language, especially when it comes to handling large sets of data in relational databases. It allows for querying, updating, and manipulating data in a structured and relatively straightforward manner. The syntax is generally clear, and for most standard operations (like selecting, filtering, and joining data), it’s easy to learn and apply.

The most challenging part of translating an English question to SQL, in my opinion, is dealing with complex relationships between tables and crafting efficient queries for such scenarios