1. Which are not DCL operations?
Answers:
• Insert
• Grant
• Delete
• Update
• Revoke
• Commit
• Rollback
2. Which of the following is not a DML command?
Answers:
• Set transaction
• Explain plan
• Update
• Grant
• Insert
• Create
• Alter
• Revoke
3. Which of the following is not a DDL command?
Answers:
• Drop
• Create
• Alter
• Revoke
• Explain plan
• Insert
• Update
• Delete
4. Which of the following statements are not true regarding the primary key?
Answers:
• There can only be one primary key in a table
• An index is automatically generated upon creation of a primary key
• A primary key can accept null values
• A primary key can be composite
• A primary key constraint can be either at the column level or at the table level
• There can be more than one primary key in a table
5. Which of the following statements are wrong about primary keys?
Answers:
• The primary key field must contain unique values
• The primary key field can contain null values
• A primary key is the column or set of columns that makes every row in the table unique
• A primary key can be created with a maximum of three columns
• Each table can have more than one primary keys
• Unique key is another name for primary key
6. Which statements are true for views?
Answers:
• The definition of a view is stored in data dictionary
• Views provide a more secure way of retrieving data
• Views are actually Tables and store data in the same manner as Tables
7. Examine the data in the EMPLOYEES table given below:
LAST_NAME DEPARTMENT_ID SALARY
ALLEN 10 3000
MILLER 20 1500
KING 20 2200
DAVIS 30 5000
Which of the following Subqueries work?
Answers:
• SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department_id);
• SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);
• SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);
• SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);
• SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY));
8. Select all the appropriate options.
Answers:
• A table is a multiset of rows
• A table is a two-dimensional array of rows and columns
• A table is always dependent on other tables
• A third normal form table is a table free of redundant data
• A table must have a primary key
9. Which of the following field names are correct?
Answers:
• EmpNo
• 25Block
• #AccountID
• _CustomerName
• Product.Name
10. Which component of an RDBMS validates the syntax of the user's query?
Answers:
• Query Parser
• The Database Manager
• Query Optimization
• Database Administrator
11. What does the term DDL stand for?
Answers:
• Data Description Language
• Dynamic Data Language
• Data Definition Language
• Data Derived Language
• Descriptive Data Language
12. Which of the following can be used to uniquely identify a row?
Answers:
• Primary Key
• Unique Key
• Foreign Key
• All of the above
13. ________ is an operation that displays rows which meet a condition.
Answers:
• Restriction
• Extraction
• Projection
• Intersection
• Union
• Minus
• None of the above
14. A table Students has a column called name which stores the names of the students. What will be the correct query to display the names of the students in reverse order?
Answers:
• Select name from students reverse;
• Select name from students reverse name;
• Select name from students order by name descending;
• Select name from students order by name reverse;
• Select name from students order by name desc;
• Select desc name from students;
• Select reverse name from students;
15. What items, other than column names can be included in the select clause?
Answers:
• Arithmetic expressions
• Column aliases
• Concatenated columns
• None of the above
16. Which of the following statements is true?
(a)The Insert statement creates new rows
(b)The Update statement modifies the table structure
Answers:
• only (a) is true
• only (b) is true
• both (a) and (b) are true
• both (a) and (b) are false
17. Which operator will be evaluated first in the following statement:
select (age + 3 * 4 / 2 - 8) from emp
Answers:
• +
• -
• /
• *
18. What are the columns of a table called in a relational model?
Answers:
• Attributes
• Rows
• Tuples
• Constraints
• Keys
• Indexes
• Sets
• Elements
19. The level of data abstraction which describes how the data is actually stored is?
Answers:
• Physical level
• Conceptual level
• Storage level
• File level
20. Which of the following is not a set operator?
Answers:
• Union
• Union all
• Intersect
• Minus
• Minus all
21. 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?
Answers:
• 1
• 2
• 3
• 4
• 5
22. For which SQL operation is Alter Table used?
Answers:
• To add a column
• To add an integrity constraint
• To modify storage characteristics
• To enable/disable or drop an integrity constraint
• all of the above
23. < and > are examples of _________ type of operators.
Answers:
• Logical
• Arithmetic
• Assignment
• Ternary
• Relational
• Numeric
• Comparison
• None of the above
24. Examine the two SQL statements given below:
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC
What is true about them?
Answers:
• The two statements produce identical results
• The second statement returns an error
• There is no need to specify DESC because the results are sorted in descending order by default
25. What does MOD() function do?
Answers:
• Returns the remainder after division
• Modifies the column definition
• Modifies the definition of a table
• None of the above
26. The names of those departments where there are more than 100 employees have to be displayed. Given two relations, employees and departments, what query should be used?
Employee
---------
Empno
Employeename
Salary
Deptno
Department
---------
Deptno
Departname
Answers:
• Select departname from department where deptno in (select deptno from employee group by deptno having count(*) > 100);
• Select departname from department where deptno in (select count(*) from employee group by deptno where count(*) > 100);
• Select departname from department where count(deptno) > 100;
• Select departname from department where deptno in (select count(*) from employee where count(*) > 100);
27. An association of several entities in a Entity-Relation model is called?
Answers:
• Tuple
• Record
• Relationship
• Field
28. Which of the following statements regarding views are incorrect?
Answers:
• A view is like a window through which data on tables can be viewed or changed
• A view is derived from another table
• A view cannot be derived from another view
• A view is stored as a select statement only
• A view has no data of its own
• A view is another name for a table
29. Consider the following tables:
Books
------
BookId
BookName
AuthorId
SubjectId
PopularityRating (the popularity of the book on a scale of 1 to 10)
Language (such as French, English, German etc)
Subjects
---------
SubjectId
Subject (such as History, Geography, Mathematics etc)
Authors
--------
AuthorId
AuthorName
Country
What is the query to determine the names of the Authors who have written more than 1 book?
Answers:
• select AuthorName from Authors where AuthorId in (select AuthorId from Books group by AuthorId having count(*)>1)
• select AuthorName from Authors, Books where Authors.AuthorId=Books.AuthorId and count(BookId)>1
• select AuthorName from Authors, Books where Authors.AuthorId=Books.AuthorId group by AuthorName having count(*)>1
• select AuthorName from Authors where AuthorId in (select AuthorId from Books having count(BookId)>1)
30. When a table is dropped using a simple DROP statement, SQL performs some more operations simultaneously, select all the valid operations?
Answers:
• Removes all rows from the table
• Drops all the table's indexes
• Removes all dependent views
• Removes all dependent procedures
31. Which of the following statement is correct regarding table creation?
Answers:
• Tables once created cannot be modified to add columns
• Constraints can only be given while table creation
• One can easily create a table from a given table
• When a table is created from another table, all the constraints are copied as well
• The width of the columns cannot be modified
• Columns cannot be removed from a table
32. A production house has two sales outlets. Both outlets are maintaining their data separately in schemas A and B respectively. The Management wants to see the sale of both outlets in one report. Both outlets are using tables called Sales which have identical structure. Which method you will adopt to create the report?
Answers:
• Select * from A.Sales join B.Sales
• Select * from A.Sales union all B.Sales
• Select * from A.Sales, B.Sales
• None of the above
33. What are the programs that execute automatically whenever DML operations are performed on tables called?
Answers:
• Triggers
• Procedures
• Functions
• None of the above
34. Which of the following is not a type of constraint?
Answers:
• Primary key
• Unique
• Check
• Distinct
• Default
35. Consider the query:
SELECT name
FROM Student
WHERE name LIKE '_a%';
Which names will be displayed?
Answers:
• Names starting with "a"
• Names containing "a" as the second letter
• Names starting with "a" or "A"
• Names containing "a" as any letter except the first
36. A production house needs a report about the sale where total sale of the day is more than $20,000. Which query should be used?
Answers:
• select * from orders where sum(amount) > 20000
• select orderdate, sum(amount) from orders where sum(amount) > 20000 order by OrderDate
• select orderdate, sum(amount) from orders group by orderdate having sum(amount) > 20000
• select orderdate, sum(amount) from orders group by OrderDate where sum(amount) > 20000
37. Which character function should be used to return a specified portion of a character string?
Answers:
• CONCAT
• LENGTH
• SUBSTR
• INITCAP
38. What will happen if you query the emp table as shown below:
select empno, DISTINCT ename, Salary from emp;
Answers:
• EMPNO, unique value of ENAME and then SALARY are displayed
• EMPNO, unique value ENAME and unique value of SALARY are displayed
• DISTINCT is not a valid keyword in SQL
• No values will be displayed because the statement will return an error
39. What is a rollback of transactions normally used for?
Answers:
• Recover from the transaction failure
• Update the transaction
• Retrieve old records
• None of the above
40. The STUDENT_GRADES table has these 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?
Answers:
• SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL
• SELECT (gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL
• SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end
• SELECT MAX(gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student_grades
• SELECT MAX(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL
41. Which of the following is not a single value function?
Answers:
• Round
• Floor
• Avg
• Sqrt
• Tan
42. An RDBMS performs the following steps:
1)It calculates the results of the group functions of each group
2)It groups those rows together based on the group by clause
3)It orders the groups based on the results of the group functions in the order by clause
4)It chooses and eliminates groups based on the having clause
5)It chooses rows based on the where clause
Arrange the above steps in the correct order of execution:
Answers:
• 4,3,5,1,2
• 4,5,3,2,1
• 5,2,1,4,3
• 5,2,3,4,1
• 2,3,1,4,5
• 2,3,1,5,4
• 1,2,3,4,5
• 3,2,1,4,5
43. Data validation can be implemented at the data definition stage through:
Answers:
• Check constraints with specified values
• Referential constraints, by creating foreign keys for another table
• Default value of column
• Not Null constraint
44. What clause should be used to display the rows of a table in ascending order of a particular column?
Answers:
• Where
• Order By
• Group By
• Having
• First Group By and then Having
• Like
• Between
45. What is the error in the following query if the Students table contains several records?
select name from students where name =
(select name from students order by name);
Answers:
• = should be replaced by in operator
• Order by clause in the subquery should be preceded with a group by clause
• Order by clause in the subquery can be used only if the where and group by clauses have been applied
• Group by clause should be applied to the outer query
• An order by clause is not allowed in a subquery
• There is no error
46. In which type of database is SQL used?
Answers:
• Hierarchical
• Network
• Relational
• Object oriented
• All of above
47. The concept of data independence is similar to the concept of ________
Answers:
• Data type
• Abstract data type
• Consolidation
• Isolation
48. Examine the description of the STUDENTS table:
STD_ID NUMBER (4)
COURSE_ID VARCHAR2 (10)
START_DATE DATE
END_DATE DATE
The aggregate functions valid on the START_DATE column are:
Answers:
• SUM(start_date)
• AVG(start_date)
• COUNT(start_date)
• AVG(start_date, end_date)
• MIN(start_date)
49. Evaluate the following SQL statement:
SELECT e.employee_id, (.15* e.salary) + (.5 * e.commission_pct) + (s.sales_amount * (.35 * e.bonus)) AS CALC_VALUE FROM employees e, sales s WHERE e.employee_id = s.emp_id;
What will happen if all the parentheses are removed from the calculation?
Answers:
• The value displayed in the CALC_VALUE column will be lower
• The value displayed in the CALC_VALUE column will be higher
• There will be no difference in the value displayed in the CALC_VALUE column
• An error will be reported
50. Examine the query:-
select (2/2/4) from tab1;
where tab1 is a table with one row. This would give a result of:
Answers:
• 4
• 2
• 1
• .5
• .25
• 0
• 8
• 24
No comments:
Post a Comment