SQL Commands

Conditions based on a range

□ Between
□ Lower value and upper value
□ To list icode, descp and QOH whose
QOH falls between 30 and 50 from a
table Items(Icode, Descp, Price, QOH,
ROL, ROQ)
□ Select icode, descp, QOH from items
where QOH between 30 AND 50;
Conditions based on a range
□ Not between
□ To list items whose ROL is below 100
or above 1000.
□ Select icode, descp from items where
ROL not between 100 AND 1000;
Conditions based on a list
□ To specify a list of values IN operator is
used
□ To display a list of members from Delhi,
Mumbai, Chennai or Bangalore:
□ Select * from members WHERE city IN
(‘Delhi’, ‘Mumbai’, ‘Chennai’, ‘Bangalore’);
□ Select * from members WHERE city NOT IN
(‘Delhi’, ‘Mumbai’, ‘Bangalore’);
Based on Pattern Matches
□ Patterns described using two wildcard
characters - % and _
□%
■ Matches any substring
□_
■ Matches any one character
□ LIKE keyword is used
Examples
□ “San%” – matches any string
beginning with San
□ “%idge%” – matches any string
containing “idge” as a substring
□ “_ _ _ _” – matches any string with
exactly 4 characters
□ “_ _ _ _ %” – matches any string with
atleast 4 characters
LIKE
□ To list the firstname, lastname and
city from members for which the pin
starts with 13
■ Select firstname, lastname, city from
members WHERE pin LIKE “13%”;
□ To list empno and empname from
emp who have for letter firstnames
ending with D.
■ Select empno, empname from emp
where empname LIKE “_ _ _ D”;
□ To list members which are not in
areas with pincodes starting with 13,
the command is:
■ Select firstname, lastname, city from
members where pin NOT LIKE “13%”;
□ To include special characters – use \
■ LIKE “wx\%yz%” matches strings
beginning with “wx%yz”
■ LIKE “wx\\yz%” matches strings
beginning with “wx\yz”
Searching for NULL
□ IS NULL
□ To list empno, empname, job of all
employees whose deptno contain
NULL from emp
■ Select empno, empname, job from emp
WHERE deptno IS NULL;
Sorting results – ORDER BY
□ Ascending or descending order
□ Select <column name>[,<column
name>, …] from <table name> where
<predicate> order by <column name>;
□ To display the list of employees in the
alphabetical order of their names:
■ Select * from employee ORDER BY ename;
□ To display the list of employees in the
descending order of employee code:
■ Select * FROM employee ORDER BY
ecode DESC;
□ DUAL table
■ Small worktable with one row and
column for obtaining calculation results
and system date
□ Select 4 * 3 from dual;
■ 4*3
12
□ Select sysdate from dual;
■ SYSDATE
06-Nov-12
Aggregate Functions
□ Used to calculate summary values from
data in a particular column
■ Avg
■ Min
■ Max
■ Sum
■ Stddev
■ Count – count non null values
■ Count(*) – count total no: of rows
■ variance
□ Table – Employee(ecode, ename,
gross, city)
1. To calculate total gross for employees
of grade ‘E2’.
2. To display average gross of
employees with grades ‘E1’ or ‘E2’.
3. To count the number of employees.
4. To count the number of cities.
Answers
1. Select sum(gross) from employee
where grade = ‘E2’;
2. Select avg (gross) from employee
where (grade = ‘E1’ OR grade =
‘E2’);
3. Select count (*) from employee;
4. Select count (Distinct City) from
employee;
Grouping Result – GROUP BY
□ To calculate the number of employees in
each grade.
■ Select job, count(*) from emp GROUP BY job;
□
Job Count (*)
President 1
Manager 2
Salesman 2
Analyst 2
Placing conditions on Groups –
HAVING clause
□ Places conditions on groups
□ To display the jobs where number of
employee are less than 3:
■ Select job, count(*) from emp GROUP BY
job HAVING count(*)<3;
Scalar expressions with selected
fields
□ Select salesman_name, comm*100
from salesman;

Putting text in the Query Output

□ Select salesman_name, comm*100,

‘%’ from salesman;
Creating table from existing table
□ CREATE TABLE orditem AS
(SELECT icode, descp FROM items
WHERE QOH<ROL);
□ SELECT icode, descp INTO orditems
FROM items
WHERE QOH<ROL;
INSERT command
□ Insert into <tablename>[<column
list>] values (<value>,<value>,…);
□ To insert values of a row:
■ INSERT INTO employee VALUES(1001,
‘RAVI’, ‘M’, ‘E4’, 4670);
□ To insert only ecode, ename and sex
then:
■ INSERT INTO employee (ecode, ename,
sex) VALUES(1001, ‘RAVI’, ‘M’);
Inserting results of a query
□ INSERT INTO branch1 SELECT * FROM
branch2 WHERE gross>7000;
□ Conditions:
■ Both tables must exist
■ Columns of the table being inserted into
must match the output of subquery
DELETE command
□ DELETE FROM <tablename>
WHERE <PREDICATE>;
□ To remove all contents:
■ DELETE FROM items;
□ DELETE FROM employee
WHERE gross < 2200;
UPDATE command
□ Specifies the row to be changed using
the WHERE clause and the new data
using SET clause
□ To change the ROL of all items to 250
■ UPDATE items SET ROL = 250;
□ To change the ROL TO 400 for those
items that have ROL as 300
■ UPDATE items SET ROL =400
WHERE ROL =300;
Updating multiple columns
□ To update ROL as 400 and QOH as 700
for items having icode less than I040:
■ UPDATE items SET ROL=400, QOH =700
WHERE icode<‘I040’;

Using expressions
■ To increase in Update
the gross pay of all employees
by Rs.900/-
□ UPDATE employee SET gross = gross + 900;
□ To double the gross pay of employees
of grade ‘E3’ and ‘E4’.
Updating to NULL values
□ To insert NULL values for grades ‘E4’.
■ UPDATE employees SET grade = NULL
WHERE grade = ‘E4’;
CREATE VIEW
□ CREATE VIEW taxpayee AS SELECT *
FROM employee
WHERE gross>8000;
□ To query the view
■ SELECT * FROM taxpayee;
Some built in functions
□ Lower (character- expression)
□ Upper (character- expression)
□ Replicate (char-expn, no-of-times)
□ Substr (expn, startpos, no-of-chars)
□ getdate()
Built-in functions can work on dual
tables.
Dual tables are default tables with only
one row and one column
□ Select lower (“Hello”) from dual;
□ Select upper(“friends”) from dual;
□ Select replicate (“#”, 4) from dual;
□ Select substr (“pointer”, 3, 2) from
dual;
Alter table
□ Change definition of existing table
□ Add a column
■ Alter table <table name> ADD <column
name><data type><size>;
□ Modify existing column
■ Alter table <tablename> modify
(columnname newdatatype (newsize));
Drop table
□ Drops a table from database
□ A table with rows cannot be dropped
■ DELETE from items;
■ DROP table items;
□ To delete a view
■ Drop view taxpayee;