[麵試]DATABASE 麵試問題總結 zt 梅影

1.When designing a database table, how do you avoid missing column values for non-primary key columns?

Use DEFAULT and NOT NULL constraints

2.Which of the following is the syntax for creating an Index?

CREATE [UNIQUE] INDEX ON tbl_name (index_columns)

3.Which of the following is not a valid character datatype in SQL Server?
a.BLOB
b.CHAR
c.VARCHAR
d.TEXT
e.VARTEXT

VARTEXT

4.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?
a. "Ramesh” will be updated to “Mahesh”, but salaries of engineers will not be
nullb.Neither “Ramesh” will be updated to “Mahesh”, nor the salary of engineers will be updated.
c.“Ramesh” will be updated to “Mahesh” and salary of engineers will also be

Ramesh” will be updated to “Mahesh”, but salaries of engineers will not be
updated.


5.Which of the following constraints can be used to enforce the uniqueness of rows in a table?

a.DEFAULT and NOT NULL constraints
b.FOREIGN KEY constraints
c.PRIMARY KEY and UNIQUE constraints
d.IDENTITY columns
e.CHECK constraints

PRIMARY KEY and UNIQUE constraints
6.Which of the following are not date parts?

a.quarter
b.dayofweek
c.dayofyear
d.weekday

dayofweek

7. Which one of the following must be specified in every DELETE statement?

a.Table Name
b.Database name
c.LIMIT clause
d.WHERE clause
e.Column Names

Table Name

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

a.SELECT * FROM myTable WHERE column1 is null b.SELECT * FROM myTable WHERE column1 = null
c.SELECT * FROM myTable WHERE column1 EQUALS null
d.SELECT * FROM myTable WHERE column1 NOT null
e.SELECT * FROM myTable WHERE column1 CONTAINS null

SELECT * FROM myTable WHERE column1 is null

9. Is this statement true or false:
A cursor is a pointer that identifies a specific working row within a set

a.True
b.False

True

10. Which of the following commands is used to change the structure of table?

a.CHANGE TABLE
b.MODIFY TABLE
c.ALTER TABLE
d.UPDATE TABLE

ALTER Table 

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

a.1 is true and 2 is false
b.1 is false and 2 is true
c.Both 1 and 2 are true
d.Both 1 and 2 are false

Both 1 and 2 are true

12. What is the correct SQL syntax for returning all the columns from a table named “Persons” sorted REVERSE alphabetically by “FirstName”?

a.SELECT * FROM Persons WHERE FirstName ORDER BY FirstName DESC
b.SELECT * FROM Persons SORT REVERSE ‘FirstName’
c.SELECT * FROM Persons ORDER BY ‘FirstName’
d.SELECT * FROM Persons ORDER BY FirstName DESC

SELECT * FROM Persons ORDER BY FirstName DESC

13.What is the maximum value that can be stored for a datetime field?

a.Dec 31, 9999
b.Jun 6, 2079
c.Jan 1, 2753
d.Jan 1, 2100

Dec 31, 9999

14.How can you view the structure of a table named “myTable” in SQL Server?

a.desc myTable
b.desc table myTable
c.sp_columns myTable
d.None of the above
e.Using either option a or c

desc myTable

15.What does referential integrity (also called relational integrity) prevent?

a.Loss of data from employee sabotage
b.Loss of data from any one corrupted table
c.Recursive joins
d.One-to-many or many-to-many relationships between columns in a table
e.Data redundancy

One-to-many or many-to-many relationships between columns in a table 


 16.Which of the following is not a global variable?

a.@@colcount
b.@@error
c.@@rowcount
d.@@version
e.All are valid global variables

@@colcount

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

a.It will return the fields customer_id, customer_name, branch_name
b.It will return the fields customer_id, customer_name, branch_id, branch_name
c.It will return the fields customer_id, customer_name, branch_id, branch_name, branch_name
d.It will return an empty set since the two tables do not have any common field name
e.It will return an error since * is used alone for one table only

none of all

18. Which of the following is not a control statement?

a.if…else
b.if exists
c.do…while
d.while
e.begin…end

if exists

19.Which of the following datatypes is not supported by SQL-Server?

a.Character
b.Binary
c.Logical
d.Date
e.Numeric
f.All are supported

Logical

20.What will the output be if you try to perform arithmetic on NULL values?

a.0
b.NULL
c.It will generate an error message
d.Can’t be determined

NULL

21. Which of the following options is not correct about the DATEDIFF() function?

a.It returns the difference between parts of two specified dates
b.It takes three arguments
c.It returns a signed integer value equal to second date part minus first date part
d.It returns a signed integer value equal to first date part minus second date part

It returns a signed integer value equal to first date part minus second date part

22. Sample Code

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

Which one of the following is the correct syntax for adding the column named “column2a” after column2 to the table shown above?

a.ALTER TABLE table1 ADD column2a varchar(50) AFTER column2;
b.MODIFY TABLE table1 ADD column2a AFTER column2;
c.INSERT INTO table1 column2a AS varchar(50) AFTER column2;
d.ALTER TABLE table1 INSERT column2a varchar(50) AFTER column2;
e.CHANGE TABLE table1 INSERT column2a BEFORE column3;

none of all

23. Which of the following is not a valid binary datatype in SQL Server?

a.BINARY
b.VARBINARY
c.BIT
d.IMAGE
e.TESTAMP

TESTAMP IMAGE

24. Which of the following is not a valid Numeric datatypes in SQL Server?

a.INT
b.SMALLINT
c.TINYINT
d.BIGINT
e.MONEY

MONEY

25. Which of the following has the highest order of precedence?

a.Functions and Parenthesis
b.Multiplication, Division and Exponents
c.Addition and Subtraction
d.Logical Operations

Functions and Parenthesis