SQL

Question 1

Why is a primary key used ?

Answer 1

1. Maintain primary key integrity - like unique, not null

2. Faster lookup due to index

Question 2

Why is foreign key used ?

Answer 2

to maintain referential integrity

Question 3

What is the diff. b/w INTERSECT and inner join

Answer 3

Both will give matching rows, but intersect will remove duplicates ,
so intersect and join with unique/distinct are the same

Question 4

What is the diff. b/w inner join and join

Answer 4

no diff

Question 5

What is diff b/w union and union all

Answer 5

union all doesn’t remove duplicates

Both show all rows in both tables

Question 6

Diff b/w cartesian product and union ?

Answer 6

cartesian shows m*n rows, union all shows m+n rows , union shows distinct (m+n) rows

Question 7

Diff b/w truncate and delete ?

Answer 7

truncate is a DDL. Doesn’t need commit hence doesn’t need transaction log, thus much faster.
Any DML needs transaction log as it can be rolled back

Question 8

What is a trigger?

Answer 8

Simple procedure which runs automatically on an event.

For eg. there might be an insert/update/delete event and a trigger based on it.

Question 9

What is normalization?

Answer 9

Data is not duplicated in multiple tables, instead foreign keys are used to maintain referential integrity
Used to prevent insert/update/delete anomalies.

Question 10

Diff b/w clustered and non-clustered index ?

Answer 10

Each table can only have 1 clustered index, multiple non-clustered indices.

Clustered index is physically stored on the database FS, so there can only be one. Usually with the PK.

Question 11

Write an SQL query to find the position of the alphabet (‘a’) in the first name column ‘Amitabh’ from Worker table.:

Answer 11

Select INSTR(FIRST_NAME, ‘a’) from Worker where FIRST_NAME = ‘Amitabh’

Question 12

What if i need to combine 2 columns ?

Answer 12

Select CONCAT(FIRST_NAME, ‘ ‘, LAST_NAME) AS ‘COMPLETE_NAME’ fromWorker;

Question 13

How do i search all log entries after current time?

Answer 13

Depends on database.

but in sql server , can use > getdate() or current_timestamp etc.

Question 14

What is an ER diagram?

Answer 14

Entity Relationship diagram which shows cardinality or relationship like 1:m 1:1 or 1:m

Question 15

If cardinality is 1:m which table will have the foreign key ?
If cardinality is m:m, which table wil have the foreign key?

Answer 15

1: m - m table will have FKs of 1 table
m: m - association table which will have FKs from both tables

Question 16

Lets write simple pseudo code

• create a table
• create a primary key
• create an index

If this is a 1:m relationship -
create the m table

Answer 16

create table test  (
col1 primary key integer, 
 address varchar(20) etc.. 
 timestamp 
)

create table test1  (
col1 primary key integer, 
col2  integer, 
FOREIGN KEY (col2) REFERENCES table1(col1)

Question 17

How do i add a key/change column etc.

How do i add a new column

Answer 17

alter table column/index

These are all DDLs, so no commit / rollback is reqd.

Question 18

I have one simple table - SALARY
Name | Salary 
A| 10
B|2
C|5
D|10

I want to find the names of the people who have max SALARY - should return A|10 and D|10

Answer 18

select * from SALARY
where salary in (
select max(SALARY)
from SALARY)

Question 19

PRODUCT table 
ID | ProductName
1 | Product1
2 | Product2
3 | Product3
4 | Product4

SYNONYM
Product_ID|Synonym|TYpeofSynonym
1 | CUS | P1Cus
1 | GSN | P1GSN
1 | JSN | P1JSN
2 | CUS | P2Cus
2 | GSN | P2GSN
5 | GSN | P2GSN
3 | CUS | P3GSN

Q1. What is the cardinality ?
Q2.Which products have at least 2 synonym ?
Q3. Which products have 0 synonyms
Q4. Which products have the most number of synonyms ?
For eg. i should get
1|Product1|3 as the ID|ProductName|NumberofSynonyms

Answer 19

select p.ID, count(*)
from PRODUCT p 
join SYNONYM  s on p.ID= s.Product_id 
group by p.ID 
having count(*) >1 

– see in below query. count() is not on the select clause, that’s ok , but its not possible to have count() without the group by

select p.ID, p.NAME
from PRODUCT p 
LEFT join SYNONYM  s on p.ID= s.Product_id 
group by p.ID ,p.NAME
having count(*) = 0