Sql solutions

There are more available on https://play.google.com/store/apps/details?id=com.master.askmastermaterial

Student database :

1. Write a query which will display the ID, name,

Tamil mark, Hindi mark, science mark, social mark, maths

mark, total mark, average mark and grade of all students. Display the records sorted in ascending order based on ID

SELECT A.STUDENT_ID,A.STUDENT_NAME,B.TAMIL_MARK,B.HINDI_MARK,B.SC IENCE_MARK,B.SOCIAL_MARK,B.MATHS_MARK,C.TOTAL_MARK,C.AVERAGE_MARK,C.GRADE FROM student_personal_info a join student_mark_info b on a.student_id=b.student_id join student_grade_info c on a.student_id=c.student_id order by a.student_id;

2. Write a query which will display Id, name, date of birth, guardian name, address, contact number, mail id, gender, standard, transport and net fees of all the students and ordered by ID in ascending order.

select a.student_id,a.student_name, a.date_of_birth,a.guardian_name,a.address,a.contact_no,a.mail_id,a.gender,b.standard, b.transport,b.net_fees from student_personal_info a join student_official_info b on a.student_id=b.student_id order by a.student_id;

3. WAQ which will display the total transport fees of

all the students of standard III and who have enrolled for transport.

Hint: Transport=’Y’ refers to that the students have

 

enrolled for transport.

For example: if there are 5 students from standard III each paying 100 as transport fee then query should return the 500. And use “TOTALTRANSPORTFEESPAID” as alias

select sum(b.transportfee)as TOTALTRANSPORTFEESPAID from student_official_info a join student_fees_info b on a.standard=b.standard where a.standard='III' and a.transport='Y';

4. WAQ to display the ID of the students who are eligible for the scholarship and have not availed for transport.

Hint: The criteria for Scholarship is student’s average

mark should be above 70 and transport flag should be ‘N’.

Display the records sorted by ID in ascending order.

Select A.STUDENT_ID from student_personal_info a join student_official_info b on a.student_id=b.student_id join student_grade_info c on a.student_id=c.student_id where b.transport='N' and c.average_mark>70 order by

a.student_id;

5. WAQ which displays the ID, name and net fees of all students who are paying the maximum net fees

For example, assume there are 10 student records and the maximum net fee paid is 1000 rupees, query should display all the students who are paying 1000 rupees. Display the records sorted in ascending order based on ID

select a.student_id,a.student_name,b.net_fees from student_personal_info a join student_official_info b on a.student_id=b.student_id where b.net_fees=( select max(net_fees) from student_official_info) order by a.student_id;

select net_fees from student_official_info order by net_fees desc limit 1;

6. WAQ which will display the fee status and the respective standard as mentioned below. If Standard Fees is 26000 display ‘AVERAGE’, if Standard Fees id 20000

display ‘LOW’, if Standard Fees is 18000 display ‘VERY LOW’ otherwise display ‘INVALID’ under the column name “FEESTATUS”. The fee status to be displayed for each

standard

Hint: USE “FEESTATUS” as alias for the standard fess

status.

Example: The fess status to be displayed for each standard such as standard I as ‘VERY LOW’, Standard II as ‘LOW’ and Standard III as ‘Average” respectively. The record to be

displayed as follows:

• STANDARD

FEESTATUS

• I VERY LOW

• II LOW

• III AVERAGE

• Strictly use the upper case for displaying the

“FEESTATUS”.

• display the records sorted in ascending order

based on STANDARD.

select standard , case true

when stdfees >= 26000 then 'AVERAGE' when stdfees >= 20000 then 'LOW' when stdfees >= 18000 then 'VERY LOW'

 

else 'INVALID'

end as "FEESTATUS" from student_fees_info order by standard ;

7. WAQ to display total number of students in each

standard with ‘Numberofstudents’ as alias name and the

respective standard.

HINT: Example, assume there are 10 records in standard ‘I’

and 20 in standard ‘II’ then query should display

10 I

20 II

Display the records sorted in ascending order on standard.

select count(student_id) as numberofstudents , standard from student_official_info group by standard order by

standard ;

8. WAQ which display the id, name and date of birth of the Male ‘M’ students. Display   the records sorted in ascending order based on ID.

select student_id , student_name , date_of_birth from student_personal_info where gender = 'M' order by student_id ;

9. WAQ which displays the total amount of net fees paid by the students. Example, if there are 3 students paying 100 rupees each then query should return the value 300 as total fees paid by the students. USE ‘TOTALFEESPAID’ as alias.

SELECT SUM(NET_FEES) AS TOTALFEESPAID FROM STUDENT_OFFICIAL_INFO;

 

10. WAQ which displays the total number of students as

’STUDENT_COUNT’ (alias) who are using transport. Transport =’Y’ refers to that student have enrolled for

transport.

SELECT COUNT(STUDENT_ID) AS STUDENT_COUNT FROM STUDENT_OFFICIAL_INFO WHERE TRANSPORT='Y';

11. WAQ which will display the student id and total fees of all students. If transport is availed, then the

standard fees and the transport fees else display only the standard fees under the total fees column with the alias name ‘TOTAL_FEES’. Round the value to the closest whole number. Display the records sorted in ascending order based on ID.

SELECT A.STUDENT_ID, CASE TRUE

WHEN A.TRANSPORT='Y' THEN ROUND( B.STDFEES

+B.TRANSPORTFEE,0)

ELSE ROUND( B.STDFEES,0)

END AS TOTAL_FEES FROM STUDENT_OFFICIAL_INFO A JOIN STUDENT_FEES_INFO B ON A.STANDARD=B.STANDARD ORDER BY

A.STUDENT_ID;

12. WAQ which will display the net fees in rupees, $, EURO AND POUND format for all the student records.

Hint: Values stored in the table are in rupees format.

a) Rs.53.47=1 DOLLAR

Example:100 rupees is equivalent to 100/53.47 DOLLARS, round it to the nearest whole number.

 

b) Rs.69.68=1 EURO

