MCQs on Database Basics | SQL Server

nderstanding database basics in SQL Server is essential for managing data effectively. These 30 MCQs cover key concepts such as creating databases, table structures, keys, and database schemas.

---

Database Basics | SQL Server

Creating Databases

1. Which command is used to create a new database in SQL Server?
   • a) CREATE TABLE
   • b) CREATE DATABASE
   • c) ADD DATABASE
   • d) CREATE SCHEMA
2. What will happen if a CREATE DATABASE statement is executed without specifying file size?
   • a) SQL Server throws an error
   • b) Default file sizes are used
   • c) The database is created with zero file size
   • d) A prompt appears for manual size entry
3. Which of the following options is mandatory in the CREATE DATABASE syntax?
   • a) The database name
   • b) The initial size of the database
   • c) The maximum size of the database
   • d) The file growth percentage
4. What is the default location for database files in SQL Server?
   • a) C:\ProgramData\SQLServer
   • b) C:\Program Files\Microsoft SQL Server\MSSQL\Data
   • c) D:\SQLData
   • d) Configurable by the user during installation
5. What does the AUTOGROW option in a CREATE DATABASE statement determine?
   • a) Whether the database can shrink
   • b) How the database files expand when full
   • c) The size of the log file
   • d) The default backup size

Understanding Tables, Columns, and Data Types

6. Which statement is used to create a table in SQL Server?
   • a) INSERT INTO
   • b) CREATE TABLE
   • c) ADD TABLE
   • d) ALTER TABLE
7. Which data type should be used for storing monetary values?
   • a) FLOAT
   • b) DECIMAL
   • c) MONEY
   • d) INT
8. Which of the following column definitions is invalid in SQL Server?
   • a) FirstName NVARCHAR(50)
   • b) Price MONEY NOT NULL
   • c) DateOfBirth DATE DEFAULT GETDATE()
   • d) Address VARCHAR
9. What is the maximum storage size for a VARCHAR(MAX) column?
   • a) 1 MB
   • b) 2 GB
   • c) 4 GB
   • d) Unlimited
10. What does the IDENTITY property on a column signify?
    • a) The column is a primary key
    • b) The column value will auto-increment
    • c) The column is unique
    • d) The column cannot contain NULL values

Primary Keys and Foreign Keys

11. What is the primary purpose of a primary key in a table?
    • a) To link two tables
    • b) To ensure unique identification of each row
    • c) To allow NULL values in columns
    • d) To optimize query performance
12. Which of the following constraints is required to enforce a primary key?
    • a) UNIQUE
    • b) DEFAULT
    • c) PRIMARY KEY
    • d) CHECK
13. What happens if a foreign key constraint is violated?
    • a) The operation proceeds without error
    • b) The operation is rolled back
    • c) The database is locked for updates
    • d) The foreign key is removed
14. What is required when defining a foreign key constraint?
    • a) Both tables must have the same primary key
    • b) The foreign key column must reference a primary or unique key
    • c) The foreign key column must allow NULL values
    • d) Both tables must belong to the same schema
15. Which statement is correct regarding composite primary keys?
    • a) A composite primary key can consist of up to two columns only
    • b) A composite primary key ensures uniqueness across all specified columns
    • c) Composite keys are slower than single-column primary keys
    • d) A composite primary key must reference a foreign key

Database Schemas and Ownership

16. What is the default schema for a new user in SQL Server?
    • a) SYS
    • b) PUBLIC
    • c) DBO
    • d) ADMIN
17. What command is used to change the schema of a table in SQL Server?
    • a) ALTER SCHEMA
    • b) UPDATE TABLE SCHEMA
    • c) ALTER TABLE MOVE TO SCHEMA
    • d) ALTER TABLE SET SCHEMA
18. Which statement is true about schemas in SQL Server?
    • a) Schemas are database-level roles
    • b) Schemas group database objects logically
    • c) Schemas must match user accounts
    • d) A database can only have one schema
19. Which of the following is NOT a benefit of using schemas?
    • a) Improved security and access control
    • b) Logical organization of database objects
    • c) Faster query execution
    • d) Easier management of database objects
20. What happens if a user with schema ownership is deleted without transferring ownership?
    • a) The schema is deleted
    • b) Ownership reverts to the dbo
    • c) The operation is not allowed
    • d) All objects in the schema become inaccessible
21. Which command lists all schemas in the current database?
    • a) SELECT * FROM SYS.SCHEMAS
    • b) EXEC SP_LIST_SCHEMAS
    • c) SELECT * FROM INFORMATION_SCHEMA.TABLES
    • d) SHOW SCHEMAS
22. What does the ALTER SCHEMA command do?
    • a) Drops the specified schema
    • b) Modifies the properties of a schema
    • c) Moves database objects between schemas
    • d) Grants schema-level permissions
23. What is the primary difference between a schema and a database?
    • a) A schema is a container within a database
    • b) A database contains multiple schemas and system objects
    • c) A database is owned by a user, while a schema is not
    • d) Both a and b
24. What happens when a table is created without specifying a schema?
    • a) It is placed in the dbo schema by default
    • b) The table creation fails
    • c) The user is prompted to specify a schema
    • d) The table is created under the system schema
25. Which of the following is NOT true about schemas?
    • a) Schemas improve security by separating objects
    • b) Schemas can have their own permissions
    • c) Every schema must have a unique owner
    • d) Schemas enforce referential integrity

---

Answer Key

Qno	Answer (Option with text)
1	b) CREATE DATABASE
2	b) Default file sizes are used
3	a) The database name
4	b) C:\Program Files\Microsoft SQL Server\MSSQL\Data
5	b) How the database files expand when full
6	b) CREATE TABLE
7	c) MONEY
8	c) DateOfBirth DATE DEFAULT GETDATE()
9	b) 2 GB
10	b) The column value will auto-increment
11	b) To ensure unique identification of each row
12	c) PRIMARY KEY
13	b) The operation is rolled back
14	b) The foreign key column must reference a primary or unique key
15	b) A composite primary key ensures uniqueness across all columns
16	c) DBO
17	a) ALTER SCHEMA
18	b) Schemas group database objects logically
19	c) Faster query execution
20	c) The operation is not allowed
21	a) SELECT * FROM SYS.SCHEMAS
22	c) Moves database objects between schemas
23	d) Both a and b
24	a) It is placed in the dbo schema by default
25	d) Schemas enforce referential integrity