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);