SQL Test Answer Part-06



51. Are both the statements correct?
(a) where deptno in(2,4,5)
(b) where deptno=2 or deptno=4 or deptno =5
a. True
b. False
Answer: a. true
52. What is wrong with the following query?
Select* from orders where OrderID=(select OrderID from OrderItems where ItemQty > 50)
a. In the sub query, ‘*’ should be used instead of ‘OrderID’
b. The sub query can return more than one row, so, ‘=’ should be replaced with ‘in’
c. The sub query should be parenthesis
d. None of the above
Answer: b
53. Which of the following queries valid?
  1. Select * from students where marks > avg(marks);
  2. Select * from students order by marks where subject =’SQL’;
  3. Select * from students having subject =’SQL’;
  4. Select name from students group by subject, name;
  5. Select name, avg(marks) from students;
  6. Select group(*) from students;
  7. None of the above
Answer: d.
54. What will happen if the emp table shown below is queried:
Select enpno, DISTINCT ename, Salary from emp;
  1. EMPNO ,unique value of ENAME and then SALARY are displayed
  2. EMPNO ,unique value of the two columns, ENAME and salary are displayed
  3. DISTINCT is not a valid keyword in SQL
  4. No values will be displayed because the statement will return an error
Answer: d.
55. What does the following update statement do?
Update OrderTable set OrderDiscount=OrderDiscount*1.10
  1. Increases OrderDiscount of first row by 10%
  2. Increases OrderDiscount of all rows by 10%
  3. Does nothing without where clause
  4. Gives an error due to lack of where clause
Answer: b.
56. View the following create statement:
1. Create table Pers
2. (EmpNo Number(4) not null,
3. EName Char not null,
4. Join_dt Date not null,
5. Pay Number)
Which line contains an error?
a. 1
b. 2
c. 3
d. 4
e. 5
Answer: 2.
57. Which one of the following correctly selects rows from the table myTable that have null in column column1?
a. SELECT*FROM myTable WHERE column1 is null
b. SELECT*FROM myTable WHERE column1 = null
c. SELECT*FROM myTable WHERE column1 EQUALS null
d. SELECT*FROM myTable WHERE column1 NOT null
e. SELECT*FROM myTable WHERE column1 CONTAINS null
Answer: b.
58. A company has the following departments:
Marketing, Designing, Production, Packing
What will be the result of the following query?
Select * from the table where department < ‘Marketing’
  1. The query will return “Designing, Packing”
  2. The query will return “Designing, Production, Packing”
  3. The query will return “Packing”
  4. Strings cannot be compared using <>
  5. The query will return “Designing”
Answer: e.
59. Consider the following two tables:
1. customers(customer_id, customer_name)
2. branch(branch_id, branch_name)
What will be the output if the following query is executed;
Select *, branch_name from customers, branch
  1. It will return the fields customer_id, customer_name, branch_name
  2. It will return the fields customer_id, customer_name, branch_id, branch_name
  3. It will return the fields customer_id, customer_name, branch_id, branch_name, branch_name
  4. It will return an empty set since the two tables do not have any common field name
  5. It will return an error since * is used alone for one table only
Answer: c.
60. The STUDENT_GRADES table has the following columns: 
STUDENT_ID
NUMBER (12)
SEMESTER_END
DATE
GPA
NUMBER (4)
Which of the following statements finds the highest Grade Point Average (GPA) per semester?
  1. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL;
  2. SELECT (gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;
  3. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end;
  4. SELECT MAX(gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student_grades;
  5. SELECT MAX(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;
Answer: c.