Basic SQL queries and Interview quick Reference
Create Table Queries
Borrower
Loan
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
select * from borrower;
select bid,Name from borrower;
select * from borrower where bid > 702;
select * from borrower where Name != 'Punitha';
select * from borrower where bid in (702,703,706);
select * from borrower where Address Like 'M%' or Name Like '%u';
select * from borrower where bid BETWEEN 700 AND 704;
select * from borrower where bid IS NOT NULL;
select count(*) from borrower;
select count(bid) from borrower;
select * from loan ORDER BY Duration asc;
select Distinct Duration from loan ;
select Min(Interest), Max(Interest) , avg(Interest) from loan;
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;
select Distinct(LoanType), (Select count(*) from borrower where borrower.loanId= loan.loanId) as total from loan;
No comments:
Post a Comment