MS SQL Server 2000 Test 2016 – Upwork Test Answers

Till 27 February 2016, this Upwork MS SQL Server 2000 Test attended by 5,351 freelancers and total 2,876 freelancers qualified it. Please Try to review each questions and answers properly. Take your time. Maybe you can have Top Result in Upwork MS SQL Server 2000 Test.

Do you need prove? watch this video.

[sociallocker id=”884″] Upwork MS SQL Server 2000 Test 2016 [/sociallocker]

2 Answered Test Questions:
1. 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:
a. The query will return “Designing, Packing”
a. The query will return “Designing, production, Packing”
a. The query will return “packing”
a. Strings cannot be compared using < operator
a. The query will return “Designing”
2. Consider the following table structure of students:
rollno int
name varchar(20)
course varchar(20)

What will be the query to display the courses in which the number of students
enrolled is more than 5?
Answers:
a. Select course from students where count(course) > 5;
a. Select course from students where count(*) > 5 group by course;
a. Select course from students group by course;
a. Select course from students group by course having count(*) > 5;
a. Select course from students group by course where count(*) > 5;
a. Select course from students where count(group(course)) > 5;
a. Select count(course) > 5 from students;
a. None of the above

85 NOT Answered Yet Test Questions:
(hold on, will be updated soon)
3. Consider the query:
SELECT name
FROM Student
WHERE name LIKE ‘_a%’;
Which names will be displayed?
Answers:
a. Names starting with “a”
a. Names containing “a” as the second letter
a. Names starting with “a” or “A”
a. Names containing “a” as any letter except the first
4. Which of the following is not a SQL operator?
Answers:
a. Between..and..
a. Like
a. In
a. Is null
a. Having
a. Not in
5. What is the maximum value that can be stored for a datetime field?
Answers:
a. Dec 31, 9999
a. Jun 6, 2079
a. Jan 1, 2753
a. Jan 1, 2100
6. 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

Which of the following queries will return the the most popular books written in French?
Answers:
a. select bookname from books where language=’French’ and popularityrating = (select max(popularityrating) from books where language=’French’)
a. select bookname from books where language=’French’ and popularityrating = (select max(popularityrating) from books Having language=’French’)
a. select bookname,max(popularityrating) from books where language=’French’ and max(popularityrating)
a. select bookname,max(popularityrating) from books where language=’French’ having max(popularityrating)
7. Which of the following is not a column property?
Answers:
a. Null
a. Not Null
a. Default
a. Identity
8. Which one of the following fields is the ideal candidate for the primary key in a student record base?
Answers:
a. Parent’s name
a. Address
a. System generated ID
a. Student name
a. Phone number
9. Which of the following are not date parts?
Answers:
a. quarter
a. dayofweek
a. dayofyear
a. weekday
10. You want to display the titles of books that meet the following criteria:

1. Purchased before November 11, 2002
2. Price is less than $500 or greater than $900

You want to sort the result by the date of purchase, starting with the most recently bought book.

Which of the following statements should you use?
Answers:
a. SELECT book_title FROM books WHERE price between 500 and 900 AND purchase_date < ’11/11/2002′ ORDER BY purchase_date;
a. SELECT book_title FROM books WHERE price IN (500, 900) AND purchase_date < ’11/11/2002′ ORDER BY purchase date ASC;
a. SELECT book_title FROM books WHERE (price < 500 OR > 900) AND purchase_date DESC;
a. SELECT Book_title FROM books WHERE (price < 500 OR price > 900) AND purchase_date < ’11/11/2002′ ORDER BY purchase_date DESC; 11. 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: a. select subject,count(*) as Books from books,subjects where books.popularityrating > 7 group by subjects.subject
a. select subject,count(*) as Books from books,subjects where books.authorid=subjects.authorid and books.popularityrating > 7 group by subjects.subject
a. select subject,count(*) as Books from books,subjects where books.subjectid=subjects.subjectid and books.popularityrating = 7 group by subjects.subject
a. select subject,count(*) as Books from books,subjects where books.subjectid=subjects.subjectid and books.popularityrating > 7 group by subjects.subject
12. What will happen if you query the emp table as shown below:

select empno, DISTINCT ename, Salary from emp;
Answers:
a. EMPNO, unique value of ENAME and then SALARY are displayed
a. EMPNO, unique value ENAME and unique value of SALARY are displayed
a. DISTINCT is not a valid keyword in SQL
a. No values will be displayed because the statement will return an error
13. You should avoid the use of cursors because:
Answers:
a. They are very difficult to implement
a. Programs with cursors take more time to run, hence performance degrades
a. programs with cursors are more lengthy, hence they consume more space/memory
a. No, you must maximize the use of cursors because they improve performance
14. Which of the following statements about SQL Server comments is false?
Answers:
a. /* … */ are used for multiline comments
a. // is used for single line comments
a. — is used for single line comments
a. Nested comments are allowed i.e. /* comment 1 /* comment 2 */ comment 1*/
a. ‘ is used for single line comments
15. State which of the following are true
Answers:
a. Views are a logical way of looking at the logical data located in the tables
a. Views are a logical way of looking at the physical data located in the tables
a. Tables are physical constructs used for storage and manipulation of data in databases
a. Tables are logical constructs used for storage and manipulation of data in databases
16. The AND operator displays a row if ANY conditions listed are true. The OR operator displays a row if ALL of the conditions listed are true
Answers:
a. True
a. False
17. Which of the following are false for batches (batch commands)?
Answers:
a. Statements in a batch are parsed, compiled and executed as a group
a. None of the statements in the batch is executed if there are any syntax errors in the batch
a. None of the statements in the batch is executed if there are any parsing errors in the batch
a. None of the statements in the batch is executed if there are any fatal errors in the batch
18. Does SQL Server support user-defined datatypes?
Answers:
a. No, SQL doesn’t support user-defined datatypes
a. Yes, but it allows the user to subclass an existing datatype only
a. Yes, it allows a user to define new datatypes without new storage characterstics
a. Yes, it allows a user to define new datatypes with new storage characteristics
19. Consider the transaction:

Begin Transaction
Create table A ( x smallint , y smallint )
Create table B ( p smallint , q smallint )

Update A set x=600 where y > 700
Update B set p=78 where q=99

If @@ error != 0
Begin
RollBack Transaction
Return
End

Commit Transaction

Select the correct option:
Answers:
a. The transaction will work perfectly fine
a. It will report an error
a. The error handling routine contains a syntax error
a. None of the above
a. Both b and c
20. What is the correct order of clauses in the select statement?

1 select
2 order by
3 where
4 having
5 group by
Answers:
a. 1,2,3,4,5
a. 1,3,5,4,2
a. 1,3,5,2,4
a. 1,3,2,5,4
a. 1,3,2,4,5
a. 1,5,2,3,4
a. 1,4,2,3,5
a. 1,4,3,2,5
21. Is the FROM clause necessary in every SELECT statement?
Answers:
a. Yes
a. No
22. Which of the following statements is not true about the table object in SQL Server?
Answers:
a. A table can have only 1024 columns
a. Multiple Tables with the same name can be created within the same database
a. A column in a table can have the same name as that of the table
a. All statements are true
a. All statements are false
23. How can you view the structure of a table named “myTable” in SQL Server?
Answers:
a. desc myTable
a. desc table myTable
a. sp_columns myTable
a. None of the above
a. Using either option a or c
24. Which of the following datatypes is not supported by SQL-Server?
Answers:
a. Character
a. Binary
a. Logical
a. Date
a. Numeric
a. All are supported
25. 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:
a. SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department_id);
a. SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);
a. SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);
a. SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);
a. SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY));
26. What does referential integrity (also called relational integrity) prevent?
Answers:
a. Loss of data from employee sabotage
a. Loss of data from any one corrupted table
a. Recursive joins
a. One-to-many or many-to-many relationships between columns in a table
a. Data redundancy
27. 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:
a. select subject,count(*) from books,subjects where books.subjectid=subjects.subjectid group by books.subjectid
a. select subject,count(*) from books,subjects where books.subjectid=subjects.subjectid group by books.subjectid,subjects.subject
a. select subject,count(*) from books,subjects where books.Authorid=subjects.Authorid group by books.subjectid,subjects.subject
a. select subject,count(*) from books,subjects where books.BookId=subjects.BookId group by books.subjectid,subjects.subject
28. What is the correct SQL syntax for returning all the columns from a table named “Persons” sorted REVERSE alphabetically by “FirstName”?
Answers:
a. SELECT * FROM Persons WHERE FirstName ORDER BY FirstName DESC
a. SELECT * FROM Persons SORT REVERSE ‘FirstName’
a. SELECT * FROM Persons ORDER BY -‘FirstName’
a. SELECT * FROM Persons ORDER BY FirstName DESC
29. The simplest query must include at least ________ and _________.
Answers:
a. A select clause
a. A where clause
a. A from clause
a. A group by clause
a. A having clause
a. An order by clause
30. Which of the following options is not correct about the DATEDIFF() function?
Answers:
a. It returns the difference between parts of two specified dates
a. It takes three arguments
a. It returns a signed integer value equal to second date part minus first date part
a. It returns a signed integer value equal to first date part minus second date part
31. The STUDENT_GRADES table has these columns:

