Posts

Showing posts from March, 2024

70) The courier manager wants to find the details of the courier whose cost is greater than the average cost of the courier in that branch. Write a query to display courier details like courier id, to address and weight of the couriers. Sort the results based on the to address in ascending and weight in ascending order.

Image
 
Post a Comment
Read more

69) The courier manager decides to create a new courier code for the courier which was booked after '31-03-2020'. The courier code should be created by adding the first three letters of the from address, followed by the first two letters of the courier id and the first three letters of the to address. The courier code should be in upper case. Write a query to display courier details like courier id, branch id and courier code. Give an alias name for the courier code as 'COURIER_CODE'. Sort the results based on the booking date in ascending order.

Image
 
Post a Comment
Read more

68) Write a query to display the branch id, courier id,from address,to address, weight and cost of the courier where the booking date is same as the expected delivery date. Sort the results based on the branch id in ascending order.

Image
 
Post a Comment
Read more

67) Write a single query to update the courier's cost based on the following conditions: If the weight of the courier is less than 5 kg, then increase the cost by 5%. If the weight of the courier is greater than or equal to 5 kg and less than or equal to 10 kg, then increase the cost by 10%. If the weight of the courier is greater than 10 kg, then increase the cost by 15%.

Image
 
Post a Comment
Read more

66) Write a query to change the data type from varchar2(20) to varchar2(30) and also add a not null constraint for the column branch_location in the branch table.

Image
 
Post a Comment
Read more

65) Refer to the given schema. Write a query to create a courier table with the constraints mentioned. Assume that the branch table and customer table are already created.

Image
 
Post a Comment
Read more

64) Create an Index with the name 'customer_index' for the customer table on email column. Refer to the given schema.

Image
 
Post a Comment
Read more

63) Create a sequence with the name 'branch_sequence' for the table customer which Starts with 101 and increases by 1

Image
 
Post a Comment
Read more

62) Create a synonym with the name 'client' for the table customer.

Image
 
Post a Comment
Read more

61) Create a view named customer_details with the attributes: customer name, contact number, courier id, expected delivery date and cost for the courier. Sort the records in the descending order based on customer name.

Image
 
Post a Comment
Read more

60) Write a query to display the name, department name and marks of the top performer(s) in 'Software Engineering'. (Hint: Use Correlated Subquery or Inline view.)

Image
 
Post a Comment
Read more

59) Write a query to display the name of the departments that has the least student count. Sort the result based on the department name in ascending order.

Image
 
Post a Comment
Read more

58) Write a query to display the names of all students who have secured more than 50 in all subjects that they have appeared in. Sort the records based on the student's name in ascending order.

Image
 
Post a Comment
Read more

57) Write a query to find the name of the student(s) who has scored the maximum mark in 'Software Engineering'. Sort the results based on the student name in ascending order. (Hint: Use Correlated Subquery or Inline view.)

Image
 
Post a Comment
Read more

56) Write a query to display the names of the staff who are not handling any subject. Sort the records based on the name in ascending order.

Image
 
Post a Comment
Read more

55) Write a query to display the name of the department that has the maximum staff count. Sort the records based on the department name in ascending order.

Image
 
Post a Comment
Read more

54) Write a query to display the courier details such as courier id, from address, to address and the cost of the courier whose cost is less than the average cost of all the couriers. Sort the records based on the cost in descending order. (Hint: Use subquery)

Image
 
Post a Comment
Read more

53) Write a query to display branch details such as branch id, branch location and contact number of all the branches that did not receive any couriers. Sort the records based on the branch location in descending order.

Image
 
Post a Comment
Read more

52) Write a query to display the courier id, contact number of the from branch with the alias name 'CONTACT_FROM_BRANCH' and contact number of the delivered branch with the alias name 'CONTACT_TO_BRANCH'. Sort the records based on the courier id in descending order.

Image
 
Post a Comment
Read more

51) rite a query to display the branch details such as branch id, location and contact number of the branches that sent the maximum number of couriers. Sort the results based on the branch id in ascending order.

Image
 
Post a Comment
Read more

50) Write a query to display the courier id, from address, to address and the contact number of the courier that has minimum weight.

Image
 
Post a Comment
Read more

49) Write a query to display the student names and the maximum mark scored by them in any subject. Give an alias to the maximum mark as 'MAX_MARK'. Sort the records based on the name in ascending order.

Image
 
Post a Comment
Read more

48) Write a query to display the courier details such as courier id with the alias name 'COURIER1' and the courier id of the courier that got delivered on the same date as courier1 with the alias name 'COURIER2' and the delivered date. Sort the records by the delivery date in descending order and then by the courier id of courier1 in descending order.

Image
 
Post a Comment
Read more

47) Write a query to display the customer details such as customer name, contact number and courier id where the difference between the expected delivery date and booking date is less than 5 days. Sort the records based on customer name in ascending order.

Image
 
Post a Comment
Read more

46) Write a query to display the customer name, contact number and to address of the customers who sent a courier to the same address more than once. Sort the records based on the customer name in ascending order.

Image
 
Post a Comment
Read more

45) Write a query to display the courier details such as courier id, from address, to address and the status of the couriers delivered in the month of 'January'. Sort the records based on to address in ascending order.

Image
 
Post a Comment
Read more

44) Write a query to display the customer name, contact number and courier id of the couriers that are not 'Delivered'. Sort the records based on the customer name in ascending order.(Note: Data is case-sensitive.)

Image
 
Post a Comment
Read more

43) Write a query to display the courier id, to_address, weight, cost and comments of all the couriers from 'Chicago'. Comments should be based on the following conditions. If the courier weight is in the range of 1 to 5 then display 'Less Cost'. If the courier weight is in the range of 6 to 9 then display 'Average Cost'. Otherwise display 'High Cost'. Give an alias name for comments as 'COMMENTS'. Sort the records based on the to_address in descending order. (Note: Data is case-sensitive.)

