Freelancer Point

Which are not DCL operations

1. Which are not DCL operations?

Answers:

• Insert

• Grant

• Delete

• Update

• Revoke

• Commit

• Rollback

oDesk Test answers for System Analysis Test 2015

Blogger 22:18 Add Comment Edit

56 NOT Answered Yet Test Questions:

(hold on, will be updated soon)

1. PERT stands for:

Answers:

• Program Error Reduction Technique

• Project Evaluation and Review Technique

• Program Evaluation and Review Technique

• Program Execution and Review Technique

• Project Execution and Review Technique

2. A Data Dictionary:

Answers:

• Is created in the Conception phase

• Defines the data types

• Identifies the external entities of the system

• Explains the coding logic

• Defines all the data elements and structures in the DFD

3. CASE stands for:

Answers:

• Computer Aided Software Engineering

• Computer and Software Expert

• Computer and Software Education

• Computer and Systems Engineering

• Computer Aided Software Education

4. Cohesion refers to:

Answers:

• Design of system as a hierarchy of modules

• The strength of relations between modules

• Strength of relations within a module

• Number of instructions composing a module

• Number of modules subordinate to calling module

5. Which tool is an effective means of relating input data to files and output reports?

Answers:

• Grid chart

• Pie chart

• Gantt chart

• Hierarchy chart

• Structure chart

6. Which of the following symbols represents a process in a DFD?

Answers:

• Square

• Rectangle

• Rhombus

• Circle

• Line

7. Which tool shows the processes and the sequence in which they are performed?

Answers:

• Hierarchy Chart

• Warnier Orr Diagrams

• VTOC

• Structure Chart

• Gantt Chart

8. Which phase of the Systems Development is concerned with determining the system feasibility?

Answers:

• Conception

• Construction

• Design

• Analysis

• Initiation

9. To achieve a good response time from OLTP, critical requirements are:

Answers:

• Efficient end users

• Faster disk access and efficient programming

• Faster disk access and efficient I/O controller

• Efficient end users, faster disk access and efficient I/O controller

• Faster disk access, efficient I/O controller and efficient programming

10. Identify the correct sequence of the various types of testing:

Answers:

• Module, Unit and System

• Module, System and Unit

• Unit, Module and System

• System, Module and Unit

• Unit, System and Module

11. The graphical tool for describing decision logic in a data dictionary is:

Answers:

• Gantt Chart

• CAD

• DFD

• Decision Tree

• Structured English

12. Which design principle relates to the strength of relations within a module?

Answers:

• Coupling

• Cohesion

• Modularity

• Shared use

• Span of control

13. A DFD is:

Answers:

• Generated in the Design phase

• Generated in the Construction phase

• The modern version of a Flow Chart

• Not helpful in explaining the system to the end user

• Mainly used at the system specification stage

14. A top level DFD is generally referred to as a:

Answers:

• Context Analysis Diagram

• Structure Chart

• Data Dictionary

• Data Structure Diagram

• Hierarchy Chart

15. You want to describe the data flow in a system. Which tool would you use?

Answers:

• Structure Chart

• Data Chart

• Hierarchy Chart

• Flowchart

• Data Flow Diagram

16. Data Dictionary keeps details of:

Answers:

• External entities

• Data stores and processes

• Data flows, external entities and data stores

• External entities and data flows

• Data flows, data stores, processes and external entities

17. Which tool shows the relation between each of the documents making up a HIPO package?

Answers:

• Warnierr Orr Diagram

• Hierarchy Chart

• Structure Chart

• VTOC

• ERD

18. Which tool shows the hierarchy of the various modules in a system?

Answers:

• Warnier Orr Diagram

• PERT

• HIPO

• Hierarchy Chart

• ERD

19. HIPO:

Answers:

• Is used to describe the process logic

• Defines the relationship between the various entities

• Describes the flow of data through a system

• Stands for Hierarchical Input Programming and Output

• Is a commonly used method for developing systems software