STUDENT_ID INT
SEMESTER_END DATETIME
GPA FLOAT

Which of the following statements finds the highest Grade Point Average (GPA) per semester?
Answers:
a. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL
a. SELECT (gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL
a. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end
a. SELECT MAX(gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student_grades
a. SELECT MAX(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL
32. Is this statement true or false:

A cursor is a pointer that identifies a specific working row within a set
Answers:
a. True
a. False
33. Which of the following is/are true with reference to Triggers?
Answers:
a. A trigger cannot maintain cascading referential integrity
a. Manipulating columns of datatype IMAGE / TEXT does not cause triggers to be executed
a. Only the database owner can create triggers
a. Triggers are executed on INSERT, UPDATE, DELETE and SELECT statements
34. Which of the following constraints can be used to enforce the uniqueness of rows in a table?
Answers:
a. DEFAULT and NOT NULL constraints
a. FOREIGN KEY constraints
a. PRIMARY KEY and UNIQUE constraints
a. IDENTITY columns
a. CHECK constraints
35. Is the following statement true or false?

A column that allows NULLs requires more space to store a value.
Answers:
a. True
a. False
36. You are maintaing data for its products in the Products table, and wants to see the products which are 50 or more numbers far from the minimum stock limit. The structure of the Products table is:

ProductID
ProductName
CurrentStock
MinimumStock

Two possible queries are:

(a)select * from products where currentStock > MinimumStock + 50
(b)select * from products where currentStock – 50 > MinimumStock

Choose the appropriate option with regard to the above queries.
Answers:
a. (a) is correct
a. (b) is correct
a. (a) and (b) both are correct
a. (a) and (b) both are incorrect
37. In which sequence are queries and sub-queries executed by the SQL Engine?
Answers:
a. primary query -> sub query -> sub sub query and so on
a. sub sub query -> sub query -> prime query
a. the whole query is interpreted at one time
a. there is no fixed sequence of interpretation, the query parser takes a decision on the fly
38. Which of the following commands is used to change the structure of table?
Answers:
a. CHANGE TABLE
a. MODIFY TABLE
a. ALTER TABLE
a. UPDATE TABLE
39. Which one of the following must be specified in every DELETE statement?
Answers:
a. Table Name
a. Database name
a. LIMIT clause
a. WHERE clause
a. Column Names
40. What is the correct SQL syntax for selecting all the columns where the “LastName” is alphabetically between (and including) “Hansen” and “Pettersen”?
Answers:
a. SELECT * FROM Persons WHERE LastName > ‘Hansen’, LastName < ‘Pettersen’ a. SELECT LastName > ‘Hansen’ AND LastName < ‘Pettersen’ FROM Persons a. SELECT * FROM customers WHERE LastName > ‘Hansen’ AND LastName > ‘Pettersen’
a. SELECT * FROM Persons WHERE LastName BETWEEN ‘Hansen’ AND ‘Pettersen’
41. Which of the following is false with regards to sp_help?
Answers:
a. When a procedure name is passed to sp_help, it shows the parameters
a. When a table name is passed to sp_help, it shows the structure of the table
a. When no parameter is passed, it provides a list of all objects and user-defined datatypes in a database
a. All of the above are true
42. What is the order of precedence among the following operators?

1 IN
2 NOT
3 AND
4 OR
Answers:
a. 1,2,3,4
a. 2,3,4,1
a. 1,2,4,3
a. 1,4,3,2
a. 4,3,2,1
a. 4,1,2,3
a. 4,2,1,3
a. 3,2,1,4
43. Is it possible to insert several rows into a table with a single INSERT statement?
Answers:
a. No
a. Yes
44. Which of the following is not a control statement?
Answers:
a. if…else
a. if exists
a. do…while
a. while
a. begin…end
45. Which of the following is not a global variable?
Answers:
a. @@colcount
a. @@error
a. @@rowcount
a. @@version
a. All are valid global variables
46. A production house needs a sale report where total sale of the day is more than $20,000. Which of the following query should be used?
Answers:
a. select OrderDate,Amount from orders where sum(amount) > 20000
a. select OrderDate,Amount from orders where sum(amount) > 20000 order by OrderDate
a. select count(OrderDate),sum(amount) from orders group by OrderDate having sum(amount) > 20000
a. select count(OrderDate),sum(amount) from orders group by OrderDate where sum(amount) > 20000
47. When designing a database table, how do you avoid missing column values for non-primary key columns?
Answers:
a. Use UNIQUE constraints
a. Use PRIMARY KEY constraints
a. Use DEFAULT and NOT NULL constraints
a. Use FOREIGN KEY constraints
a. Use SET constraints
48. Which of the following is an invalid statement for manipulation of binary data?
Answers:
a. displaytext
a. readtext
a. writetext
a. updatetext
49. Which of the following options is correct about identity(seed, increment)?
Answers:
a. The increment parameter can never be negative
a. The increment can be 0
a. More than one columns in a table can be assigned the identity property
a. The identity property value for a column assures uniqueness of a row
a. None of the above
50. Which of the following is not a valid binary datatype in SQL Server?
Answers:
a. BINARY
a. VARBINARY
a. BIT
a. IMAGE
a. TMESTAMP
51. Sample Code

CREATE TABLE table1(
column1 varchar(50),
column2 varchar(50),
column3 varchar(50),
column4 varchar(50));

Which one of the following is the correct syntax for adding the column named “column2a” to the table shown above?
Answers:
a. ALTER TABLE table1 ADD column2a varchar(50);
a. MODIFY TABLE table1 ADD column2a;
a. INSERT INTO table1 column2a AS varchar(50);
a. ALTER TABLE table1 INSERT column2a varchar(50);
a. CHANGE TABLE table1 INSERT column2a;
52. 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:
a. Select departname from department where deptno in (select deptno from employee group by deptno having count(*) > 100);
a. Select departname from department where deptno in (select count(*) from employee group by deptno where count(*) > 100);
a. Select departname from department where count(deptno) > 100;
a. Select departname from department where deptno in (select count(*) from employee where count(*) > 100);
53. 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:
a. marketing, production, sales
a. marketing, production, sales, NULL
a. marketing, production, sales, NULL, NULL
a. marketing, production, sales, NULL, Marketing
a. marketing, production, sales, NULL, NULL, Marketing
54. Which of the following is not a valid Arithmetic operator in SQL Server?
Answers:
a. +
a. –
a. *
a. \
a. ^
55. It is time for the annual sales awards at your company. The awards include
certificates awarded for the five sales of the highest sale amounts. You need
to produce a list of the five highest revenue transactions from the Orders table
in the Sales database. The Orders table is defined as follows:

CREATE TABLE Orders (
OrderID Int IDENTITY NOT NULL,
SalesPersonID Int NOT NULL,
RegionID Int NOT NULL,
OrderDate Datetime NOT NULL,
OrderAmount Int NOT NULL )

Which statement will produce the report correctly?
Answers:
a. SELECT TOP 5 OrderAmount, SalesPersonID FROM orders
a. SELECT TOP 5 OrderAmount, SalesPersonID FROM orders ORDER BY OrderAmount DESC
a. SELECT TOP 5 WITH TIES OrderAmount, SalesPersonID From Orders
a. SELECT TOP 5 WITH TIES OrderAmount, SalesPersonID From Orders ORDER BY OrderAmount
56. What is the numeric range that is supported by the datatype tinyint?
Answers:
a. 0-25
a. 0-100�����
a. 0-255
a. 0-3276
57. Which one of the following correctly selects rows from the table myTable that have null in column column1?
Answers:
a. SELECT * FROM myTable WHERE column1 is null
a. SELECT * FROM myTable WHERE column1 = null
a. SELECT * FROM myTable WHERE column1 EQUALS null
a. SELECT * FROM myTable WHERE column1 NOT null
a. SELECT * FROM myTable WHERE column1 CONTAINS null
58. 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:
a. The two statements produce identical results
a. The second statement returns an error
a. There is no need to specify DESC because the results are sorted in descending order by default
a. None of the above
59. Which of the following statements are true?
Answers:
a. soundex(Smith) is the same as soundex(Smithson)
a. soundex(SMITH) is different from soundex(smith)
a. soundex(smith) is the same as soundex(smyth)
a. soundex(smith) is different from soundex(smythe)
a. All are false
60. Which operator will be evaluated first in the following statement:

select (age + 3 * 4 / 2 – 8) from emp
Answers:
a. +
a. –
a. /
a. *
61. Examine the description of the STUDENTS table:

STD_ID INT
COURSE_ID VARCHAR (10)
START_DATE DATETIME
END_DATE DATETIME

The aggregate functions valid on the START_DATE column are:
Answers:
a. SUM(start_date)
a. AVG(start_date)
a. COUNT(start_date)
a. AVG(start_date, end_date)
a. MIN(start_date)
62. 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:
a. It returns employees whose salary is 50% more than $23,000
a. It returns employees who have 50% commission rate or salary greater than $23,000
a. It returns employees whose salary is 50% less than $23,000
a. None of the above
63. 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:
a. Select * from Products order by CurrentStock,ProductGroup
a. Select * from Products order by CurrentStock DESC,ProductGroup
a. Select * from Products order by ProductGroup,CurrentStock
a. Select * from Products order by ProductGroup,CurrentStock DESC
a. None of the above
64. Which of the following is the syntax for creating an Index?
Answers:
a. CREATE [UNIQUE] INDEX index_name OF tbl_name (index_columns)
a. CREATE [UNIQUE] INDEX OF tbl_name (index_columns)
a. CREATE [UNIQUE] INDEX ON tbl_name (index_columns)
a. CREATE [UNIQUE] INDEX index_name ON tbl_name (index_columns)
65. The IF UPDATE (column_name) parameter in a trigger definition will return
TRUE in case of an INSERT statement being executed on the triggered table:
Answers:
a. Yes
a. No
a. It returns TRUE only if an UPDATE query is executed
a. Both b and c
66. 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:
a. select authorname from authors where authorid in (select authorid from books where popularityrating<5)
a. select authorname from authors where authorid in (select authorid from books where popularityrating<=5)
a. select authorname from authors where authorid in (select BookId from books where popularityrating<5) a. select authorname from authors where authorid in (select authorid from books where popularityrating in (0,5)) 67. View the following Create statement: 1 Create table Pers 2(EmpNo Int not null, 3 EName Char not null, 4 Join Datetime not null, 5 Pay Smallmoney) Which line contains an error? Answers: a. 1 a. 2 a. 3 a. 4 a. 5 68. Study the situation described below and identify the nature of relationship? Each student can enroll into more than one class. Each class can accommodate more than one student. Answers: a. 1 to N a. 1 to 1 a. M to N to 1 a. M to M a. N to 1 69. What is wrong with the following query: select * from Orders where OrderID = (select OrderID from OrderItems where ItemQty > 50)
Answers:
a. In the sub query, ‘*’ should be used instead of ‘OrderID’
a. The sub query can return more than one row, so, ‘=’ should be replaced with ‘in’
a. The sub query should not be in parenthesis
a. None of the above
70. How can you change “Hansen” into “Nilsen” in the LastName column in the Persons
Table?
Answers:
a. UPDATE Persons SET LastName = ‘Nilsen’ WHERE LastName = ‘Hansen’
a. UPDATE Persons SET LastName = ‘Hansen’ INTO LastName = ‘Nilsen’
a. SAVE Persons SET LastName = ‘Nilsen’ WHERE LastName = ‘Hansen’
a. SAVE Persons SET LastName = ‘Hansen’ INTO LastName = ‘Nilsen’
71. What happens if you type the following statement at the T-SQL prompt?

SELECT getdate()
Answers:
a. It will print the current date
a. It will generate an ‘Undefined Function’ error message
a. It will generate an ‘Incorrect Syntax’ error message because you cannot write a function name after SELECT
72. In a query, which of the following is/are executed first?
Answers:
a. Parenthesis
a. Multiplication, Division and Exponents
a. Addition and Subtraction
a. Logical Operations
73. Which of the following is not a valid character datatype in SQL Server?
Answers:
a. BLOB
a. CHAR
a. VARCHAR
a. TEXT
a. VARTEXT
74. 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:
a. It will return the fields customer_id, customer_name, branch_name
a. It will return the fields customer_id, customer_name, branch_id, branch_name
a. It will return the fields customer_id, customer_name, branch_id, branch_name, branch_name
a. It will return an empty set since the two tables do not have any common field name
a. It will return an error since * is used alone for one table only
75. The sales database contains a customer table and an order table. For each order there is one and only one customer, and for each customer there can be zero or more orders. How should primary and foreign key fields be placed into the design of this database?
Answers:
a. A primary key should be created for the customer_id field in the customer table and also for the customer_id field in the order table
a. A primary key should be created for the order_id field in the customer table and also for the customer_id field in the order table
a. A primary key should be created for the customer_id field in the customer table and a foreign key should be created for the customer_id field in the order table
a. A primary key should be created for the customer_id field in the customer table and a foreign key should be created for the order_id field in the order table
a. None of these
76. 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:
a. The value displayed in the CALC_VALUE column will be lower
a. The value displayed in the CALC_VALUE column will be higher
a. There will be no difference in the value displayed in the CALC_VALUE column
a. An error will be reported
77. ___________ is the highest level of a transaction isolation implemented by SQL Server.
Answers:
a. Read Uncommitted
a. Read Committed
a. Repeatable Read
a. Serializable
78. Which of the following are aggregate functions in SQL?
Answers:
a. Avg
a. Select
a. Order By
a. Sum
a. Union
a. Group by
a. Having
79. What does BLOB stand for?
Answers:
a. Binary Large Object
a. Binary Lower Byte
a. Bit Large Object
a. Bit Lower Byte
80. Which of the following queries is valid?
Answers:
a. Select * from students where marks > avg(marks);
a. Select * from students order by marks where subject = ‘SQL’;
a. Select * from students having subject =’SQL’;
a. Select name from students group by subject, name;
a. Select group(*) from students;
a. Select name,avg(marks) from students;
a. None of the above
81. Examine the query:-

select (2/2/4) from tab1;

where tab1 is a table with one row. This would give a result of:
Answers:
a. 4
a. 2
a. 1
a. .5
a. .25
a. 0
a. 8
a. 24
82. Consider the following queries:

1. select * from employee where department LIKE “[^F-M]%”;
2. select * from employee where department = ‘[^F-M]%’;

Select the correct option:
Answers:
a. Query 2 will return an error
a. Both the queries will return the same set of records
a. Query 2 is perfectly correct
a. Query 2 would return one record less than Query 1
83. Consider the following statements and pick the correct answer:

1. ceiling() – returns the smallest integer greater than or equal to the specified value
2. floor() – returns the largest integer less than or equal to the specified value
Answers:
a. 1 is true and 2 is false
a. 1 is false and 2 is true
a. Both 1 and 2 are true
a. Both 1 and 2 are false
84. 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:
a. select * from Pers where joining_date from ‘1/1/2005’ to ‘1/2/2005′, job=’Analyst or clerk or salesman’
a. select * from Pers where joining_date between ‘1/1/2005’ to ‘1/2/2005′, job=’Analyst’ or job=’clerk’ or job=’salesman’
a. select * from Pers where joining_date between ‘1/1/2005’ and ‘1/2/2005′ and (job=’Analyst’ or job=’clerk’ or job=’salesman’)
a. None of the above
85. 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:
a. select AuthorName from Authors where AuthorId in (select AuthorId from Books group by AuthorId having count(*)>1)
a. select AuthorName from Authors, Books where Authors.AuthorId=Books.AuthorId and count(BookId)>1
a. select AuthorName from Authors, Books where Authors.AuthorId=Books.AuthorId group by AuthorName having count(*)>1
a. select AuthorName from Authors where AuthorId in (select AuthorId from Books having count(BookId)>1)
86. Which of the following statements are false?
Answers:
a. trim() function is used to remove leading and trailing spaces from a string
a. ltrim() function is used to remove leading spaces from a string
a. rtrim() function is used to remove trailing spaces from a string
a. alltrim() function is used to remove leading and trailing spaces from a string
87. Consider the following transaction code:

Begin Transaction
Update names_table set employee_name = “Ramesh” where employee_name = “Mahesh”
Save Transaction SAVE_POINT
Update salaries set salary=salary + 900 where employee_job = “Engineer”
Rollback transaction
Commit transaction

What will be the result produced by this transaction?
Answers:
a. “Ramesh” will be updated to “Mahesh”, but salaries of engineers will not be updated.
a. Neither “Ramesh” will be updated to “Mahesh”, nor the salary of engineers will be updated.
a. “Ramesh” will be updated to “Mahesh” and salary of engineers will also be updated.
a. “Mahesh” will be updated to “Ramesh”, but salaries of engineers will not be updated

Please follow and like to help us:

Leave a Reply

Your email address will not be published. Required fields are marked *