SQL Server Exercise 3

For this exercise use the following schema, the script to create this schema and populate data is given in ATM.SQL file.

Join:

1- List the names of the users who are from the same city. (Use Self Join)

Aggregation-Grouping

2- List the number of users in each city, sorted high to low.

3- List the Card numbers and number of transactions against each card number having more than two Transactions.

Like operators:

4- List all the user having name at least 3 character long.

5- List all the user whose name start with H and end with z and user type gold.

6- List all the user having character a in their name.

Set Operations:

7- List the userId and name of the users whose cards are not expired.

8- List the Users details who are gold type and common type. (Without using AND operator and join)

Outer Join task:

9- List the Users who have never used the Card for transaction.

10- List all users along with their corresponding card numbers and transaction amounts. If a user does not have a card or has never made a transaction, show NULL values for the card number and transaction amount.

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 T1.name AS User1, T2.name AS User2, T1.city AS City
FROM User_Table T1
JOIN User_Table T2 ON T1.city = T2.city AND T1.name != T2.name
WHERE T1.userID < T2.userID;

--Question: 2--
SELECT city, COUNT(*)
FROM User_Table
GROUP BY city
ORDER BY city DESC;

--Question: 3--
SELECT cardNum, COUNT(*) AS Number_of_Transactions
FROM Transaction_Table
GROUP BY cardNum
HAVING COUNT(*) > 2;

--Question: 4--
SELECT name
FROM User_Table
WHERE name LIKE '___%';

--Question: 5--
SELECT User_Table.name
FROM User_Table
LEFT JOIN UserType ON User_Table.userType = UserType.userTypeID
WHERE User_Table.name LIKE 'H%' AND User_Table.name LIKE '%z' AND UserType.name = 'Gold';

--Question: 6
SELECT name
FROM User_Table
WHERE name LIKE '%a%';

--Question: 7--
SELECT DISTINCT User_Table.userID, User_Table.name
FROM User_Table
JOIN UserCard ON User_Table.userID = UserCard.userID
JOIN Card ON UserCard.cardNum = Card.cardNum
WHERE Card.expireDate >= GETDATE();

--Question: 8--
SELECT name
FROM User_Table
WHERE userType = ANY(
    SELECT userTypeID
	FROM UserType
	WHERE name = 'Gold' OR name = 'Common'
);

--Question: 9--
SELECT User_Table.userID, User_Table.name
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: 10--
SELECT User_Table.userID, User_Table.name, UserCard.cardNum, Transaction_Table.amount
FROM User_Table
LEFT JOIN UserCard ON User_Table.userID = UserCard.userID
LEFT JOIN Transaction_Table ON UserCard.cardNum = Transaction_Table.cardNum;