Lecture Notes Of Class 4: Data Types and Table Creation

 

Lecture Notes Of Class 4: Data Types and Table Creation

Objective:

• To understand MySQL data types and their uses.
• To create tables with appropriate data types and constraints.

Outcome:

• Students will be able to create tables using various data types and constraints in MySQL.

---

Introduction to MySQL Data Types

In MySQL, data types are used to specify the type of data that can be stored in a column. Choosing the correct data type is important because it optimizes storage, enforces data integrity, and improves performance.

There are several categories of data types in MySQL:

1.   Numeric Data Types: For storing numbers, both integer and floating-point.

2.   String Data Types: For storing text and string values.

3.   Date and Time Data Types: For storing dates, times, and timestamps.

4.   Other Data Types: For specific uses, like JSON or binary data.

1. Numeric Data Types

Numeric data types store numbers. MySQL supports both integer and floating-point numbers.

Integer Types

• TINYINT: Integer data type with a small range (-128 to 127).
• SMALLINT: Integer data type with a medium range (-32,768 to 32,767).
• MEDIUMINT: Integer data type with a larger range (-8,388,608 to 8,388,607).
• INT or INTEGER: Standard integer data type (-2,147,483,648 to 2,147,483,647).
• BIGINT: Large integer data type for very large numbers (-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807).

Floating-Point Types

• FLOAT: For storing floating-point numbers with single precision (about 7 decimal places).
• DOUBLE: For storing floating-point numbers with double precision (about 15 decimal places).
• DECIMAL: For exact numeric values, typically used for financial calculations. It requires two values: precision and scale (e.g., DECIMAL(10,2) can store 10 digits, with 2 digits after the decimal point).

Examples:

sql

CopyEdit

CREATE TABLE employees (

    id INT,

    salary DECIMAL(10, 2)

);

---

2. String Data Types

String data types store text and string values. The most commonly used string types are:

CHAR and VARCHAR

• CHAR(n): Stores a fixed-length string. If the string is shorter than the defined length, it will be padded with spaces.
• VARCHAR(n): Stores a variable-length string. It only uses as much space as needed.

TEXT Types

• TEXT: For long text data (up to 65,535 characters).
• TINYTEXT: A shorter version of TEXT, for storing up to 255 characters.
• MEDIUMTEXT: A medium-length text for storing up to 16,777,215 characters.
• LONGTEXT: For extremely long text, storing up to 4,294,967,295 characters.

BINARY Types

• BINARY(n): Fixed-length binary data.
• VARBINARY(n): Variable-length binary data.

Example:

sql

CopyEdit

CREATE TABLE users (

    username VARCHAR(50),

    email VARCHAR(100)

);

---

3. Date and Time Data Types

Date and time data types are used to store date and time information.

• DATE: Stores a date in the format YYYY-MM-DD.
• TIME: Stores time in the format HH:MM:SS.
• DATETIME: Stores both date and time in the format YYYY-MM-DD HH:MM:SS.
• TIMESTAMP: Similar to DATETIME, but also tracks the time the record was last modified.
• YEAR: Stores a year in YYYY format.

Example:

sql

CopyEdit

CREATE TABLE events (

    event_name VARCHAR(100),

    event_date DATE

);

---

4. Other Data Types

MySQL also supports other data types for specific needs.

• ENUM: A string object that can only take one value from a predefined list of possible values.
• SET: Similar to ENUM, but can store multiple values from the predefined list.

Example of ENUM:

sql

CopyEdit

CREATE TABLE products (

    product_name VARCHAR(100),

    availability ENUM('in stock', 'out of stock')

);

Example of SET:

sql

CopyEdit

CREATE TABLE users (

    user_name VARCHAR(100),

    preferences SET('email', 'sms', 'push')

);

---

Table Creation in MySQL

To create a table in MySQL, you use the CREATE TABLE statement. The basic syntax for creating a table is:

sql

CopyEdit

CREATE TABLE table_name (

    column1 datatype CONSTRAINTS,

    column2 datatype CONSTRAINTS,

    ...

);

