DML MySQL Basic Select – Query Writing

1. Write a query to display the entire contents of the ‘electricity_connection_type’.Display the records in ascending order based on their connection name.

select * from electricity_connection_type order by connection_name

2. Write a query to display the entire contents of the building_type table, sorted by name in ascending order.

select * from building_type order by name asc

3. Write a query to display the entire contents of the ‘building’.Display the records in ascending order based on owner name.

select * from building order by owner_name asc

4. Write a query to display the entire contents of the  ‘electricity_reading’, Display the records in descending order based on ‘total_units’.

select * from electricity_reading order by total_units desc

5. Write a query to display all ‘meter_number’ from meter table.

select meter_number from meter

6. Write a query to display the owner_name and contact_number of all building, Display the records in ascending order based on owner_name.

select owner_name,contact_number from building order by owner_name asc

7. Write a query to display the total_units, payable_amount, fine_amount of all bills, sorted by total_units in descending order.

select total_units, payable_amount, fine_amount from bill order by total_units desc

8. Write a query to display the entire contents of the slab table, sorted by from_unit in ascending order.

select * from slab order by from_unit asc

9. Write a query to display the details of the building whose owner_name is ‘Nicholas’.

select * from building where owner_name = 'Nicholas'

10. Write a query to display the details of all the bills whose ‘total_units’ greater than 10000, sorted by total_units in descending order.

select * from bill where total_units > 10000 order by total_units desc

11. Write a query to display the rate of slab whose from_unit is 2001 and to_unit is 4500.

select rate from slab where from_unit = 2001 and to_unit = 4500

12. Write a query to display the details of all the bills with the due_date on ‘2017-10-01’, sorted by payable_amount in descending order.

select * from bill where due_date = '2017-10-01' order by payable_amount desc

13. Write a query to display all the details of all the bills whose payment_date is on the year 2018, sorted by payable_amount in descending order.

select * from bill where payment_date like '%2018%' order by payable_amount desc

14. Write a query to display the owner_name, address and contact_number of the buildings which does not have an email_address, sorted by owner_name in ascending order.

select owner_name, address, contact_number from building where email_address is null order by owner_name asc

15. Write a query to display the entire details of the building whose owner_name starts with the letter ‘M’, sorted by owner_name in ascending order.

select * from building where owner_name like 'M%' order by owner_name asc

16. Write a query to display the entire details of the building whose building_type_id is 2, sorted by owner_name in ascending order.

select * from building where building_type_id = 2 order by owner_name asc

17. Write a query to display the details of the electricity_reading whose total_units per day is between 500 and 1000, sorted by total_units in ascending order.

select * from electricity_reading where total_units between 500 and 1000 order by total_units asc

18. Write a query to display the details of the bill whose payment is not completed, sorted by due_date in ascending order.

select * from bill where is_payed=0 order by due_date asc;

19. Write a query to display the meter_id and total_units of electricity_reading whose 13th hour reading is lesser than the 14th hour reading, sorted by total_units in descending order.

select meter_id, total_units from electricity_reading where h13 < h14 order by total_units desc

20. Write a query to display the details of the meter whose meter_number starts with ‘S’ and ends with ‘6’.

select * from meter where meter_number like 'S%6'

21. Write a query to display the entire contents of the route table, sorted by route_name in ascending order.

select * from route order by route_name

22. Write a query to display the entire contents of the station table in ascending order based on name.

select * from station order by name asc

23. Write a query to display all the person_name and contact_number of the  travel_card.Display the records in ascending order based on person_name.

select person_name,contact_number from travel_card order by person_name

24. Write a query to display travel_card_id, entry_time and exit_time of the travel_payment.Display the records in ascending order based on entry_time.

select travel_card_id, entry_time, exit_time from travel_payment order by entry_time

25. Write a query to display all the details of the metro_train whose position is ‘1’ in ascending order based on updated_time.

select * from metro_train where position=1 order by updated_time

26. Write a query to display all the details of the station which does not have any interchanges in ascending order based on name.

select * from station where IS_INTERCHANGE = 0  order by name asc

27. Write a query to display the details of the travel_card whose person_name is ‘Michael‘.

select *from travel_card where person_name = 'Michael'

28. Write a query to display all the details of the station whose name starts with the letter ‘K‘ in ascending order based on name.

select * from station where name like 'K%' order by name asc

29. Write a query to display the details of the travel_payment whose amount is greater than 30 in ascending order based on entry_time.

select * from travel_payment where amount > 30 order by entry_time asc

30. Write a query to display the details of the train_arrival_time which does not have any deviation in ascending order based on metro_train_id.

select * from train_arrival_time where deviation = 0 order by metro_train_id asc

Happy Learning – If you require any further information, feel free to contact me.