Week 2 – CST– 363 Introduction 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 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.

 (Found example in stackoverflow).

In SQL you would need to use something other than keys when you need to join two tables that do not have keys in common. An example would be two tables that have one column each. There would be Table A with column CustomerId and TableB with RewardCode. The query would be, join TableA with TableB.

Select tA.CustomerId, tB.RewardCode

From

(Select customerId,

     ROW_NUMBER() OVER (ORDER BY CustomerId) as TableA_RowNumber

From TableA) tA

Inner join

(Select RewardCode,

     ROW_NUMBER() OVER (ORDER BY RewardCode) as TableB_RowNumber

From TableB) tB

ON TableA_RowNumber = TableB_RowNumber;

 This query will join both TableA and TableB to create a result table where every row number in TableA will be joined with the same row number in TableB.

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?

 

SQL is a language that every professional software developer should have an advanced understanding of. It is not easy to learn or use because of the relational rules and constraints that are enforced by the database system. These constraints are meant to maintain data integrity and avoid anomalies that may corrupt the database. When translating queries from English to SQL, I find it challenging to determine which kind of joins need to be used between tables in questions where you must join multiple tables. I also find it difficult when the questions ask to show null or zero for values. I understand now that using a left outer join keeps all the values from the left table and would give you a NULL value for that column. You would then have to use GROUP BY and COUNT() for that column in order to get a zero value.