St.

Stephen's Senior Secondary School, Ajmer

XII Informatics Practices
MySQL - Worksheet 1 (OTQ's)
Question Pape
r
1 NULL value means: 2020
(A) 0 value (B) 1 value (C) None value (D) None of the above SQP

2 Write the output of the following SQL command. select round(49.88); 2021
(A) 49.88 (B) 49.8 (C) 49.0 (D) 50 SQP

3 The avg() function in MySql is an example of ___________________. 2021

(A) Math function (B) Text function (C) Date Function (D) Aggregate Function SQP

4 The ____________command can be used to makes changes in the rows of a table in SQL. 2021
SQP
5 Consider the table STUDENT given below: 2021
RollNo Name Class DOB Gender City Marks SQP
1 Anand XI 6/6/97 M Agra 430
2 Chetan XII 7/5/94 M Mumbai 460
3 Geet XI 6/5/97 F Agra 470
4 Preeti XII 8/8/95 F Mumbai 492
5 Saniyal XII 8/10/95 M Delhi 360
6 Maakhiy XI 12/12/94 F Dubai 256
7 Neha X 8/12/95 F Moscow 324
8 Nishant X 12/6/95 M Moscow 429

I) State the command that will give the output as :

i. select name from student where class=’XI’ and class=’XII’;
ii. select name from student where not class=’XI’ and class=’XII’;
iii. select name from student where city=”Agra” OR city=”Mumbai”;
iv. select name from student where city IN(“Agra”, “Mumbai”);
Choose the correct option:
(A) Both (i) and (ii). (B) Both (iii) and (iv).
(C) Any of the options (i), (ii) and (iv) (D) Only (iii)

II) What will be the output of the following command?

Select * from student where gender =”F” order by marks;
(A)

(B)

(C) (D)
 III) Prachi has given the following command to obtain the highest marks
Select max(marks) from student where group by class;
but she is not getting the desired result. Help her by writing the correct command.
(A) Select max(marks) from student where group by class;
(B) Select class, max(marks) from student group by marks;
(C) Select class, max(marks) group by class from student;
(D) Select class, max(marks) from student group by class;

IV) State the command to display the average marks scored by students of each gender who
are in class XI?
i. Select gender, avg(marks) from student where class= “XI” group by gender;
ii Select gender, avg(marks) from student group by gender where class=”XI”;
iii. Select gender, avg(marks) group by gender from student having class=”XI”;
iv. Select gender, avg(marks) from student group by gender having class = “XI”;
Choose the correct option:
(A) Both (ii) and (iii) (B) Both (ii) and (iv)
(C) Both (i) and (iii) (D) Only (iii)

V) Help Ritesh to write the command to display the name of the youngest student?
(A) select name,min(DOB) from student ;
(B) select name,max(DOB) from student ;
(C) select name,min(DOB) from student group by name ;
(D) select name,maximum(DOB) from student;

6 Which type of values will not be considered by SQL while executing the following 2023
statement? SQP
SELECT COUNT(column name) FROM inventory;
(A) Numeric value (B) text value (C) Null value (D) Date value

7 If column “Fees” contains the data set (5000,8000,7500,5000,8000), what will be the output 2023
after the execution of the given query? SQP
SELECT SUM (DISTINCT Fees) FROM student;
(A) 20500 (B) 10000 (C) 20000 (D) 33500

8 Which SQL statement do we use to find out the total number of records present in the table 2023
ORDERS? SQP
(A) SELECT * FROM ORDERS; (B) SELECT COUNT (*) FROM ORDERS;
(C) SELECT FIND (*) FROM ORDERS; (D) SELECT SUM () FROM ORDERS;

9 Which one of the following is not an aggregate function? 2023

(A) ROUND() (B) SUM() (C) COUNT() (D) AVG() SQP

1 Which one of the following functions is used to find the largest value from the given data in 2023
0 MySQL? SQP
(A) MAX( ) (B) MAXIMUM( ) (C) BIG( ) (D) LARGE( )

1 In SQL, which function is used to display current date and time? 2023
1 (A) Date () (B) Time () (C) Current () (D) Now () SQP

1 Which of the following is the correct output of the following SQL command? 2023
2 SELECT ROUND (7876.4568, 2);
(A) 7876.46 (B) 7876.45 (C) 7900 (D) 7900.4568

1 Aggregate functions are also known as: 2023

3 (A) Scalar Functions (B) Single Row Functions
 (C) Multiple Row Functions (D) Hybrid Functions

1 Ravisha has stored the records of all students of her class in a MYSQL table. Suggest a 2023
4 suitable SQL clause that she should use to display the names of students in alphabetical
order.
(A) SORT BY (B) ALIGN BY (C) GROUP BY (D) ORDER BY

1 To remove the leading and trailing space from data values in a column of MySql Table, we 2023
5 use
(A) Left( ) (B) Right( ) (C) Trim( ) (D) Ltrim( )

1 If the substring is not present in a string, the INSTR() returns: 2023

6 (A) – 1 (B) 1 (C) NULL (D) 0

1 Which of the following is not a valid aggregate function in MYSQL? 2023

7 (A) COUNT( ) (B) SUM( ) (C) MAX( ) (D) LEN( )

1 Predict the output of the following query: SELECT MOD (9,0); 2024
8 (A) 0 (B) NULL (C) NaN (D) 9 SQP

1 Which of the following SQL functions does not belong to the Math functions category? 2024
9 (A) POWER() (B) ROUND() (C) LENGTH() (D) MOD() SQP

2 Raj, a Database Administrator, needs to display the average pay of workers from those 2024
0 departments which have more than five employees. He is experiencing a problem while SQP
running the following query:
SELECT DEPT, AVG(SAL) FROM EMP WHERE COUNT(*) > 5 GROUP BY DEPT;
Which of the following is a correct query to perform the given task?
(A) SELECT DEPT, AVG(SAL) FROM EMP WHERE COUNT(*) > 5 GROUP BY DEPT;
(B) SELECT DEPT, AVG(SAL) FROM EMP HAVING COUNT(*) > 5 GROUP BY DEPT;
(C) SELECT DEPT, AVG(SAL) FROM EMP GROUP BY DEPT WHERE COUNT(*) > 5;
(D) SELECT DEPT, AVG(SAL) FROM EMP GROUP BY DEPT HAVING COUNT(*) > 5;

