-
I have a customer table custid,Trade,Amount three fields are there custid dont have unique value, now trade B & S values. I want to Show sum of amount of custid, for both the trade in one line Like cust id,Btrade,Bamount,Strade,Samount1 B 12000 S 5000 This One i tried but problem in thisselect a.Custid,a.TRADEID,sum(a.AMOUNT),b.TRADEID,sum(b.AMOUNT) from custmer a,custmer bwhere a.Custid = b.Custid and A.TRADEID = 'B' and b.TRADEID = 'S' group by a.Custid,a.TRADEID,b.Custid,b.TRADEID
Imran_Javed
- May 9th, 2008
SELECT MIN(b.CUSTID) "cust id", MIN(b.TRADE) "Btrade", SUM(b.AMOUNT) "Bamount", MIN(s.trade) "Strade", MIN(s.amount) "Samount"FROM cust_t b, (SELECT MIN(CUSTID),MIN(TRADE) trade,SUM(AMOUNT) amount FROM cust_t WHERE trade ='s') s WHERE b.trade = 'b';
jamesravid
- Dec 10th, 2006
Hi Try this query,Select custid, 'B' as BTrade, sum(decode(tradeid,'B',amount,0) Bamount 'S' as Strade, sum(decode(tradeid,'S',amount,0) Samountfrom customergroup by custidRegards,James.
-
Using the following Customer, Country and Orders tables: Customer Table c_id c_name c_country_id 1 John 1 Country Table country_id Name 1 US 2 UK Orders Table o_id c_id Qty o_deliver_country_id 1 1 10 1 2 1 20 2
I need the following output in SQLc_id c_name c_country_lives qty o_deliver_country 1 john US 10 US 2 john US 20 UK
Imran_Javed
- May 9th, 2008
SELECT c.c_id, c.c_name , ord.name AS "c_country_lives", ord.qty, ord.name AS "o_deliver_country"FROM customer_c c, (SELECT * &nbs...
vishalb
- Apr 25th, 2008
SELECT X.o_id, X.c_name, X.[Name], X.qty , MT.[name] FROM(SELECT OT.o_id, CT.c_name, CUT.[Name], OT.qty , OT.o_deliver_country_id --CUT.o_deliver_countryFROM Orders OT Join Customer CT on ...
-
Giving two tables:EmpCustEmpName, CustID, EmpIDApril, 1, AApril, 3, AApril, 2, ABruce, 4, BBruce, 5, BCarson, 2, CCarson, 1, CCarson, 3, CDavid, 2, DCustReviewCustID, ReviewDate, EmpID1, 01-05-06, A1, 12-13-05, C2, 07-04-04, A2, 05-21-03, C2, 04-02-03, D3, 10-10-02, A3, 02-01-06, C4, 01-01-06, B5, 12-12-04, BWrite a SQL statement that return each EmpName and LATEST ReviewDate. Result should look like this:EmpName, ReviewDateApril, 01-05-06Bruce, 01-01-06...
vishalb
- Apr 27th, 2008
Tested!SELECT E.EmpName, Max(C.Reviewdate) from EmpCust E Join CustReview C on e.Empid=c.empid group by c.empid, E.EmpName
ratna_ponnapalli
- Apr 11th, 2006
Select a.empname,max(b.reviewdate) from empcust a,custreview b where a.custid=b.custid group by a.empname;
-
What is syntax for dropping a procedure and a function .Are these operations possible?
Drop Procedure procedure_nameDrop Function function_name
-
How to retrieving the data from 11th column to n th column in a table.
-
How SQL Statment is processed?
ritesh_ksingh
- Apr 21st, 2008
To process an SQL statement, a DBMS performs the following five steps: The DBMS first parses the SQL statement. It breaks the statement up into individual words, called tokens, makes sure that the sta...
-
DOS command
How to execute DOS Commands from SQL prompt?
vinodsahani
- Apr 12th, 2008
Either you can type HOST to go into DOS prompt, use DOS commands and then type EXIT to return back.OR, if you want to run only one DOS command you can type HOST Hope this will work for u.
-
What WHERE CURRENT OF clause does in a cursor?
LOOPSELECT num_credits INTO v_numcredits FROM classesWHERE dept=123 and course=101;UPDATE studentsSET current_credits=current_credits+v_numcreditsWHERE CURRENT OF X;END LOOPCOMMIT;END;
aravindpurohit
- Apr 4th, 2008
When referencing the current row from an explicit cursor, we use 'WHERE CURRENT OF'. it allows ...
g_sidhu
- Feb 6th, 2008
The WHERE CURRENT OF clause is used to reference the current row from an explicit cursor.
-
How do you generate prime numbers in sql not in plsql
saradajasti
- Mar 27th, 2008
Here CNT is a column name SELECT CNT FROM table_nameWHERE MOD(CNT,CNT) = 0AND MOD(CNT,2) != 0AND MOD(CNT,3) != 0AND MOD(CNT,5) != 0AND MOD(CNT,7) != 0AND MOD(SQRT(CNT),ROUND(SQRT(CNT))) != 0ORDER BY CNT
saradajasti
- Mar 26th, 2008
SELECT CNT FROM table_nameWHERE MOD(CNT,CNT) = 0AND MOD(CNT,2) != 0AND MOD(CNT,3) != 0AND MOD(CNT,5) != 0AND MOD(CNT,7) != 0ORDER BY CNT
-
Explain Plan and TKPROFF
What is explain plan and what is tkproff?
-
Between Operator for Date Fields
What is the disadvantage of using "BETWEEN" for date feilds
-
Access data from Database
In which manner the SQL commands access the data from database?
chandu77
- Mar 17th, 2008
Accessing data from a database depends on its implementation.consider the follwing statement select empname from emp where empid=100;(Oracle)1:first the server process of oracle checks wheth...
-
Types of Databases
How many types of databases are there? What are they?
akavitha
- Feb 28th, 2008
These days when we talk abt database we are mainly talking about 2 databases namely : Analytical DatabaseOperational DatabaseAnalytical Database : Like OLTP( On Line Analytical Processing) are p...
-
What is the difference between ROWNUM and ROWID
sampra
- Feb 26th, 2008
the ROWNUM is a number(like serial no) which is dynamically changes.but the ROWID does not change.Because it is having the unique address,which is generated by the oracle
g_sidhu
- Feb 7th, 2008
Rowid: Hexadecimal string representing the unique address of a row in its table. This datatype is primarily for values returned by the ROWID pseudocolumn. Rownum: For each row returned by ...
-
How to see the existing constraints in a table?
safdar
- Feb 14th, 2008
HI I AM SAFDAR DOGAR create table dept1( deptno number(10) constraints pk_deptno primary key, dname varchar2(20), loc varchar2(20 ));desc user_constraints;select constraint_name,constraint_type from user_constraints where table_name='DEPT1';AM I RIGHT
-
What is the Difference between stored procedures and external procedures?
sampra
- Feb 26th, 2008
An external procedure is simply a program unit that is not written in Oracle’s SQL or PL/SQL and resides outside of the database. External procedures must be stored as shared libraries at the op...
g_sidhu1
- Feb 21st, 2008
An external procedure is simply a program unit that is not written in Oracle’s SQL or PL/SQL and resides outside of the database. External procedures must be stored as shared libraries at the operating system level. In a Windows NT environment, the procedure would be compiled into a DLL.
-
Non Cluster Index
1. How many non cluster index we have use in one table?2. How we set the priority in the cluster index?3. What is co-releated sub query?
-
There is a table with Pluks(‘plk’) and Zinks (‘zin’). Each Pluk onlyresides on a single Zink and each Zink may contain multiple Pluks. Howdo you find how many Pluks are on each and every Zink? Please provide SQL.
-
What is the difference between SQL standard joins and Oracle propreitary joins
g_sidhu
- Feb 13th, 2008
The Oracle9i database offers join syntax that is SQL: 1999 Compliant. Prior to release 9i, the join syntax was different from the ANSI standards. The new SQL: 1999 Compliant join syntax does not offer any performance benefits over the Oracle proprietary join syntax that existed in prior releases.
-
What is the data type of the column of Dual table?
g_sidhu
- Feb 13th, 2008
The DUAL table is owned by the user SYS and can be accessed by all users. It contains one column, DUMMY, and one row with the value X. The DUAL table is useful when you want to return a value once onl...
kowmudiswarna
- Sep 13th, 2007
Hi,The data type of column of dual table is character data type i.e varchar2
Oracle SQL Interview Questions
Ans