Adapt Capgemini SQL Previous Year Questions Answer - MCQ Village

Adapt Capgemini SQL Previous Year Questions Answer

Hello friends in this post we are going to discuss about Capgemini Adapt SQL Previous Question Paper with Solution. Which will helpful to you for practising the Questions for your prepration for exam.

BRL1-1Available Books

Q1. Display the books which are available for the rent

ANS – alter session set current schema = bookrental;
SET heading OFF

SELECT * FROM bookrental.book WHERE stock>0;

BRL1-2-Available Books with Expected Quantity

Q2.Display allthe books which have total quantity of more than 30

ANS – alter session set current schema = bookrental;
SET heading OFF

SELECT * FROM bookrental.book WHERE stock>30;

BRL1-3-Sort Users Based on Name

Q3.Display the users in the ascending order (alphabetically by their name)

ANS – alter session set current schema = bookrental;
SET heading OFF

SELECT * FROM user_details ORDER BY first_name;

BRL1-4-Users Based on City

Q4.Display the total number of users based on the city

ANS – alter session set current schema = bookrental;
SET heading OFF

SELECT city,count(*) FROM user_details GROUP BY city;

BRL2-1-Books based on Rental Amount

Q5.Fetch the books which have rental as less than 500 for a month

ANS – alter session set current schema = bookrental;
SET heading OFF

SELECT * FROM book WHERE (rent per_day*30)<500;

BRL2-2-Books based on Price and Quantity

Q6.Display the books which have a price more than $500 and the available quantity is 10

ANS – alter session set current schema = bookrental;
SET heading OFF

SELECT * FROM book WHERE price>500 AND available_quantity=10;

BRL2-3- Books based on Quantity range

Q7.Display thebooks which have more than 30 and less than 50 as total quantity

ANS – alter session set current schema = bookrental;
SET heading OFF

SELECT * FROM book WHERE stock between 30 AND 58;

BRL3-1-Books Rented on Given Date

Q8.Display the books which was rented out on 20/11/2020

ANS – SELECT * FROM book WHERE book id in (SELECT book id FROM book_rental WHERE to_char (rental start_date, DD-MM-YYYY )-20-11-2020);

BRL3-2-Current Month Users Details

Q9.List the users who has availed the books current month

ANS – SELECT * FROM book WHERE book id in (SELECT book id FROM book_rental WHERE to_char (rental_start_date, MON)=DEC);

BRL3-3-User Details of Book Rental

Q10.Display all the users who have rented at least 1book with details of book and rental return date.

ANS – alter session set current schema = bookrental;
SET heading OFF

SELECT first name, title, rental end_date FROM user_details a, book rental b, book c WHERE a.user id=b.user id(+) AND b.book id=c.book id;

BRL3-4-Maximum Book availed by the User

Q11.Fetch the first name of the user who has availed maximum number of books for rental during the current fiscal

ANS – alter session set current schema = bookrental;
SET heading OFF

SELECT first name
FROM user detail1s
WHERE user id-(SELECT *

FROM (SELECT uSer id
FROM book_rental GROUP BY user id
ORDER BY count(*) desc)
WHERE row num<=1);

BRL3-5-Books Availed by Most of the users

Q12.Fetch the book which was availed for rent by most users

ANS – alter session set current schema = bookrental;
SET heading OFF

select from * book where
book id (select from
select book id
from book rental
group by book id
order by count (book id) desc)
where rownum<=1);

BRL3-6-Users Rented Neural Networks Book

Q13.ldentify the users who have opted for book named ‘Neural Networks’

ANS – alter session set current schema = bookrental;
SET heading OFF

SELECT * FROM user _details
WHERE user id in(
SELECT a.user id

FROM book rental a,book b,user details c
WHERE b.book_id = a.book_id
AND c.user_id = a.user_id
AND b.title = Neural Networks);

BRL3-7-Book not returned by the user

Q14. Fetch the user information who has not returned the books after the rental period

ANS – alter session set current schema = bookrental;
SET heading OFF

SELECT * FROM user_details WHERE user_id in(SELECT user_id
FROM book_ rental WHERE isreturned = no);

BRL3-8-User and Rental information

Q15.Display the user’s first name, book title and the rental end date of
the book for the rental id/s which was rented on 25th of Nov 2020

