Home › Quizzes › SQL for Oracle

SQL for Oracle

Comprehensive SQL quiz covering SELECT statements, DML operations, constraints, views, aliases, GROUP BY, HAVING, and complex queries with subqueries

15 Questions Published
Play Quiz Practise this topic out loud

Questions

Question 1 Multiple Choice (Single Answer)

Triggers are most useful to enforce which type of integrity?

  1. Referential integrity
  2. Entity integrity
  3. Domain integrity
  4. User-defined integrity
  5. None of these
Question 2 Multiple Choice (Single Answer)

Select * from employees where (total_sales-25000) between (select avg(total_sales) from employees) and (select avg(total_sales)*1.2 from employees);
What will be the output of the above query?
Table:
Employees(emp_id,fname,lname,salary,total_sales)

  1. It retrieves the details of employees whose total sales volume is greater than the average sales volume.
  2. It lists those employees whose total sales volume minus 25000 is between the average sales volume and 120 percent of the average.
  3. It lists those employees whose total sales volume minus 25000 is between the average sales and 1.2% of the sales.
  4. It lists those employees whose total sales volume minus 25000 is between the average sales and 1.2% of the average.
  5. None of these
Question 3 Multiple Choice (Single Answer)

Which of the following syntaxes is not correct for insert statement (consider table in description)?
Table: rolno(numeric),fname(varchar),mname(varchar),lname(varchar)

  1. INSERT INTO tableVALUES ('xyz', 'abc');
  2. INSERT INTO table fname, lnameVALUES 'xyz', 'abc';
  3. INSERT INTO table ('fname', 'lname')VALUES ('xyz', 'abc');
  4. INSERT INTO table (fname, lname)VALUES ('xyz', 'abc');
  5. INSERT INTO table (rolno, lname)VALUES (34, abc);
Question 4 Multiple Choice (Single Answer)

What would be the SQL query/queries to list the regno of examinees who have a score greater than the average score?
Table:
Examinee(regno,name,score)
Where regno is primary key.

  1. Select regno from examine where score>avg(score);
  2. Select regno, avg(score) from examine where score>avg(score);
  3. Select regno from examine where score>(select avg(score) from examinee);
  4. All of the above
  5. None of these
Question 5 Multiple Choice (Single Answer)

What will be the SQL query to retrieve the sp and cp of all products whose item (name) is not entered?

Table:Product(id,item,sp,cp)

  1. Select sp,cp from product where item is NULL;
  2. Select sp,cp from product where item = NULL;
  3. Select sp,cp from product where item = 0;
  4. All of the above
  5. None of these
Question 6 Multiple Choice (Single Answer)

Which of the following syntaxes is not correct for column or table aliasing in SQL?

  1. Select id,item,(sp-cp) as profit from product;
  2. Select id,item,(sp-cp) profit from product;
  3. Select id,item,(sp-cp)=profit from product;
  4. Select id,p.item, from product p;
  5. None of these
Question 7 Multiple Choice (Single Answer)

What is the difference between drop and delete?

  1. Both remove columns from table.
  2. Both remove rows from table.
  3. DELETE Statement is used to remove rows in a table while drop removes a column in a table.
  4. Both work in a similar way.
  5. None of these
Question 8 Multiple Choice (Single Answer)

