MySQL Question Bank - 1

Q1. Consider the table Patient and write SQL Commands: i. Display the details of all patients who
were admitted in January.
ii. Count the total number of patients
from Delhi.
iii. Display the last 2 digits of the Patientid
of all patients from Surgery
Department.
Q2. Consider the table Fashion and Write Output: i. SELECT COUNT(Product) FROM
FASHION;

ii. SELECT SUM(Price*Qty) FROM FASHION

WHERE Product=”Night Cream”;

iii. SELECT LEFT(Product,4) FROM FASHION

WHERE Price>1500;

Q3. Consider the table Product and write SQL Commands: i. Display the first 3 characters of all
Itemnames.
ii. Display the names of all items whose
Stockday is “Monday”.
iii. Display the total price of all the
products.
iv. Display the maximum price.
v. Display the average price of all the
products by the company named
‘Logitech’.
Q4. Write SQL queries to perform following operations Predict Output:
i. To display the year from your Date of Admission which is ‘2023- i. SELECT SUBSTR(“CLIMATE CHANGE”,4,4);
05-15’. ii. SELECT UCASE(RIGHT(“Pollution”,3));
ii. To convert your email id ‘ABC@[Link]’ to lowercase. iii. SELECT LENGTH(“HAPPY”)+3;
iii. To remove leading spaces from a string ‘ my country ‘.
iv. To display current date.
v. To display the value of 106
Q5. Consider the table Schooldata and write SQL Commands i. Display the average Marks secured by
each Gender.
ii. Display the minimum Marks secured by
each students of Grade 10.
iii. Display the total number of students in
each Club where number of students are
more than 1.
iv. Display the maximum and minimum
marks secured by each gender.

Q6. Based on the SQL table CAR_SALES, write

suitable queries for the following:

i. Display fuel wise average sales in the

first quarter.
ii. Display segment wise highest sales in
the second quarter.
iii. Display the records in the descending
order of sales in the second quarter.

Based on SQL table CAR_SALES, predict output:

 i. SELECT LEFT(SEGMENT,2) FROM CAR_SALES WHERE FUEL=
"PETROL";
ii. SELECT (QT2-QT1)/2 "AVG SALE" FROM CAR_SALES WHERE
SEGMENT= "SUV";
iii. SELECT SUM(QT1) "TOT SALE" FROM CAR_SALES WHERE FUEL=
"DIESEL";
Q7. Based on the table BLOCKCHAIN, write SQL queries: i. Write a query to display the year of
oldest transaction.
ii. Write a query to display the month of
most recent transaction.
iii. Write a query to display all the
transactions done in the month of
May.
iv. Write a query to count total number of
transactions in the year 2022.

Q8. Write suitable SQL queries for the following:

i. To calculate the exponent for 3 raised to the power of 4.
ii. To display current date and time.
iii. To round off the value -34.4567 to 2 decimal place.
iv. To remove all the probable leading and trailing spaces from the column userid of the table named user.
v. To display the length of the string ‘FIFA World Cup’.
Q9. On the basis of the table named EXAM. Write SQL Queries: i. Insert a new record in the table having
following values:
[6,'Khushi','CS',85]
ii. To change the value “IP” to
“Informatics Practices” in subject
column.
iii. To remove the records of those
students whose marks are less than 30.
iv. To add a new column Grade of suitable
datatype.
v. To display records of “Informatics
Practices” subject.
Q10. Write MySQL statements for the following:
i. To create a database named FOOD.
ii. To create a table named Nutrients based on the following specification:

Q11. On the basis of table PURCHASE, write output: i. SELECT LENGTH(CNAME) FROM
PURCHASE WHERE QUANTITY>100;

ii. SELECT CNAME FROM PURCHASE

WHERE MONTH(DOP)=3;

iii. SELECT MOD (QUANTITY, DAY(DOP))

FROM PURCHASE WHERE CITY=
‘CHANDIGARH’;
Q12. On the basis of table STUDENT, write SQL
queries:

i. Display gender wise highest marks.

ii. Display city wise lowest marks.

iii. Display total number of male and

female students.

Q13. Write suitable SQL query for the following:

i. Display 7 characters extracted from 7th left character onwards from the string ‘INDIA SHINING’.
ii. Display the position of occurrence of string ‘COME’ in the string ‘WELCOME WORLD’.
iii. Round off the value 23.78 to one decimal place.
iv. Display the remainder of 100 divided by 9.
v. Remove all the expected leading and trailing spaces from a column userid of the table ‘USERS’.
Q14. On the basis of table CLOTH, write SQL queries: i. Write a query to display cloth names in
lower case.
ii. Write a query to display the lowest
price of the cloths.
iii. Write a query to count total number of
cloths purchased of medium size.
iv. Write a query to count year wise total
number of cloths purchased.

Q15. Consider the table HOTEL given below and write SQL commands : i. Display the details of all the Hotels
situated in London.
ii. Display the details of all 'Deluxe' rooms
with price more than 6000 in ascending
order of Price.
iii. Display the Hotel names that end with
''e''.
iv. Count different types of rooms
available in the Hotels.
v. Display the Hotel names in descending
order.
Q16. On the basis of the table F_INDIA, write
output:

i. SELECT COUNT (Distinct product)

FROM F_INDIA;

ii. SELECT Product, Price FROM F_INDIA

WHERE Product LIKE ‘%m’;

Q17. On the basis of the table SCHOOL, write SQL queries: i. Display the total number of students in
each House where number of students
are more than 2.
ii. Display the average Percentage of girls
and boys.
iii. Display the minimum Percentage
secured by the students of Class 10.

Q18. Write SQL commands for the following:

i. To display the name of the weekday for your date of birth.
ii. To convert e-mail-id ‘OFFICE@[Link]’ to lowercase.
iii. To count the number of characters in your name.
iv. To display the first name from the name ‘Manju Sharma’ i.e. to display ‘Manju’ From ‘Manju Sharma’
 v. To display the first character from the name ‘SANJEEV KUMAR’ i.e. to extract ‘S’.
Q19. On the basis of the table ITEM, write SQL queries: i. Display the first 3 characters of the
Itemname.
ii. Display the month name from the
Stockdate.
iii. Display the total price of the whole
stock.
iv. Display the average Price of all the
stocks.
v. Display all the Price round off up to 2
decimal places.
Q20. On the basis of the table EMP, write
output
i. select max(year(DOB)) from emp;
ii. select ENAME from emp where
month(DOJ)=11;
write SQL queries:
i. To display the name of eldest employee
and his/her date of birth.
ii. To display the name of those
employees whose joining month is
May.
Q21. Predict the output of the following queries::
i. select instr('exams@[Link]','.');
ii. select substr('exams@[Link]',7,4);
iii. select left('exams@[Link]',5);
Q22. On the basis of the table Hotel, write SQL queries: i. To fetch last 2 characters from the
user_id column.
ii. To display the values of name column
in lower case.
iii. To display 3 characters from 3rd place
from the column city.

Q23. On the basis of the table STOCK, write SQL queries: i. To display the records in decreasing
order of price.
ii. To display category and category wise
total quantities of products.
iii. To display the category and its average
price.
iv. To display category and category wise
highest price of the products.

Q24. On the basis of the table STUDENT, write output: i. select sum(MARKS) from student where
OPTIONAL= ‘IP’ and STREAM=
‘Commerce’;
ii. select max(MARKS)+min(MARKS) from
student where OPTIONAL= ‘CS’;
iii. select avg(MARKS) from student where
OPTIONAL= ‘IP’;
iv. select length(SNAME) from student
where MARKS is NULL;
v. Select count(*) from student;
vi. Select count(MARKS) from student;

Q25. On the basis of the table VEHICLES, write sql queries :

 i. Display the average price of each type
of vehicle having quantity more than
20.

ii. Count the type of vehicles

