MCQs on Data Types and Constraints | PostgreSQL Database
This comprehensive set of 30 MCQs focuses on PostgreSQL’s data types and constraints, covering common data types, user-defined types, NULL/NOT NULL handling, and essential constraints like primary keys, foreign keys, and more. Perfect for exams and practical learning.
Common Data Types (Text, Numeric, Date/Time, etc.)
- Which of the following is a valid data type in PostgreSQL for storing textual data?
- A) VARCHAR
- B) TEXT
- C) CHAR
- D) All of the above
- What is the difference between CHAR and VARCHAR in PostgreSQL?
- A) CHAR is variable-length, VARCHAR is fixed-length
- B) CHAR is fixed-length, VARCHAR is variable-length
- C) Both are fixed-length
- D) Both are variable-length
- Which data type is used for storing whole numbers in PostgreSQL?
- A) FLOAT
- B) DECIMAL
- C) INTEGER
- D) CHAR
- Which data type in PostgreSQL is best suited for storing monetary values?
- A) NUMERIC
- B) MONEY
- C) FLOAT
- D) DOUBLE PRECISION
- Which of the following is a valid Date/Time data type in PostgreSQL?
- A) DATE
- B) TIME
- C) TIMESTAMP
- D) All of the above
- What does the BOOLEAN data type in PostgreSQL store?
- A) True or False
- B) Yes or No
- C) 0 or 1
- D) All of the above
User-Defined Data Types
- How can you create a user-defined data type in PostgreSQL?
- A) Using CREATE TYPE statement
- B) Using CREATE TABLE statement
- C) Using ALTER TABLE statement
- D) Using CREATE DOMAIN statement
- Which of the following is a key advantage of user-defined data types?
- A) Increased storage efficiency
- B) Custom validation rules
- C) Built-in constraints
- D) Automatic indexing
- What are enumerated (ENUM) data types in PostgreSQL?
- A) Data types for storing numerical ranges
- B) Data types that map to arrays
- C) Data types for storing predefined constant values
- D) Data types for storing JSON objects
- Which command is used to create an ENUM data type in PostgreSQL?
- A) CREATE DOMAIN
- B) CREATE ENUM
- C) CREATE TYPE
- D) CREATE TABLE
- Can user-defined data types in PostgreSQL be altered after creation?
- A) Yes, using the ALTER TYPE command
- B) Yes, using the ALTER TABLE command
- C) No, they cannot be changed
- D) Only by dropping and recreating
- Which of the following is NOT a user-defined data type in PostgreSQL?
- A) ENUM
- B) DOMAIN
- C) CUSTOM TABLE
- D) COMPOSITE
NULL and NOT NULL
- What does a NULL value in PostgreSQL signify?
- A) 0
- B) Empty string
- C) Undefined or missing value
- D) False
- Which constraint ensures that a column cannot store NULL values?
- A) UNIQUE
- B) PRIMARY KEY
- C) NOT NULL
- D) CHECK
- If a column is defined as NOT NULL, what happens when you try to insert a NULL value?
- A) The NULL value is stored
- B) The row is rejected
- C) The value is replaced with 0
- D) The column is left blank
- What is the default behavior of a column in PostgreSQL regarding NULL values?
- A) Allows NULL values
- B) Disallows NULL values
- C) Requires a DEFAULT value
- D) Depends on the data type
- Which SQL keyword checks for NULL values in PostgreSQL?
- A) = NULL
- B) IS NULL
- C) NULL CHECK
- D) IS NOT NULL
- How can you prevent NULL values in multiple columns of a table?
- A) Add a NOT NULL constraint to each column
- B) Use a CHECK constraint
- C) Use a UNIQUE constraint
- D) Use a FOREIGN KEY constraint
Primary Key, Foreign Key, Unique, Check, and Default Constraints
- What is the primary purpose of a PRIMARY KEY in PostgreSQL?
- A) To enforce unique values in a column
- B) To establish relationships between tables
- C) To identify rows uniquely in a table
- D) To provide a default value
- Can a table have multiple PRIMARY KEY constraints in PostgreSQL?
- A) Yes
- B) No
- C) Only if combined with FOREIGN KEY
- D) Depends on the table design
- What is the difference between PRIMARY KEY and UNIQUE constraints?
- A) PRIMARY KEY allows NULL values; UNIQUE does not
- B) UNIQUE allows NULL values; PRIMARY KEY does not
- C) Both are identical in PostgreSQL
- D) UNIQUE applies to rows, PRIMARY KEY to columns
- What does a FOREIGN KEY constraint do in PostgreSQL?
- A) Enforces unique values in a column
- B) Links two tables based on a reference column
- C) Prevents NULL values
- D) Sets a default value for a column
- Which of the following constraints is used to enforce a specific range of values?
- A) PRIMARY KEY
- B) CHECK
- C) DEFAULT
- D) UNIQUE
- How does the DEFAULT constraint work in PostgreSQL?
- A) Ensures the column is not NULL
- B) Provides a default value if none is specified
- C) Limits the values in the column to a predefined list
- D) Enforces relationships between tables
- What is the key difference between CHECK and UNIQUE constraints?
- A) CHECK applies to ranges, UNIQUE applies to duplicates
- B) UNIQUE applies to ranges, CHECK applies to duplicates
- C) Both have the same functionality
- D) CHECK is for relationships, UNIQUE for rows
- Which of the following is true about composite PRIMARY KEYs?
- A) They involve multiple columns
- B) They apply only to one column
- C) They are used for user-defined data types
- D) They cannot be used with FOREIGN KEYs
- Can a column have both a DEFAULT and NOT NULL constraint?
- A) Yes
- B) No
- C) Only with PRIMARY KEY
- D) Only with UNIQUE
- What happens if a value violates a CHECK constraint during insertion?
- A) The value is accepted
- B) The value is replaced with NULL
- C) The operation is rejected
- D) The constraint is ignored
- What does the ON DELETE CASCADE option do for a FOREIGN KEY?
- A) Deletes all rows in the parent table
- B) Automatically deletes rows in the child table
- C) Prevents deletion of the referenced row
- D) Ignores the delete operation
- Which statement about UNIQUE constraints is true?
- A) They allow multiple NULL values
- B) They allow only one NULL value
- C) They disallow all NULL values
- D) They enforce relationships between tables
Answer Table
| Qno | Answer |
|---|---|
| 1 | D) All of the above |
| 2 | B) CHAR is fixed-length, VARCHAR is variable-length |
| 3 | C) INTEGER |
| 4 | B) MONEY |
| 5 | D) All of the above |
| 6 | D) All of the above |
| 7 | A) Using CREATE TYPE statement |
| 8 | B) Custom validation rules |
| 9 | C) Data types for storing predefined constant values |
| 10 | C) CREATE TYPE |
| 11 | A) Yes, using the ALTER TYPE command |
| 12 | C) CUSTOM TABLE |
| 13 | C) Undefined or missing value |
| 14 | C) NOT NULL |
| 15 | B) The row is rejected |
| 16 | A) Allows NULL values |
| 17 | B) IS NULL |
| 18 | A) Add a NOT NULL constraint to each column |
| 19 | C) To identify rows uniquely in a table |
| 20 | B) No |
| 21 | B) UNIQUE allows NULL values; PRIMARY KEY does not |
| 22 | B) Links two tables based on a reference column |
| 23 | B) CHECK |
| 24 | B) Provides a default value if none is specified |
| 25 | A) CHECK applies to ranges, UNIQUE applies to duplicates |
| 26 | A) They involve multiple columns |
| 27 | A) Yes |
| 28 | C) The operation is rejected |
| 29 | B) Automatically deletes rows in the child table |
| 30 | A) They allow multiple NULL values |