• Q1. What is the difference between where and having clause?

In SQL Where filters data on lowest row level. Having filters data after group by has been performed so it filters on "groups"

• Q2. What are Primary Keys and Foreign Keys?

Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental aspect of all keys and constraints. A table can have only one primary key. Foreign keys are a method of ensuring data integrity and manifestation of the relationship between tables.

• Q3. Difference between Function & Procedure?

Function is a self-contained program segment, function will return a value but procedure not. Procedure is sub program will perform some specific actions.

• Q4. Difference between Varchar and Varchar2?

The difference between Varchar and Varchar2 is both are variable length but only 2000 bytes of character of data can be store in varchar where as 4000 bytes of character of data can be store in varchar2.

• Q5. Write a query to Select the nth highest rank from the table?

Select * from tab t1 where 2=(select count (distinct (t2.sal)) from tab t2 where t1.sal<=t2.sal)

• Q6. Difference between GROUP BY & ORDER BY?

Group by controls the presentation of the rows, order by controls the presentation of the columns for the results of the SELECT statement. SELECT "col_nam1", SUM("col_nam2") FROM "tab_name" GROUP BY "col_nam1" SELECT "col_nam" FROM "tab_nam" [WHERE "condition"] ORDER BY "col_nam" [ASC, DESC]

• Q7. Write a query to remove duplicate records in a table?

DELETE FROM tableName WHERE ID NOT IN ( SELECT MAX(ID) FROM tableName GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3 );

• Q8. What is Trigger?

A database trigger is procedural code that is automatically executed in response to certain events on a particular table in a database.

• Q9. What Is Commit And Rollback In Sql?

Commit and Rollback are transaction statements that are used in database access; they can also be called Data Control Language for SQL (so you may see it as SQL DCL). A Commit statement does what it says, and commits all the changes made to data that have been made during the current transaction; a Rollback statement, again does what its name implies, and rolls back, or rescinds, all changes to the current transaction.

• Q10. What are joins? Inner join & outer join?

By using joins, you can retrieve data from two or more tables based on logical relationships between the tables Inner Join: - returns all rows from both tables where there is a match. Outer Join: - outer join includes rows from tables when there are no matching values in the tables. LEFT JOIN or LEFT OUTER JOIN The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table. RIGHT JOIN or RIGHT OUTER JOIN. A right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table. FULL JOIN or FULL OUTER JOIN. A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.