Explore topic-wise InterviewSolutions in .

This section includes InterviewSolutions, each offering curated multiple-choice questions to sharpen your knowledge and support exam preparation. Choose a topic below to get started.

1.

Which of the following statement drops the synonym?(a) DELETE SYNONYM(b) DROP SYNONYM(c) REMOVE SYNONYM(d) None of the mentionedThis question was posed to me during an interview.Enquiry is from Data Definition Language topic in portion Creating the Physical Database Schema of SQL Server

Answer»

Right option is (d) NONE of the mentioned

Explanation: DROP SYNONYM DELETES an existing synonym.
Discussion
2.

DDL statements not used for Synonym is __________(a) CREATE SYNONYM(b) DROP SYNONYM(c) ALTER SYNONYM(d) None of the mentionedI have been asked this question in a national level competition.This key question is from Data Definition Language topic in section Creating the Physical Database Schema of SQL Server

Answer»

The CORRECT CHOICE is (c) ALTER SYNONYM

For EXPLANATION I would say: Synonyms allow client applications to use the single-part name of a synonym to reference a base object.
Discussion
3.

Purpose of ALTER CERTIFICATE is __________(a) Adds a private key to a certificate(b) Changes the public key of a certificate(c) Removes the certificate from the database(d) None of the mentionedI have been asked this question in semester exam.The doubt is from Data Definition Language topic in chapter Creating the Physical Database Schema of SQL Server

Answer»

Correct choice is (a) ADDS a private KEY to a certificate

Explanation: ALTER CERTIFICATE changes the private key used to ENCRYPT a certificate.
Discussion
4.

Command used to modify the schema is __________(a) ALTER SCHEMA(b) ALTER STRUCTURE(c) ALTER VIEW(d) DBCC SHRINKDBI had been asked this question in an international level competition.My question is from Data Definition Language in chapter Creating the Physical Database Schema of SQL Server

Answer»

Right ANSWER is (a) ALTER SCHEMA

Easy EXPLANATION: ALTER SCHEMA CHANGES the OWNER of a schema.
Discussion
5.

Creation ofnew database role in the current database is made by __________(a) CREATE APPLICATION ROLE(b) CREATE ROLE(c) CREATE USER(d) CREATE LOGINThe question was posed to me in an interview for job.My query is from Data Definition Language topic in chapter Creating the Physical Database Schema of SQL Server

Answer» CORRECT CHOICE is (b) CREATE ROLE

To EXPLAIN: CREATE ROLE generates a new role in the DATABASE.
Discussion
6.

Statement used to remove a user from the current database is ____________(a) DROP ROLE(b) DROP USER(c) DROP LOGIN(d) None of the mentionedI got this question in examination.Query is from Data Definition Language in chapter Creating the Physical Database Schema of SQL Server

Answer»

The correct CHOICE is (b) DROP USER

For explanation: DROP USER is USED to delete the user account from the DATABASE.
Discussion
7.

Which of the following creates a new Microsoft Windows or SQL Server login account?(a) CREATE LOGIN(b) DROP LOGIN(c) UPDATE LOGIN(d) REMOVE LOGINI got this question in homework.This question is from Data Definition Language in section Creating the Physical Database Schema of SQL Server

Answer» RIGHT choice is (a) CREATE LOGIN

Best EXPLANATION: CREATE login generates a new SQL SERVER login.
Discussion
8.

Which is a new DDL statement?(a) DROP ROLES(b) CREATE APPLICATION ROLE(c) CREATE USERS(d) None of the mentionedThis question was posed to me by my college professor while I was bunking the class.I would like to ask this question from Data Definition Language in division Creating the Physical Database Schema of SQL Server

Answer»

The correct answer is (b) CREATE APPLICATION ROLE

To EXPLAIN: Application role CREATION changes the PROPERTIES of an application role.
Discussion
9.

Which of the following adds MOVE TO clause?(a) DROP INDEX(b) ALTER INDEX(c) UPDATE INDEX(d) CREATE INDEXThe question was posed to me by my college professor while I was bunking the class.Origin of the question is Data Definition Language in chapter Creating the Physical Database Schema of SQL Server

Answer»

Correct CHOICE is (a) DROP INDEX

Easy explanation: DROP INDEX STATEMENT ENABLES MOVING data from a clustered index to another filegroup or PARTITION scheme.
Discussion
10.

Which of the following statement modifies view index?(a) ALTER INDEX(b) ALTER VIEW INDEX(c) ALTER INDEX_VIEW(d) None of the mentionedI had been asked this question during an interview.Query is from Data Definition Language topic in division Creating the Physical Database Schema of SQL Server

Answer»

Right option is (a) ALTER INDEX

