Question

Consider the following table and write the output of the following SQL Queries.

Table : ORDERS

ORDERID	CUSTOMERNAME	TOTALAMOUNT	DISCOUNT	ORDERDATE
101	Hemant	5000	10	2024–03-01
102	Neha	7000	15	NULL
103	Keshav	3000	5	2024–01-20
104	Sandhya	4500	NULL	2023-12-25

Write the output of the following SQL Queries:

I. SELECT CUSTOMERNAME FROM ORDERS WHERE DISCOUNT IS NOT NULL;

II. SELECT CUSTOMERNAME, DISCOUNT FROM ORDERS WHERE DISCOUNT BETWEEN 5 AND 10;

III. SELECT MONTHNAME(ORDERDATE) FROM ORDERS WHERE ORDERDATE IS NOT NULL;

IV. SELECT ORDERID, DAY(ORDERDATE) FROM ORDERS;

Hint:

Rows with NULL never pass IS NOT NULL or BETWEEN, and BETWEEN includes both end values. MONTHNAME and DAY work on the date part.

Answer 1

Step 1: Know the NULL rules:
A NULL value means unknown. IS NOT NULL keeps only rows with a real value. A comparison such as BETWEEN never matches a NULL.
Neha has a NULL ORDERDATE. Sandhya has a NULL DISCOUNT.
Two dates in the question are printed with a long dash after the year. We read them as the normal dates 2024-03-01 and 2024-01-20.

Step 2: Query I:
The query keeps the rows where DISCOUNT is not NULL. Hemant (10), Neha (15) and Keshav (5) pass. Sandhya (NULL) is removed.
Output:
CUSTOMERNAME
Hemant
Neha
Keshav

Step 3: Query II:
BETWEEN 5 AND 10 includes both 5 and 10. Hemant has 10 and Keshav has 5, so both pass.
Neha has 15, which is above 10. Sandhya has NULL, which never matches.
Output:
CUSTOMERNAME - DISCOUNT
Hemant - 10
Keshav - 5

Step 4: Query III:
The WHERE clause removes the row of Neha because her date is NULL. MONTHNAME gives the month in words for the other three rows.
2024-03-01 gives March. 2024-01-20 gives January. 2023-12-25 gives December.
Output:
MONTHNAME(ORDERDATE)
March
January
December

Step 5: Query IV:
There is no WHERE clause, so all four rows are shown. DAY() gives the day number of the month.
DAY of a NULL date is NULL.
Output:
ORDERID - DAY(ORDERDATE)
101 - 1
102 - NULL
103 - 20
104 - 25

Final Answer:
I. Hemant, Neha, Keshav
II. Hemant 10, Keshav 5
III. March, January, December
IV. (101, 1), (102, NULL), (103, 20), (104, 25)