Playing as a guest? to earn XP and track your progress.
📅 Table Creation and Data Manipulation Commands Quiz
📅 Table Creation and Data Manipulation Commands
Which SQL command is used to create a new database in MySQL?
NEW DATABASE school;
CREATE DATABASE school;
MAKE DATABASE school;
BUILD DATABASE school;
CREATE DATABASE is the correct DDL command to create a new database in MySQL.
Which command is used to select and activate a database for use in MySQL?
SELECT DATABASE school;
OPEN DATABASE school;
USE school;
CONNECT school;
USE database_name; selects and activates a database for the current session.
What will happen if you run CREATE DATABASE school; when a database named 'school' already exists?
It overwrites the existing database
It creates a second database with the same name
It throws an error
It does nothing silently
MySQL throws an error if you try to CREATE DATABASE with a name that already exists. Use CREATE DATABASE IF NOT EXISTS to avoid this error.
Which clause is added to CREATE DATABASE to avoid an error if the database already exists?
OR IGNORE
IF NOT EXISTS
IF EXISTS
SKIP IF DUPLICATE
CREATE DATABASE IF NOT EXISTS database_name; creates the database only if it does not already exist, preventing an error.
Which command permanently deletes an entire database along with all its tables and data?
DELETE DATABASE school;
REMOVE DATABASE school;
DROP DATABASE school;
TRUNCATE DATABASE school;
DROP DATABASE permanently removes the entire database, all its tables, and all data inside it. This action cannot be undone.
Which SQL command shows the name of the currently active database?
SHOW ACTIVE DATABASE;
SELECT DATABASE();
DESCRIBE DATABASE;
LIST DATABASE;
SELECT DATABASE(); returns the name of the currently selected database in the active session.
A student writes CREATE TABLE Student (RollNo INT); but gets a 'No database selected' error. What is the most likely reason?
The syntax of CREATE TABLE is wrong
The column definition is incomplete
No database was selected using the USE command
Student is a reserved keyword in MySQL
MySQL requires you to first select a database using USE database_name; before creating tables. Without it, MySQL does not know where to create the table.
Which data type should be used for a column that stores percentage marks such as 91.50?
INT
VARCHAR(5)
DECIMAL(5,2)
DATE
DECIMAL(5,2) stores exact decimal numbers with 5 total digits and 2 after the decimal point, making it ideal for precise values like 91.50.
What is the key difference between CHAR(10) and VARCHAR(10)?
CHAR stores numbers; VARCHAR stores text
CHAR is fixed-length; VARCHAR is variable-length
VARCHAR is always faster than CHAR
There is no difference between them
CHAR(n) always uses exactly n characters of storage. VARCHAR(n) uses only as much storage as the actual data requires, up to n characters.
Which constraint ensures that a column cannot store a NULL value?
UNIQUE
DEFAULT
NOT NULL
CHECK
NOT NULL constraint ensures that a column must always have a value; an INSERT or UPDATE that leaves this column empty will fail.
Which constraint ensures that all values in a column are different from each other?
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
UNIQUE constraint prevents duplicate values in a column. Unlike PRIMARY KEY, a UNIQUE column can contain NULL values.
A PRIMARY KEY constraint is equivalent to which combination of constraints?
NOT NULL + DEFAULT
UNIQUE + CHECK
NOT NULL + UNIQUE
NOT NULL + FOREIGN KEY
A PRIMARY KEY automatically enforces both NOT NULL (the column must have a value) and UNIQUE (no two rows can share the same value).
How many PRIMARY KEY constraints can a single table have in MySQL?
As many as needed
Maximum 2
Exactly one
One per column
A table can have only ONE Primary Key. However, that Primary Key can span multiple columns (composite primary key).
Which of the following is TRUE about the UNIQUE constraint?
A column with UNIQUE cannot have any NULL values
Only one UNIQUE constraint is allowed per table
A column with UNIQUE can have multiple NULL values
UNIQUE and PRIMARY KEY behave identically
UNIQUE allows NULL values — multiple rows can all have NULL in a UNIQUE column because NULL is not considered equal to any value, including another NULL.
Which constraint is used to link a column in one table to the PRIMARY KEY of another table?
CHECK
UNIQUE
FOREIGN KEY
NOT NULL
FOREIGN KEY creates a referential link between a column in the child table and the PRIMARY KEY column in the parent table, enforcing Referential Integrity.
A table Orders has a FOREIGN KEY referencing Customers(CustId). What happens if you insert a row in Orders with a CustId that does not exist in Customers?
The row is inserted with NULL for CustId
MySQL throws a foreign key constraint violation error
The row is inserted normally
MySQL auto-creates the customer record
Referential Integrity enforced by FOREIGN KEY means the value must exist in the parent table's PRIMARY KEY column. If it does not exist, the INSERT fails with a constraint violation error.
Which constraint automatically assigns a value to a column when no value is explicitly provided during INSERT?
NOT NULL
AUTO_INCREMENT
DEFAULT
CHECK
The DEFAULT constraint specifies a pre-set value that MySQL uses automatically when an INSERT statement does not provide a value for that column.
A column is defined as City VARCHAR(20) DEFAULT 'Delhi'. A row is inserted without specifying City. What value does City get?
NULL
Empty string
Delhi
An error is thrown
When no value is provided for City during INSERT, the DEFAULT value 'Delhi' is automatically used.
Which constraint ensures that the Marks column only accepts values between 0 and 100?
NOT NULL
BETWEEN
CHECK
RANGE
CHECK (Marks >= 0 AND Marks <= 100) defines a condition that every value in the Marks column must satisfy before being stored.
Which of the following correctly creates a table with RollNo as the PRIMARY KEY?
CREATE TABLE Student (RollNo INT KEY, Name VARCHAR(30));
CREATE TABLE Student (RollNo INT PRIMARY KEY, Name VARCHAR(30));
CREATE TABLE Student (PRIMARY KEY RollNo INT, Name VARCHAR(30));
CREATE TABLE Student (RollNo INT, Name VARCHAR(30), KEY(RollNo));
PRIMARY KEY is correctly defined inline after the column definition: RollNo INT PRIMARY KEY. This is the standard syntax.
What is the purpose of DML commands in SQL?
To define the structure of tables and databases
To insert, update, and delete data within tables
To grant and revoke user permissions
To commit and rollback transactions
DML (Data Manipulation Language) commands — INSERT, UPDATE, DELETE — are used to manipulate the actual data stored within database tables.
Which SQL command is used to add new rows to an existing table?
ADD INTO
INSERT INTO
APPEND INTO
PUT INTO
INSERT INTO table_name (columns) VALUES (values); is the correct DML command for adding new rows to a table.
Identify the error in this INSERT statement: INSERT INTO Student VALUES (1, Priya, 91.5);
VALUES keyword is incorrect
The string Priya must be enclosed in single quotes
Table name Student must be in lowercase
There is no error in this statement
String values in SQL must always be enclosed in single quotes: 'Priya'. Without quotes, MySQL treats it as a column name, causing an error.
A table Employee has 5 columns. When using INSERT without specifying column names, how many values must be provided?
At least 1
Only for NOT NULL columns
Exactly 5, matching all columns in order
Any number in any order
When column names are not specified in INSERT, values must be provided for ALL columns in the exact order they appear in the table definition.
Which INSERT statement correctly inserts a NULL value for the Salary column?
INSERT INTO Employee (EmpId, Name, Salary) VALUES (1, 'Raj', '');
INSERT INTO Employee (EmpId, Name, Salary) VALUES (1, 'Raj', 0);
INSERT INTO Employee (EmpId, Name, Salary) VALUES (1, 'Raj', NULL);
INSERT INTO Employee (EmpId, Name) VALUES (1, 'Raj', NULL);
NULL (without quotes) is the correct keyword to explicitly insert a missing/unknown value. An empty string '' and 0 are actual values, not NULL.
Which SQL command is used to modify existing data in a table?
MODIFY
CHANGE
ALTER
UPDATE
UPDATE is the DML command used to change existing values in one or more rows of a table.
What is the correct syntax for the UPDATE command?
UPDATE table SET column = value WHERE condition;
UPDATE SET column = value FROM table WHERE condition;
MODIFY table SET column = value WHERE condition;
UPDATE table CHANGE column = value WHERE condition;
The correct syntax is: UPDATE table_name SET column_name = value [WHERE condition]; The SET keyword assigns new values and WHERE restricts which rows are affected.
What happens when an UPDATE statement is executed without a WHERE clause?
Only the first row is updated
No rows are updated
MySQL throws a syntax error
Every row in the table is updated
Without a WHERE clause, UPDATE applies the change to ALL rows in the table. This is a dangerous mistake — always verify your WHERE condition before running UPDATE.
Which query correctly increases the salary of all employees in the 'IT' department by 5000?
UPDATE Employee SET Salary = 5000 WHERE Dept = 'IT';
UPDATE Employee SET Salary = Salary + 5000 WHERE Dept = 'IT';
UPDATE Employee ADD Salary = Salary + 5000 WHERE Dept = 'IT';
MODIFY Employee SET Salary = Salary + 5000 WHERE Dept = 'IT';
To increase the existing value, you must reference the column itself: SET Salary = Salary + 5000. Writing SET Salary = 5000 would replace the value, not add to it.
Which SQL command is used to remove specific rows from a table?
REMOVE
TRUNCATE
DELETE
ERASE
DELETE FROM table_name [WHERE condition]; is the DML command used to remove rows. TRUNCATE removes all rows but is a DDL command, not DML.
What is the result of DELETE FROM Employee; (without a WHERE clause)?
The table and all its data are permanently deleted
All rows are deleted but the table structure remains
Only the first row is deleted
MySQL throws an error because WHERE is mandatory
DELETE FROM without WHERE removes all rows but the table structure (columns, constraints) remains intact. This is different from DROP TABLE, which removes the entire table.
Which statement about TRUNCATE is correct?
TRUNCATE is a DML command that can be rolled back
TRUNCATE removes all rows, keeps the table structure, and cannot be rolled back
TRUNCATE can remove specific rows using a WHERE clause
TRUNCATE removes the table structure along with all data
TRUNCATE is a DDL command that removes all rows instantly without logging each deletion. It keeps the table structure intact but is auto-committed and cannot be rolled back.
A student wants to delete only employees who work in the 'Sales' department. Which query is correct?
DELETE Employee WHERE Dept = 'Sales';
DELETE FROM Employee WHERE Dept = 'Sales';
REMOVE FROM Employee WHERE Dept = 'Sales';
DELETE * FROM Employee WHERE Dept = 'Sales';
The correct syntax is DELETE FROM table_name WHERE condition; The FROM keyword is required, and no column list is specified in DELETE.
Which is a key difference between DELETE and TRUNCATE?
DELETE removes table structure; TRUNCATE only removes data
DELETE can use WHERE to target specific rows; TRUNCATE cannot
TRUNCATE is slower than DELETE
DELETE is a DDL command; TRUNCATE is a DML command
DELETE supports a WHERE clause to selectively remove rows. TRUNCATE removes ALL rows at once with no WHERE support. DELETE is DML (can be rolled back); TRUNCATE is DDL (cannot be rolled back).
Which command is used to change the structure of an existing table in MySQL?
MODIFY TABLE
CHANGE TABLE
ALTER TABLE
UPDATE TABLE
ALTER TABLE is the DDL command used to modify the structure of an existing table — adding/removing columns, changing data types, and managing constraints.
Which ALTER TABLE clause is used to add a new column to an existing table?
INSERT
ADD
APPEND
CREATE
ALTER TABLE table_name ADD column_name datatype; is the correct syntax to add a new column. The ADD clause is used for this purpose.
A table Student exists with column Marks INT. Which query changes its data type to DECIMAL(5,2)?
ALTER TABLE Student CHANGE Marks DECIMAL(5,2);
ALTER TABLE Student MODIFY Marks DECIMAL(5,2);
ALTER TABLE Student UPDATE Marks DECIMAL(5,2);
UPDATE TABLE Student SET Marks = DECIMAL(5,2);
MODIFY is used to change a column's data type or size while keeping the same column name. Syntax: ALTER TABLE table_name MODIFY column_name new_datatype;
What is the difference between ALTER TABLE MODIFY and ALTER TABLE CHANGE?
MODIFY deletes the column; CHANGE updates its data
MODIFY changes data type only; CHANGE can also rename the column
CHANGE changes data type only; MODIFY can also rename the column
Both do exactly the same thing
MODIFY changes a column's data type/size/constraints but keeps the same column name. CHANGE can do everything MODIFY does AND rename the column simultaneously.
Which query correctly renames the column DOJ to DateOfJoining in the Employee table (keeping DATE type)?
ALTER TABLE Employee MODIFY DOJ DateOfJoining DATE;
ALTER TABLE Employee RENAME DOJ TO DateOfJoining;
ALTER TABLE Employee CHANGE DOJ DateOfJoining DATE;
ALTER TABLE Employee ALTER DOJ RENAME DateOfJoining;
CHANGE is used to rename a column. Syntax: ALTER TABLE table_name CHANGE old_column_name new_column_name datatype; The data type must always be specified.
Which clause is used with ALTER TABLE to remove a column from a table?
REMOVE COLUMN
DELETE COLUMN
DROP COLUMN
ERASE COLUMN
ALTER TABLE table_name DROP COLUMN column_name; permanently removes the column and all its data from the table.
What happens to the data in a column when it is removed using ALTER TABLE DROP COLUMN?
Data is moved to a temporary backup table
Data is converted to NULL in the remaining columns
Data in that column is permanently and irreversibly deleted
Data can be recovered using ROLLBACK
ALTER TABLE DROP COLUMN is a DDL auto-committed operation. The column and all data stored in it are permanently deleted with no way to recover them.
A table was created without a PRIMARY KEY. Which query adds EmpId as the PRIMARY KEY afterwards?
ALTER TABLE Employee MODIFY EmpId INT PRIMARY KEY;
ALTER TABLE Employee ADD PRIMARY KEY (EmpId);
ALTER TABLE Employee SET PRIMARY KEY = EmpId;
UPDATE TABLE Employee ADD CONSTRAINT PRIMARY KEY (EmpId);
ALTER TABLE table_name ADD PRIMARY KEY (column_name); is the correct syntax to add a Primary Key to an existing table.
Which SQL command permanently deletes an entire table including its structure, data, and constraints?
DELETE TABLE Employee;
TRUNCATE TABLE Employee;
REMOVE TABLE Employee;
DROP TABLE Employee;
DROP TABLE removes the entire table — its structure, all data, all constraints, and all indexes — permanently. Unlike DELETE or TRUNCATE, the table itself no longer exists after DROP TABLE.
After TRUNCATE TABLE Student; is executed, which of the following is TRUE?
The Student table no longer exists in the database
The Student table exists but is empty
Only rows where Marks is NULL were deleted
The operation can be undone using ROLLBACK
TRUNCATE removes all rows but the table structure remains. The empty Student table still exists and can accept new INSERT statements. It cannot be rolled back.
How does DROP TABLE Employee; differ from DELETE FROM Employee;?
Both remove only data; the table structure remains in both cases
DROP TABLE removes structure and data; DELETE removes only data rows
DELETE removes structure and data; DROP TABLE removes only data
Both permanently destroy the table and all its data
DELETE FROM removes rows but the table structure stays intact. DROP TABLE removes everything — structure, data, constraints, and indexes. The table no longer exists after DROP TABLE.
Which of the following commands belongs to the DDL (Data Definition Language) category?
INSERT
UPDATE
DELETE
ALTER TABLE
ALTER TABLE is a DDL command because it modifies database structure. INSERT, UPDATE, and DELETE are all DML commands because they manipulate data.
A table is defined with Salary DECIMAL(10,2) DEFAULT 0.00. The following is executed: INSERT INTO Employee (EmpId, Name) VALUES (5, 'Ravi');. What will be the value of Salary?
NULL
0
0.00
An error — Salary cannot be omitted
Since Salary has DEFAULT 0.00, omitting it in INSERT triggers the default. The stored value will be 0.00. No error occurs because Salary is not defined as NOT NULL.
A FOREIGN KEY in the Employee table references Department(DeptId). Which operation would violate referential integrity?
Inserting a new row into the Department table
Deleting a Department row that is referenced by one or more Employee rows
Updating a non-key column in the Department table
Truncating the Employee table
Deleting a parent row whose PRIMARY KEY is referenced by an existing FOREIGN KEY in the child table violates referential integrity, causing a constraint error unless ON DELETE CASCADE is defined.
Which of the following SQL statements has a syntax error?
ALTER TABLE Student ADD Phone VARCHAR(15);
ALTER TABLE Student MODIFY Name VARCHAR(50) NOT NULL;
ALTER TABLE Student DROP COLUMN City;
ALTER TABLE Student CHANGE Marks Score;
ALTER TABLE CHANGE requires the new column name AND the data type to be specified. CHANGE Marks Score; is missing the data type. Correct form: CHANGE Marks Score DECIMAL(5,2);
Which of the following statements about the DROP DATABASE command is TRUE?
It removes only the tables inside the database, keeping the database intact
It can be reversed using ROLLBACK
It permanently removes the database along with all its tables and data
It requires each table to be dropped individually first
DROP DATABASE permanently removes the entire database — including every table, every row of data, and every constraint — in a single auto-committed operation. It cannot be rolled back and does not require tables to be dropped first.