Example:100 rupees is equivalent to 100/69.98 Euros, round it to the nearest whole number.

c) Rs.86.59=1 POUND

Example: 100 rupees is equivalent to 100/86.59 pounds, round it to the nearest whole number.

uses below aliases for the column names:

"NET_FEES_RS", "NET_FEES_DOLLAR", "NET_FEES_EURO",

"NET_FEES_POUND".

27200 509 389 314 , Display the records sorted in ascending order based on ID.

SELECT STUDENT_ID,NET_FEES AS "NET_FEES_RS" , ROUND(NET_FEES/53.47,0) AS "NET_FEES_DOLLAR", ROUND(NET_FEES/69.98,0) AS "NET_FEES_EURO" , ROUND(NET_FEES/86.59,0) AS "NET_FEES_POUND" FROM STUDENT_OFFICIAL_INFO

ORDER BY STUDENT_ID;

13. WAQ which will display the student id, student name, net fees, grade of the students who have availed for transport and who have failed.

Hint: The grade id 'F' for those students who failed

Display the records sorted in ascending order based on ID

SELECT A.STUDENT_ID,A.STUDENT_NAME, B.NET_FEES,C.GRADE FROM STUDENT_PERSONAL_INFO A JOIN STUDENT_OFFICIAL_INFO B ON A.STUDENT_ID=B.STUDENT_ID

JOIN STUDENT_GRADE_INFO C ON A.STUDENT_ID=C.STUDENT_ID WHERE B.TRANSPORT='Y' AND C.GRADE='F'

ORDER BY A.STUDENT_ID;

14. WAQ which will display the student_id, standard, transport, net fees, Tamil mark, hindi mark, social mark, maths mark, total mark, average mark, grade and ratings and sort by student_id in descending order.

Hint: DISPLAY "GOLD" as rating for students whose grade is "B", "SILVER" for "C" and "NA" for "F" grades.

 

Use 'RATING" as alias for rating field. Strictly use the upper case for displaying rating.

SELECT A.STUDENT_ID, A.STANDARD, A.TRANSPORT A.NETFEES,B.TAMIL_MAR, B.HINDI_MARK,B.SOCIAL_MARK B.MATH_MARK,C.TOTAL_MARK, C.AVERAGE_MARK,C.GRADE, CASE

TRUE

WHEN C.GRADE='B' THEN 'GOLD' WHEN C.GRADE='C' THEN 'SILVER' WHEN C.GRADE='F' THEN 'NA' END AS "RATING"

FROM STUDENT_OFFICIAL_INFO A JOIN STUDENT_MARK_INFO B ON A.STUDENT_ID=B.STUDENT_ID

JOIN STUDENT_GRADE_INFO C ON A.STUDENT_ID=C.STUDENT_ID ORDER BY A.STUDENT_ID DESC;

15. WAQ which will display the student id, student name, total mark, average mark and grade of the students who has second highest total marks across the standards.

SELECT * FROM (SELECT TOTAL_MARK FROM STUDENT_GRADE_INFO ORDER BY TOTAL_MARK DESC LIMIT 2) ORDER BY TOTAL_MARK

LIMIT 1;

SELECT A.STUDENT_ID,A.STUDENT_NAME, B.TOTAL_MARK, B.AVERAGE_MARK,B.GRADE

FROM STUDENT_PERSONAL_INFO A JOIN STUDENT_GRADE_INFO B ON A.STUDENT_ID=B.STUDENT_ID

WHERE B.TOTAL_MARK=( SELECT MAX(TOTAL_MARK) FROM STUDENT_GRADE_INFO

WHERE TOTAL_MARK< (SELECT MAX(TOTAL_MARK) FROM STUDENT_GRADE_INFO));

 

16. WAQ which will display student id, student name and

display the output in the below format StudentName_StudentID

Use “STUDENT_INFO” as alias for the string displayed

above. Care to be taken when creating the String, the exact format needs to be followed.

Example: JOHN_S-123.

Display the records sorted in ascending order based on ID

SELECT concat( concat( student_name, '_' ), student_id)

AS “STUDENT_INFO”

FROM student_personal_info ORDER BY student_id;

17. WAQ which will display the student id, student name, date of birth, contact no, standard, net fees, Tamil mark, Hindi mark, science mark, social mark, maths, total mark, average mark and grade of all the students and mark, social mark, maths mark, total mark, average mark and grade of all the students and sort by student id. Display

the contact number in the below mentioned format

Format for contact number is: “+91-3digits-3digits-

4digits”

Example: +91-924-234-2312: Use “CONTACT_ISD” as alias name

SELECT a.student_id, a.student_name, a.date_of_birth, a.contact_no,

concat(concat(concat(concat(concat('+91-', substr

(a.contact_no, 1, 3)), '-'), substr(a.contact_no, 4, 3)), '-' ), substr(a.contact_no, 7 ) )

AS “CONTACT_ISD”, b.standard, b.transport,

b.net_fees, c.tamil_mar, c.hindi_mark, c.science_mark, c.social_mark, c.maths_mark,

 

d.total_mark, d.average_mark, d.grade

FROM student_personal_info a JOIN student_official_info b ON a.student_id = b.student_id

JOIN student_mark_info c

ON a.student_id=c.student_id JOIN student_grade_info d ON a.student_id=d.student_id ORDER BY A.STUDENT_ID;

18. WAQ which will display the student id, date of birth, month of birth, student name, guardian name, contact no, address, average marks and grade of the students whose average mark is between 60 and 85 and sort by student name

in ascending order.

Example: assume Tim's date of birth is "2003-09-12" then

month of birth is “SEPTEMBER".

Strictly use the upper case for displaying the month of birth.

SELECT a.student_id,a.date_of_birth, upper(monthname(a.date_of_birth)),a.student_name, a.guardian_name, a.contact_no, a.address,

b.average_mark, b.grade

FROM student_personal_info a JOIN student_grade_info b ON a.student_id=b.student_id

ORDER BY a.student_name;

19. WAQ which will display fees difference of the standard fees between Standard III and standard II.

