3
To list the names of patients who are in ENT and surgery department and charges
are less than 300
(1 Point)
both (a) and (c)
both (a) and (b)
All of above
(b) select Name from hospitaltbl where (Department=’ENT’ or
Department=’Surgery’) and (Charges<300)
both (b) and (c)
(d) select Name from hospitaltbl where Department like ('surgery', 'ENT') and
(Charges<300)
(a) select Name from hospitaltbl where Department=’ENT’
or Department=’Surgery’ and Charges<300
(c) select Name from hospitaltbl where Department in ('surgery', 'ENT') and
(Charges>300)
4
Note: Here is the dataset (Employee) that contains the record Employee ID, Name,
Department, Education, and their salary in Lacs.
Find the department in which SUM salary is greater than or equal to 20 lacs, but the
education of employees is not UG.
(1 Point)
(a) SELECT Department, SUM(Salary) FROM Student WHERE Education <>
‘UG’ GROUP BY Department HAVING SUM(Salary) >= 20;
(b) SELECT Department, SUM(Salary) FROM Student WHERE Education != ‘UG’
GROUP BY Department HAVING SUM(Salary) >= 20;
Both (a) and (b)
none of these
5
Choose the correct option to show all information about the patients except
cardiology department.
(1 Point)
both (b) and (c)
(a) select * from hospital where Department<>’Cardiology’
Both (a) and (b)
(b) select * from hospital where Department==’Cardiology’
All of the above
None of the above
both (a) and (c)
(c) select * from hospital where Department!=’Cardiology’
6
Query to create emptbl table having column (EmpID, EmpName, DOJ,
Department, ContactDetails, EmailID, Location). Choose the most relevant
query for the emptbl. Location having 4 places to work and by default WFH.
(1 Point)
create table emptbl(EmpID int primary key identity(1,1) ,EmpName varchar(20)
not null, DOJ date not null, contactDetails varchar(10),constraint ck_contactdetails
check((len(contactDetail)=10) andcontactDetails not like '%[^0-9]%'), EmailID
varchar(20) constraint uk_email_unique unique, Location varchar(20)constraint
ck_location check(Location in
('Vadodara','Anand','Nadiad','Bharuch,'WFH''))constraint df_location default ('WFH'));
create table emptbl (EmpID int primary key, EmpName varchar(20) not null, DOJ
date not null, contactDetails varchar(10),constraint ck_contactdetails
check((len(contactDetail)=10) andcontactDetail not like '%[^0-9]%'), EmailID
varchar(20) constraint uk_email_unique unique, Location varchar(20)constraint
ck_location check(Location in ('Vadodara','Anand','Nadiad','Bharuch,'WFH''));
create table emptbl(EmpID int primary key identity(1,1) ,EmpName varchar(20)
not null, DOJ date not null, contactDetails varchar(10), constraint ck_contactdetails
check((len(contactDetail)=10) andcontactDetails like '%[0-9]%'), EmailID varchar(20)
constraint uk_email_unique unique, Location varchar(20) constraint ck_location
check(Location in ('Vadodara','Anand','Nadiad','Bharuch,'WFH''));
create table emptbl(EmpID int primary key, EmpName varchar(20) not null, DOJ
date not null, contactDetails varchar(10),constraint ck_contactdetails
check((len(contactDetail)=10) andcontactDetail not like '%[^0-9]%'), EmailID
varchar(20) constraint uk_email_unique unique, Location varchar(20)constraint
ck_location check(Location in
('Vadodara','Anand','Nadiad','Bharuch,'WFH''))constraint df_location default ('WFH'));
7
How can you change "Thomas" into "Michel" in the "LastName" column in the Users
table?
(1 Point)
MODIFY Users SET LastName = 'Thomas' INTO LastName = 'Michel'
UPDATE Users SET LastName = 'Thomas' INTO LastName = 'Michel'
MODIFY Users SET LastName = 'Michel' WHERE LastName = 'Thomas'
UPDATE Users SET LastName = 'Michel' WHERE LastName = 'Thomas'
8
Query to change the name of the database having name from RISE to RISE2024
(1 Point)
All (a), (b), (c)
(b) sp_renameDB 'RISE','RISE2024'
(c) sp_rename 'RISE','RISE2024'
(d) alter database RISE modify name = 'RISE2024'
(a) alter database RISE modify name = RISE2024
Both (b) and (c)
Both (c) and (d)
Both (a) and (b)
9
Query to find salespeople who received commission of more than 12% from the
company.
(1 Point)
option (a), (b), (c) are correct
(a) select c.cust_name, [Link], [Link], [Link] from customer as c join
salesman as s on c.salesman_id=s.salesman_id where [Link]>0.12
(d) select c.cust_name, [Link], [Link], [Link] from customer as c join
salesman as s on c.salesman_id=s.salesman_id where [Link]>(0.12/100)
(b) select c.cust_name, [Link], [Link], [Link] from customer as c join
salesman as s on c.salesman_id=s.salesman_id where [Link]>12
All option are correct
(c) select c.cust_name, [Link], [Link], [Link] from customer as c join
salesman as s on c.salesman_id=s.salesman_id where [Link]>(12/100)
both (a) and (c)
option (b), (c), (d) are correct
both (a) and (b)
10
To show all the records for number of students more than the capacity in order of
Route No.
(1 Point)
All (a), (b) and (c) are correct
both (a) and (c)
(a) select * from [School bus] where [no of students] < Capacity orderby [Route
No]
(c) select * from [School bus] where Capacity <[no of students] order by [Route
No]
both (a) and (b)
(b) select * from [School bus] where [no of students] > Capacity orderby [Route
No]
both (b) and (c)
11
Find the cities name with the condition and temperature from table 'weather' where
condition = sunny or cloudy but temperature >= 60.
(1 Point)
SELECT city, temperature, condition FROM weather WHERE condition = 'cloudy'
AND condition = 'sunny' OR temperature >= 60
SELECT city, temperature, condition FROM weather WHERE condition = 'cloudy'
OR condition = 'sunny' OR temperature >= 60
SELECT city, temperature, condition FROM weather WHERE condition = 'sunny'
OR condition = 'cloudy' AND temperature >= 60
SELECT city, temperature, condition FROM weather WHERE condition = 'sunny'
AND condition = 'cloudy' AND temperature >= 60
12
Display the number of students getting grade A w.r.t Game and study not in 12 Class
(1 Point)
Select Game, count (name) from Studenttbl where Class = 12 , Grade='A' group
by Game
Select Game, count(name) from Studenttbl where Class = 12 and Grade='A'
group by Game
Select Game, count (name) from Studenttbl where Class = 11 and Grade='A'
group by Game
Select Game, count (name) from Studenttbl where Class = 12 or Grade='A' group
by Game
13
Query for modifying table set null on delete and cascading for update in the <parent
table>
(1 Point)
Alter table <child table> add constraint <constraint name> foreign key <child
table> (column) references <parent table> (column) on delete set null on update
cascade
Alter table <child table> add constraint <constraint name> foreign key <child
table> (column) references <parent table> (column) on delete cascade on update
cascade
Alter table <child table> add constraint <constraint name> foreign key <child
table> (column) references <parent table> (column) on delete set null on update
set null
Alter table <child table> add constraint <constraint name> foreign key <child
table> (column) references <parent table> (column) on delete cascade on update
set null
14
Display the count of female patient, max charge, min charges and avg charges with
age >50 for female patient
(1 Point)
Select Count(name) as Total_Patient, max(charges) as max_charges,
min(charges) as min_charges, average(charges) from hospitaltbl where age>50 and
sex='female'
Select Count(name) as Total_Patient, max(charges) as max_charges,
min(charges) as min_charges, avg(charges) from hospitaltbl where age>50 and
sex='female'
Select Count(name) as Total_Patient, max(charges) as max_charges,
min(charges) as min_charges, average(charges) from hospitaltbl where age>50 or
sex='female'
Select Count(name) as Total_Patient, maximum(charges) as max_charges,
minimum(charges) as min_charges, average(charges) from hospitaltbl where
age>50 and sex='female'
15
syntax to add foreign key constraint
(1 Point)
alter table <parent table> add constraint <constraint name> foreign key <child
table> (column) references <child table> (column)
alter table <child table> add constraint <constraint name> foreign key <child
table> (column) references <parent table> (column)
alter table <parent table> add constraint <constraint name> foreign key
<parent table> (column) references <child table> (column)
alter table <child table> add constraint <constraint name> foreign key <child
table> (column) references <child table> (column)
16
Query to drop a primary constraint from the emptbl
(1 Point)
alter table emptbl drop constraint <primary key constraint name>
alter table drop constraint <primary key constraint name>
alter table emptbl drop constraint primary key
alter emptbl drop constraint primary key
17
Display count of male patient name having admission in the month of february,1998
and having age in the range of 24 to 48.
(1 Point)
select count (*) as Total_male from hospitaltbl where sex='M' or (date of
admission between 01-02-2024 to 28-02-2024) and (age between 24 to 48)
select count (*) as Total_male from hospitaltbl where sex='M' and (date of
admission between 01-02-2024 to 28-02-2024) and (age between 24 and 48)
select count (*) as Total_male from hospitaltbl where sex='M' and (date of
admission between 01-02-2024 and 28-02-2024) or (age between 24 and 48)
select count (*) as Total_male from hospitaltbl where sex='M' and (date of
admission between 01-02-2024 and 28-02-2024) and (age between 24 and 48)
18
syntax to rollback access to single user
(1 Point)
ALTER <DATABASE NAME>SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE <DATABASE NAME>SET SINGLE_USER WITH ROLLBACK
IMMEDIATE;
ALTER DATABASE SET SINGLE_USER WITH COMMIT IMMEDIATE;
ALTER <DATABASE NAME>SET SINGLE_USER WITH COMMIT IMMEDIATE;
19
Write a query to find out unique department in the hospitaltbl
(1 Point)
both (a) and (b)
All of above
(c) select distinct(department) from hospitaltbl
None of the above
(a) Select Department(distinct) from hospitaltbl
Only (a)
(b) select * from department having distinct(department)
20
Query to list in ascending order of salespersons who work either for one or more
customers or have not yet joined any of the customers
(1 Point)
both (b) and (d)
(a) select [Link], c.cust_name from salesman as s left join customer as c on
s.salesman_id=c.salesman_id order by s.salesman_id desc
(d) select [Link], c.cust_name from salesman as s left join customer as c on
s.salesman_id=c.salesman_id order by s.salesman_id asc
(b) select [Link], c.cust_name from salesman as s left join customer as c on
s.salesman_id=c.salesman_id order by s.salesman_id
both (a) and (b)
(c) select [Link], c.cust_name from salesman as s join customer as c on
s.salesman_id=c.salesman_id
none of option
all are correct
21
Write a query to change datatype of salary from money to integer in emptbl
(1 Point)
alter table emptbl alter salary int
alter table emptbl alter salary int(10)
alter table emptbl alter column salary int(10)
alter table emptbl alter column salary int
22
To show Route No , area covered and average cost per student for all routes.
(1 Point)
none of above
All of above
Select [Route No], area covered , ‘average cost=’ , charges/[No of students] from
[School bus]
Select [Route No], area covered, avg(charges)/[No of students] as [average cost]
from [School bus]
Select [Route No], area covered, [average cost] = avg(charges)/[No of students]
from [School bus]