20. Given the following scenario, what is the number of conditions that will be used to draw a decision table?

If the volume of sales is greater than 10,000 units and advance collected is 50% or more, then the commission is 16%. If the advance collected is less than 50%, then it is 14%. For sales of 10,000 units, irrespective of the advance collected, commission is 10%. For sales less than 10,000 units, the commission is 9% or 8% based on whether the advance collected is 50% or more, or less than 50% respectively.

Answers:

• 1

• 2

• 3

• 4

• 5

21. The user manual is generated in which phase of the System Development cycle?

Answers:

• Conception

• Construction

• Design

• Analysis

• Initiation

22. In which phase of the System Development cycle is the Project Request form generated?

Answers:

• Conception

• Construction

• Design

• Analysis

• Initiation

23. The smallest unit of data in a Data Dictionary is referred to:

Answers:

• Data structure

• Data store

• Data element

• Data flow

• Database

24. In a Data Dictionary, process logic is described using:

Answers:

• Flowcharts and Structured English

• Decision Trees and Decision Tables

• Flowcharts and Decision Trees

• Decision Trees, Decision Tables and Structured English

• Decision Trees, Decision Tables and Flowcharts

25. A tabular representation of the Decision Tree is known as a:

Answers:

• Table

• Conditional table

• Check table

• Decision table

• Tree Table

26. You want to plan your project. Which tool would you use?

Answers:

• Gantt Chart

• Structure Chart

• Hierarchy Chart

• Flowchart

• Warnier Orr Diagram

27. Which of the following tools would you use for project planning?

Answers:

• Structure Chart

• Data Flow Diagram

• PERT

• HIPO

• ERD

28. Which of the following is not an important reason for creating a Data Dictionary?

Answers:

• To manage the detail in larger systems

• To document the features of the system

• To locate errors and omissions in the system

• To communicate a common meaning for all system elements

• To help in reviewing the progress of the project

29. The document of functional specifications will be used by:

Answers:

• The Management

• The Analysis team

• The Design team

• The Construction team

• The Operators

30. You are designing a database. Which tool would be useful?

Answers:

• Warnier Orr Diagram

• Gantt Chart

• Entity Relationship Diagram

• Structure Chart

• Data Flow Diagram

31. Which of the following regarding a DFD is incorrect?

Answers:

• It is different from a flowchart

• It is generated in the Analysis phase

• It is a graphical representation of the system

• It shows a sequence of steps

• It is a tool for Data Flow Analysis

32. Normalization of data is done to:

Answers:

• Simplify retrieval

• Structure the data

• Simplify maintenance

• Structure the data and simplify maintenance

• Simplify retrieval, structure the data and simplify maintenance

33. How are processes generally numbered in a DFD?

Answers:

• From left to right and top to bottom

• From left to right and bottom to top

• From top to bottom and left to right

• From top to bottom and right to left

• There is no prescribed numbering method

34. A person who is in charge of updating an account-holder's account would be depicted in a Data Flow Diagram by:

Answers:

• External Entity

• Will not be shown

• Data Flow

• Data Store

• The work being done (process)

35. Which phase of the Systems Development is concerned with writing programs?

Answers:

• Conception

• Construction

• Design

• Analysis

• Initiation

36. Which of the following statements is not true regarding Data Flow Diagrams?

Answers:

• They are graphical in nature

• They represent the working of the system

• They can be readily converted into a Structure Chart

• A circle in a DFD represents an entity

• They are of two types - physical and logical

37. A self-check digit is useful in detecting:

Answers:

• Transcription errors

• Transposition errors

• Transcription and transposition errors

• Random errors

• Transcription, transposition and random errors

38. In which phase of the SDLC will the document of Functional Specifications be generated?

Answers:

• Conception

• Construction

• Design

• Analysis

• Initiation

39. A structure chart is derived from a:

Answers:

• Hierarchy Chart

• Data Flow Diagram

• Warnier Orr Diagram

• ERD