To explain I WOULD say: ALTER INDEX MODIFIES an existing table or VIEW index (RELATIONAL or XML) by disabling, rebuilding, or reorganizing the index; or by setting options on the index.
Discussion
11.

Which of the following index options are added by the CREATE INDEX statement?(a) XML INDEX(b) INCLUDED (column)(c) PARTITION SCHEME(d) ONLINEThe question was posed to me in an online quiz.The origin of the question is Data Definition Language topic in chapter Creating the Physical Database Schema of SQL Server

Answer»

Right option is (d) ONLINE

To EXPLAIN I would SAY: CREATE INDEX includes the following NEW SYNTAX ELEMENTS:XML INDEX, INCLUDED and PARTITION SCHEME.
Discussion
12.

Point out the wrong statement with respect to ALTER INDEX statement.(a) Adds new format for specifying index and table names(b) Adds the index option:MAXDOP(c) Adds the index option:ONLINE(d) None of the mentionedI got this question in an interview for job.This intriguing question comes from Data Definition Language in division Creating the Physical Database Schema of SQL Server

Answer»

Right ANSWER is (d) None of the mentioned

The BEST explanation: Options PROVIDED are features of DROP INDEX and CREATE index statements.
Discussion
13.

When does most ALTER DATABASE statements fail?(a) Database is in the RESTORING state(b) Database is in the STORING state(c) Database is in the RESTORE state(d) None of the mentionedThis question was addressed to me in class test.My question comes from Data Definition Language in chapter Creating the Physical Database Schema of SQL Server

Answer»

Right option is (a) DATABASE is in the RESTORING state

Best EXPLANATION: When a database is in RESTORING state then it means one or more files of the primary filegroup is been RESTORED or one or more secondary files are being resotored OFFLINE.
Discussion
14.

Command used to shrink the size of the database is _____________(a) DBCC SHRINK(b) SHRINKDATABASE(c) DBCC SHRINKDATABASE(d) DBCC SHRINKDBI got this question by my school principal while I was bunking the class.My doubt stems from Data Definition Language topic in chapter Creating the Physical Database Schema of SQL Server

Answer» RIGHT answer is (c) DBCC SHRINKDATABASE

Explanation: To DECREASE the size of a DATABASE, USE DBCC SHRINKDATABASE.
Discussion
15.

Maximum number of filegroups specified for each database is ___________(a) 32, 667(b) 32, 777(c) 32, 766(d) 32, 767I have been asked this question during a job interview.Origin of the question is Data Definition Language topic in chapter Creating the Physical Database Schema of SQL Server

Answer» CORRECT OPTION is (d) 32, 767

The explanation: A maximum of 32, 767 files and 32, 767 FILE groups can be specified for each database.
Discussion
16.

Point out the correct statement.(a) Database snapshots cannot be modified(b) Database snapshots can be modified(c) A collation name can be specified with the FOR ATTACH or FOR ATTACH_REBUILD_LOG clauses(d) None of the mentionedThe question was asked in final exam.Origin of the question is Data Definition Language in division Creating the Physical Database Schema of SQL Server

Answer» RIGHT OPTION is (a) Database SNAPSHOTS cannot be modified

To elaborate: A database snapshot is a read-only, STATIC VIEW of a SQL Server database.
Discussion
17.

Features provided by ALTER DATABASE statement do not include ____________(a) Deletes database(b) Modifies file(c) Changes the attributes of a database(d) Changes the database collationThe question was posed to me in final exam.Enquiry is from Data Definition Language in chapter Creating the Physical Database Schema of SQL Server

Answer» RIGHT option is (a) Deletes DATABASE

To elaborate: ALTER DATABASE is USED to modify the database.
Discussion
18.

The DROP DATABASE statement must run in ________ mode.(a) Autocommit(b) Commit(c) Readcommit(d) None of the mentionedThe question was asked in an internship interview.I would like to ask this question from Data Definition Language topic in section Creating the Physical Database Schema of SQL Server

Answer» RIGHT OPTION is (a) Autocommit

To EXPLAIN I would say: Autocommit MODE is the default transaction management mode.
Discussion
19.

Which of the following database should be backed up regularly?(a) Adventure Works(b) Pub(c) TEMPDB(d) MasterI got this question in semester exam.Question is from Data Definition Language in section Creating the Physical Database Schema of SQL Server

Answer»

The CORRECT answer is (d) Master

Explanation: When a DATABASE is dropped, the master database should be backed up.
Discussion
20.

Point out the wrong statement.(a) You cannot drop a database currently being used(b) A database cannot be dropped regardless of its state(c) Any database snapshots on a database must be dropped before the database can be dropped(d) None of the mentionedI had been asked this question in a job interview.This question is from Data Definition Language topic in division Creating the Physical Database Schema of SQL Server

