SQL Important Queries.....................

 

1) How can we find the duplicates in the sql table :

Duplicate sql query

SELECT column_nameFROM table
GROUP BY column_nameHAVING COUNT(*) > 1

Example.
 select inventory_item_id,count(inventory_item_id)
from mtl_system_items_b
where inventory_item_id = 114785
group by inventory_item_id
having count(inventory_item_id) >1  

Here's a handy query for finding duplicates in a table. Suppose you want to find all email addresses in a table that exist more than once:
SELECT email,
 COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )
You could also use this technique to find rows that occur exactly once:
SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )

simple
------
select job_number, count(*)
from jobsgroup by job_numberhaving count(*) > 1;


another way
===========SELECT *
FROM TABLE AWHERE EXISTS (
  SELECT 1 FROM TABLE
  WHERE COLUMN_NAME = A.COLUMN_NAME
  AND ROWID < A.ROWID)
--------------------------------------------------------------------------------------------------------

2) Insert/Insert all command using select statement.

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE city = 'Newark';
or

Frequently Asked Questions

---

Question: I am setting up a database with clients. I know that you use the "insert" statement to insert information in the database, but how do I make sure that I do not enter the same client information again?
Answer: You can make sure that you do not insert duplicate information by using the EXISTS condition.
For example, if you had a table named clients with a primary key of client_id, you could use the following statement:

INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers
WHERE not exists (select * from clients
where clients.client_id = suppliers.supplier_id);

This statement inserts multiple records with a subselect.
If you wanted to insert a single record, you could use the following statement:

INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE not exists (select * from clients
where clients.client_id = 10345);

The use of the dual table allows you to enter your values in a select statement, even though the values are not currently stored in a table.
Learn more about the EXISTS condition.

---

Question: How can I insert multiple rows of explicit data in one SQL command in Oracle?
Answer: The following is an example of how you might insert 3 rows into the suppliers table in Oracle.

INSERT ALL
   INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
   INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
   INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google')
SELECT * FROM dual;