Post your solutions to the Nov 13 In-Class Exercise to this thread.

Best,

Chris

SELECT Price, Model, Make FROM Models, Products WHERE modelid = productsid;

SELECT Price, Model, Make FROM (Models JOIN Products ON modelid = productid);

StarsIn(actor, movie, date) Stars(name, address)

1.) SELECT * FROM StarsIn, Stars WHERE name=actor

2.) SELECT * FROM StarsIn JOIN Stars ON name=actor

The JOIN ON syntax is arguably more readable since it's more explicit given that we have to name the two tables that we will be performing the joins on. This means that after the ON keyword, only columns belonging to the tables can be referenced

Relations: R(A, B, C, D, E) and S(D, E, F, G, H)

1. SELECT A, D, E, G FROM R, S WHERE A>100;
2. SELECT A, D, E, G FROM R INNER JOIN S ON S.E = R.E AND S.D = R.D WHERE A> 100;

Both statements do the same thing, but query 2 is more explicit than query 1.

SELECT name FROM Courses,Professors WHERE name='Smith'

SELECT name FROM Courses FULL OUTER JOIN Professors ON name = Professors.name AND = name = Courses.pName

It is better to use a join, because it is unclear which 'name' should be used. If the Course table has a Course name called "name", and Professor table has a Professor name called "name" then it would be confusing unless the two tables are joined properly with the right columns.

SELECT *

FROM PC, Product

WHERE PC.model = Product.model

SELECT *

FROM PC NATURAL JOIN Product ON PC.model = Product.model

JOIN ON is more clear since it explicitly says JOIN between 2 tables. It provides more options such as left and right join. Additionally, it can handle null input

Tables: Product(maker, model, type) PC(model, speed, ram, hd, price) Laptop(modelNumber, speed, ram, hd, screen, price) Printer(model, color, type, price)

1.) Select * from Laptop, product where laptop.modelNumber= product.model 2.) Select * from Laptop NATURAL JOIN Product on modelNumer= model

The Join computes less computation on the cartesian products which the first one does

Consider the following schema: patients(id, first_name, last_name, address, dob, primary_insurance, primary_care); procedures(name, department, OR_room, timestamp_start, timestamp_end, patient_id, main_surgeon_name, department);

What patients had a surgery with Dr. Pollett after January 1, 2004

1. SELECT first_name, last_name

FROM patients, procedures WHERE patients.id = procedures.patient_id and procedures.main_surgeon_name = 'Pollett' and procedure.timestamp_start > 01-01-2004:0000;

1. SELECT first_name, last_name

FROM (patients JOIN procedures ON id = patient_id) Where main_surgeon_name = 'Pollett' and timestamp_start > 01-01-2004:0000;

Querry 2 is more readable (you clearly see at the second line what had to be common between the two tables) and will avoid creating a huge temporary table with a lot of unnecessary data than creating a cross product of the two tables(providing that the engine does not optimize the query).

SELECT * FROM Actors, Novelist WHERE Actors.name = Novelist.name;

SELECT * FROM (Actors JOIN Novelist ON Actors.name = Novelist.name);

The 2nd query is better because it explicitly shows the operations we are performing on the relations. The 1st query implies that we compute a joins but the 2nd query explicitly shows that we make making a join operation on the given conditions

Singer(name, address, DOB, genre)

Band(name, SingerName, address, studio)

1. SELECT name, address FROM Singer, Band WHERE name = SingerName AND address = address;
2. SELECT name, address FROM Singer NATURAL JOIN Band ON name.Singer = SingerName.band AND address.Singer = address.Band;

The two Sql query will yield the same result, however, number one is better because the query command is simpler.