Hint: use 'STUDENT_FEES_INFO' table use 'FeeDifference' as alias for the fees.

SELECT A.stdfees - B.stdfees AS "FeeDifference" FROM

(SELECT stdfees FROM student_fees_info WHERE standard='III') A,

 

(SELECT stdfees FROM student_fees_info WHERE standard='II' ) B

21. WAQ which will display the ID and name of the students who had failed in the exams.

Hint: The student who failed will get 'F' grade.

Display the records sorted in ascending order based on ID.

SELECT a.student_id, a.student_name FROM student_personal_info a JOIN student_grade_info b ON a.student_id=b.student_id

WHERE b.grade='F' ORDER BY a.student_id;

22. WAQ to display the student's name whose name starts with j. Display the records in ascending order based on student's name.

SELECT student_name FROM student_personal_info WHERE student_name LIKE 'J%'

ORDER BY student_name;

23. WAQ to display the ID and name of the Students who has got the grade 'C'. Display the records in ascending order based on ID.

SELECT a.student_id,a.student_name

FROM student_personal_info a JOIN student_grade_info b ON a.student_id=b.student_id

WHERE b.grade='C' ORDER BY a.student_id;

SELECT student_id, student_name FROM student_personal_info

WHERE student_id IN (SELECT student_id FROM student_grade_info WHERE grade='C');

 

24. WAQ which will display the list of ID and name of the

students who are using transport.

Hint: Transport='Y' refers to that the student have

enrolled for transport.

Display the records sorted in Ascending order based on ID.

SELECT a.student_id, a.student_name

FROM student_personal_info a JOIN student_official_info b ON a.student_id=b.student_id

WHERE b.transport='Y' ORDER BY a.student_id;

select student_id , student_name from student_personal_info where student_id in (select student_id from student_official_info where transport='Y')

order by student_id;

Video Library

1. Please follow instructions given below.

Write a query to display movie names and number of times that movie is issued to customers. Incase movies are never issued to customers display number of times as 0. Display the details in sorted order based on number of times (in descending order) and then by movie name (in ascending order).

The Alias name for the number of movies issued is ISSUE_COUNT.

SELECT mm.movie_name, count(cid.issue_id)

FROM movies_master mm JOIN customer_issue_details cid ON mm.movie_id = cid.movie_id

GROUP BY mm.movie_name;

2. Please follow instructions given below.

Write a query to display id,name,age,contact no of customers whose age is greater than 25 and and who have registered in the year 2012. Display contact no in the below format +91- XXX-XXX-XXXX example +91-987-678-3434 and use the alias name as "CONTACT_ISD". If the

 

contact no is null then display as 'N/A' Sort all the records in ascending order based on age and then by name.

SELECT customer_id, customer_name, age, IFNULL(

concat(concat( concat ( concat( concat ('+91', substr(contact_no,1,3) ), '-' ), substr(contact_no,4,3) ),'-' ), substr(contact_no,7)), 'N/A' ) AS CONTACT_ISD FROM customer

WHERE age>25 and year(date_of_registration)='2012' ORDER BY age, customer_name;

3. Please follow instructions given below.

Write a query to display the movie category and number of movies in that category. Display records based on number of movies from higher to lower order and then by movie category in ascending order.

Hint: Use NO_OF_MOVIES as alias name for number of movies.

SELECT movie_category, count(movie_id) as NO_OF_MOVIES from movies_master order by count(movie_id) desc, movie_category;

4. Please follow instructions given below.

Write a query to display the number of customers having card with description “Gold card”.

<br/>Hint: Use CUSTOMER_COUNT as alias name for number of customers

select count(a.customer_id) as customer_count

from customer_master a join customer_card_details b on a.customer_id=b.customer_id

join library_card_master c on b.card_id=c.card_id ORDER BY age, customer_name;

4. Please follow instructions given below.

Write a query to display the customer id, customer name, year of registration,library card id, card issue date of all the customers who hold library card. Display the records sorted by customer name in descending order.

Use REGISTERED_YEAR as alias name for year of registration.

SELECT a.customer_id,a.customer_name,year(a.date_of_registration) as REGISTERED_YEAR,b.card_id,b.issue_date

FROM customer_master a JOIN customer_card_details b ON a.customer_id=b.customer_id

ORDER BY a.customer_name DESC;

 

5. Please follow instructions given below.

Write a query to display issue id, customer id, customer name for the customers who have paid fine and whose name starts with 'R'. Fine is calculated based on return date and actual date of return. If the date of actual return is after date of return then fine need to be paid by the customer.

Display the records sorted in ascending order based on customer name.

SELECT CID.issue_id,CID.customer_id,CM.customer_name FROM customer_issue_details CID JOIN customer_master CM ON CID.customer_id=CM.customer_id

WHERE CM.customer_name LIKE 'R%'AND CID.actual_date_of_return>CID.return_date ORDER BY CM.customer_name;

6. Please follow instructions given below.

Write a query to display customer id, customer name, card id, card description and card amount in dollars of customers who have taken movie on the same day the library card is registered.

For Example Assume John registered a library card on 12th Jan 2013 and he took a movie on 12th Jan 2013 then display his details.

AMOUNT_DOLLAR = amount/52.42 and round it to zero decimal places and display as

$Amount. Example Assume 500 is the amount then dollar value will be $10. Hint: Use AMOUNT_DOLLAR as alias name for amount in dollar.

Display the records in ascending order based on customer name.

SELECT a.customer_id,a.customer_name,b.card_id,c.description,ROUND(c.amount/52.42 ,0) as AMOUNT_DOLLAR

FROM customer_master a join customer_card_details b on a.customer_id=b.customer_id join library_card_master c on b.card_id=c.card_id

join customer_issue_details d on a.customer_id=d.customer_id where a.date_of_registration=d.issue_date

ORDER BY a.customer_name;

7. Please follow instructions given below.

Write a query to display the customer id, customer name,contact number and address of customers who have taken movies from library without library card and whose address ends with 'Nagar'.

