SQL IN
The SQL IN clause allows you to specify discrete values in your SQL WHERE search criteria.
THE SQL IN syntax looks like this:
SELECT Column1, Column2, Column3, …
FROM Table1
WHERE Column1 IN (Valu1, Value2, …)
The SQL UPDATE general syntax looks like this:
UPDATE Table1
SET Column1 = Value1, Column2 = Value2
WHERE Some_Column = Some_Value
The SQL UPDATE clause changes the data in already existing database row(s) and usually we need to add a conditional SQL WHERE clause to our SQL UPDATE statement in order to specify which row(s) we intend to update.
The SQL INSERT INTO syntax has 2 main forms and the result of either of them is adding a new row into the database table.
The first syntax form of the INSERT INTO SQL clause doesn’t specify the column names where the data will be inserted, but just their values:
INSERT INTO Table1
VALUES (value1, value2, value3…)
The second form of the SQL INSERT INTO command, specifies both the columns and the values to be inserted in them:
INSERT INTO Table1 (Column1, Column2, Column3…)
VALUES (Value1, Value2, Value3…)
As you might already have guessed, the number of the columns in the second INSERT INTO syntax form must match the number of values into the SQL statement, otherwise you will get an error.
If we want to insert a new row into our Customers table, we are going to use one of the following 2 SQL statements:
INSERT INTO Customers
VALUES (‘Peter’, ‘Hunt’, ‘peter.hunt@tgmail.net’, ‘1/1/1974’, ‘626 888-8888’)
INSERT INTO Customers (FirstName, LastName, Email, DOB, Phone)
VALUES (‘Peter’, ‘Hunt’, ‘peter.hunt@tgmail.net’, ‘1/1/1974’, ‘626 888-8888’)
The SQL LIKE clause is very useful when you want to specify a search condition within your SQL WHERE clause, based on a part of a column contents. For example if you want to select all customers having FirstName starting with ‘J’ you need to use the following SQL statement:
SELECT *
FROM Customers
WHERE FirstName LIKE ‘J%’
If you want to select all Customers with phone numbers starting with ‘416’ you will use this SQL expression:
SELECT *
FROM Customers
WHERE Phone LIKE ‘416%’
The ‘%’ is a so called wildcard character and represents any string in our pattern.
You can put the wildcard anywhere in the string following the SQL LIKE clause and you can put as many wildcards as you like too.
Note that different databases use different characters as wildcard characters, for example ‘%’ is a wildcard character for MS SQL Server representing any string, and ‘*’ is the corresponding wildcard character used in MS Access.
Another wildcard character is ‘_’ representing any single character.
The ‘[]’ specifies a range of characters. Have a look at the following SQL statement:
SELECT *
FROM Customers
WHERE Phone LIKE ‘[4-6]_6%’
This SQL expression will return all customers satisfying the following conditions:
• The Phone column starts with a digit between 4 and 6 ([4-6])
• Second character in the Phone column can be anything (_)
• The third character in the Phone column is 6 (6)
• The remainder of the Phone column can be any character string (%)