My Profile
Active Members
TodayLast 7 Days
more...
Awards & Gifts
Online Exams
Fresher Jobs
Our fresher job section is exclusively for fresh graduates! Find jobs for freshers in major Indian
cities including Bangalore, Chennai, Hyderabad, Pune or Kochi
Resources
Find educational articles, blogs, discussion threads and other resources.
Colleges
Find details about any college in India or search for courses.
|
Resources » Articles/Knowledge Sharing » Education »
Sql commands one
|
SQL COMMANDS ONE
1.CHARACTER FUNCTIONS:
? CASE MANIPULATION FUNCTIONS:
• LOWER FUNCTIONS
SQL> Select lower (‘PREETHI’) from dual;
LOWER ('
-------
preethi
• UPPER FUNCTIONS
SQL> Select upper ('preethi') from dual;
UPPER ('
-------
PREETHI
• INITCAP FUNCTION:
SQL> Select initcap (‘hello’) from dual;
INITC
-----
Hello
? CHARACTER MANIPULATION FUNCTIONS:
• LENGTH FUNCTION:
SQL> Select stno, length (stname) namlen from student;
STNO NAMLEN
---------- ----------
100 11
101 7
• SUBSTRING FUNCTION:
SQL> Select stname, substr (stname, 1,4) firstname from student;
STNAME FIRS
--------------- ----
vigneshwari vign
avinash avin
• REPLACE FUNCTION:
SQL> Select replace ('reena','r',’v’) changestring from dual;
CHANG
-----
veena
• RTRIM FUNCTION:
SQL> Select rtrim ('database',’ase’) chdata from dual;
CHDAT
-----
datab
• LTRIM FUNCTION:
SQL> Select ltrim ('database',’data’) chdata from dual;
CHDA
----
base
2.NUMBER FUNCTIONS:
• ROUND FUNCTION:
SQL> Select round (45.923,1), round (45.923,0), round (45.923,-1) from dual;
ROUND (45.923,1) ROUND (45.923,0) ROUND (45.923, -1)
--------------- --------------- ----------------
45.9 46 50
• TRUNCATE FUNCTION:
SQL> Select trunc (45.923,2), trunc (45.923, -2) from dual;
TRUNC (45.923,2) TRUNC (45.923, -2)
--------------- ----------------
45.92 0
• MODULUS FUNCTION:
SQL> Select mod (1600,300) from dual;
MOD (1600,300)
-------------
100
3.AGGREGATING FUNCTIONS:
• COUNT FUNCTION:
SQL> Select count (*) from employeee;
COUNT (*)
----------
5
• MAX, MIN, AVG, SUM FUNCTION:
SQL> Select max (salary) maximum, min (salary) minimum, avg (salary) average, sum (salary) total from employee;
MAXIMUM MINIMUM AVERAGE TOTAL
---------- ---------- ---------- ----------
77777.77 54432.22 65997.36 329986.8
4.GENERAL FUNCTION:
• NVL FUNCTION:
SQL> select stno, stname, nvl (dep,’0’) from student;
STNO STNAME NVL (DEP,'0
---------- --------------- ----------
100-vigneshwari cse
101 avinash it
102 lakshmi 0
5.RELATIONAL OPERATORS:
SQL> Select * from employeee;
ENO ENAME AGE SALARY DOJ
--------- --------------- --------- --------- ---------
100 preethi 29 66666.66 28-JAN-00
101 swathi 30 66655.55 20-DEC-99
102 deepika 28 54432.22 20-FEB-01
104 aarthi 32 55423.44 15-SEP-02
IN OPERATOR:
SQL>Select * from employeee where eno in (100,102);
ENO ENAME AGE SALARY DOJ
--------- --------------- --------- --------- ---------
100 preethi 29 66666.66 28-JAN-00
102 deepika 28 54432.22 20-FEB-01
BETWEEN AND OPERATOR
SQL>Select * from employeee eno between 100 and 102;
ENO ENAME AGE SALARY DOJ
--------- --------------- --------- --------- ---------
100 preethi 29 66666.66 28-JAN-00
101 swathi 30 66655.55 20-DEC-99
102 deepika 28 54432.22 20-FEB-01
LIKE OPERATOR
SQL>Select * from employeee where ename like ‘p%’;
ENO ENAME AGE SALARY DOJ
--------- --------------- --------- --------- ---------
100 preethi 29 66666.66 28-JAN-00
SQL>Select * from employeee where ename like ‘%i’;
ENO ENAME AGE SALARY DOJ
--------- --------------- --------- --------- ---------
100 preethi 29 66666.66 28-JAN-00
101 swathi 30 66655.55 20-DEC-99
DISPLAY COMMANDS:
SQL> Select * from employeee;
ENO ENAME AGE SALARY DOJ
---------- --------------- ---------- ---------- ---------
100 preethi 29 66666.66 28-JAN-00
101 swathi 30 69988.33 20-DEC-99
102 deepika 28 54432.22 20-FEB-01
TO DISPLAY A SPECIFIC COLUMN:
SQL> Select ename from employeee;
ENAME
---------------
preethi
swathi
deepika
TO DISPLAY SPECIFIC RECORD OR RECORDS:
SQL> Select * from employeee where age>28 and age<32;
ENO ENAME AGE SALARY DOJ
---------- --------------- ---------- ---------- ---------
100 preethi 29 66666.66 28-JAN-00
101 swathi 30 69988.33 20-DEC-99
TO DISPLAY IN SPECIFIC ORDER:
SQL> select * from employeee where age>28 and age<32 order by age;
ENO ENAME AGE SALARY DOJ
---------- --------------- ---------- ---------- ---------
100 preethi 29 66666.66 28-JAN-00
101 swathi 30 69988.33 20-DEC-99
TO DISPLAY ONE COLUMN AS NULL:
SQL> Select * from employeee where doj is null;
ENO ENAME AGE SALARY DOJ
---------- --------------- ---------- ---------- ---------
100 preethi 29 66666.66
101 swathi 30 69988.33
|
Responses
|
No responses found. Be the first to respond and make money from revenue sharing program.
|
|
Advertise Here
|