Display customer name in upper case. Hint: Use CUSTOMER_NAME as alias name for customer name. Display the details sorted in ascending order based on customer name. select customer_id,upper(customer_name) as

CUSTOMER_NAME ,contact_no,contact_address from customer_master where customer_id not in(select customer_id from customer_card_details) and contact_address like '%Nagar' order by customer_name;

 

8. Please follow instructions given below.

Write a query to display the movie id, movie name,release year,director name of movies acted by the leadactor1 who acted maximum number of movies .Display the records sorted in ascending order based on movie name.

select movie_id, movie_name , release_date, director from movies_master

WHERE lead_actor_name1 IN ( SELECT lead_actor_name1 FROM movies_master GROUP BY lead_actor_name1 HAVING count(movie_id) = (select count(movie_id) from movies_master group by lead_actor_name1 order by count(movie_id) desc limit 1 ) ) order by movie_name;

9. Please follow instructions given below.

Write a query to display the customer name and number of movies issued to that customer sorted by customer name in ascending order. If a customer has not been issued with any movie then display 0. Hint: Use MOVIE_COUNT as alias name for number of movies issued.

select a.customer_name,count(b.movie_id)as MOVIE_COUNT FROM customer_master a left join customer_issue_details b on a.customer_id=b.customer_id group by a.customer_name order by a.customer_name

10. Please follow instructions given below.

Write a query to display serial number,issue id, customer id, customer name, movie id and movie name of all the videos that are issued and display in ascending order based on serial number.

Serial number can be generated from the issue id , that is last two characters of issue id is the serial number.

For Example Assume the issue id is I00005 then the serial number is 05 Hint: Alias name for serial number is 'SERIAL_NO'

select substr(a.issue_id,length(a.issue_id)-1) SERIAL_NO, a.customer_id,c.customer_name, a.movie_id,b.movie_name

from custome_issue_details a join movies_master b

on a.movie_id=b.movie_id join customer_master c

on c.customer_id=a.customer_id order by SERIAL_NO ;

 

11. Please follow instructions given below.

Write a query to display the issue id,issue date, customer id, customer name and contact number for videos that are issued in the year 2013.Display the records in decending order based on issue date of the video.

SELECT cid.issue_id,cid.issue_date,cid.customer_id, cm.customer_name, cm.contact_no

FROM customer_issue_details cid JOIN customer_master cm ON cid.customer_id=cm.customer_id

WHERE year(issue_date)='2013' ORDER BY issue_date;

12. Please follow instructions given below.

Write a query to display movie id ,movie name and actor names of movies which are not issued to any customers. <br> Actors Name to be displayed in the below format.LEAD_ACTOR_ONE space ambersant space LEAD_ACTOR_TWO.

Example: Assume lead actor one's name is "Jack Tomson" and Lead actor two's name is "Maria" then Actors name will be "Jack Tomsom & Maria"Hint:Use ACTORS as alias name for actors name. <br> Display the records in ascending order based on movie name.

select movie_id,movie_name,concat(concat(lead_actor_name1,' & '),lead_actor_name2) as ACTORS from movies_master where movie_id not in (select movie_id from customer_issue_details) order by movie_name;

13. Please follow instructions given below.

Write a query to display the director's name, movie name and lead_actor_name1 of all the movies directed by the director who directed more than one movie. Display the directors name in capital letters. Use DIRECTOR_NAME as alias name for director name column Display the records sorted in ascending order based on director_name and then by movie_name in descending order.

select upper(director) as DIRECTOR_NAME ,movie_name,lead_actor_name1 from movies_master where director in(select director from movies_master group by director having count(movie_id)>1)

order by director,movie_name desc;

14. Please follow instructions given below.

Write a query to display number of customers who have registered in the library in the year 2012 and who have given/provided contact number. <br> Hint:Use NO_OF_CUSTOMERS as alias name for number of customers.

select count(customer_id) as NO_OF_CUSTOMERS from customer_master where year(date_of_registration)='2012' and contact_no is not null;

 

15. Please follow instructions given below.

Write a query to display the customer's name, contact number,library card id and library card description of all the customers irrespective of customers holding a library card. If customer contact number is not available then display his address. Display the records sorted in ascending order based on customer name. Hint: Use CONTACT_DETAILS as alias name for customer contact.

select a.customer_name,coalesce(contact_no,contact_add)as CONTACT_DETAILS,b.card_id,c.description from customer_master a left join customer_card_details b on a.customer_id=b.customer_id left join library_card_master c on b.card_id=c.card_id order by a.customer_name;

16. Please follow instructions given below.

Write a query to display the customer id, customer name and number of times the same movie is issued to the same customers who have taken same movie more than once. Display the records sorted by customer name in decending order For Example: Assume customer John has taken Titanic three times and customer Ram has taken Die hard only once then display the details of john. Hint: Use NO_OF_TIMES as alias name for number of times

select a.customer_id, b.customer_name, count(a.issue_id) as NO_OF_TIMES from customer_issue_details a join customer_master b on a.customer_id=b.customer_id count(movie_id)>1 group by a.customer_id, b.customer_name

order by count(a.issue_id)

SELECT A.customer_id, B.customer_name,

--A.movie_id, C.movie_name, count(A.issue_id) AS NO_OF_TIMES FROM customer_issue_details A JOIN customer_master B

ON A.customer_id = B.customer_id

--JOIN movies_master C ON A.movie_id = C.movie_id GROUP BY A.customer_id, A.movie_id

HAVING count(A.issue_id) > 1 ORDER BY B.customer_name DESC;

17. Please follow instructions given below.

Write a query to display customer id, customer name,contact number, movie category and number of movies issued to each customer based on movie category who has been issued with more than one movie in that category. Example: Display contact number as "+91-876- 456-2345" format.&nbsp;

Hint:Use NO_OF_MOVIES as alias name for number of movies column.

 

Hint:Use CONTACT_ISD as alias name for contact number.

Display the records sorted in ascending order based on customer name and then by movie category.

