Data Retrieval Language (or) Data Query Language Commands:
Select: By using these command we can retrieve the data from the database objects in user required format.
Syntax: select * from student;
select * from student where student_id=22;
select * from student where student_name=’praveen’;
Using select Query with ArithmeticOperations:
select emp_no,emp_name,emp_sal+5000 from emp;
select emp_no, emp_name, emp_sal+commission from emp;
if commission column has null values then we will get wrong output because null+anything=null
Inorder to solve the above problem we have null values handling functions
Null: Null is a unavailable / undefined / unexpected value and it is not equals to zero. To handle null values we have a function called nvl() which is mostly used when compared to other null value handling functions.
nvl(): It accepts two parameters. In the first parameter we have to set the the column name which contains the null value and in the second parameter we have to set the value which we want to pass in place of the null value.
Syntax: select emp_no, emp_name, emp_sal+nvl(commission ,2000) from emp;
Using select Query with dual:
Dual: Dual is a predefined virtual table contains one column and one row. We can test the predefined functions functionalities by using dual table.
Syntax: select nvl(null,20) from dual; //20
Select 5*5 from dual;//25
Select 100+100 from dual;//200
Using Select Query with Comparison operators:
By using comparison operators we can retrieve the data from the database objects by comparing with the required values.
Syntax: select * from emp where esal>3000;
select * from emp where job<>’manager’;
Using Select Query with Logical operators:
By using the select Query with logical operators we can check for multiple conditions.
Syntax: select * from emp where deptno=20 OR deptno=30;
Using Select Query with Special operators:
Syntax: select * from emp where deptno in(20,30)//same like OR
Select * from emp where salary between 1000 and 2000;
Select * from emp where salary not between 1000 and 2000;
Select * from emp where commission is null;
Using select Query with like:
Here we use two symbols (– and %)
Syntax: select * from emp where ename like ‘-c%’; //it displays employees information whose name second letter is ‘c’
Select * from emp where ename like ‘%c’; //it displays employees information whose name last letter is ‘c’
Concatenation(||):
It displays user defined text in between two columns
Syntax:Select ename ||’ salary is’||sal from emp; //ravi salary is 200000
Select Query with variuos Functions:
Function is a sub-routine which performs a definite task. Let us see some function syntaxes which are used along with the select Query.
Select ascii(‘a’) from dual; //97
Select chr(97) from dual; //a
Select concat(‘chaar’ ‘minar’)from dual; //chaarminar
Select initcap(‘hyderabad’)from dual; //Hyderabad—First letter in capital
Select initcap(‘pawan kalyan’)from dual; //Pawan Kalyan---p,k are capital
Select instr(‘pawankalyan’, ‘a’)from dual;//first occurrence of ‘a’ is 2
Select instr(‘pawankalyan’, ‘a’,3)from dual;//first occurrence of ‘a’ is 4.
Here search starts from third letter ‘w’ so the first occurrence of ‘a’ is 4th position.
Select length(‘pawan kalyan’)from dual; //12
Select lower(‘PAWAN KALYAN’) from dual;//pawan kalyan
Select lpad (string, padded length, pad character)from dual;
If the padded length is greater than the string length then its difference is added by the pad character on to the left side. Left padding
Select lpad(‘pawan kalyan’,15, ‘*’)from dual ;//***pawan kalian
Here the length of ‘pawan kalyan’ is 12 and the padded length is 15, the difference is 15-12=3. So 3 places on left side of the string is added by ‘*’
Select lpad(‘pawan kalyan’,10, ‘*’)from dual; // pawan kaly
If the padded length is less than the string length ,then the string itself truncates.
Select ltrim(‘ pawan kalyan’)from dual; //pawan kalyan
No comments:
Post a Comment