Basic sql interview questions

1.What are the main five types of sql Statements present?
Ans.

• Data Manipulation Statement (DML).
• Data Definition Language Statement (DDL).
• Transaction Control Statement (TC).
• Data Control Language Statement (DCL).
• Query Statement.

2. What do you mean by Data Manipulation Language (DML) statements?
Ans. It is mainly used to modify the content of the tables.The three DML statements are

• Insert
• Update
• Delete

3. What do you mean by Data Definition Language (DDL) statements?

Ans. It mainly defines the data structure of a tables. That means whether to alter a table or create a table.

There are five basic types DDL.

• Create
• Alter
• Truncate
• Drop
• Rename

4. What do you mean by  TC statements?

Ans.It mainly records permanently any data changes or undo those changes.

There are three main types of TC presents.

• commit
• rollback
• savepoint

5.What do you mean by  DCL statements?

Ans. It is related to the permission of my own database structure.There are mainly two types of DCL statements present.

• Revoke: Prevents another user to access your data base.
• Grant: Gives another user's access to my database structure

6.Write down the statement to create a "customers" table .

Ans.CREATE TABLE customers (
customer_id INTEGER CONSTRAINT customers_pk PRIMARY KEY,
first_name VARCHAR2(10) NOT NULL,
last_name VARCHAR2(10) NOT NULL,
dob DATE,
phone VARCHAR2(12)
);

7.what is constraints used above?

Ans.

The CONSTRAINT clause indicates that the customer_id column is the primary key.You should always name your primary key constraints, so that when a constraint error occurs it is easy to spot where it happened.So in the above case if you enter the same value for the customer id in two times,then the error massage will be like..unique constraint (SYSTEM.CUSTOMERS_PK) violated... here any name can be attached instead of customers_pk.

8.Use of foreign key?
Ans.Actually foreign key points the primary key of another table.
person's table...

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

The "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	2
4	24562	1

Here in the person's table, the P_ID  column is the primary key.And in the orders table the P_ID column is the foreign key.That means,we can't enter any values in this P-ID column.These two tables are linked.We can't enter values in orders table in P-ID column other than 1,2,3.

Foreign key written in the following way.
IN MYSql..
create table Orders(
O-ID int,
OrderNo int,

FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)

);