Monthly Archives: January 2008

XML and Database

Posted on by
Reply

XML interaction with multiple database. Here we have taken two databases – Oracle (Remote) and Access (Local)

<% @Language=VBScript %

<?xml version=’1.0′?>

<?xml-stylesheet type=”text/xsl” href=”Fstyle1.xsl”?>

<people>

<%

set con=server.CreateObject(“adodb.connection”)

con.Open “dsn=asu;uid=asu;pwd=asu”

set rs = server.CreateObject(“adodb.recordset”)

set rs = server.Execute(“Select *From Emp”)

%>

<student>

<name><%=rs(0)%></name>

<roll><%=rs(1)%></roll>

</student>

<%

rs.Movenext

loop

%>

</people>
 

Creating a file in XML

Posted on by
Reply

An example of how to create a basic XML file

<?xml version=’1.0′?>
<details>
<company>
     <name>Lakme</name>
     <products>Cosmetic</products>
</company>
<company>
     <name>Data</name>
     <products>Spices</products>
 
</company>
<company>
     <name>Ranbaxy</name>
     <products>Medicine</products>
 
</company>
</details>

Arranging output in SQL

Posted on by
1

Remember ORDER BY is the last clause in the SELECT statement, which arranges the order after the SQL data are fetched

The ORDER BY clause arranges the  output of a SELECT statement in a particular order. It can be ascending or descending.

SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL;
 

The above SQL will display the result in the ascending order of SAL.

We can achieve the similar output by slightly reforming the statement, like

SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY 3;

Notice SAL is the 3rd column to be displayed

SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC;

will display the output in descending order.

You can use where condition in the sqame statement, but the ORDER BY clause should be the last one in the SELECT statement

Between, In Operators in SQL

Posted on by
Reply

This lesson will describe the usage of IN, Between Operators in Oracle

At times usage of OR, AND becomes cumbersome. Oracle has facilitated some other operators

BETWEEN

SELECT ENAME,JOB,SAL FROM EMP

WHERE SAL BETWEEN 1500 AND 3000;

is exactly equivalent to

SELECT ENAME,JOB,SAL FROM EMP

WHERE SAL> 1500 AND SAL < 3000;

Between can replace complicated AND operators

IN

SELECT EMPNO,ENAME,JOB,DEPTNO FROM EMP

WHERE JOB IN (‘PRESIDENT’,’MANAGER’,’ANALYST’);
 

This is exactly equivalent to

SELECT EMPNO,ENAME,JOB,DEPTNO FROM EMP

WHERE JOB =‘PRESIDENT’ OR JO’MANAGER’,’ANALYST’;

IN can replace complicated OR operators

And & OR Operators in SQL

Posted on by
Reply

How to and where to use AND and OR Operators in Select Statement

We should remember a fundamental concept. AND restricts further and OR loosens the restrictions in the result set

SELECT ENAME, SAL FROM EMP WHERE DEPTNO=10 AND SAL>1500;

The above statement will display the restricted set of rows whose department no is 10 and SAL is greater than 1500.

If we use OR instead of AND

it will display the rows, where ever there is a department number 10 as well as SAL is greater than 1500