• Gantt Chart

40. Which principle of design relates to the design of the system as a hierarchy of modules?

Answers:

• Modularity

• Coupling

• Cohesion

• Span of Control

• Shared use

41. A Data Dictionary is created at which stage of the Systems Development cycle?

Answers:

• Conception

• Construction

• Design

• Analysis

• Initiation

42. Which of the following is not a part of the DFD?

Answers:

• Entity

• Data Store

• Disk Storage

• Process

• Data Flow

43. Repository of data about data is depicted by:

Answers:

• DFD

• Data Dictionary

• Data Flow

• Data Store

• CAD

44. System quality relates to:

Answers:

• Reliability

• Efficiency

• Reliability and efficiency

• Maintainability

• Reliability, efficiency and maintainability

45. Using a Decision Table, what is the maximum number of combinations that may arise for three conditions?

Answers:

• 3

• 4

• 5

• 6

• 8

46. In which phase is the processing logic developed ?

Answers:

• Conception

• Construction

• Design

• Analysis

• Initiation

47. SDLC stands for:

Answers:

• Systems Development Life Cycle

• Systems Deployment Life Cycle

• Systems Design Life Cycle

• Systems Design Logic and Code

• Systems Duration of Life Cycle

48. The Conception phase is related to:

Answers:

• Writing programs

• Developing the processing logic

• Determining the system feasibility

• Identifying the problem

• Deciding the functions of the system

49. Which of the following statements, attributed to prototyping is not true?

Answers:

• It always gives faster performance than the actual system

• It encourages active end-user participation

• It is an active model that end-users can see, feel and experience

• It can accelerate several processes of Systems Design

• The prototype is actually a pilot or test model

50. Given the following phases of Systems Development, arrange them in the correct order:

1 Analysis
2 Conception
3 Design
4 Construction
5 Initiation

Answers:

• 2,5,1,3,4

• 2,5,3,1,4

• 5,1,3,2,4

• 1,3,2,4,5

• 1,2,3,4,5

51. Which of the following should be avoided while drawing DFDs?

Answers:

• Numbering the processes

• Labeling the processes

• Depicting flow of control

• Showing the external entities

• Showing the data stores

52. A Structure chart:

Answers:

• Shows module inter-relationships in a hierarchical manner

• Shows the flow of data within a system

• Describes the internal structure of a program in a graphical manner

• Is a graphical representation of structured English

• Depicts data structures in the form of a chart

53. CAD stands for:

Answers:

• Context Analysis Diagram

• Completed Analysis Diagram

• Compact Analyzed Diagram

• Contextual Analysis Diagram

• Combined Analysis Diagram

54. A process which has inputs but no outputs is known as a:

Answers:

• Black hole

• Dead process

• Null process

• Empty process

• White hole

55. A process which generates outputs without any inputs is known as a:

Answers:

• White hole

• Black hole

• Consuming process

• Unproductive process

• Idle process

56. VTOC stands for:

Answers:

• Visual Table of Contents

• Visual Translation of Contents

• Visual Translation of Characters

• Visual Table of Characters

• Visual Table of Conversions

Blogger 22:17 Add Comment Edit

98 Answered Test Questions:

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. 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

6. Point out the incorrect statement regarding group functions:

Answers:

• Group functions act on a group of rows

• Group functions return one result for all the rows operated upon

• Group functions ignore the null values

• Stdev and variance are examples of group functions

• One cannot combine group and single value functions in a query

• Sum is not a group function

7. 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

8. 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

9. 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));

10. 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

11. 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)

12. Which of the following field names are correct?

Answers:

• EmpNo

• 25Block

• #AccountID

• _CustomerName

• Product.Name

13. Which component of an RDBMS validates the syntax of the user's query?

Answers:

• Query Parser

• The Database Manager

• Query Optimization

• Database Administrator

14. What does the term DDL stand for?

Answers:

• Data Description Language

• Dynamic Data Language

• Data Definition Language

• Data Derived Language