2 Predict the output of the following query: SELECT LCASE (MONTHNAME ('2023-03-05')); 2024
1 (A) May (B) March (C) May (D) march SQP

2 With reference to SQL, identify the invalid data type. 2024

2 (A) Date (B) Integer (C) Year (D) Month SQP

2 In SQL, the equivalent of UCASE() is: 2024

3 (A) UPPERCASE () (B) CAPITALCASE() (C) UPPER() (D) TITLE () SQP

2 What will be the output of the following query? SELECT POWER(2,MOD(17,3)); 2024
4 (A) 8 (B) 1 (C) 0 (D) 4

2 Which of the following is not an aggregate function in MYSQL? 2024

5 (A) AVG() (B) MAX() (C) LCASE() (D) MIN()

2 Which MySQL command helps to add a primary key constraint to any table that has already 2024
6 been created?
(A) UPDATE (B) INSERT INTO (C) ALTER TABLE (D) ORDER BY

2 What will be the output of the following query? SELECT SUBSTR("G20 2023 INDIA", 5, 4); 2024
7 (A) G20 2 (B) 2023 (C) INDI (D) 023

2 Which of the following clause cannot work with SELECT statement in MYSQL? 2024
8 (A) FROM (B) INSERT INTO (C) WHERE (D) GROUP BY
2 Which MySQL string function is used to extract a substring from a given string based on a 2024
9 specified starting position and length?
(A) SUBSTRING_INDEX() (B) LENGTH() (C) MID() (D) TRIM()
3 The purpose of WHERE clause in a SQL statement is to: 2025
0 (A) Create a table (B) Filter rows based on a specific condition SQP
(C) Specify the columns to be displayed (D) Sort the result based on a column

3 Identify the SQL command used to delete a relation (table) from a relational database. 2025
1 (A) DROP TABLE (B) REMOVE TABLE (C) DELETE TABLE (D) ERASE TABLE SQP

3 State whether the following statement is True or False: 2025

2 In SQL, the HAVING clause is used to apply filter on groups formed by the GROUP BY clause. SQP

3 Fill in the Blank 2025

3 The COUNT(*) function provides the total number of __________ within a relation (table) in SQP
a relational database.
(A) Columns (B) Unique values (C) Not-null values (D) Rows

3 Match the following SQL functions/clauses with their descriptions: 2025

4 SQL Function Description SQP
P. MAX() 1. Find the position of a substring in a string.
Q. SUBSTRING() 2. Returns the maximum value in a column.
R. INSTR() 3. Sorts the data based on a column.
S. ORDER BY 4. Extracts a portion of a string.
(A) P-2, Q-4, R-3, S-1 (B) P-2, Q-4, R-1, S-3
(C) P-4, Q-3, R-2, S-1 (D) P-4, Q-2, R-1, S-3

3 Assertion (A): In SQL, INSERT INTO is a Data Definition Language (DDL) Command. 2025
5 Reason (R): DDL commands are used to create, modify, or remove database structures, such SQP
as tables.

3 What will be the output of the following SQL command? SELECT MONTHNAME('2024-08- 2025
6 02');
(A) 08 (B) 02 (C) February (D) August

3 Which of the following is not an aggregate function in SQL? 2025

7 (A) COUNT(*) (B) MIN() (C) LEFT() (D) AVG()

3 State whether the following statement is True or False: 2025

8 The MOD() function in SQL returns the quotient of division operation between two
numbers.

3 Which SQL function calculates aᵇ? 2025

9 (A) MOD() (B) POWER() (C) RAISE() (D) ROUND()

4 With respect to SQL, match the function given in column-II with categories given in column- 2025
0 I:
I II
Math function (a) COUNT()
i)
(ii) Aggregate function (b) ROUND()
(iii) Date function (c) RIGHT()
(iv) Text function (d) YEAR()
Options:
 (A) (i)-(c), (ii)-(d), (iii)-(a), (iv)-(b) (B) (i)-(b), (ii)-(a), (iii)-(d), (iv)-(c)
(C) (i)-(d), (ii)-(b), (iii)-(a), (iv)-(c) (D) (i)-(b), (ii)-(a), (iii)-(d), (iv)-(c)

4 Assertion (A): The ROUND() function in SQL can be used to round off a number to a 2025
1 specified number of decimal places.
Reason (R): The ROUND() function is a string function that accepts character values as input
and returns numerical values as output.

St. Stephen's Senior Secondary School, Ajmer

XII Informatics Practices
MySQL - Worksheet 2
1 What is the use of COMMIT in sql? 202
0
SQP
2 What is the function of ALTER command? 202
0
SQP
3 Shewani has recently started working in MySQL. Help her in understanding the difference 202
between the following: 0
SQP
(i) Where and having clause (ii) Count(column_name) and count(*)

4 On the basis of following table answer the given questions: 202

Table: CUSTOMER_DETAILS 0
SQP
Cust_ID Cust_Name Acct_Type Accumlt_Amt DOJ Gender
CNR_001 Manoj Saving 101250 1992-02-19 M
CNR_002 Rahul Current 132250 1998-01-11 M
CNR_004 Steve Saving 18200 1998-02-21 M
CNR_005 Manpreet Current NULL 1994-02-19 M
(i) Write the degree and cardinality of the above table.
(ii) What will be the output of the following query: Select max(DOJ) From Customer_Details;
(iii) Write the sql query to delete the row from the table where customer has no accumulated
amount.

5 Write commands in SQL for (i) to (iv) and output for (v) and (vi). 202
Table : Store 0
SQP
StoreId Name Location City NoOfEmp DateOpen SalesAmt
S101 Planet Fashion Bandra Mumbai 7 2015-10-16 40000
S102 Vogue Karol Bagh Delhi 8 2015-07-14 120000
S103 Trends Powai Mumbai 10 2015-06-24 30000
S104 Super Fashion Thane Mumbai 11 2015-02-06 45000
S105 Annabelle South Extn. Delhi 8 2015-04-09 60000
S106 Rage Defence Colony Delhi 5 2015-03-01 20000
(i) To display names of stores along with SalesAmount of those stores that have ‘fashion’
anywhere in their store names.
(ii) To display Stores names, Location and DateOfOpen of stores that were opened before 1st
March, 2015.
(iii) To display name and location of those store which have either ‘u’ as second character in
their name.
(iv) To display the City and the number of stores located in that City, only if number of stores
is more than 2.
(v) To display names of stores along with Sales Amount of those stores that are located in
Mumbai.
(vi) To display the details of store in alphabetical order of name.
(vii) Select Min(DateOpen) from Store;
 (viii) Select Count(Storeid), Noofemp From Store Group By Noofemp Having
