1.Can foreign key be null or duplicate?
2.What are the NVL and the NVL2 functions in SQL? How do they differ?
3.
SELECT * FROM Employees-- fetched 10 records
Now below query is executed, What will be result?
BEGIN TRAN
TRUNCATE TABLE Employees
ROLLBACK
SELECT * FROM Employees
Answer:
This query will return 10 records as TRUNCATE was executed in the transaction. TRUNCATE does not itself keep a log but BEGIN TRANSACTION keeps track of the TRUNCATE command.
4. ID
1
2
3
4
5
Select SUM(1) FROM "Test"."EMP"; 5
Select SUM(2) FROM "Test"."EMP"; 10
Select SUM(3) FROM "Test"."EMP"; 15
5. How do you get the last id without the max function?
6. What is the difference between IN and EXISTS?
IN:
Works on List result set
Doesn’t work on subqueries resulting in Virtual tables with multiple columns
Compares every value in the result list
Performance is comparatively SLOW for larger resultset of subquery
EXISTS:
Works on Virtual tables
Is used with co-related queries
Exits comparison when match is found
Performance is comparatively FAST for larger resultset of subquery
7. Difference between TRUNCATE and DELETE?
8. Triggers on Table? Max number of triggers:12
9. Materialised view and simple view?
12. Difference between Commit and Savepoint
13. Difference in Procedure and a Function
14. Difference between ROLLBACK and ROLLBACK TO
15. SCD 2 using Merge Statement
16. Difference between CLustered and Non Clustered Index
17. Scenario 1
Data:
|
Table A |
Table B |
|
ID |
ID |
|
1 |
1 |
|
1 |
1 |
|
1 |
1 |
|
1 |
|
Left Join:12 rows
Right Join:12 rows
Full Join:12 rows
Inner Join:12 rows
|
Table A |
Table B |
|
ID |
ID |
|
1 |
1 |
|
1 |
1 |
|
1 |
1 |
|
1 |
null |
Left Join: 12 rows
Right Join: 13 rows
Outer Join: 13 Rows
18. Scenario
Data:
|
Table A |
Table B |
|
ID |
ID |
|
1 |
1 |
|
2 |
2 |
|
3 |
3 |
|
4 |
null |
Q:select id from t1 where id not in (select id from t2);
A:null
No comments:
Post a Comment