Yes. In oracle 9i and Oracle 10g if you mention
primary key constraint in the create table statement
oracle Automatically creating an index with same
name as constraint
Ex;SY0023456
Thursday, September 30, 2010
Thursday, September 23, 2010
How do you add column and modify its size in oracle?
alter table student add
address varchar2(20);
alter table student MODIFY
address varchar2(40);
address varchar2(20);
alter table student MODIFY
address varchar2(40);
How do you delete duplicate rows using row ID concept.Assume no Primary key in the table
-- deleting duplicate rows
--using row id concept
drop table emp;
create table emp (empno number(6) , ename varchar2(20), sal number(8));
insert into emp values(100,'aaa',4000);
insert into emp values(200,'bbb',1000);
insert into emp values(300,'ccc',5000);
insert into emp values(400,'ddd',3000);
insert into emp values(500,'eee',400);
insert into emp values(100,'aaa',4000);
insert into emp values(200,'bbb',1000);
insert into emp values(300,'ccc',5000);
insert into emp values(400,'ddd',3000);
insert into emp values(500,'eee',400);
select * from emp order by empno;
-- RowID concept... Unique for each row...same row id
-- if rows are same
SELECT min(rowid)
FROM emp
group BY empno,ename,sal;
-- delete min row id. here we need to
-- list columns in group by clause
-- 5 rows deleted
DELETE FROM emp
WHERE rowid not in
(
SELECT MIN(rowid)
FROM emp
GROUP BY empno, ename, sal
) ;
-- 3 rows deleted
select * from emp order by empno;
--using row id concept
drop table emp;
create table emp (empno number(6) , ename varchar2(20), sal number(8));
insert into emp values(100,'aaa',4000);
insert into emp values(200,'bbb',1000);
insert into emp values(300,'ccc',5000);
insert into emp values(400,'ddd',3000);
insert into emp values(500,'eee',400);
insert into emp values(100,'aaa',4000);
insert into emp values(200,'bbb',1000);
insert into emp values(300,'ccc',5000);
insert into emp values(400,'ddd',3000);
insert into emp values(500,'eee',400);
select * from emp order by empno;
-- RowID concept... Unique for each row...same row id
-- if rows are same
SELECT min(rowid)
FROM emp
group BY empno,ename,sal;
-- delete min row id. here we need to
-- list columns in group by clause
-- 5 rows deleted
DELETE FROM emp
WHERE rowid not in
(
SELECT MIN(rowid)
FROM emp
GROUP BY empno, ename, sal
) ;
-- 3 rows deleted
select * from emp order by empno;
How to find top n salaries in Oracle table?
create table emp (empno number(6) primary key, ename varchar2(20), sal number(8));
insert into emp values(100,'aaa',4000);
insert into emp values(200,'bbb',1000);
insert into emp values(300,'ccc',5000);
insert into emp values(400,'ddd',3000);
insert into emp values(500,'eee',400);
-- Using Row Number
SELECT *
FROM (
SELECT * FROM emp ORDER BY sal DESC
)
WHERE ROWNUM <= 3;
-- Using oracle rank() Function
SELECT *
FROM ( SELECT empno,ename,sal,RANK() OVER (ORDER BY sal DESC) sal_rank
FROM emp )
WHERE sal_rank <= 2;
insert into emp values(100,'aaa',4000);
insert into emp values(200,'bbb',1000);
insert into emp values(300,'ccc',5000);
insert into emp values(400,'ddd',3000);
insert into emp values(500,'eee',400);
-- Using Row Number
SELECT *
FROM (
SELECT * FROM emp ORDER BY sal DESC
)
WHERE ROWNUM <= 3;
-- Using oracle rank() Function
SELECT *
FROM ( SELECT empno,ename,sal,RANK() OVER (ORDER BY sal DESC) sal_rank
FROM emp )
WHERE sal_rank <= 2;
Wednesday, September 15, 2010
SQL loader control file example
Assume data file is not delimited file. file contains data
continuously and we know that data length . i.e Fixed-Width Files
we can also use sql loader for Delimited files
LOAD DATA
INFILE '#INFILE#'
APPEND
INTO TABLE TEST_SCORE (
REPORTING_YEAR POSITION(01:02) CHAR,
LAST_NAME POSITION(03:27) CHAR,
FIRST_NAME POSITION(28:43) CHAR,
MI POSITION(44:44) CHAR,
IMP_FILENAME CONSTANT '#INFILE#',
IMP_FILE_DATE DATE "YYYYMMDD" "#DATE#" ,
IMP_FILE_SEQ CONSTANT #INTEGER#
)
'#INFILE#' is the input data file name.
TEST_SCORE is the target table name
you can call sql loader utility using following command
$sqlldr username/password@dbsid control=control_file_name log=log_file_name data=data_file_name
continuously and we know that data length . i.e Fixed-Width Files
we can also use sql loader for Delimited files
LOAD DATA
INFILE '#INFILE#'
APPEND
INTO TABLE TEST_SCORE (
REPORTING_YEAR POSITION(01:02) CHAR,
LAST_NAME POSITION(03:27) CHAR,
FIRST_NAME POSITION(28:43) CHAR,
MI POSITION(44:44) CHAR,
IMP_FILENAME CONSTANT '#INFILE#',
IMP_FILE_DATE DATE "YYYYMMDD" "#DATE#" ,
IMP_FILE_SEQ CONSTANT #INTEGER#
)
'#INFILE#' is the input data file name.
TEST_SCORE is the target table name
you can call sql loader utility using following command
$sqlldr username/password@dbsid control=control_file_name log=log_file_name data=data_file_name
what is Oracle database control file?
Database control files are
binary files containing information about the physical structure of your database.
binary files containing information about the physical structure of your database.
what is SQL Loader?
SQL*Loader is an Oracle-supplied utility that allows you to load data from a flat
file into one or more database tables. That’s it. That’s the sole reason for
SQL*Loader’s existence.
The basis for almost everything you do with SQL*Loader is a file known as the
control file. The SQL*Loader control file is a text file into which you place a
description of the data to be loaded. You also use the control file to tell
SQL*Loader which database tables and columns should receive the data that you
are loading.
file into one or more database tables. That’s it. That’s the sole reason for
SQL*Loader’s existence.
The basis for almost everything you do with SQL*Loader is a file known as the
control file. The SQL*Loader control file is a text file into which you place a
description of the data to be loaded. You also use the control file to tell
SQL*Loader which database tables and columns should receive the data that you
are loading.
What we check while dropping column?
In production, Before dropping any field
on a table, its recommended to check whether
any views are using that fields or not.
for example, As part of encryption if you drop
SSN field on a table without checking any views
then all views shows errors.
So be careful....
on a table, its recommended to check whether
any views are using that fields or not.
for example, As part of encryption if you drop
SSN field on a table without checking any views
then all views shows errors.
So be careful....
Can create a table as with NULL fields?
I am trying to create a table using
create table student as
select
a,
'' b,
c
from student_temp;
This query fails. we can create table in this way using zero
length fields or NULL fields.
To solve this, we generally use NULL place holder while
creating table and then replace that with NULL
once table is created.
create table student as
select
a,
'' b,
c
from student_temp;
This query fails. we can create table in this way using zero
length fields or NULL fields.
To solve this, we generally use NULL place holder while
creating table and then replace that with NULL
once table is created.
Tuesday, September 14, 2010
How companies got named?
Mercedes:
This was actually financier's daughter's name
Adobe:
This came from the name of the riverAdobe Creek that ran behind the house of founder John Warnock
Apple Computers:
http://track.vcommission.com/aff_i?offer_id=774&aff_id=1018&file_id=4057It was the favourite fruit of founder Steve Jobbs. He was three months late for filing a name for the business, and he threatened to call his company Apple Computers if the other colleagues didn't suggest a better name by 5 o'clock that day
CISCO:
It is not an acronym as popularly believed. It is short for San Francisco .
Compaq:
This name was formed by using COMp, for computer and PAQ to denote a small integral object
Corel:
The name was derived from the founder's name Dr. Michael Cowpland. It stands for COwpland Research Laboratory.
Google:
The name started as a joke boasting about the amount of information the search-engine would be able to search. It was originally named 'Googol',a word for the number represented by 1 followed by 100 zeros. After founders - Stanford graduate students Sergey Brin and Larry Pagepresented their project to an angel investor; they received a cheque made out to 'Google'.
Hotmail:
Founder Jack Smith got the idea of accessing e-mail via the web from a computer anywhere in the world. WhenSabeer Bhatia came up with the business plan for the mail service, he tried all kinds of names ending in 'mail' and finally settled for hotmail as it included the letters"html" - the programming language used to write web pages. It was initially referred to as HoTMaiL with selective uppercasing
Hewlett Packard :
Bill Hewlett and Dave Packard tossed a coin to decide whether the company they founded would be called Hewlett-Packardor Packard-Hewlett.
Intel:
Bob Noyce and Gordon Moore wanted to name their new company ' Moore Noyce' but that was already trademarked by a hotel chain so they had to settle for an acronym of INTegrated ELectronics.
Lotus (Notes) :
Mitch Kapor got the name for his company from 'The Lotus Position' or'Padmasana' . Kapor used to be a teacher oftranscendental Meditation of Maharishi Mahesh Yogi.
Microsoft:
Coined by Bill Gates to represent the company that was devoted to MICROcomputer SOFTware. Originally christened Micro-Soft, the '-' was removed later on.
Motorola:
Founder Paul Galvin came up with this name when his company started manufacturing radios for cars. The popular radio company at the time was called Victrola.
Sony:
It originated from the Latin word 'sonus' meaning sound, and 'sonny' a slang used by Americans to refer to a bright youngster.
SUN:
Founded by 4 Stanford University buddies, SUN is the acronym forStanford University Network. Andreas Bechtolsheim built a microcomputer;Vinod Khosla recruited him and Scott McNealy to manufacture computers based on it, and Bill Joy to develop a UNIX-based OS for the computer.
Apache:
It got its name because its founders got started by applying patches to code written for NCSA's httpd daemon.. The result was 'A PAtCHy' server --thus, the name Apache Jakarta (project from Apache):A project constituted by SUN and Apache to create a web server handling servlets and JSPs. Jakarta was name of the conference room at SUN where most of the meetings between SUN and Apache took place.
C:
Dennis Ritchie improved on the B programming language and called it 'New B'.He later called it C. Earlier B was created by Ken Thompson as a revision of the Bon programming language (named after his wife Bonnie).
C++:
Bjarne Stroustrup called his new language 'C with Classes' and then 'newC'. Because of which the original C began to be called 'old C' which was considered insulting to the C community. At this time Rick Mascittisuggested the name C++ as a successor to C.
Java:
Originally called Oak by creator James Gosling, from the tree that stood outside his window, the programming team had to look for a substitute as there was no other language with the same name. Java was selected from a list of suggestions. It came from the name of the coffee that the programmers drank.
LG:
Combination of two popular Koreanbrands Lucky and Goldstar.
Linux:
Linus Torvalds originally used the Minix OS on his system which here placed by his OS. Hence the working name was Linux (Linus' Minix). He thought the name to be too egotistical and planned to name it Freax(free+ freak + x).His friend Ari Lemmke encouraged Linus to upload it to a network so it could be easily downloaded. Ari gave Linus a directory called linux on his FTP server, as he did not like the name Freax.(Linus' parents named him after two-time Nobel Prize winner Linus Pauling) .
Mozilla:
When Marc Andreesen, founder of Netscape, created a browser to replace Mosaic (also developed by him), it was named Mozilla (Mosaic-Killer, Godzilla) .The marketing guys didn't like the name however and it wasre-christenedNetscape Navigator.
Red Hat:
Company founder Marc Ewing was given the Cornell lacrosse team cap (with red and white stripes) while at college by his grandfather. He lost it and had to search for it desperately. The manual of the beta version of Red Hat Linux had an appeal to readers to return his Red Hat if found by anyone!
SAP:
"Systems, Applications, Products in Data Processing", formed by 4 ex-IBM employees who used to work in the 'Systems/Applicatio ns/Projects' group of IBM.
SCO (UNIX):
From Santa Cruz Operation. The company's office was in Santa Cruz .
UNIX:
When Bell Labs pulled out of MULTICS (MULTiplexed Information and Computing System), which was originally a joint Bell/GE/MIT project, KenThompson and Dennis Ritchie of Bell Labs wrote a simpler version of the OS.They needed the OS to run the game Space War which was compiled underMULTICS. It was called UNICS - UNIplexed operating and Computing System by Brian Kernighan. It was later shortened to UNIX.
Xerox:
The inventor, Chestor Carlson, named his product trying to say `dry' (asit was dry copying, markedly different from the then prevailing wet copying). The Greek root `xer' means dry.
Yahoo!:
The word was invented by Jonathan Swiftand used in his book 'Gulliver's Travels' . It represents a person who is repulsive in appearance and action and is barely human. Yahoo! founders Jerry Yang andDavid Filo selected the name because they considered themselves yahoos.
3M:
Minnesota Mining and Manufacturing Company started off by mining the material corundum used to make sandpaper.
This was actually financier's daughter's name
Adobe:
This came from the name of the riverAdobe Creek that ran behind the house of founder John Warnock
Apple Computers:
http://track.vcommission.com/aff_i?offer_id=774&aff_id=1018&file_id=4057It was the favourite fruit of founder Steve Jobbs. He was three months late for filing a name for the business, and he threatened to call his company Apple Computers if the other colleagues didn't suggest a better name by 5 o'clock that day
CISCO:
It is not an acronym as popularly believed. It is short for San Francisco .
Compaq:
This name was formed by using COMp, for computer and PAQ to denote a small integral object
Corel:
The name was derived from the founder's name Dr. Michael Cowpland. It stands for COwpland Research Laboratory.
Google:
The name started as a joke boasting about the amount of information the search-engine would be able to search. It was originally named 'Googol',a word for the number represented by 1 followed by 100 zeros. After founders - Stanford graduate students Sergey Brin and Larry Pagepresented their project to an angel investor; they received a cheque made out to 'Google'.
Hotmail:
Founder Jack Smith got the idea of accessing e-mail via the web from a computer anywhere in the world. WhenSabeer Bhatia came up with the business plan for the mail service, he tried all kinds of names ending in 'mail' and finally settled for hotmail as it included the letters"html" - the programming language used to write web pages. It was initially referred to as HoTMaiL with selective uppercasing
Hewlett Packard :
Bill Hewlett and Dave Packard tossed a coin to decide whether the company they founded would be called Hewlett-Packardor Packard-Hewlett.
Intel:
Bob Noyce and Gordon Moore wanted to name their new company ' Moore Noyce' but that was already trademarked by a hotel chain so they had to settle for an acronym of INTegrated ELectronics.
Lotus (Notes) :
Mitch Kapor got the name for his company from 'The Lotus Position' or'Padmasana' . Kapor used to be a teacher oftranscendental Meditation of Maharishi Mahesh Yogi.
Microsoft:
Coined by Bill Gates to represent the company that was devoted to MICROcomputer SOFTware. Originally christened Micro-Soft, the '-' was removed later on.
Motorola:
Founder Paul Galvin came up with this name when his company started manufacturing radios for cars. The popular radio company at the time was called Victrola.
Sony:
It originated from the Latin word 'sonus' meaning sound, and 'sonny' a slang used by Americans to refer to a bright youngster.
SUN:
Founded by 4 Stanford University buddies, SUN is the acronym forStanford University Network. Andreas Bechtolsheim built a microcomputer;Vinod Khosla recruited him and Scott McNealy to manufacture computers based on it, and Bill Joy to develop a UNIX-based OS for the computer.
Apache:
It got its name because its founders got started by applying patches to code written for NCSA's httpd daemon.. The result was 'A PAtCHy' server --thus, the name Apache Jakarta (project from Apache):A project constituted by SUN and Apache to create a web server handling servlets and JSPs. Jakarta was name of the conference room at SUN where most of the meetings between SUN and Apache took place.
C:
Dennis Ritchie improved on the B programming language and called it 'New B'.He later called it C. Earlier B was created by Ken Thompson as a revision of the Bon programming language (named after his wife Bonnie).
C++:
Bjarne Stroustrup called his new language 'C with Classes' and then 'newC'. Because of which the original C began to be called 'old C' which was considered insulting to the C community. At this time Rick Mascittisuggested the name C++ as a successor to C.
Java:
Originally called Oak by creator James Gosling, from the tree that stood outside his window, the programming team had to look for a substitute as there was no other language with the same name. Java was selected from a list of suggestions. It came from the name of the coffee that the programmers drank.
LG:
Combination of two popular Koreanbrands Lucky and Goldstar.
Linux:
Linus Torvalds originally used the Minix OS on his system which here placed by his OS. Hence the working name was Linux (Linus' Minix). He thought the name to be too egotistical and planned to name it Freax(free+ freak + x).His friend Ari Lemmke encouraged Linus to upload it to a network so it could be easily downloaded. Ari gave Linus a directory called linux on his FTP server, as he did not like the name Freax.(Linus' parents named him after two-time Nobel Prize winner Linus Pauling) .
Mozilla:
When Marc Andreesen, founder of Netscape, created a browser to replace Mosaic (also developed by him), it was named Mozilla (Mosaic-Killer, Godzilla) .The marketing guys didn't like the name however and it wasre-christenedNetscape Navigator.
Red Hat:
Company founder Marc Ewing was given the Cornell lacrosse team cap (with red and white stripes) while at college by his grandfather. He lost it and had to search for it desperately. The manual of the beta version of Red Hat Linux had an appeal to readers to return his Red Hat if found by anyone!
SAP:
"Systems, Applications, Products in Data Processing", formed by 4 ex-IBM employees who used to work in the 'Systems/Applicatio ns/Projects' group of IBM.
SCO (UNIX):
From Santa Cruz Operation. The company's office was in Santa Cruz .
UNIX:
When Bell Labs pulled out of MULTICS (MULTiplexed Information and Computing System), which was originally a joint Bell/GE/MIT project, KenThompson and Dennis Ritchie of Bell Labs wrote a simpler version of the OS.They needed the OS to run the game Space War which was compiled underMULTICS. It was called UNICS - UNIplexed operating and Computing System by Brian Kernighan. It was later shortened to UNIX.
Xerox:
The inventor, Chestor Carlson, named his product trying to say `dry' (asit was dry copying, markedly different from the then prevailing wet copying). The Greek root `xer' means dry.
Yahoo!:
The word was invented by Jonathan Swiftand used in his book 'Gulliver's Travels' . It represents a person who is repulsive in appearance and action and is barely human. Yahoo! founders Jerry Yang andDavid Filo selected the name because they considered themselves yahoos.
3M:
Minnesota Mining and Manufacturing Company started off by mining the material corundum used to make sandpaper.
Subscribe to:
Posts (Atom)