SELECT CM.customer_id, CM.customer_name, concat( concat(

concat( concat(concat( '+91', substr(CM.contact_no,1,3)), '-'), substr(CM.contact_no, 4, 3) ),

'-'),substr(CM_contact_no,7)

) AS CONTACT_ISD ,

MM.movie_category , count(cid.issue_id) AS NO_OF_MOVIES from customer_master CM join customer_issue_details cid on CM.customer_id=CID.customer_id

join movies_master MM on CID.movie_id=MM.movie_id group by CM.customer_id,MM.movie_category

ORDER BY CM.customer_name, mm.movie_category;

18. Please follow instructions given below.

Write a query to display customer id and customer name of customers who has been issued with maximum number of movies and customer who has been issued with minimum no of movies.

For example Assume customer John has been issued 5 movies, Ram has been issued 10 movies and Kumar has been issued 2 movies. The name and id of Ram should be displayed for issuing maximum movies and Kumar should be displayed for issuing minimum movies. Consider only the customers who have been issued with atleast 1 movie Customer(s) who has/have been issued the maximum number of movies must be displayed first followed by the customer(s) who has/have been issued with the minimum number of movies. In case of multiple customers who have been displayed with the maximum or minimum number of movies, display the records sorted in ascending order based on customer name.

Max - c00003

Min - c00010 c00011

SELECT Cm.customer_id,cm.customer_name

from customer_master cm join customer_issue_details cid on cm.customer_id=cid.customer_id

group by cm.customer_id, cm.customer_name having count(cid.issue_id)=(select count(issue_id) from customer_issue_details

group by customer_id

order by count(issue_id) DESC LIMIT 1)

 

union

SELECT Cm.customer_id,cm.customer_name

from customer_master cm join customer_issue_details cid on cm.customer_id=cid.customer_id

group by cm.customer_id, cm.customer_name having count(cid.issue_id)=(select count(issue_id) from customer_issue_details

group by customer_id

order by count(issue_id) LIMIT 1) order by 2;

19. Please follow instructions given below.

Write a query to display the customer id , customer name and number of times movies have been issued from Comedy category. Display only for customers who has taken more than once.

Hint: Use NO_OF_TIMES as alias name

Display the records in ascending order based on customer name.

SELECT CM.customer_id,CM.customer_name,COUNT(CID.issue_id)

AS NO_OF_TIMES FROM customer_master CM JOIN

customer_issue_details CID ON CM.customer_id = CID.customer_id JOIN movies_master MM ON CID.movie_id

= MM.movie_id WHERE MM.movie_category='COMEDY' GROUP BY CM.customer_id,CM.customer_name

HAVING COUNT(CID.issue_id)>1 ORDER BY CM.customer_name;

20. Please follow instructions given below.

Write a query to display customer id and total rent paid by the customers who are issued with the videos. Need not display the customers who has not taken / issued with any videos. Hint: Alias Name for total rent paid is TOTAL_COST. Display the records sorted in ascending order based on customer id

SELECT a.customer_id,sum(b.rental_cost) as TOTAL_COST FROM customer_issue_details a join movies_master b on a.movie_id=b.movie_id group by a.customer_id order by a.customer_id;

Mobile

Problem # 1: WAQ to Display the mobile details such as IMENO, Model Name produced by the manufacturer "Nokia".

 

select IME_no,model_name from mobile_master

where manufacturer='NOKIA';

Problem # 2: WAQ to display IMENO, Model Name,Manufacturer,Camerea Quality of mobiles whose camera quality is 5MP.

select m.ime_no, mm.model_name, mm.manufacturer, ms.camera_quality

from mobile_master mm join mobile_specification ms on mm.ime_no=ms.ime_no

where ms.camera_quality='5MP';

Problem # 3: WAQ to display Model Name,Quantity sold on the date 25-APR-12. select model_name,count(salesid)

from sales_info

where date(sales_date)='2012-04-20' group by model_name;

Problem # 4: WAQ to display distributor id ,mobile supply details such as mobile model name, quantity supplied in sorted order of distributor id.

select distributor_id, model_name, count(model_name) from mobile_master

group by distributor_id, model_name order by distributor_id;

Problem # 5: WAQ to display the IMENO,model name,manufacturer,price and discount of all mobiles regardless of whether the mobile is sold or not.

select mm.ime_no, mm.model_name, mm.manufacturer, mm.price, si.discount

from mobile_master mm left join sales_info si on mm.ime_no=si.ime_no;

Problem # 6: WAQ to display the distributor details such as distributor name,mobile number and email of the model 'Nokia 1100'.

select distributor_name,mobile,email from distributor where distributor_id in(select distributor_id from mobile_master where model_name='Nokia 1100');

Problem # 7: WAQ to display the Ime No and Model Name of mobiles which are not sold(Hint : use minus operator)

 

select ime_no,model_name from mobile_master

where ime_no not in(select ime_no from sales_info)and model_name not in(select model_name from sales_info);

Problem # 8: WAQ to display the Ime No and Model Name of mobiles which are sold(Hint : use intersect operator)

SELECT ime_no,model_name FROM sales_info;

Problem # 9: WAQ to display the ImeNO, Model Name,Manufacturer, Price and NewPrice of all mobiles.

(Hint : find new price as 10% of the price with column name "New Price")

SELECT ime_no,model_name,manufacturer,price,price*1.1 as "NEW PRICE" from mobile_master;

Problem # 10: WAQ to display mobile model, manufacturer and price for the mobiles having a price range from 8500 to 25300.

SELECT ime_no,model_name,manufacturer,price from mobile_master WHERE price BETWEEN 8500 AND 25300;

Problem # 1: WAQ to display the Model Name,Manufacturer, Price , Warranty , Internal memory, memory card capacity,gprs support,bluetooth,camera quality and OS for the mobile with IME NO "MC1000104" .

SELECT mm.model_name, mm.manufacturer, mm.price, mm.warranty_in_years, ms.internal_mem_in_mb, ms.memory_card_capacity_gb, ms.GPRS, ms.Bluetooth, ms.camera_quality, ms.OS