manufactured by each company.

iii. Display the total price of all the types of

vehicles

Q26. Write SQL queries for following:

i. To display the name of the month of the current date
ii. To remove spaces from the beginning and end of a string, “ Panorama “.
iii. To display the name of the day eg, Friday or Sunday from your date of birth, dob
iv. To display the starting position of surname “Bedi” from whole name “Suman Bedi”
v. To compute the remainder of division between two numbers, 67 and 3
Q27. On the basis of the table SALESMAN, write SQL queries: i. Display salesman name and bonus after
rounding off to zero decimal places.
ii. Display the position of occurrence of
the string “ta” in salesman names.
iii. Display the four characters from
salesman name starting from second
character.
iv. Display the month name for the date of
join of salesman
v. Display the name of the weekday for
the date of join of salesman
Q28. On the basis of the table STUDENT, write SQL queries: i. Write the name of the students who
live in either Agra or Mumbai
ii. Display the details of the female
students in the ascending order of
marks.
iii. Display class wise highest marks.
iv. display the average marks scored by
students of each gender who are in
class XI.
v. display the name of the youngest
student
Q29. On the basis of the table SCHOOLDATA, write sql queries : i. To remove leading spaces from the
column name.
ii. Display the names of students who
were born on Sunday.
iii. Display maximum percent.
iv. Display one character from the left of
gender column who were born in the
year 2005.
v. Display Average percentage of Red
House.
vi. Display name and percentage of
students who were born in the month
of November.
Q30. Based on the table MYPET, write SQL queries: i. Display the breed of all the pets in
uppercase.
ii. Display the total price of all the pets.
iii. Display the average life span of all the
pets.

Q31. Write SQL Queries for the following:

i. Display the name of the month from your date of birth.
 ii. Convert email id SUPPORT@[Link] to lowercase.
iii. Display the number of characters in the name “Johnny
Breganza”
Q32. On the basis of the table PRODUCT, write Output: i. SELECT 10+MOD(QUANTITY,3) FROM
PRODUCT WHERE PNAME=’Eraser’;
ii. SELECT ROUND(PRICE,2) * QUANTITY
FROM PRODUCT WHERE QUANTITY>2;
iii. SELECT UCASE(RIGHT(PNAME,2)) FROM
PRODUCT;
iv. SELECT 10+QUANTITY FROM
PRODUCT WHERE PID=”P1003”;
v. SELECT PRICE*QUANTITY FROM
PRODUCT WHERE QUANTITY<2;
vi. SELECT LEFT(PNAME,3) FROM
PRODUCT;
Q33. On the basis of the table ITEM, write SQL Queries i. Display all the records in descending
order of Stockdate.
ii. Display the Type and total number of
items of each type.
iii. Display the least price.
iv. Display the Itemname with their price
rounded to 1 decimal place.

Q34. On the basis of the table SALESMAN,

write SQL Command to:
i. Count the total number of salesman.
ii. Display the maximum qtysold from
each area.
iii. Display the average qtysold from each
area where number of salesman is
more than 1.
iv. Display all the records in ascending
order of area.
On the basis of the table SALESMAN, write output of:
i. SELECT MAX(Qtysold), MIN(Qtysold) FROM SALESMAN;
ii. SELECT COUNT(Area) FROM SALESMAN;
iii. SELECT LENGTH(Sname) FROM SALESMAN WHERE
MONTH(Dateofjoin)=10;
iv. SELECT Sname FROM SALESMAN WHERE RIGHT(SCode,1)=5;
Q35. On the basis of the table CARMARKET, write sql queries :

i. Display the carname along with the charges rounded off to 1 digit after decimal place.
ii. Display the carname, color and position of the character ‘E’ in the color of all the cars.
iii. Display the carname ,name of the company in lower case of all cars whose year (of dom) is 2020.
iv. Display the number of cars manufactured each year.
v. What is the cardinality and degree of the table CARMARKET?
Q36. Based on the table GARMENT, write SQL queries: i. To display first 3 characters from the
column colour
ii. To delete the record with GCode as 116
iii. To display gname and price of all
garment in descending order of price.
 iv. To change the colour of GARMENT with
