Week 9

1. Week 9 MySQLJoins

2. Differences between MSQL and Access • No Whitespace In Field Names • No Bracket Notation • More Functions and Keywords adding powerful features. • All types of joins discussed earlier this semester are allowed

3. Types of Joins • Outer Join • Inner Join • Theta Join • Equi-Join • Natural Join • Cross Join • Left Join • Right Join • Full Join

4. Natural Join • This is an equi-join with an implicit join-predicate. The join-predicate is automatically created by comparing all fields with the same field name. • In the resulting table, we remove 1 duplicate column. • It is generally discouraged because of ambiguity on what the join-predicate is.SELECT * • FROM Table NATURAL JOIN Table;

5. Cross Join A cross join is a special inner join that acts as a direct Cartesian product between 2 tables. It can be thought of as a inner product where the join-predicate is always true. SELECT *FROM Table CROSS JOIN Table;

6. Full Join • A Full Join will output records that satisfy the join predicate, similar to an inner join. • However every record from the both tables will appear at least once. When there is no matching record NULL values are supplied. • SELECT * • FROM Table FULL JOIN Table • ON JOIN_PREDICATE;

7. Entity Relationship Diagrams • Entity Relationship (ER) Diagrams are abstract representation of data. • For simplicity we can say they are “web diagrams” used to describe a database. • ER Diagrams are used to help visualize tables and relationships between tables before creating them. • They allow a person to create well-formed database by minimizing the number of One to One and Many to Many relationships.

8. Problems minimized by having well-formed tables • Inconsistency Between Tables • Redundant Information • Isolated Tables

9. Issues With Databases • We can reduce the amount of table altering needed by diagramming the table using an ER Diagram before writing any code.

10. Problems with Malformed Tables • Inconsistency • With this structure we now need to keep 2 tables in check.

11. Removing Inconsistencies We can fix this by removing redundant columns, and adding only 1 linking field so the tables can join. We take a key from one table and place it in the other table.When choosing which table it is up to the database designer, but consider which makes the most sense. In this case • Adding ProductID to the Sales table or • Adding SaleID to the Products table

12. Redundant Information At this point we can join these two tables on ProductID should we need to access the actual Product Information Because we only have one source for Product Information now, it can not be inconsistent. We also save space since we have less redundant information.

13. Isolated Tables • Isolated tables provide little useful information because they cannot be inner joined with anything. • A general trick to fix an isolated table is to try and find a junction table, basically a common ground that will connect two tables in your database

14. Wage DECIMAL(5,2)‏ SSN CHAR(10)‏ Employees Name VARCHAR(100)‏ Gender CHAR(6)‏ ProductID INT SSN CHAR(10)‏ Sales Customers Each Customer has many sales. Name VARCHAR(100)‏ Age INT SaleID INT SSN CHAR(10)‏ Qty INT

15. Normalizing • The concept of having a well-formed table is a casual generalization for the mathematical term of normalizing tables. • For our purposes you may think of the two as synonymous

16. String Comparisons • One powerful feature MySQL has over Microsoft Access is Regular Expressions. • The power to find records based on only parts of a string. • Utilized as an operator in the WHERE clause

17. LIKE Begins With • SELECT * • FROM L13Students • WHERE FirstName LIKE 'G%‘; • The % sign means • 0 or more of any character combination. • This will find anyone in the students table whose FirstName begins with G • Ex. • Gary GeofrygGGG G g

18. Ends With • SELECT * • FROM L13Students • WHERE FirstName LIKE '%y'; • This will find anyone in the students table whose FirstName ends with y • YochehyClarencyyyyyy Y

19. Case Sensitive? • Like should be noted to be case insensitive by default. • To make it case sensitive add the keyword BINARY • Ex. • SELECT * • FROM L12Students • WHERE FirstName LIKE BINARY 'G%‘;

20. Contains • SELECT * • FROM L13Students • WHERE FirstName LIKE '%en%'; • This will find anyone in the students table whose FirstName contains an 'en' • Ex. • Ben Been BeeNeBenen En • The following will not be found: • Bean Bane

21. Match with exactly 1 character • Another useful wild card is the “match with exactly one “character, the underscore. • SELECT * • FROM L12Students • WHERE FirstName LIKE 'e_n'; • Ex • eon e8n eOn • The following will not be found: • en

22. Escape Characters • We may need to search a string for something that ends with a percent sign or contains an underscore. • Since they are special characters we need to escape them using a backslash. \ (above enter key) • The character following the backslash will not be considered a wild card.

23. Examples: • Ends with a percent sign • SELECT * FROM table WHERE field LIKE '%\%'; • Contains an underscore • SELECT * FROM table WHERE field LIKE '%\_%';

24. Testing Regular Expressions • We can use the syntax • SELECT 'teststring' LIKE 'regexp'; • to compare two strings. • This will return either 1 or 0 which represent true or false • Example • mysql> SELECT 'aaa' LIKE 'aaa'; • 1 • mysql> SELECT 'aaa' LIKE 'a_a'; • 1 • mysql> SELECT 'baa' LIKE 'a_a'; • 0

25. Class Exercises • Find all customer whose first name that begin with a 'V' True: v V Vegas False: favavV • Find all employees whose last name ends with a 'W' True: w WPow wow False: wav Wwf • Find all employees whose SSN has a '59' in it. True: 59 659- -59- False: 569 95 • Find all employees whose first name contains a 'g' followed eventually by a 'd'. True: gd greed GED False: geeedee DG • Find all products that are 4 characters long, begin with 'b' and end with 'r' True: beer bear blur False: bar rhoBBazar • Find all files that contain a '_' followed eventually by a percent sign. True: _% _50% f_t%t False: f%_af%b_

26. REGEXP • A More Powerful Version of LIKE is also available in MySQL, called REGEXP. • However it is syntax heavy • The LIKE keyword can do most of the simple regular expressions. • More information On Regular Expressions • http://dev.mysql.com/doc/refman/5.0/en/regexp.html