• Descriptive Data Language

15. Which of the following can be used to uniquely identify a row?

Answers:

• Primary Key

• Unique Key

• Foreign Key

• All of the above

16. ________ is an operation that displays rows which meet a condition.

Answers:

• Restriction

• Extraction

• Projection

• Intersection

• Union

• Minus

• None of the above

17. 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;

18. What items, other than column names can be included in the select clause?

Answers:

• Arithmetic expressions

• Column aliases

• Concatenated columns

• None of the above

19. 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

20. Which operator will be evaluated first in the following statement:

select (age + 3 * 4 / 2 - 8) from emp

Answers:

• +

• -

• /

• *

21. What are the columns of a table called in a relational model?

Answers:

• Attributes

• Rows

• Tuples

• Constraints

• Keys

• Indexes

• Sets

• Elements

22. The level of data abstraction which describes how the data is actually stored is?

Answers:

• Physical level

• Conceptual level

• Storage level

• File level

23. Which of the following is not a set operator?

Answers:

• Union

• Union all

• Intersect

• Minus

• Minus all

24. 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

25. 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

26. < and > are examples of _________  type of operators.

Answers:

• Logical

• Arithmetic

• Assignment

• Ternary

• Relational

• Numeric

• Comparison

• None of the above

27. 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

28. 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

29. 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);

30. An association of several entities in a Entity-Relation model is called?

Answers:

• Tuple

• Record

• Relationship

• Field

31. 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

32. 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)

33. 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

34. 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

35. 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

36. What are the programs that execute automatically whenever DML operations are performed on tables called?

Answers:

• Triggers

• Procedures

• Functions

• None of the above

37. Which of the following is not a type of constraint?

Answers:

• Primary key

• Unique

• Check

• Distinct

• Default

38. 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

39. 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

40. Which character function should be used to return a specified portion of a character string?

Answers:

• CONCAT

• LENGTH

• SUBSTR

• INITCAP

41. 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

42. 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

43. 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

44. Which of the following is not a single value function?

Answers:

• Round

• Floor

• Avg

• Sqrt

• Tan

45. 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

46. 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

47. 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

48. In which type of database is SQL used?

Answers:

• Hierarchical

• Network

• Relational

• Object oriented

• All of above

49. The concept of data independence is similar to the concept of ________

Answers:

• Data type

• Abstract data type

• Consolidation

• Isolation

50. 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

51. 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

52. Where should sub queries be used?

Answers:

• To define the set of rows to be inserted in a table

• To define the set of rows to be included in a view

• To define one or more values to be assigned to existing rows

• To provide values for conditions in the Where clause

• To define a table to be operated on by a containing query

• All of the above are correct

• None of the above is correct

53. In which sequence are queries and sub-queries executed by the SQL Engine?

Answers:

• primary query -> sub query -> sub sub query and so on

• sub sub query -> sub query -> prime query

• the whole query is interpreted at one time

• there is no fixed sequence of interpretation, the query parser takes a decision on the fly

54. Are both the statements correct?

(a)where deptno in(2,4,5)
(b)where deptno=2 or deptno=4 or deptno=5

Answers:

• True

• False

55. What is the correct order of clauses in the select statement?
1        select
2        order by
3        where
4        having
5        group by

Answers:

• 1,2,3,4,5

• 1,3,5,4,2

• 1,3,5,2,4

• 1,3,2,5,4

• 1,3,2,4,5

• 1,5,2,3,4

• 1,4,2,3,5

• 1,4,3,2,5

56. There are two tables A and B. You are retreiving data from both tables where all rows from B table and only matching rows from A table should be displayed. Which type of join you will apply between A and B tables?

Answers:

• Inner join

• Left outer join

• Right outer join

• Self join

57. How many foreign key constraints can a table have?

Answers:

• 1

• 2

• 3

• 4

• 5

• 6

• None of the above

58. Which statement is correct for FIRST NORMAL FORM?

Answers:

• Includes only tables that do not have composite primary keys

• Must have data stored in a two-dimensional table with no repeating groups

• Every non-key column is nontransitively dependent upon its primary key

• None of the above

59. What is wrong with the following query:

select * from Orders where OrderID = (select OrderID from OrderItems where ItemQty > 50)

Answers:

• In the sub query, '*' should be used instead of 'OrderID'

• The sub query can return more than one row, so, '=' should be replaced with 'in'

• The sub query should not be in parenthesis

• None of the above

60. A company has the following departments:

Marketing , Designing , Production , Packing


What will be the result of the following query?

select * from table where department < 'Marketing';

Answers:

• The query will return " Designing , Packing "

• The query will return " Designing , production ,Packing "

• The query will return "Packing"

• Strings cannot be compared using < operator

• The query will return " Designing "

61. Which of the following is not a numeric group function?

Answers:

• Avg

• Count

• Highest

• Max

• Stdev

• Sum

62. 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 which German books(if any) are more popular than all the French?

Answers:

• select bookname from books where language='German' and popularityrating = (select popularityrating from books where language='French')

• select bookname from books where language='German' and popularityrating > (select popularityrating from books where language='French')

• select bookname from books where language='French' and popularityrating > (select max(popularityrating) from books where language='German')

• select bookname from books where language='German' and popularityrating > (select max(popularityrating) from books where language='French')

63. 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 which is the most popular book written in French?

Answers:

• select bookname from books where language='French' and popularityrating = (select max(popularityrating) from books where language='French')

• select bookname from books where language='French' and popularityrating = (select max(popularityrating) from books Having language='French')

• select bookname,max(popularityrating) from books where language='French' and max(popularityrating)

• select bookname,max(popularityrating) from books where language='French' having max(popularityrating)

64. Which of the following constraints cannot be applied at the table level?

Answers:

• Primary key

• Foreign key

• Not null

• Check

• Unique

65. A construction company is currently executing three projects- hotel construction, residential construction and business towers. The construction company employs both Civil Engineers and Structural Engineers. A Civil Engineer can work on only one project at a time, but each project can accomodate more than one Civil Engineer. On the other hand, a Structural Engineer can work on more than one project and a project could accomodate several Structural Engineers. Define the nature of relationship between (Civil Engineers and Projects) and (Structural Engineers and Projects)

Answers:

• one to many, one to one

• one to one, one to many

• many to one, many to many

• many to one, many to one

66. If entity x is existence-dependent on entity y then what is x said to be?

Answers:

• Dominant entity

• Subordinate entity

• Primary entity

• Secondary entity

67. Examine the code given below:

SELECT employee_id FROM employees WHERE commission_pct=.5 OR salary > 23000

Which of the following statements is correct with regard to this code?

Answers:

• It returns employees whose salary is 50% more than $23,000

• It returns employees who have 50% commission rate or salary greater than $23,000

• It returns employees whose salary is 50% less than $23,000

• None of the above

68. 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 how many books have been written on each subject. Displaying Name of Subject and count of the Books?

Answers:

• select subject,count(*) from books,subjects where books.subjectid=subjects.subjectid group by books.subjectid

• select count(*),subject from books,subjects where books.subject.id=subjects.subject.id group by subjects.subject

• select subject,count(*) from books,subjects where books.Authorid=subjects.Authorid group by books.subjectid,subjects.subject

• select subject,count(*) from books,subjects where books.BookId=subjects.BookId group by books.subjectid,subjects.subject

69. Which of the following statements are true?

Answers:

• With DDL you can create and remove tables, schemas, domains, indexes and views

• Select, Insert and Update are DCL commands

• Grant and Revoke are DML commands

• Commit and Rollback are DCL commands

70. Which logical operator can reverse the result?

Answers:

• AND

• OR

• NOT

• ANY

71. What is the collection of information stored in a database at a particular moment called?

Answers:

• Schema

• Instance

• Table

• Cluster

• View

• Index

• None of the above