Max(Salesamt)<60000;

6 Consider the table FANS and answer the following. 202

FANS 0
SQP
FAN_ID FAN_NAME FAN_CITY FAN_DOB FAN_MODE
F001 SUSHANT MUMBAI 1998-10-02 MAIL
F002 RIYA MUMBAI 1997-12-12 LETTER
F003 ANIKA DELHI 2001-06-30 BLOG
F004 RUDRA AJMER 2005-08-22 MAIL
F006 MIARA KOLKATA 1998-11-01 BLOG
Write MySQL queries for the following:
i. To display the details of fans in decending order of their DOB
ii. To display the details of FANS who does not belong to AJMER
iii. To count the total number of fans of each fan mode
iv. To display the dob of the youngest fan.

7 Write the command to delete a table STUDENT. 202

0
SQP
8 What do you understand by Primary Key? 202
0
SQP
9 Write the SQL command that will display the current time and date 202
1
SQP
1 State any two differences between single row functions and multiple row functions. 202
0 1
SQP
1 What is the difference between the order by and group by clause when used along with the 202
1 select statement? Explain with an example. 1
SQP
1 Consider the decimal number x with value 8459.2654. Write commands in SQL to: 202
2 i. round it off to a whole number ii. round it to 2 places before the decimal. 1
SQP
1 Anjali writes the following commands with respect to a table employee having fields, empno, 202
3 name, department, commission. 1
SQP
Command1: Select count(*) from employee;
Command2: Select count(commission) from employee;
She gets the output as 4 for the first command but gets an output 3 for the second command.
Explain the output with justification.

1 Consider the following SQL string: "Preoccupied". Write commands to display: 202
4 a. "occupied" b. "cup" 1
SQP
1 Considering the same string “Preoccupied”. Write SQL commands to display: 202
5 a. the position of the substring ‘cup’ in the string “Preoccupied” 1
SQP
b. the first 4 letters of the string

1 A relation Vehicles is given below : 202

6 V_no Type Company Price Qty 1
SQP
AW125 Wagon Maruti 250000 25
J0083 Jeep Mahindra 4000000 15
S9090 SUV Mitsubishi 2500000 18
M0892 Mini van Datsun 1500000 26
W9760 SUV Maruti 2500000 18
R2409 Mini van Mahindra 350000 15
 Write SQL commands to:
a. Display the average price of each type of vehicle having quantity more than 20.
b. Count the type of vehicles manufactured by each company.
c. Display the total price of all the types of vehicles.

1 Consider a table SALESMAN with the following data: 202

7 SNO SNAME SALARY BONUS DATE OF JOIN 1
SQP
A01 Beena Mehta 30000 45.23 29-10-2019
A02 K. L. Sahay 50000 25.34 13-03-2018
B03 Nisha Thakkar 30000 35.00 18-03-2017
B04 Leela Yadav 80000 NULL 31-12-2018
C05 Gautam Gola 20000 NULL 23-01-1989
C06 Trapti Garg 70000 12.37 15-06-1987
D07 Neena Sharma 50000 27.89 18-03-1999
Write SQL queries using SQL functions to perform the following operations:
a) Display salesman name and bonus after rounding off to zero decimal places.
b) Display the position of occurrence of the string “ta” in salesman names.
c) Display the four characters from salesman name starting from second character.
d) Display the month name for the date of join of salesman
e) Display the name of the weekday for the date of join of salesman

1 Write the SQL functions which will perform the following operations: 202
8 i) To display the name of the month of the current date. 1
SQP
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 your first name (fname) from your whole name (name).
v) To compute the remainder of division between two numbers, n1 and n2

1 Predict the output of the following queries: 2021-

22
9 i. Select power(5,3); ii. Select mod(5,3); Term
2 SQP
2 Briefly explain the purpose of the following SQL functions: 2021-
22
0 i. power() ii. mod() Term
2 SQP
2 Help Reshma in predicting the output of the following queries: 2021-
22
1 i) select round(8.72,3); ii) select round(9.8); Term
2 SQP
2 Aryan, a database administrator, has grouped records of a table with the help of group by 2021-
22
2 clause. He needs to further filter groups of records generated through group by clause. Term
Suggest suitable clause for it and properly explain its usage with the help of an example. 2 SQP

2 Mr. Som, a HR Manager in a multinational company “Star-X world” has created the following 2021-
22
3 table to store the records of employees: Term
Table: Emp 2 SQP

Eid EName Department DOB DOJ

Star1 Ivan Sales 1994-08-28 2020-02-14
Star2 Melinda IT 1997-10-15 2021-11-19
Star3 Raj Accounts 1998-10-02 2019-04-02
Star4 Michael Sales 2000-02-17 2020-05-01
Star5 Sajal IT 2001-12-05 2018-06-13
Star6 John Accounts 1995-01-03 2019-07-15
Star7 Julia Sales 1985-11-13 2020-08-19
A) He has written following queries:
i) select max(year(DOB)) from emp;
ii) select ENAME from emp where month(DOJ)=11;
Predict the output.
 B) Based on the table given above, help Mr. Som writing queries for the following task:
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.

2 [Link] is working on a MySQL table named ‘Hotel’ having following structure: 2021-
22
4 Field Type Null Key Default Extra Term
user_id varchar(20) YES NULL 2 SQP
name varchar(20) YES NULL
city varchar(20) YES NULL
mobile_no varchar(11) YES NULL
She need to perform following task on the table:
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.
Suggest suitable SQL function for the same. Also write the query to achieve the desired task.

2 Predict the output of the following queries: 2021-

22
5 i. select instr('exams@[Link]','.'); Term
ii. select substr('exams@[Link]',7,4); 2 SQP

iii. select left('exams@[Link]',5);

2 Reena is working with functions of MySQL. Explain her following: 2021-

22
6 i. What is the purpose of now () function? Term
ii. How many parameters does it accept? 2 SQP

