CompTIA DataSys+ (DS0-001)Database FundamentalsMedium
A data architect is designing a database for a new e-commerce platform. They need to create a table named `Products` to store product information, including a unique `ProductID` (integer, primary key), `ProductName` (variable characters, cannot be null), `Price` (decimal with 2 decimal places, cannot be null), and `StockQuantity` (integer, defaults to 0). Which SQL DDL statement correctly defines this table?
- ACREATE TABLE Products ( ProductID INTEGER PRIMARY KEY, ProductName TEXT NOT NULL, Price FLOAT(10,2) NOT NULL, StockQuantity INT DEFAULT '0' );
- BADD TABLE Products ( ProductID INT PRIMARY KEY, ProductName VARCHAR(255) NOT NULL, Price DECIMAL(10,2) NOT NULL, StockQuantity INT DEFAULT 0 );
- CMAKE TABLE Products ( ProductID INT PRIMARY KEY, ProductName STRING NOT NULL, Price MONEY NOT NULL, StockQuantity INTEGER DEFAULT 0 );
- DCREATE TABLE Products ( ProductID INT PRIMARY KEY, ProductName VARCHAR(255) NOT NULL, Price DECIMAL(10,2) NOT NULL, StockQuantity INT DEFAULT 0 );
Show answer & explanationAnswer & explanation
Correct answer: D. CREATE TABLE Products ( ProductID INT PRIMARY KEY, ProductName VARCHAR(255) NOT NULL, Price DECIMAL(10,2) NOT NULL, StockQuantity INT DEFAULT 0 );
Option A uses the correct SQL DDL syntax for creating a table and accurately specifies the data types, constraints (PRIMARY KEY, NOT NULL), and default value for each column as described in the scenario.
Why the other options are wrong
- A. `FLOAT` is less precise than `DECIMAL` for currency, and `DEFAULT '0'` assigns a string default, not an integer.
- B. `ADD TABLE` is not a valid SQL DDL command for creating a new table; `CREATE TABLE` is used.
- C. `MAKE TABLE` and `STRING`/`MONEY` are not standard SQL DDL keywords or data types across all major RDBMS.
CREATE TABLE Statement
A SQL DDL command used to define a new table in a relational database, specifying its name, column names, data types, and constraints.
- Defines the table's structure.
- Includes column definitions with data types (e.g., INT, VARCHAR, DECIMAL).
- Allows specifying constraints like PRIMARY KEY, NOT NULL, DEFAULT.
Memory trick: CREATE a TABLE with COLUMNS and their TYPES, then ADD CONSTRAINTS!