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.
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.
Solution
To answer the question, you would need to write a SQL query. Here is a step-by-step guide on how to do it:
- First, you need to select the count of bills from the bill table. This can be done using the COUNT() function in SQL.
SELECT COUNT(*)
FROM bill
- Next, you need to add a condition to select only the bills which have been paid in October 2017. This can be done using the WHERE clause in SQL. The payment_date must not be NULL and it must be in October 2017.
SELECT COUNT(*)
FROM bill
WHERE payment_date IS NOT NULL AND payment_date BETWEEN '2017-10-01' AND '2017-10-31'
- Then, you need to add another condition to select only the bills which have no fine. This means that the fine_amount must be NULL.
SELECT COUNT(*)
FROM bill
WHERE payment_date IS NOT NULL AND payment_date BETWEEN '2017-10-01' AND '2017-10-31' AND fine_amount IS NULL
- Finally, you need to give an alias name to the count of bills. This can be done using the AS keyword in SQL. The alias name should be 'bills_paid_withoutfine'.
SELECT COUNT(*) AS bills_paid_withoutfine
FROM bill
WHERE payment_date IS NOT NULL AND payment_date BETWEEN '2017-10-01' AND '2017-10-31' AND fine_amount IS NULL
This query will give you the count of bills which have been paid without fine 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.