72. What is the order of precedence among the following operators?

1 IN
2 NOT
3 AND
4 OR

Answers:

• 1,2,3,4

• 2,3,4,1

• 1,2,4,3

• 1,4,3,2

• 4,3,2,1

• 4,1,2,3

• 4,2,1,3

• 3,2,1,4

73. Which of the following date function(s) are invalid in Oracle SQL?

Answers:

• NEXT_DAY

• NEXT_MONTH

• MONTHS_BETWEEN

• DAYS_BETWEEN

74. Which of the following are aggregate functions in SQL?

Answers:

• Avg

• Select

• Order By

• Sum

• Union

• Group by

• Having

75. Which of the following is not a relational operation?

Answers:

• Restriction

• Extraction

• Projection

• Intersection

• Union

76. The level of abstraction which describes only part of the entire database is called?

Answers:

• Conceptual level

• View level

• Procedural level

• None of the above

77. 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 how many books, with a popularity rating of more than 7, have been written on each subject?

Answers:

• select subject,count(*) as Books from books,subjects where books.popularityrating > 7 group by subjects.subject

• select subject,count(*) as Books from books,subjects where books.authorid=subjects.authorid and books.popularityrating > 7 group by subjects.subject

• select subject,count(*) as Books from books,subjects where books.subjectid=subjects.subjectid and books.popularityrating = 7 group by subjects.subject

• select subject,count(*) as Books from books,subjects where books.subjectid=subjects.subjectid and books.popularityrating > 7 group by subjects.subject

78. The overall logical structure of a database can be expressed graphically by:

Answers:

• Data Flow Chart

• Flow Chart

• Directed Chart

• Entity-Relationship Diagram

• None of the above

79. If E1 and E2 are relational algebra expressions, then which of the following is NOT a relational algebra expression?

Answers:

• E1 U E2

• E1 / E2

• E1 - E2

• E1 x E2

80. Which one of the following correctly selects rows from the table myTable that have null in column column1?

Answers:

• SELECT * FROM myTable WHERE column1 is null

• SELECT * FROM myTable WHERE column1 = null

• SELECT * FROM myTable WHERE column1 EQUALS null

• SELECT * FROM myTable WHERE column1 NOT null

• SELECT * FROM myTable WHERE column1 CONTAINS null

81. A table has following values for its department field:

marketing, production, production, sales, NULL, NULL, Marketing, Null

What will the following query return:

Select distinct(department) from employees;

Answers:

• marketing, production, sales

• marketing, production, sales, NULL

• marketing, production, sales, NULL, NULL

• marketing, production, sales, NULL, Marketing

• marketing, production, sales, NULL, NULL, Marketing

82. Which query will display data from the Pers table relating to Analysts, Clerks and Salesmen who joined between 1/1/2005 and 1/2/2005 ?

Answers:

• select * from Pers where joining_date from '1/1/2005' to '1/2/2005', job='Analyst' or 'Clerk' or 'Salesman'

• select * from Pers where joining_date between '1/1/2005' to '1/2/2005', job='Analyst' or job='Clerk' or job='Salesman'

• select * from Pers where joining_date between '1/1/2005' and '1/2/2005' and (job='Analyst' or 'Clerk' or 'Salesman')

• None of the above

83. 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 which Authors have written at least 1 book with a popularity rating of less than 5?

Answers:

• select authorname from authors where authorid in (select authorid from books where popularityrating<5)

• select authorname from authors where authorid in (select authorid from books where popularityrating<=5)

• select authorname from authors where authorid in (select BookId from books where popularityrating<5)

• select authorname from authors where authorid in (select authorid from books where popularityrating in (0,5))

84. Choose the appropriate query for the Products table where data should be displayed primarily in ascending order of the ProductGroup column. Secondary sorting should be in descending order of the CurrentStock column.

Answers:

• Select * from Products order by CurrentStock,ProductGroup

• Select * from Products order by CurrentStock DESC,ProductGroup

