Manual Testing – SQL Queries Interview Questions and Answers –Part 1 :

Manual Testing – SQL Queries Interview Questions and Answers –Part 1 :

1. What is the difference between “delete” , “truncate” and “drop” commands

Delete Command	Truncate Command	Drop Command
Delete Command Belongs to DML	Truncate Command Belongs to DDL	Drop Command Belongs to DDL
Can be Used to delete entire Table data	Can be Used to delete entire Table data	Can be Used to delete entire Table data and also the structure
Can be used to delete specific rows using where clause	Can't be used to delete specific rows using where clause	Can't be used to delete specific rows using where clause
Can be rolled back	Can't be rolled back	Can't be rolled back

2.What is the syntax for writing insert queries

We can write insert queries in two ways :

a.insert into <table-name> values (value1,value2,…..)

b.insert into <table-name> (col1,col2,……) values (value1,value2,…..)

3.What is the difference between primary key and unique key

Unique Key	Primary Key
a.A table can contain more than one unique key	a.A table can contain only one primary key (can be one/more columns)
b.Unique key allows one null value	b.Primary key will not allow null values

4.What are DDL and DML commands .

DDL (DataDifinition Language)	DML (Data Manipulation Language)
Create	Select
Alter	Insert
Truncate	Update
Drop	Deletet

5. What is the difference between sub queries and joins

a.Sub Queries :

To write sub queries between two or more tables, there is no need to be relation exist among those tables

b.Joins:

To write joins between two or more tables, there should be relation exist among those tables

6. What are the different  types of joins and explain them

Inner Join

Left Outer Join

Right Outer Join

Full Outer Join

7. Write  syntax for views and stored procedures

View Syntax:

Create view <view-name> as select <column names> from <table-name> where <condition>

Stored Procedure Sysntax:

Create procedure <procedure-name> as begin select <column names> from <table-name> end

Note: These are only sample examples, we can write in different ways also.

8. Relation between views and the their parent tables

If we modify the views table,it effects the parent tables and vice versa

9. Write syntax for update query

Update <table-name> set some_co1l=value1, some_col2=value2 where some_col3=value3

10.What are the different constraints available in sql and Explain

Not Null      -> enforces a column to Not accept Null values

Unique       -> uniquely identifies each record in a database table

Primary Key -> uniquely identifies each record in a database table and it cannot contain NULL values.

Foreign Key -> A Foreign Key in one table points to a PRIMARY KEY in another table.

Check         -> It is used to limit the value range that can be placed in a column.

Default       -> It is used to insert a default value into a column.

11.By default how the column data would display if use order by <column>

By default the order by would display the data in ascending order

Syntax:

a. select <col-names> from <table-name> order by <col-names>

It will display the results in ascending order

select <col-names> from <table-name> order by <col-names> desc

It will display the results in descending order

select <col-names> from <table-name> order by <col-names> asc

It will display the results in ascending order

12.What is the operator in sql to find pattern match

Like

Select <colnames> from <table-name> where <col> like <pattern>

Ex:

a.

Select ename from emp where ename like ‘v%’

It will display all the names which are start with ‘v’

b.

Select ename from emp where ename not like ‘v%’

It will display other than all the names which are start with ‘v’

13. What is the purpose of ‘distinct’ in sql

Distinct is used to display only the different values

Ex:

Select distinct <col-names> from <table-name>

Select distinct ename from emp

14. What is the difference between union and union all

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL

Select <col-names> from <table1> union select <col-names> from <table2>

Select <col-names> from <table1> union all select <col-names> from <table2>