SQL Flashcards
Why is a primary key used ?
- Maintain primary key integrity - like unique, not null
2. Faster lookup due to index
Why is foreign key used ?
to maintain referential integrity
What is the diff. b/w INTERSECT and inner join
Both will give matching rows, but intersect will remove duplicates ,
so intersect and join with unique/distinct are the same
What is the diff. b/w inner join and join
no diff
What is diff b/w union and union all
union all doesn’t remove duplicates
Both show all rows in both tables
Diff b/w cartesian product and union ?
cartesian shows m*n rows, union all shows m+n rows , union shows distinct (m+n) rows
Diff b/w truncate and delete ?
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
What is a trigger?
Simple procedure which runs automatically on an event.
For eg. there might be an insert/update/delete event and a trigger based on it.
What is normalization?
Data is not duplicated in multiple tables, instead foreign keys are used to maintain referential integrity
Used to prevent insert/update/delete anomalies.
Diff b/w clustered and non-clustered index ?
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.
Write an SQL query to find the position of the alphabet (‘a’) in the first name column ‘Amitabh’ from Worker table.:
Select INSTR(FIRST_NAME, ‘a’) from Worker where FIRST_NAME = ‘Amitabh’
What if i need to combine 2 columns ?
Select CONCAT(FIRST_NAME, ‘ ‘, LAST_NAME) AS ‘COMPLETE_NAME’ fromWorker;
How do i search all log entries after current time?
Depends on database.
but in sql server , can use > getdate() or current_timestamp etc.
What is an ER diagram?
Entity Relationship diagram which shows cardinality or relationship like 1:m 1:1 or 1:m
If cardinality is 1:m which table will have the foreign key ?
If cardinality is m:m, which table wil have the foreign key?
1: m - m table will have FKs of 1 table
m: m - association table which will have FKs from both tables
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
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)
How do i add a key/change column etc.
How do i add a new column
alter table column/index
These are all DDLs, so no commit / rollback is reqd.
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
select * from SALARY
where salary in (
select max(SALARY)
from SALARY)
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
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