Steps to Create a Table:

1.   Define the table name: Choose a unique name for the table.

2.   Specify columns: Each table must have one or more columns.

3.   Set data types: Define the data type for each column.

4.   Apply constraints: Constraints are rules to enforce data integrity.

---

MySQL Constraints

Constraints are used to define rules for data in a table. The common types of constraints are:

• NOT NULL: Ensures that a column cannot have a NULL value.
• UNIQUE: Ensures that all values in a column are unique.
• PRIMARY KEY: Uniquely identifies each record in a table. It is a combination of NOT NULL and UNIQUE.
• FOREIGN KEY: Ensures that values in a column match values in another table, establishing a relationship between tables.
• DEFAULT: Sets a default value for a column when no value is provided.
• CHECK: Ensures that values in a column satisfy a specific condition.

Example:

sql

CopyEdit

CREATE TABLE customers (

    id INT PRIMARY KEY,

    name VARCHAR(100) NOT NULL,

    email VARCHAR(100) UNIQUE,

    date_of_birth DATE,

    country VARCHAR(50) DEFAULT 'India'

);

---

Practical Example:

Let's create a simple students table to understand the process of defining data types and constraints.

sql

CopyEdit

CREATE TABLE students (

    student_id INT PRIMARY KEY,

    first_name VARCHAR(50) NOT NULL,

    last_name VARCHAR(50) NOT NULL,

    date_of_birth DATE,

    email VARCHAR(100) UNIQUE,

    enrollment_date DATETIME DEFAULT CURRENT_TIMESTAMP

);

• student_id: An integer value that uniquely identifies each student (Primary Key).
• first_name and last_name: Text fields that cannot be NULL.
• date_of_birth: A date field to store the student’s date of birth.
• email: A unique email address for each student.
• enrollment_date: A datetime field that defaults to the current timestamp when the record is created.

---

Conclusion

In this class, we learned about:

1.   MySQL Data Types: Numeric, string, date/time, and other specialized data types.

2.   Table Creation: How to define tables with various data types.

3.   Constraints: How to enforce rules on data using constraints like PRIMARY KEY, NOT NULL, UNIQUE, and more.

By understanding the data types and constraints, students can effectively design database tables and ensure data integrity.

---

Exercises:

1.   Create a products table with the following fields:

o    product_id (integer, primary key)

o    product_name (varchar, not null)

o    price (decimal, not null)

o    stock_quantity (integer)

o    manufacture_date (date)

2.   Create a students table with the following:

o    student_id (integer, primary key)

o    first_name (varchar, not null)

o    last_name (varchar, not null)

o    grade (enum, values: 'A', 'B', 'C', 'D', 'F')

o    enrolled (datetime, default to current timestamp)

3.   Create a users table that stores:

o    user_id (integer, primary key)

o    username (varchar, unique, not null)

o    password (varchar, not null)

o    email (varchar, unique)

o    date_of_birth (date)

---

By completing these exercises, students will gain practical experience in creating tables with proper data types and constraints.

4o mini

You said:

Answer this ...Exercises: Create a products table with the following fields: product_id (integer, primary key) product_name (varchar, not null) price (decimal, not null) stock_quantity (integer) manufacture_date (date) Create a students table with the following: student_id (integer, primary key) first_name (varchar, not null) last_name (varchar, not null) grade (enum, values: 'A', 'B', 'C', 'D', 'F') enrolled (datetime, default to current timestamp) Create a users table that stores: user_id (integer, primary key) username (varchar, unique, not null) password (varchar, not null) email (varchar, unique) date_of_birth (date)

ChatGPT said:

ChatGPT

Here are the SQL statements to create the tables based on the given specifications:

1. Products Table

sql

CopyEdit

CREATE TABLE products (

    product_id INT PRIMARY KEY,              -- product_id as the primary key (integer type)

    product_name VARCHAR(100) NOT NULL,       -- product_name as a varchar, cannot be NULL

    price DECIMAL(10, 2) NOT NULL,            -- price as a decimal type (10 digits, 2 after decimal)

    stock_quantity INT,                       -- stock_quantity as an integer

    manufacture_date DATE                     -- manufacture_date as a date type

);

