 |
| image source on Google |
This
Post totally related to most frequently asking Java Persons Interview but
related to Database side. Please see these and we have any better answers these
questions please mail me so that I can update here.
Q)
How to create table from existing table in oracle?
R) You can
also create a table from an existing table by copying the existing table's columns.
It is important to note that when creating a table in this way, the new table
will be populated with the records from the existing table
Syntax #1 - Copying
all columns from another table
The syntax for
CREATING a table by copying all columns from another table is:
CREATE TABLE new_table
AS (SELECT * FROM old_table);
For Example:
CREATE TABLE suppliers
AS (SELECT *
FROM companies
WHERE id > 1000);
Syntax #2 - Copying
selected columns from another table
The syntax for CREATING a table by
copying selected columns from another table is:
CREATE TABLE new_table
AS (SELECT column_1, column2, ... column_n FROM old_table);
For Example:
CREATE TABLE suppliers
AS (SELECT id, address, city, state, zip
FROM companies
WHERE id > 1000);
Syntax #3 - Copying
selected columns from multiple tables
The syntax for CREATING a table by
copying selected columns from multiple tables is:
CREATE TABLE new_table
AS (SELECT column_1, column2, ... column_n
FROM old_table_1, old_table_2, ... old_table_n);
For Example:
CREATE TABLE suppliers
AS (SELECT companies.id, companies.address, categories.cat_type
FROM companies, categories
WHERE companies.id = categories.id
AND companies.id > 1000);
Q)
How to delete duplicates from the table?
R)
1. Using
MIN(rowid) : The most common method of removing duplicate rows.
DELETE FROM tbl_test
WHERE ROWID NOT IN (SELECT MIN (ROWID)
FROM tbl_test
GROUP BY ser_no, fst_nm, deptid, cmnt);
Note: This will take hours & hours if
the table is large (records in million).
2. Using
MIN(rowid) & Join: More or
less the same as first one
DELETE FROM tbl_test t
WHERE t.ROWID NOT IN (SELECT MIN (b.ROWID)
FROM tbl_test b
WHERE b.ser_no = t.ser_no
AND b.fst_nm = t.fst_nm
AND b.deptid = t.deptid
AND b.cmnt = t.cmnt);
3. Using Subquery: This is an
interesting one
DELETE FROM tbl_test
WHERE ser_no IN (SELECT ser_no FROM tbl_test GROUP BY ser_no,
fst_nm, deptid, cmnt HAVING COUNT (*) > 1)
AND fst_nm IN (SELECT fst_nm FROM tbl_test GROUP BY ser_no,
fst_nm, deptid, cmnt HAVING COUNT (*) > 1)
AND deptid IN (SELECT deptid FROM tbl_test GROUP BY ser_no,
fst_nm, deptid, cmnt HAVING COUNT (*) > 1)
AND cmnt IN (SELECT cmnt FROM tbl_test GROUP BY ser_no,
fst_nm, deptid, cmnt HAVING COUNT (*) > 1)
AND ROWID NOT IN (SELECT MIN (ROWID)
FROM tbl_test
GROUP BY ser_no, fst_nm, deptid, cmnt
HAVING COUNT (*) > 1)
Note: A complicated way of performing the
same task. Not efficient.
4. Using Nested Subqueries:
DELETE FROM tbl_test a
WHERE (a.ser_no, a.fst_nm, a.deptid, a.cmnt) IN (SELECT b.ser_no, b.fst_nm,
b.deptid, b.cmnt FROM tbl_test b
WHERE a.ser_no = b.ser_no
AND a.fst_nm = b.fst_nm
AND a.deptid = b.deptid
AND a.cmnt = b.cmnt
AND a.ROWID > b.ROWID);
Note: Will work but for large tables, this
is not efficient.
5. Using Analytic Fucntions:
DELETE FROM tbl_test
WHERE ROWID IN (
SELECT rid
FROM (SELECT ROWID rid,
ROW_NUMBER () OVER (PARTITION BY ser_no, fst_nm, deptid,
cmnt ORDER BY ROWID) rn
FROM tbl_test)
WHERE rn <> 1);
Note: This is by far one of the best
solutions if the table is really really large. Using the invaluable power of
Analytics.
6. CREATE-DROP-RENAME: This one is a more appropriate
solution in terms of resource usage in the sense that if we have a really
large table, then with delete option we are generating a huge amount of UNDO
information.(if we want to rollback for any reason). Even worst, the rollback
segment may not be large enough to hold your UNDO information and
give error. CTAS comes handy in this case.
Step 1.
Step 1.
CREATE TABLE tbl_test1 NOLOGGING
AS
SELECT tbl_test .*
FROM tbl_test tbl_test
WHERE ROWID IN (SELECT rid
FROM (SELECT ROWID rid, ROW_NUMBER() OVER (PARTITION BY
ser_no, fst_nm, deptid, cmnt ORDER BY ROWID) rn
FROM tbl_test)
WHERE rn=1);
Step 2.
DROP TABLE tbl_test; --drop the original table with lots of duplicate
Step 3.
RENAME tbl_test1 TO tbl_test; -- your original table without duplicates.
Q)
How to find highest salary in the table?
R)
Simple
solution is
Select sal from (Select rownum as salindex,
sal from (Select distinct sal from emp order by sal desc)) where
salIndex=&n
Just substitute the value of n at run time
and get the nth max salary.
Q) I have a table in Oracle called orders which has the following fields: order_no,
customer, and amount. I need a query that will return the customer who has
ordered the highest total amount?
R)
The following SQL should return the customer with the
highest total amount in the orders table.
select query1.*
FROM (SELECT customer, Sum(orders.amount) AS total_amt
FROM orders
GROUP BY orders.customer) query1,
(SELECT max(query2.total_amt) as highest_amt
FROM (SELECT customer, Sum(orders.amount) AS total_amt
FROM orders
GROUP BY orders.customer) query2) query3
where query1.total_amt = query3.highest_amt;
Note: similar kind of another question for understanding
Q) I’ve got a table named Scoring with two fields - Name and Score. What I
want to get is the highest score from the table and the name of the player.
R: The following SQL SELECT statement
should work:
SELECT Name, Score
FROM Scoring
WHERE Score = (select Max (Score) from Scoring);
Shortly I can add many more question here please tune once again.
Post a Comment
Post a Comment