For this exercise use the following schema, the script to create this schema and populate data is given in ATM.SQL file.
1- List all names of the users from Lahore in Descending Order.
2- List all cards, who have card type “Credit” in ascending order.
3- List the name of all the users who are Gold type.
4- List the names of the user who’s Card number ends with 6. (HINT: USE LIKE)
5- List the Cities of the users who have a balance between 20000 and 35000.
6- List the Users who have never used the Card for transaction.
7- List the Card numbers that will expire in 2020.
8- List the names of the users who’s transaction had failed.
9- List all the card numbers with owner names and scheduled transactions, in case the user has never used scheduled transactions show null or zero in the transaction column. (Hint use ISNULL function)
10- List all usernames and phone numbers, who’s card number expiry date is less than 2-2-2019.
11- List all users IDs, Name and card numbers who have committed transactions today.
12- List all the users and their card name using union.
13- List all the users and their card name using Intersection.
Solution:
CREATE DATABASE ATM;
DROP TABLE IF EXISTS UserType;
CREATE TABLE UserType(
userTypeID INT PRIMARY KEY,
name VARCHAR(20) NOT NULL
);
DROP TABLE IF EXISTS User_Table;
CREATE TABLE User_Table(
userID INT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
userType INT FOREIGN KEY REFERENCES UserType(userTypeID),
phoneNum VARCHAR(15) NOT NULL,
city VARCHAR(20) NOT NULL
);
DROP TABLE IF EXISTS CardType;
CREATE TABLE CardType(
cardTypeID INT PRIMARY KEY,
name VARCHAR(15),
description VARCHAR(40) NULL
);
DROP TABLE IF EXISTS Card;
CREATE TABLE Card(
cardNum VARCHAR(20) PRIMARY KEY,
cardTypeID INT FOREIGN KEY REFERENCES CardType(cardTypeID),
PIN VARCHAR(4) NOT NULL,
expireDate DATE NOT NULL,
balance FLOAT NOT NULL
);
DROP TABLE IF EXISTS UserCard;
CREATE TABLE UserCard(
userID INT FOREIGN KEY REFERENCES User_Table(userID),
cardNum VARCHAR(20) FOREIGN KEY REFERENCES Card(cardNum),
PRIMARY KEY(userID, cardNum)
);
DROP TABLE IF EXISTS TransactionType;
CREATE TABLE TransactionType(
transTypeID INT PRIMARY KEY,
typeName VARCHAR(20) NOT NULL,
description VARCHAR(40) NULL
);
DROP TABLE IF EXISTS Transaction_Table;
CREATE TABLE Transaction_Table(
transID INT PRIMARY KEY,
transDate DATE NOT NULL,
cardNum VARCHAR(20) FOREIGN KEY REFERENCES Card(cardNum),
amount INT NOT NULL,
transType INT FOREIGN KEY REFERENCES TransactionType(transTypeID)
);
INSERT INTO UserType (userTypeID, name)
VALUES (1, 'Silver'),
(2, 'Gold'),
(3, 'Bronze'),
(4, 'Common');
INSERT INTO User_Table (userID, name, userType, phoneNum, city)
VALUES (1, 'Ali', 2, '03036067000', 'Narowal'),
(2, 'Ahmed', 1, '03036047000', 'Lahore'),
(3, 'Aqeel', 3, '03036063000', 'Karachi'),
(4, 'Usman', 4, '03036062000', 'Sialkot'),
(5, 'Hafeez', 2, '03036061000', 'Lahore');
INSERT INTO CardType (cardTypeID, name, description)
VALUES (1, 'Debt', 'Spend Now, Pay Now'),
(2, 'Credit', 'Spend Now, Pay Later'),
(3, 'Gift', 'Enjoy');
INSERT INTO Card (cardNum, cardTypeID, PIN, expireDate, balance)
VALUES ('1324327436569', 3, '1770', '2022-07-01', 43025.31),
('2324325423336', 3, '0234', '2020-03-02', 14425.62),
('2324325436566', 1, '1234', '2019-02-06', 34325.52),
('2324325666456', 2, '1200', '2021-02-05', 24325.3),
('2343243253436', 2, '0034', '2020-09-02', 34025.12);
INSERT INTO UserCard (userID, cardNum)
VALUES (1, '1324327436569'),
(1, '2343243253436'),
(2, '2324325423336'),
(2, '2343243253436'),
(3, '2324325436566'),
(5, '2324325423336');
INSERT INTO TransactionType (transTypeID, typename)
VALUES (1, 'withdraw'),
(2, 'Deposit'),
(3, 'Schedul..'),
(4, 'Failed');
INSERT INTO Transaction_Table(transID, transDate, cardNum, amount, transType)
VALUES (1, '2017-02-02', '1324327436569', 500, 1),
(2, '2018-02-03', '2343243253436', 3000, 3),
(3, '2017-05-06', '2324325436566', 2500, 2),
(4, '2016-09-09', '2324325436566', 2000, 1),
(5, '2015-02-10', '2324325423336', 6000, 4);
SELECT * FROM UserType;
SELECT * FROM User_Table;
SELECT * FROM UserCard;
SELECT * FROM Card;
SELECT * FROM CardType;
SELECT * FROM Transaction_Table;
SELECT * FROM TransactionType;
--Question: 1--
SELECT name
FROM User_Table
WHERE city = 'Lahore'
ORDER BY name DESC;
--Question: 2--
SELECT *
FROM Card
JOIN CardType ON Card.cardTypeID = CardType.cardTypeID
WHERE CardType.name = 'Credit'
ORDER BY cardNum ASC;
--Question: 3--
SELECT User_Table.name
FROM User_Table
JOIN UserType ON User_Table.userType = UserType.userTypeID
WHERE userType.name = 'Gold';
--Question: 4--
SELECT DISTINCT User_Table.name
FROM User_Table
JOIN UserCard ON User_Table.userID = UserCard.userID
WHERE UserCard.cardNum LIKE '%6';
--Question: 5--
SELECT DISTINCT User_Table.city
FROM User_Table
JOIN UserCard ON User_Table.userID = UserCard.userID
join Card ON UserCard.cardNum = Card.cardNum
WHERE Card.balance BETWEEN 20000 AND 35000;
--Question: 6--
SELECT User_Table.name AS userName
FROM User_Table
LEFT JOIN UserCard ON User_Table.userID = UserCard.userID
LEFT JOIN Transaction_Table ON UserCard.cardNum = Transaction_Table.cardNum
WHERE Transaction_Table.transID IS NULL;
--Question: 7--
SELECT cardNum
FROM Card
WHERE expireDate BETWEEN '2020-01-01' AND '2020-12-12';
--Question: 8--
SELECT DISTINCT User_Table.name
FROM User_Table
JOIN UserCard ON User_Table.userID = UserCard.userID
JOIN Transaction_Table ON UserCard.cardNum = Transaction_Table.cardNum
JOIN TransactionType ON Transaction_Table.transType = TransactionType.transTypeID
WHERE TransactionType.typeName = 'Failed';
--Question: 9--
SELECT Card.cardNum, User_Table.name AS ownerName, Transaction_Table.amount AS scheduledTransaction
FROM Card
JOIN UserCard ON Card.cardNum = UserCard.cardNum
JOIN User_Table ON UserCard.userID = User_Table.userID
LEFT JOIN Transaction_Table ON Card.cardNum = Transaction_Table.cardNum AND Transaction_Table.transType = (SELECT transTypeID FROM TransactionType WHERE typeName = 'Schedul..');
--Question: 10--
SELECT User_Table.name AS usernames, User_Table.phoneNum
FROM User_Table
JOIN UserCard ON User_Table.userID = UserCard.userID
JOIN Card ON UserCard.cardNum = Card.cardNum
WHERE Card.expireDate < '2-2-2019';
--Question: 11--
SELECT User_Table.userID, User_Table.name, UserCard.cardNum
FROM User_Table
JOIN UserCard ON User_Table.userID = UserCard.userID
JOIN Transaction_Table ON Transaction_Table.cardNum = UserCard.cardNum
WHERE Transaction_Table.transDate = CAST(GETDATE() AS DATE);
--Question: 12--
SELECT User_Table.name AS userName, CardType.name AS cardName
FROM User_Table
FULL JOIN UserCard ON User_Table.userID = UserCard.userID
FULL JOIN Card ON UserCard.cardNum = Card.cardNum
FULL JOIN CardType ON Card.cardTypeID = CardType.cardTypeID
WHERE User_Table.userID IN (
SELECT userID
FROM User_Table
UNION
SELECT userID
FROM UserCard
);
--Question: 13--
SELECT User_Table.name AS userName, CardType.name AS cardName
FROM User_Table
FULL JOIN UserCard ON User_Table.userID = UserCard.userID
FULL JOIN Card ON UserCard.cardNum = Card.cardNum
FULL JOIN CardType ON Card.cardTypeID = CardType.cardTypeID
WHERE User_Table.userID IN (
SELECT userID
FROM User_Table
INTERSECT
SELECT userID
FROM UserCard
);