iii. What is the general format of its return type?

2 While dealing with string data type in MySQL, its observed that sometimes unnecessary space 2021-
22
7 character comes in between which hampers the successful execution of a string manipulation Term
module. Name the suitable MySQL function (s) to remove leading, trailing and both type of 2 SQP

space characters from a string. Also give MySQL queries to depict the same.

2 Carefully observe the following table named ‘stock’: 2021-

22
8 Table: stock Term
Pid PName Category Qty Price 2 SQP

1 Keyboard IO 15 450
2 Mouse IO 10 350
3 Wifi-router NW 5 2600
4 Switch NW 3 3000
5 Monitor O 10 4500
6 Printer O 4 17000
Write SQL queries for the following:
(a) To display the records in decreasing order of price.
(b) To display category and category wise total quantities of products.
(c) To display the category and its average price.
(d) To display category and category wise highest price of the products.

2 Satyam, a database analyst has created the following table: 2021-

22
9 Table: Student Term
RegNo SName Stream Optional Marks 2 SQP

S1001 Akshat Science CS 99

S1002 Harshit Commerce IP 95
S1003 Devika Humanities IP 100
S1004 Manreen Commerce IP 98
S1005 Gaurav Humanities IP 82
S1006 Saurav Science CS NULL
 S1007 Bhaskar Science CS 95
S1007 Bhaskar Science CS 96
A) He has written following queries:
(a) select sum(MARKS) from student where OPTIONAL= ‘IP’ and STREAM= ‘Commerce’;
(b) select max(MARKS)+min(MARKS) from student where OPTIONAL= ‘CS’;
(c) select avg(MARKS) from student where OPTIONAL= ‘IP’;
(d) select length(SNAME) from student where MARKS is NULL;
Help him in predicting the output of the above given queries.
B) Based on the above given table named ‘Student’, Satyam has executed following queries:
Select count(*) from student;
Select count(MARKS) from student;
Predict the output of the above given queries.
Also give proper justifications of the output generated through each query.

St. Stephen's Senior Secondary School, Ajmer

XII Informatics Practices
MySQL - Worksheet 3
1 Find the output of the following SQL Queries: 2021-22
Term 2
i) SELECT ROUND(7658.345, 2); ii) SELECT MOD(ROUND(13.9, 0), 3);

2 Give any two differences between the POWER() and SUM() SQL functions. 2021-22
Term 2

3 Find the output of the following SQL queries: 2021-22

Term 2
i) SELECT SUBSTR("FIT INDIA MOVEMENT", 5);
ii) SELECT INSTR("ARTIFICIAL INTELLIGENCE", "IA");

4 Srikanth created the following table STUDENT in his database. 2021-22

Term 2
Table: STUDENT
RollNo Name Class Marks
1 Ritika 12 40
2 Angad 12 35
3 Kaveri 11 42
4 Lalitha 12 21
5 Daniel 11 44
6 Rabindra 11 39
7 Rabia 11 28
He now wants to count number of students in each class where the number of students is
more than 3. He has executed the following query:
SELECT MAX(Marks) FROM STUDENT WHERE COUNT(*) > 3 GROUP BY Class;
But, he got an error. Identify the error(s) and rewrite the query. Also underline the
correction(s) done.

5 Ms. Mohini is working in a school and stores the details of all students in a table 2021-22
Term 2
SCHOOLDATA.
TABLE: SCHOOLDATA
Admno Name Class House Percent Gender Dob
20150001 Aditya Das 10 Green 86 Male 2006-02-20
20140212 Harsh Sharma 11 Red 75 Male 2004-10-05
20090234 Swapnil Pant 10 Yellow 84 Female 2005-11-21
20130216 Soumen Rao 9 Red 91 Male 2006-04-10
20190227 Rahil Arora 10 Blue 70 Male 2005-05-14
20120200 Akasha Singh 11 Red 64 Female 2004-12-16
A) Write SQL statements from the above given table to:
i) To remove leading spaces from the column Name.
ii) Display the names of students who were born on Sunday.
 B) Predict the output of the following SQL queries from the given table: SCHOOLDATA
i) SELECT MAX(Percent) from SCHOOLDATA;
ii) SELECT LEFT(Gender,1), Name from SCHOOLDATA WHERE YEAR(Dob)=2005;

6 Consider a table "MYPET" with the following data: 2021-22

Term 2
Table: MYPET
Pet_id Pet_Name Breed LifeSpan Price Discount
101 Rocky Labrador Retriever 12 16000 5
202 Duke German Shepherd 13 22000 10
303 Oliver Bulldog 10 18000 7
404 Cooper Yorkshire Terrier 16 20000 12
505 Oscar Shih Tzu NULL 25000 8
Write SQL queries for the following:
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.

7 Predict the output of the following SQL queries: 2021-22

Term 2
i) SELECT TRIM(" ALL THE BEST ");
ii) SELECT POWER(5,2);
iii) SELECT UPPER(MID("start up india", 10));

8 Write the names of SQL functions to perform the following operations: 2021-22
Term 2
i) Display name of the Month from your date of birth.
ii) Convert email-id to lowercase.
iii) Count the number of characters in your name.

9 Consider the following table: PRODUCT 2021-22

Term 2
Table: PRODUCT
PID PNAME PRICE QUANTITY
P1001 Eraser 10.50 5
P1002 Ball Pen 15.00 2
P1003 Gel Pen 25.10 3
P1004 Ruler 5.00 1
Find the output of the following SQL queries:
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;

1 Consider the table : ITEM 2021-22

Term 2
0 Table : ITEM
SNo Itemname Type Price Stockdate
1 Chaises Living 11500.58 2020-02-19
2 Accent Chairs Living 31000.67 2021-02-15
3 Baker Racks Kitchen 25000.623 2019-01-01
4 Sofa Living 7000.3 2020-10-18
5 Nightstand Bedroom NULL 2021-07-23
Write SQL queries for the following:
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.

1 Consider the following table: 2021-22

Term 2
1 Table : SALESMAN
 Scode Sname Area Qtysold Dateofjoin
S001 Ravi North 120 2015-10-01
S002 Sandeep South 105 2012-08-01
S003 Sunil NULL 68 2018-02-01
S004 Subh West 280 2010-04-01
S005 Ankit East 90 2018-10-01
S006 Raman North NULL 2019-12-01
A) Predict the output for the following SQL queries:
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;
B) Based on the given table SALESMAN write SQL queries to perform the following
operations:
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.

