1 / 11

Creating Databases for Web Applications

Learn how to create databases for web applications and implement left join operations in this midterm review. Get motivated with examples and confirm teams for enhancement projects.

rosalez
Download Presentation

Creating Databases for Web Applications

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Creating Databases for Web Applications Go over midterm. Left join: motivation, example. Classwork/Homework: Confirm teams for enhancement projects. Make proposal. Do not track watching.

  2. Join • Making a new table out of 2 or more tables. • Make new record by combining fields in 2 (or more) records based on the ON condition. • Generally, this is matching primary key with a foreign key. • Left Join: make the new table the same way BUT if no matches for a record in the “left” table (the one mentioned first), extend the record with NULL values. • Right Join: same, but adding one more record for each unmatched record in the “right” table (the one mentioned second)

  3. Sample database • Assume 2 tablesSongssong_idsong_namesong_artist could be groupAwardsaward_idaward_namesong_id could have another nameaward_date

  4. Join examples. Produce list of songs with any awards wonSELECT s.song_name, a.award_name FROM songs as s JOIN awards as a ON s.song_id = a.song_id • If a song has not won any awards, it does not appear on the resultset produced by this query. SELECT s.song_name, a.award_name FROM songs as s LEFT JOIN awards as a ON s.song_id=a.song_id • All songs will be on this list. Some may have more than 1 row. If a song won just one award, it will have exactly one row. • Any that did not win any awards, will be present in exactly one row, with a NULL value for the a.awards_name. • The php program can detect the NULL value and produce a table with some indication, such as a zero, for the songs with only the row with NULL values.

  5. Conditions • WHERE specifies a condition on each individual records. Only records satisfying the condition will be included. • ON specifies a condition relating the two tables. Records will be joined together only if condition is met, unless it is a LEFT or RIGHT JOIN. • HAVING is a condition on a GROUP. Example: if records are groups and the requested fields include a COUNT(*) as c, then the HAVING condition can refer to c.

  6. DISTINCT, GROUP BY, ORDER BY • DISTINCT returns a resultset with the distinct values for a field • GROUP BY combines (aggregates) according to a field. It generally is used with one or more of the aggregate functions: COUNT, SUM, AVG. • COUNT(*) calculates number of rows. COUNT(fieldname) does NOT count any null values. • ORDER BY does not combine, but puts rows in order. • Standard order is low to high for numbers, alphabetical for strings. Add DESC to reverse this. • ORDER BY fieldname will put rows with the same fieldname together, but DOES NOT aggregate any rows. • It does make sense to use GROUP BY and ORDER BY together.

  7. Example (made up data) Previous example • SELECT s.song_name, a.award_name FROM songs as s LEFT JOIN awards as a ON s.song_id=a.song_id ”Hello” “Best Album” “Hello” “Best Record” “Lemonade” null • SELECT s.song_name, COUNT(a.award_name) FROM songs as s LEFT JOIN awards as a ON s.song_id=a.song_id GROUP BY s.song_name “Hello” 2“Lemonade” 0 • SELECT s.song_name, COUNT(*) FROM songs as s LEFT JOIN awards as a ON s.song_id=a.song_id GROUP BY s.song_name “Hello” 2“Lemonade” 1

  8. Hints • Single table or more than one table. • More than one, need JOIN and an ON condition • Do we need records without matches, if yes, use LEFT JOIN or RIGHT JOIN or FULL JOIN • Are there conditions on individual records: use WHERE • Are we combining (aggregating) records: need one or more of the aggregate functions (COUNT, AVG, SUM) and need GROUP BY • Do we want to put records in order: use ORDER. • Not standard order, add DESC • Are there conditions on the groups? Use HAVING

  9. Pop quiz • Give the SELECT query for all songs winning awards since January 1, 2015. Return the song_name, award_name, date. Order by date, oldest first. • You may look up or take a guess on how to handle dates. Give the online source. • Give the SELECT query for any song that has won at least 2 awards since January 1, 2015, giving the count of awards won. Order by number of awards, highest number first.

  10. Do not track • You don’t have to give any information, but it is more interesting if you do. • Consider erasing cookies (any cached information) after doing this and periodically. • If given a choice on permitting geolocation, consider saying no OR just for the one time, each time. • Check settings for these permissions. • Note: you can “white list” a specific site.

  11. Classwork / Homework • Finish the do_not_track watching and make posting. • You can comment on other postings. • Study sources, especially on SELECT. • Get in team and make proposal. • Look at the proposals already made. • Look at the sample php projects. • Work on enhancement project.

More Related