|
|
-
|
|
Let Us Learn Oracle - Part 46 of N [ UID and USER function of Oracle ]
Uid
Purpose: It is the user id and helps to find the id of the user who is currently logged in. It returns an integer value
SQL> Select Uid From Dual;
UID
----------
54
</pre>
User
Purpose: It helps......
|
|
-
|
|
Aim: A query to show which employees works for which department
Option 1: Using String_Agg function
Purpose: This values helps to concatenate the input values into a string, separated by delimiter. We can pass any delimiter in this function
Syntax: String_Agg(String_Values, delimiter)
Select ......
|
|
-
|
|
Let Us Learn Oracle - Part 34 of N [ Dump function of Oracle ]
It extracts the column details of the column from the table.
Select
BelongsTo,Dump(BelongsTo) "Dump BelongsTo",
PlayerId,Dump(PlayerId) "Dump PlayerId" ,
DOB,Dump(DOB) "Dump DOB"
From tblPlayers;
/* Result */
B......
|
|
-
|
|
Let Us Learn Oracle - Part 31 of N [ RollUp function of Oracle ]
Purpose: Calculates multiple level of subtotals across a specified group of dimensions.It also calculates the grand total.
Select DeptName,BelongsTo, Count(*) "Departmentwise Employee", Avg(Salary) "Deptwise Avg Sal"
From tblEmpl......
|
|
-
|
|
Let Us Learn Oracle - Part 29 of N [ Collect function of Oracle 10g ]
Introduce in Oracle 10g,this function comes under the category of Collection function. It helps to aggregate data into a collection. This function takes a column of any type as it's input argument and creates a nested table f......
|
|
-
|
|
WITH ModCte as
(
SELECT
RowsValues
,Col
,DECODE(MOD(RowsValues,Col),0,0,1) AS ModVal
FROM
(
SELECT RowsValues,Col
FROM tblCols
CROSS JOIN tblRows
)
)
SELECT RowsValues,
CASE WHEN [1]= 0 THEN 'X' ELSE '' END [1]
,CASE WH......
|
|
-
|
|
Let Us Learn Oracle - Part 21 of N [ RowNum in Oracle ]
Source table: tblEmployee
It is a pseudo column that generates sequences in the result set starting from 1 and is evaluated BEFORE the execution of ORDER BY clause and AFTER records are selected.
SQL> Select RowNum,EmpId,EmpName,Salary,D......
|
|
-
|
|
The tables that will participate in the demonstration
tblPlayers
tblStudents
tblDept
tblEmployee
Script for tblPlayers
CREATE TABLE tblPlayers
(
PlayerID INT NOT NULL,
PlayerFirstName VARCHAR(10),
PlayerLastName VARCHAR(10),
BelongsTo VARCHAR(10),
DOB varchar(20),
Fee......
|
|
-
|
|
Let Us Learn Oracle - Part 17 of N [ Set Operators in Oracle ]
Like Sql Server, Oracle has 4 Set Operators
Union
Union All
Intersect
Minus ( Except in Sql Server)
Let us create two tables Table A
Create table TableA(ID int);
Insert Into TableA(ID) Values(10);
Insert Into TableA......
|
|
-
|
|
Select PLAYERID,Concat(PLAYERFIRSTNAME,Concat(' ' , PLAYERLASTNAME)) PlayerName,Coalesce(FeePerMatch,100) From tblplayers Order By PLAYERID;
PLAYERID PLAYERNAME COALESCE(FEEPERMATCH,100)
---------- --------------------- -------------------------
1 A Raman ......
|
|