Diagram showing SQL DDL operations for managing tables, relationships and constraints like primary key and foreign key.

6.How do you remove a column from an existing table?

To remove a column, you use the ALTER TABLE statement with the DROP COLUMN clause:
ALTER TABLE employees DROP COLUMN phone_number;
The SQL statement
ALTER TABLE employees DROP COLUMN phone_number; Removes a column from the table employees. It removes both the column phone_number as well as its data present in the table.
Notes
• MySQL: The syntax will work as it is for dropping a column from a table
. • SQL Server: The same syntax is supported by SQL Server also to drop columns from the tables.
• PostgreSQL: This syntax is applicable for dropping columns in PostgreSQL also.
•Oracle: Again, you can use the same command but you have an option of using CASCADE CONSTRAINTS in case the column has associated foreign keys.
For example, ALTER TABLE employees DROP COLUMN phone_number CASCADE CONSTRAINTS;

7.How do you rename a column in an existing table?

You can rename a column by using the ALTER TABLE statement with the RENAME COLUMN clause in Oracle. Here is the syntax:
Oracle:
ALTER TABLE employees
RENAME COLUMN old_column_name TO new_column_name;
This will rename the column old_column_name to new_column_name in the employees table.
Notes:
•MySQL: ALTER TABLE including the CHANGE COLUMN clause providing both column names and datatype. ALTER TABLE employees CHANGE COLUMN old_column_name new_column_name VARCHAR(100);.
•SQL Server : You can use the sp_rename stored procedure. EXEC sp_rename 'employees.old_column_name', 'new_column_name', 'COLUMN'.
•PostgreSQL: identical to Oracle, you are forced to use ALTER TABLE employees RENAME COLUMN old_column_name TO new_column_name; by which you do not need to re-specify the data type.

8.How do you Change the data type of a column?

The data type of a column can be changed as follows by using the ALTER TABLE statement with the word MODIFY :
ALTER TABLE employees MODIFY phone_number VARCHAR(30);
The SQL statement ALTER TABLE employees MODIFY phone_number VARCHAR(30); alters the phone_number column of the employees table. New data type is now VARCHAR(30), meaning the column can hold text with up to 30 characters.
Notes
•MySQL : This syntax will work on an existing column to alter the data type.
•SQL Server: The syntax for changing columns is different.
SQL Server uses ALTER COLUMN instead of MODIFY.
Example: ALTER TABLE employees ALTER COLUMN phone_number VARCHAR(30);
•PostgreSQL: Supports the same ALTER COLUMN syntax as SQL Server.
•Oracle: Uses MODIFY in a similar way to MySQL for changing column types.
Example:
ALTER TABLE employees MODIFY (phone_number VARCHAR2(30));

9.How to drop a table using DROP TABLE statement?

It is a DROP TABLE statement used for permanent removal of a table from the database
DROP TABLE employees;
The SQL statement DROP TABLE employees; will remove permanently employees table in the database. This will remove table structure and data permanetly from database.
•MySQL: The syntax works as it is to drop a table from the database.
•SQL Server: The syntax is exactly the same to drop a table.
Example: DROP TABLE employees;
•PostgreSQL: Supports the same syntax for dropping tables.
•Oracle: The syntax is the same but in Oracle, you can use CASCADE CONSTRAINTS to delete dependent objects, such as foreign keys.
Example: DROP TABLE employees CASCADE CONSTRAINTS;

10.How do you use CASCADE when dropping a table to delete dependent objects?

When a table contains dependent objects, such as foreign keys or indexes you can use the CASCADE option to delete those objects automatically:
DROP TABLE employees CASCADE;
The SQL command DROP TABLE employees; deletes the employees table permanently from the database. This operation cannot be reversed and all data in the table will be lost.
Notes:
•MySQL: The syntax is used as it is to delete a table from the database.
•SQL Server: The syntax is identical to delete a table.
Example: DROP TABLE employees;
•PostgreSQL: Supports the same syntax to drop tables.
•Oracle: Same syntax, but in Oracle you can drop any dependent objects (like foreign keys) using CASCADE CONSTRAINTS.
Example: DROP TABLE employees CASCADE CONSTRAINTS;

