Write SQL Queries for Tables (Users, Categories, Expenses)

When writing SQL queries, there are several possible ways to achieve the same goal. While attending exams writing any one of them is fine.

a. Insert a new user.

Since the values are not given we will assume some values and insert them into the Users table.

Method #1: Assuming the userID is NOT an AUTO_INCREMENT field.

INSERT INTO Users
VALUES      (403,
             'Ram',
             'Abc2@1234',
             'ram1232@gmail.com',
             '1995-05-10',
             'Male',
             '2022-6-8');

--- OR --

INSERT INTO Users
            (userID,
             username,
             password,
             email,
             dateOfBirth,
             gender,
             registerDate)
VALUES      (403,
             'Ram',
             'Abc2@1234',
             'ram1232@gmail.com',
             '1995-05-10',
             'Male',
             '2022-6-8');  

 

Method #2: Assuming the userID is an AUTO_INCREMENT field.

INSERT INTO Users
            (username,
             password,
             email,
             dateOfBirth,
             gender,
             registerDate)
VALUES      ('Sita',
             'Sita@1234',
             'sita123@gmail.com',
             '1995-06-12',
             'Male',
             '2022-6-10');  

 

b. Find users whose date of birth is before Jan 01, 2000.

 

Method #1: Using the DATE function

SELECT *
FROM   Users
WHERE  DATE(dateOfBirth) < DATE('2000-01-01');  

 

Method #2: Without the DATE function

SELECT *
FROM   Users
WHERE  dateOfBirth < '2000-01-01';  

 

c. Find top 5 categories on which users spend their money.

SELECT categoryID,
       SUM(amount)
FROM   Expenses
GROUP  BY categoryID
ORDER  BY SUM(amount) DESC
LIMIT  5;  

 

d. Find categories in which no expenditure has been made so far.

 

Method #1: Using Nested Queries

SELECT categoryID
FROM   Categories
WHERE  categoryID NOT IN (SELECT categoryID
                          FROM   Expenses);  

 

Method #2: Using LEFT OUTER JOIN

SELECT Categories.categoryID
FROM   Categories
       LEFT OUTER JOIN Expenses
                    ON Categories.categoryID = Expenses.categoryID
WHERE  Expenses.expenseID IS NULL;  

 

Method #3: Using RIGHT OUTER JOIN

SELECT Categories.categoryID
FROM   Expenses
       RIGHT OUTER JOIN Categories
                     ON Expenses.categoryID = Categories.categoryID
WHERE  Expenses.expenseID IS NULL;  

 

e. Find those users whose expenditure is not less than that of userID 405.

SELECT userID,
       SUM(amount)
FROM   Expenses
GROUP  BY userID
HAVING SUM(amount) >= (SELECT SUM(amount)
                       FROM   Expenses
                       WHERE  userID = 405);