Tutorial by Examples

Consider a database with the following two tables. Employees table: IdFNameLNameDeptId1JamesSmith32JohnJohnson4 Departments table: IdName1Sales2Marketing3Finance4IT Simple select statement * is the wildcard character used to select all available columns in a table. When used as a substitute f...

The basic syntax of SELECT with WHERE clause is: SELECT column1, column2, columnN FROM table_name WHERE [condition] The [condition] can be any SQL expression, specified using comparison or logical operators like >, <, =, <>, >=, <=, LIKE, NOT, IN, BETWEEN etc. The following ...

SELECT PhoneNumber, Email, PreferredContact FROM Customers This statement will return the columns PhoneNumber, Email, and PreferredContact from all rows of the Customers table. Also the columns will be returned in the sequence in which they appear in the SELECT clause. The re...

Column aliases are used mainly to shorten code and make column names more readable. Code becomes shorter as long table names and unnecessary identification of columns (e.g., there may be 2 IDs in the table, but only one is used in the statement) can be avoided. Along with table aliases this allows ...

SELECT * FROM Employees ORDER BY LName This statement will return all the columns from the table Employees. IdFNameLNamePhoneNumber2JohnJohnson24681012141JamesSmith12345678903MichaelWilliams1357911131 SELECT * FROM Employees ORDER BY LName DESC Or SELECT * FROM Employees ORDER BY LName ASC...

When a column name matches a reserved keyword, standard SQL requires that you enclose it in double quotation marks: SELECT "ORDER", ID FROM ORDERS Note that it makes the column name case-sensitive. Some DBMSes have proprietary ways of quoting names. For example, SQL Serve...

The SQL 2008 standard defines the FETCH FIRST clause to limit the number of records returned. SELECT Id, ProductName, UnitPrice, Package FROM Product ORDER BY UnitPrice DESC FETCH FIRST 10 ROWS ONLY This standard is only supported in recent versions of some RDMSs. Vendor-specific non-stand...

SELECT e.Fname, e.LName FROM Employees e The Employees table is given the alias 'e' directly after the table name. This helps remove ambiguity in scenarios where multiple tables have the same field name and you need to be specific as to which table you want to return data from. SELECT e.Fname,...

SELECT * FROM table1, table2 SELECT table1.column1, table1.column2, table2.column1 FROM table1, table2 This is called cross product in SQL it is same as cross product in sets These statements return the selected columns from multiple tables in one query....

Average The AVG() aggregate function will return the average of values selected. SELECT AVG(Salary) FROM Employees Aggregate functions can also be combined with the where clause. SELECT AVG(Salary) FROM Employees where DepartmentId = 1 Aggregate functions can also be combined with group by ...

SELECT Name FROM Customers WHERE PhoneNumber IS NULL Selection with nulls take a different syntax. Don't use =, use IS NULL or IS NOT NULL instead.

When results need to have some logic applied 'on the fly' one can use CASE statement to implement it. SELECT CASE WHEN Col1 < 50 THEN 'under' ELSE 'over' END threshold FROM TableName also can be chained SELECT CASE WHEN Col1 < 50 THEN 'under' WHEN Col1 > 50 AND Col1 ...

Sometimes when tables are used mostly (or only) for reads, indexing does not help anymore and every little bit counts, one might use selects without LOCK to improve performance. SQL Server SELECT * FROM TableName WITH (nolock) MySQL SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;...

SELECT DISTINCT ContinentCode FROM Countries; This query will return all DISTINCT (unique, different) values from ContinentCode column from Countries table ContinentCodeOCEUASNAAF SQLFiddle Demo

SELECT * FROM Cars WHERE status IN ( 'Waiting', 'Working' ) This is semantically equivalent to SELECT * FROM Cars WHERE ( status = 'Waiting' OR status = 'Working' ) i.e. value IN ( <value list> ) is a shorthand for disjunction (logical OR).

Counting rows based on a specific column value: SELECT category, COUNT(*) AS item_count FROM item GROUP BY category; Getting average income by department: SELECT department, AVG(income) FROM employees GROUP BY department; The important thing is to select only columns specified in the GRO...

The AND keyword is used to add more conditions to the query. NameAgeGenderSam18MJohn21MBob22MMary23F SELECT name FROM persons WHERE gender = 'M' AND age > 20; This will return: NameJohnBob using OR keyword SELECT name FROM persons WHERE gender = 'M' OR age < 20; This will return: n...