SQL

Q: What is the difference between primary key and unique key?

Ans:

Both primary and unique keys carry unique values but a primary key cannot have a null value, while a unique key can. 

In a table, there cannot be more than one primary key, but there can be multiple unique keys.

CREATE TABLE Employee (

ID int NOT NULL,

Employee_name varchar(255) NOT NULL,

Employee_designation varchar(255),

Employee_Age int,

UNIQUE(ID));

Q: How would you find the second highest salary from the following table?

Ans:

select max(e_salary) from employee where e_salary not in (select max(e_salary) from employee)

Q: How to copy columns from one table to another table?

Ans:

Copy all columns from one table to another table:

INSERT INTO table2

SELECT * FROM table1

WHERE condition;

Copy only some columns from one table into another table:

INSERT INTO table2 (column1, column2, column3, ...)

SELECT column1, column2, column3, ...

FROM table1

WHERE condition;

Q: How to Querying Two Tables For Duplicate Values in SQL?

Ans:

SELECT demo_table1.ID

FROM demo_table1, demo_table2

WHERE demo_table1.ID=demo_table2.ID;

Q: How to find Duplicate Values in table in SQL?

Ans:

//on one column

SELECT OrderID, COUNT(OrderID)

FROM Orders

GROUP BY OrderID

HAVING COUNT(OrderID) > 1

//on multiple columns

SELECT OrderID, ProductID, COUNT(*)

FROM OrderDetails

GROUP BY OrderID, ProductID

HAVING COUNT(*) > 1

Q:How delete duplicate records in a table in sql ?

Ans:

We can use the rowid, a locator that specifies where Oracle stores the row. Because the rowid is unique so that we can use it to remove the duplicates rows.

DELETE  

FROM  

Vegetables  

WHERE  

rowid NOT IN  

(  

SELECT  

MIN(rowid)  

FROM  

vegetables  

GROUP BY  

VEGETABLE_ID,  

VEGETABLE_NAME,  

color  

  );  

Q: How to Create a Table from an Existing Table in SQL?

Ans:

CREATE TABLE NEW_TABLE_NAME AS SELECT [ column1, column2...columnN ] FROM EXISTING_TABLE_NAME [ WHERE ]