2. Students Table

sql

CopyEdit

CREATE TABLE students (

    student_id INT PRIMARY KEY,              -- student_id as the primary key (integer type)

    first_name VARCHAR(50) NOT NULL,          -- first_name as varchar (cannot be NULL)

    last_name VARCHAR(50) NOT NULL,           -- last_name as varchar (cannot be NULL)

    grade ENUM('A', 'B', 'C', 'D', 'F'),      -- grade as ENUM with predefined values

    enrolled DATETIME DEFAULT CURRENT_TIMESTAMP  -- enrolled as datetime, defaults to current timestamp

);

3. Users Table

sql

CopyEdit

CREATE TABLE users (

    user_id INT PRIMARY KEY,                 -- user_id as the primary key (integer type)

    username VARCHAR(50) UNIQUE NOT NULL,     -- username as varchar, unique and cannot be NULL

    password VARCHAR(255) NOT NULL,           -- password as varchar, cannot be NULL

    email VARCHAR(100) UNIQUE,                -- email as varchar, unique

    date_of_birth DATE                        -- date_of_birth as date type

);

---

Explanation:

• product_id, student_id, and user_id are set as primary keys, ensuring they are unique identifiers for each record in their respective tables.
• VARCHAR is used for text fields like product_name, first_name, last_name, username, email, and password.
• DECIMAL(10, 2) is used for price, specifying that the field can hold up to 10 digits, with 2 digits after the decimal point.
• INT is used for integer fields like stock_quantity, student_id, and user_id.
• ENUM is used in the students table to define possible values for the grade field ('A', 'B', 'C', 'D', 'F').
• DATETIME is used for the enrolled field in the students table, with a default value of the current timestamp.
• DATE is used for manufacture_date and date_of_birth fields to store dates without time values.

These SQL queries will create the requested tables with the specified data types and constraints.

Assignment 1: Understanding and Using INT Data Type

Objective:
To understand the use of the INT data type for storing integer values.

Task:
Create a table named students to store student information. The table should have the following columns:

• id (INT)
• name (VARCHAR)
• age (INT)
• enrollment_date (DATE)

Solution:

sql

CopyEdit

CREATE TABLE students (

    id INT NOT NULL AUTO_INCREMENT,

    name VARCHAR(100),

    age INT,

    enrollment_date DATE,

    PRIMARY KEY (id)

);

Explanation:

• The id column is of type INT, which stores integer values. We have set it as AUTO_INCREMENT, so MySQL automatically generates a unique value for each row.
• name is of type VARCHAR, which stores variable-length strings.
• age is an integer data type.
• enrollment_date stores the date the student enrolled in the course.

Expected Output:
The table will be created without errors. You can check it by running:

sql

CopyEdit

DESCRIBE students;

---

Assignment 2: Using DECIMAL Data Type for Financial Data

Objective:
To store financial information using the DECIMAL data type for precision.

Task:
Create a table named products that holds product data with the following columns:

• product_id (INT)
• product_name (VARCHAR)
• price (DECIMAL)
• quantity (INT)

Solution:

sql

CopyEdit

CREATE TABLE products (

    product_id INT NOT NULL AUTO_INCREMENT,

    product_name VARCHAR(100),

    price DECIMAL(10, 2),

    quantity INT,

    PRIMARY KEY (product_id)

);

Explanation:

• price is defined as DECIMAL(10, 2), which means it can store up to 10 digits, with 2 digits after the decimal point (e.g., 99999999.99).

Expected Output:
The table products is created, and you can check it by running:

sql

CopyEdit

DESCRIBE products;

---

Assignment 3: Using VARCHAR and CHAR Data Types

Objective:
Learn how to use VARCHAR and CHAR for storing text.

Task:
Create a table employees with the following columns:

