Write a query to display the count of bills which has completed payment without fine on the month of October 2017. Give an alias name as 'bills_paid_withoutfine'.Note:1. Display only the count of bills which has completed payment without fine on the month of October 2017 from bill table.2. Specify the alias name without any mistake.3. Specify condition on payment_date and fine_amount. payment_date must be NOT NULL and it must be October 2017 and fine_amount must be NULL.4. Compare your executed result with the sample output given below.Sample output:
Question
Write a query to display the count of bills which has completed payment without fine on the month of October 2017. Give an alias name as 'bills_paid_withoutfine'.Note:1. Display only the count of bills which has completed payment without fine on the month of October 2017 from bill table.2. Specify the alias name without any mistake.3. Specify condition on payment_date and fine_amount. payment_date must be NOT NULL and it must be October 2017 and fine_amount must be NULL.4. Compare your executed result with the sample output given below.Sample output:
Solution 1
Here is the SQL query you need to write:
SELECT COUNT(*) AS bills_paid_withoutfine
FROM bill
WHERE payment_date IS NOT NULL
AND fine_amount IS NULL
AND MONTH(payment_date) = 10
AND YEAR(payment_date) = 2017;
This query works as follows:
-
SELECT COUNT(*) AS bills_paid_withoutfine: This line is counting the number of rows that meet the conditions specified in the WHERE clause and giving that count an alias of 'bills_paid_withoutfine'. -
FROM bill: This line is specifying the table that we are querying from, which is the 'bill' table. -
WHERE payment_date IS NOT NULL: This line is a condition that excludes rows where the payment_date is NULL. -
AND fine_amount IS NULL: This line is a condition that only includes rows where the fine_amount is NULL. -
AND MONTH(payment_date) = 10 AND YEAR(payment_date) = 2017: These lines are conditions that only include rows where the payment_date is in October 2017.
Solution 2
Here is the SQL query you need to write:
SELECT COUNT(*) AS bills_paid_withoutfine
FROM bill
WHERE payment_date IS NOT NULL
AND fine_amount IS NULL
AND MONTH(payment_date) = 10
AND YEAR(payment_date) = 2017;
This query works as follows:
-
SELECT COUNT(*) AS bills_paid_withoutfine: This line is counting the number of rows that meet the conditions specified in the WHERE clause and giving that count an alias of 'bills_paid_withoutfine'. -
FROM bill: This line is specifying the table that we are selecting data from, which is the 'bill' table. -
WHERE payment_date IS NOT NULL: This line is a condition that excludes rows where the payment_date is NULL. -
AND fine_amount IS NULL: This line is a condition that excludes rows where the fine_amount is not NULL. -
AND MONTH(payment_date) = 10 AND YEAR(payment_date) = 2017: These lines are conditions that limit the rows to those where the payment_date is in October 2017.
Similar Questions
Write a query to display the number of bills having fine_amount. Give an alias name as 'count_of_bills_with_fine'.Note:1. Display only the number of bills having the fine_amount from bill table. (If fine_amount is greater than 0 then it indicates that bill got fine_amount.)2. Specify the alias name without any mistake.3. Specify condition on fine_amount column of bill table.3. Compare your executed result with the sample output given below.Sample output:
Write a query to display the number of bills in which the bill payment is completed. Give an alias name as 'count_of_bills_with_payment'.Note:1. Display only the number of bills where payment is completed.(If payment is completed then is_payed will be 1, otherwise 0.)2. Specify the alias name without any mistake.3. Specify condition on is_payed column from bill table.4. Compare your executed result with the sample output given below.Sample output:
Write a query to display the average fine_amount of the bills whose payment_date is on the year 2018. Give an alias name to the average fine amount as 'average_fine_amount'.Note:1. Display only the average fine_amount with the given payment_date from bill table.2. Specify the alias name without any mistake.3.Specify the condition and retrieve the correct rows in the result(condition need to be specified on fine amount).4. Compare your executed result with the sample output given below.Sample output:
Write a query to display the month and sum of fine_amount collected on the month between July to October. Give an alias name as 'monthly_fine_amount' also display the records in ascending order based on month.Note:1. Display only the month and sum of fine_amount collected in the month between July to October from bill table. (month will be 01, 02, 03,...12)2. Specify the alias name without any mistake.3. Order by is mandatory4. Group by month to get the month wise sum of fine_amount5. Compare your executed result with the sample output given below.
Write a query to display the sum of payable_amount and fine_amount of all the bills from bill table. Give an alias name to the sum of payable amount and fine amount as 'sum_payable_and_fine_amount'.Note:1. Display only the sum of payable_amount and fine_amount from bill table.2. Specify the alias name without any mistake.3. Compare your executed result with the sample output given below.Sample output:
Upgrade your grade with Knowee
Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.