mtpWMU20

1 
2
3Please follow instructions given below.
4Write a query to display account number, customer’s number, customer’s firstname,lastname,account
5opening date.
6Display the records sorted in ascending order based on account number.
7SELECT account_number,am.customer_number,firstname,lastname,account_opening_date
8FROM customer_master cm INNER JOIN account_master am
9ON cm.customer_number=am.customer_number
10ORDER BY account_number
11Please follow instructions given below.
12Write a query to display the number of customer’s from Delhi. Give the count an alias name of
13Cust_Count.
14SELECT count(customer_number) Cust_Count
15FROM customer_master
16WHERE customer_city='Delhi'
17Please follow instructions given below.
18Write a query to display the customer number, customer firstname,account number for the customer’s
19whose accounts were created after 15th of any month.
20Display the records sorted in ascending order based on customer number and then by account number.
21SELECT am.customer_number, firstname, account_number
22FROM customer_master cm INNER JOIN account_master am
23ON cm.customer_number=am.customer_number
24WHERE extract(day from account_opening_date)>15
25ORDER BY am.customer_number, account_number
26Please follow instructions given below.
27Write a query to display customer number, customer's first name, account number where the account
28status is terminated.
29Display the records sorted in ascending order based on customer number and then by account number.
30SELECT am.customer_number,firstname, account_number
31FROM customer_master cm INNER JOIN account_master am
32ON cm.customer_number=am.customer_number
33WHERE account_status='Terminated'
34ORDER BY am.customer_number, account_number
35Please follow instructions given below.
36Write a query to display the total number of withdrawals and total number of deposits being done by
37customer whose customer number ends with 001. The query should display transaction type and the
38number of transactions. Give an alias name as Trans_Count for number of transactions.
39Display the records sorted in ascending order based on transaction type.
40SELECT transaction_type,count(transaction_number) Trans_Count
41FROM account_masteram INNER JOIN transaction_details td
42ON am.account_number=td.account_number
43WHERE customer_number like '%001'
44GROUP BY transaction_type
45ORDER BY transaction_type
46Please follow instructions given below.
47Write a query to display the number of customers who have registration but no account in the bank.
48Give the alias name as Count_Customer for number of customers.
49SELECT count(customer_number) Count_Customer
50FROM customer_master
51WHERE customer_number NOT IN (SELECT customer_number FROM account_master)
52Please follow instructions given below.
53Write a query to display account number and total amount deposited by each account holder ( Including
54the opening balance ). Give the total amount deposited an alias name of Deposit_Amount. Display the
55records in sorted order based on account number.
56SELECT td.account_number, opening_balance+sum(transaction_amount) Deposit_Amount
57FROM account_masteram INNER JOIN transaction_details td
58ON am.account_number=td.account_number
59WHERE transaction_type='deposit'
60GROUP BY account_number
61ORDER BY account_number
62Please follow instructions given below.
63Write a query to display the number of accounts opened in each city .The Query should display Branch
64City and number of accounts as No_of_Accounts.For the branch city where we don’t have any accounts
65opened display 0. Display the records in sorted order based on branch city.
66select
67branch_master.branch_city, count(account_master.account_number) as No_of_Accounts from
68branch_master left join account_master on account_master.branch_id=branch_master.branch_id
69group by branch_master.branch_city order by branch_city;
70Please follow instructions given below.
71Write a query to display the firstname of the customers who have more than 1 account. Display the
72records in sorted order based on firstname.
73selectfirstname
74FROM customer_master cm INNER JOIN account_master am
75ON cm.customer_number=am.customer_number
76group by firstname
77having count(account_number)>1
78order by firstname;
79Please follow instructions given below.
80Write a query to display the customer number, customer firstname, customer lastname who has taken
81loan from more than 1 branch.
82Display the records sorted in order based on customer number.
83SELECT ld.customer_number, firstname, lastname
84FROM customer_master cm INNER JOIN loan_detailsld
85ON cm.customer_number=ld.customer_number
86GROUP BY customer_number
87HAVING count(branch_id)>1
88ORDER BY customer_number
89Please follow instructions given below.
90Write a query to display the customer’s number, customer’s firstname, customer’s city and branch city
91where the city of the customer and city of the branch is different.
92Display the records sorted in ascending order based on customer number.
93selectcustomer_master.customer_number, firstname, customer_city, branch_city
94fromaccount_master inner join customer_master on account_master.customer_number =
95customer_master.customer_number
96inner join branch_master on account_master.branch_id = branch_master.branch_id
97wherecustomer_city != branch_city order by customer_master.customer_number;
98Please follow instructions given below.
99Write a query to display the number of clients who have asked for loans but they don’t have any
100account in the bank though they are registered customers. Give the count an alias name of Count.
101SELECT count(ld.customer_number) Count
102FROM customer_master cm INNER JOIN loan_detailsld
103ON cm.customer_number=ld.customer_number
104WHERE cm.customer_number NOT IN ( SELECTcustomer_number FROM account_master)
105Please follow instructions given below.
106Write a query to display the account number who has done the highest transaction.
107For example the account A00023 has done 5 transactions i.e. suppose 3 withdrawal and 2 deposits.
108Whereas the account A00024 has done 3 transactions i.e. suppose 2 withdrawals and 1 deposit. So
109account number of A00023 should be displayed.
110In case of multiple records, display the records sorted in ascending order based on account number.
111SELECT td.account_number
112FROM account_masteram INNER JOIN transaction_details td
113ON am.account_number=td.account_number
114group by td.account_number
115having count(td.transaction_number)>=ALL
116(SELECT count(td.transaction_number)
117FROM account_masteram INNER JOIN transaction_details td
118ON am.account_number=td.account_number
119group by td.account_number) order by am.account_number;
120Please follow instructions given below.
121Write a query to show the branch name,branch city where we have the maximum customers.
122For example the branch B00019 has 3 customers, B00020 has 7 and B00021 has 10. So branch id B00021
123is having maximum customers. If B00021 is Koramangla branch Bangalore, Koramangla branch should be
124displayed along with city name Bangalore.
125In case of multiple records, display the records sorted in ascending order based on branch name.
126selectbranch_name,branch_city
127FROM branch_master INNER JOIN account_master
128ON branch_master.branch_id=account_master.branch_id
129group by branch_name
130having count(customer_number)>=ALL
131(select count(customer_number)
132FROM branch_master INNER JOIN account_master
133ON branch_master.branch_id=account_master.branch_id
134group by branch_name) order by branch_name;
135Please follow instructions given below.
136Write a query to display all those account number, deposit, withdrawal where withdrawal is more than
137deposit amount. Hint: Deposit should include opening balance as well.
138For example A00011 account opened with Opening Balance 1000 and A00011 deposited 2000 rupees
139on 2012-12-01 and 3000 rupees on 2012-12-02. The same account i.e A00011 withdrawn 3000 rupees
140on 2013-01-01 and 7000 rupees on 2013-01-03. So the total deposited amount is 6000 and total
141withdrawal amount is 10000. So withdrawal amount is more than deposited amount for account
142number A00011.
143Display the records sorted in ascending order based on account number.
144SELECT td.account_number,sum(CASE WHEN transaction_type='Deposit' THEN transaction_amount
145END)
146+(SELECT opening_balance FROM account_master am2 where
147am2.account_number=am.account_number) Deposit,
148sum(CASE WHEN transaction_type='Withdrawal' THEN transaction_amount END) Withdrawal
149FROM account_masteram INNER JOIN transaction_details td
150ON am.account_number=td.account_number
151GROUP BY td.account_number
152HAVING Withdrawal > Deposit
153ORDER BY am.account_number
154Please follow instructions given below.
155Write a query to show the balance amount for account number that ends with 001.
156Note: Balance amount includes account opening balance also. Give alias name as Balance_Amount.
157For example A00015 is having an opening balance of 1000. A00015 has deposited 2000 on 2012-06-12
158and deposited 3000 on 2012-07-13. The same account has drawn money of 500 on 2012-08-12 , 500 on
1592012-09-15, 1000 on 2012-12-17. So balance amount is 4000 i.e (1000 (opening balance)+2000+3000 ) –
160(500+500+1000).
161SELECT (SUM(CASE WHEN transaction_type='Deposit'
162THEN transaction_amount END)) -
163(SUM(CASE WHEN transaction_type='Withdrawal'
164THEN transaction_amount END))+(select opening_balance
165fromaccount_master where account_number like '%001') AS Balance_Amount
166FROM transaction_details where account_number like '%001'
167Please follow instructions given below.
168Display the customer number, customer's first name, account number and number of transactions
169being made by the customers from each account. Give the alias name for number of transactions as
170Count_Trans. Display the records sorted in ascending order based on customer number and then by
171account number.
172SELECT cm. customer_number,firstname, am.account_number,count(transaction_number) Count_Trans
173FROM customer_master cm inner JOIN account_master am
174ON cm.customer_number=am.customer_number
175INNER JOIN transaction_details td
176ON am.account_number=td.account_number
177group by am.account_number order by cm.customer_number, am.account_number
178Please follow instructions given below.
179Write a query to display the customer’s firstname who have multiple accounts (atleast 2 accounts).
180Display the records sorted in ascending order based on customer's firstname.
181SELECT firstname
182FROM customer_master INNER JOIN account_master
183ON customer_master.customer_number=account_master.customer_number
184GROUP BY firstname
185having count(firstname)>=2 order by firstname;
186Please follow instructions given below.
187Write a query to display the customer number, firstname, lastname for those client where total loan
188amount taken is maximum and at least taken from 2 branches.
189For example the customer C00012 took a loan of 100000 from bank branch with id B00009 and C00012
190Took a loan of 500000 from bank branch with id B00010. So total loan amount for customer C00012 is
191600000. C00013 took a loan of 100000 from bank branch B00009 and 200000 from bank branch B00011.
192So total loan taken is 300000. So loan taken by C00012 is more then C00013.
193SELECT ld.customer_number, firstname, lastname
194FROM customer_master cm INNER JOIN loan_detailsld
195ON cm.customer_number=ld.customer_number
196group by customer_number
197having count(branch_id)>=2 and sum(loan_amount)>=All(select sum(loan_amount) from loan_details
198group by customer_number)
199Please follow instructions given below.
200Write a query to display the customer’s number, customer’s firstname, branch id and loan amount for
201people who have taken loans..
202Display the records sorted in ascending order based on customer number and then by branch id and
203then by loan amount.
204SELECT ld.customer_number, firstname,branch_id, loan_amount
205FROM customer_master cm INNER JOIN loan_detailsld
206ON cm.customer_number=ld.customer_number order by cm.customer_number, branch_id,
207loan_amount
208Please follow instructions given below.
209Write a query to display city name and count of branches in that city. Give the count of branches an alias
210name of Count_Branch.
211Display the records sorted in ascending order based on city name.
212SELECT branch_city, count(branch_id) Count_Branch
213FROM branch_master
214GROUP BY branch_city
215ORDER BY branch_city
216Please follow instructions given below.
217Write a query to display account id, customer’s firstname, customer’slastname for the customer’s
218whose account is Active.
219Display the records sorted in ascending order based on account id /account number.
220SELECT account_number, firstname, lastname
221FROM customer_master cm INNER JOIN account_master am
222ON cm.customer_number=am.customer_number
223WHERE account_status='Active'
224ORDER BY account_number
225Please follow instructions given below.
226Write a query to display customer’s number, first name and middle name. For the customers who don’t
227have middle name, display their last name as middle name. Give the alias name as Middle_Name.
228Display the records sorted in ascending order based on customer number.
229SELECT customer_number,firstname,coalesce(middlename,lastname) Middle_Name
230FROM customer_master order by customer_number
231Please follow instructions given below.
232Write a query to display the customer number ,firstname, customer’s date of birth . Display the records
233sorted in ascending order of date of birth year and within that sort by firstname in ascending order.
234SELECT customer_number,firstname,customer_date_of_birth
235FROM customer_master order by year(customer_date_of_birth), firstname;
236Please follow instructions given below.
237Write a query to display the customersfirstname, city and account number whose occupation are not
238into Business, Service or Student.
239Display the records sorted in ascending order based on customer first name and then by account
240number.
241SELECT firstname, customer_city,account_number
242FROM customer_master cm INNER JOIN account_master am
243ON cm.customer_number=am.customer_number
244WHERE occupation !='Service' and occupation != 'Student' and occupation != 'Business' order by
245firstname, account_number
246
247 
248
2491.	Write a query to display the average monthly ticket cost for each flight in ABC  Airlines. The query should display the Flight_Id,From_location,To_Location,Month Name as “Month_Name” and average price as “Average_Price”
250Display the records sorted in ascending order based on flight id and then by Month Name.
251SELECT f.flight_id,
252              f.from_location,
253              f.to_location,
254              fp.Month_Name,
255              fp.Average_Price  
256FROM  air_flight f,
257(
258SELECT flight_id,
259 MONTHNAME(flight_departure_date) AS Month_Name,
260               AVG(price) as Average_Price
261FROM air_flight_details
262GROUP BY flight_id,  MONTHNAME(flight_departure_date)
263) fp
264WHERE f.flight_id = fp.flight_id
265and f.airline_name = 'ABC AIRLINES'
266order by f.flight_id, fp.Month_Name;
267
268(or)
269SELECT f.flight_id,f.from_location,f.to_location,
270monthname(af.flight_departure_date) as Month_Name,
271AVG(price) as Average_Price
272from air_flight f inner join air_flight_details af
273on f.flight_id = af.flight_id
274where f.airline_name = 'abc'
275group by f.flight_id,f.from_location,f.to_location,Month_Name 
276order by f.flight_id, Month_Name;
277
278
2792.	Write a query to display the number of flight services between locations in a month. The Query should display From_Location, To_Location, Month as “Month_Name” and number of flight services as “No_of_Services”.  
280<br>Hint: The Number of Services can be calculated from the number of scheduled departure dates of a flight.
281<br> The records should be displayed in ascending order based on From_Location and then by To_Location and then by month name.
282
283SELECT f.from_location,
284              f.to_location,
285             s.Month_Name,
286             SUM(s.No_of_Services) AS No_of_Services
287FROM air_flight f  JOIN
288(SELECT flight_id,
289              MONTHNAME(flight_departure_date) AS Month_Name,
290              COUNT(flight_departure_date) AS No_of_Services
291FROM  air_flight_details
292GROUP BY flight_id,  MONTHNAME(flight_departure_date)
293) s
294ON s.flight_id  = f.flight_id
295GROUP BY f.from_location,
296                f.to_Location,
297               s.Month_Name
298order by f.from_location,
299                f.to_Location, s.Month_Name;
300
301(or)
302
303SELECT f.from_location,f.to_location,
304monthname(af.flight_departure_date) as Month_Name,
305count(af.flight_departure_date) as No_of_Services 
306from air_flight f inner join air_flight_details af
307on f.flight_id = af.flight_id
308group by f.from_location,f.to_location,Month_Name
309order by f.from_location,f.to_Location,Month_Name;
310
3113.  Write a query to display the customer(s) who has/have booked least number of tickets in ABC Airlines. The Query should display profile_id, customer’s first_name, Address and Number of tickets booked as “No_of_Tickets”
312Display the records sorted in ascending order based on customer's first name.
313
314SELECT cus.profile_id,
315              cus.first_name,
316              cus.address,
317              mintkt.No_of_Tickets
318FROM  air_passenger_profile cus,
319           (SELECT MIN(s.Tot_No_of_Tickets) AS No_of_Tickets
320            FROM   (SELECT profile_id, 
321                                         COUNT(ticket_id) AS Tot_No_of_Tickets
322                           FROM   air_ticket_info
323                           GROUP BY profile_id
324                          ) s
325           ) mintkt,
326          ( SELECT profile_id,
327                         COUNT(ticket_id) AS Tot_No_of_Tickets
328            FROM  air_ticket_info
329            GROUP BY profile_id
330          ) tottkt
331WHERE mintkt.No_of_Tickets = tottkt.Tot_No_of_Tickets
332AND      cus.profile_id = tottkt.profile_id
333order by cus.first_name;
334(OR)
335SELECT cus.profile_id,cus.first_name,cus.address,
336              count(ticket_id) as No_of_Tickets
337FROM  air_passenger_profile cus inner join air_ticket_info ati
338on cus.profile_id = ati.profile_id
339inner join air_flight af
340on  af.flight_id=ati.flight_id
341where af.airline_name = 'abc'
342group by cus.profile_id,cus.first_name,cus.address
343having count(ticket_id)<=all(select count(ticket_id) 
344FROM  air_passenger_profile cus inner join air_ticket_info ati
345on cus.profile_id = ati.profile_id
346inner join air_flight af
347on  af.flight_id=ati.flight_id)
348order by cus.first_name;
349
3504.	Write a query to display the number of tickets booked from Chennai to Hyderabad. The Query should display passenger profile_id,first_name,last_name, Flight_Id , Departure_Date and  number of tickets booked as “No_of_Tickets”.
351
352Display the records sorted in ascending order based on profile id and then by flight id and then by departure date.
353SELECT cus.profile_id,
354              cus.first_name,
355              cus.last_name,
356              tkt.flight_id,
357             tkt.flight_departure_date,
358             tkt.No_of_Tickets
359FROM   air_passenger_profile cus JOIN
360(
361SELECT profile_id, FLIGHT_ID,FLIGHT_DEPARTURE_DATE,COUNT(ticket_id) AS No_of_Tickets
362FROM   air_ticket_info
363WHERE  flight_id IN (SELECT flight_id 
364                        FROM   air_flight
365                                    WHERE  from_location = 'Chennai'
366         AND    to_location = 'Hyderabad'
367     )
368GROUP BY profile_id,flight_id,flight_departure_date
369)tkt
370ON cus.profile_id = tkt.profile_id
371order by cus.profile_id, tkt.flight_id, tkt.flight_departure_date;
372
373
374(OR)
375
376SELECT cus.profile_id,cus.first_name,cus.last_name,ati.flight_id,
377             ati.flight_departure_date,
378            COUNT(ticket_id) AS No_of_Tickets
379FROM air_ticket_info ati inner join air_passenger_profile cus 
380on cus.profile_id = ati.profile_id
381inner join air_flight af
382on  af.flight_id=ati.flight_id
383WHERE  from_location = 'Chennai'
384AND  to_location = 'Hyderabad'
385group by cus.profile_id,cus.first_name,cus.last_name,ati.flight_id,
386             ati.flight_departure_date
387order by cus.profile_id, ati.flight_id, ati.flight_departure_date;
388
3895.	Write a query to display flight id,from location, to location and ticket price of flights whose departure is in the month of april.
390Display the records sorted in ascending order based on flight id and then by from location.
391SELECT fd.flight_id,af.FROM_LOCATION,af.TO_LOCATION, fd.price
392 FROM air_flight_details fd join air_flight af 
393on af.FLIGHT_ID=fd.FLIGHT_ID 
394where substring(fd.flight_departure_date,6,2)='04' 
395order by fd.flight_id, af.FROM_LOCATION;
396
3976.	Write a query to display the average cost of the tickets in each flight on all scheduled dates. The query should display flight_id, from_location, to_location and Average price as “Price”.
398
399Display the records sorted in ascending order based on flight id and then by from_location and then by to_location.
400SELECT f.flight_id,
401              f.from_location,
402              f.to_location,
403             AVG(fd.Price) AS Price
404FROM  air_flight f JOIN
405             air_flight_details fd
406ON      f.flight_id = fd.flight_id
407GROUP BY f.flight_id 
408order by f.flight_id, f.from_location, f.to_location;
409
4107.	Write a query to display the customers who have booked tickets from Chennai to Hyderabad. The query should display profile_id, customer_name (combine first_name & last_name with comma in b/w), address of the customer.
411
412Give an alias to the name as customer_name.
413 Hint: Query should fetch unique customers irrespective of multiple tickets booked.
414
415Display the records sorted in ascending order based on profile id.
416SELECT DISTINCT c.profile_id,
417              CONCAT(c.first_name, ',',c.last_name) AS customer_name,
418              c.address
419FROM  air_passenger_profile c JOIN    air_ticket_info t
420ON      c.profile_id = t.profile_id
421JOIN   air_flight f
422ON     f.flight_id = t.flight_id
423WHERE  f.from_location = 'Chennai'
424AND  f.to_location = 'Hyderabad'
425order by c.profile_id;
426
4278.	Write a query to display profile id of the passenger(s) who has/have booked maximum number of tickets.
428In case of multiple records, display the records sorted in ascending order based on profile id.
429SELECT profile_id 
430FROM air_ticket_info
431 group by profile_id
432 having count(profile_id)>=all(select count(profile_id)
433 from air_ticket_info
434 group by profile_id) 
435order by profile_id;
436
4379.	Write a query to display the total number of tickets as “No_of_Tickets” booked in each flight in ABC Airlines. The Query should display the flight_id, from_location, to_location and the number of tickets.
438Display only the flights in which atleast 1 ticket is booked.
439Display the records sorted in ascending order based on flight id.
440SELECT f.flight_id,
441          f.from_location,
442          f.to_location,
443         COUNT(t.ticket_id) AS No_of_Tickets
444FROM air_ticket_info t JOIN
445            air_flight f
446ON      f.flight_id = t.flight_id
447where AIRLINE_NAME = 'abc'
448GROUP by f.flight_id
449ORDER by f.flight_id;
450
45110.	Write a query to display the no of services offered by each flight and the total price of the services. The Query should display flight_id, number of services as “No_of_Services” and the cost as “Total_Price” in the same order. 
452
453Order the result by Total Price in descending order and then by flight_id in descending order.
454
455Hint:The number of services can be calculated from the number of scheduled departure dates of the flight
456SELECT flight_id,
457             COUNT(flight_departure_date) AS No_of_Services,
458             SUM(price) AS Total_Price
459FROM  air_flight_details
460GROUP BY flight_id 
461order by total_price DESC, flight_id DESC;
462
46311.	Write a query to display the number of passengers who have travelled in each flight in each scheduled date. The Query should display flight_id, flight_departure_date and the number of passengers as “No_of_Passengers” in the same order.
464Display the records sorted in ascending order based on flight id and then by flight departure date.
465SELECT flight_id,
466              flight_departure_date,
467              COUNT(ticket_id) AS No_of_Passengers
468FROM   air_ticket_info 
469GROUP BY flight_id   flight_departure_date
470ORDER BY flight_id, flight_departure_date;
471
47212.	Write a query to display profile id of passenger(s) who booked minimum number of tickets.
473In case of multiple records, display the records sorted in ascending order based on profile id.
474SELECT profile_id 
475FROM air_ticket_info
476 group by profile_id 
477having count(profile_id)<=all(select count(profile_id)
478 from air_ticket_info
479 group by profile_id) 
480order by profile_id;
481
48213.	Write a query to display unique passenger profile id, first name, mobile number and email address of passengers who booked ticket to travel from HYDERABAD to CHENNAI.
483Display the records sorted in ascending order based on profile id.
484SELECT distinct ti.PROFILE_ID,pi.first_name,pi.mobile_number, pi.email_id
485 FROM air_ticket_info ti join air_passenger_profile pi 
486on pi.profile_id=ti.profile_id
487 where flight_id in (SELECT FLIGHT_ID FROM air_flight 
488where FROM_LOCATION ='HYDERABAD' 
489and to_location ='CHENNAI') 
490order by ti.profile_id
491
49214.	Write a query to intimate the passengers who are boarding Chennai to Hyderabad Flight on 6th May 2013 stating the delay of 1hr in the departure time. The Query should display the passenger’s profile_id, first_name,last_name, flight_id, flight_departure_date,  actual departure time , actual arrival time , delayed departure time as "Delayed_Departure_Time", delayed arrival time as "Delayed_Arrival_Time" Hint: Distinct  Profile ID should be displayed irrespective of multiple tickets booked by the same profile.
493Display the records sorted in ascending order based on passenger's profile id.
494SELECT DISTINCT p.profile_id,
495p.first_name,
496p.last_name,
497t.flight_id,
498t.flight_departure_date,
499f.departure_time,
500f.arrival_time,
501ADDTIME(f.departure_time,'01:00:00') AS Delayed_Departure_Time,
502ADDTIME(f.arrival_time,'01:00:00') AS Delayed_Arrival_Time
503FROM air_passenger_profile p
504JOIN air_ticket_info t
505ON p.profile_id = t.profile_id
506JOIN air_flight f
507ON t.flight_id = f.flight_id
508WHERE t.flight_departure_date = '2013-05-06'
509AND f.from_location = 'Chennai'
510AND f.to_location = 'Hyderabad'
511order by p.profile_id;
512
51315.	Write a query to display the number of tickets as “No_of_Tickets” booked by Kochi Customers. The Query should display the Profile_Id, First_Name, Base_Location and number of tickets booked.
514
515 Hint: Use String functions to get the base location of customer from their Address and give alias name as “Base_Location”
516
517 Display the records sorted in ascending order based on customer first name.
518SELECT cus.profile_id,
519              cus.First_name,        
520              SUBSTR(cus.address,INSTR(cus.address,',')+1,INSTR(cus.address,'-')- INSTR(cus.address,',')-1) 
521              AS Base_Location,
522             s.No_of_Tickets
523FROM  air_passenger_profile cus JOIN
524(
525SELECT profile_id,
526           COUNT(ticket_id) AS No_of_Tickets
527FROM  air_ticket_info
528GROUP BY profile_id
529)s
530ON s.profile_id = cus.profile_id
531AND  SUBSTR(cus.address,INSTR(cus.address,',')+1,INSTR(cus.address,'-')- INSTR(cus.address,',')-1) = 'Kochi' 
532order by first_name;
533(OR)
534select a.profile_id,first_name,SUBSTR(a.address,INSTR(a.address,',')+1,INSTR(a.address,'-')- INSTR(a.address,',')-1) as baseadres,
535count(ticket_id) as No_of_Tickets
536from air_passenger_profile a
537join air_ticket_info ati
538on a.profile_id=ati.profile_id
539join air_flight af
540on af.flight_id=ati.flight_id
541where af.from_location='kochi'
542group by a.profile_id,first_name,baseadres
543order by first_name;
544
54516.	Write a query to display the flight_id, from_location, to_location, number of Services as “No_of_Services” offered in the month of May.
546SELECT f.flight_id,
547              f.from_location,
548              f.to_location,
549             s.No_of_Services
550FROM  air_flight f JOIN
551(
552SELECT flight_id,
553             COUNT(flight_departure_date) AS No_of_Services
554FROM  air_flight_details
555WHERE MONTH(flight_departure_date) = 5
556GROUP BY flight_id
557) s
558ON f.flight_id = s.flight_id
559order by f.flight_id;
560                                                              (or)
561SELECT f.flight_id,
562              f.from_location,
563              f.to_location,
564             count(flight_departure_date) as No_of_Services
565FROM  air_flight f JOIN air_flight_details af 
566on f.flight_id = af.flight_id
567where extract(month from flight_departure_date)='05'
568group by f.flight_id,f.from_location,f.to_location
569order by f.flight_id;
570
57117.	Write a query to display profile id,last name,mobile number and email id of passengers whose base location is chennai.
572Display the records sorted in ascending order based on profile id.
573SELECT PROFILE_ID, LAST_NAME, MOBILE_NUMBER, EMAIL_ID
574 FROM air_passenger_profile
575 where address like '%CHENNAI%' 
576ORDER BY PROFILE_ID;
577
57818.	Write a query to display number of flights between 6.00 AM and 6.00 PM from chennai. Hint Use FLIGHT_COUNT as alias name.
579SELECT count(flight_id) FLIGHT_COUNT
580 FROM air_flight 
581where FROM_LOCATION='CHENNAI' 
582and departure_time between '06:00:00' and '18:00:00';
583
58419.	Write a query to display unique profile id,first name , email id and contact number of passenger(s) who travelled on flight with id 3148. Display the records sorted in ascending order based on first name.
585SELECT distinct ti.PROFILE_ID,pi.first_name,pi.email_id,pi.mobile_number
586 FROM air_ticket_info ti join air_passenger_profile pi
587 on pi.profile_id=ti.profile_id 
588where flight_id=3148 
589order by pi.first_name;
590
59120.	Write a query to display flight id,departure date,flight type of all flights. Flight type can be identified based on the following rules : if ticket price is less than 3000 then 'AIR PASSENGER',ticket price between 3000 and less than 4000 'AIR BUS' and ticket price between 4000 and greater than 4000 then 'EXECUTIVE PASSENGER'. Hint use FLIGHT_TYPE as alias name.
592
593Display the records sorted in ascendeing order based on flight_id and then by departure date.
594select flight_id,flight_departure_date,
595case 
596when price<3000 then 'AIR PASSENGER'
597when price>=3000 and price<4000 then 'AIR BUS'
598when price>=4000 then 'EXECUTIVE PASSENGER'
599end  as  FLIGHT_TYPE 
600from  air_flight_details 
601order by flight_id, flight_departure_date;
602
60321.	Write a query to display the credit card type and no of credit cards used on the same type.  Display the records sorted in ascending order based on credit card type.
604Hint: Use CARD_COUNT AS Alias name for no of cards.
605
606SELECT CARD_TYPE, count(card_type) CARD_COUNT 
607FROM air_credit_card_details
608 group by CARD_TYPE 
609order by CARD_TYPE;
610
61122.	Write a Query to display serial no, first name, mobile number, email id of all the passengers who holds email address from gmail.com.
612The Serial No will be the last three digits of profile ID.
613Hint: Use SERIAL_NO as Alias name for serial number.
614<br>
615Display the records sorted in ascending order based on name.
616select substring(profile_id,4) SERIAL_NO,first_name,mobile_number,email_id
617 from air_passenger_profile 
618where email_id like '%@gmail.com' 
619order by first_name;
620
62123.	Write a query to display the flight(s) which has least number of services in the month of May. The Query should fetch flight_id, from_location, to_location, least number of Services as “No_of_Services” Hint: Number of services offered can be calculated from the number of scheduled departure dates of a flight
622
623 If there are multiple flights, display them sorted in ascending order based on flight id.
624SELECT f.flight_id,
625              f.from_location,
626              f.to_location,
627             mins.No_of_Services
628FROM  air_flight f,
629(
630SELECT MIN(s.No_of_Services) AS No_of_Services
631FROM  (SELECT flight_id,
632                           COUNT(flight_departure_date) AS No_of_Services
633               FROM  air_flight_details fd
634               WHERE MONTH(flight_departure_date) = 5
635               GROUP BY flight_id
636              )s
637) mins,
638(
639SELECT flight_id,
640             COUNT(flight_departure_date) AS No_of_Services
641FROM  air_flight_details fd
642WHERE MONTH(flight_departure_date) = 5
643GROUP BY flight_id
644) ser
645WHERE mins.No_of_Services = ser.No_of_Services
646AND      ser.flight_id = f.flight_id
647order by f.flight_id;
648
649
650                               (OR)
651
652
653 SELECT f.flight_id,f.from_location,f.to_location,
654             COUNT(flight_departure_date) AS No_of_Services
655FROM  air_flight f inner join air_flight_details fd
656on fd.flight_id = f.flight_id
657where MONTH(flight_departure_date) = 5
658group by f.flight_id,f.from_location,f.to_location
659having COUNT(flight_departure_date)<=all(
660select COUNT(flight_departure_date) 
661from air_flight f inner join air_flight_details fd
662on fd.flight_id = f.flight_id
663group by f.flight_id  )
664order by f.flight_id;
66524.	Write a query to display the flights available in Morning, AfterNoon, Evening & Night. The Query should display the Flight_Id, From_Location, To_Location , Departure_Time, time of service as "Time_of_Service". 
666
667Time of Service should be calculated as: From 05:00:01 Hrs to 12:00:00 Hrs -  Morning, 12:00:01 to 18:00:00 Hrs -AfterNoon, 18:00:01 to 24:00:00 - Evening and 00:00:01 to 05:00:00 - Night
668
669Display the records sorted in ascending order based on flight id.
670SELECT f.flight_id,
671f.from_location,
672f.to_location,
673f.Departure_Time,
674CASE
675 WHEN f.departure_time BETWEEN ('05:00:01') AND ('12:00:00')
676THEN 'Morning'
677WHEN f.departure_time BETWEEN ('12:00:01') AND ('18:00:00')
678THEN 'AfterNoon'
679WHEN f.departure_time BETWEEN ('18:00:01') AND ('24:00:00')
680THEN 'Evening'
681WHEN f.departure_time BETWEEN ('00:00:01') AND ('05:00:00')
682THEN 'Night'
683END AS Time_of_Service
684FROM air_flight f
685order by f.flight_id;
686
68725.	Write a query to display the number of flights flying from each location. The Query should display the from location and the number of flights to other locations as “No_of_Flights”. 
688
689Hint: Get the distinct from location and to location.
690
691Display the records sorted in ascending order based on from location.
692SELECT f.from_location ,
693             COUNT(f.flight_id) AS No_of_Flights
694FROM  air_flight f
695JOIN
696(
697SELECT DISTINCT from_location AS Location
698FROM  air_flight
699UNION
700SELECT DISTINCT to_location AS Location
701FROM air_flight
702)a
703ON f.from_location = a.location
704GROUP BY f.from_location
705ORDER BY f.from_location;
706
707(OR)
708select distinct from_location,count(flight_id)
709FROM air_flight
710group by from_location
711ORDER BY from_location;
712
71326.	Write a query to display the number of passengers traveled in each flight in each scheduled date. The Query should display flight_id,from_location,To_location, flight_departure_date and the number of passengers as “No_of_Passengers”. 
714
715Hint: The Number of passengers inclusive of all the tickets booked with single profile id.
716
717Display the records sorted in ascending order based on flight id and then by flight departure date.
718SELECT f.flight_id,
719              f.from_location,
720              f.to_location,
721              t.FLIGHT_DEPARTURE_DATE,
722              t.No_of_Passengers
723FROM  air_flight f
724JOIN
725(
726SELECT flight_id,
727              FLIGHT_DEPARTURE_DATE,
728              COUNT(ticket_id) AS No_of_Passengers
729FROM   air_ticket_info 
730GROUP BY flight_id,
731                              FLIGHT_DEPARTURE_DATE
732)t
733ON f.flight_id = t.flight_id
734order by f.flight_id, t.flight_departure_date;
735
736(OR)
737SELECT f.flight_id,
738              f.from_location,
739              f.to_location,
740              ati.flight_departure_date,
741              count(ati.flight_id) as No_of_Passengers
742FROM  air_flight f inner join air_ticket_info ati
743on f.flight_id = ati.flight_id
744group by f.flight_id,f.from_location,f.to_location,ati.flight_departure_date
745order by f.flight_id, ati.flight_departure_date;
746
74727.	Write a query to display the flight details in which more than 10% of seats have been booked. The query should display Flight_Id, From_Location, To_Location,Total_Seats, seats booked as “No_of_Seats_Booked” .
748
749Display the records sorted in ascending order based on flight id and then by No_of_Seats_Booked.
750SELECT f.flight_id,
751       f.from_location,
752       f.to_location,
753       f.Total_Seats,
754   (f.Total_Seats)-(fd.Available_Seats) AS No_of_Seats_Booked
755FROM  air_flight f JOIN air_flight_details fd
756ON       f.flight_id = fd.flight_id
757Where  (f.Total_Seats)-(fd.Available_seats) > (f.total_seats *0.1)
758order by f.flight_id, No_of_Seats_Booked;
759
76028. Write a query to display the Flight_Id, Flight_Departure_Date, From_Location,To_Location and Duration  of all flights which has duration of travel less than 1 Hour, 10 Minutes.
761SELECT distinct  f.flight_id,
762    fd.flight_departure_date,
763    f.from_location,
764    f.to_location,
765    f.duration
766FROM   air_flight f JOIN air_flight_details fd
767ON     f.flight_id = fd.flight_id
768WHERE  f.duration < '01:10:00'
769order by f.flight_id, fd.flight_departure_date;
770
77129.	Write a query to display the flight_id, from_location,to_location,number of services as “No_of_Services” , average ticket price as “Average_Price” whose average ticket price is greater than the total average ticket cost of  all flights. Order the result by lowest average price.
772SELECT f.flight_id,
773             f.from_location,
774             f.to_location,
775             a.No_of_Services,
776             a.Average_Price
777FROM air_flight f JOIN         
778(
779SELECT flight_id,  COUNT(flight_departure_date) AS No_of_Services,   AVG(price) AS Average_Price
780FROM  air_flight_details
781GROUP BY flight_id
782HAVING AVG(price) > (SELECT AVG(price) FROM air_flight_details)
783)a
784ON f.flight_id = a.flight_id 
785order by average_price ASC
786
787
788(OR)
789SELECT f.flight_id,
790             f.from_location,
791             f.to_location,
792             COUNT(flight_departure_date) AS No_of_Services, 
793  AVG(price) AS Average_Price
794             
795FROM air_flight f JOIN air_flight_details a
796ON f.flight_id = a.flight_id
797group by f.flight_id,
798             f.from_location,
799             f.to_location
800HAVING AVG(price) >=all(
801SELECT AVG(price) FROM air_flight_details) 
802order by average_price ASC;
803 
804
805
806 Please follow instructions given below. 
807<br> 
808Write a query to display category and number of items in that category. Give the count an alias name of Count_category. Display the details on the sorted order of count in descending order. 
809SELECT item_category , count(item_id) Count_category 
810FROM item_master 
811GROUP BY item_category order by count_category DESC 
812Please follow instructions given below. 
813<br> 
814Write a query to display the number of employees in HR department. Give the alias name as No_of_Employees. 
815SELECT count(employee_id) AS No_of_Employees 
816FROM employee_master 
817WHERE department= 'HR' 
818Please follow instructions given below. 
819<br><br> 
820Write a query to display employee id, employee name, designation and department for employees who have never been issued an item as a loan from the company. Display the records sorted in ascending order based on employee id. 
821SELECT employee_id, employee_name, designation, department 
822FROM employee_master WHERE employee_id 
823NOT IN ( SELECT employee_id FROM employee_issue_details) 
824order by employee_id; 
825Please follow instructions given below. 
826<br><br> 
827Write a query to display the employee id, employee name who was issued an item of highest valuation. 
828<br> In case of multiple records, display the records sorted in ascending order based on employee id. 
829[Hint Suppose an item called dinning table is of 22000 and that is the highest price of the item that has been issued. So display the employee id and employee name who issued dinning table whose price is 22000.] 
830SELECT eid.employee_id, employee_name 
831FROM employee_master em INNER JOIN employee_issue_details eid 
832ON em.employee_id=eid.employee_id 
833INNER JOIN item_master im 
834ON eid.item_id=im.item_id 
835WHERE item_valuation=(SELECT max(item_valuation) 
836FROM employee_issue_details eid INNER JOIN item_master im 
837ON eid.item_id=im.item_id) order by eid.employee_id; 
838Please follow instructions given below. 
839<br> 
840Write a query to display issue_id, employee_id, employee_name. 
841Display the records sorted in ascending order based on issue id. 
842SELECT issue_id, eid.employee_id, employee_name 
843FROM employee_master em INNER JOIN employee_issue_details eid 
844ON em.employee_id=eid.employee_id order by issue_id; 
845Please follow instructions given below. 
846<br> 
847Write a query to display employee id, employee name who don’t have loan cards. 
848Display the records sorted in ascending order based on employee id. 
849SELECT employee_id, employee_name 
850FROM employee_master 
851WHERE employee_id NOT IN ( SELECT employee_id FROM employee_card_details ) 
852order by employee_id; 
853Please follow instructions given below. 
854<br> 
855Write a query to count the number of cards issued to an employee “Ram”. Give the count an alias name as No_of_Cards. 
856SELECT count(loan_id) AS No_of_Cards 
857FROM employee_card_details c 
858JOIN employee_master e 
859ON c.employee_id = e.employee_id 
860WHERE e.employee_name= 'Ram' 
861GROUP BY c.employee_id 
862Please follow instructions given below. 
863<br> 
864Write a query to display the count of customers who have gone for loan type stationary. Give the count an alias name as Count_stationary. 
865SELECT count(employee_id) Count_stationary 
866FROM employee_card_details ecd INNER JOIN loan_card_master lcd 
867ON ecd.loan_id=lcd.loan_id 
868WHERE loan_type='stationary' 
869Please follow instructions given below. 
870<br> 
871Write a query to display the employee id, employee name and number of items issued to them. Give the number of items an alias name as Count. Display the details in descending order of count and then by employee id in ascending order. Consider only employees who have been issued atleast 1 item. 
872SELECT eid.employee_id, employee_name, count(item_id) Count 
873FROM employee_master em INNER JOIN employee_issue_details eid 
874ON em.employee_id=eid.employee_id 
875GROUP BY employee_id order by count DESC, eid.employee_id; 
876Please follow instructions given below. 
877<br> 
878Write a query to display the employee id, employee name who was issued an item of minimum valuation. 
879<br> 
880In case of multiple records, display them sorted in ascending order based on employee id. 
881[Hint Suppose an item called pen is of rupees 20 and that is the lowest price. So display the employee id and employee name who issued pen where the valuation is 20.] 
882SELECT eid.employee_id, employee_name 
883FROM employee_master em INNER JOIN employee_issue_details eid 
884ON em.employee_id=eid.employee_id 
885INNER JOIN item_master im 
886ON eid.item_id=im.item_id 
887WHERE item_valuation=(SELECT min(item_valuation) 
888FROM employee_issue_details eid INNER JOIN item_master im 
889ON eid.item_id=im.item_id) 
890order by eid.employee_id; 
891Please follow instructions given below. 
892<br> 
893Write a query to display the employee id, employee name and total valuation of the product issued to each employee. Give the alias name as TOTAL_VALUATION. 
894<br> 
895Display the records sorted in ascending order based on employee id. 
896<br> 
897Consider only employees who have been issued atleast 1 item. 
898SELECT em.employee_id, employee_name, sum(item_valuation) TOTAL_VALUATION 
899FROM employee_master em INNER JOIN employee_issue_details eid 
900ON em.employee_id=eid.employee_id 
901INNER JOIN item_master im 
902ON eid.item_id=im.item_id 
903GROUP BY eid.employee_id 
904ORDER BY eid.employee_id; 
905Please follow instructions given below. 
906<br> 
907Write a query to display distinct employee id, employee name who kept the item issued for more than a year. Hint: Use Date time function to calculate the difference between item issue and return date. Display the records only if it is more than 365 Days. 
908<br> 
909Display the records sorted in ascending order based on employee id. 
910SELECT DISTINCT eid.employee_id,employee_name 
911FROM employee_issue_details eid JOIN employee_master em 
912ON eid.employee_id=em.employee_id 
913WHERE datediff(return_date,issue_date) > 365 
914order by eid.employee_id; 
915Please follow instructions given below. 
916<br> 
917Write a query to display employee id, employee name and count of items of those who asked for more than 1 furniture. Give the alias name for count of items as COUNT_ITEMS. 
918<br> 
919Display the records sorted in ascending order on employee id. 
920SELECT eid.employee_id, employee_name, count(eid.item_id) COUNT_ITEMS 
921FROM employee_issue_details eid JOIN item_master im 
922ON eid.item_id=im.item_id 
923JOIN employee_master em 
924ON eid.employee_id=em.employee_id 
925WHERE item_category='furniture' 
926GROUP BY employee_id 
927HAVING COUNT_ITEMS>1 
928order by eid.employee_id; 
929Please follow instructions given below. 
930<br> 
931Write a query to display the number of men & women Employees. The query should display the gender and number of Employees as No_of_Employees. Display the records sorted in ascending order based on gender. 
932SELECT gender , count(gender) No_of_Employees 
933FROM employee_master 
934GROUP BY gender ORDER BY gender 
935Please follow instructions given below. 
936<br> 
937Write a query to display employee id, employee name who joined the company after 2005. Display the records sorted in ascending order based on employee id. 
938SELECT employee_id, employee_name 
939FROM employee_master 
940WHERE extract(year from date_of_joining)>2005 
941order by employee_id; 
942Please follow instructions given below. 
943<br> 
944Write a query to get the number of items of the furniture category issued and not issued. The query should display issue status and the number of furniture as No_of_Furnitures. 
945Display the records sorted in ascending order based on issue_status. 
946SELECT issue_status, count(item_id) No_of_Furnitures 
947FROM item_master 
948WHERE item_category='Furniture' 
949GROUP BY issue_status 
950ORDER BY issue_status 
951Please follow instructions given below. 
952<br> 
953Write a query to find the number of items in each category, make and description. The Query should display Item Category, Make, description and the number of items as No_of_Items. Display the records in ascending order based on Item Category, then by item make and then by item description. 
954SELECT item_category, item_make,item_description, count(item_description) No_of_Items FROM item_master 
955GROUP BY item_category, item_make, item_description ORDER BY item_category, item_make,item_description; 
956Please follow instructions given below. 
957<br> 
958Write a query to display employee id, employee name, item id and item description of employees who were issued item(s) in the month of January 2013. Display the records sorted in order based on employee id and then by item id in ascending order. 
959SELECT eid.employee_id, employee_name, eid.item_id, item_description 
960FROM employee_issue_details eid JOIN employee_master em 
961ON eid.employee_id=em.employee_id 
962JOIN item_master im 
963ON eid.item_id=im.item_id 
964WHERE extract(month from issue_date)=1 and extract(year from issue_date)=2013 
965ORDER BY eid.employee_id, eid.item_id; 
966Please follow instructions given below. 
967<br> 
968Write a query to display the employee id, employee name and count of item category of the employees who have been issued items in at least 2 different categories. 
969Give the alias name for category count as COUNT_CATEGORY. 
970Display the records sorted in ascending order based on employee id. 
971SELECT em.employee_id,employee_name, count(distinct item_category) COUNT_CATEGORY 
972FROM employee_issue_details eid JOIN item_master im 
973ON eid.item_id=im.item_id 
974JOIN employee_master em 
975ON eid.employee_id=em.employee_id 
976GROUP BY employee_id 
977HAVING COUNT_CATEGORY>=2 
978ORDER BY employee_id; 
979Please follow instructions given below. 
980<br> 
981Write a query to display the item id , item description which was never issued to any employee. Display the records sorted in ascending order based on item id. 
982SELECT item_id, item_description 
983FROM item_master 
984WHERE item_id 
985NOT IN ( SELECT item_id from employee_issue_details) 
986ORDER BY item_id; 
987Please follow instructions given below. 
988<br> 
989Write a query to display the employee id, employee name and&nbsp;&nbsp;total valuation&nbsp;for the employees who has issued minimum total valuation of the product. Give the alias name for total valuation as TOTAL_VALUATION. 
990[Hint: Suppose an employee E00019 issued item of price 5000, 10000, 12000 and E00020 issue item of price 2000, 7000 and 1000. So the valuation of items taken by E00019 is 27000 and for E00020 it is 10000. So the employee id, employee name of E00020 should be displayed. ] 
991select employee_issue_details.employee_id,employee_master.employee_name,sum(item_master.item_valuation)as TOTAL_VALUATION from 
992employee_issue_details inner join item_master 
993on item_master.item_id = employee_issue_details.item_id 
994inner join employee_master 
995on employee_master.employee_id=employee_issue_details.employee_id 
996group by employee_issue_details.employee_id 
997order by TOTAL_VALUATION asc limit 1; 
998Please follow instructions given below. 
999Write a query to display the employee id, employee name, card issue date and card valid date. 
1000Order by employee name and then by card valid date. Give the alias name to display the card valid date as CARD_VALID_DATE. 
1001[Hint: Validity in years for the loan card is given in loan_card_master table. Validity date is calculated by adding number of years in the loan card issue date. If the duration of year is zero then display AS 'No Validity Date'. ] 
1002SELECT ecd.employee_id,employee_name, 
1003card_issue_date, CASE duration_in_years 
1004WHEN 0 THEN 'No Validity Date' 
1005ELSE DATE_ADD(card_issue_date, INTERVAL duration_in_years YEAR) 
1006END CARD_VALID_DATE 
1007FROM employee_master em INNER JOIN 
1008employee_card_details ecd 
1009ON em.employee_id=ecd.employee_id 
1010INNER JOIN loan_card_master lcd 
1011ON ecd.loan_id=lcd.loan_id 
1012order by employee_name, CARD_VALID_DATE; 
1013Please follow instructions given below. 
1014Write a query to display the employee id, employee name who have not issued with any item in the year 2013. Hint: Exclude those employees who was never issued with any of the items in all the years. Display the records sorted in ascending order based on employee id. 
1015SELECT DISTINCT a.employee_id,b.employee_name FROM employee_issue_details a JOIN 
1016employee_master b ON a.employee_id=b.employee_id WHERE a.employee_id NOT IN(SELECT employee_id 
1017FROM employee_issue_details WHERE (EXTRACT(YEAR FROM issue_date)=2013)) 
1018ORDER BY a.employee_id; 
1019Please follow instructions given below. 
1020Write a query to display issue id, employee id, employee name, item id, item description and issue date. Display the data in descending order of date and then by issue id in ascending order. 
1021SELECT issue_id, eid.employee_id, employee_name, im.item_id, item_description,issue_date 
1022FROM employee_issue_details eid INNER JOIN employee_master em 
1023ON eid.employee_id=em.employee_id 
1024INNER JOIN item_master im 
1025ON eid.item_id=im.item_id 
1026ORDER BY issue_date desc, issue_id asc; 
1027Please follow instructions given below. 
1028<br> 
1029Write a query to display the employee id, employee name and total valuation for employee who has issued maximum total valuation of the product.&nbsp; Give the alias name for total valuation as TOTAL_VALUATION.&nbsp; 
1030<br>[Hint: Suppose an employee E00019 issued item of price 5000, 10000, 12000 and E00020 issue item of price 2000, 7000, and 1000. So the valuation of items taken by E00019 is 27000 and for E00020 it is 10000. So the employee id, employee name and total valuation of E00019 should display. ] 
1031select employee_issue_details.employee_id,employee_master.employee_name,sum(item_master.item_valuation)as TOTAL_VALUATION from 
1032employee_issue_details inner join item_master 
1033on item_master.item_id = employee_issue_details.item_id 
1034inner join employee_master 
1035on employee_master.employee_id=employee_issue_details.employee_id 
1036group by employee_issue_details.employee_id 
1037order by TOTAL_VALUATION desc limit 1;
1038
1039 
1040Please follow instructions given below.
1041Write a query to display movie names and number of times that movie is issued to customers. Incase
1042movies are never issued to customers display number of times as 0.
1043Display the details in sorted order based on number of times (in descending order) and then by movie
1044name (in ascending order).
1045The Alias name for the number of movies issued is ISSUE_COUNT.
1046SELECT movie_name, count(cid.movie_id) ISSUE_COUNT FROM movies_master mm LEFT JOIN
1047customer_issue_details cid ON mm.movie_id=cid.movie_id GROUP BY movie_name ORDER BY
1048Issue_count DESC, movie_name;
1049Please follow instructions given below.
1050Write a query to display id,name,age,contact no of customers whose age is greater than 25 and and
1051who have registered in the year 2012. Display contact no in the below format +91-XXX-XXX-XXXX
1052example +91-987-678-3434 and use the alias name as "CONTACT_ISD". If the contact no is null then
1053display as 'N/A' Sort all the records in ascending order based on age and then by name.
1054select customer_id,customer_name, age, coalesce(concat('+91-',substring(contact_no,1,3),'-
1055',substring(contact_no,4,3),'-',substring(contact_no,7)),'N/A') CONTACT_ISD from customer_master
1056where age>25 and substring(date_of_registration,1,4)=2012 order by age asc, customer_name;
1057Please follow instructions given below.
1058<br>
1059Write a query to display the movie category and number of movies in that category. Display records
1060based on number of movies from higher to lower order and then by movie category in ascending order.
1061<br/>Hint: Use NO_OF_MOVIES as alias name for number of movies.
1062SELECT movie_category, count(movie_id) NO_OF_MOVIES FROM movies_master GROUP BY
1063movie_category order by no_of_movies desc, movie_category asc;
1064Please follow instructions given below.
1065<br>
1066Write a query to display the number of customers having card with description “Gold card”. <br/>Hint:
1067Use CUSTOMER_COUNT as alias name for number of customers
1068SELECT count(customer_id) CUSTOMER_COUNT FROM library_card_master lcm INNER JOIN
1069customer_card_details ccd ON lcm.card_id=ccd.card_id WHERE description='Gold Card';
1070Please follow instructions given below.
1071<br>
1072Write a query to display the customer id, customer name, year of registration,library card id, card issue
1073date of all the customers who hold library card. Display the records sorted by customer name in
1074descending order.
1075<br> Use REGISTERED_YEAR as alias name for year of registration.
1076SELECT c.customer_id, c.customer_name, extract(year from c.date_of_registration)
1077REGISTERED_YEAR,cd.card_id,cd.issue_date FROM customer_master c join customer_card_details cd on
1078c.customer_id=cd.customer_id ORDER BY c.customer_name DESC;
1079Please follow instructions given below.
1080<br>
1081Write a query to display issue id, customer id, customer name for the customers who have paid fine and
1082whose name starts with 'R'. Fine is calculated based on return date and actual date of return. If the date
1083of actual return is after date of return then fine need to be paid by the customer.
1084<br>
1085Display the records sorted in ascending order based on customer name.
1086SELECT issue_id ,cid.customer_id, customer_name FROM customer_issue_details cid INNER JOIN
1087customer_master cm ON cid.customer_id=cm.customer_id WHERE actual_date_of_return>return_date
1088and customer_name like 'R%' order by customer_name;
1089Please follow instructions given below.
1090Write a query to display customer id, customer name, card id, card description and card amount in
1091dollars of customers who have taken movie on the same day the library card is registered.
1092For Example Assume John registered a library card on 12th Jan 2013 and he took a movie on 12th Jan
10932013 then display his details.
1094AMOUNT_DOLLAR = amount/52.42 and round it to zero decimal places and display as $Amount.
1095Example Assume 500 is the amount then dollar value will be $10.
1096Hint: Use AMOUNT_DOLLAR as alias name for amount in dollar.
1097Display the records in ascending order based on customer name.
1098SELECT ccd.customer_id, customer_name, ccd.card_id, description,concat('$',round(amount/52.42,0))
1099AMOUNT_DOLLAR FROM customer_master cm INNER JOIN customer_card_details ccd ON
1100cm.customer_id=ccd.customer_id INNER JOIN library_card_master lcm ON ccd.card_id=lcm.card_id
1101INNER JOIN customer_issue_details cid ON cid.customer_id = cm.customer_id WHERE
1102cm.date_of_registration=cid.issue_date order by customer_name;
1103Please follow instructions given below.
1104Write a query to display the customer id, customer name,contact number and address of customers
1105who have taken movies from library without library card and whose address ends with 'Nagar'.
1106Display customer name in upper case. Hint: Use CUSTOMER_NAME as alias name for customer name.
1107Display the details sorted in ascending order based on customer name.
1108SELECT customer_id , upper(customer_name) CUSTOMER_NAME,contact_no,contact_address FROM
1109customer_master WHERE customer_id NOT IN ( select customer_id from customer_card_details ) AND
1110customer_id IN ( SELECT customer_id from customer_issue_details ) and contact_address like '%Nagar'
1111order by customer_name ;
1112Please follow instructions given below.
1113Write a query to display the movie id, movie name,release year,director name of movies acted by the
1114leadactor1 who acted maximum number of movies .Display the records sorted in ascending order based
1115on movie name.
1116select movie_id,movie_name,release_year,director_name from movies_master where
1117lead_actor_name1 in(select lead_actor_name1 from (select lead_actor_name1,count(movie_id)ct from
1118movies_master group by lead_actor_name1)t where t.ct>=all(select count(movie_id) from
1119movies_master group by lead_actor_name1)) order by movie_name;
1120Please follow instructions given below.
1121<br>
1122Write a query to display the customer name and number of movies issued to that customer sorted by
1123customer name in ascending order. If a customer has not been issued with any movie then display 0.
1124<br>Hint: Use MOVIE_COUNT as alias name for number of movies issued.
1125SELECT customer_name, count(movie_id) MOVIE_COUNT FROM customer_master cm LEFT OUTER JOIN
1126customer_issue_details cid ON cm.customer_id=cid.customer_id GROUP BY customer_name ORDER BY
1127customer_name;
1128Please follow instructions given below.
1129<br>
1130Write a query to display serial number,issue id, customer id, customer name, movie id and movie name
1131of all the videos that are issued and display in ascending order based on serial number.
1132<br/>Serial number can be generated from the issue id , that is last two characters of issue id is the
1133serial number.
1134<br/>For Example Assume the issue id is I00005 then the serial number is 05
1135<br/>Hint: Alias name for serial number is 'SERIAL_NO'<br/><br/><br>
1136SELECT substring(issue_id,5,2) SERIAL_NO,issue_id, cid.customer_id,customer_name, mm.movie_id,
1137movie_name FROM customer_issue_details cid INNER JOIN customer_master cm ON
1138cid.customer_id=cm.customer_id INNER JOIN movies_master mm ON cid.movie_id=mm.movie_id order
1139by SERIAL_NO ASC;
1140Please follow instructions given below.
1141<br>
1142Write a query to display the issue id,issue date, customer id, customer name and contact number for
1143videos that are issued in the year 2013.Display the records in decending order based on issue date of the
1144video.
1145SELECT issue_id, issue_date, cid.customer_id, customer_name,contact_no FROM
1146customer_issue_details cid INNER JOIN customer_master cm ON cid.customer_id=cm.customer_id
1147where extract(year from issue_date)=2013 order by issue_date DESC
1148Please follow instructions given below.
1149<br>
1150Write a query to display movie id ,movie name and actor names of movies which are not issued to any
1151customers. <br> Actors Name to be displayed in the below format.LEAD_ACTOR_ONE space ambersant
1152space LEAD_ACTOR_TWO.
1153Example: Assume lead actor one's name is "Jack Tomson" and Lead actor two's name is "Maria" then
1154Actors name will be "Jack Tomsom & Maria"Hint:Use ACTORS as alias name for actors name. <br>
1155Display the records in ascending order based on movie name.
1156SELECT movie_id, movie_name,concat(lead_actor_name1,' & ',lead_actor_name2) ACTORS FROM
1157movies_master WHERE movie_id NOT IN ( SELECT movie_id from customer_issue_details ) order by
1158movie_name asc;
1159Please follow instructions given below.
1160Write a query to display the director's name, movie name and lead_actor_name1 of all the movies
1161directed by the director who directed more than one movie. Display the directors name in capital
1162letters. Use DIRECTOR_NAME as alias name for director name column Display the records sorted in
1163ascending order based on director_name and then by movie_name in descending order.
1164SELECT upper(director_name) DIRECTOR_NAME,movie_name,lead_actor_name1 FROM movies_master
1165WHERE director_name in (SELECT director_name FROM movies_master GROUP BY director_name
1166HAVING count(movie_id)>1) order by director_name, movie_name desc;
1167Please follow instructions given below.
1168<br>
1169Write a query to display number of customers who have registered in the library in the year 2012 and
1170who have given/provided contact number. <br> Hint:Use NO_OF_CUSTOMERS as alias name for number
1171of customers.
1172SELECT count(customer_id) NO_OF_CUSTOMERS FROM customer_master WHERE extract(year from
1173date_of_registration)=2012 and contact_no is not null
1174Please follow instructions given below.
1175<br>
1176Write a query to display the customer's name, contact number,library card id and library card
1177description of all the customers irrespective of customers holding a library card. If customer contact
1178number is not available then display his address. Display the records sorted in ascending order based on
1179customer name. Hint: Use CONTACT_DETAILS as alias name for customer contact.
1180SELECT c.customer_name,coalesce(c.contact_no,c.contact_address)
1181CONTACT_DETAILS,cd.card_id,cd.description FROM customer_master c left join customer_card_details
1182ccd on ccd.customer_id=c.customer_id left join library_card_master cd on cd.card_id=ccd.card_id order
1183by customer_name;
1184Please follow instructions given below.
1185Write a query to display the customer id, customer name and number of times the same movie is issued
1186to the same customers who have taken same movie more than once. Display the records sorted by
1187customer name in decending order For Example: Assume customer John has taken Titanic three times
1188and customer Ram has taken Die hard only once then display the details of john. Hint: Use
1189NO_OF_TIMES as alias name for number of times
1190SELECT cid.customer_id, customer_name,count(movie_id) NO_OF_TIMES FROM customer_master cm
1191INNER JOIN customer_issue_details cid ON cm.customer_id=cid.customer_id group by customer_id,
1192customer_name,movie_id having count(movie_id)>1 order by customer_name desc;
1193Please follow instructions given below.
1194Write a query to display customer id, customer name,contact number, movie category and number of
1195movies issued to each customer based on movie category who has been issued with more than one
1196movie in that category. Example: Display contact number as "+91-876-456-2345" format.&nbsp;
1197<br>Hint:Use NO_OF_MOVIES as alias name for number of movies column.
1198<br>Hint:Use CONTACT_ISD as alias name for contact number.
1199<br> Display the records sorted in ascending order based on customer name and then by movie
1200category.
1201SELECT cid.customer_id,customer_name,concat('+91-',substring(contact_no,1,3),'-
1202',substring(contact_no,4,3),'-',substring(contact_no,7))
1203CONTACT_ISD,movie_category,count(movie_category) NO_OF_MOVIES FROM customer_issue_details
1204cid INNER JOIN movies_master mm ON cid.movie_id=mm.movie_id INNER JOIN customer_master cm
1205ON cm.customer_id=cid.customer_id group by
1206customer_id,customer_name,CONTACT_ISD,movie_category having count(movie_category)>1 order by
1207customer_name, movie_category;
1208Please follow instructions given below.
1209Write a query to display customer id and customer name of customers who has been issued with
1210maximum number of movies and customer who has been issued with minimum no of movies.
1211For example Assume customer John has been issued 5 movies, Ram has been issued 10 movies and
1212Kumar has been issued 2 movies. The name and id of Ram should be displayed for issuing maximum
1213movies and Kumar should be displayed for issuing minimum movies. Consider only the customers who
1214have been issued with atleast 1 movie Customer(s) who has/have been issued the maximum number of
1215movies must be displayed first followed by the customer(s) who has/have been issued with the
1216minimum number of movies. In case of multiple customers who have been displayed with the maximum
1217or minimum number of movies, display the records sorted in ascending order based on customer name.
1218(select cid.customer_id , customer_name FROM customer_master cm INNER JOIN
1219customer_issue_details cid
1220ON cm.customer_id=cid.customer_id
1221group by customer_id , customer_name
1222having count(movie_id)>=ALL(select count(movie_id)
1223FROM customer_issue_details
1224group by customer_id) order by customer_name)
1225UNION ALL
1226(select cid.customer_id , customer_name FROM customer_master cm INNER JOIN
1227customer_issue_details cid
1228ON cm.customer_id=cid.customer_id
1229group by customer_id , customer_name
1230having count(movie_id)<=ALL(select count(movie_id)
1231FROM customer_issue_details
1232group by customer_id) order by customer_name) ;
1233Please follow instructions given below.
1234Write a query to display the customer id , customer name and number of times movies have been
1235issued from Comedy category. Display only for customers who has taken more than once.
1236Hint: Use NO_OF_TIMES as alias name
1237Display the records in ascending order based on customer name.
1238SELECT cid.customer_id,customer_name,count(cid.movie_id) NO_OF_TIMES FROM customer_master
1239cm INNER JOIN customer_issue_details cid ON cm.customer_id=cid.customer_id INNER JOIN
1240movies_master mm
1241ON cid.movie_id=mm.movie_id WHERE movie_category='Comedy' GROUP BY customer_name HAVING
1242count(cid.customer_id)>1 order by customer_name;
1243Please follow instructions given below.
1244Write a query to display customer id and total rent paid by the customers who are issued with the
1245videos. Need not display the customers who has not taken / issued with any videos. Hint: Alias Name for
1246total rent paid is TOTAL_COST. Display the records sorted in ascending order based on customer id
1247SELECT cid.customer_id, sum(rental_cost) TOTAL_COST FROM customer_issue_details cid INNER JOIN
1248movies_master mm ON cid.movie_id=mm.movie_id GROUP BY customer_id order by customer_id;