[Type here]

Sr.No. Questions

Unit 2: Database Query using SQL

Q. 01 Write
i) the queries in MySQL for the following requirements:
Create a Students table as follows:
Field Name Data Type Size Constraints

Student_Id Integer - Primary Key

Sname Varchar 20

Marks Integer -
ii) Insert the following details of the students in the above table.
(In copy write the query for inserting only first record but practically for further queries
insert all other queries in the table.)
Table : Students
Student_Id Sname Marks

1 Amit 72

2 Bharat 88

3 Chintan 68

4 Dinesh 54

5 Farahan 90

6 Hitesh 64

7 Jitesh 42

8 Ketan 59
iii) Get the details of the students with marks more than 80.

iv) Get the details of those students who have scored marks between 70 and 85 (both
values inclusive)
v) Get the details of those students whose name is “Hitesh”

vi) Get the details of those students whose name end with “esh”.

vii) Get the details of those students who marks are below 70 or above 80 using between in
the required manner.

Session 2023-24 Page 1 of 10

[Type here]

viii) Permanently change the marks of student to 95 whose name is “Farahan”.

ix) Delete the record of particular student whose Student_Id is 8.

x) Find the min, max, sum, and average of the marks from the above table.

xi) Display only student ID and marks table in descending order of the marks.

Session 2023-24 Page 2 of 10

[Type here]

Q. 2 Consider the following tables Doctor. Write SQL commands for the statement
(i) to (vii)
Table: Doctor
ID Name Dept Gender Experience Basic

101 John ENT M 12 23000

104 Smith ORTHOPEDIC M 5 12000

107 George CARDIOLOGY M 10 32000

114 Lara SKIN F 3 12000

109 Katarina MEDICINE F 9 42000

105 Johnson ORTHOPEDIC M 10 18900

117 Lucy ENT F 3 14500

111 Bill MEDICINE F 12 49000

130 Morphy ORTHOPEDIC M 15 38000 and

Write the queries for the following situations:

i. To create the table Doctor choose the appropriate datatypes size (if needed) for
each attribute as per the observation of the data under it.
ii. To insert the first record in the table from the above-mentioned records. iii.
To display the Department name without repetition.
iv. To display the name of doctors who are in “Medicine” and having experience of more
than 10 years.
v. To display ID, name of doctors with a prefix “Dr.” with their department name of all those
records where Basic salary is more than 20000.
(example : The doctor’s name should appear like Dr. John) vi. To display ID, Doctor’s
name and only first three letters of the Department name and Experience for all those doctors
who are male and having experience in the range of 10 to 15 years (both inclusive).
vii. To update the salary of all those doctors whose gender is ‘F’ and who have more than 10
years’ experience by 20% of their actual Basic salary.

Session 2023-24 Page 3 of 10

[Type here]

Q. 3 Consider the following table “SBOP” with details of account holders. Write commands of MySql
for (i) to (v) and output for (vi) to (viii).
Note: Output to be written after execution of queries on computer.
Table : SBOP
Accountno Name Balance Dateofopen Transaction

SB-1 Mr. Anil 15000.00 2011-02-24 7

SB-2 Mr. Amit 23567.89 8

SB-3 Mrs. Sakshi 45000.00 2012-02-04 5

SB-4 Mr. Gopal 23812.35 2013-09-22

SB-5 Mr. Dennis 63459.80 2009-11-10 15

Note: Data not available should be considered as null at that place
i. To display Accountno, Name and Dateofopen of account holder having transactions
more than 8. ii. To display all information of account holders whose transaction
value is not mentioned.
iii. To display the month day with reference to Dateofopen for all the account holders.
iv. To put 0 for Transaction where there is no value is mentioned in that column in the
given table
v. To add another column Address with datatype and size as VARCHAR(25) and show
appear after the column Name.
vi. SELECT count(*) from SBOP;
vii. SELECT Name, Balance from SBOP
WHERE Name like ‘%i”; viii. SELECT
ROUND(Balance,-3) from SBOP WHERE
Accountno = “SB-5”;

Session 2023-24 Page 4 of 10

[Type here]

Q.4

Q.5

Session 2023-24 Page 5 of 10

[Type here]

Q. 6 Consider the following tables Employee and Department. Write SQL commands for
the statement (i) to (vi)
Table: Employee
ENO ENAME JOB SAL COMM DEPTNO
5369 Sunita Clerk 2800 NULL 20
5499 Ashok Salesman 3600 300 30
5521 Rohit Salesman 5250 500 30
5566 Jyoti Manager 4975 NULL 20
5654 Martin Salesman 6250 1400 30
5698 Binod Manager 5850 NULL 30
5782 Anju Manager 2450 NULL 10

Table: Department
DEPTNO DNAME LOC
10 Accounting Delhi
20 Research Kolkata
30 Sales Mumbai
40 Operations Bangalore

i. To create the table ‘Employee’ and ‘Department’ with the referential

integrity among them through the key ‘DEPTNO’.
ii. To insert the above-mentioned records in the respective tables.
iii. To display the Employee number, Employee Name, Job and Location of each
employee.
iv. To display the records of employees who are working as ‘Salesman’ and their
location is ‘Mumbai’
v. To display the EName, Annual Salary with column heading as ‘Annual Salary'
of all the employees working in department number 30.
vi. To display the details of the employees who have salary between 4000 and
6000.

Session 2023-24 Page 6 of 10

[Type here]

Q,7

Session 2023-24 Page 7 of 10

[Type here]

Q.8

Session 2023-24 Page 8 of 10

[Type here]

Q. 9

Session 2023-24 Page 9 of 10

[Type here]

Q. 10

Session 2023-24 Page 10 of 10