code as 116 to “Orange”.
v. What is the degree and cardinality of
‘GARMENT’ table?
vi. Suggest a free software for managing
the database

Q37. On the basis of the table SALESMAN, write queries:

i. Display the name of the salesman whose address is not Delhi.

ii. display the details of all sales record of North Area, regardless of case (whether North / NORTH / north).
iii. Display sname and sales of east and west areas
iv. Display the name of the salesman along with the sales amount rounded off to one decimal point
v. Display last three characters from sname and sales rounded off to 0 decimal place.
Q38. On the basis of the table CUSTOMER, write SQL Command i. To display the following output:

ii. To display customer name and opening balance in the ascending order of opening balance.
iii. To obtain Highest Opening Balance of each City
iv. Display the total no. of records having open_bal between 15000 to 20000
v. To display the first two letters of each customer’s name.
Q39. Write the output of the following SQL query :
SELECT LCASE(SUBSTR("Project Management",9,6));
(a) Manag (b) Manage (c) MANAGE (d) manage
Q40. Write the output of the following SQL command :
select pow(2,2*2);
(a) 16 (b) 2 (c) 4 (d) 8
Q41. In SQL, ________ returns the month name from the specified date.
(a) MONTH( ) (b) DATE( ) (c) MONTHNAME( ) (d) NOW( )
Q42. Which of the following SQL function returns the number of values in the
specified column ignoring the NULL values ?
(a) COUNT(*) (b) COUNT(columnname) (c) LENGTH(*) (d) LENGTH(columnname)
Q43. __________ helps to fetch a group of rows based on common values in a column.
(a) ORDER BY (b) FILTER (c) GROUP BY (d) SELECT BY
Q44. Find the output of the following SQL queries :
Select INSTR("Data Science","ie");
(a) 8 (b) 5 (c) True (d) False
Q45. Arpit wants to find the total commission earned by those departments where the number of employees in the
department is more than 2 and writes the following query :
Select department, count(commission) from TECHNO having count(*)>2 group by department;
But he got an error. Identify the error(s) and rewrite the query by underlining the correction(s) done.
Q46. Ananya, a database executive, needs to display a system’s current date and time. Suggest to her a suitable SQL
function name. Also write the query for the same.
Q47. Consider the table Customer given below and write SQL commands :
 (i) Display the details of all customers who have placed the order in the year 2023.
(ii) Count the total number of customers from India.
(iii) Display the last 9 characters of the email id of all customers of Denmark.
Q48. Write the output (i-vi) for the following SQL commands :

(i) SELECT COUNT(*) FROM CLUB;

(ii) SELECT SUM(Fee) FROM CLUB;
(iii) SELECT LEFT(Activity,2) FROM CLUB WHERE Days='M,W';
(iv) SELECT MID("Exam",1,2);
(v) SELECT MONTHNAME("2023-05-30");
(vi) SELECT MONTH("2023-03-05");
Q49. Write the SQL queries which will perform the following operations :
(i) To display the day part from your date of admission, which is ‘2023-12-13’
(ii) To convert your father’s email id ‘xyz@[Link]’ to uppercase.
(iii) To count the number of characters in the string ‘India’
(iv) To display today’s day name i.e., Sunday or Monday …
(v) To display your height 5.41 after rounding off to 1 decimal place.
Q50. Explain the following SQL functions using suitable examples :
(i) LTRIM( ) (ii) RTRIM( ) (iii) INSTR( ) (iv) MOD( ) (v) TRIM( )
Q51. Consider the following table Student :

Write SQL queries for the following :

(a) Display the Total Marks secured by students of each class.
(b) Display the Average Marks secured by the students of IP optional subject.
(c) Display the total number of students in each Class where the number of students are more than 2.
(d) Display the maximum and minimum marks secured in each optional subject.