FROM mobile_master mm JOIN mobile_specification ms ON mm.ime_no=ms.ime_no

WHERE mm.ime_no="MC1000104";

Problem # 2: WAQ to display IMENO, Model Name,Manufacturer,Price ,GPRS information,Memory card support of mobiles which has GPRS support with memory card capacity 16GB or above.

SELECT mm.IME_no, mm.model_name, mm.manufacturer, mm.price, ms.GPRS, ms.memory_card_capacity_gb

FROM mobile_master mm JOIN mobile_specification ms ON mm.ime_no=ms.ime_no

WHERE ms.GPRS="YES" AND ms.memory_card_capacity_gb >= 16;

 

Problem # 3: WAQ to display the customer name ,mobile purchase details such as IMENO,Model Name ,Purchase Date,Net amount paid in sorted order of customer name.

SELECT a.customer_name,b.IME_no,b.model_name,b.sales_date,b.Net_amount from customer_info a join sales_info b on a.customer_id=b.customer_id order by a.customer_name;

Problem # 4: WAQ to display the distributor details such as distributor id ,name

,address,contact no who has supplied the maximum number of mobiles.

select distributor_id, distributor_name, Address, Mobile from distributor Where distributor_id IN

( SELECT distributor_id FROM mobile_master GROUP BY distributor_id HAVING count(IME_NO) = (SELECT count(ime_no) FROM mobile_master GROUP BY distributor_id

ORDER BY count(ime_no) DESC LIMIT 1 ) );

Problem # 5: WAQ to display the IMENO,model name,manufacturer,price and discount of all mobiles regardless of whether the mobile is sold or not.

[Hint: If not sold, display discount as "Not Sold"]

SELECT a.IME_no,a.model_name,a.manufacturer,a.price,ifnull(b.discount,'not sold') from mobile_master a left join sales_info b on a.IME_no=b.IME_no

Problem # 6: WAQ to display the report containing the sales date and total sales amount of the dates between 20-APR-12 and 25-APR-12.

(Hint : total sales amount column should be displayed as "Total Sales Amount" )

SELECT sales_date, sum(Net_amount) AS "TOTAL SALES AMOUNT"

FROM sales_info WHERE sales_date BETWEEN '2012-04-20' AND '2012-04-25' GROUP BY sales_date;

Problem # 7: WAQ to display mobile imeno,model name,manufacturer and price of the mobiles which are having the longest battery life.

SELECT IME_no, model_name, manufacturer, price FROM mobile_master WHERE IME_no IN (

SELECT IME_no FROM mobile_specification WHERE Battery_Life_Hrs=( SELECT max(Battery_Life_Hrs) FROM mobile_specification));

 

Problem # 8: WAQ to display the ImeNO, Model Name,Manufacturer, Price of mobiles having the maximum price.

select ime_no,model_name,manufacturer,price from mobile_master where ime_no in(select ime_no from mobile_master where price=(select max(price) from mobile_master));

Problem # 9: WAQ to display the customer details such as Customer ID,Customer Name, Address, Total Purchase amount.

select a.customer_id,a.customer_name,a.address,sum(b.net_amount) as "total purchase amount" from customer_info a join sales_info b on a.customer_id=b.customer_id

group by a.customer_id,a.customer_name,a.address;

Problem # 10: WAQ to display the most costly mobile information such as mobile model, manufacturer and price manufactured by "Samsung".

select ime_no,model_name,manufacturer,price from mobile_master where ime_no in(select ime_no from mobile_master where price=(select max(price) from mobile_master WHERE manufacturer = 'Samsung'));

•Problem # 1: WAQ to display the customer details such as Customer ID,Customer Name,

Address and Total Purchase amount having the maximum purchase amount.

SELECT a.customer_id,a.customer_name,a.address,sum(b.net_amount) as "total purchase amount" from customer_info a join sales_info b on a.customer_id=b.customer_id

group by a.customer_id,a.customer_name,a.address ORDER BY sum(b.net_amount)DESC LIMIT 1;

Problem # 2: WAQ to determine whether the mobile with ime no "MC1000105" is been sold out or not and display the model name,sales status.(Hint: If sold display status as "Sold Out" with column name "Sales Status").

select mm.Model_Name, CASE TRUE WHEN si.salesId IS NULL THEN 'Not Sold' ELSE 'Sold Out'

END AS "Sales Status"

from mobile_master mm LEFT join sales_info si on mm.Ime_no=si.Ime_no where mm.IME_no = 'MC1000106';

 

Problem # 3: WAQ to display the mobile information such as ime no,model name,manufacturer ,distributor id ,distributor name and price

supplied by the distributor named 'AXA Ltd' .

SELECT a.Ime_no, a.Model_Name,a.Manufacturer,a.Distributor_id,b.Distributor_name,a.price from mobile_master a join distributor b on

a.distributor_id=b.distributor_id WHERE b.distributor_id= (

SELECT distributor_ID from distributor where Distributor_name='AXA Ltd');

Problem # 4: WAQ to display distributor details who supplies mobile with the following speficiations such as 3G Network, Android OS, 5 MP Camera.

SELECT distributor_id,distributor_name,address FROM distributor WHERE distributor_id=

(SELECT distributor_id FROM mobile_master WHERE Ime_no=

(SELECT IME_no FROM mobile_specification WHERE Network_3G='YES' AND OS LIKE 'ANDROID OS%' AND camera_quality='5MP'));

Problem # 5: WAQ to Display the maximum sold mobile model name and manufacturer . SELECT a.model_name, a.manufacturer

FROM mobile_master a JOIN sales_info b ON A.IME_no = B.IME_no

GROUP BY a.model_name, a.manufacturer ORDER BY COUNT(salesid) DESC LIMIT 1;

Payroll

#Problem # 1: Write a query to display Employee ID, Employee Name, Department ID and Department Name of all employees who has a department assigned.

SELECT a.empid, a.empname, a.deptid, b.deptname FROM employee_info a JOIN dept_info b

