Upwork SQL TEST 2018

Hello Freelancer, Get the SQL TEST of Latest version 2018. We are ready here to provide your desire upwork test answer. We already tested all exam test information Available include on here. So Dear, Why Late? Read the below full exam test and get more information from our website.

1. Which are not DCL operations?

Answers:

1. Insert
2. Grant
3. Delete
4. Update
5. Revoke
6. Commit
7. Rollback

2. Which of the following is not a DML command?

Answers:

1. Set transaction
2. Explain plan
3. Update
4. Grant
5. Insert
6. Create
7. Alter
8. Revoke

3. Which of the following is not a DDL command?

Answers:

1. Drop
2. Create
3. Alter
4. Revoke
5. Explain plan
6. Insert
7. Update
8. Delete

4. Which of the following statements are not true regarding the primary key?

Answers:

1. There can only be one primary key in a table
2. An index is automatically generated upon creation of a primary key
3. A primary key can accept null values
4. A primary key can be composite
5. A primary key constraint can be either at the column level or at the table level
6. There can be more than one primary key in a table

5. Which of the following statements are wrong about primary keys?

Answers:

1. The primary key field must contain unique values
2. The primary key field can contain null values
3. A primary key is the column or set of columns that makes every row in the table unique
4. A primary key can be created with a maximum of three columns
5. Each table can have more than one primary keys
6. Unique key is another name for primary key

6. Which statements are true for views?

Answers:

1. The definition of a view is stored in data dictionary
2. Views provide a more secure way of retrieving data
3. Views are actually Tables and store data in the same manner as Tables

7. Examine the data in the EMPLOYEES table given below:

AST_NAME DEPARTMENT_ID SALARY

ALLEN 10 3000
MILLER 20 1500
KING 20 2200
DAVIS 30 5000

Which of the following Subqueries work?

Answers:

1. SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department_id);
2. SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);
3. SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);
4. SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);
5. SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY));

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

1. SUM(start_date)
2. AVG(start_date)
3. COUNT(start_date)
4. AVG(start_date, end_date)
5. MIN(start_date)

9. Select all the appropriate options.

Answers:

1. A table is a multiset of rows
2. A table is a two-dimensional array of rows and columns
3. A table is always dependent on other tables
4. A third normal form table is a table free of redundant data
5. A table must have a primary key

10. Which of the following field names are correct?

Answers:

1. EmpNo
2. 25Block
3. #AccountID
4. _CustomerName
5. Product.Name

11. Which component of an RDBMS validates the syntax of the user’s query?

Answers:

1. Query Parser
2. The Database Manager
3. Query Optimization
4. Database Administrator

12. What does the term DDL stand for?

Answers:

1. Data Description Language
2. Dynamic Data Language
3. Data Definition Language
4. Data Derived Language
5. Descriptive Data Language

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

Answers:

1. Primary Key
2. Unique Key
3. Foreign Key
4. All of the above

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

Answers:

1. Restriction
2. Extraction
3. Projection
4. Intersection
5. Union
6. Minus
7. None of the above

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

1. Select name from students reverse;
2. Select name from students reverse name;
3. Select name from students order by name descending;
4. Select name from students order by name reverse;
5. Select name from students order by name desc;
6. Select desc name from students;
7. Select reverse name from students;

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

Answers:

1. Arithmetic expressions
2. Column aliases
3. Concatenated columns
4. None of the above

17. Which of the following statements is true?

(a)The Insert statement creates new rows
(b)The Update statement modifies the table structure

Answers:

1. only (a) is true
2. only (b) is true
3. both (a) and (b) are true
4. both (a) and (b) are false

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

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

Answers:

1. +
2. –
3. /
4. *

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

Answers:

1. Attributes
2. Rows
3. Tuples
4. Constraints
5. Keys
6. Indexes
7. Sets
8. Elements

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

Answers:

1. Physical level
2. Conceptual level
3. Storage level
4. File level

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

Answers:

1. Union
2. Union all
3. Intersect
4. Minus
5. Minus all

22. 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. 1
2. 2
3. 3
4. 4
5. 5

23. For which SQL operation is Alter Table used?

Answers:

1. To add a column
2. To add an integrity constraint
3. To modify storage characteristics
4. To enable/disable or drop an integrity constraint
5. all of the above

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

Answers:

1. Logical
2. Arithmetic
3. Assignment
4. Ternary
5. Relational
6. Numeric
7. Comparison
8. None of the above

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

1. The two statements produce identical results
2. The second statement returns an error
3. There is no need to specify DESC because the results are sorted in descending order by default

26. What does MOD() function do?

Answers:

1. Returns the remainder after division
2. Modifies the column definition
3. Modifies the definition of a table
4. None of the above

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

1. Select departname from department where deptno in (select deptno from employee group by deptno having count(*) > 100);
2. Select departname from department where deptno in (select count(*) from employee group by deptno where count(*) > 100);
3. Select departname from department where count(deptno) > 100;
4. Select departname from department where deptno in (select count(*) from employee where count(*) > 100);

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

Answers:

1. Tuple
2. Record
3. Relationship
4. Field

29. Which of the following statements regarding views are incorrect?

Answers:

1. A view is like a window through which data on tables can be viewed or changed
2. A view is derived from another table
3. A view cannot be derived from another view
4. A view is stored as a select statement only
5. A view has no data of its own
6. A view is another name for a table

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

1. select AuthorName from Authors where AuthorId in (select AuthorId from Books group by AuthorId having count(*)>1)
2. select AuthorName from Authors, Books where Authors.AuthorId=Books.AuthorId and count(BookId)>1
3. select AuthorName from Authors, Books where Authors.AuthorId=Books.AuthorId group by AuthorName having count(*)>1
4. select AuthorName from Authors where AuthorId in (select AuthorId from Books having count(BookId)>1)

31. When a table is dropped using a simple DROP statement, SQL performs some more operations simultaneously, select all the valid operations?

Answers:

1. Removes all rows from the table
2. Drops all the table’s indexes
3. Removes all dependent views
4. Removes all dependent procedures

32. Which of the following statement is correct regarding table creation?

Answers:

1. Tables once created cannot be modified to add columns
2. Constraints can only be given while table creation
3. One can easily create a table from a given table
4. When a table is created from another table, all the constraints are copied as well
5. The width of the columns cannot be modified
6. Columns cannot be removed from a table

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

1. Select * from A.Sales join B.Sales
2. Select * from A.Sales union all B.Sales
3. Select * from A.Sales, B.Sales
4. None of the above

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

Answers:

1. Triggers
2. Procedures
3. Functions
4. None of the above

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

Answers:

1. Primary key
2. Unique
3. Check
4. Distinct
5. Default

36. Consider the query:
SELECT name
FROM Student
WHERE name LIKE ‘_a%’;
Which names will be displayed?

Answers:

1. Names starting with “a”
2. Names containing “a” as the second letter
3. Names starting with “a” or “A”
4. Names containing “a” as any letter except the first

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

1. select * from orders where sum(amount) > 20000
2. select orderdate, sum(amount) from orders where sum(amount) > 20000 order by OrderDate
3. select orderdate, sum(amount) from orders group by orderdate having sum(amount) > 20000
4. select orderdate, sum(amount) from orders group by OrderDate where sum(amount) > 20000

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

Answers:

1. CONCAT
2. LENGTH
3. SUBSTR
4. INITCAP

39. What will happen if you query the emp table as shown below:

select empno, DISTINCT ename, Salary from emp;

Answers:

1. EMPNO, unique value of ENAME and then SALARY are displayed
2. EMPNO, unique value ENAME and unique value of 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

40. What is a rollback of transactions normally used for?

Answers:

1. Recover from the transaction failure
2. Update the transaction
3. Retrieve old records
4. None of the above

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

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

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

Answers:

1. Round
2. Floor
3. Avg
4. Sqrt
5. Tan

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

1. 4,3,5,1,2
2. 4,5,3,2,1
3. 5,2,1,4,3
4. 5,2,3,4,1
5. 2,3,1,4,5
6. 2,3,1,5,4
7. 1,2,3,4,5
8. 3,2,1,4,5

44. Data validation can be implemented at the data definition stage through:

Answers:

1. Check constraints with specified values
2. Referential constraints, by creating foreign keys for another table
3. Default value of column
4. Not Null constraint

45. What clause should be used to display the rows of a table in ascending order of a particular column?

Answers:

1. Where
2. Order By
3. Group By
4. Having
5. First Group By and then Having
6. Like
7. Between

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

1. = should be replaced by in operator
2. Order by clause in the subquery should be preceded with a group by clause
3. Order by clause in the subquery can be used only if the where and group by clauses have been applied
4. Group by clause should be applied to the outer query
5. An order by clause is not allowed in a subquery
6. There is no error

47. In which type of database is SQL used?

Answers:

1. Hierarchical
2. Network
3. Relational
4. Object oriented
5. All of above

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

Answers:

1. Data type
2. Abstract data type
3. Consolidation
4. Isolation

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:

1. The value displayed in the CALC_VALUE column will be lower
2. The value displayed in the CALC_VALUE column will be higher
3. There will be no difference in the value displayed in the CALC_VALUE column
4. 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:

1. 4
2. 2
3. 1
4. .5
5. .25
6. 0
7. 8
8. 24

51. Where should sub queries be used?

Answers:

1. To define the set of rows to be inserted in a table
2. To define the set of rows to be included in a view
3. To define one or more values to be assigned to existing rows
4. To provide values for conditions in the Where clause
5. To define a table to be operated on by a containing query
6. All of the above are correct
7. None of the above is correct

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

Answers:

1. primary query -> sub query -> sub sub query and so on
2. sub sub query -> sub query -> prime query
3. the whole query is interpreted at one time
4. there is no fixed sequence of interpretation, the query parser takes a decision on the fly

53. Are both the statements correct?

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

Answers:

1. True
2. False

54. 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. 1,2,3,4,5
2. 1,3,5,4,2
3. 1,3,5,2,4
4. 1,3,2,5,4
5. 1,3,2,4,5
6. 1,5,2,3,4
7. 1,4,2,3,5
8. 1,4,3,2,5

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

1. Inner join
2. Left outer join
3. Right outer join
4. Self join

56. Point out the incorrect statement regarding group functions:

Answers:

1. Group functions act on a group of rows
2. Group functions return one result for all the rows operated upon
3. Group functions ignore the null values
4. Stdev and variance are examples of group functions
5. One cannot combine group and single value functions in a query
6. Sum is not a group function

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

Answers:

1. 1
2. 2
3. 3
4. 4
5. 5
6. 6
7. None of the above

58. Which statement is correct for FIRST NORMAL FORM?

Answers:

1. Includes only tables that do not have composite primary keys
2. Must have data stored in a two-dimensional table with no repeating groups
3. Every non-key column is nontransitively dependent upon its primary key
4. 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:

1. In the sub query, ‘*’ should be used instead of ‘OrderID’
2. The sub query can return more than one row, so, ‘=’ should be replaced with ‘in’
3. The sub query should not be in parenthesis
4. 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:

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 < operator
5. The query will return ” Designing “

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

Answers:

1. Avg
2. Count
3. Highest
4. Max
5. Stdev
6. 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:

1. select bookname from books where language=’German’ and popularityrating = (select popularityrating from books where language=’French’)
2. select bookname from books where language=’German’ and popularityrating > (select popularityrating from books where language=’French’)
3. select bookname from books where language=’French’ and popularityrating > (select max(popularityrating) from books where language=’German’)
4. 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:

1. select bookname from books where language=’French’ and popularityrating = (select max(popularityrating) from books where language=’French’)
2. select bookname from books where language=’French’ and popularityrating = (select max(popularityrating) from books Having language=’French’)
3. select bookname,max(popularityrating) from books where language=’French’ and max(popularityrating)
4. 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:

1. Primary key
2. Foreign key
3. Not null
4. Check
5. 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:

1. one to many, one to one
2. one to one, one to many
3. many to one, many to many
4. many to one, many to one

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

Answers:

1. Dominant entity
2. Subordinate entity
3. Primary entity
4. 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:

1. It returns employees whose salary is 50% more than $23,000
2. It returns employees who have 50% commission rate or salary greater than $23,000
3. It returns employees whose salary is 50% less than $23,000
4. 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:

1. select subject,count(*) from books,subjects where books.subjectid=subjects.subjectid group by books.subjectid
2. select count(*),subject from books,subjects where books.subject.id=subjects.subject.id group by subjects.subject
3. select subject,count(*) from books,subjects where books.Authorid=subjects.Authorid group by books.subjectid,subjects.subject
4. 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:

1. With DDL you can create and remove tables, schemas, domains, indexes and views
2. Select, Insert and Update are DCL commands
3. Grant and Revoke are DML commands
4. Commit and Rollback are DCL commands

70. Which logical operator can reverse the result?

Answers:

1. AND
2. OR
3. NOT
4. ANY

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

Answers:

1. Schema
2. Instance
3. Table
4. Cluster
5. View
6. Index
7. 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. 1,2,3,4
2. 2,3,4,1
3. 1,2,4,3
4. 1,4,3,2
5. 4,3,2,1
6. 4,1,2,3
7. 4,2,1,3
8. 3,2,1,4

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

Answers:

1. NEXT_DAY
2. NEXT_MONTH
3. MONTHS_BETWEEN
4. DAYS_BETWEEN

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

Answers:

1. Avg
2. Select
3. Order By
4. Sum
5. Union
6. Group by
7. Having

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

Answers:

1. Restriction
2. Extraction
3. Projection
4. Intersection
5. Union

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

Answers:

1. Conceptual level
2. View level
3. Procedural level
4. 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:

1. select subject,count(*) as Books from books,subjects where books.popularityrating > 7 group by subjects.subject
2. select subject,count(*) as Books from books,subjects where books.authorid=subjects.authorid and books.popularityrating > 7 group by subjects.subject
3. select subject,count(*) as Books from books,subjects where books.subjectid=subjects.subjectid and books.popularityrating = 7 group by subjects.subject
4. 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:

1. Data Flow Chart
2. Flow Chart
3. Directed Chart
4. Entity-Relationship Diagram
5. 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:

1. E1 U E2
2. E1 / E2
3. E1 – E2
4. E1 x E2

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

Answers:

1. SELECT * FROM myTable WHERE column1 is null
2. SELECT * FROM myTable WHERE column1 = null
3. SELECT * FROM myTable WHERE column1 EQUALS null
4. SELECT * FROM myTable WHERE column1 NOT null
5. 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:

1. marketing, production, sales
2. marketing, production, sales, NULL
3. marketing, production, sales, NULL, NULL
4. marketing, production, sales, NULL, Marketing
5. 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:

1. select * from Pers where joining_date from ‘1/1/2005’ to ‘1/2/2005′, job=’Analyst’ or ‘Clerk’ or ‘Salesman’
2. select * from Pers where joining_date between ‘1/1/2005’ to ‘1/2/2005′, job=’Analyst’ or job=’Clerk’ or job=’Salesman’
3. select * from Pers where joining_date between ‘1/1/2005’ and ‘1/2/2005′ and (job=’Analyst’ or ‘Clerk’ or ‘Salesman’)
4. 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:

1. select authorname from authors where authorid in (select authorid from books where popularityrating<5)
2. select authorname from authors where authorid in (select authorid from books where popularityrating<=5)
3. select authorname from authors where authorid in (select BookId from books where popularityrating<5)
4. 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:

1. Select * from Products order by CurrentStock,ProductGroup
2. Select * from Products order by CurrentStock DESC,ProductGroup
3. Select * from Products order by ProductGroup,CurrentStock
4. Select * from Products order by ProductGroup,CurrentStock DESC
5. None of the above

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

Answers:

1. A simple view is one which derives data from only one table
2. A simple view is one which contains no functions or groups of data
3. A complex view is one which derives data from many tables
4. A complex view is one which may contain functions
5. A complex view cannot contain groups of data
6. A complex view is a combination of more than one simple view
7. A table can either have a simple or complex view, not both

86. What is a cluster?

Answers:

1. Group of users
2. Group of indexes
3. Method of storing tables that are intimately related and often joined together into the same area of the disk
4. Compressed datafile
5. Segment of a partitioned table
6. Group of constraints
7. Group of databases
8. Group of roles

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

Answers:

1. True
2. 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:

1. It will return the fields customer_id, customer_name, branch_name with a cartesian join
2. It will return the fields customer_id, customer_name, branch_id, branch_name with a cartesian join
3. It will return the fields customer_id, customer_name, branch_id, branch_name, branch_name with a cartesian join
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 * 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:

1. (a) is true
2. (b) is true
3. both (a) and (b) are true
4. 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:

1. CONCAT()
2. SUBSTR()
3. UPPER()
4. LOWER()
5. REPLACE()

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

Answers:

1. Between..and..
2. Like
3. In
4. Is null
5. Having
6. Not in

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

Answers:

1. SQL is a non-procedural language
2. SQL processes sets of records rather than a single record at a time
3. SQL can be used by a range of users including DBA’s, application programmers, management personnel and many other types of end users
4. Procedures and functions can be written with SQL
5. SQL provides commands for a variety of tasks including querying data and inserting, updating and deleting data
6. 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:

1. By creating views
2. By creating triggers
3. By creating stored procedures
4. None of the above

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

Answers:

1. A select clause
2. A where clause
3. A from clause
4. A group by clause
5. A having clause
6. An order by clause

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

Answers:

1. First Normal Form
2. Second Normal Form
3. Third Normal Form
4. None of the above

96. Identify the incorrect statement/s regarding constraints.

Answers:

1. A primary key constraint can be disabled
2. Information about the constraints on a table can be retrieved from the data dictionary
3. Information about the columns on which the constraints have been applied can be retrieved from the data dictionary
4. There can be only one unique key in a table
5. If a column has null values then the not null constraint cannot be applied to that column
6. There can be more than one primary key

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

Answers:

1. Restricting access to database
2. Allowing users to make simple queries to retrieve the results from complicated queries
3. Providing data independence for adhoc users and application programs
4. Faster retrieval of records
5. 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:

1. Restriction
2. Intersection
3. Join
4. Union
5. Projection
6. Selection
7. Extraction
8. SubQuery

Finally no more words require about the SQL TEST  information in this session of this content. If you are require knowing more, Please ask to us via our contact us form or comment box. Please make sure that, you don’t send Personal information via the Comment box. Thanks for Being with us.