SQL: DDL Interview Questions and Answers

 

DDL Commands Interview Questions & Answers

DDL Commands

1.      CREATE

2.      DROP

3.      ALTER

4.      TRUNCATE

5.      COMMENT

6.      RENAME

 

CREATE:

CREATE is used to create database, schema, table and  view .

1.      Create table syntax?

CREATE TABLE TABLE_NAME (COL NUMBER,COL1 CHAR(10) COL2 DATE, COL3 VARCHAR2(10));

 

2.      Create Primary key, foreign key relation table?

 

·         CREATE TABLE TABLE_NAME(ID NUMBER,NAME CHAR(1), CONSTRAINT PK_ID PRIMARY KEY(ID));

·         CREATE TABLE TABLE_NAME1(DID NUMBER,DNAME VARCHAR(10) ,FOREIGN KEY(DID) REFERENCES TABLE_NAME(ID));

 

3.      How to create table with constraints(primary key, foreign key,  unique key, not null)

 

CREATE TABLE CONSTRAINT_TABLE (

    ID INT PRIMARY KEY,

    COUNTRY VARCHAR(100) UNIQUE,

    SAL DECIMAL(10, 2) CHECK(SAL > 0),  -- Changed to LESS than'

    DNAME VARCHAR(100) NOT NULL

);

 

4.      How to create table using existing table?

·         CREATE TABLE NEW_TABLE AS SELECT * FROM EXISTING_TABLE ;

·         CREATE TABLE NEW_TABLE AS SELECT COL,COL1 FROM EXISTING TABLE;

·         CREATE TABLE_NEW_TABLE AS SELECT COL,SUM(COL1) AS SUMSAL FROM EXISTING TABLE GROUP BY COL;

 

What is the difference between below two queries?

5.      create table table_name as select * from existing_table;

This query is used to create same existing table structure and will copy all the records from existing table into the new table.

 

6.      create table table_name as select * from existing_table where 1=1;

The first statement creates a new table with the same data and same structure as the existing table.

 

7.      create table table_name as select * from existing_table where 1=2;

The second statement creates a new table with the same structure as the existing table but without any data.

 

 

DROP

Delete objects from the database.

 

1.      Can we drop table if table is having with/without data? à Yes, we can drop table

2.      Can we drop table if table has primary key?(no foreign key relation) à Yes, we can drop table if there is no foreign key relation.

3.      Can we drop table if table has primary and foreign key relation? à we will get ORA-02449: unique/primary keys in table referenced by foreign keys error.

 

TRUNCTE:

Remove all records from a table, including all spaces allocated for the records are removed

 

1.      Can we use TRUNCATE to table if table is having with/without data?

2.      Can we use TRUNCATE to table if table has primary key?(no foreign key relation)

3.      Can we use TRUNCATE to table if table has primary and foreign key relation?

 

ANSWERS:

1)      Truncate will remove all the data from table

2)      No, it will through error

3)      No, it will through error

 

ALTER:

Alter the structure of the database.

 

1.      Add a new column to a table?.

2.      Rename column name?

3.      If the column contain data can we change data type from char to number or number to char?

4.      If the column has no data can we change data type from char to number or number to char?

5.      Increase or Decrease the length of a column's data type, considering whether the column has existing data?.

6.      Add or Remove constraints on existing columns?.

7.      Insert a new column between existing columns or at a specific position?.

Answer:

-- ADD NEW COLUMN

ALTER TABLE AEMP ADD  STATS CHAR(1);

 

-- RENAME COLUMN

 

ALTER TABLE AEMP RENAME COLUMN STATS TO STATUS;

 

-- CHANGE DATA TYPE IF COLUMN HAVING DATA(NUMBER -- CHAR)

ALTER TABLE  AEMP MODIFY EMPNO CHAR(10);

--eRROR ORA-01439: column to be modified must be empty to change datatype

 

-- CHANGE DATA TYPE IF COLUMN HAVING DATA CHAR TO NUMER

ALTER TABLE  AEMP MODIFY ENAME NUMBER(10);

--eRROR ORA-01439: column to be modified must be empty to change datatype

 

-- INCREASE DATA LENGTH IF COLUMN CONTAIN DATA

ALTER TABLE AEMP MODIFY DEPTNO NUMBER(5);

 

-- DECREASE DATA LENGTH IF COLUMN CONTAIN DATA

ALTER TABLE AEMP MODIFY EMPNO NUMBER(3);

--SQL Error: ORA-01440: column to be modified must be empty to decrease precision or scale

 

-- ADD COLUMN IN NTH POISITION

ALTER TABLE AEMP ADD  ID VARCHAR(255) AFTER EMPNO;

 

-- REMOVE PK

ALTER TABLE drop3 DROP constraint PK_ID1;

 

-- ADD PK

 

ALTER TABLE drop3 ADD CONSTRAINT PK_ID1 PRIMARY KEY(ID1);

 

RENAME:

Rename an object existing in the database

 

1.      Rename table name?

2.      Rename table if table having primary key?

3.      Rename table if table has primary –foreign key relation?

 

1)      RENAME OLD_TABLE TO NEW_TABLE?

2)      We can change table name

3)      We can rename even if table relation with another table.

 

Difference b/w TRUNCATE and DROP

Feature	TRUNCATE	DROP
Type	Data Definition Language (DDL)	Data Definition Language (DDL)
Purpose	Removes all rows from a table	Removes a table and its structure
Speed	Faster than DELETE and DROP	Fastest
Reversibility	Not reversible	Not reversible
Impact on table structure	Does not affect table structure	Removes table structure
Impact on indexes	Indexes are dropped and recreated	Indexes are dropped and not recreated
Permissions required	TRUNCATE permission on the table	DROP permission on the table
Use cases	Removing all rows from a table quickly	Removing a table that is no longer needed
Syntax	TRUNCATE TABLE table_name;	DROP TABLE table_name;