• Select * from Products order by ProductGroup,CurrentStock

• Select * from Products order by ProductGroup,CurrentStock DESC

• None of the above

85. Which of the following statements are incorrect regarding definition of simple and complex views?

Answers:

• A simple view is one which derives data from only one table

• A simple view is one which contains no functions or groups of data

• A complex view is one which derives data from many tables

• A complex view is one which may contain functions

• A complex view cannot contain groups of data

• A complex view is a combination of more than one simple view

• A table can either have a simple or complex view, not both

86. What is a cluster?

Answers:

• Group of users

• Group of indexes

• Method of storing tables that are intimately related and often joined together into the same area of the disk

• Compressed datafile

• Segment of a partitioned table

• Group of constraints

• Group of databases

• Group of roles

87. The purpose of the domain is to constrain the set of valid values. True or false?

Answers:

• True

• False

88. 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

Answers:

• It will return the fields customer_id, customer_name, branch_name with a cartesian join

• It will return the fields customer_id, customer_name, branch_id, branch_name with a cartesian join

• It will return the fields customer_id, customer_name, branch_id, branch_name, branch_name with a cartesian join

• It will return an empty set since the two tables do not have any common field name

• It will return an error since * should be for queries involving one table only

89. With regard to statement (a) and statement (b), Which of the following option is correct.

(a)The FLOOR function returns the smallest integer greater or equal to the argument.
(b)The CEIL function gives the largest integer equal to or less that the argument.

Answers:

• (a) is true

• (b) is true

• both (a) and (b) are true

• both (a) and (b) are false

90. The Employee table uses alphanumeric characters for their Employee ID field, which contains 7 digits prefixed with 3 characters. The alphanumeric characters refer to the department code. You want to generate a list of all the department codes displayed in capital letters. Which function(s) you will use for this query?

Answers:

• CONCAT()

• SUBSTR()

• UPPER()

• LOWER()

• REPLACE()

91. Which of the following is not a SQL operator?

Answers:

• Between..and..

• Like

• In

• Is null

• Having

• Not in

92. Which of the following is not a feature of SQL?

Answers:

• SQL is a non-procedural language

• SQL processes sets of records rather than a single record at a time

• SQL can be used by a range of users including DBA's, application programmers, management personnel and many other types of end users

• Procedures and functions can be written with SQL

• SQL provides commands for a variety of tasks including querying data and inserting, updating and deleting data

• SQL can retrieve data from more than one table

93. How can data be accessed by users who do not have direct access to the tables?

Answers:

• By creating views

• By creating triggers

• By creating stored procedures

• None of the above

94. The simplest query must include at least________ and _________.

Answers:

• A select clause

• A where clause

• A from clause

• A group by clause

• A having clause

• An order by clause

95. Every Boyce-Codd Normal Form(BCNF)is in:

Answers:

• First Normal Form

• Second Normal Form

• Third Normal Form

• None of the above

96. Identify the incorrect statement/s regarding constraints.

Answers:

• A primary key constraint can be disabled

• Information about the constraints on a table can be retrieved from the data dictionary

• Information about the columns on which the constraints have been applied can be retrieved from the data dictionary

• There can be only one unique key in a table

• If a column has null values then the not null constraint cannot be applied to that column

• There can be more than one primary key

97. Which of the following is not the benefits of views?

Answers:

• Restricting access to database

• Allowing users to make simple queries to retrieve the results from complicated queries

• Providing data independence for adhoc users and application programs

• Faster retrieval of records

• Views allow the same data to be seen by different users in different ways

98. _________ is the operation that displays certain columns from the table.

Answers:

• Restriction

• Intersection

• Join

• Union

• Projection

• Selection

• Extraction

• SubQuery

Subscribe to: Posts ( Atom )

Which are not DCL operations

oDesk Test answers for System Analysis Test 2015

56 NOT Answered Yet Test Questions:

oDesk Test answers for SQL Test 2015

98 Answered Test Questions: