The SELECT statement is used to select or retrieve data from one or more tables. You can use this statement to retrieve all rows/records from a table in one go, or to retrieve only those rows/records that satisfy a certain criteria/condition.
SELECT * FROM table_name;
SELECT emp_id, emp_name, hire_date, salary FROM employees;
Selecting Record Based on Condition
The WHERE clause is used to filter records. The WHERE clause is used with SELECT statement to extract only those records that fulfill specified conditions.
SELECT * FROM employees WHERE emp_id = 2;
SELECT * FROM employees WHERE salary > 7000;
SQL requires single quotes around text values (most database systems will also allow double quotes). However, numeric fields should not be enclosed in quotes.
SELECT * FROM employees WHERE name = 'Ankit';
SQL supports a number of different operators that can be used in WHERE clause.
|=||Equal||WHERE id = 2|
|>||Greater than||WHERE age > 30|
|<||Less than||WHERE age < 18|
|>=||Greater than or equal||WHERE rating >= 4|
|<=||Less than or equal||WHERE price <= 100|
|<>, !=, NOT||Not equals to||WHERE price <> 100
WHERE price != 100
WHERE not price = 100
|LIKE||Pattern matching||WHERE name LIKE 'Dav'|
|IN||Check whether a specified value matches any value in a list or subquery||WHERE country IN ('India', 'UK'),
WHERE dept_id IN (1, 3),
WHERE dept_id NOT IN (1, 3)
|BETWEEN||Check whether a specified value is within a range of values||WHERE age BETWEEN 20 AND 50
BETWEEN doj('2006-01-01' AS DATE) AND doj('2016-12-31' AS DATE)
WHERE emp_name BETWEEN 'p' AND 'Z';
|AND||TRUE only if both conditions are TRUE||WHERE salary > 7000 AND dept_id = 5;|
|OR||TRUE when either of the conditions is TRUE.||WHERE salary > 7000 OR dept_id = 5;|
The AND and OR operators are used to filter records based on more than one condition
SELECT * FROM student WHERE course = 'Web Designing' AND (city = 'Delhi' OR city = 'Haryana');
SELECT * FROM student WHERE NOT course = 'Web Designing' AND NOT course = 'Web Development';