Class 12 IP – MySQL Test Paper
Unit: Database Query using SQL
Total Marks: 70
Time: 2 Hours
Instructions:
1. Attempt all questions.
2. Use the Students table and the data provided for practical questions.
Table: Students
StudentID StudentName Department Marks DateOfJoining
101 Mohit CS 85 2023-01-15
102 Sagar IT 78 2022-12-10
103 Manish CS 92 2023-02-05
104 Jayesh IT 65 2023-01-20
105 Priya CS 70 2022-11-25
Part A: Important MySQL MCQs / 1 Mark Questions (15 Marks)
1. Identify the data type returned by the following query:
SELECT LENGTH("LENGTH");
a) Numeric value b) Text value c) Null value d) Float value
2. If column SCORE of table PLAYER contains (45,80,35,15,7), output of:
SELECT MAX(SCORE) - MIN(SCORE) FROM PLAYER;
a) 38 b) 73 c) 8 d) 35
3. Column temp of table weather contains (45,35,35,28,28). Output of:
SELECT AVG(DISTINCT temp) FROM weather;
a) 34.2 b) 21.6 c) 38.33 d) 36
4. Category of MID() function:
a) Math b) Text c) Date d) Aggregate
5. Output of:
SELECT ROUND(155.9772,-1);
a) 155 b) 156 c) 160 d) 156
6. String "[Link] is best", which command(s) gives best?
i) RIGHT("[Link] is best",4)
ii) LEFT("[Link] is best",4)
iii) SUBSTR("[Link] is best",23,4)
iv) SUBSTR("[Link] is best",-4)
a) (i) b) (i) & (iii) c) (i) & (iv) d) (i), (iii) & (iv)
7. Table matches with venue = ('Ahmedabad','Baroda','Ahmedabad','Anand','Baroda'). Output
of:
SELECT COUNT(DISTINCT venue) FROM matches;
a) 5 b) 3 c) 2 d) 4
8. SQL to find temperatures in increasing order of all cities:
a) SELECT city FROM weather ORDER BY temperature;
b) SELECT city, temperature FROM weather;
c) SELECT city, temperature FROM weather ORDER BY temperature;
d) SELECT city, temperature FROM weather ORDER BY city;
9. Aggregate function(s): MIN(), MAX(), POWER(), NOW()
a) Only MIN() b) MIN() & MAX() c) MIN(), MAX(), POWER() d) MIN(), POWER(), NOW()
10. “WHERE and HAVING clauses can be used interchangeably”:
a) True b) False c) Only in views d) With ORDER BY
11. Function that returns current execution time:
a) SYSDATE() b) NOW() c) CURRENT() d) TIME()
12. Function to display current date and time:
a) DATE() b) TIME() c) CURRENT() d) NOW()
13. Output of:
SELECT SUBSTR("BoardExam@2023",-1,7);
a) 3 b) am@2023 c) BoardEx d) 3202@ma
14. Output of:
SELECT ROUND(9999.299,2);
a) 9999.30 b) 10000.29 c) 19999.00 d) 10000.30
15. Use of DESC with ORDER BY:
a) Display all rows b) Descriptive format c) Distinct rows d) Descending order
Part B: SQL Practical Questions – Students Table (55 Marks)
Section A: Mathematical Functions (10 Marks)
1. Round the marks of all students to the nearest 10.
2. Find the square root of marks for all students.
3. Display the absolute difference between the highest and lowest marks.
4. Increase all marks by 5% using an arithmetic operation.
Section B: Text Functions (10 Marks)
5. Display first 3 letters of each student’s name using LEFT().
6. Convert all StudentName to uppercase using UPPER().
7. Remove leading and trailing spaces from StudentName using TRIM().
8. Display the position of letter “a” in each StudentName using INSTR().
9. Extract the substring of StudentName starting from 2nd character of length 3 using
SUBSTR().
Section C: Date Functions (10 Marks)
10. Display the current date using CURDATE().
11. Display the current date and time using NOW().
12. Find the year, month, and day of DateOfJoining using YEAR(), MONTH(), DAY().
13. Add 30 days to DateOfJoining using DATE_ADD().
14. Find the difference in days between today and DateOfJoining using DATEDIFF().
Section D: Aggregate Functions (10 Marks)
15. Find the total number of students using COUNT().
16. Find the average marks in each department using AVG() and GROUP BY.
17. Find the highest and lowest marks in the CS department using MAX() and MIN().
18. Find the sum of marks for all students in IT department.
19. Count distinct departments using COUNT(DISTINCT Department).
Section E: Mixed Function Queries (Combination) (15 Marks)
20. Display StudentName in uppercase and first 2 letters of Department.
21. Show StudentName and Marks rounded to nearest 5 along with joining year.
22. Find students whose Marks are above average in their department.
23. Display StudentName, DateOfJoining, and days passed since joining.
24. Display StudentName and last 3 letters of their name along with rounded marks.