• employee_id (INT)
• first_name (VARCHAR)
• last_name (CHAR)
• email (VARCHAR)

Solution:

sql

CopyEdit

CREATE TABLE employees (

    employee_id INT NOT NULL AUTO_INCREMENT,

    first_name VARCHAR(50),

    last_name CHAR(50),

    email VARCHAR(100),

    PRIMARY KEY (employee_id)

);

Explanation:

• first_name and email use VARCHAR, which is more flexible for varying string lengths.
• last_name uses CHAR, which is fixed-length (it always takes up 50 characters).

Expected Output:
You can verify the table creation with:

sql

CopyEdit

DESCRIBE employees;

---

Assignment 4: Using DATE and DATETIME Data Types

Objective:
Store date and time data using DATE and DATETIME.

Task:
Create a table events with the following columns:

• event_id (INT)
• event_name (VARCHAR)
• event_date (DATE)
• event_time (DATETIME)

Solution:

sql

CopyEdit

CREATE TABLE events (

    event_id INT NOT NULL AUTO_INCREMENT,

    event_name VARCHAR(100),

    event_date DATE,

    event_time DATETIME,

    PRIMARY KEY (event_id)

);

Explanation:

• event_date stores just the date (year, month, day).
• event_time stores both the date and time.

Expected Output:
Check the table by running:

sql

CopyEdit

DESCRIBE events;

---

Assignment 5: Using ENUM Data Type

Objective:
Use the ENUM data type for restricting a column to a set of predefined values.

Task:
Create a table courses with the following columns:

• course_id (INT)
• course_name (VARCHAR)
• course_type (ENUM: 'Online', 'Offline')

Solution:

sql

CopyEdit

CREATE TABLE courses (

    course_id INT NOT NULL AUTO_INCREMENT,

    course_name VARCHAR(100),

    course_type ENUM('Online', 'Offline'),

    PRIMARY KEY (course_id)

);

Explanation:

• course_type uses the ENUM data type to limit the values to 'Online' or 'Offline'.

Expected Output:
Check the table with:

sql

CopyEdit

DESCRIBE courses;

---

Assignment 6: Using BOOLEAN Data Type

Objective:
Learn how to store boolean values.

Task:
Create a table users with the following columns:

• user_id (INT)
• username (VARCHAR)
• is_active (BOOLEAN)

Solution:

sql

CopyEdit

CREATE TABLE users (

    user_id INT NOT NULL AUTO_INCREMENT,

    username VARCHAR(100),

    is_active BOOLEAN,

    PRIMARY KEY (user_id)

);

Explanation:

• is_active stores a boolean value, where 0 represents false and 1 represents true.

Expected Output:
Check the table by running:

sql

CopyEdit

DESCRIBE users;

---

Assignment 7: Using TINYINT Data Type

Objective:
Use the TINYINT data type for storing very small integer values.

Task:
Create a table ratings with the following columns:

• rating_id (INT)
• rating_value (TINYINT)

Solution:

sql

CopyEdit

CREATE TABLE ratings (

    rating_id INT NOT NULL AUTO_INCREMENT,

    rating_value TINYINT,

    PRIMARY KEY (rating_id)

);

Explanation:

• TINYINT is used to store small integers (-128 to 127).

Expected Output:
Verify table creation with:

sql

CopyEdit

DESCRIBE ratings;

---

Assignment 8: Using TEXT Data Type for Large Text

Objective:
Learn how to use the TEXT data type for storing long strings of text.

Task:
Create a table articles with the following columns:

• article_id (INT)
• title (VARCHAR)
• content (TEXT)

Solution:

sql

CopyEdit

CREATE TABLE articles (

    article_id INT NOT NULL AUTO_INCREMENT,

    title VARCHAR(255),

    content TEXT,

    PRIMARY KEY (article_id)

);

Explanation:

• content is defined as TEXT, allowing storage of long paragraphs of text.

Expected Output:
Check the table by running:

sql

CopyEdit

DESCRIBE articles;

---

Assignment 9: Using PRIMARY KEY Constraint

Objective:
Understand the use of the PRIMARY KEY constraint.

Task:
Create a table orders with the following columns:

• order_id (INT, PRIMARY KEY)
• customer_id (INT)
• order_date (DATE)

Solution:

sql

CopyEdit

CREATE TABLE orders (

    order_id INT PRIMARY KEY,

    customer_id INT,

    order_date DATE

);

Explanation:

• order_id is the primary key, which uniquely identifies each row in the table.

Expected Output:
Check table structure with:

sql

CopyEdit

DESCRIBE orders;

---

Assignment 10: Using NOT NULL and UNIQUE Constraints

Objective:
Learn how to apply the NOT NULL and UNIQUE constraints to columns.

Task:
Create a table members with the following columns:

• member_id (INT)
• email (VARCHAR, UNIQUE, NOT NULL)
• join_date (DATE)

Solution:

sql

CopyEdit

CREATE TABLE members (

    member_id INT NOT NULL AUTO_INCREMENT,

    email VARCHAR(100) NOT NULL UNIQUE,

    join_date DATE,

    PRIMARY KEY (member_id)

);

Explanation:

• email is marked as NOT NULL (cannot be empty) and UNIQUE (must be unique for each row).
• member_id is set to AUTO_INCREMENT and serves as the primary key.

Expected Output:
Check the table with:

sql

CopyEdit

DESCRIBE members;

---

These assignments cover a variety of data types and constraints in MySQL, giving students practical experience with creating tables, defining constraints, and selecting appropriate data types.

4o mini

You said:

write 50 MCQs for the concept above with explanation

ChatGPT said:

ChatGPT

Here are 50 multiple-choice questions (MCQs) based on the Data Types and Table Creation concept in MySQL. Each question includes explanations to help with learning:

---

MCQ 1: Which data type is best suited for storing a person's age in MySQL?

A) VARCHAR
B) DATE
C) INT
D) TEXT

Answer: C) INT
Explanation: INT is the appropriate data type for storing integer values such as age.

---

MCQ 2: Which of the following data types is used to store decimal values in MySQL?

A) INT
B) DECIMAL
C) CHAR
D) TEXT

Answer: B) DECIMAL
Explanation: DECIMAL is used for precise storage of decimal values, such as for financial data.

---

MCQ 3: What is the default size of a CHAR data type in MySQL?

A) 50
B) 255
C) 1
D) 100

Answer: C) 1
Explanation: The default size of a CHAR field is 1 if the size is not specified.

---

MCQ 4: What is the range of values that can be stored in the TINYINT data type in MySQL?

A) -128 to 127
B) -32768 to 32767
C) 0 to 255
D) -2147483648 to 2147483647

Answer: A) -128 to 127
Explanation: TINYINT can store values from -128 to 127 for signed and 0 to 255 for unsigned.

---

MCQ 5: What does the AUTO_INCREMENT keyword do in MySQL?

A) Automatically generates a unique value for each new row
B) Increases the value of a column by 1
C) Decreases the value of a column by 1
D) Automatically generates a random number for each new row

Answer: A) Automatically generates a unique value for each new row
Explanation: The AUTO_INCREMENT keyword is used to automatically generate a unique integer value for each new record in the table.

---

MCQ 6: Which data type is used to store fixed-length strings in MySQL?

A) VARCHAR
B) TEXT
C) CHAR
D) ENUM

Answer: C) CHAR
Explanation: CHAR is used for storing fixed-length strings, while VARCHAR is for variable-length strings.

---

MCQ 7: Which data type is used to store large text data in MySQL?

A) VARCHAR
B) TEXT
C) INT
D) CHAR

Answer: B) TEXT
Explanation: TEXT is used to store large text values, such as articles, descriptions, or notes.

---

MCQ 8: What is the correct way to specify a column that can only have one of two values, like 'Online' or 'Offline'?

A) ENUM
B) CHAR
C) TEXT
D) VARCHAR

Answer: A) ENUM
Explanation: ENUM is used to limit the values of a column to a specific set of options, such as 'Online' or 'Offline'.

---

MCQ 9: Which of the following data types would you use to store a boolean value in MySQL?

A) INT
B) TINYINT
C) BOOLEAN
D) VARCHAR

Answer: C) BOOLEAN
Explanation: BOOLEAN is used to store boolean values (TRUE or FALSE), but internally it is stored as TINYINT.

---

MCQ 10: What does the PRIMARY KEY constraint do in MySQL?

A) Ensures that values in a column are unique
B) Ensures that a column cannot have null values
C) Combines both uniqueness and not null properties for a column
D) Automatically increments the value of a column

Answer: C) Combines both uniqueness and not null properties for a column
Explanation: The PRIMARY KEY constraint ensures that the values are unique and cannot be NULL.

---

MCQ 11: Which of the following is the correct syntax to create a table in MySQL?

A) CREATE TABLE tablename (column1 datatype, column2 datatype);
B) CREATE TABLE (column1 datatype, column2 datatype);
C) TABLE CREATE tablename (column1 datatype, column2 datatype);
D) CREATE TABLE (tablename column1 datatype, column2 datatype);

Answer: A) CREATE TABLE tablename (column1 datatype, column2 datatype);
Explanation: The CREATE TABLE statement is used to create a table and define its columns and data types.

---

MCQ 12: What will happen if you define a column with a data type of VARCHAR(50) but insert a string with 60 characters?

A) An error will occur
B) The string will be truncated to 50 characters
C) The string will be stored with 60 characters
D) MySQL will automatically resize the column

Answer: B) The string will be truncated to 50 characters
Explanation: If the string exceeds the specified size, it will be truncated to fit within the defined size.

---

MCQ 13: What is the purpose of the NOT NULL constraint in MySQL?

A) To specify that a column cannot have duplicate values
B) To prevent the column from having any null values
C) To create a default value for a column
D) To enforce the uniqueness of the column

Answer: B) To prevent the column from having any null values
Explanation: The NOT NULL constraint ensures that a column must contain a value; it cannot be left empty (null).

---

MCQ 14: Which of the following data types would be appropriate for storing a product's price in MySQL?

A) DECIMAL
B) INT
C) CHAR
D) TEXT

Answer: A) DECIMAL
Explanation: DECIMAL is best suited for storing precise values like prices, where you need to define the number of decimal places.

---

MCQ 15: Which data type is used to store a date without time information in MySQL?

A) DATETIME
B) DATE
C) TIMESTAMP
D) TIME

Answer: B) DATE
Explanation: DATE is used to store dates in the format 'YYYY-MM-DD', excluding time information.

---

MCQ 16: What is the maximum length of a VARCHAR column in MySQL?

A) 255
B) 65,535
C) 32,767
D) 1,000

Answer: B) 65,535
Explanation: The maximum length for a VARCHAR column is 65,535 characters, but it depends on the row size.

---

MCQ 17: Which of the following is the correct way to define a column with a default value in MySQL?

A) column_name datatype DEFAULT value;
B) column_name datatype DEFAULT value()
C) column_name DEFAULT value;
D) column_name value DEFAULT datatype;

Answer: A) column_name datatype DEFAULT value;
Explanation: You can specify a default value for a column by using the DEFAULT keyword.

---

MCQ 18: Which of the following constraints ensures that all values in a column are unique?

A) PRIMARY KEY
B) UNIQUE
C) FOREIGN KEY
D) INDEX

Answer: B) UNIQUE
Explanation: The UNIQUE constraint ensures that all values in a column are unique.

---

MCQ 19: What does the FOREIGN KEY constraint do in MySQL?

A) Ensures the uniqueness of values in a column
B) Prevents a column from having null values
C) Establishes a relationship between two tables
D) Automatically generates a value for a column

Answer: C) Establishes a relationship between two tables
Explanation: The FOREIGN KEY constraint is used to link two tables, enforcing referential integrity.

---

MCQ 20: Which of the following data types can store a date and time value in MySQL?

