Databases and Data Modeling: SQL Basics

• Week 13 introduces database and data modeling concepts.

SQL Basics

• SQL (Structured Query Language) is rooted in relational algebra.
• Queries employ combinations of relational algebra operations.

E-R Diagram

• E-R Diagram example includes SONG, ARTIST, and CD entities
• SONG contains song_id, song_title, length, and artist_id
• ARTIST contains artist_id and artist
• CD contains cd_id, cd_title
• CD_MAKEUP contains cd_id and song_id
• A unique constraint is shown for artist->artist_id.
• Functional dependencies (FDs) are defined for each entity.

SQL JOIN Shortcuts

• "INNER" and "OUTER" are optional keywords in SQL JOIN syntax.
• Outer joins were introduced with SQL92 and necessitate the "ON" clause.
• Inner joins existed since SQL89 and don't need the "ON" clause.

SQL 89 vs SQL 92: INNER JOIN

• SQL 89/92 equivalent statements use INNER JOIN.
• SQL 92 example: SELECT artist.artist, song.song_title FROM artist INNER JOIN song ON artist.artist_id = song.artist_id;
• SQL 89 equivalent: SELECT artist.artist, song.song_title FROM artist, song WHERE artist.artist_id = song.artist_id;

SQL 92 OUTER JOIN

• The SQL 92 equivalent statement uses OUTER JOIN: SELECT artist.artist, song.song_title FROM song RIGHT OUTER JOIN artist ON song.artist_id = artist.artist_id;
• No SQL 89 equivalent exists for OUTER JOINS.

SQL Shortcuts & Table Aliases

• Most DBMS support table aliases (SQL 89 or SQL 92),
• Example: SELECT art.artist, s.song_title FROM song as s RIGHT JOIN artist art ON s.artist_id = art.artist_id;
• USING() can be employed when joining tables with identically named attributes (SQL 92 only).
• Example: SELECT art.artist, s.song_title FROM song as s RIGHT JOIN artist art USING(artist_id);

Multi-Table Joins

• SQL allows joining as many tables as necessary.
• The physical E-R diagram should be referenced while writing the query.
• Tables are joined two at a time, following the established path in the diagram.
• It is useful to consider how the structure of which table is added.
• Example: Listing the Artist name and CD Titles the artist is included in

Multi-Table Join Example (Artist Name and CD Titles)

• Determine attributes and "path" start: SELECT artist.artist, cd.cd_title FROM artist
• Join the next table in the path: SELECT artist.artist, cd.cd_title FROM artist INNER JOIN Songs ON Artists.ArtistID = Songs. ArtistID
• Join further tables in the path: SELECT artist.artist, cd.cd_title FROM (artist INNER JOIN song ON artist.artist_id = song.artist_id) INNER JOIN cd_makeup ON song.song_id = cd_makeup.song_id
• Final join to complete the path: SELECT artist.artist, cd.cd_title FROM ((artist INNER JOIN song ON artist.artist_id = song.artist_id) INNER JOIN cd_makeup ON song.song_id = cd_makeup.song_id) INNER JOIN cd ON cd_makeup.cd_id=cd.cd_id;

SQL 89 vs. SQL 92 - JOINs

• SQL 92 fully supports all JOIN types.
• The SQL 89 standard only supports INNER JOIN

Beyond JOINs

• It can show the artist and CD title for CDs that include the artist ' Smash Mouth', but require slightly different syntax for SQL92 and SQL89

SQL Functions

• SQL functions avoid the need to reinvent the wheel.
• Functions exist, are easy to use, and efficient.
• Functions are SQL features and operate on single rows or sets of data, enabling data modification and statistical analysis.

Single-Row Functions

• Character functions include: LOWER(), UPPER(), LENGTH(), INSTR(), SUBSTR(), CONCAT()
• Numeric functions: ROUND(), TRUNCATE(), MOD()

LOWER/UPPER Functions

• LOWER(s): Returns string s in all lowercase.
• UPPER(s): Returns string s in all uppercase.
• Usefulness lies in case-insensitive string operations.
• MySQL, although not case sensitive, recommends using these functions when case should not impact results.

LENGTH Function

• LENGTH(s): Returns the number of characters in string s.
• Very useful when doing string comparisons

INSTR Function

• INSTR(s1,s2) (or LOCATE(s2,s1) in MySQL) checks if string s2 is in string s1.
• Returns the start position of the first match; returns 0 if s2 is not in s1.

SUBSTR Function

• SUBSTR(s,n,m): Returns a substring of s starting at position n and of length m.

CONCAT Function

• CONCAT(s1,s2): Appends string s2 to string s1.
• MySQL allows CONCAT overloads

Concatenation Operator

• || is a SQL concatenation operator.
• Not supported in MySQL

ROUND/TRUNCATE Functions

• ROUND(n,m): Rounds number n to m decimal places.
• TRUNCATE(n,m): Truncates number n after m decimal places.

MOD Function

• MOD(n,m): Returns the remainder when n is divided by m.

Multi-Row Functions

• Multi-row functions are also known as Aggregate Functions
• These functions work a column of data and return a single result
• Includes AVG(), COUNT(), MAX(), MIN(), and SUM()

AVG Function

• AVG(column): Returns the average value in the column.
• Null values are not included in the average calculation.
• AVG(DISTINCT column): Returns the average of distinct values in the column.

COUNT Function

• COUNT(*): Returns the total number of rows in a table.
• COUNT(column): Returns the number of rows with non-NULL values in the specified column.
• COUNT(DISTINCT column): Returns the number of rows with unique, non-NULL values in the column.

MAX/MIN Functions

• MAX(column): Returns the maximum non-null value in the column.
• MIN(column): Returns the minimum non-null value in the column.

SUM Function

• SUM(column): Totals all values in the column.
• SUM(DISTINCT column): Sums only the distinct values in the column.

Counting Songs

• Shows how to use Aggregate Functions to calculate information about the data.
• Example is counting the number of songs with SELECT COUNT(song_id) AS 'Number of Songs' FROM cd_makeup;

GROUP BY Clause

• Provides the ability to show the results of an aggregate function, grouped by a specific field.
• A GROUP BY clause is used when attributes outside of an aggregate function call are part of the SELECT clause.

HAVING Clause

• The HAVING clause places restrictions according to the results of aggregate functions.
• It is used instead of WHERE to limit results after GROUP BY is performed.

WHERE versus HAVING Clause

• A statement can have both a WHERE clause and a HAVING clause
• The WHERE clause applies to raw, ungrouped data and is evaluated before a GROUP BY clause
• The HAVING clause applies to grouped data and executes after a GROUP BY

ORDER BY Clause

• The ORDER BY clause sorts the rows returned
• Order can be ascending (default) or descending.
• Multiple levels of sorting are possible.

SQL Review

• Expanded format of the SELECT statement:
• SELECT attribute(s) FROM tablename(s) WHERE condition(s) GROUP BY groupList HAVING haveCondition ORDER BY orderList;
• WHERE, GROUP BY, HAVING, and ORDER BY are all optional

Highly Abstract Tables

• Consider the song table from the cd_catalog database
• Includes SONG(song id, song title, length, artist id) and additional song attributes (tempo and mood)
• Highly abstract tables collapse common fields (Tempo, Mood, and Genre) into a tag/value relationship
• Create another, weak entity, that has an "attribute" field and a value field
• Benefit
• Very flexible
• Easy to handle unforeseen information
• Downside
• Queries get difficult