ON a.deptid = b.deptid;

 

#Problem # 2: Write a query to display the Employee ID, Employee Name, Basic Pay of all employees who are in employee category 'A'

SELECT a.empid, a.empname, b.basic FROM employee_info a JOIN salary_info b

ON A.employee_category = B.employee_category WHERE A.employee_category = 'A';

#Problem # 3: Write a query to display the Employee ID, Employee Name, Department ID and Department Name of all employees who has a department assigned and department location is Bangalore.

SELECT a.empid, a.empname, a.deptid, b.deptname FROM employee_info a JOIN dept_info b

ON a.deptid = b.deptid

WHERE b.location = 'Bangalore';

#Problem # 4: Write a query to display the employee ID and

employee name of the employees who have not been assigned a department yet.

SELECT empid, empname FROM employee_info WHERE deptid IS NULL;

#Problem # 5: Write a query to display the employee ID, employee name and joining date of the employees who joined before 2005.

SELECT empid, empname, joining_dt FROM employee_info

WHERE year(joining_dt) < '2005';

#Problem # 6: write a query to display employee name and date of joining for all employees. (Date should be displayed in the format 23/JANUARY/2012• with Alias JOINING_DATE• in select statement)

SELECT empname, date_format(joining_dt, '%d/%M/%Y') as "JOINING_DATE" FROM employee_info;

#Problem # 7: Write a query to display the employee ID, employee name and joining date of the employees who joined between Jan 1 2005 and Dec 31st 2010

 

SELECT empid, empname, joining_dt FROM employee_info

WHERE joining_dt BETWEEN '2005-01-01' AND '2010-12-31';

#Problem # 8: Write a query to display the employee ID, employee name and joining date of the employees who joined in MARCH.

SELECT empid, empname, joining_dt FROM employee_info

WHERE monthname(joining_dt) = 'March';

#Problem # 9: Write a query to display all employee names which begins with 'R'. SELECT empname

FROM employee_info WHERE empname LIKE 'R%';

#Problem # 10: Write a query to display the first five employees name in the employee table and the respective row number (use ROWNUM for identifying the first five records)

SELECT ROWNUM, first_name FROM employees WHERE rownum <= 5;

set @v=0;

SELECT @v:=@v+1, empname FROM employee_info WHERE @v < 5;

#Problem # 1: Write a query to display the EmployeeID, Employee Name, Net Pay of an employee whose ID is "E001" for the month of APRIL

SELECT e.EMPID, e.EMPNAME,ep.NETPAY FROM EMPLOYEE_INFO e JOIN EMP_PAYROLL ep ON e.EMPID=ep.EMPID

WHERE e.EMPID= 'E001' AND ep.MONTH='APR';

#Problem # 2: Write a query to display the department id and no of employees in each department sorted by department id.(Exclude department with null values).

SELECT DEPTID,COUNT(EMPID) FROM EMPLOYEE_INFO WHERE DEPTID IS NOT NULL GROUP BY DEPTID

ORDER BY DEPTID;

 

#Problem # 3: Write a query to display the EmployeeID, Employee Name and the total number of leaves each employee has taken with Total_Leaves• as alias.

SELECT*FROM EMP_LEAVE_INFO;

SELECT A.EMPID,A.EMPNAME,SUM(B.TOTAL_LEAVES)AS TOTAL_LEAVES FROM EMPLOYEE_INFO A JOIN EMP_LEAVE_INFO B ON A.EMPID=B.EMPID GROUP BY A.EMPID,A.EMPNAME;

#Problem # 4: Write a query to display the EmployeeID, Employee Name, DOB and Age in Years without decimals with alias name "Age".

Hint: Formula for age calculation is Age = current date- dob/12, round this to the nearest whole number.

SELECT EMPID, EMPNAME, ROUND(DATEDIFF(CURDATE(),DOB)/365,0) AS AGE FROM EMPLOYEE_INFO;

#Problem # 5: Write a query to display employee id, employee name of all employees who doesn't have LOP amount for the month of APR and year 2012.

SELECT A.EMPID, A.EMPNAME FROM EMPLOYEE_INFO A JOIN EMP_PAYROLL B ON A.EMPID=B.EMPID

WHERE B.MONTH='APR' AND B.YEAR=2012 AND B.LOPAMOUNT=0;

#Problem # 6: Write a query to display employee name, professional tax, netpay of employees with employee category 'A'

SELECT A.EMPNAME, B.PROFTAX, C.NETPAY FROM EMPLOYEE_INFO A

JOIN SALARY_INFO B ON A.EMPLOYEE_CATEGORY=B.EMPLOYEE_CATEGORY JOIN EMP_PAYROLL C ON A.EMPID=C.EMPID

WHERE A.EMPLOYEE_CATEGORY='A';

#Problem # 7: Write a query to display employee id, employee name, department id who are having netpay in the range 10000 - 20000

SELECT A.EMPID,A.EMPNAME,A.DEPTID FROM EMPLOYEE_INFO A JOIN EMP_PAYROLL B ON A.EMPID=B.EMPID WHERE B.NETPAY BETWEEN 10000 AND 20000;

#Problem # 8: Write a query to display employee names whose total deduction is more than 2000 for the month of APRIL.

SELECT EMPNAME FROM EMPLOYEE_INFO A JOIN EMP_PAYROLL B ON A.EMPID=B.EMPID WHERE B.TOTALDEDUCTION>2000 AND B.MONTH='APR';

 

#Problem # 9: Write a query to display employee id, employee name, department id, department name of all employees regardless of whether an employee is assigned a department or not.

SELECT A.EMPID,A.EMPNAME,A.DEPTID,B.DEPTNAME FROM EMPLOYEE_INFO A LEFT JOIN DEPT_INFO B ON A.DEPTID=B.DEPTID;

#Problem # 10: Write a query to display Employee ID, Employee Name, Department ID, Years of Experience and Employee Category of the employees who have availed leaves more than 10 days.

