Skip to content

Instantly share code, notes, and snippets.

@owstron

owstron/edited-bankloans.sql

Created February 12, 2018 08:28
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save owstron/658743b6926eb3810261384fddce2605 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save owstron/658743b6926eb3810261384fddce2605 to your computer and use it in GitHub Desktop.
Download ZIP
CS162 Session 6.1 Pre-Class Work. SQL Overview!
Raw
edited-bankloans.sql
.mode column
CREATE TABLE Clients (
CLIENTNUMBER INT,
FIRSTNAME VARCHAR(20),
SURNAME VARCHAR(20),
EMAIL VARCHAR(100),
PHONE VARCHAR(20)
);
CREATE TABLE Loans (
ACCOUNTNUMBER INT, --A unique integer to identify this account
CLIENTNUMBER INT, -- An integer to identify the client (clients may have more than one account)
STARTDATE DATETIME, -- The time that this account was created
STARTMONTH INT, -- The month for which the first repayment is due (201805 means May 2018)
TERM INT, -- Over how many months the loan must be repaid
REMAINING_TERM INT, -- How many months remain
PRINCIPALDEBT NUMERIC(11, 2), -- The size of the initial loan
ACCOUNTLIMIT NUMERIC(11, 2), --
BALANCE NUMERIC(11, 2), -- How much is currently owed
STATUS VARCHAR(11) -- Human readable status - e.g. "PAID OFF", "ARREARS", "NORMAL"
);
INSERT INTO Clients VALUES (1, 'Robert', 'Warren', '[email protected]', '(251) 546-9442');
INSERT INTO Clients VALUES (2, 'Vincent', 'Brown', '[email protected]', '(125) 546-4478');
INSERT INTO Clients VALUES (3, 'Janet', 'Prettyman', '[email protected]', '(949) 569-4371');
INSERT INTO Clients VALUES (4, 'Martina', 'Kershner', '[email protected]', '(630) 446-8851');
INSERT INTO Clients VALUES (5, 'Tony', 'Schroeder', '[email protected]', '(226) 906-2721');
INSERT INTO Clients VALUES (6, 'Harold', 'Grimes', '[email protected]', '(671) 925-1352');
INSERT INTO Loans VALUES (1,1,'2017-11-01 10:00:00', 201712, 36, 35, 10000.00, 15000.00, 9800.00, 'NORMAL');
INSERT INTO Loans VALUES (2,2,'2018-01-01 10:00:00', 201802, 24, 24, 1000.00, 1500.00, 1000.00, 'NORMAL');
INSERT INTO Loans VALUES (3,1,'2016-11-01 10:00:00', 201612, 12, -3, 2000.00, 15000.00, 4985.12, 'ARREARS');
INSERT INTO Loans VALUES (4,3,'2018-01-01 10:00:00', 201802, 24, 24, 3500.00, 5000.00, 1300.00, 'NORMAL');
INSERT INTO Loans VALUES (5,4,'2017-11-01 10:00:00', 201712, 12, 35, 10000.00, 15000.00, 0.00, 'PAID OFF');
INSERT INTO Loans VALUES (6,5,'2018-01-01 10:00:00', 201802, 48, 24, 1000.00, 1500.00, 0.00, 'PAID OFF');
INSERT INTO Loans VALUES (7,6,'2015-11-01 10:00:00', 201512, 12, -20, 10000.00, 15000.00, 9800.00, 'Arrears');
INSERT INTO Loans VALUES (7,4,'2018-01-01 10:00:00', 201802, 12, 1, 2400.00, 3600.00, 130.00, 'NORMAL');
-- Is the data there?
SELECT 'Loans';
SELECT '----------------------------------------------------';
SELECT * FROM Loans;
SELECT '';
SELECT 'Clients';
SELECT '----------------------------------------------------';
SELECT * FROM Clients;
SELECT '1. Everyone who owes more than $5,000 on an account:';
SELECT '----------------------------------------------------';
SELECT FIRSTNAME, SURNAME, BALANCE FROM Loans
JOIN Clients ON Loans.CLIENTNUMBER = Clients.CLIENTNUMBER
WHERE BALANCE > 5000.00;
SELECT '';
SELECT '2. Find all loans older than Jan 2017';
SELECT '----------------------------------------------------';
SELECT FIRSTNAME, SURNAME, BALANCE, STARTDATE FROM Loans
JOIN Clients ON Loans.CLIENTNUMBER = Clients.CLIENTNUMBER
WHERE STARTDATE < "2017-01";
SELECT '';
SELECT '3. Find all clients who have more than one loan';
SELECT '----------------------------------------------------';
SELECT Clients.FIRSTNAME, Clients.SURNAME, COUNT(*) FROM Loans
JOIN Clients ON Loans.CLIENTNUMBER = Clients.CLIENTNUMBER
GROUP BY Loans.CLIENTNUMBER HAVING COUNT(*) > 1;
SELECT '';
SELECT "4. Find the total balance outstanding over all loans that aren't in arrears";
SELECT '----------------------------------------------------';
SELECT SUM(BALANCE) FROM Loans WHERE REMAINING_TERM > 0;
SELECT '';
SELECT '5. Are all account numbers unique? (How should we fix this in general)';
SELECT '----------------------------------------------------';
/* Nope, we would make the account numbers primary key and add autoincrement while declaring the table*/
SELECT '';
SELECT '6. Martina has undergone gender reassignment and is now Martin';
SELECT '----------------------------------------------------';
UPDATE Clients
SET FIRSTNAME = 'Martin'
WHERE FIRSTNAME = 'Martina';
SELECT '';
SELECT '7. Get a list of email addresses for all clients who paid off a loan';
SELECT '----------------------------------------------------';
SELECT Clients.EMAIL FROM Clients
JOIN Loans
ON Loans.CLIENTNUMBER = Clients.CLIENTNUMBER
WHERE STATUS LIKE '%PAID OFF%';
SELECT '';
SELECT '8. Print out the largest loan for each client';
SELECT '----------------------------------------------------';
SELECT Clients.FIRSTNAME, Clients.SURNAME, MAX(Loans.PRINCIPALDEBT) FROM Clients
JOIN Loans
ON Loans.CLIENTNUMBER = Clients.CLIENTNUMBER
GROUP BY Loans.CLIENTNUMBER HAVING COUNT(*) > 0;
Raw
ridesharing.sql
.mode column
CREATE TABLE Users (
USERID INTEGER PRIMARY KEY AUTOINCREMENT,
FIRSTNAME VARCHAR(20),
SURNAME VARCHAR(20),
PHONE VARCHAR(20),
EMAIL VARCHAR(100),
ISDRIVER BOOLEAN
);
CREATE TABLE Trips (
TRIPID INTEGER PRIMARY KEY AUTOINCREMENT,
STARTLOCATION VARCHAR(40),
ENDLOCATION VARCHAR(40),
TRIPDATE DATETIME,
RIDERID INT,
DRIVERID INT,
CHARGE NUMERIC(11, 2)
);
INSERT INTO Users(FIRSTNAME, SURNAME, PHONE, EMAIL, ISDRIVER)
VALUES ("Nik", "Stha", "8179091074", "[email protected]", 1);
INSERT INTO Users(FIRSTNAME, SURNAME, PHONE, EMAIL, ISDRIVER)
VALUES ("Philip", "Sterne", "8188490213", "[email protected]", 0);
INSERT INTO Users(FIRSTNAME, SURNAME, PHONE, EMAIL, ISDRIVER)
VALUES ("Suraj", "Paneru", "6292022722", "[email protected]", 1);
INSERT INTO Users(FIRSTNAME, SURNAME, PHONE, EMAIL, ISDRIVER)
VALUES ("Mandlenkosi", "Sibanda", "91238020932", "[email protected]", 1);
INSERT INTO Users(FIRSTNAME, SURNAME, PHONE, EMAIL, ISDRIVER)
VALUES ("Nifesh", "Chakubaji", "81790939234", "[email protected]", 0);
INSERT INTO Trips(STARTLOCATION, ENDLOCATION, TRIPDATE, RIDERID, DRIVERID, CHARGE)
VALUES ("At Home", "SF", '2018-02-12 10:00:00', 2, 1, 50);
INSERT INTO Trips(STARTLOCATION, ENDLOCATION, TRIPDATE, RIDERID, DRIVERID, CHARGE)
VALUES ("SF", "At Home", '2018-02-12 10:00:00', 2, 1, 60);
INSERT INTO Trips(STARTLOCATION, ENDLOCATION, TRIPDATE, RIDERID, DRIVERID, CHARGE)
VALUES ("At Home", "Chit Chat Chai", '2018-02-12 10:00:00', 1, 3, 100);
INSERT INTO Trips(STARTLOCATION, ENDLOCATION, TRIPDATE, RIDERID, DRIVERID, CHARGE)
VALUES ("At Home", "Phoenix", '2018-01-12 10:00:00', 3, 4, 50);
-- Data test?
SELECT 'Trips';
SELECT '----------------------------------------------';
SELECT * FROM Trips;
SELECT '';
SELECT 'Users';
SELECT '----------------------------------------------------';
SELECT * FROM Users;
--Query to find number of drivers
SELECT 'QUERY for number of trips';
SELECT '----------------------------------------------------';
SELECT Users.FIRSTNAME, Users.SURNAME, COUNT(*), SUM(TRIPS.CHARGE) FROM Users
LEFT OUTER JOIN Trips
ON Trips.DRIVERID = Users.USERID
GROUP BY Trips.DRIVERID HAVING COUNT(*) > 0 AND Users.ISDRIVER = 1;
SELECT 'QUERY for haggling customers';
SELECT '----------------------------------------------------';
SELECT Users.FIRSTNAME, Users.Email FROM Users
WHERE Users.USERID NOT IN (SELECT RIDERID FROM Trips WHERE strftime('%m', Trips.TRIPDATE) = strftime('%m', DATE('now'))) AND Users.ISDRIVER = 0;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment