Top 20 SQL Queries Interview Questions and Answers for Software Testing professionals - SET 3

 

1.     List all the employee details

SQL > Select * from employee;

2.     List all the department details

SQL > Select DEPARTMENT_ID from Employee;

3.     List all job details

SQL > Select JOB_ID from Employee

4.     List all the locations

SQL > Select loc from Employee;

5.List the latest updated record

select TOP 1 * from employee ORDER BY HIREDATE ASC

OR

select * from Employee where HIREDATE=(select max(HIREDATE) from Employee)

 

6.List out first name, last name, salary, commission for all employees

select LAST_NAME,FIRST_NAME, HIREDATE from employee

 

7. List out employee_id,last name,department id for all  employees and rename employee id as “ID  of the employee”, last name as “Name of the employee”, department id as  “department  ID”

 

Select employee_id “id of the employee”, last_name “name", department id as “department id” from employee;

 

8. List out the employees anual salary with their names only.

select first_name as Employee_names, (salary*12) as Anuual_Salary from employee

 

9. List the details about “SMITH”

Select * from employee where first_name=smith

 

10. List out the employees who are working in department 20

Select * from employee where department=20

 

11. List out the employee who are earning salary between 3000 to 4500

Select * from employee where salary between 3000 and 4500

 

12. List out the employees who are working in department 10 or 20

select * from employee where DEPARTMENT_ID in (10,20)

 

13. Find out the employees who are not working in department 10 or 30

select * from employee where DEPARTMENT_ID NOT in (10,20)

 

14. List out the employees whose name start with “S” and end with “H”

Select * from employee where last_name like ‘S%H’

 

15.List out the employees whose name length is 4 and start with “S”

Select * from employee where last_name like ‘S___’

 

16.     List out the employees who are working in department 20 and 30 and draw the salaries more than 3500

select * from employee where DEPARTMENT_ID in (20,30) AND salary=3500

17. List out the employees who are not receiving commission

select * from employee where COMM is NULL

Top 15 SQL Queries Interview Questions and Answers for Software Testing professionals - SET 2

 

1. Write Max salary from each department

Select max(SALARY) from employee group by department

 

2. Select Distinct employee name whose salary between 3000 to 3500

Select DISTINCT(firstName) from employee where SALARY between 3000 AND 3500

3. Write a query to find employee whose salary is is equal or greater than 40000?

Select * from employee where SALARY>=40000

4. Write a Query to find a name of employee whose name start with ‘S’;

Select * from employee where firstName like ‘S%’

         

5. Write a query to find maximum salary for a lady employee

Select * from employee where SALARY IN(select MAX(SALARY) from employee where GENDER=’FEMALE’)

      

6. Write a query to find 3^rd highest salary

Select Top 1 SALARY as ‘3^rd highest salary’ from(select Top 3 salary from employee ORDER BY SALARY DESC) ORDER BY ASC

 

7. Write a query to get 3^rd lowest salary

Select Top 1 SALARY as ‘3^rd lowest salary’ from (Select DISTINCT Top 3 SALARY from employee ORDER BY SALARY ASC) ORDER BY SALARY DESC

                             

8. Write a query to update salary of an employee Yashika to 34000 

Update employee SET SALARY=34000 where FIRSTNAME = ‘Yashika’ and LASTNAME = ‘Reddy’

 

9. Write a query to delete employee details named Deepak

Delete from employee where FIRSTNAME = ‘deepak’;

 

10. Write a query to fetch all details from both Employee and Company details 

Select * from employee, Company WHERE employee.EMP_ID=Company.Employee_Id

 

11. Write a query inner join on Employee and Company details   

Select * from employee INNER JOIN Company ON employee.EMP_ID=Company.Employee_Id

 

12. Write a query Outer join on Employee and Company details

Select * from employee FULL JOIN Company ON employee.EMP_ID=Company.Employee_Id

 

13. Write a query to fetch company name of the lady employee who is getting highest

salary?

Select cmp.COMPANY_NAME From employee emp FULL JOIN Company cmp ON emp.EMP_ID=cmp.Employee_Id

WHERE emp.SALARY IN(SELECT MAX(SALARY) from employee where gender=’Female’);

 

14. Write a query to Create a table and drop same table?

DROP table employee

15. Count the employees departments wise?

Select department_id, count(*), from employee group by department_id

Top 20 SQL Queries Interview Questions and Answers for Software Testing professionals - SET 3

 

Top 15 most important SQL basic concepts for Interview - SET 1

                                        

1. What are the different types of SQL commands? Can you explain those commands?

DDDL— Data Definition Language (CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME)

DML— Data Manipulation Language (SELECT, INSERT, UPDATE, DELETE, MERGE)

DDCL— Data Control Language (GRANT, REVOKE)

TCL — Transaction Control Language (COMMIT, ROLLBACK, SAVEPOINT)

 

2. What is an Index in SQL?

An index is performance-tuning method of allowing faster retrieval of records from the table. An index creates an entry for each value, and it will be faster to retrieve data.

Syntax

CREATE INDEX index_name ON table_name;

DROP INDEX index_name ON table_name;

 

4. What is a view in SQL?

A view is a virtual table, which consists of a subset of data contained in a table.

View can be create on one or many tables

 

CREATE VIEW view name AS SELECT columnl, column2.....

FROM table _ name WHERE [condition];

 

Advantage

It does not occupy Space

It used to retry the result of complex queries that executes often

It restricting data access

 

 

 5. What is a Normalization?

NORMALIZATION is a database design technique that reduces data redundancy and eliminates Undesirable characteristics like Insertion, Update and Deletion Anomalies.

Normalization rule divides larger table into smaller table and links them using relationship

Purpose of normalization in SQL is eliminate redundant or repetitive data and ensure that data is sorted logically

Database normalization process is divides into following categories

First Normal Form (INF)

Second Normal Form (2NF)

Third Normal Form (3NF)

Boyce-Codd Normal Form (BCNF)

Fourth Normal Form (4NF)

Fifth Normal Form (5NF)

6NF (Sixth Normal Form)

               

 

6. What is a Primary Key in SQL?

A primary key is a field in a table, which uniquely identifies each row/record in a database table.

Primary keys must contain unique values.

A primary key column cannot have NULL values.

A table can have only one primary key

A table cannot accept duplicate record

 

CREATE TABLE EMPLOYEE (EMPID INT, NAME VARCHAR (20), AGE INT, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (EMPID));   

 

 

7. What is a Foreign Key in SQL ?

> A foreign key is a key used to link two tables together.

A foreign key in one table used to point primary key in another table.

 

CREATE TABLE EMPLOYEE(

EMP_ID NOT NULL,

NAME VARCHAR (20) NOT NULL,

AGE INT NOT NULL,

ADDRESS CHAR (25),

SALARY DECIMAL (18, 2),

PRIMARY KEY (EMP_ID)

);

 

 

CREATE TABLE COMPANY(

COMPANY ID INT NOT NULL,

COMPANY_NAME VARCHAR (20) NOT NULL,

COMPANY_DOMAIN VARCHAR (20) NOT NULL,

COMPANY _ ADDRESS CHAR (25) ,

EMPLOYEE_ID INT references EMPLOYEE(EMP_ID)

PRIMARY KEY (COMPANY_ID)

);

 

 

8. What is a Query?

> A database query is a request for data or information from a database table or

Combination of tables.

> A database query can be either a select query or an action query.

 

 

9. What is a Sub-query?

> A Subquery is a SQL query within another query.

It is a subset of a Select statement whose return values are used in filtering the

Conditions of the main query.

 

10. What is a Stored procedure ?

A Stored Procedure is a collection of SQL statements that have been created and stored in the

database to perform a particular task.

 

11. What is a Trigger?

A DB trigger is a code or programs that automatically execute with response to some event on a table or view in a database.

It helps to maintain the integrity of the database.

  

12. Explain Distinct in SQL with example?

DISTINCT statement is used with the SELECT statement.

If the records contain duplicate values, then DISTINCT is used to select different values among duplicate records.

Syntax:

SELECT DISTINCT column_name(s) FROM table _ name;

SELECT DISTINCT emp_no FROM Employee;

               

 

13. What are SQL constraints?

Constraints are the rules that we can apply on the type of data in a table.

We can specify the limit on the type of data that can be stored in a particular column in a table using constraints.

NOT NULL - if specify we cannot insert null value               

UNIQUE – If specify all values must be unique (values cannot be duplicate)

PRIMARY KEY – If specify - it

FOREIGN KEY – It is fields, which can uniquely identify each row on another table.

CHECK – It helps to validate values of column to meet a particular condition. It ensure the values in column meets a condition.

DEFAULT – Specify the default values in column. If we forget to insert values, it should take default specified value.

 

 

14. What is the difference between Delete, Truncate and Drop command?

➢Delete command is a DML command; it is used to delete rows from a table. It can be rolled back.

➢Truncate is a DDL command, it is used to delete all the rows from the table and free the space containing the table. It cannot be rolled back.

➢Drop is a DDL command, it removes the complete data along with the table structure (unlike truncate command that removes only the rows). All the tables' rows, indexes, and privileges will also be removed.

 

15. What is a Join and What are the different types at joins!

Join is used to combine data or rows from two or more tables based on a common field between them.

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL JOIN                

Top 15 SQL Queries Interview Questions and Answers for Software Testing professionals - SET 2

Refer this SQL table for all SQL interview questions

 create table Employee

(EMPLOYEE_ID int, LAST_NAME varchar(20), FIRST_NAME varchar(30), MIDDLE_NAME varchar(20),JOB_ID int, MANAGER_ID int, HIREDATE date, SALARY int, COMM int, DEPARTMENT_ID int);

 

insert into Employee(EMPLOYEE_ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, JOB_ID, MANAGER_ID, HIREDATE,SALARY,COMM,  DEPARTMENT_ID )

values(7369,'SMITH','JOHN','Q',667,7902,'17-DEC-84',800,NULL,20);

 

insert into Employee(EMPLOYEE_ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, JOB_ID, MANAGER_ID, HIREDATE,SALARY,COMM,  DEPARTMENT_ID )

values(7499,'ALLEN', 'KEVIN', 'J', 670, 7698, '20-FEB-85', 1600, 300, 30);

 

insert into Employee(EMPLOYEE_ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, JOB_ID, MANAGER_ID, HIREDATE,SALARY,COMM,  DEPARTMENT_ID )

values(7505,'DOYLE', 'JEAN', 'K', 671, 7839, '04-APR-85', 2850, NULL, 30);

 

insert into Employee(EMPLOYEE_ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, JOB_ID, MANAGER_ID, HIREDATE,SALARY,COMM,  DEPARTMENT_ID )

values(7506,'DENNIS', 'LYNN', 'S', 671, 7839, '15-MAY-85', 2750, NULL, 30);

 

insert into Employee(EMPLOYEE_ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, JOB_ID, MANAGER_ID, HIREDATE,SALARY,COMM,  DEPARTMENT_ID )

values(7507,'BAKER', 'LESLIE', 'D', 671, 7839, '10-JUN-85', 2200, NULL, 40);

 

insert into Employee(EMPLOYEE_ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, JOB_ID, MANAGER_ID, HIREDATE,SALARY,COMM,  DEPARTMENT_ID )

values(7521,'WARK', 'CYNTHIA', 'D', 670, 7698, '22-FEB-85', 1250, 500, 30);

EMPLOYEE
EMPLOYEE_ID	LAST_NAME	FIRST_NAME	MIDDLE_NAME	JOB_ID	MANAGER_ID	HIREDATE	SALARY	COMM	DEPARTMENT_ID
7369	SMITH	JOHN	Q	667	7902	17-DEC-84	800	NULL	20
7499	ALLEN	KEVIN	J	670	7698	20-FEB-85	1600	300	30
7505	DOYLE	JEAN	K	671	7839	04-APR-85	2850	NULL	30
7506	DENNIS	LYNN	S	671	7839	15-MAY-85	2750	NULL	30
7507	BAKER	LESLIE	D	671	7839	10-JUN-85	2200	NULL	40
7521	WARK	CYNTHIA	D	670	7698	22-FEB-85	1250	500	30