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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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 ofthe book for the rental id/s which was rented on 25th of Nov 2020

ANS – alter session set current schema = bookrental;

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, rentalend date for all users with the rental details even they don’t availany books for rental and sort the output based on first name

ANS – alter session set current schema = bookrental;

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;

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;

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;

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;

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;

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;

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;

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;

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;