Creating Databases

Creating Databases SQL joins. Miscellaneous. Work session. Homework: continue work on projects

SQL join • SELECT statement against a table made up by JOINing tables together on identical fields. • Different types of JOIN: • JOIN (same as INNER JOIN) • LEFT JOIN • RIGHT JOIN • FULL JOIN http://www.w3schools.com/sql/sql_join.asp

SELECT Conditions • WHERE • Sets condition on individual records • with a JOIN • the ON specifying what field to do the JOIN on, generally a foreign key equal to a primary key • After aggregating using GROUP • HAVING sets a condition on grouped data

Conditions • Remember: the single equal sign is the operator for equality! • Other comparisons: >, <, >=, <= • LOGIC: AND, OR, NOT • REGEX for regular expressions • LIKE: another way to specify a pattern

Conditions • Can select using set of values • SELECT * FROM questions WHERE category IN (‘trivia’,’misc’,’silly’) • See also BETWEEN

UPDATE • Can update a single record or a set of records. • UPDATE questions SET text = ‘$ntext’ WHERE ques_id=‘$qid’ • Assumes table names questions with fields text and ques_id and php variables $ntext and $qid set previously

UPDATE • Raise all the prices 10% • UPDATE products SET price=1.10*price • Raise the prices that are over 100 by 5% • UPDATE products SET price=1.05*price WHERE price > 100

Presentation projects • Explain all the SELECT statements, original ones and any you add!

Ideas for enhancing basic projects • ALL projects: • add edit facility • display records and allow changes of fields. • Determine if this requires changing records in another place. • improve looks! • add different types of displays • add fields • add more data, that is, more records • Trivia quiz: allow looser check for correctness • Songs, quiz, book marks: include suggested categories • Origami store: create scripts for displaying all orders • ???

Classwork • Share ideas on enhancements

Classwork / homework • Work on projects

Creating Databases