A) DATE
B) DATETIME
C) TIME
D) YEAR

Answer: B) DATETIME
Explanation: DATETIME is used to store both date and time values.

---

MCQ 21: How would you define a column that can only accept the values 'Male' or 'Female' in MySQL?

A) VARCHAR
B) ENUM
C) BOOLEAN
D) CHAR

Answer: B) ENUM
Explanation: ENUM is used to restrict a column to specific values such as 'Male' or 'Female'.

---

MCQ 22: What is the default value of a BOOLEAN column in MySQL?

A) NULL
B) TRUE
C) 1
D) FALSE

Answer: C) 1
Explanation: The default value of a BOOLEAN column is 1, which represents TRUE.

---

MCQ 23: How would you specify a column to store a phone number with dashes (e.g., '555-1234') in MySQL?

A) CHAR
B) TEXT
C) VARCHAR
D) INT

Answer: C) VARCHAR
Explanation: VARCHAR is ideal for storing alphanumeric data such as phone numbers with dashes.

---

MCQ 24: Which data type should be used for storing a list of integers (e.g., a list of employee IDs)?

A) INT
B) TEXT
C) SET
D) VARCHAR

Answer: C) SET
Explanation: SET can store multiple values in a column and is often used to store lists of predefined values.

---

MCQ 25: How can you ensure that a column's value must always be unique?

A) Use the UNIQUE constraint
B) Use the PRIMARY KEY constraint
C) Use the NOT NULL constraint
D) Both A and B

Answer: D) Both A and B
Explanation: Both UNIQUE and PRIMARY KEY constraints ensure uniqueness, but PRIMARY KEY also prevents NULL values.

---

MCQ 26: Which of the following is the correct way to create a table with an AUTO_INCREMENT primary key?

A) CREATE TABLE students (id INT PRIMARY KEY AUTO_INCREMENT);
B) CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY);
C) CREATE TABLE students (id AUTO_INCREMENT PRIMARY KEY);
D) CREATE TABLE students (id AUTO_INCREMENT);

Answer: B) CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY);
Explanation: The correct syntax for an AUTO_INCREMENT column is specifying it after the column type and defining it as the PRIMARY KEY.

---

MCQ 27: Which data type would you use to store a large amount of textual information in MySQL?

A) CHAR
B) VARCHAR
C) TEXT
D) ENUM

Answer: C) TEXT
Explanation: TEXT is designed for large amounts of text data, such as articles or descriptions.

---

MCQ 28: Which of the following constraints can be used to ensure referential integrity between two tables?

A) UNIQUE
B) FOREIGN KEY
C) NOT NULL
D) PRIMARY KEY

Answer: B) FOREIGN KEY
Explanation: The FOREIGN KEY constraint enforces referential integrity between tables, linking a column in one table to a column in another.

---

MCQ 29: What will happen if you try to insert a NULL value into a column that has the NOT NULL constraint?

A) The value will be inserted successfully
B) The database will ignore the value
C) An error will occur
D) The value will be set to 0

Answer: C) An error will occur
Explanation: If a column is defined as NOT NULL, inserting a NULL value will result in an error.

---

MCQ 30: Which data type would you use to store a year value (e.g., 2023) in MySQL?

A) YEAR
B) DATE
C) INT
D) VARCHAR

Answer: A) YEAR
Explanation: YEAR is specifically designed for storing year values in the format 'YYYY'.

---

MCQ 31: Which of the following data types can be used to store a fixed-length numeric value with decimal places?

A) DECIMAL
B) INT
C) CHAR
D) DATE

Answer: A) DECIMAL
Explanation: DECIMAL is used to store fixed-length numeric values with decimal places, ideal for storing financial values.

---

MCQ 32: Which constraint ensures that a column's values are automatically incremented by 1 each time a new record is inserted?

A) AUTO_INCREMENT
B) UNIQUE
C) PRIMARY KEY
D) NOT NULL

Answer: A) AUTO_INCREMENT
Explanation: AUTO_INCREMENT automatically generates a unique value for the column on each insert.