1 Rashmi, a database administrator needs to display house wise total number of records of 2023
2 ‘Red’ and ‘Yellow’ house. She is encountering an error while executing the following query: SQP
SELECT HOUSE, COUNT (*) FROM STUDENT GROUP BY HOUSE WHERE HOUSE=’RED’ OR
HOUSE= ‘YELLOW’;
Help her in identifying the reason of the error and write the correct query by suggesting the
possible correction (s).

1 What is the purpose of Order By clause in SQL? Explain with the help of suitable example. 2023
3 SQP
1 Write outputs for SQL queries (i) to (iii) which are based on the given table PURCHASE: 2023
4 TABLE: PURCHASE SQP
CNO CNAME CITY QUANTITY DOP
C01 GURPREET NEW DELHI 150 2022-06-11
C02 MALIKA HYDERABAD 10 2022-02-19
C03 NADAR DALHOUSIE 100 2021-12-04
C04 SAHIB CHANDIGARH 50 2021-10-10
C05 MEHAK CHANDIGARH 15 2021-10-20
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’;

1 Based on table STUDENT given here, write suitable SQL queries for the following: 2023
5 Roll No Name Class Gender City Marks SQP
1 Abhishek XI M Agra 430
2 Prateek XII M Mumbai 440
3 Sneha XI F Agra 470
4 Nancy XII F Mumbai 492
5 Himanshu XII M Delhi 360
6 Anchal XI F Dubai 256
7 Mehar X F Moscow 324
8 Nishant X M Moscow 429
i. Display gender wise highest marks.
ii. Display city wise lowest marks.
iii. Display total number of male and female students.

1 Discuss the significance of Group by clause in detail with the help of suitable example. 2023
6 SQP
1 Write suitable SQL query for the following: 2023
7 i. Display 7 characters extracted from 7th left character onwards from the string ‘INDIA SQP
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’.

1 Explain the following SQL functions using suitable examples. 2023

8 i. UCASE() ii. TRIM() iii. MID() iv. DAYNAME() v. POWER() SQP

1 Differentiate between COUNT( ) and COUNT(*) functions in MYSQL. Give suitable examples 2023
9 to support your answer. SQP

