SQL interview question

SQL EXAM  PAPER

1. There are four coding errors in the following statement. Can you identify them?

SELECT employee_id, last_name

salx12 ANNUAL SALARY

FROM employees;

Ans:

1. Multiplication is used wrong symbol X insteadof *

2. Don’t give spacesal x 12 spaces

3. Don’t give space  ANNUAL SALARY

4. afterlast_name missed comma(,)

2. HOW TO CREATE BACKUP TABLE FOR HR.EMPLOYEES TABLE WITH DATA AND WITHOUT  DATA?

Ans:

createtable<table_name>asselect * fromhr.employees;

createtable<table_name>asselect * fromhr.employeeswhere1=2;

3.WRITE QUERY the HR department needs a report that displays the last name and

salary of employees who earn more than $12,000.

ANS:

selectlast_name , salary fromhr.employeeswhere salary  >12000;

4. WRITE Q QUERY TO REMOVE THE SPACEES and label as display?

INPUT ('SURESH                    ');

ANS:

selectRTRIM('SURESH                    ') as DISPLAY from dual;

5.WRITE A QUETY TO Find the highest, lowest, sum, and average salary of all employees IN EMPLOYEES TABLE. Label the columns

Maximum, Minimum, Sum, and Average, respectively.?

Ans:

selectmax(salary) Maximum ,min(salary) Minimum,sum(salary) Sum,avg(salary) Average fromhr.employees

6.The HR department needs a report of all employees. Write a query to display the last

name, department ID, and department name for all employees and department table ?

ANS:

selecta.last_name,b.department_id,b.department_namefromhr.employees a ,hr.departments b

7. create  EMPLOYEES_VU view, write a query for the HR department to display all

first names and department id?

ANS:

createview EMPLOYEES_VU  asselectfirst_name,department_idfromhr.employees;

8.WRITE QUERY TO ADD CITY COLUMN IN HR.EMPLOYEES TABLE?

ANS:

altertablehr.employeesadd city varchar2(20);

9.write a query to find the names of all synonyms,index,consraints in current database?

Ans:

select * fromuser_synonyms;

select * fromUser_Indexes;

select * fromuser_constraints;

10. create a sequence min 100 and max 120 increments by 2?

ANS:

CREATESEQUENCESS

MINVALUE101

MAXVALUE120

INCREMENTBY2

11.how to drop sequence,index?

ANS:

dropSEQUENCE<SEQUENCE_name>

dropindex<index_name>

12. write a joins query with common department id columns in emplyees and department table?

ANS:

select * fromhr.employeesa,hr.departments b wherea.department_id=b.department_id

               

13. create a table to use default consraints in ENAME column with values "SURESH"?     

ANS:

Createtable<Table_name> (

Eidnumber(3),

Enamevarchar(15)defaultSURESH,

Sal number(5));

14.Write a query to display the current date. Label the column Date?

ANS:

selectsysdate  "DATE" from dual;

15. what is differntbetweem delete and truncate?

ANS:

No.	DELETE	TRUNCATE
1)	DELETE is a DML command.	TRUNCATE is a DDL command.
2)	We can use WHERE clause in DELETE command.	We cannot use WHERE clause with TRUNCATE
3)	DELETE statement is used to delete a row from a table	TRUNCATE statement is used to remove all the rows from a table.
4)	DELETE is slower than TRUNCATE statement.	TRUNCATE statement is faster than DELETE statement.
5)	You can rollback data after using DELETE statement.	It is not possible to rollback after using TRUNCATE statement.

16. What are set operators in SQL? and how to get data only  10,20,30 department id in employees table

17. What are the subsets(like DML) of SQL? write all 14 keywords?

18.create view for first name last name salary in employees table ?

19. create unique index on employees table in employee id column?

20. write query DISTINCT on employee_id column. how to write a query fro "stev" name with like operators in where condition ?