1
Creating Database
CREATE DATABASE drpdb1;
List Databases
SELECT * FROM pg_database;
SELECT * FROM pg_database WHERE datname LIKE '%temp%';
Note: Database names are case-sensitive;
SELECT * FROM pg_database WHERE datname LIKE 'drp___'
List Tables
SELECT * FROM information_schema.tables
SELECT * FROM information_schema.tables WHERE table_catalog = 'drpdb';
SELECT * FROM information_schema.tables WHERE table_catalog = 'drpdb'
List Columns of a Table
SELECT * FROM information_schema.columns WHERE table_catalog='drpdb' AND table_name =
'emptable';
Creating Table Structure
DROP TABLE EmpTable;
CREATE TABLE EmpTable
(
EmpNo INT,
"First Name" VARCHAR(15),
LastName VARCHAR(15),
Address VARCHAR(50),
City VARCHAR(20),
State VARCHAR(20),
Salary FLOAT
);
Or
CREATE TABLE EmpTable1
(
"EmpNo" INT,
"First Name" VARCHAR(15),
"LastName" VARCHAR(15),
"Address" VARCHAR(50),
"City" VARCHAR(20),
"State" VARCHAR(20),
"Salary" FLOAT
);
Or
CREATE TABLE "Emp Table"
(
"EmpNo" INT,
"First Name" VARCHAR(15),
"LastName" VARCHAR(15),
"Address" VARCHAR(50),
"City" VARCHAR(20),
"State" VARCHAR(20),
"Salary" FLOAT
);
Deleting Table
DROP TABLE EmpTable1;
Inserting Rows in a Table
INSERT INTO EmpTable
(EmpNo, "First Name", LastName, Address, City, State, Salary)
VALUES(1, 'PRASHANT', 'SAWANT', 'WADALA(E)', 'MUMBAI', 'MAHARASHTRA', 1000);
2
INSERT INTO EmpTable
("First Name", EmpNo, LastName, Salary, Address, City, State)
VALUES('AKASH', 2, 'CHABRIA', 2000, 'KHAR(W)', 'MUMBAI', 'MAHARASHTRA') ;
INSERT INTO EmpTable VALUES(3, 'VIPUL', 'THOSAR', 'KALYAN(W)', 'THANE', 'MAHARASHTRA',
3000);
INSERT INTO EmpTable VALUES(4, 'AVANI', 'KAMATH', 'AMBARNATH', 'THANE', 'MAHARASHTRA',
1500);
INSERT INTO EmpTable VALUES(5, 'PALAK', 'TENDULKAR', 'VASHI', 'NEW MUMBAI',
'MAHARASHTRA', NULL);
OR
INSERT INTO EmpTable
("First Name", EmpNo, LastName, Address, City, State)
VALUES('PALAK', 5, 'TENDULKAR', 'VASHI', 'NEW MUMBAI', 'MAHARASHTRA');
INSERT INTO EmpTable VALUES(6, 'SITA', 'BABBAR', 'GOREGAON (E)', 'MUMBAI', 'MAHARASHTRA',
3000);
RETRIEVE ALL COLUMNS and ALL ROWS: The SELECT Statement
SELECT * FROM EmpTable; OR SELECT ALL * FROM EmpTable;
RETRIEVE ONLY ONE COLUMN
SELECT "First Name" FROM EmpTable;
RETRIEVE MORE THAN ONE SPECIFIED COLUMNS
SELECT "First Name",LastName FROM EmpTable;
Concatenate Two/More Columns
SELECT "First Name" || LastName FROM EmpTable;
SELECT CONCAT("First Name", LastName) FROM EmpTable;
SELECT "First Name" || ' ' || LastName FROM EmpTable;
SELECT CONCAT("First Name", ' ', LastName) FROM EmpTable;
SELECT "First Name" || ' ' || LastName AS FULLNAME FROM EmpTable;
SELECT CONCAT("First Name", ' ', LastName) AS FULLNAME FROM EmpTable;
Concatenate numeric Fields
SELECT EmpNo::text || ' ' ||Salary::Text from emptable;
SELECT CONCAT(EmpNo::text,' ',Salary::Text) from EmpTable;;
Concatenating Literal String Values
SELECT 'Hello ' || 'Good Morning';
SELECT CONCAT('Hello ', 'Good Morning') ;
SELECT 'Hello ' || "First Name" || ' ' || LastName FROM EmpTable;
SELECT CONCAT('Hello ', "First Name", ' ', LastName) FROM EmpTable;
Concatenating Literal Numeric Values
SELECT CONCAT(1::text,' ',5000::Text);
Aliases in PostgreSQL
COLUMN ALIASES are used to make column headings in the result set easier to read.
TABLE ALIASES are used to shorten the SQL to make it easier to read or when we are performing a self
join (ie: listing the same table more than once in the FROM clause).
Aliases is more useful when
There are more than one tables involved in a query,
3
Functions are used in the query,
The column names are big or not readable,
More than one columns are combined together (Concatenation using || or CONCATE function)
Syntax
The syntax to ALIAS A COLUMN in PostgreSQL is:
column_name AS alias_name OR column_name alias_name
The syntax to ALIAS A TABLE in PostgreSQL is:
table_name AS alias_name OR table_name alias_name
Column Name Alias
SELECT CONCAT("First Name", ' ', LastName) FullName FROM EmpTable;
SELECT CONCAT("First Name", ' ', LastName) AS FullName FROM EmpTable;
SELECT CONCAT("First Name", ' ', LastName) "Full Name" FROM EmpTable;
SELECT CONCAT("First Name", ' ', LastName) AS "Full Name" FROM EmpTable;
Table Name Alias
SELECT CONCAT("First Name", ' ', LastName) FullName FROM EmpTable AS et;
SELECT CONCAT("First Name", ' ', LastName) AS FullName FROM EmpTable et;
SELECT CONCAT("First Name", ' ', LastName) FullName FROM EmpTable AS "e t";
SELECT CONCAT("First Name", ' ', LastName) AS FullName FROM EmpTable "et";
Inserting NULL Values
INSERT INTO EmpTable VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL);
Select constant as a column
SELECT EmpNo, "First Name", 'Permanent' as Typeof Employee FROM Emptable;
Expressions in SELECT Statement
An arithmetic expression can contain column names, numeric numbers, and arithmetic operators.
Operator Description
+ Addition operator
- Minus operator
* Multiplication operator
/ Division operator
% Modulo operator
- Unary minus. It changes the sign of the argument.
^ Raise To then Power . Exponentiation.
! Factorial
@ Absolute Value
& Bit-wise AND
| Bit-wise OR
# Bit-wsie XOR
~ Bit-wise NOT
<< Bit-wise Shift Left
>> Bit-wise Shift Right
SELECT EmpNo, "First Name", LastName, Salary * 2 as "New Salary" FROM EmpTable;
SELECT EmpNo, "First Name", LastName, Salary / 2 as "New Salary" FROM EmpTable;
SELECT EmpNo, "First Name", LastName, CAST(Salary AS INT) % 2.0 as "New Salary" FROM empTable;
SELECT EmpNo, "First Name", LastName, CAST(Salary AS INTEGER) % 2.0 as "New Salary" FROM
EmpTable;
SELECT EmpNo, "First Name", LastName, Salary ^ 2 as "New Salary" FROM EmpTable;
SELECT 5!
SELECT @(-5)
SELECT 2 & 3
4
SELECT 2 | 3
SELECT 2 # 3
SELECT ~ 3
SELECT 1 << 4
SELECT 8>>2
Performing Operations on 2 or more Columns
SELECT Salary * Salary FROM EmpTable; SELECT Salary / Salary FROM EmpTable;
SELECT Salary + Salary FROM EmpTable; SELECT Salary - Salary FROM EmpTable;
SQL WHERE Clause
Conditional selection of data / Filtering or Restricting Rows of data. Using Relational (comparison)
Operators: =, >, >=, <, <=, !=, <>, ^=. The WHERE clause indicates the condition or conditions that rows
must satisfy to be selected. Note that the expression in the WHERE clause is evaluated to either true or
false for every row in the table. If the value evaluates to TRUE the row is selected otherwise not. In the
WHERE clause, the condition(s) is/are made up of any of column names, literal values, expressions (such
as arithmetic expressions), constants, list of values, operators, or built-in or user-defined functions (except
for aggregate functions). The comparison must be conducted between values of the same data type. When
comparing values of different data types, we can use CAST() function to convert a value to a specific type.
CAST() function is not used when we compare strings with numbers, strings are automatically converted to
numbers, if posible. The same is also true for numbers where numbers are automatically converted to
strings as necessary. By default, string comparisons are not case sensitive and it relies on the current
character set of the column, table, database, or server.
SELECT * FROM EmpTable WHERE Salary =1000;
SELECT * FROM EmpTable WHERE Salary <> 1000;
SELECT * FROM EmpTable WHERE Salary != 1000;
SELECT * FROM EmpTable WHERE Salary > 1000;
SELECT * FROM EmpTable WHERE Salary >= 1000;
SELECT * FROM EmpTable WHERE Salary < 1000;
SELECT * FROM EmpTable WHERE Salary <= 1000;
SELECT EmpNo, "First Name", LastName, Salary * 2 AS NewSalary FROM EmpTable WHERE Salary * 2
>=5000;
SELECT * FROM EmpTable WHERE 1 = 2;
SELECT * FROM EmpTable WHERE 'AKASH' = 'AKASH';
The above SELECT query select none of the rows present in the table as it evaluates to FALSE for every
row.
SELECT * FROM EmpTable WHERE 1 = 1;
The above SELECT queries select all the rows present in the table as it evaluates to TRUE for every row.
SELECT * FROM EmpTable WHERE 'AKASH' = 'VIPUL';
The above SELECT queries does not select any row present in the table as it evaluates to FALSE for every
row.
Using Logical Operators in SELECT Statement
SELECT * FROM EmpTable WHERE City = 'MUMBAI' AND Salary <= 2000;
SELECT * FROM EmpTable WHERE City = 'MUMBAI' AND "First Name" = 'AKASH';
SELECT * FROM EmpTable WHERE City = 'MUMBAI' OR Salary >= 2000;
SELECT * FROM EmpTable WHERE NOT City = 'MUMBAI';
SELECT * FROM EmpTable WHERE City = 'MUMBAI' OR City = 'THANE' AND Salary > 2000;
SELECT * FROM EmpTable WHERE LOWER(City) = 'mumbai' OR (LOWER (City) = 'thane' AND Salary >
2000);
Note: PostgreSQL String data is Case Sensitive.
SELECT * FROM EmpTable WHERE City = 'THANE' OR City = 'MUMBAI' AND Salary > 2000;
NOTE: NOT Operator is evaluated before AND operator and AND Operator is evaluated before OR
operator.
5
SELECT * FROM EmpTable WHERE (City = 'THANE' OR City = 'MUMBAI') AND Salary > 2000;
SELECT * FROM EmpTable WHERE City = 'THANE' AND NOT Salary > 2000;
SELECT * FROM EmpTable WHERE City = 'THANE' AND Salary <= 2000;
SELECT * FROM EmpTable WHERE City = 'THANE' OR City = 'MUMBAI' AND Salary <= 2000;
SELECT * FROM EmpTable WHERE City = 'THANE' OR (City = 'MUMBAI' AND Salary <= 2000);
SELECT * FROM EmpTable WHERE City = 'THANE' OR City = 'MUMBAI' AND NOT Salary > 2000;
ALL, ANY and SOME Comparison Conditions in SQL
It is quite possible we could work with PostgreSQL databases for many years and never come across the
ALL, ANY and SOME comparison conditions in SQL because there are alternatives to them that are used
more regularly.
It must be preceded by =, !=, >, <, <=, >= and followed by a list or subquery.
When the ALL condition is followed by a list, the optimizer expands the initial condition to all elements of
the list and strings them together with AND operators, as shown below.
In PostgreSQL list values used inside the brackets of ALL, ANY, SOME operators are required to be
converted into corresponding data type array. This is not applicable to IN operator
Note: “::” (colon colon sign) operator: Casts the value expr to the target data type type . This operator is a
synonym for cast function.
SELECT empno, salary FROM emptable WHERE salary = ALL('{1000, 2000}' :: int[]);
SELECT empno, salary FROM emptable WHERE salary > ALL('{1000, 2000}' :: int[]);
SELECT empno, salary FROM emptable WHERE salary = ANY('{1000, 2000, 3000}' :: int[]);
OR
SELECT empno, salary FROM emptable WHERE salary = SOME('{1000, 2000, 3000}' :: int[]);
SELECT empno, "First Name", salary FROM emptable WHERE "First Name" = ANY('{VIPUL, AKASH,
AVANI}' :: text[]);
OR
SELECT empno, "First Name", salary FROM emptable WHERE "First Name" = SOME('{VIPUL, AKASH,
AVANI}' :: text[]);
Membership Conditions
Using IN / NOT IN Comparison Operator to specify multiple values (List of Values) in WHERE clause
SELECT * FROM EmpTable WHERE Salary IN (1000, 3000);
Or
SELECT * FROM EmpTable WHERE Salary = 1000 OR Salary = 3000;
SELECT * FROM EmpTable WHERE Salary NOT IN (1000, 3000);
Or
SELECT * FROM EmpTable WHERE Salary != 1000 AND Salary != 3000;
Or
SELECT * FROM EmpTable WHERE Salary <> 1000 AND Salary <> 3000;
Or
SELECT * FROM EmpTable WHERE NOT(Salary = 1000) AND NOT(Salary = 3000);
SELECT * FROM EmpTable WHERE Salary IN (1000, 3000);
OR
SELECT * FROM EmpTable WHERE Salary = ANY ('{1000, 3000}' :: Int[]);
SELECT * FROM EmpTable WHERE Salary IN(1000, 2000);
Or
SELECT * FROM EmpTable WHERE Salary = 1000 OR Salary=2000;
Or
SELECT * FROM EmpTable WHERE Salary =ANY(1000, 2000);
Using BETWEEN…AND Comparison Operator to Select values within range
SELECT * FROM EmpTable WHERE Salary BETWEEN 1000 AND 3000;
Or
SELECT * FROM EmpTable WHERE Salary >= 1000 AND Salary <=3000;
6
IS NULL / IS NOT NULL Comparison Operator (Unary Operator)
SELECT * FROM EmpTable WHERE Salary IS NULL;
SELECT * FROM EmpTable WHERE Salary IS NOT NULL;
LIKE Comparison Operator (Pattern Matching) [Escape Character]
When we do not know the exact value to search for, we can use LIKE operator to perform searches on
similar values. The similar values are given a pattern that has to be matched.
The pattern matching operation is referred to as wildcard search.
There are two wildcard characters for pattern matching:
% -> Matches any number of characters, including zero or more characters.
_ -> Matches exactly one character
SELECT * FROM EmpTable WHERE Salary ::TEXT LIKE '%';
Note: Rows with NULL value are not retrieved.
SELECT * FROM EmpTable WHERE Salary::TEXT LIKE '%0';
SELECT * FROM EmpTable WHERE Salary::TEXT LIKE '%1';
SELECT * FROM EmpTable WHERE Salary::TEXT LIKE '%50';
SELECT * FROM EmpTable WHERE Salary::TEXT LIKE '1000%';
SELECT * FROM EmpTable WHERE Salary::TEXT LIKE '100%';
SELECT * FROM EmpTable WHERE Salary::TEXT LIKE '10%';
SELECT * FROM EmpTable WHERE Salary::TEXT NOT LIKE '1000%';
SELECT * FROM EmpTable WHERE Salary::TEXT NOT LIKE '100%';
SELECT * FROM EmpTable WHERE Salary::TEXT NOT LIKE '10%';
SELECT * FROM EmpTable WHERE Salary::TEXT LIKE '1%';
SELECT * FROM EmpTable WHERE Salary::TEXT NOT LIKE '1%';
SELECT * FROM EmpTable WHERE Salary::TEXT LIKE '_';
SELECT * FROM EmpTable WHERE Salary::TEXT LIKE '____';
SELECT * FROM EmpTable WHERE Salary::TEXT LIKE '_000';
SELECT * FROM EmpTable WHERE Salary::TEXT LIKE '1_00';
SELECT * FROM EmpTable WHERE Salary::TEXT NOT LIKE '1_00';
SELECT * FROM EmpTable WHERE "First Name" LIKE 'A%';
SELECT * FROM EmpTable WHERE "First Name" LIKE 'a%';
SELECT * FROM EmpTable WHERE UPPER("First Name") LIKE 'A%';
SELECT * FROM EmpTable WHERE LOWER("First Name") LIKE 'a%';
SELECT * FROM EmpTable WHERE "First Name" LIKE '__A%';
SELECT * FROM EmpTable WHERE "First Name" LIKE '%SH%';
SELECT * FROM EmpTable WHERE "First Name" LIKE '%NT';
INSERT INTO EmpTable VALUES(100, 'AAA_', 'XYZ', 'XXX', 'YYY', 'ZZZ', 1);
INSERT INTO EmpTable VALUES(101, 'AAA_AAA', 'XYZ', 'XXX', 'YYY', 'ZZZ', 2);
INSERT INTO EmpTable VALUES(102, '%BBB', 'XYZ', 'XXX', 'YYY', 'ZZZ', 3);
SELECT * FROM EmpTable WHERE "First Name" LIKE '%!_' ESCAPE '!';
SELECT * FROM EmpTable WHERE "First Name" LIKE '%?_A%' ESCAPE '?';
SELECT * FROM EmpTable WHERE "First Name" LIKE '%X_A%' ESCAPE 'X';
SELECT * FROM EmpTable WHERE "First Name" LIKE '%\_A%' ESCAPE '\';
SELECT * FROM EmpTable WHERE "First Name" LIKE '$%%' ESCAPE '$';
Editing or Modifying Data: UPDATE Statement
Update statement is used to make changes to data in one or more columns in a table.
The basic UPDATE statement takes the following syntax format:
UPDATE [ ONLY ] table [ * ] [ [ AS ] alias ]
SET { column = { expression | DEFAULT } |
( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
[ FROM fromlist ]
7
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ AS output_name ] [, ...] ]
INSERT INTO EmpTable
(EmpNo, "First Name", LastName, Address, City, State, Salary)
VALUES(1001, 'AAA', 'BBB', 'CCC', 'DDD', 'EEE', 9000);
INSERT INTO EmpTable
(EmpNo, "First Name", LastName, Address, City, State, Salary)
VALUES(1002, 'FFF', 'GGG', 'HHH', 'III', 'JJJ', 9001);
Updating Single Column/ Field of All Records
UPDATE EmpTable SET Salary = 1000;
SELECT * FROM EmpTable;
Updating Multiple Columns/ Fields of All Records
UPDATE EmpTable SET Salary = 5000, Address = 'New Address';
Conditional Update: Using =, !=, <>,^=, <, <=, >, >= Relational / Comparison Operators
UPDATE EmpTable SET "First Name" = 'aaa' WHERE "EmpNo" = 1001;
SELECT * FROM EmpTable WHERE EmpNo =1001;
UPDATE EmpTable SET Salary = NULL WHERE EmpNo >= 100;
Update using IN Relational / Comparison Operator
UPDATE EmpTable SET Salary = 1 WHERE EmpNo IN (1001, 1002);
Or
UPDATE EmpTable SET Salary = 1 WHERE EmpNo = 1001 OR EmpNo = 1002;
Or
UPDATE EmpTable SET Salary = 1 WHERE EmpNo = ANY ('{1001, 1002}'::int[])
SELECT * FROM EmpTable WHERE EmpNo IN(1001, 1002);
Update using NOT IN Relational / Comparison Operator
UPDATE EmpTable SET Salary = Salary * 1/2 WHERE EmpNo NOT IN (1001, 1002);
Or
UPDATE EmpTable SET Salary = Salary * 1/2 WHERE EmpNo <> 1001 AND EmpNo <> 1002;
Or
UPDATE EmpTable SET Salary = Salary * 1/2 WHERE EmpNo != 1001 AND EmpNo != 1002;
Or
UPDATE EmpTable SET Salary = Salary * 1/2 WHERE EmpNo ^= 1001 AND EmpNo ^= 1002;
Update using BETWEEN … AND Relational / Comparison Operator
UPDATE EmpTable SET Salary = 2 WHERE EmpNo BETWEEN 1001 AND 1002;
Or
UPDATE EmpTable SET Salary = 2 WHERE EmpNo >= 1001 AND EmpNo <= 1002;
SELECT * FROM EmpTable WHERE EmpNo BETWEEN 1001 AND 1002;
Update using NOT BETWEEN … AND Relational / Comparison Operator
UPDATE EmpTable SET Salary = 3000 WHERE EmpNo NOT BETWEEN 1001 AND 1002;
Or
UPDATE EmpTable SET Salary = 3000 WHERE EmpNo < 1001 OR EmpNo > 1002;
SELECT * FROM EmpTable WHERE EmpNo BETWEEN 1001 AND 1002;
Update using ANY / SOME Relational / Comparison Operator
UPDATE EmpTable SET Salary = 10000 WHERE EmpNo = ANY('{1001, 1002}'::int[]);
Or
UPDATE EmpTable SET Salary = 10000 WHERE EmpNo = SOME('{1001, 1002}'::int[]);
Or
UPDATE EmpTable SET Salary = 10000 WHERE EmpNo = 1001 OR EmpNo = 1002;
UPDATE EmpTable SET Salary = 10000 WHERE EmpNo > ANY('{1001, 1002}'::int[]);
8
Or
UPDATE EmpTable SET Salary = 10000 WHERE EmpNo > SOME('{1001, 1002}'::int[]);
Or
UPDATE EmpTable SET Salary = 10000 WHERE EmpNo > 1001 OR EmpNo > 1002;
UPDATE EmpTable SET Salary = 1000 WHERE EmpNo > ANY('{10, 20, 30}'::int[]);
Or
UPDATE EmpTable SET Salary = 1000 WHERE EmpNo > SOME('{10, 20, 30}'::int[]);
Or
UPDATE EmpTable SET Salary = 1000 WHERE EmpNo > 10 OR EmpNo > 20 OR EmpNo > 30;
UPDATE EmpTable SET Salary = 1000 WHERE EmpNo <> ANY('{1001,1002}'::int[]);
Or
UPDATE EmpTable SET Salary = 1000 WHERE EmpNo != ANY('{1001,1002}'::int[]);
Or
UPDATE EmpTable SET Salary = 1000 WHERE EmpNo <> ANY('{1001,1002}'::int[]);
Or
UPDATE EmpTable SET Salary = 1000 WHERE EmpNo != ANY('{1001,1002}'::int[]);
Or
UPDATE EmpTable SET Salary = 1000 WHERE EmpNo <> 1001 OR EmpNo <> 1002;
Or
UPDATE EmpTable SET Salary = 1000 WHERE EmpNo != 1001 OR EmpNo != 1002;
UPDATE EmpTable SET Salary = 1000 WHERE NOT EmpNo > ANY(10, 20, 30);
Or
UPDATE EmpTable SET Salary = 1000 WHERE NOT EmpNo > SOME(10, 20, 30);
Or
UPDATE EmpTable SET Salary = 1000 WHERE NOT (EmpNo > 10 OR EmpNo > 20 OR EmpNo > 30);
Using ALL Relational / Comparison Operator
UPDATE EmpTable SET Salary = 10000 WHERE EmpNo = ALL(1001, 1002);
Or
UPDATE EmpTable SET Salary = 10000 WHERE EmpNo = 1001 AND EmpNo = 1002;
UPDATE EmpTable SET Salary = 10000 WHERE EmpNo > ALL(1001, 1002);
Or
UPDATE EmpTable SET Salary = 10000 WHERE EmpNo > 1001 AND EmpNo > 1002;
Note: With ‘=’ operator ‘ALL’ operator with more than one values in a list always evaluates to false.
UPDATE EmpTable SET Salary = 10000 WHERE NOT EmpNo = ALL(1001, 1002);
Or
UPDATE EmpTable SET Salary = 10000 WHERE NOT (EmpNo = 1001 AND EmpNo = 1002);
Or
UPDATE EmpTable SET Salary = 10000 WHERE EmpNo <> 1001 OR EmpNo <> 1002;
Or
UPDATE EmpTable SET Salary = 10000 WHERE EmpNo != 1001 OR EmpNo != 1002;
In fact with ‘ALL’ comparison operator using = has no effect i.e. condition does not evaluates to true for any
row. Thus no record is updated. Similarly with ‘NOT … ALL’ comparison operator using ‘= ‘condition
evaluates to true for any row. Thus all records are always updated.Therefore it should be used with <, <=,
>, >= operators.
Using IS NULL Relational / Comparison Operator
UPDATE EmpTable SET Salary = NULL WHERE EmpNo IN (1001, 1002);
UPDATE EmpTable SET Salary = 100 WHERE Salary IS NULL;
Using IS NOT NULL Relational / Comparison Operator
UPDATE EmpTable SET Salary = 5000 WHERE Salary IS NOT NULL;
9
Using LIKE Relational / Comparison Operator
UPDATE EmpTable SET Salary = 1000 WHERE "First Name" LIKE 'T%';
UPDATE EmpTable SET Salary = 7000 WHERE "First Name" LIKE '%T';
UPDATE EmpTable SET Salary = 3500 WHERE "First Name" LIKE 'S%A';
UPDATE EmpTable SET Salary = 1 WHERE City LIKE 'TH_';
UPDATE EmpTable SET Salary = 50 WHERE City LIKE '_H%';
UPDATE EmpTable SET Salary = 1000 WHERE City LIKE '%A_';
UPDATE EmpTable SET Salary = 1000 WHERE City LIKE '%A!_' ESCAPE '!';
UPDATE EmpTable SET Salary = 1000 WHERE City LIKE '#%A_' ESCAPE '#';
UPDATE EmpTable SET Salary = 2500 WHERE 1=1;
UPDATE EmpTable SET Salary = 2500 WHERE 1=2;
UPDATE EmpTable SET Salary = 2500 WHERE 'A' = 'A';
UPDATE EmpTable SET Salary = 2500 WHERE 'A' = 'z';
DELETE Statement
Delete All Rows
DELETE FROM EmpTable;
SELECT * FROM EmpTable;
Conditional Delete / WHERE Clause :Relational / Comparison Operators
DELETE FROM EmpTable WHERE EmpNo = 1001;
SELECT * FROM EmpTable WHERE EmpNo =1001;
DELETE FROM EmpTable WHERE EmpNo >= 100;
Using IN Relational / Comparison Operator
DELETE FROM EmpTable WHERE EmpNo IN (1001, 1002);
Or
DELETE FROM EmpTable WHERE EmpNo = 1001 OR EmpNo = 1002;
SELECT * FROM EmpTable WHERE EmpNo IN(1001, 1002);
Using NOT IN Relational / Comparison Operator
DELETE FROM EmpTable WHERE EmpNo NOT IN (1001, 1002);
Or
DELETE FROM EmpTable WHERE EmpNo <> 1001 AND EmpNo <> 1002;
Or
DELETE FROM EmpTable WHERE EmpNo != 1001 AND EmpNo != 1002;
Or
DELETE FROM EmpTable WHERE EmpNo ^= 1001 AND EmpNo ^= 1002;
Using BETWEEN … AND Relational / Comparison Operator
DELETE FROM EmpTable WHERE EmpNo BETWEEN 1001 AND 1002;
Or
DELETE FROM EmpTable WHERE EmpNo >= 1001 AND EmpNo <= 1002;
DELETE FROM EmpTable WHERE Salary BETWEEN 5000 AND 7000;
Or
DELETE FROM EmpTable WHERE Salary >= 5000 AND Salary <=7000;
SELECT * FROM EmpTable WHERE EmpNo BETWEEN 1001 AND 1002;
SELECT * FROM EmpTable WHERE firstname BETWEEN 'A%' AND 'G%';
Using NOT BETWEEN … AND Relational / Comparison Operator
DELETE FROM EmpTable WHERE EmpNo NOT BETWEEN 1001 AND 1002;
Or
DELETE FROM EmpTable WHERE EmpNo < 1001 OR EmpNo > 1002;
SELECT * FROM EmpTable WHERE EmpNo BETWEEN 1001 AND 1002;
DELETE FROM EmpTable WHERE Salary NOT BETWEEN 10000 AND 20000;
Or
DELETE FROM EmpTable WHERE Salary < 10000 OR Salary > 20000;
10
Using ANY / SOME Relational / Comparison Operator
DELETE FROM EmpTable WHERE EmpNo = ANY(1001, 1002);
Or
DELETE FROM EmpTable WHERE EmpNo = SOME(1001, 1002);
Or
DELETE FROM EmpTable WHERE EmpNo = 1001 OR EmpNo = 1002;
DELETE FROM EmpTable WHERE EmpNo = ANY(NULL);
DELETE FROM EmpTable WHERE EmpNo > ANY(1001, 1002);
Or
DELETE FROM EmpTable WHERE EmpNo > SOME(1001, 1002);
Or
DELETE FROM EmpTable WHERE EmpNo > 1001 OR EmpNo > 1002;
DELETE FROM EmpTable WHERE EmpNo > ANY(10, 20, 30);
Or
DELETE FROM EmpTable WHERE EmpNo > SOME(10, 20, 30);
Or
DELETE FROM EmpTable WHERE EmpNo > 10 OR EmpNo > 20 OR EmpNo > 30;
DELETE FROM EmpTable WHERE EmpNo <> ANY(1001, 1002);
Or
DELETE FROM EmpTable WHERE EmpNo != ANY(1001, 1002);
Or
DELETE FROM EmpTable WHERE EmpNo ^= ANY(1001, 1002);
Or
DELETE FROM EmpTable WHERE EmpNo <> SOME(1001, 1002);
Or
DELETE FROM EmpTable WHERE EmpNo != SOME(1001, 1002);
Or
DELETE FROM EmpTable WHERE EmpNo ^= SOME(1001, 1002);
Or
DELETE FROM EmpTable WHERE EmpNo <> 1001 OR EmpNo <> 1002;
Or
DELETE FROM EmpTable WHERE EmpNo != 1001 OR EmpNo != 1002;
Or
DELETE FROM EmpTable WHERE EmpNo ^= 1001 OR EmpNo ^= 1002;
DELETE FROM EmpTable WHERE NOT EmpNo > ANY(10, 20, 30);
Or
DELETE FROM EmpTable WHERE NOT EmpNo > SOME(10, 20, 30);
Or
DELETE FROM EmpTable WHERE NOT (EmpNo > 10 OR EmpNo > 20 OR EmpNo > 30);
Note:All comparison operators <, <=, >. >= =, <> can be used
Using ALL Relational / Comparison Operator
DELETE FROM EmpTable WHERE EmpNo = ALL(1001, 1002);
Or
DELETE FROM EmpTable WHERE EmpNo = 1001 AND EmpNo = 1002;
Note: With ‘=’ operator ‘ALL’ operator with more than one values in a list always evaluates to false.
DELETE FROM EmpTable WHERE NOT EmpNo = ALL(1001, 1002);
Or
11
DELETE FROM EmpTable WHERE NOT (EmpNo = 1001 AND EmpNo = 1002);
Or
DELETE FROM EmpTable WHERE EmpNo <> 1001 OR EmpNo <> 1002;
Or
DELETE FROM EmpTable WHERE EmpNo != 1001 OR EmpNo != 1002;
Or
DELETE FROM EmpTable WHERE EmpNo ^= 1001 OR EmpNo ^= 1002;
Note: All comparison operators <, <=, >. >= =, <> can be used.
In fact with ‘ALL’ comparison operator using = has no effect i.e. condition does not evaluates to false for
any row. Thus no record is updated.
Similarly with ‘NOT … ALL’ comparison operator using ‘= ‘condition evaluates to true for any row. Thus all
records are always updated.
Therefore it should be used with <, <=, >, >= operators.
Using IS NULL Relational / Comparison Operator
DELETE FROM EmpTable WHERE Salary IS NULL;
Using IS NOT NULL Relational / Comparison Operator
DELETE FROM EmpTable WHERE Salary IS NOT NULL;
Using LIKE Relational / Comparison Operator
DELETE FROM EmpTable WHERE "First Name" LIKE 'T%';
DELETE FROM EmpTable WHERE "First Name" LIKE '%T';
DELETE FROM EmpTable WHERE "First Name" LIKE 'S%A';
DELETE FROM EmpTable WHERE City LIKE 'TH_';
DELETE FROM EmpTable WHERE City LIKE '_H%';
DELETE FROM EmpTable WHERE City LIKE '%A_';
DELETE FROM EmpTable WHERE City LIKE '%A!_ ' ESCAPE '!';
DELETE FROM EmpTable WHERE City LIKE '#%A_ ' ESCAPE '#';
DELETE FROM EmpTable WHERE 1=1;
DELETE FROM EmpTable WHERE 1=2;
DELETE FROM EmpTable WHERE 'A' = 'A';
DELETE FROM EmpTable WHERE 'A' = 'z';
Using Logical Operators
Similar to SELECT /UPDATE statements