ANS – alter session set current schema = bookrental;
SET heading OFF

SELECT ud.first name,bk.title,br.rental end date FROM user details ud,book bk, book_rental br WHERE ud.user id = br.user id AND br.book_id = bk.book_id AND to_char(br.rental start_date, ‘dd/mm/yy )-25/11/20’;

BRL3-9-All User’s and Rental information

Q16.Display all the users first name, book title, rental start date, rental
end date for all users with the rental details even they don’t avail
any books for rental and sort the output based on first name

ANS – alter session set current schema = bookrental;
SET heading OFF

SELECT ud. first name, bk.title, br.rental_start_date, br.ren
FROM user details ud, book bk, book rental br
WHERE ud.user_id = br.user_id(+)
AND br.book_id = bk.book_id(+)
ORDER BY ud. firstname;

BRL3-10-Maximum Rent for the Month

Q17.Display fist name, city, total rent amount of the month for the user whose rental amount is maximum for the month.

ANS – alter session set current schema = bookrental;
SET heading OFF

SELECT * FROM (SELECT first name, city,SUM(totalamount) FROM book rental br,user_details ud WHERE br.user_id= ud.user_id AND br.rental_start date like DEC% GROUP BY ud.first_name, ud.city
ORDER BY SUM( total_amount) desc) WHERE rownum=1;

BRL4-1- Month Maximum Books Rented

Q18.Display month and the number of books when the maximum number of books were rented

ANS – alter session set current schema = bookrental;
SET heading OFF

SELECT FROM
(SELECT TO_CHAR (RENTAL_START_DATE, Month)
AS MONTH, COUNT (book_id) AS TOTAL_BOOKs
FROM BOOK RENTAL
GROUP BY TO CHAR (RENTAL_START_DATE, Month)
ORDER BY TOTAL_BOOKS DESC)

BRL4-2-Book Rented for Maximum Duration

Q19.Fetch the book’s title which was on rent for maximum duration

ANS – alter session set current schema = bookrental;
SET heading OFF

SELECT title FROM book WHERE book id in(SELECT book_id FROM book_rental WHERE (months between (nental start_date, rental_end date) = (SELECT max(months between(rental_start date, rental end_date)) FROM book nental)));

BRL4-3-Book availed by most of the customers

Q20.Fetch the book’s title which was availed for rent by most of the users.

ANS – alter session set current schema = bookrental;
SET heading OFF

SELECT title FROM book WHERE book id in(SELECT book_id FROM book_rental HAVING count(*) = (SELECT max(count(*)) FROM book nental
GRoUP BY book_id) GROUP BY book_ id);

BRL4-5-Top 2 Users by Revenue

Q21.Identify the top 2 preferred users in descending order of max revenue generated.

ANS – alter session set current schema = bookrental;
SET heading OFF

select*from (select ud.first_name
from user_details ud,book rental br

where ud. user id br.user id
group by ud.first name
order by sum(total_amount) dese)
where rownums;

BRL5-1-Rental Details of a Book

Q22.For a given book named as Machine Learning,display allthe rental details

ANS – alter session set current schema = bookrental;
SET heading OFF

SELECT br.
FROM book b,book rental br
WHERE b.book idbr.book id AND b.title = Machine Learning;

BRL5-2-Rental Book by City

Q23.Display the books title which were taken for rent only in “Bangalore

ANS – alter session set current schema = bookrental;
SET heading OFF

SELECT title FROM (SELECT title,b.book id

FROM book b,book rental br,user_detai1s ud
WHERE br.book_id b.book id
AND br.user_id -ud.user id
AND ud.city-‘Bangalore)
WHERE book id NOT 1n
(SELECT book id FROM book rental
WHERE user id in
(SELECT user id FROM user details
WHERE city Bangalore ))
AND rownum-1;

BRL5-3-Book Not Rented by Customer

Q24.Display the books title which was not at all rented out

ANS – alter session set current schema = bookrental;
SET heading OFF

SELECT title
FROM book
WHERE book id NOT in(SELECT book id
FROM book_ rental);

BRL5-4-Rental Details of Chandrav

Display the Rental details of a user “Chandrav”

ANS – alter session set current schema = bookrental;
SET heading OFF

SELECT br.
FROM book rental br, user details ud
WHERE br.user id ud.userid AND ud. first name Chandrav

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: