CREATE DATABASE LOANS;
USE LOANS;
CREATE TABLE Loan_Accounts (
AccNo INT,
Cust_Name VARCHAR(50),
Loan_Amount INT,
Installments INT,
Int_Rate DECIMAL(5,2),
Start_Date DATE,
Interest DECIMAL(10,2)
);
INSERT INTO Loan_Accounts VALUES
(1, 'R.K. Gupta', 300000, 36, 12.00, '2009-07-19', 0),
(2, 'S.P. Sharma', 500000, 48, 10.00, '2008-03-22', 0),
(3, 'K.P. Jain', 300000, 36, NULL, '2007-08-03', 0),
(4, 'M.P. Yadav', 800000, 60, 10.00, '2008-06-12', 0),
(5, 'S.P. Sinha', 200000, 36, 12.50, '2010-03-01', 0),
(6, 'P. Sharma', 700000, 60, 12.50, '2008-05-06', 0),
(7, 'K.S. Dhall', 500000, 48, NULL, '2008-05-03', 0);
-- Simple Select
SELECT * FROM Loan_Accounts;
SELECT AccNo, Cust_Name, Loan_Amount FROM Loan_Accounts;
-- Conditional Select using WHERE
SELECT * FROM Loan_Accounts WHERE Installments < 40;
SELECT AccNo, Loan_Amount FROM Loan_Accounts WHERE Start_Date < '2009-04-01';
SELECT Int_Rate FROM Loan_Accounts WHERE Start_Date > '2009-04-01';
-- NULL checks
SELECT * FROM Loan_Accounts WHERE Int_Rate IS NULL;
SELECT * FROM Loan_Accounts WHERE Int_Rate IS NOT NULL;
-- Logical Operators
SELECT Cust_Name, Loan_Amount FROM Loan_Accounts WHERE Installments <> 36;
SELECT Cust_Name, Loan_Amount FROM Loan_Accounts WHERE Loan_Amount < 500000 OR
Int_Rate > 12;
SELECT * FROM Loan_Accounts WHERE Loan_Amount BETWEEN 400000 AND 500000;
-- IN Operator
SELECT Cust_Name, Loan_Amount FROM Loan_Accounts WHERE Installments IN (24, 36,
48);
-- BETWEEN
SELECT * FROM Loan_Accounts WHERE Int_Rate BETWEEN 11 AND 12;
-- LIKE
SELECT AccNo, Cust_Name, Loan_Amount FROM Loan_Accounts WHERE Cust_Name LIKE '%a%';
SELECT AccNo, Cust_Name, Loan_Amount FROM Loan_Accounts WHERE Cust_Name NOT LIKE
'%P%';
-- ORDER BY
SELECT * FROM Loan_Accounts ORDER BY Loan_Amount ASC, Start_Date DESC;
-- UPDATE / DELETE / ALTER
UPDATE Loan_Accounts SET Int_Rate = 11.50 WHERE Int_Rate IS NULL;
DELETE FROM Loan_Accounts WHERE Start_Date < '2008-01-01';
ALTER TABLE Loan_Accounts ADD Category CHAR(1);
-- STUDENTS & SPORTS Tables
CREATE TABLE STUDENTS (
AdmNo INT,
Name VARCHAR(50),
Class INT,
Sec CHAR(1),
RNo INT,
Address VARCHAR(100),
Phone BIGINT
);
INSERT INTO STUDENTS VALUES
(1271, 'Utkarsh Mandaan', 12, 'C', 1, 'C-32, Punjabi Bagh', 4356154),
(1324, 'Naresh Sharma', 10, 'A', 1, '31, Mohan Nagar', 435654),
(1325, 'Md. Yusuf', 10, 'A', 2, '12/21, Chand Nagar', 145654),
(1328, 'Sumedha', 10, 'B', 23, '59,Moti Nagar', 4135654),
(1364, 'Subya Akhtar', 11, 'B', 13, '12, Janak Puri', NULL),
(1434, 'Varuna', 12, 'B', 21, '69, Rohini', NULL),
(1461, 'David DSouza', 11, 'B', 1, 'D-34, Model Town', 243554);
CREATE TABLE SPORTS (
AdmNo INT,
Game VARCHAR(30),
Coach_Name VARCHAR(50),
Grade CHAR(1)
);
INSERT INTO SPORTS VALUES
(1324, 'Cricket', 'Narendra', 'A'),
(1364, 'Volleyball', 'M.P.Singh', 'A'),
(1271, 'Volleyball', 'M.P.Singh', 'B'),
(1434, 'Basket Ball', 'I.Malhotra', 'B'),
(1461, 'Cricket', 'Narendra', 'B'),
(1271, 'Basket Ball', 'I.Malhotra', 'A'),
(1434, 'Cricket', 'Narendra', 'B');
-- Queries
SELECT MIN(Class), MAX(Class) FROM STUDENTS;
SELECT Class, COUNT(*) FROM STUDENTS GROUP BY Class;
SELECT COUNT(*) FROM STUDENTS WHERE Class = 10;
SELECT S.* FROM STUDENTS S, SPORTS SP WHERE S.AdmNo = SP.AdmNo AND SP.Game =
'Cricket';
SELECT S.AdmNo, S.Name, S.Class, S.Sec, S.RNo FROM STUDENTS S, SPORTS SP WHERE
S.AdmNo = SP.AdmNo AND SP.Grade = 'A';
SELECT Name, Phone FROM STUDENTS WHERE Class = 12 AND AdmNo IN (SELECT AdmNo FROM
SPORTS);
SELECT Coach_Name, COUNT(*) FROM SPORTS GROUP BY Coach_Name;
SELECT S.Name, S.Phone FROM STUDENTS S, SPORTS SP WHERE S.AdmNo = SP.AdmNo AND
SP.Grade = 'A' AND SP.Coach_Name = 'Narendra';
