Previously we looked at using wildcards in conjunction with the SQL LIKE operator. Wildcards allow you to search for data in a defined table. Think of them as text globbing for SQL. The wildcards available include the following:
  1. [list]: Define a ranges of characters for pattern matching
  2. [!charlist]: Matches only a character NOT specified within the brackets
  3. %: Require a single character/object in a pattern
  4. _: Allow any single character in a pattern
In this article, we’ll use the same “Customers” table from our first articles: ID Site Contact Address City Zip Country 1 Krypted Charles Edge my house Minneapolis 55418 US 2 Apple Tim Cook spaceship Cupertino 95014 US 3 Microsoft Satya Nadella campus Redmond 98053 US 4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 5 JAMF Dean Hager Grain Exchange Minneapolis 55410 US The following SQL statement selects all customers with a City starting with “Minne”: SELECT * FROM Customers WHERE City LIKE 'Minne%'; The above SELECT would not locate an object that was just called Minne because the % indicates that something must be there. We could also look for something with nn but with something before and after the pattern: SELECT * FROM Customers WHERE City LIKE '%nn%'; Or to look for something that would contain Minneapoli: SELECT * FROM Customers WHERE City LIKE 'Minneapoli_'; Not that the _ is looking for a single character and that the % is looking for any string in that space. We could also look for a set of objects or omit a set of objects using brackets to define multiple items or a range. For example, let’s say we wanted to look for zip codes 55418 and 55410 SELECT * FROM Customers WHERE Zip LIKE '[55418 55410]%'; Or 55410 through 55419, use a dash to separate the two (in ascending order): SELECT * FROM Customers WHERE Zip LIKE '[55410-55418]%'; Or the ones that don’t match that pattern: SELECT * FROM Customers WHERE Zip LIKE '[!55410-55418]%'; Overall, the globbing/pattern matching options are very basic (think DOS-like syntax) unless you use more complicated functions. But, it’s amazing what kind of stuff you can string together with simple commands, joins, and other tricks, without having to get into functions.

Sometimes you need to write a record into a table in a SQL database. The INSERT INTO statement creates new records in a table and can work in one of two ways. The first form does not specify the column names where the data will be inserted, only their values. When doing so, each value needs to be inserted in the columned order they appear, here the table being the name of the table you’re adding a record into and each value would be replaced with the contents of your value (don’t insert the string ‘value’ into each!): INSERT INTO table VALUES (value,value,value,...); If you don’t have every value to insert, you can also list the columns to insert data in and then include the values in the same order that the columns are listed in. The second form specifies both the column names and the values to insert: INSERT INTO table (column,column,column,...) VALUES (value,value,value,...); Below is a selection from the “Customers” table that I put in the first article. We will use it to run some SQL statements using the INSERT keyword so that we can add data to our little database: ID Site Contact Address City Zip Country 1 Krypted Charles Edge my house Minneapolis 55418 US 2 Apple Tim Cook spaceship Cupertino 95014 US 3 Microsoft Satya Nadella campus Redmond 98053 US 4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US To insert a new row in the “Customers” table, we’ll add a row for Huffington Post with Arianna Huffington as the owner, her address as “The Library, with a city of Los Angeles, a zip of 90077, and a country of the US. INSERT INTO Customers (Site, Contact, Address, City, Zip, Country) VALUES ('Huffington Post','Arianna Huffington','The Library','Los Angeles','90077','US'); The selection from the “Customers” table will now look like this, after the ID record incremented on its own, taking the next available integer: ID Site Contact Address City Zip Country 1 Krypted Charles Edge my house Minneapolis 55418 US 2 Apple Tim Cook spaceship Cupertino 95014 US 3 Microsoft Satya Nadella campus Redmond 98053 US 4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US 6 Huffington Post Arianna Huffington The Library Los Angeles 90077 US As mentioned, you can also Insert Data Only in Specified Columns It is also possible to only insert data in specific columns. The following SQL statement will insert a new row, but only insert data in the Site, Contact, and Country columns: INSERT INTO Customers (Site, Contact, Country) VALUES ('Spotify', 'Daniel Elk', 'SE'); The selection from the “Customers” table will now look like this: ID Site Contact Address City Zip Country 1 Krypted Charles Edge my house Minneapolis 55418 US 2 Apple Tim Cook spaceship Cupertino 95014 US 3 Microsoft Satya Nadella campus Redmond 98053 US 4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US 6 Huffington Post Arianna Huffington The Library Los Angeles 90077 US 7 Spotify Daniel Elk SE Overall, adding rows to SQL tables is really straight forward. I mean, you’re not replacing anything… Yet…

Previously, we looked at the SQL SELECT and WHERE options to obtain output and then constrain what would be displayed in a query. The AND & OR operators are used to add additional logic so you can filter records based on more than one condition. Which is to say to search based on the contents of multiple columns within a single table. AND is used to show information if two different conditions are true and OR is used to show information if either condition is true. Below is a selection from the “Customers” table that showed in our first article an we will use it to run some SQL sorting statements using the ORDER BY keyword: ID Site Contact Address City Zip Country 1 Krypted Charles Edge my house Minneapolis 55418 US 2 Apple Tim Cook spaceship Cupertino 95014 US 3 Microsoft Satya Nadella campus Redmond 98053 US 4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US The following example SQL statement outputs customers from the country “US” AND the city “55418”, in the “Customers” table from above: SELECT * FROM Customers WHERE Country='US' AND Zip='55418'; The following SQL statement selects all customers from the city “Minneapolis” OR the city “Cupertino”, in the “Customers” table: SELECT * FROM Customers WHERE City='Cupertino' OR City='Minneapolis'; As the logic of your searches expands, you can combine AND and OR by nesting logic within parenthesis (who said pre-algebra would be useless?!?!). For example, the following SQL statement selects all customers from the country “US” AND the city must be equal to “Minneapolis” OR “Cupertino”, in the “Customers” table: SELECT * FROM Customers WHERE Country='US' AND (City='Minneapolis' OR City='Cupertino');