Yioop - PHP Search Engine

Nov 13 In-Class Exercise Thread.

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

Best,

Chris

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

-- Nov 13 In-Class Exercise Thread

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

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

(Edited: 2019-11-13)

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

-- Nov 13 In-Class Exercise Thread

 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

(Edited: 2019-11-13)

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

-- Nov 13 In-Class Exercise Thread

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.

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.

-- Nov 13 In-Class Exercise Thread

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.

(Edited: 2019-11-13)

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.

-- Nov 13 In-Class Exercise Thread

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

(Edited: 2019-11-13)

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

-- Nov 13 In-Class Exercise Thread

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

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

-- Nov 13 In-Class Exercise Thread

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;

 2) 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).

(Edited: 2019-11-13)

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; 2) 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).

-- Nov 13 In-Class Exercise Thread

 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

(Edited: 2019-11-13)

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

-- Nov 13 In-Class Exercise Thread

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.

(Edited: 2019-11-13)

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.