Answer»

The CORRECT OPTION is (b) A DATABASE cannot be dropped regardless of its state

The best explanation: A database can be dropped regardless of its state: OFFLINE, read-only, suspect, and so on.
Discussion
21.

Syntax for dropping a database snapshot named “Sushant” would be ____________(a) DROP DATABASE Sushant(b) DROP DATABASE SNAP Sushant(c) DROP SNAPSHOT Sushant(d) None of the mentionedThe question was asked by my school teacher while I was bunking the class.I need to ask this question from Data Definition Language topic in section Creating the Physical Database Schema of SQL Server

Answer» CORRECT option is (a) DROP DATABASE Sushant

For explanation: DROP command removes database snapshot WITHOUT AFFECTING the source database.
Discussion
22.

Which of the following statement is used to create database snapshot?(a) CREATE DATABASE(b) CREATE SNAPSHOT(c) CREATE SNAPDB(d) None of the mentionedThe question was posed to me during an online exam.My question is from Data Definition Language in section Creating the Physical Database Schema of SQL Server

Answer» RIGHT CHOICE is (a) CREATE DATABASE

Easiest explanation: CREATE DATABASE statement creates a new database and the files used to store the database, creates a database snapshot, or ATTACHES a database from the detached files of a previously created database.
Discussion
23.

Which of the following statement removes sales and suppliers database?(a) DROP DATABASE Sales, NewSales(b) DROP DATABASE Sales, suppliers(c) DROP DATABASE Sales and suppliers(d) DROP DATABASE Sales-suppliersI have been asked this question during a job interview.I'd like to ask this question from Data Definition Language topic in portion Creating the Physical Database Schema of SQL Server

Answer»

Correct ANSWER is (b) DROP DATABASE Sales, suppliers

Explanation: DROP DATABASE can be used for removing multiple databases in one statement.
Discussion
24.

Which of the following database cannot be dropped?(a) TEMPDB(b) Master(c) MSDB(d) All of the mentionedI have been asked this question by my college professor while I was bunking the class.My doubt stems from Data Definition Language topic in section Creating the Physical Database Schema of SQL Server

Answer» CORRECT CHOICE is (d) All of the mentioned

Explanation: SYSTEM DATABASES cannot be DROPPED.
Discussion
25.

Which is the minimal permission that a user must have to drop the database?(a) READ(b) WRITE(c) VIEW(d) CONTROLThis question was addressed to me in an international level competition.My question comes from Data Definition Language topic in division Creating the Physical Database Schema of SQL Server

Answer»

Right ANSWER is (d) CONTROL

The explanation is: To execute DROP DATABASE, at a MINIMUM, a USER must have CONTROL permission on the database.
Discussion
26.

Point out the correct statement.(a) When a database is dropped, the master database should be backed up(b) You can drop a database currently being used(c) Dropping a database snapshot does not delete the database snapshot from an instance of SQL Server(d) None of the mentionedI got this question during an interview for a job.This intriguing question originated from Data Definition Language in chapter Creating the Physical Database Schema of SQL Server

Answer»

The correct answer is (a) When a DATABASE is dropped, the MASTER database should be backed up

Explanation: Master database records all of the system LEVEL INFORMATION for a SQL Server.
Discussion
27.

Which of the following statement removes database including its related components?(a) DROP DATABASE(b) DELETE DATABASE(c) REMOVE DATABASE(d) None of the mentionedI have been asked this question in an interview for job.This key question is from Data Definition Language in chapter Creating the Physical Database Schema of SQL Server

Answer» RIGHT choice is (a) DROP DATABASE

For EXPLANATION: DROP statement adds support for DROPPING database SNAPSHOTS.
Discussion
28.

Which of the following item is provided by CREATE DATABASE statement?(a) Service Broker options(b) ALLOW_SNAPSHOT_ISOLATION(c) DB_CHAINING(d) Database mirroringThis question was posed to me in unit test.The question is from Data Definition Language topic in division Creating the Physical Database Schema of SQL Server

Answer»

Correct answer is (a) SERVICE BROKER options

Explanation: CREATE DATABASE is USED mainly for CREATING new database.
Discussion
29.

What options did ALTER DATABASE statement add?(a) DATABASE SNAPSHOT syntax(b) ATTACH_REBUILD_LOG clause(c) DB_CHAINING option(d) READ_COMMITTED_SNAPSHOTI got this question by my college professor while I was bunking the class.The above asked question is from Data Definition Language topic in division Creating the Physical Database Schema of SQL Server

Answer» RIGHT OPTION is (d) READ_COMMITTED_SNAPSHOT

For explanation: ALTER DATABASE is used for INSTANT file initialization when adding or growing DATA files.
Discussion