Image
 
Post a Comment
Read more

42) Write a query to display the branch id and the maximum weight of the courier sent in a branch before the month of 'May'. Give an alias name as 'MAX_WEIGHT' for the maximum weight of the courier. Sort the records based on the branch id in ascending order. (Hint: Use Extract(). Data is case sensitive)

Image
 
Post a Comment
Read more

41) Write a query to display the list of student ids and average marks in 2 decimal places if their average mark is greater than 80. Give an alias to the average mark as AVG_MARK. Sort the result based on the average mark in ascending order.

Image
 
Post a Comment
Read more

40) Write a query to display the to address and total cost of the couriers whose total cost exceeds 1000 dollars. Give an alias name as 'TOTAL_COST' . Sort the results based on the to address in descending order.

Image
 
Post a Comment
Read more

39) Write a query to display the from address and average weight of the couriers whose average weight is greater than 5 kg. Give an alias name as 'AVERAGE_WEIGHT'. Sort the results based on the from_address in descending order.

Image
 
Post a Comment
Read more

38) Write a query to display the contact number and total number of couriers sent by customers who have sent more than one courier. Give an alias name to the total courier count as 'COURIER_COUNT'. Sort the results based on contact number in ascending order.

Image
 
Post a Comment
Read more

37) Write a query to display the maximum weight and minimum weight of the courier that received in 'Washington'. Give an alias name to the maximum cost as 'MAX_WEIGHT' and the minimum cost as 'MIN_WEIGHT'. (Note: Data is case-sensitive.)

Image
 
Post a Comment
Read more

36) The courier management system has decided to collect feedback after 2 days for the couriers that are 'Delivered'. Write a query to display the courier id, delivered branch id and feedback date of all the couriers that are 'Delivered'. Give an alias name to feedback date as 'FEEDBACK_DATE'. Sort the records based on the FEEDBACK_DATE in descending order. (Note: Data is case-sensitive.)

Image
 
Post a Comment
Read more

35) Write a query to display the courier id and booking date in the format 'DDMMYYYY' of all couriers whose weight is less than 5. Give an alias name as 'FORMATTED_DATE' for the booking date. Sort the records based on the courier id in descending order.

Image
 
Post a Comment
Read more

34) Due to some reason the expected delivery date is delayed for a month. Write a query that display the courier ID, from address, to address and delayed delivery date of all the couriers that booked in the month of 'August'. Give an alias name to the delayed delivery date as 'DELAYED_DELIVERY_DATE'. Sort the results based on the to_address in descending order. (Hint: Use Extract(). Data is case sensitive)

Image
 
Post a Comment
Read more

33) Write a query to display the courier id, from address, to address, and name of booking month of all couriers whose weight is in the range of 11 to 15 kg. Display the month name in upper case. Give an alias name to the booking month as 'BOOKING_MONTH'. Sort the results based on the courier id in descending order.

Image
 
Post a Comment
Read more

32) Write a query to display the customer's name, address, and email who is from 'Chicago' and 'Tokyo'. If the customer has email, then display 'Available' otherwise display 'Not available'. Give an alias name to the email as 'EMAIL' . Sort the results based on the customer's name in descending order.

Image
 
Post a Comment
Read more

31) Write a query to display the customer's name, customer address, and contact information of customers whose address has the 5th letter 'o'. If the customer email is not available,then display the contact number. Give an alias name to the contact information as 'CONTACT'. Sort the results based on the customer's name in descending order.

Image
 
Post a Comment
Read more

30) Write a query to display the customer name and customer code of all customers who uses 'yahoo' as their email. The customer code should be generated by concatenating the first 3 characters of the customer's name and the first 3 numbers of their contact number. Give an alias name as 'CUSTOMER_CODE'. Sort the results based on the customer address in ascending order.

Image
 
Post a Comment
Read more

29)Write a query to display the customer's name in uppercase and address in lowercase, whose address has a character count of 6. Give an alias name to the customer's name as 'NAME' and customer address as 'ADDRESS'. Sort the results based on the customer's name in ascending order.

Image
 
Post a Comment
Read more

28) Write a query to display the customer name, email, and contact number of the customer whose address is not in 'London' and not using 'yahoo' mail. Sort the results based on the customer name in descending order.

Image
 
Post a Comment
Read more

27) Write a query that displays the name, address, email address, and contact number of all customers whose names begin with 'P' or 'S' and have a 'gmail' account. Sort the results based on the customer name in descending order.

Image
 
Post a Comment
Read more

26) Write a query to display the customer name and address of all customers who have an email address. Sort the records based on the address in ascending order.

Image
 
Post a Comment
Read more

25) Write a query to display the courier id, to address and cost of all the couriers from 'Sydney' and the cost should be more than 500 dollars. Sort the results based on courier id in descending order.

Image
 
Post a Comment
Read more

24) Write a query to display the courier id, booking date, weight and cost of all the couriers whose cost is in the range of 800 to 1500 dollars. Sort the results based on the courier id in descending order.

Image
 
Post a Comment
Read more

23) Write a query to display the customer name, address and contact number of all the customers whose names contain the letter 'e'. Sort the results based on the customer name in descending order.

Image
 
Post a Comment
Read more

22) Write a query that displays the courier id, booking date, weight, and cost of the couriers that are going to 'Mackay' and 'Austin'. Sort the results based on weight in descending order.

Image
 
Post a Comment
Read more

21) Write a query to display the customer name, email and contact number of all the customers who live in 'Berlin' . Sort the results based on the customer name in descending order.

Image
 
Post a Comment
Read more