Types of Joins
We learned how to do a basic join in the last lesson. Surprise! There are different kinds of Joins. The one you just learned is called an Inner Join. Let’s take a look at the four different kinds and understand how they work. Inner Join This returns data that has matching values in BOTH tables.…
Read MoreJoining More Than 2 Tables
Going back to our original set of questions we asked: Looking at our members database what if we want to see all students and their phone numbers? Or all students and their classes? Or all students taking a single class? When we learned about Joins – we only answered the first one. So, how do…
Read MoreJoins
A JOIN is a way to connect two or more database tables. Let’s start of with the syntax of a basic JOIN. SELECT table1.column1, table1.column2, table2.column1, table2.column2… FROM table_name1 JOIN table_name2 ON condition; Let’s take a look at what this does – Line 1: selects the columns to pull data from. However, you will notice…
Read MoreAliases
Sounds cool, doesn’t it? Like we are going to spy on our database? What it is just giving a table or column a shorter name or alias. Table aliases come in very useful when you are writing queries that refer to multiple tables. Column aliases are used to make column names more readable. Aliases are…
Read MoreAdding Records
You can add or create a new record using the INSERT INTO statement – there are two ways to write it. Adding All Fields The first way assumes that you are adding values for all the fields in your table, so you do not need to list the field names. But, it is very important…
Read MoreGROUP BY & HAVING
GROUP BY is used in as part of the WHERE clause when you are using an aggregate function (COUNT, SUM, MAX, MIN, AVG) and you are using multiple columns. It allows you to perform operations by groups of data. The basic syntax is as follows: SELECT column1, column2,… FROM table_name WHERE condition GROUP BY column_name(s)…
Read MoreHomework: Sorting Data
Go into phpMyAdmin! Open up the states table up and look at the structure and the data. Then go to the SQL tab while that table is selected. Use this as your work space to answer the questions in the quiz!
Read MoreSorting Data
Want to be able to sort your data by a particular field or multiple fields? Then the ORDER BY keyword is your tool. It allows you to sort by one or more columns in ascending or descending order. The basic syntax is as follows: SELECT column1, column2,… FROM table_name ORDER BY condition; Simple Sort Example…
Read MoreWHERE Variations
LIKE & Wildcards Combined these two allow for some powerful searches. Let’s learn about each one. LIKE LIKE is used in WHERE clause when you want to search for a partial match of a string to all or part of a column that is either TEXT or VARCHAR. The basic syntax is as follows: SELECT…
Read MoreSELECT DISTINCT
We will be using the members table for the examples in this topic. Here it is for reference: SELECT DISTINCT This allows you to select only records from the table that have distinct or unique values. The basic syntax is as follows: SELECT DISTINCT column1, column2,… FROM table_name; For example, if you want to find…
Read More