11.How do I rename a table using the RENAME statement?

To rename a table, you issue the RENAME statement as follows:
RENAME employees TO staff;
The SQL statement RENAME employees TO staff changes the employees table name to staff in a database.
Note:
MySQL: Not supported directly. For this you need to employ the statement RENAME TABLE instead.
Example:
RENAME TABLE employees TO staff;
•SQL Server: SQL Server does not support RENAME for tables. Instead, you have to use the sp_rename system stored procedure.
Example: EXEC sp_rename 'employees', 'staff';
•PostgreSQL: Supports the RENAME statement to rename tables.
•Oracle: Also supports the RENAME statement to rename tables.

12.How do you modify the table constraints with the use of the ALTER TABLE statement?

To change table constraints you use the ALTER TABLE statement along with specific keywords which have to be ADD, DROP or MODIFY depending on type of constraint you want to add, remove or change.
Here are some examples of how to change the table constraints with the ALTER TABLE statement:

1.ADD a new constraint: Adding a new constraint, for example UNIQUE on an existing column uses the ADD CONSTRAINT clause.
The following is the example on adding a UNIQUE constraint on the email column.
ALTER TABLE employees
ADD CONSTRAINT unique_email
UNIQUE (email);
The above SQL statement introduces a UNIQUE constraint on the email column of the employees table; it means that no two values in the column should be equal.

2.Dropping an existing constraint: To delete a constraint from a table, you use the DROP CONSTRAINT clause. You have to mention the constraint name for deleting it.
Example to delete a UNIQUE constraint on the email column:
ALTER TABLE employees DROP CONSTRAINT unique_email;
Deletes the `unique_email` constraint from the employees table.

3.Modifying an existing constraint: Typically, if you want to change a constraint (eg, you want to make a change to the column's data type or change a condition in the column) you need to drop first the current constraint and afterwards add a new one. Direct modification is not necessarily supported.
Change a column's data type (which is going to affect constraints, such as CHECK):
ALTER TABLE employees MODIFY salary DECIMAL(10, 2);

Notes:
MySQL Supports adding and dropping constraints, using ALTER TABLE.
However, direct modification of CHECK constraints is not always supported.
SQL Server: You can add or drop constraints using ALTER TABLE.
However, the common way of modifying an existing constraint is to drop it and then add a new one.
PostgreSQL: Most of the constraints can be added, dropped, and modified using ALTER TABLE.
Oracle: As with other databases, ALTER TABLE can be used to add or drop constraints. Usually, changing constraints has to be done by dropping and re-creating them.

13.How would you add a foreign key constraint to an existing table?

You use the ALTER TABLE statement for adding a foreign key to an existing table. This is as shown below:

ALTER TABLE employees ADD CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id);

This SQL command adds a foreign key constraint named fk_department to the employees table. The department_id column in employees will reference the department_id column in the departments table, meaning that the department_id values in the employees table must match existing values in the departments table.
Notes:
•MySQL- Used to add the foreign key constraints. Make sure both involved columns are indexed; indeed, the referencing column normally should be either a PRIMARY KEY or UNIQUE.
SQL Server- Its syntax of adding foreign key constraints remains identical with MySQL.
PostgreSQL Supports MySQL compatible syntax for the adding the FOREIGN KEY constraints.
• Oracle: It uses almost the same syntax, except you can add options such as ON DELETE CASCADE or ON UPDATE CASCADE for the case in which it actually updated or deletes the referenced data.
This is demonstrated as follows:
ALTER TABLE employees ADD CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE CASCADE;

14.How do you drop a foreign key constraint from a table?

To delete a foreign key constraint, you use the ALTER TABLE statement with the DROP CONSTRAINT clause:

ALTER TABLE employees DROP CONSTRAINT fk_department;

This SQL statement deletes a foreign key constraint named fk_department from the employees table. This means that the dependency between the department_id within employees and department_id within departments will no longer exist. Notes:
MySQL For MySQL, the syntax differs.
You have to use DROP FOREIGN KEY instead of using DROP CONSTRAINT.
For example,
ALTER TABLE employees DROP FOREIGN KEY fk_department;
• SQL Server: The syntax is exactly the same as above in order to drop a constraint.
Example ALTER TABLE employees DROP CONSTRAINT fk_department;
• PostgreSQL: The syntax is exactly the same for dropping foreign key constraints.
•Oracle: It is implemented with the same syntax for SQL Server and PostgreSQL to drop a constraint.
Example: ALTER TABLE employees DROP CONSTRAINT fk_department;

15.How do you create an index using the CREATE INDEX statement?

An index is used to speed up queries. Define it using the CREATE INDEX statement:


CREATE INDEX idx_employee_name ON employees (name);


The above SQL command creates an index called idx_employee_name on the column name of the employees table. This will improve search operations by the column name.
Note:
•MySQL: The above syntax can be used to create an index on a single column.
•SQL Server: The above syntax can be used to create an index. CREATE INDEX idx_employee_name ON employees (name);
•PostgreSQL: The same syntax can be used to create indexes.
•Oracle: Also supports creating indexes using the same syntax.
Example: CREATE INDEX idx_employee_name ON employees (name);

16.How do you drop an index using the DROP INDEX statement?

To drop an index on a table, you use the DROP INDEX statement:
DROP INDEX idx_employee_name;
This SQL command will drop the index named idx_employee_name, freeing resources. Keep in mind that this may be slowing query performance if this index was used to make certain searches or sorts more efficient.
Notes
• MySQL: The above syntax works to drop an index. The syntax is:
DROP INDEX idx_employee_name;
•SQL Server: The syntax is different, in that you will need to include the name of the table.
DROP INDEX employees.idx_employee_name;
•PostgreSQL: Also supports the same syntax in dropping indexes.
•Oracle: This database uses the same syntax as MySQL and PostgreSQL to drop an index.
DROP INDEX idx_employee_name;

17.How do you manage data integrity using triggers and constraints?

Data integrity in databases is maintained through constraints and triggers. Constraints refer to rules applied to columns in tables ensuring valid data is stored. Among the common constraints include.
primary key ensures uniqueness in a column.
The foreign key ensures maintainability of referential integrity between the tables since the references must be valid.
Check Constraint: Checks for specific conditions on column values, such as being greater than or equal to 18.
Unique Constraint : Prevents duplicate values in any column.
Not Null Constraint: Does not allow null values in a column.
In example:

CREATE TABLE employees (employee_id INT PRIMARY KEY, age INT CHECK (age >= 18) ;

Triggers are automatic procedures that are executed in response to events such as INSERT, UPDATE or DELETE. They allow for advanced validation and automation.
Before Triggers: Validate or modify data before an operation.
After Triggers: Perform actions after an operation.
Example of a trigger to enforce rules:
CREATE TRIGGER check_age BEFORE INSERT ON employees FOR EACH ROW
BEGIN
IF NEW.age < 18 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Age must be 18 or older';
END IF;
END;
While constraints are declarative and ensure data validity at the schema level, triggers provide procedural enforcement for complex rules and actions. Together, they maintain consistency and reliability.

18.How do you use CHECK constraints to enforce data validation rules on columns?

To enforce data validation rules on columns, you can use CHECK constraints. The CHECK constraint checks whether the values in a column meet a given condition. If the condition is violated, then the database will reject the operation.

Query Example:
CREATE TABLE employees(
employee_id INT PRIMARY KEY,
age INT CHECK (age >= 18)
);

This SQL command ensures that the age column in the employees table only accepts values 18 or greater. If a value less than 18 is inserted the database will throw an error.
Notes
MySQL: FULL SUPPORT for the CHECK constraint. However, in the older versions until 8.0, this constraint is totally ignored. Your database must be of that version or above.
SQL Server: Supports the CHECK constraint as shown by the syntax below.
PostgreSQL: supports the CHECK constraint and verifies the data at the time of insertion as well as updation.
Oracle: The CHECK constraint is supported with nearly the same syntax.

19.How do you create and manage composite primary keys in tables?

Composite primary keys or composite keys can be two or more columns used to ensure that each record is uniquely identified while creating and managing in a table. Composite primary keys are very useful where no two rows may contain the same combination of values for this column.

a table OrderDetails -
CREATE TABLE OrderDetails (
OrderID INT NOT
PRIMARY KEY (OrderID, ProductID)
);

This command creates a table for OrderDetails such that OrderID and ProductID collectively act as a composite primary key. Thus, no two rows are allowed to share the same combination of OrderID and ProductID.
Notes:
MySQL: It completely supports composite primary keys. All columns to be part of the composite key should be defined as NOT NULL.
SQL Server: Composite primary keys are supported using the PRIMARY KEY constraint. Syntax and behavior are similar to MySQL.
PostgreSQL: Supports composite primary keys and enforces uniqueness and NOT NULL constraints on the columns specified.
Oracle: Supports composite primary keys and the syntax is nearly identical to the above. It also enforces both uniqueness and NOT NULL constraints on the columns specified.

20.How would you use TRUNCATE to delete all data in a table without deleting the table?

You can delete all the data in a table by using the TRUNCATE statement. It is like a delete command except it removes all rows from a table without removing the table itself. This deletes all rows in the table but retains the structure of the table, which is the columns, constraints and indexes.

TRUNCATE TABLE employees;

Explanation
Data Deletion This deletes all rows in the employees table.
Table Structure: The table structure remains the same, so you can add new rows, and all columns, constraints, and indexes are preserved.
Faster Operation: TRUNCATE is faster than DELETE since it does not log individual row deletions and does not fire any triggers.
Notes:
MySQL: TRUNCATE resets any auto-increment counters (if applicable) to 0.
SQL Server: TRUNCATE is minimally logged and does not fire any triggers; therefore, it is faster than DELETE.
PostgreSQL: TRUNCATE removes all rows from a table and resets any sequence number of an AUTOINCREMENT column.
Oracle: TRUNCATE is DDL statement, it automatically commits, thus cannot rollback; deletes all data of a table, leaving the structure.
Important Points
TRUNCATE is a DML operation that cannot be rolled back unless it is used around a transaction in depending database system.
In contrast to DELETE, TRUNCATE does not check an individual row condition or even call triggers. TRUNCATE does not remove constraints or table structure, it only clears data.

21.How do you define and manage table relationships (one-to-many, many-to-many)?

To define and manage table relationships in a database, you use foreign keys to link tables.
There are primarily two types of relationships:
one-to-many and many-to-many.
One-to-Many Relationship:
A one-to-many relationship is one in which a record in one table is related to many records in another table. For instance, one Customer may have many Orders, but each Order is associated with only one Customer. To create this relationship a foreign key is added to the "many" table, such as Orders, referencing the primary key of the "one" table such as Customers.
Query Example:

CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
Name VARCHAR(100)
);
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
OrderDate DATE,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) );

Many-to-Many Relationship: A many-to-many relationship exists when more than one record in one table has to be related to more than one record in another table. For example, a Student can enroll for many Courses and a Course can have many Students. Such a relationship is implemented by a junction table that holds foreign keys from both the tables.

Query Example:
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(100)
);

CREATE TABLE Courses (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(100)
);

CREATE TABLE StudentCourses (
StudentID INT,
CourseID INT,
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

Important note:
MySQL / SQL Server / PostgreSQL: Support both one-to-many and many-to-many relationships.
Oracle: It also supports such relationships, though foreign key constraints