What will be the output of the following query?
select dept from employee e group by dept having (select count() from employee cs_e where e.dept = cs_e.dept and (position like %managers' or position like %supervisor or position like '%president%'))>(select count() from employees cs_e where e.dept = cs_e.dept and (position not like '%manager' and position not like '%supervisor%' and position not like 'president%')) order by dept;
Table:
Employee(emp_id,fname,lname,dept,position)

  1. It will display the name of the departments that have managers, supervisors and president.
  2. It will display the name of the departments that have more employees with job titles such as managers, supervisors or president than not.
  3. It will display the name of the departments that have manager, assistant manager, supervisor, vice president or president.
  4. It will display the name of the departments that have more employees with job titles such as managers, assistant manager, supervisors, vice president or president than not.
  5. None of these
Question 9 Multiple Choice (Single Answer)

Which of the following statements is/are true about having and group by clause?
(a) Group by clause can work alone.
(b) Having clause can work alone.
(c) If group by clause is used, then having clause must be used in a query.
(d) The having clause is used only if group by clause is used in a query.

  1. Only c
  2. a and b
  3. a, b, c and d
  4. a and d
  5. None of these
Question 10 Multiple Choice (Single Answer)

Select distinct name from students, courses, grades where
students.roll_no = grade.roll_no and courses.instructor = Korth and courses.course_no = grades.course_no and grades.grade = A;
Which of the following sets is computed by the above query?
Tables:
Students(roll_no,name,dob)
Courses(course_no,course_name,instructor)
Grades(roll_no,course_no,grade)

  1. Names of students who have got an A grade in all courses.
  2. Names of students who have got an A grade in all courses taught by Korth.
  3. Names of students who have got an A grade in at least one of the courses taught by Korth.
  4. Names of students who have got an A grade in at least one of the courses.
  5. None of these
Question 11 Multiple Choice (Single Answer)

Which of the following is not true about the limitations of view?

  1. If you use the DISTINCT clause to create a view, you cannot update or insert records within that view.
  2. If you do insert or update records through a join view, all records that are updated must belong to the same physical table.
  3. Virtual columns can be updated.
  4. You cannot use DELETE statements on multiple table views.
  5. None of these
Question 12 Multiple Choice (Single Answer)

Which constraint is used to ensure that all the values in the column are different?

  1. NOT NULL constraint
  2. DEFAULT constraint
  3. PRIMARY key
  4. UNIQUE constraint
  5. FOREIGN key
Question 13 Multiple Choice (Single Answer)

What will be the output of the following SQL statement?
Select * from guest where guestno = (select guestno from booking where datefrom< = current_date and dateto> = current_date and hotelno = (select hotelno from hotel where hname = 'XYZ'));

Tables:
Guest(guestno,gname,gadd)
Hotel(hotelno,hname,city)
Booking(hotelno,guestno,datefrom,datto,roomno)
Room(roomno,hotelno,type,price)

  1. Guest details who are staying in XYZ hotel from the specific date to the current date.
  2. Guest details who are staying in XYZ hotel as on date.
  3. Guest details who are staying in XYZ hotel.
  4. Guest details who are not staying in XYZ hotel as on date.
  5. None of these
Question 14 Multiple Choice (Single Answer)

Which of the following statements is/are true?
(a) The where clause searches the rows after they are grouped by using 'group by' clause.
(b) The 'group by' clause collects rows that meet the where clause search conditions and places those rows into a group for each unique value in the ' group by' clause.
(c) Omitting the 'group by' clause creates a single group for the whole table.
Table1
NAME

ABLE
BAKER
CHARLIE
DEAN

Table2
NAME

ABLE
BAKER
BRAVO
CHARLIE
DECON

  1. a and b
  2. b and c
  3. a and c
  4. All of these
  5. Only b
Question 15 Multiple Choice (Single Answer)

Update inventory set price = price*.75 where not exists (select * from invoice_details i where inventory.item_no = i.item_number and inv_no in (select inv_no from invoices where inv_date> = getdate()-30));
The purpose of the above given query is
Tables:
Invoice_details(item,number,price,invoice_dat,inv_no)
Inventory(item_no,price,inv_date,....)
Invoices(inv_no,inv_date,....)

  1. to discount only those things that have sales since the inv_date
  2. to discount only those items that have had no sales within the last 30 months
  3. to gives 25% discount to only those items that have had sales within the past 30 days
  4. to discount only those items that have had no sales within the past 30 days
  5. None of these
← Back to Quizzes