SQL Commands Flashcards
This is a list of commands and Syntax (38 cards)
SELECT
The SELECT statement is used to select data from a database.
SQL SELECT Syntax
SELECT column_name,column_name
FROM table_name;
—
and
—
SELECT * FROM table_name;
—
DISTINCT
In a table, a column may contain many duplicate values; and sometimes you only want to list the different (distinct) values.
SQL SELECT DISTINCT Syntax
SELECT DISTINCT column_name,column_name
FROM table_name;
—
WHERE
SQL WHERE Syntax
SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;
—
AND, OR
The AND operator displays a record if both the first condition AND the second condition are true.
The OR operator displays a record if either the first condition OR the second condition is true.
Example
—
SELECT * FROM Customers
WHERE Country=’Germany’
AND City=’Berlin’;
—
ORDER BY
The ORDER BY keyword is used to sort the result-set by one or more columns.
SQL ORDER BY Syntax
SELECT column_name, column_name
FROM table_name
ORDER BY column_name ASC|DESC, column_name ASC|DESC;
—
Wild Card
In SQL, wildcard characters are used with the SQL LIKE operator.
SQL wildcards are used to search for data within a table.
With SQL, the wildcards are:
%
A substitute for zero or more characters
_
A substitute for a single character
[charlist]
Sets and ranges of characters to match
[^charlist]
or
[!charlist]
Matches only a character NOT specified within the brackets
Example
SELECT * FROM Customers
WHERE City LIKE ‘ber%’;
Example
SELECT * FROM Customers
WHERE City LIKE ‘%es%’;Example
SELECT * FROM Customers
WHERE City LIKE ‘_erlin’;
IN
The IN operator allows you to specify multiple values in a WHERE clause.
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,…);
—
Alias
SQL aliases are used to give a database table, or a column in a table, a temporary name.
Basically aliases are created to make column names more readable.
SQL Alias Syntax for Columns
SELECT column_name AS alias_name
FROM table_name;
SQL Alias Syntax for Tables
SELECT column_name(s)
FROM table_name AS alias_name;
INNER JOIN
The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.
SQL INNER JOIN Syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
or:
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
LEFT JOIN
The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.
SQL LEFT JOIN Syntax
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
or:
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;
RIGHT JOIN
The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.
SQL RIGHT JOIN Syntax
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
or:
SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;
full JOIN
The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2).
The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.
SQL FULL OUTER JOIN Syntax
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;
UNION
The UNION operator is used to combine the result-set of two or more SELECT statements.
Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.
SQL UNION Syntax
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
Note: The UNION operator selects only distinct values by default. To allow duplicate values, use the ALL keyword with UNION.
SQL UNION ALL Syntax
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
HAVING
The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.
HAVING Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
LIMIT
The LIMIT clause is used to specify the number of records to return.
The LIMIT clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.
LIMIT Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;
CASE
The CASE statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
If there is no ELSE part and no conditions are true, it returns NULL.
CASE Syntax
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;
LENGTH
The LENGTH() function returns the length of a string (in bytes).
Syntax
LENGTH(string)
UPPER
The UPPER() function converts a string to upper-case.
Note: Also look at the LOWER() function.
Syntax
UPPER(text)
LOWER
Convert the text to lower-case:
SELECT LOWER(‘SQL Tutorial is FUN!’);
TRIM
Remove leading and trailing spaces from a string:
SELECT TRIM(‘ SQL Tutorial! ‘) AS TrimmedString;
SELECT LTRIM(‘ SQL Turorial! ‘) AS LeftTrimmedString;
SELECT RTRIM(‘ SQL Turorial! ‘) AS RightTrimmedString;
RIGHT
Extract 3 characters from a string (starting from right):
SELECT RIGHT(‘SQL Tutorial’, 3) AS ExtractString;
LEFT
Extract 3 characters from a string (starting from left):
SELECT LEFT(‘SQL Tutorial’, 3) AS ExtractString;
SUBSTRING
Extract 3 characters from a string, starting in position 1:
SELECT SUBSTRING(‘SQL Tutorial’, 1, 3) AS ExtractString;
REPLACE
Replace “T” with “M”:
SELECT REPLACE(‘SQL Tutorial’, ‘T’, ‘M’);