2 Shreya, a database administrator has designed a database for a clothing shop. Help her by 2023
0 writing answers of the following questions based on the given table:
TABLE: CLOTH
CCODE CNAME SIZE COLOR PRICE DOP
C001 JEANS XL BLUE 990 2022-01-21
C002 T SHIRT M RED 599 2021-12-12
C003 TROUSER M GREY 399 2021-11-10
C004 SAREE FREE GREEN 1299 2019-11-12
C005 KURTI L WHITE 399 2021-12-07
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.
2 Keshav has written the following query to find out the sum of bonus earned by the 2023
1 employees of WEST zone :
SELECT zone, TOTAL (bonus) FROM employee HAVING zone = 'WEST';
But he got an error. Identify the errors and rewrite the query by underlining the
correction(s) done.
2 Consider the table Patient given below and write SQL commands. 2023
2 Table: Patient
Patientid Name City Phone Dateofadm Department
1000001 Ritvik Garg Delhi 68476213 2021-12-10 Surgery
1000002 Rahil Arora Mumbai 36546321 2022-01-08 Medicine
1000003 Mehak Bhatt Delhi 68721879 2022-02-02 Cardiology
1000004 Soumik Rao Delhi 26543266 2022-01-11 Medicine
1000005 Suresh Sood Bangalore 65432442 2021-03-09 Surgery
(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.

2 Write the output (i-iii) for the following SQL commands. 2023
3 Table: FASHION
ID Product Price Qty
F01 Kajal 970 10
F02 Foundation 2100 15
F03 Night Cream 1700 20
F04 Day Cream 1400 10
F05 Shampoo 1200 25
 F06 Lipstick 850 32
(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;
Find the output of the following SQL queries :
(i) SELECT SUBSTR("CLIMATE CHANGE",4,4);
(ii) SELECT UCASE(RIGHT("Pollution",3));
(iii) SELECT LENGTH("HAPPY")+3;

2 Write the SQL queries which will perform the following operations: 2023
4 (i) To display the year from your Date of Admission which is '2023-05-15'.
(ii) To convert your email id 'ABC@[Link]' to lowercase.
(iii) To remove leading spaces from a string 'my country'.
(iv) To display current date.
(v) To display the value of 106

St. Stephen's Senior Secondary School, Ajmer

XII Informatics Practices
MySQL - Worksheet 4
1 Consider a table PRODUCT with the following data: 202
Table: PRODUCT 3
SNO Itemname Company Stockdate Price Discount
1 Monitor HP 2021-12-20 15499.739 15
2 Webcam Logitech 2020-02-03 4890.90 5
3 Keyboard Logitech 2022-08-19 1878.985 30
4 Mouse HCL 2021-05-16 1200.00 7
5 Speakers iBall 2021-10-19 NULL 25
Write SQL queries using SQL functions to perform the following operations :
(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'

2 Consider the following table Schooldata : 202

Table: Schooldata 3
Admno Name Grade Club Marks Gender
20150001 Sargam Singh 12 STEM 86 Male
20140212 Alok Kumar 10 SPACE 75 Male
20090234 Mohit Gaur 11 SPACE 84 Male
20130216 Romil Malik 10 READER 91 Male
20190227 Tanvi Batra 11 STEM 70 Female
20120200 Nomita Ranjan 12 STEM 64 Female
Write SQL queries for the following:
(i) Display the average Marks secured by each Gender.
(ii) Display the minimum Marks secured by the students of Grade 10.
(iii) Display the total number of students in each Club where number of students is more than
1.
(iii) Display the maximum and minimum marks secured by each gender. 2

3 Consider the given SQL string: "12#All the Best!". Write suitable SQL queries for the 202
following: 4
SQP
i. Returns the position of the first occurrence of the substring “the” in the given string.
ii. To extract last five characters from the string.
4 Based on the SQL table CAR_SALES, write suitable queries for the following: 202
NUMBER SEGMENT FUEL QT1 QT2 4
SQP
1 Compact HatchBack Petrol 56000 70000
2 Compact HatchBack Diesel 34000 40000
3 MUV Petrol 33000 35000
4 MUV Diesel 14000 15000
5 SUV Petrol 27000 54000
6 SUV Diesel 18000 30000
7 Sedan Petrol 8000 10000
8 Sedan Diesel 1000 5000
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. OR Predict the
output of the following queries based on the table CAR_SALES given above:
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";

5 What are aggregate functions in SQL? Name any two. 202

4
SQP
6 Write MySQL statements for the following: 202
i. To create a database named FOOD. 4
SQP
ii. To create a table named Nutrients based on the following specification:
Column Name Data Type Constraints
Food_Item Varchar(20) Primary Key
Calorie Integer

7 Preeti manages database in a blockchain start-up. For business purposes, she created a table 202
named BLOCKCHAIN. Assist her by writing the following queries: 4
SQP
TABLE: BLOCKCHAIN
id user value hash transaction_date
1 Steve 900 ERTYU 2020-09-19
2 Meesha 145 @345r 2021-03-23
3 Nimisha 567 #wert5 2020-05-06
4 Pihu 678 %rtyu 2022-07-13
5 Kopal 768 rrt4% 2021-05-15
7 Palakshi 534 wer@3 2022-11-29
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.

8 Write suitable SQL queries for the following: 202

i. To calculate the exponent for 3 raised to the power of 4. 4
SQP
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’.

9 Kabir has created following table named exam: 202

RegNo Name Subject Marks 4
SQP
1 Sanya Computer Science 98
2 Sanchay IP 100
 3 Vinesh CS 90
4 Sneha IP 99
5 Akshita IP 100
Help him in writing SQL queries to the perform the following task:
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.

1 (a) Consider the given table and write the following queries in MySQL : 202
0 Table: Sports 4
Sid SName Fees DateofPlay CoachId
1 Karate 1200 2024-08-24 S1
2 Football 1800 2024-09-13 S2
3 Cricket 1500 2024-06-14 S3
4 Lawn Tennis 2500 2024-09-25 S4
5 Badminton 1800 2024-10-20 S5
(i) To display Sid and name of those sports which are to be played in the month of September.
(ii) To display all Sports names in lower case.
(iii) To display last two characters of all sports names whose fees is less than 1500.
(b) Predict the output of the following queries based on the table Sports given above:
(i) SELECT UCASE(TRIM(SName)) FROM Sports WHERE Sid=5 ;
(ii) SELECT LEFT (SName,3) FROM Sports WHERE DateofPlay>"2024-09-13";
(iii) SELECT SUBSTR (CoachId,1) FROM Sports WHERE SName="Cricket" or Fees>=2000;

1 What will be the output of the following SQL queries : 202

1 (i) SELECT RIGHT ("CHANDRAYAN3",4); (ii) SELECT ROUND(76345.456,2); 4

1 Write any two differences between UPDATE and ALTER TABLE commands of MySQL. 202
2 4
1 Answer the following questions based on the table Salesman given below : 202
3 Table: Salesman 4
salesman_id name city commission
5001 Neil Bhatt Delhi 0.15
5002 Rohan Malik Mumbai 0.13
5005 Ravi Mohan Delhi 0.11
5006 Mehak Rai Delhi 0.14
5007 Paul Lal Bangalore 0.13
5003 Raman Sen Kolkata 0.12
(i) How many tuples does the given table have?
(ii) Suggest the primary key for the given table.
(iii) Write the MySQL query to display all the records in descending order of commission.

1 Excellent Consultancy Pvt. Ltd. maintains two tables for all its employees. 202
4 Table: Employee 4
Employee_ id First_ name Last_ name Salary Joining_date Department
El0l Monika Das 100000 2019-01-20 Finance
E102 Mehek Verma 600000 2019-01-15 IT
E103 Manan Pant 890000 2019-02-05 Banking
E104 Shivam Agarwal 200000 2019-02-25 Insurance
E105 Alisha Singh 220000 2019-02-28 Finance
E106 Poonam Sharma 400000 2019-05-10 IT
E107 Anshuman Mishra 123000 2019-06-20 Banking
 Table: Reward
Employee_id Date_reward Amount
E101 2019-05-11 1000
E102 2019-02-15 5000
E103 2019-04-22 2000
E106 2019-06-20 8000
A) Write suitable SQL queries to perform the following task :
(i) Change the Department of Shivam to IT in the table Employee.
(ii) Remove the record of Alisha from the table Employee.
(iii) Add a new column Experience of integer type in the table Employee.
(iv) Display the first name, last name and amount of reward for all employees from the tables
Employee and Reward.
(v) Display first name and salary of all the employees whose amount is less than 2000 from
the tables Employee and Reward.
B) Write suitable SQL queries for the following task:
(i) Display the year of joining of all the employees from the table Employee.
(ii) Display each department name and its corresponding average salary.
(iii) Display the first name and date of reward of those employees who joined on Monday
from the tables Employee and Reward.
(iv) Display sum of salary of those employees whose reward amount is greater than 3000
from the tables Employee and Reward.
(v) Remove the table Reward.

1 Rupam created a MySQL table to store the details of Nobel prize winners. Help her to write 202
5 the following MySQL queries: 4
Table: NOBEL
Winner_id YEAR SUBJECT WINNER COUNTRY CATEGORY
1001 1970 Physics Hannes Alfven Sweden Scientist
1002 1970 Physiology Bernard Katz NULL Scientist
1003 1970 Literature Aleksandr Solzhenitsyn Russia Linguist
1004 1971 Chemistry Gerhard Herzberg Germany Scientist
1005 1978 Peace Menachem Begin Israel Prime Minister
1006 1987 Economics Robert Solow USA Economist
1007 1994 Literature Kenzaburo Oe Japan Linguist
(i) Display the names of Nobel Prize winner in 'Literature’ for the year 1970.
(ii) Display the subject and category of winners whose country is not known.
(iii) Display the details of all Nobel Prize winners who were Scientists.
(iv) Count total number of winners whose subject is Literature.

1 Consider the string: "Database Management System". Write suitable SQL queries for the 202
6 following: 5
SQP
I. To extract and display "Manage" from the string.
II. Display the position of the first occurrence of "base" in the given string.

1 Define the term Primary Key in a database. Explain how it is different from a Candidate Key. 202
7 5
SQP
1 I. Write an SQL statement to create a table named STUDENTS, with the following 202
8 specifications: 5
SQP
Column Name Data Type Key
StudentID Numeric Primary Key
FirstName Varchar(20)
LastName Varchar(10)
DateOfBirth Date
Percentage Float(10,2)
 II. Write SQL Query to insert the following data in the Students Table
1, Supriya, Singh, 2010-08-18, 75.5

1 Consider the following tables: 202

9 Table 1: EMPLOYEE which stores Employee ID (EMP_ID), Employee Name (EMP_NAME), 5
SQP
Employee City (EMP_CITY)
Table 2: PAYROLL which stores Employee ID (EMP_ID), Department (DEPARTMENT),
Designation (DESIGNATION), and Salary (SALARY) for various employees.
Note: Attribute names are written within brackets.
Table: EMPLOYEE Table: PAYROLL
EMP_ID EMP_NAME EMP_CITY EMP_ID DEPARTMENT DESIGNATION SALARY
1 ABHINAV AGRA 1 SALES MANAGER 75000
2 KABIR FARIDABAD 2 SALES ASSOCIATE 50000
3 ESHA NOIDA 3 ENGINEERING MANAGER 95000
4 PAUL SEOUL 4 ENGINEERING ENGINEER 70000
5 VICTORIA LONDON 5 MARKETING MANAGER 65000
Write appropriate SQL queries for the following:
I. Display department-wise average Salary.
II. List all designations in the decreasing order of Salary.
III. Display employee name along with their corresponding departments.

St. Stephen's Senior Secondary School, Ajmer

XII Informatics Practices
MySQL - Worksheet 5
1 Consider the following tables: 202
Table 1: ATHLETE, which stores AthleteID, Name, Country. The table displays basic 5
information of the athletes SQP
Table 2: MEDALS, which stores AthleteID, Sport, and Medals. The table displays the number
of medals won by each athlete in their respective sports.
Table: ATHLETE Table: MEDALS
AthleteID Name COUNTRY AthleteID Sport Medals
101 Arjun INDIA 101 Swimming 8
102 Priya INDIA 102 Track 3
103 Asif UAE 103 Gymnastics 5
104 Rozy USA 104 Swimming 2
105 David DENMARK 105 Track 6
Write appropriate SQL queries for the following:
I. Display the sports-wise total number of medals won.
II. Display the names of all the Indian athletes in uppercase.
III. Display the athlete name along with their corresponding sports
2 Rahul, who works as a database designer, has developed a database for a bookshop. This 202
database includes a table BOOK whose column (attribute) names are mentioned below: 5
BCODE: Shows the unique code for each book. SQP
TITLE: Indicates the book’s title.
AUTHOR: Specifies the author’s name.
PRICE: Lists the cost of the book.
Table: BOOK
BCODE TITLE AUTHOR PRICE
B001 MIDNIGHT'S CHILDREN SALMAN RUSHDIE 500
B002 THE GOD OF SMALL ARUNDHATI ROY 450
THINGS
B003 A SUITABLE BOY VIKRAM SETH 600
B004 THE WHITE TIGER ARAVIND ADIGA 399
B005 TRAIN TO PAKISTAN KHUSHWANT SINGH 350
 I. Write SQL query to display book titles in lowercase.
II. Write SQL query to display the highest price among the books.
III. Write SQL query to display the number of characters in each book title.
IV. Write SQL query to display the Book Code and Price sorted by Price in descending order.
3 Dr. Kavita has created a database for a hospital's pharmacy. The database includes a table 202
named MEDICINE whose column (attribute) names are mentioned below: 5
MID: Shows the unique code for each medicine. SQP
MED_NAME: Specifies the medicine name
SUPP_CITY: Specifies the city where the supplier is located.
STOCK: Indicates the quantity of medicine available.
DEL_DATE: Specifies the date when the medicine was delivered.
Table: MEDICINE
MID MED_NAME SUPP_CITY STOCK DEL_DATE
M01 PARACETAMOL MUMBAI 200 2023-06-15
M02 AMOXICILLIN KOLKATA 50 2023-03-21
M03 COUGH SYRUP BENGALURU 120 2023-02-10
M04 INSULIN CHENNAI 135 2023-01-25
M05 IBUPROFEN AHMEDABAD 30 2023-04-05
Write the output of the following SQL Queries.
I. Select LENGTH(MED_NAME) from MEDICINE where STOCK > 100;
II. Select MED_NAME from MEDICINE where month(DEL_DATE) = 4;
III. Select MED_NAME from MEDICINE where STOCK between 120 and 200;
IV. Select max(DEL_DATE) from MEDICINE;
4 Write suitable SQL query for the following: 202
I. To display the average score from the test_results column (attribute) in the Exams table 5
II. To display the last three characters of the registration_number column (attribute) in the SQP
Vehicles table. (Note: The registration numbers are stored in the format DL-01-AV-1234)
III. To display the data from the column (attribute) username in the Users table, after
eliminating any leading and trailing spaces.
IV. To display the maximum value in the salary column (attribute) of the Employees table.
V. To determine the count of rows in the Suppliers table.
5 Write suitable SQL query for the following: 202
I. Round the value of pi (3.14159) to two decimal places. 5
II. Calculate the remainder when 125 is divided by 8. SQP
III. Display the number of characters in the word 'NewDelhi'.
IV. Display the first 5 characters from the word 'Informatics Practices'.
V. Display details from 'email' column (attribute), in the 'Students' table, after removing any
leading and trailing spaces.
6 Consider the string, "Informatics Practices". Write suitable SQL queries for the following: 202
(i) To convert the entire string to uppercase. 5
(ii) To display the total number of characters in the given string.
7 What is a Database Management System (DBMS)? Mention any two examples of DBMS. 202
5
8 Given the following tables: 202
Table: STUDENTS Table: GRADES 5
S_ID NAME AGE CITY S_ID SUBJECT GRADE
1 Rahul 20 Delhi 1 Math A
2 Priya 22 Mumbai 2 English B
3 David 21 Delhi 3 Math C
4 Neha 23 Bengaluru 4 English A
 5 Khurshid 22 Delhi 5 Math B
Write SQL queries for the following:
(i) To display the number of students from each city.
(ii) To find the average age of all students.
(iii) To list the names of students and their grades.
9 Consider the following tables: 202
Table 1: PRODUCTS Table 2: SALES 5
This table stores the basic details of the products This table records the number of units sold for
available in a shop. each product.
PID PName Category SaleID PID UnitsSold
201 Laptop Electronics 301 201 50
202 Chair Furniture 302 202 100
203 Desk Furniture 303 203 60
204 Smartphone NULL 304 204 80
205 Tablet Electronics 305 205 70
Write SQL queries for the following:
(i) To delete those records from table SALES whose UnitsSold is less than 80.
(ii) To display names of all products whose category is not known.
(iii) To display the product names along with their corresponding units sold.

1 Write the SQL statement to create a table, Customer with the following specifications: 202
0 Table: Customer 5
Column Name Data Type Key
CID Int Primary Key
FName Varchar(20)
LName Varchar(20)
Age Int
(ii) Write the SQL query to display all records in descending order of LName from the Table
Customer.
1 An educational institution is maintaining a database for storing the details of courses being 202
1 offered. The database includes a table COURSE with the following attributes: 5
C_ID: Stores the unique ID for each course.
C_NAME: Stores the course's name.
INSTRUCTOR: Stores the name of the course instructor.
DURATION: Stores the duration of the course in hours.
Table: COURSE
C_ID C_NAME INSTRUCTOR DURATION
C101 Data Structures Dr. Alok 40
C102 Machine Learning Prof. Sunita 60
C103 Web Development Ms. Sakshi 45
C104 Database Management Mr. Suresh 50
C105 Python Programming Dr. Pawan 35
Write SQL queries for the following:
(i) To add a new record with following specifications:
 C_ID: C106, C_NAME: Introduction to AI, INSTRUCTOR: Ms. Preeti, DURATION: 55
(ii) To display the longest duration among all courses.
(iii) To count total number of courses run by the institution.
(iv) To display the instructors’ name in lower case.
1 Ashutosh, who is a manager, has created a database to manage employee records. The 202
2 database includes a table named EMPLOYEE whose attribute names are mentioned below: 5
EID: Stores the unique ID for each employee.
EMP_NAME: Stores the name of the employee.
 DEPT: Stores the department of the employee.
SALARY: Stores the salary of the employee.
JOIN_DATE: Stores the employee’s joining date.
Table: EMPLOYEE
EID EMP_NAME DEPT SALARY JOIN_DATE
E01 ARJUN SINGH SALES 75000 2019-11-01
E02 PRIYA JAIN ENGINEERING 65000 2020-06-20
E03 RAVI SHARMA MARKETING 60000 2021-08-16
E04 AYESHA NULL 40000 2021-01-05
E05 RAHUL VERMA FINANCE 90000 2017-06-21
Write the output of the following SQL Queries:
(i) Select SUBSTRING(EMP_NAME, 1, 5) from EMPLOYEE where DEPT = 'ENGINEERING';
(ii) Select EMP_NAME from EMPLOYEE where month(JOIN_DATE) = 8;
(iii) Select EMP_NAME from EMPLOYEE where SALARY > 60000;
(iv) Select count(DEPT) from EMPLOYEE;
1 Write SQL query for the following: 202
3 (i) To display sum total of all the values of the Score column, from STUDENTS table. 5
(ii) To display the first five characters of the Name column from STUDENTS table.
(iii) To display the values of Name column from the STUDENTS table, after removing the
trailing spaces.
(iv) To retrieve the lowest score from the Score column of GRADES table.
(v) To increase the fee of all students by 100, in the STUDENTS table. (The name of the
column is FEE).
1 Write SQL queries for the following: 202
4 (i) To calculate the square of 15. 5
(ii) To round the number 456.789 to the nearest integer.
(iii) To display the position of first occurance of 'com' in the string '[Link]'.
(iv) To display the name of the day for the date '2024-11-07'.
(v) To display the current date and time.
1 Assume that you are working in the IT Department of a Creative Art Gallery (CAG), which sells 202
5 different forms of art creations like Paintings, Sculptures, etc. The data of Art Creations and 5
Artists are kept in tables Articles and Artists respectively. Following are few records from
these two tables:
Table: Articles
Code A_Code Article DOC Price
PL001 A0001 Painting 2018-10-19 20000
SC028 A0004 Sculpture 2021-01-15 16000
QL005 A0003 Quilling 2024-04-24 3000
Table: Artists
A_Cod Name Phone Email DOB
e
A0001 Roy 595923 r@[Link] 1986-10-12
A0002 Ghosh 1122334 ghosh@[Link] 1972-02-05
A0003 Gargi 121212 Gargi@[Link] 1996-03-22
A0004 Mustafa 33333333 Mf@[Link] 2000-01-01
Note:
 The tables contain many more records than shown here.
 DOC is Date of Creation of an Article.
As an employee of CAG, you are required to write the SQL queries for the following:
(i) To display all the records from the Articles table in descending order of price.
(ii) To display the details of Articles which were created in the year 2020.
(iii) To display the structure of Artists table.
(iv) To display the name of all artists whose Article is Painting through Equi Join.
Saman has been entrusted with the management of Law University Database. He needs to
access some information from FACULTY and COURSES tables for a survey analysis. Help him
extract the following information by writing the desired SQL queries as mentioned below.
Table: FACULTY Table: COURSES
F_I FName LName Hire_Date Salary C_I F_ID CName Fees
D D
102 Amit Mishra 12-10- 12000 C21 102 Grid Computing 40000
1998 C22 106 System Design 16000
103 Nitin Vyas 24-12- 8000 C23 104 Computer Security 8000
1994 C24 106 Human Biology 15000
104 Rakshit Soni 18-5-2001 14000 C25 102 Computer Network 20000
105 Rashmi Malhotra 11-9-2004 11000 C26 105 Visual Basic 6000
106 Sulekha Srivastava 5-6-2006 10000
(I) To display complete details (from both the tables) of those Faculties whose salary is less
than 12000.
(II) To display the details of courses whose fees is in the range of 20000 to 50000 (both
values included).
(III) To increase the fees of all courses by 500 which have "Computer" in their Course names.
(IV) To display names (FName and LName) of faculty taking System Design.
(V) To display the Cartesian Product of these two tables.