SQL queries and Interview quick Reference

Basic SQL queries and Interview quick Reference


Create Table Queries 


Borrower


bid

Name

Ssn

Address

loanId

701

Ram

786541019

Sunnyvale

1001

702

Bala

886541019

Mountainview

1001

703

Punitha

986541019

SantaClara

1001

704

Naga

286541019

SantaCruz

1006

705

Biju

386541019

SFO

1002

706

Ankit

486541019

NorthCalorina

1006


Loan


loanId

LoanType

PrincipalAmt

Interest

Duration

1001

Personal

20,000

23%

5Years

1002

Personal

20,000

16%

3Years

1003

SmallBusiness

60,000

10%

2Years

1004

Auto

30,000

12%

5Years

1005

Gold

10,000

19%

5Years

1006

Medical

20,000

12%

4Years



CREATE TABLE borrower(

  bid INTEGER PRIMARY KEY,

  Name Varchar(50) NOT NULL,

  Ssn INTEGER NOT NULL,

  Address Varchar(50) NULL,

  loanId INTEGER NULL

);


CREATE TABLE loan(

  loanId INTEGER PRIMARY KEY,

  LoanType Varchar(50) NOT NULL,

  PrincipalAmt INTEGER NOT NULL,

  Interest Varchar(50) NULL,

  Duration INTEGER NULL

);



Insert Query


insert into loan (loanId,LoanType,PrincipalAmt,Interest,Duration) 

values 

(1001,'Personal','20,000','23%','5Years'),

(1002,'Personal','20,000','16%','3Years'), 

(1003,'SmallBusiness','60,000','10%','2Years'), 

(1004,'Auto','30,000','12%','5Years'),

(1005,'Gold','10,000','19%','5Years'), 

(1006,'Medical','20,000','12%','4Years')



insert into borrower (bid,  Name,  Ssn, Address,  loanId) values

(701,'Ram','786541019','Sunnyvale','1001'),

(702,'Bala','886541019','Mountainview','1001'), 

(703,'Punitha','986541019','SantaClara','1001'), 

(704,'Naga','286541019','SantaCruz','1006'),

(705,'Biju','386541019','SFO','1002'), 

(706,'Ankit','486541019','NorthCalorina','1006');



Select Queries - Different Usages

  1. select *  from borrower;

  2. select bid,Name from borrower;

  3. select * from borrower where bid > 702;

  4. select * from borrower where Name != 'Punitha';

  5. select * from borrower where bid in (702,703,706);

  6. select * from borrower where Address Like 'M%' or Name Like '%u';

  7. select * from borrower where bid BETWEEN 700 AND 704;

  8. select * from borrower where bid IS NOT NULL;

  9. select count(*) from borrower;

  10. select count(bid) from borrower;

  11. select * from loan ORDER BY Duration asc;

  12. select Distinct Duration from loan ;

  13. select Min(Interest), Max(Interest) , avg(Interest) from loan;

  14. select sum(PrincipalAmt) from loan;


Inner Join 

select * from borrower join loan on loan.loanId=borrower.loanId;

Left Join

select * from loan left join borrower on borrower.loanId= loan.loanId;

Right join

select * from loan right join borrower on borrower.loanId= loan.loanId;


Interview Questions

Question 1 

1) Get the performance of each LoanType  from borrower table :- Mean get count of LoanType
taken by borrowers .

select  Distinct(LoanType), (Select count(*) from borrower where borrower.loanId= loan.loanId) as total from loan;










No comments:

Post a Comment