InfyTQ Subquery Quiz 1

Question 1

Time: 00:00:00
Consider the table gift given below:
Table: gift























































giftid giftname Price discount availability
G101 Magic Mug 500 9 10
G102 Golfset 3550 5 15
G103 Little Astronomer 2000 20 18
G104 Paintings 1500 15 15
G105 French Gastronomy 3000 7 10
G106 Magic Set 1300 30 30


Query:
SELECT g1.giftid, g1.giftname FROM gift g1 JOIN gift g2
ON g1.availability = g2.availability AND g1.discount <> g2.discount
WHERE g1.discount>7

How many rows will be fetched when the above query gets executed?

4

4

2

2

3

3

Question 2

Time: 00:00:00
From the above Table:

Select max(G1.price) from gift G1 WHERE 2=( SELECT COUNT (DISTINCT price) FROM 

Gift G2 where G1.price <  G2.price) 

What will be the output of the Query:

3550

3550

2000

2000

1500

1500

3000

3000

Question 3

Time: 00:00:00























































Instructorid name subject University Salary
1201 Alex Java Harvard 70000
1202 Sam Ruby Oxford 7500
1201 Alex Rdbms Harvard 60000
1203 Mitchel Networking Cambridge 50000
1202 Sam Rdbms Harvard 40000
1203 Mitchel .Net Oxford 50000


How many number of rows will be in output after executing the below query?
Query:
SELECT instructorid, name FROM instructor WHERE salary > 40000
GROUP BY instructorid, name HAVING COUNT (DISTINCT university) > 1;

4

4

3

3

2

2

No rows will be displayed

No rows will be displayed

Question 4

Time: 00:00:00
From The Above Table
SELECT ins.name from instructor ins join instructor ins1 on ins.instructorid=ins1.instructorid where ins1.university =ins.university
How many rows will be displayed by above Query ?

9

9

6

6

8

8

11

11

Question 5

Time: 00:00:00
The given Query can also be replaced with_______:
SELECT name, course_id
FROM instructor, teaches
WHERE instructor_ID= teaches_ID;

Select name,course_id from teaches,instructor where instructor_id=course_id;

Select name,course_id from teaches,instructor where instructor_id=course_id;

Select name, course_id from instructor natural join teaches;

Select name, course_id from instructor natural join teaches;

Select name, course_id from instructor;

Select name, course_id from instructor;

Select course_id from instructor join teaches;

Select course_id from instructor join teaches;

Question 6

Time: 00:00:00
The relationship between DEPARTMENT and EMPLOYEE is a

One-to-one relationship

One-to-one relationship

One-to-many relationship

One-to-many relationship

Many-to-many relationship

Many-to-many relationship

Many-to-one relationship

Many-to-one relationship

Question 7

Time: 00:00:00
Ready the Query carefully:
SELECT emp_name
FROM department
WHERE dept_name LIKE ' _____ Computer Science';
In the above-given Query, which of the following can be placed in the Query's blank portion to select the "dept_name" that also contains Computer Science as its ending string?

&

&

_

_

%

%

$

$

Question 8

Time: 00:00:00
This set of Database Multiple Choice Questions & Answers (MCQs) focuses on “Aggregate Functions and Nested Subqueries – 1”.
1. Aggregate functions are functions that take a ___________ as input and return a single value.

Collection of values

Collection of values

Single value

Single value

Aggregate value

Aggregate value

Both Collection of values & Single value

Both Collection of values & Single value

Question 9

Time: 00:00:00
SELECT __________

FROM instructor

WHERE dept name= ’Comp. Sci.’;

Which of the following should be used to find the mean of the salary ?

Mean(salary)

Mean(salary)

 Avg(salary)

 Avg(salary)

Sum(salary)

Sum(salary)

Count(salary)

Count(salary)

Question 10

Time: 00:00:00
SELECT COUNT (____ ID)

FROM teaches

WHERE semester = ’Spring’ AND YEAR = 2010;

If we do want to eliminate duplicates, we use the keyword ______in the aggregate expression.

Distinct

Distinct

Count

Count

Avg

Avg

Primary Key

Primary Key

null
null
×

Please login to report