#Hint: Use the total_leaves column to check the leave condition for more than ten days.

SELECT A.EMPID,A.EMPNAME,A.DEPTID,A.YRS_OF_EXP, A.EMPLOYEE_CATEGORY

FROM EMPLOYEE_INFO A JOIN EMP_LEAVE_INFO B ON A.EMPID=B.EMPID

GROUP BY A.EMPID,A.EMPNAME,A.DEPTID,A.YRS_OF_EXP, A.EMPLOYEE_CATEGORY

HAVING SUM(B.TOTAL_LEAVES)>10;

#Problem # 1: Write a query to display employee id, employee name and remaining casual leaves (alias- RemainingLeaves) for the employee with employee id "E002".

Based on the total causal leaves available, subtract the number of causal leaves he has availed to get the remaining leaves.

Hint: CL as Causal leave.

EMPLOYEE_INFO table has Employee's leave Category. For example employee E001• belong to X• leave category.

EMP_LEAVE_INFO table has details of number of leaves the employees has availed.

For example, E001• has availed totally 8 days of causal leave. LEAVE_INFO table has the Leave Category and number of CL, EL and ML available for the category. For example, E001• has category X which has 18 days of total causal leave that he can avail. So, E001's remaining leave would be 10 days. Similarly calculate for E002.

STEP 1: TO GET THE ALLOWED CASUAL LEAVES

SELECT EI.EMPID,EI.EMPNAME,LI.CL FROM EMPLOYEE_INFO EI JOIN LEAVE_INFO LI ON EI.LEAVE_CATEGORY=LI.LEAVE_CATEGORY;

STEP 2: NO OF CASUAL LEAVES TAKEN BY EVERY EMPLOYEE

SELECT EI.EMPID,EI.EMPNAME,SUM(ELI.TOTAL_LEAVES) FROM EMPLOYEE_INFO EI JOIN EMP_LEAVE_INFO ELI ON EI.EMPID=ELI.EMPID

 

WHERE ELI.LEAVE_TYPE='CL' GROUP BY EI.EMPID,EI.EMPNAME;

STEP 3:

SELECT EI.EMPID,EI.EMPNAME,LI.CL-SUM(ELI.TOTAL_LEAVES) AS RemainingLeaves FROM EMPLOYEE_INFO EI JOIN LEAVE_INFO LI ON EI.LEAVE_CATEGORY = LI.LEAVE_CATEGORY JOIN EMP_LEAVE_INFO ELI ON ELI.EMPID=EI.EMPID

WHERE ELI.LEAVE_TYPE='CL' AND EI.EMPID='E002' GROUP BY EI.EMPNAME;

#Problem # 2: Write a query to display employee id, employee name and total number of leaves he can take (hint: with EligibleLeave as alias). This should be retrieved for all the employees. Sum all the EL, ML and EL leaves for the each employee's category to get the total leaves.

#Hint: EMPLOYEE_INFO table has Employee's leave Category. For example employee E001• belong to X• leave category.

LEAVE_INFO table has the Leave Category and number of CL, EL and ML available for them. For example, Employee E001 belongs to X category and he has 18 days of CL and 5 days of EL and 10 days of ML that he can avail. So, E001's eligible leave would be 33 days which is sum of all his leaves.

Similarly calculate for all employees.

SELECT A.EMPID,A.EMPNAME,B.CL+B.EL+B.ML AS ELIGIBLELEAVE FROM EMPLOYEE_INFO A JOIN LEAVE_INFO B ON A.LEAVE_CATEGORY=B.LEAVE_CATEGORY;

#Problem # 3: Write a Query to display employee id, employee name, department id, department name, net pay of all employees who have drawn the highest salary (net pay) in the month of APRIL 2012.

#Hint: For example if there are 10 employees where 3 employees have got a salary of 1000 which is the highest salary of the employee in the month of April all the three

records needs to be displayed.

SELECT A.EMPID,A.EMPNAME,A.DEPTID,B.DEPTNAME,C.NETPAY FROM EMPLOYEE_INFO A JOIN DEPT_INFO B ON A.DEPTID=B.DEPTID

JOIN EMP_PAYROLL C ON A.EMPID=C.EMPID

WHERE C.NETPAY=(SELECT MAX(NETPAY) FROM EMP_PAYROLL WHERE MONTH='APR');

#Problem # 4: Write a query to display employee id, employee name, basic pay and tax percentage for all employees. Use TaxPercentage• as alias. Display the Tax percentage for

 

all employees based on the following criteria: (If Basic Pay <= 4000 then tax percentage should be 10%, basic <= 5000 then 20%, basic<=6000 then 30% basic > 6000 then 40%).

SELECT A.EMPID,A.EMPNAME,B.BASIC, CASE TRUE

WHEN B.BASIC<=4000 THEN '10%' WHEN B.BASIC<=5000 THEN '20%' WHEN B.BASIC<=6000 THEN '30%' WHEN B.BASIC>6000 THEN '40%'

END AS TaxPercentage

FROM EMPLOYEE_INFO A JOIN SALARY_INFO B ON A.EMPLOYEE_CATEGORY

=B.EMPLOYEE_CATEGORY;

#Problem # 5: Write a query to display employee name,employee category and basic pay for all employees based on the following criteria: (Emp_cat 'A' - Basic Pay should be 6000, 'B'- 4000, 'C'- 3000, 'D'-2000)

#Hint: Use salary• as alias for the basic pay field.

For example, if there are 3 employees with one having category A and other having category D and other category C. Then the query should display the three employees

name, category and the salary will be 6000, 2000 and 1000 for the three employees.

SELECT EMPNAME,EMPLOYEE_CATEGORY, CASE TRUE

WHEN EMPLOYEE_CATEGORY='A' THEN 6000 WHEN EMPLOYEE_CATEGORY='B' THEN 4000 WHEN EMPLOYEE_CATEGORY='C' THEN 3000 WHEN EMPLOYEE_CATEGORY='D' THEN 2000 END AS SALARY

FROM EMPLOYEE_INFO ;