All Courses
sql interview questions and answers

SQL Interview Questions and Answers

SQL Interview Questions and Answers

In case you’re searching for SQL Interview Questions and answers for Experienced or Freshers, you are at the correct place. SQL Question and Answers listed here would really help to progress your SQL skills, how to install SQL package and get trained with it. We have outlined many questions based on SQL basics, intermediate and advanced levels of concepts. Theoretical questions, examples, scripts, programming languages, and test data are some of the segments of our Q&A designed by experts and skilled people. Topics covered are SQL Query Types, Clauses, Conditions, Functions, Tables and Views, Operators, SQL Syntax, SQL Wildcards, and many more. Professionals like Database Administrators, Data Scientists, Application Developers, Database Developers, and BI experts can make use of this wonderful questionnaire to work on database software. You can easily get a real-time experience on reading all the below post.

There is a parcel of chances from many presumed organizations on the planet. The SQL advertise is relied upon to develop to more than $5 billion by 2020, from just $180 million, as per SQL industry gauges. In this way, despite everything you have the chance to push forward in your vocation in SQL Development. Gangboard offers Advanced SQL Interview Questions and answers that assist you in splitting your SQL interview and procure dream vocation as SQL Developer.

Do you believe that you have the right stuff to be a section in the advancement of future SQL, the GangBoard is here to control you to sustain your vocation. Various fortune 1000 organizations around the world are utilizing the innovation of SQL to meet the necessities of their customers. SQL is being utilized as a part of numerous businesses. To have a great development in SQL work, our page furnishes you with nitty-gritty data as SQL prospective employee meeting questions and answers. SQL Interview Questions and answers are prepared by 10+ years experienced industry experts. SQL Interview Questions and answers are very useful to the Fresher or Experienced person who is looking for the new challenging job from the reputed company. Our SQL Questions and answers are very simple and have more examples for your better understanding.

By this SQL Interview Questions and answers, many students are got placed in many reputed companies with high package salary. So utilize our SQL Interview Questions and answers to grow in your career.

Q1)  What is the sql?

Answer: structure query language.

Q2) What is SQL language?

Answer: DML,DDL  DCL

Q3) How to find the count of the table.

Answer:

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

Q4) What is the syntax for avg?

Answer:

to find the avg number value
SELECT AVG(column_name)
FROM table_name
WHERE condition

Q5) What is the syntax of the sum?

Answer:

finding the sum of the column value
SELECT SUM(column_name)
FROM table_name
WHERE condition;

Q6) What is the order by class ?

Answer:
order the column  with the help of the key column.
syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

Q7) What it group by class?

Answer:

group the result-set by one or more columns.
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
[/su_table]

Q8) How to select the distinct value in table?

Answer:

SELECT DISTINCT column1, column2, ...
FROM table_name;

Q7) How to use the where class?

Answer: Where classes are used for finding the table condition

Q8) Where class is fine the where of the table?

Answer:
syntax :

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Q9) What it the query for delete the table?

Answer:

delete statement is used for delete the table data
DELETE FROM table_name
WHERE condition;

Q10) How to find the min and max value in the table ?

Answer:
to  find the min value by use of min statement.
max statement to find the max value of date in table

min:
SELECT MIN(column_name)
FROM table_name
WHERE condition;
max:
SELECT MAX(column_name)
FROM table_name
WHERE condition;

Q11) What is the alias?

Answer:
alias in a h=the copy of the table

Q12) How to update the table?

Answer:

the update statement is used for update query
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Q13) What is the joint statement?

Answer:
a joint statement is used for joining the table.

Q14. What is the null value?

Answer: The table has no value is known as null

Q15) What is the like statement?

Answer: like the statement is used in the where class

Q16) What is the union operation?

Answer: The union operation is used to combining the column the table

Q17) What are the constraints?

Answer: set rule on the table

Q18) What is the check constraints?

Answer: checking child and parent relation.

Q19) Name any DDL commands

Answer: DDL commands are CREATE, ALTER, DROP, TRUNCATE. Where runs the DDL command the database automatically runs the commit statement if the DDL command is successful else runs troll back command.

Q20) Difference between drop , truncate and delete?

Answer: Drop command is a DDL command and it drops the entire table structure, data and well as the space occupied by it.
Truncate command is a DDL command and it does not drop the structure, but it removes all the data from table including the space occupied by these records in the data blocks. It also updates the High Water tank in the data block. It runs an implicit commit and hence once it is removes the record we cannot get it back.
Delete command is a DML command and it removes the data from the table and stores those data in undo segment, so that when the user fires a rollback, data can be retrieved. If the user fires commit then data is permanently removed from the table. But the space occupied by the records is not removed.

Q21) Name the different types of Joins?

Answer: Equi Join , Non Equi Join , Self Join , Cartesian Join , Outer Joins (Left and right).

Q22) Difference between co-related sub query and Scalar sub query?

Answer: In a co-related sub query, for every record processed by the outer query the inner query is fired once. We use co-related sub query to check for some conditions and if the condition is true then the record is displayed.
Example:
Display department names that have atleast one employee working in it.
Select d.department_name
From departments d
Where exists (select 1 from employees e where e.department_id=d.department_id).

Q23) What is the use of View?

Answer: View is a virtual table which gets data from its base table. View does not store data of its own. View is used to hide the complexity of a query. It also used as security to hide the underlying database object which it is referring to.

Q24) What is a materialized view?

Answer: A materialized view is a view which acts as a normal table . It also gets data from the base tables and it need to refreshed to store the current data . It always has the past data where as a normal view displays the current data.

Q25) Name the constraints using which we can define a foreign key constraint?

Answer: Unique and Primary key constraint

Q26) Name the different types of user privileges?

Answer: System privilege (create any table, create any view etc.) and Object privilege (select, insert, update, delete)

Q27) What is the use of the Index and name the  types of index?

Answer: Index is an database object which is used to speedy retrieval of data from the table.
Types of Index are

  • Normal Index
  • Bitmap Index
  • Reverse Key Index
  • Function index
  • Key compressed index

Q28) Difference between private synonym and public synonym?

Answer: we use the private synonym in the same schema as the user and refer to database objects  from other schemas within the same database. We use the public synonym to refer to database objects from schemas in different databases. Private synonym is visible or can be used only by the user who created it . Public synonym can be accessed by all the users.

Q29) Below is the table structure, write the query to display the employees working in the city of Seattle. Need to display the employee_id, department name and city name in the output.

Answer: Employee

Name Data Type
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME VARCHAR2(25)
EMAIL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE DATE
JOB_ID VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
Departments
Name Date Type
DEPARTMENT_ID NUMBER(4)
DEPARTMENT_NAME VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)
Locations
Name Type
LOCATION_ID NUMBER(4)
STREET_ADDRESS VARCHAR2(40)
POSTAL_CODE VARCHAR2(12)
CITY VARCHAR2(30)
STATE_PROVINCE VARCHAR2(25)
COUNTRY_ID CHAR(2)
select e.employee_id , e.first_name , d.department_name , l.city
from employees e join departments d on (e.department_id=d.department_id)
join locations l on (l.location_id=d.location_id)
where l.city=’Seattle’;

Q30) Display the employees who are working and not working in a department. If they are working display their department names else display No Department assigned.

Answer: Employee

Name Data Type
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME VARCHAR2(25)
EMAIL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE DATE
JOB_ID VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
select e.employee_id ,
nvl(d.department_name,’No Department Assigned’)
department_name
from employees e Left outer join departments d
on (e.department_id=d.department_id)

Q31) Display the employees who are having the same manager id and work in the same department as employee id’s 102 and 104.

Answer:
select e.employee_id ,
e.department_id
from employees e
where (department_id, manager_id)
in (Select department_id , manager_id
from employees
where employee_id in (102,104))
and e.employee_id not in (102, 104)

Q32) Below is the scenario, in which we need to load data into a table called employee_bonus. This table has below structure

Answer:

Column Name Data Type
Employee_id Number(10)
Ename Varchar2(40)
Salary Number(10
Bonus Number(10,2)
Bonus is calculated as 10 percent of an employee’s salary. If the data is already present update the record else insert the record.
merge into employee_bonus eb
using employees e
on (e.employee_id=eb.employee_id)
when matched then
update set ename=e.first_name || e.last_name,
salary=e.salary,
bonus=e.salary* 0.10
when not matched then
insert  values (e.employee_id,
concat(e.first_name, e.last_name) ,
e.salary,
e.salary * 0.10);

Q33) What is PL/SQL?

Answer: PL/SQL is a procedural language that has both interactive SQL and procedurl programming language constructs such as iteration ,conditional branching etc.

Q34) What is the basic structure of PL/SQL?

Answer: PL/SQL uses block structure as its basic structure and  Anonymous blocks or nested blocks can be used in PL/SQL.

Q35) What is a cursor ? Why cursor is required?

Answer: Cursor is named private SQL area from where information can be accessed and Cursors are required to process rows individually for queries returning multiple rows.

Q36) Explain the two types of cursors?

Answer:
There are two types of cursors

  • Implicit cursors
  • Explicit cursors

Q37) What is the cursor attributes used in PL/SQL?

Answer:

  • OPEN
  • ROWCOUNT
  • FOUND

Q38) What is Exception in Oracle?

Answer: Errors during runtime processing due to hardware / network failure / application logic errors are an exception.

Q39) When is the CURSOR_ALRADY_OPEN exception raised?

Answer: This exception is raised when the program the program tries to open an already opened cursor. The cursor  be closed before it can opened again.

Q40) Is there a limit on the size of a PL/SQL  block?

Answer: Currently, the maximum parsed size of a PL/SQL block is 64K and the maxium code size is 100K.

Q41) What are the advantages of Stored Procedures?

Answer: Precompile, Extensible, Modular, Reusable, Maintainable

Q42) Can standalone programs be overloaded?

Answer: No, Stand-alone programs cannot be overloaded. However packaged sub programs can be overloaded within the same package.

Q43) We have a column in the table Employee called phone number in which data is stored in the below format (999)-(99999)-999. Write a query which displays the records which are exactly in the format mentioned.

Answer: Select phone_number from employees where regexp_like(phone_number,’^\([0-9]+\)\-\([0-9]+\)\-[0-9]+’);

Q44) We need to provide privileges for an user ramu for accessing a table in shamu schema . He should be able to see only rows in the employee table , he should not be able to perform any DML actions on the table. What privilege do we need to give for the user shamu.

Answer: Grant select on shamu.employee to ramu;

Q45) DBA has decided to remove permissions given to the user to ramu on employee table in shamu schema , what command does have to run.

Answer: Revoke select from shamu.employee  from ramu;

Q46) Write the query to display the employee hierarchy in the below format /Steven/Den/Shelli . Steven manages Den and Den manages Shelli.

Answer: Select sys_connect_by_path(first_name,’/’)
from employees connect by prior employee_id=manager_id;

Q47) Can we get back to the table after dropping it ?

Answer: Yes.

Q48) Write the command to get back table sales before it was dropped .

Answer: Flashback table sales to before drop;

Q49) Display all employees who have joined in the year 2000 .

Answer: Select employee_id , hire_date
From employee where  extract(a year from hire_date)=2000;

Q50)  Difference between Unique and Primary key constraint.

Answer: Unique constrained column can have n number of null values and it does not accept duplicate values.  It creates an unique index to track duplicate values . The Index name is same as the constraint name. Unique constrained column can be used as foreign key in other tables.
Primary Key constrained column does not accept duplicate and null values . It is used to uniques identify a row in a table.

Q51) Write a query to display the table name and the column defined in it . The query should display the details of the Employees table.

Answer:
Select t.table_name , c.column_id,c.column_name
from user_tables t inner join user_tab_columns c
on (t.table_name=c.table_name)
where t.table_name=’EMPLOYEES’
order by 2 asc;

Q52) Name the data dictionary views used in case of Indexes in Oracle

Answer: USER_INDEXES, USER_IND_COLUMNS, USER_IND_EXPRESSIONS.

Q53) In what type of null functions one or 2 parameters are accepted, and it returns only the first non-NULL parameter?

Answer: COALESCE

Q54) In what type of null functions only 2 parameters are accepted where first parameter is checked for NULL value, if it is NULL then the second parameter is returned, otherwise it returns first parameters? Answer:

Answer: ISNULL

Q55) Which functions copies the data from one table to another table? Explain with query?

Answer: INSERT INTO SELECT
Query: INSERT INTO SUPPLIERS SELECT * FROM PRODUCTSVWHERE CNAME = “MAHA”

Q56) Which JOINS function, return records that has matching values in both the tables? Explain with query?

Answer: Inner Joins, SELECT ORDER.OID, CUSTOMERS.CNAME FROM ORDERS INNER JOINS CUSTOMERS ON ORDER.CID = CUSTOMER.CID

Q56) Which JOINS function, return records from left table and matched records from right table? Explain with query?

Answer: LEFT JOIN, SELECT ORDER.OID, CUSTOMERS.CNAME FROM ORDERS INNER JOINS CUSTOMERS ON ORDER.CID = CUSTOMER.CID

Q57) Which JOINS function, return records when there is match in either right or left table? Explain with query?

Answer: Right Join, SELECT ORDER.OID, CUSTOMERS.CNAME FROM ORDERS INNER JOINS CUSTOMERS ON ORDER.CID = CUSTOMER.CID

Q58) In Which JOINS function, the table is joined itself? Explain with query?

Answer: SELF JOIN, SELECT AD.CustomerName, BD.CustomerName, AD.City
FROM Customers AD, Customers BD
WHERE  AND A.City = B.City

Q59) What is the SQL query to get the Current date and current time?

Answer: SELECT CURDATE(); -> 2018-11-27
SELECT CURTIME();                -> 2018-11-27; 09:10:00

Q60) Write the query where UNION and UNION ALL is used?

Answer:
UNION: It concatenate the result of two tables and omits duplicate records and returns only distinct result set of two or more select statements.
UNION ALL: It returns all the rows including duplicate values  as a result og different select statements.

Q61) How the user will find the differences between the 2 given dates?

Answer: DATE DIFF();

Q62) What is the SQL query to retrieve the day name?

Answer: SELECT DAYNAME(2018-11-27);

Q63) Write the SQL query to replace the sub string with the string?

Answer: SELECT REPLACE(“SQL TUTORIAL”, “SQL”, “HTML”);
HTML TUTORIAL

Q64) Write the SQL query to replace the sub string with the string starting from the right?

Answer: SELECT RIGHT(“SQL TUTORIAL IS GOOD”, 4); => Good

Q65) Write the SQL query to convert the string to UPPERCASE and LOWERCASE?

Answer:
STRING UPPER(“anitha”); => ANITHA
STRING LOWER(“MAHA”); => maha

Q66) Write the SQL Query to concatenate 2 given string?

Answer: SELECT CONCAT(“SQL”, “IS”, “GOOD”); => SQL IS GOOD

Q67) How truncate statement is handled?

Answer: It is used to delete complete data entries from an existing table. TRUNCATE TABLE SCHOOLDATA;

Q68) How delete statement is handled?

Answer: It is used to delete existing records in a table.
DELETE FROM GANGBOARD WHERE STUDENTName=NITHA’;

Q69) How dropping the table is handled?

Answer: It is used to drop an existing table in a database. DROP TABLE GANGBOARD;

Q70)Which is declared to ensure all the column values in the column are different and not not getting repeated?

Answer: UNIQUE

Q71)Write a SQL Query to display date before 25 days?

Answer: SELECT DATEADD(dd, -15, getdate())

Q72) A table can have multiple primary key associated with it?

Answer: False

Q73) Which key is used to link 2 tables together, and where one key in a table refers to primary key of another table?

Answer: Foreign Key

Q74) A table containing a foreign key is called as child key whereas the table containing the candidate key is called the referenced or parent table?

Answer:  True

Q75) Which constraint limits value range for a column?

Answer: Check Constraints

Q76) Which allows the unique number to be generated automatically when a new record is inserted into a table?

Answer: Auto Increment

Q77) Which operator returns true if the operans is within the range of compariosion? Between

Answer: Select * From Person where price is between 50 AND 78;

Q78) Write the SQL query to return one or more records

Answer: SELECT * FROM CUSTOMERS WHERE EXISTS(SELECT * FROM CUSTOMERS WHERE PRICE > 60);

Q79) Mention the aggregation function and its types with syntax?

Answer:

  • AVG(): Returns the average value from specified columns
  • COUNT(): Returns number of table rows
  • MAX(): Returns largest value among the records
  • MIN(): Returns smallest value among the records
  • SUM(): Returns the sum of specified column values
  • SELECT AVG(Price) FROM CUSTOMERS
  • SELECT COUNT(PRICE) FROM CUSTOMERS
  • SELECT SUM(Price) FROM CUSTOMERS
  • SELECT MIN(PRICE) FROM CUSTOMERS
  • SELECT MAX(Price) FROM CUSTOMERS

Q80) Which command returns the result of 2 or more select statements?

Answer: UNION
SELECT CITY FROM CUSTOMERS UNION SELECT CITY FROM PRODUCTS ORDERBY CITIES;

Q81) Which numeric functions returns absolute value of the given number, Write the Syntax

Answer: SELECT ABS(-147.98)  => 148

Q82) How CEILING numeric functions is handled, Write the syntax? =?

Answer: SELECT(90.76) => 91

Q83) Which numeric functions return the no . of truncated values to a certain no. of decimal places?

Answer: Truncate
SELECT TRUNCATE(135.376,2) => 135.37

Q84) Which string functions returns the length of the given string?SELECT CHARLENGTH(GANGBOARD);

Answer: 9

Q85) Which string functions returns position of values in a list of values => SELECT(‘C’, ‘r’, ‘n’, ‘r’) =?

Answer: 2

Q86) Which function compares the two strings if it is equal or not? SELECT STRCMP(“GANGBOARD”, “SQL” )

Answer: False

Q87) Write a SQL query statement to show a student name starts with “M”?

Answer: SELECT * FROM Student WHERE StudentName like ‘%M’;

Q88) Define Normalization in SQL ?

Answer: Normalization is a technique used in SQL to organize the data in such manner that data redundancy will never occur in the database and avoid insert, update and delete conflicts.

Q89) What are all the different types of normalization?

Answer:
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Fourth Normal Form (4NF)

Q90) Explain 1NF:

Answer: First Normal Form (1NF): It removes all duplicate columns from the table and creates table for related data and also identifies unique column values.

Q91) Explain 2NF:

Answer: Follows 1NF and creates, places data subsets in an individual table and defines relationship between tables using primary key.

Q92) Explain 3NF:

Answer: Follows 2NF and removes those columns which are not related to the primary key associated with it.

Q93) Explain 4NF:

Answer: Follows 3NF and do not define the multi-valued dependencies and it is also known as BCNF.

Q94) How the insert statement in the SQL works, Explain it with a query?

Answer: INSERT INTO GANGBOARD (ID,NAME,AGE,ADDRESS,SALARY)VALUES (15, ‘maha’, 21, ‘chennai’,78);

Q95) How the Select statement in the SQL works, Explain it with a query?

Answer: SELECT STUDENTNAME FROM GANGBOARD WHERE STUDMARK > 40;

Q96) How the Where clause in the SQL works, Explain it with a query?

Answer: SELECT STUDENTNAME FROM GANGBOARD WHERE STUDMARK > 40;

Q97) Explain the function of the primary key in the SQL?

Answer: A Primary key is a key which uniquely identify every row in a table. Primary key values can never be reused. It is applied to only one value in a table.

Q98) Explain the various DDL Statements?

Answer: DDL is Data Definition Language. The various DDL statements are the CREATE , ALTER, DROP, RENAME are DDL statements.

Q99) Explain the various DML statements?

Answer: DML is Data Manipulation Language. The various DML statements are the INSERT, UPDATE and DELETE  are DML statements.

Q100) How the UPDATE in the SQL does works, Explain it with a query?

Answer: UPDATE GANGBOARD SET ADDRESS = ‘CHENNAI’ WHERE ID = 16;

Q101) Write the SQL query to display min salary of an employee in the EMPLOYEE Table?

Answer:
SELECT MIN(salary)
FROM employee
WHERE salary < (SELECT MIN(salary) FROM emp)

Q102) Display the employees who are working in the same department as employee id 105.

Answer: Employee

Name Data Type
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME VARCHAR2(25)
EMAIL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE DATE
JOB_ID VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
select e.employee_id ,c.employee_id , c.first_name colleague
, e.department_id
from employees, e join employees c
on (e.department_id=c.department_id and e.employee_id <> c.employee_id)
where e.employee_id=105;

Q103) What is -204 error code ?

Answer: Table or object  not found.

Q104) What is -805 error code ?

Answer: Bind or time stamp miss match.

Q105) What is the -904 error code ?

Answer: Resource unavailable.

Q106) What is -910?

Answer: Time out issue.

Q107) What -911?

Answer: Dead lock issue.

Q108) What is -204?

Answer: Object not define.

Q109) What is -206?

Answer: Column does not exist.

Q110) What is -207?

Answer: Invalid column name.

Q111) What is 209?

Answer: Ambiguous column name.

Q112) What is -501?

Answer: Cursor is not open.

Q113) What is the -502?

Answer: Cursor is already open.

Q114) What -503?

Answer: Update column need to specified.

Q115) What is -504?

Answer: Update cursor is not specified.

Q116) What is the use of utility in db2 ?

Answer: Utility are use for developing the parth that are user for reducing the valiue of on which are help that can have value  .

Q117) What is dbms?

Answer: It is the program to manage the data is known has data base management system .

Q118) Why Sql is call has rdbms?

Answer: Sql is call RDBMS because I handles with  relation data base so we call this has RDBMS.

Q119) What is data ?

Answer: A information is know has data

Q120) What is PK ?

Answer:Unique key without null is known has primary key.

Q121) What join operation?

Answer: Joing two table with join key was know as join that are  use for the joing.

Q122) What is view?

Answer: It is virtual table  which table are column are use are checking  from the main table.

Q123) Why we are using index?

Answer: Index are used for performance tuning the sql query which sort the value.

Q124) What different type of index ?

Answer: Unique index, primary index, clustered  index ,non clustered index.

Q125) What is the importance of course?

Answer: It is use for the fetching the data which are  use the data that can use validate that on row.

Q126) What is SP ?

Answer: Sp is use for fetching group of query that are  using that value.

Q127) Why we use commit?

Answer: To update the data change we will use the commie poinet.

Q128) What is SQLCA?

Answer: It is sql communication area which use the for SQL query development area which are need to have the value.

Q129) Why table space are place in check pending ?

Answer: When we done the load to the child table it will place in table in check pending.

Q130) What is the max length of SQLCA?

Answer: Max length of SQLCA is 136.

Q131) What is error code of -628?

Answer: Mutually exclusive clauses.

Q132) What is the error code of -631?

Answer: To long and too many foreign key.

Q133) What is the error code of -713?

Answer: Replacement value is invalid.

Q134) What is the error code -776?

Answer: Cursor name is not valid.

Q135) What is the error code of -803?

Answer:Insert and update value is invalid because of index space.

Q136) What is the error code of -804?

Answer: Package access is denied.

Q137) What is -811?

Answer: Subquery is Missing.

Q138) What is the error message of -820?

Answer: Sql statement is not valid.

Q139) What is the error message of -840?

Answer: Too many sql statement.
Q140) What is the bind process?
Answer: Building the access path is know has bind process where sql and program are bind together and which has process.

Q141) What are the different types page lock?

Answer: Exclusive, Update, share .

Q142) What is BF?

Answer: It is reserved the storage in the main storage.one are table space are that can that are which have that value .

Q143) Why we need storage group ?

Answer: It store the collection of DASD volumes which is use for moving the value.

Q144) What is DCLGEN?

Answer: It use for that value that can use that can have store have value can have that value to generate that value .

Q145) What are different type of BP?

Answer: BP0,BP1, BP2, BP32 .

Q146) What is the error code for -408?

Answer: The give value is not compatible with given data type.

Q147) What is the error code of -407?

Answer: An update can  not be null value that are use for inset the can on that data.

Q148) What is the error code of -418?

Answer: Use define parameter are not that can use that while that can have value that on that value.

Q149) What is the error code -420?

Answer: Sting funning are not able to accept can have that value use that can on function.

Q150) What is the error code of -554?

Answer: Grant can have the privilege itself that arecan that use that value.

Q151) What is the error code of -601?

Answer: Object are already present in the region.

Q152) What is the error code of -602?

Answer: Specified more column in index.

Q153) What is the error code of -603?

Answer: Unique index can not create due to duplicate value us present in the column.

Q154) What  is the difference between TRUNCATE and DELETE commands?

Answer:

TRUNCATE DELETE
It is a DDL statement It is DML statement
Truncate command will remove all the rows from a table, leaving the table empty. Using Delete command we can remove specific rows from a table using where clause.
Once table is truncated the data is lost and data cannot be rollback. Data can be rollback before issuing COMMIT
It is an auto commit statement. It is not an auto commit statement.
In case of TRUNCATE Trigger doesn’t get fired But in DML commands like DELETE. Trigger get fired
It free up the memory space
Truncate is faster than delete

Q155) Which system table contains information on constraints on all the tables created?

Answer:
USER_CONSTRAINTS table contains the information on constraints created on all the tables.
SYNTAX
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME=’EMPLOYEES’;
OR
SELECT * FROM USER_CONSTRAINTS;

Q156) Difference between decode and case?

Answer:
Decode

  • It specify the single column
  • Can compare only discrete values
  • Cannot process null

Case

  • It specify the multiple columns
  • Can handle range values (between, <, >)
  • Processing time is faster when compared to Decode
  • Can process null
  • All the expressions (columns) must be of the same date types.

Q157) Types of functions

Answer:

Types of functions

  • Single row functions
  • Multiple row functions

Single row functions 

  • Case manipulation functions
  • character manipulation functions
  • Date functions
  • Data type conversion
  • Number Functions
  • General Functions

Multiple row functions
Group Functions
Single row functions
CASE MANIPULATION FUNCTIONS
Lower   – select lower(last_name) from employees;    O/P  –  king
Upper   – select upper(last_name) from employees;   O/P – KING
Inticap  –  select inticatp(last_name) from employees;   O/P – King
CHARACTER MANIP`ULATION FUNCTIONS
SUBSTR    – select substr(last_name,1,3) from employees;
INSTR        – select instr(last_name,’n’) from employees;
LENGTH    – select length(last_name) from employees;
CONCAT      –  select concat(first_name,last_name) from employees; (only 2 arguement)
REPLACE   –   select replace (last_name,’a’,123) from employees;   (a replace with 123)
REVERSE     –     select reverse(last_name) from employees;
LPAD / RPAD   –   select lpad(last_name,20,’#’) from employees;
LTRIM / RTRIM   –  select ltrim(last_name,’k’) from employees;
DATE FUNCTIONS
MONTHS_BETWEEN   –  select months_between(sysdate,’01-jan-10’) from dual;
(only months_between date functions returns a number)
ADD_MONTHS   –  select add_months(sysdate,3) from dual;
NEXT_DAY    –   select next_day(sysdate,’Friday’) from dual;
LAST_DAY   –  select last_day (sysdate) from dual;
DATA TYPE CONVERSION
TO-DATE
TO-CHAR
TO_NUMBER
NUMBER FUNCTIONS
ROUND
TRUNC
MOD
General functions
NVL   – it accepts 2 arguments. If the 1st argument is null it return 2nd argument’s value. Else it returns the 1st argument’s value.
NVL2  – it accepts 3 arguments. 1st argument is null it return 3rd argument’s value. Else it returns the 2nd argument’s value.
NULLIF   –   it accepts 2 arguments. If the both arguments are equal it returns null. Else it returns the 1st argument’s value.
COALESCE    – it accepts N number of arguments. It returns the 1st not null value.
Multiple row functions
Group functions

  • MAX
  • MIN
  • COUNT
  • SUM
  • AVG

Q158) What Operator Performs Pattern Matching?

Answer:
We have to use the LIKE operators in the where clause to perform the pattern matching.
SYNTAX
1.For finding first character pattern matching :-
Select *
From employees
Where last_name like ‘a%’;
2.For finding last character pattern matching :-
Select *
From employees
Where last_name like ‘%a’;
3.For finding the letter in any part of the last_name:-
Select *
From employees
Where last_name like ‘%a%’

Q159) What is database?

Answer:
A database is the collection of information where we can  store the date, manipulate the date and retrieve the stored data.
Database is designed so that we can store and manage the enterprise information

Q160) How can I hide a particular table name of our schema?

Answer:
Using SYNONYMNS we can hide a particular table name of our schema.
SYNTAX
CREATE SYNONYMN EMP for EMPLOYEES;
After creating the above synonymn we can access the data of EMPLOYEES table using EMP as table name as below
SELECT * from EMP;
We can also give alternate name to the objects as well.

Q161) How do I eliminate the duplicate rows ?

Using DISTINCT keyword we can eliminate duplicate records.
SYNTAX
SELECT DISTINCT DEPARTMENT_ID
FROM EMPLOYEES;

Q162) How do I display row number with records?

Answer:
Using ROWNUM keyword we can display row number with records.

  • Rownum is a pseudocolumn.
  • After issuing a select statement, oracle does is to assign an increasing (starting with 1, increased by 1)
  • number to each row returned.

SYNTAX
SELECT ROWNUM,EMPLOYEE_ID
FROM EMPLOYEES;

Q163) Find out nth highest salary from emp table?

Answer:
SYNTAX
select salary from
(select salary,rownum RK from
(select salary from employees
order by salary desc))
where RK=n;

Q164) Display Odd/ Even number of records?

Answer:
We can use the MOD functions is number functions
SYNTAX
ODD:
select * from employees
where (rowid,1) in
(select  rowid, mod(rownum,2) from employees)
Alternative query
select * from employees
where mod(employee_id,2)=0;
EVEN:
select * from employees
where (rowid,0) in
(select  rowid, mod(rownum,2) from employees)
Alternative query
select * from employees
where mod(employee_id,2)=1;

Q165) What is a inline view?

Answer:

  • An inline view is a SELECT statement in the FROM-clause of another SELECT statement.
  • In-line views are commonly used simplify complex queries by removing join operations and condensing several separate queries into a single query.

SYNTAX
SELECT SALARY FROM
(SELECT SALARY , ROWNUN RK FROM
(SELECT SALARY FROM EMPLOYEES ORDER BY SALARY DESC) )
WHERE RK=4

Q166) Which date function returns number value?

Answer:

  • MONTHS_BETWEEN date function returns number value.
  • In that date function, we can able to find the months between the defined date by the user.

SYNTAX
select months_between (sysdate,’01-jan-10′) from dual;
date1  and  date2
The date1 is later than date2 – Result will be in Positive.
The date1 is earlier than date2 – Result will be in Negative.
The defined date format should be in “DD-MM-YY”. If we insert the other date format, the oracle server will through the error.

Q167) What are the more common pseudo-columns?

Answer:
ROWNUM
Rownum is a pseudo column. It numbers the records in a result set. The first record that meets the where criteria  in a select statement is given rownum=1, and every subsequent record meeting that same criteria increases rownum.
ROWID
A rowid is a pseudo column (like versions_xid), that uniquely identifies a row within a table, but not within a  database. It is possible for two rows of two different tables stored in the same cluster to have the same rowid.
SYSDATE
Sysdate is a pseudo column, It’ll show the system date.
SYSTIMESTAMP
Systimestamp is a pseudo column, It’ll show the system date with time
USER
USER is a pseudo column. It returns the name of the user currently connected
UID
UID is a pseudo column,it’ll display the user ID. For every user the oracle server has produces the unique ID for every user.

Q168) What is a relational database management system?

Answer:
RDBMS

  • A database management software system that organizes data into a series of records that are stored in linked tables. This provides the ability to relate different records, fields and tables, and aids data access and data transformation.
  • A relational Databse is collections of relation (or) two dimensional tables. The organization wants to store the information about all the employees in your company. In the relational databases the informational can store in the different tables. All the table have relations

Q169) State the difference between a primary key and foreign key?

Answer:

PRIMARY KEY FOREIGN KEY
Uniquely identifies each row of the table It is referencial identifier
It’ll not allow the null values It’ll allows the null values
It’ll not allow the duplicate values It’ll allows the duplicate values
Only one primary key allow in a table Each table can have more than one foreign key
Primary key is unique foreign key reference as Primary key in another table
Oracle server enforce the uniqueness by creating a unique index on the primary key column Oracle server doesn’t enforce such unique index for the foreign key column.

Q170) What is a synonym?

Answer:
SYNONYM is an alternative name for an object. The advantages are

  1. creating easy reference to a table.
  2.  shorter length object name.

SYNTAX
To create synonym
CREATE SYNONYM SEMP for EMPLOYEES;
To drop synonym
Drop synonym SEMP;
To create the public synonym
Create public synonym dept for deparments;
The public synonym is created so that the all users can access the synonyms. The database administrator can create the public synonym.  And the removal of public synonym can be done only by the database administrator.
Drop public synonym dept;
To display all the synonyms created by the user
Select * from user_synonyms;

Q171) What is a view?

Answer:
A VIEW is a virtual table.

  • View logicaly a subset of data from two or more table.
  • Views contains no data of its own
  • Its a window through which the data from table can view.

Types of Views        

 

SIMPLE VIEWS COMPLEX VIEWS
ONE Number of tables One or more
NO Contains Functions YES
NO Contains groups of data YES
YES DML operations through view Not always
SYNTAX
To create the view
Create or replace view [View_name] AS
Select statement
To drop the view
Drop view [view_name] WITH READ ONLY keyword
Create or replace view [View_name] AS
Select statement
With read only
By adding “with read only” keyword, the DML operators are restricted in the particular view.
Advantages

  • It is used to restrict data access
  • It makes complex query easy
  • Views can hide the complexity of data; for example a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table
  • Views take very little space to store; the database contains only the definition of a view, not a copy of all the data it presents.

Q172) What is a schema?

Answer:

  • A SCHEMA is a collection of database objects.
  • A schema is owned by a database user and has the same name as that user.
  • Schema objects are logical structures created by users to contain, or reference, their data.
  • Schema objects include structures like tables, views, and indexes.

Q173) What is a join, explain the types of joins?

Answer:

  • Selecting data from 2 or more tables is called as joins.
  • To link the two table, first we have to find out the column which is exist in the both the table

Types of joins

  • Natural joins
  • Cross joins
  • Outer joins
  • Equi joins
  • Nonequijoins
  • Self joins

NATURAL JOINS

  • Natural joins clause is based on all columns in the two tables that have the same name.
  • The selected rows frim the two tables that have equal values in all matched column.
  • The natural joins automatically select the column which is common in the both tables. But the column name in the both table should be the same and data types as well

SYNTAX
Select column_name1,column_name2
From table1
NATURAL JOIN table2
CROSS JOINS
The cross join clause produces the cross product of two tables.
SYNTAX
Select column_name1,column_name2
From table1
CROSS JOIN table2
OUTER JOINS
Selecting matched, Un-matched records is called as outer joins.
Types of outer joins

  • left outer joins
  • Right outer joins
  • Full outer joins

Left Outer Joins
A joins between two tables that returns the matched rows as well as the unmatched rows from the left table is called as left outer join
SYNTAX
select e.department_id,
d.department_name,
from departments d left outer join employees e
on (e.department_id = d.department_id);
Right Outer Joins
A joins between two tables that returns the matched rows as well as the unmatched rows from the Right table is called as Right outer join
SYNTAX
select e.department_id,
d.department_name,
from departments d Right outer join employees e
on (e.department_id = d.department_id);
Full Outer joins
A join between that returns the matched rows and the unmatched rows from the both left and right tables is called as full outer joins.
SYNTAX
select e.department_id,
d.department_name,
from departments d full outer join employees e
on (e.department_id = d.department_id);

Q174) What command is used to get back the privileges offered by the GRANT command?

Answer:

  • REVOKE command is used to get back the privileges offered by the GRANT command.
  • The SQL command revokes allows to take away System privileges and object privileges from users and roles

Q175) What command is used to create a table by copying the structure of another table?

SYNTAX
CREATE TABLE EMP AS
SELECT * FROM EMPLOYEES
WHERE 1=2
Invalid Condition
If we doesn’t give the invalid condition in the where clause the whole data will copy to the new table (EMP table). For copying the structure alone we have to give one invalid condtion in the where clause.

Q176) How can variables be passed to a SQL routine?

Answer:

  • using the & symbol a variables can be passed to a SQL routine.
  • It is a SQLPLUS command.

Entering the data in a row
SYNTAX
Insert into t1 values(&a,&b);
We can assign the both the values every time.
Insert into t1 values (&&a,&b);
We can fix the a value as constant and b value will change

Q177) What is the use of the DROP option in the ALTER TABLE command?

Answer:
The use of the DROP option in the ALTER TABLE command is to drop a specific COLUMN.
SYNTAX
ALTER TABLE TABLE_NAME
DROP COLUMN COLUMN_NAME

Q178) What is a Cartesian product?

Answer:

  • A Cartesian product is a result set which contains all the possible combinations of each row in each table included in a query.
  • A Cartesian product is almost always an incorrect result.
  • When a join condition is invalid or omitted completely the result is known as Cartesian product.
  • By using the cross join , the Cartesian product will produce.
  • To avoid the Cartesian product, insert the valid join condition.

Q179) What is the usage of SAVEPOINTS, COMMIT, ROLLBACK?

Answer:
COMMIT is used to confirm the changes done whenever a DML(INSERT/UPDATE/DELETE) statement is issued on a table.
SYNTAX
COMMIT;
ROLLBACK is used to undo the changes done issued by a DML(INSERT/UPDATE/DELETE) statement command.
SYNTAX
ROLLBACK;
Note:- we can undo the changes unless a COMMIT is not issued.
SAVEPOINT

  • We specify the savepoint after the current transaction with specified name.
  • We can rollback to that particular point by “rollback to savepoint_name”

Example:-
Insert1
Update1
Savepoint A      —-First savepoint
Insert 2
Update2
Savepoint B     —–Second savepoint
If we gave the rollback command as
ROLLBACK TO A;
The rollback operators execute upto the savepoint A. The update2 and insert2 will not be committed until we gave “commit” command.

Q180) What is difference between CHAR and VARCHAR2?  What is the maximum SIZE allowed for each type?

Answer:

CHAR VARCHAR
The char is a fixed-length character data type. The varchar is a variable-length character data type.
The storage size of the char value is equal to the maximum size for this column. The storage size of the varchar value is the actual length of the data entered, not the maximum size for this column.
A char can be used when the data entries in a column are expected to be the same size. A varchar can be used when the data entries in a column are expected to vary considerably in size.
Maximum size is 2000 and (minimum/default) is 1 Maximum size is 4000 and (minimum/default) is 1

Q181). How many LONG columns are allowed in a table? Is it possible to use LONG columns in WHERE clause or ORDER BY?

Answer:

  • Only One LONG column is allowed in each table.
  • Long column cannot be included in group by clause (or) order by clause.
  • Long column is not copied when a table is created using sub-query.
  • No constraints can be defined on a long column.

Q182) Can a view be updated/inserted/deleted? If Yes – under what conditions?

Answer:

  • You cannot add the data through a view, if the view contains the following.
  • Group Functions
  • Group by Clause
  • DISTINCT Keyword
  • Pseudo column ROWNUM keyword
  • Columns defined by expressions
  • NOT NULL column in the base table that are not selected by view.

 Q183) What are the advantages of VIEW?

Answer:

  • To restrict data access
  • To make complex queries easy
  • To provide data independence
  • To present different views of same data

Q184) How can I find the total number of records in a table?

Answer:
To find the number of records in a table we can use the COUNT(*) function.
SYNTAX
SELECT COUNT(*) FROM EMPLOYEES;

Q185) How can you compare a part of the name rather than the entire name?

Answer:
By using LIKE operator in the where clause we can compare the part of the name.
Example:-
Select employee_id,last_name from employees
Where last_name like ‘%A’;

Q186) What is GROUP BY?

Answer:

  • The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
  • The GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns.
  • If the arguement / Expression is added in the select statement with the group function. That expression will include with the group by clause in the statement.
  • If you fail to include the expression in the GROUP BY clause, the ORACLE server through the error.
  • The GROUP BY column doesn’t have to be in the select list.

SYNTAX
SELECT EMPLOYEE_ID,FIRST_NAME,MAX(SALARY) FROM EMPLOYEES
WHERE SALARY >15000
GROUP BY EMPLOYEE_ID,FIRST_NAME;

Q187) How can I change my Oracle password?

Answer:
ALTER user hr identified by admin;

 Q188) What is select statement?

Answer: It is a data extract keyword used in SQL to select required data.

 Q189) What is distinct keyword?

Answer: The distinct keyword is used to extract the distinct elements from the select column from a data table in SQL. It excludes duplicate values and produce the distinct values as a result.

 Q190)What is Where clause?

Answer: When a user wants to filter the data based on some conditions, where clause is used to make it. But where clause cannot handle aggregated conditions.

 Q191) What is AND operator in SQL?

Answer: And operator is used to have multiple conditions on the selected data. If all the conditions are satisfied, then it produce result.

 Q192) What is NOT operator in SQL?

Answer: NOT operator in SQL is something that used to exclude the satisfied data on the conditions. If the condition satisfied, it results the data excluding the satisfied data.

 Q193) What is OR operator?

Answer: OR operator is used to have multiple conditions on the selected data, but it accepts the result even if satisfies any one condition.

 Q194) What is Order by Statement in SQL?

Answer: Order by the statement is used to sort the produced result in SQL. When the result Is generated, and we want it to get produced in any of the sorted format, order by is the statement to be used. Also, order by works with both Ascending & Descending order.

 Q195) What is Insert into Statement?

Answer: When you wanted to insert some data to the existing table, we will be using the insert command in SQL. It can do append data from one table to another.

 Q196) What is the NULL value in SQL?

Answer: NULL is basically referred no value. 0 is a value. But NULL is not available. It cannot be referred as 0. It is getting produced when the improper functions executes and not data available for the process.

 Q197) What is UPDATE un SQL?

Answer: When we wanted to modify the existing data from a table, UPDATE keyword can be used to do it. It do update the table based on the condition that we give for.

 Q198) What is DELETE in SQL?

Answer: When we wanted to remove some rows in a table based on some condition, we can use DELETE keyword to do it. Basically the delete keyword deletes all records in a table, if you don’t use where function to give conditions.

 Q199)What is right Join?

Answer: Right join retrieves the entire right table and the matching values from the linked tables.

Q200) What does TOP keyword do?

Answer: The TOP function does select the number of observations from the table. If you have used order by along with the top function, it do take the top records that requested for in the ordered manner.

 Q201) How do we extract the lowest and highest data point from a table?

Answer: We can use MIN() & MAX() functions to extract those information. In the selected data, whatever we get, it basically produces that value.

 Q202) What is the commonly used aggregate function in SQL?

Answer: SUM(), AVG() & COUNT() are some of the commonly & most used aggregate functions in SQL. SQL do support +,-,/,* operators for general functionalities.

 Q203) What does LIKE function do in SQL?

Answer: LIKE function is used to extract the like pattern inputs as a condition in the table.

 Q204) What is WILDCARDS in SQL?

Answer: Wildcard character is something that when a user wants to select the pattern level data. Lets take, you wanted to extract all the customer with name starts with ‘A’, Like operator does this work for you. You can use % or _ for this purpose.

 Q205) What is IN operator in SQL?

Answer: IN operator in SQL does allow you do give multiple conditions in the query. When you wanted to have users from multiple cities, you can use IN function to get it done in one query.

 Q206) What does Between function do?

Answer: Between function does help us to make conditions with ranges. It allows us to make a range as a condition to filter data accordingly. If a user do select a range of salary as a condition, we get extract the data in that range specified.

 Q207) What is SQL Join?

Answer: Join clause is used to combine multiple table rows to start analyse. It helps us to extract data from different table with combinational key as factor.

 Q208) What is INNER join in SQL?

Answer: Inner JOIN do select the data which is common in both the tables. It do take all the columns from both table.

 Q209) What is RIGHT joined in SQL?

RIGHT JOIN do select the complete data from  the RIGHT table and in which is common in the other table. It do take all the columns from both table.

 Q210) What is LEFT join in SQL?

Answer: LEFT JOIN do select the complete data from  the LEFT table and in which is common in the other table. It do take all the columns from both table.

 Q211) What is FULL OUTER join in SQL?

Answer: FULL OUTER JOIN do select the complete data from  both the tables. It do take all the columns from both table. When the observations don’t match, it produces NULL values.

 Q212) What is SELF join in SQL?

Answer: SELF JOIN do select the data from the same table in which is key is matching within the table. Some point in time, to analyse the data from the same table, we use it.

 Q213) What is UNION in SQL?

Answer: UNION is the simple append of data from different tables. To do UNION, we wanted to have the column structure to be same in those tables and the data type of the columns should be the same. If number of columns are different, it would produce NULL for those column.

 Q214) How to display all the information of all the data present in a table?

Answer: Select * from Table_Name

 Q215) How to write a SQL statement to display a string “This is Test”.

Answer: Select “This is Test”

Q216) How to write a SQL query to display Five numbers in Five columns.

Answer: Select 5, 10, 15, 20, 25

 Q217) How to write a SQL query to display the sum of two numbers 20 and 35.

Answer: Select 10 + 25

 Q218) How to write a SQL query to achieve the result of an arithmetic expression which includes all operators like +,-,*,/.

Answer: Select 5 + 10  – 2 *0.5 /5

 Q219) How to write a SQL query to display specific columns from a table.

Answer: Select Column1, Column2 from Table_name

Q220) How to write a SQL query to change the databases.

Answer: Use [Database_name]

 Q221) What are the conditional clauses in SQL?

Answer: Where and having are the only two conditional clauses in SQL.

 Q222) What is the difference between Where and Having?

Answer: Where and Having both are conditional clauses, however where can only be executed before retrieving the output and having can be used only after the output of the data is  retrieved.

Q223) What is the difference between Group and Order by?

Answer: Group by statement is used to classify the output of the data and Order by is used to order the content either by ascending order or by descending order.

 Q224) What does a select statement do?

Answer: Select statement retrieves the data from the selected table with chosen columns.

 Q225) Where do we use From statemen and for what?

Answer: From statement is always used to identify or let know the machine about the table from where we need to retrieve data from.

Q226) How to Write a SQL query which will retrieve the values without any repeats?

Answer: Select Distinct(Column_name) from Table_Name

 Q227) What does a View do?

Answer: View acts as your Table of output from the selection made to retrive certain set of data. They can be called just like your table.
Select * View1

 Q228) Where indexing is used and why?

Answer: Indexing helps the database to search for the selected query and execute it much faster. Simply it helps expediting the output.

 Q229) How to write a SQL statement to display information about people only from Paris.

Answer: Select * from table_name
where city = ‘Paris’

Q230) How to write a SQL statement to display all the information for those customers with grades above 500.

Answer: Select * from table_name
where grade >500

 Q231) How to write a subquery?

Answer: Select * from table_name
where city = (Select city from table_name2 where customer = 5001)

 Q232) What is Union?

Answer: Union is used to join multiple tables by removing the duplicates from it.

 Q233) What is Union All?

Answer: Union All is used to join multiple tables by retrieving all the data from it.

 Q234) What is like, give an example?

Answer: Like operator is used when we are not clear with the characters of the data we need to filter.
Select First_name like ‘Mura%’ from table_name

 Q235) What does an Min function do?

Answer: Min function returns the minimum value in a table/Column.

 Q236) What does a max function does?

Answer: Max function returns the maximum value from the table.

 Q237)What are the three different Rank functions available?

Answer: Rank_avg, Rank_dense and Rank are the three different ranks available.

Q238) What are the different types of Joins?

Answer: Left Join, Right join, Inner join and full join are the joins available.

 Q239)what is Left Join?

Answer: Left join retrieves the entire left table and the matching values from the linked tables.

Q240) What is the difference between TRUNCATE and DELETE commands?

Answer:

TRUNCATE DELETE
It is a DDL statement It is DML statement
Truncate command will remove all the rows from a table, leaving the table empty. Using Delete command we can remove specific rows from a table using where clause.
Once table is truncated the data is lost and data cannot be rollback. Data can be rollback before issuing COMMIT
It is an auto commit statement. It is not an auto commit statement.
In case of TRUNCATE Trigger doesn’t get fired But in DML commands like DELETE. Trigger get fired
It free up the memory space
Truncate is faster than delete

Q241) Which system table contains information on constraints on all the tables created?

Answer:
USER_CONSTRAINTS table contains the information on constraints created on all the tables.
SYNTAX
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME=’EMPLOYEES’;
OR
SELECT * FROM USER_CONSTRAINTS;

Q242) Difference between decode and case?

Answer:
Decode

  • It specify the single column
  • Can compare only discrete values
  • Cannot process null

Case

  • It specify the multiple columns
  • Can handle range values (between, <, >)
  • Processing time is faster when compared to Decode
  • Can process null
  • All the expressions (columns) must be of the same date types.

Q243) Types of functions

Answer:

Types of functions

  • Single row functions
  • Multiple row functions

Single row functions 

  • Case manipulation functions
  • character manipulation functions
  • Date functions
  • Data type conversion
  • Number Functions
  • General Functions

Multiple row functions
Group Functions
Single row functions
CASE MANIPULATION FUNCTIONS
Lower   – select lower(last_name) from employees;    O/P  –  king
Upper   – select upper(last_name) from employees;   O/P – KING
Inticap  –  select inticatp(last_name) from employees;   O/P – King
CHARACTER MANIP`ULATION FUNCTIONS
SUBSTR    – select substr(last_name,1,3) from employees;
INSTR        – select instr(last_name,’n’) from employees;
LENGTH    – select length(last_name) from employees;
CONCAT      –  select concat(first_name,last_name) from employees; (only 2 arguement)
REPLACE   –   select replace (last_name,’a’,123) from employees;   (a replace with 123)
REVERSE     –     select reverse(last_name) from employees;
LPAD / RPAD   –   select lpad(last_name,20,’#’) from employees;
LTRIM / RTRIM   –  select ltrim(last_name,’k’) from employees;
DATE FUNCTIONS
MONTHS_BETWEEN   –  select months_between(sysdate,’01-jan-10’) from dual;
(only months_between date functions returns a number)
ADD_MONTHS   –  select add_months(sysdate,3) from dual;
NEXT_DAY    –   select next_day(sysdate,’Friday’) from dual;
LAST_DAY   –  select last_day (sysdate) from dual;
DATA TYPE CONVERSION
TO-DATE
TO-CHAR
TO_NUMBER
NUMBER FUNCTIONS
ROUND
TRUNC
MOD
General functions
NVL   – it accepts 2 arguments. If the 1st argument is null it return 2nd argument’s value. Else it returns the 1st argument’s value.
NVL2  – it accepts 3 arguments. 1st argument is null it return 3rd argument’s value. Else it returns the 2nd argument’s value.
NULLIF   –   it accepts 2 arguments. If the both arguments are equal it returns null. Else it returns the 1st argument’s value.
COALESCE    – it accepts N number of arguments. It returns the 1st not null value.
Multiple row functions
Group functions

  • MAX
  • MIN
  • COUNT
  • SUM
  • AVG

Q244) What Operator Performs Pattern Matching?

Answer:
We have to use the LIKE operators in the where clause to perform the pattern matching.
SYNTAX
1.For finding first character pattern matching :-
Select *
From employees
Where last_name like ‘a%’;
2.For finding last character pattern matching :-
Select *
From employees
Where last_name like ‘%a’;
3.For finding the letter in any part of the last_name:-
Select *
From employees
Where last_name like ‘%a%’

Q245) What is database?

Answer:
A database is the collection of information where we can  store the date, manipulate the date and retrieve the stored data.
Database is designed so that we can store and manage the enterprise information

Q246) How can I hide a particular table name of our schema?

Answer:
Using SYNONYMNS we can hide a particular table name of our schema.
SYNTAX
CREATE SYNONYMN EMP for EMPLOYEES;
After creating the above synonymn we can access the data of EMPLOYEES table using EMP as table name as below
SELECT * from EMP;
We can also give alternate name to the objects as well.

Q247) How do I eliminate the duplicate rows ?

Using DISTINCT keyword we can eliminate duplicate records.
SYNTAX

SELECT DISTINCT DEPARTMENT_ID
FROM EMPLOYEES;

Q248) How do I display row number with records?

Answer:
Using ROWNUM keyword we can display row number with records.

  • Rownum is a pseudocolumn.
  • After issuing a select statement, oracle does is to assign an increasing (starting with 1, increased by 1)
  • number to each row returned.

SYNTAX

SELECT ROWNUM,EMPLOYEE_ID
FROM EMPLOYEES;

Q249) Find out nth highest salary from emp table?

Answer:
SYNTAX

select salary from
(select salary,rownum RK from
(select salary from employees
order by salary desc))
where RK=n;

Q250) Display Odd/ Even number of records?

Answer:
We can use the MOD functions is number functions
SYNTAX
ODD:

select * from employees
where (rowid,1) in
(select  rowid, mod(rownum,2) from employees)
Alternative query
select * from employees
where mod(employee_id,2)=0;

EVEN:

select * from employees
where (rowid,0) in
(select  rowid, mod(rownum,2) from employees)
Alternative query
select * from employees
where mod(employee_id,2)=1;

Q251) What is a inline view?

Answer:

  • An inline view is a SELECT statement in the FROM-clause of another SELECT statement.
  • In-line views are commonly used simplify complex queries by removing join operations and condensing several separate queries into a single query.

SYNTAX

SELECT SALARY FROM
(SELECT SALARY , ROWNUN RK FROM
(SELECT SALARY FROM EMPLOYEES ORDER BY SALARY DESC) )
WHERE RK=4

Q252) Which date function returns number value?

Answer:

  • MONTHS_BETWEEN date function returns number value.
  • In that date function, we can able to find the months between the defined date by the user.

SYNTAX
select months_between (sysdate,’01-jan-10′) from dual;
date1  and  date2
The date1 is later than date2 – Result will be in Positive.
The date1 is earlier than date2 – Result will be in Negative.
The defined date format should be in “DD-MM-YY”. If we insert the other date format, the oracle server will through the error.

Q253) What are the more common pseudo-columns?

Answer:
ROWNUM
Rownum is a pseudo column. It numbers the records in a result set. The first record that meets the where criteria  in a select statement is given rownum=1, and every subsequent record meeting that same criteria increases rownum.
ROWID
A rowid is a pseudo column (like versions_xid), that uniquely identifies a row within a table, but not within a  database. It is possible for two rows of two different tables stored in the same cluster to have the same rowid.
SYSDATE
Sysdate is a pseudo column, It’ll show the system date.
SYSTIMESTAMP
Systimestamp is a pseudo column, It’ll show the system date with time
USER
USER is a pseudo column. It returns the name of the user currently connected
UID
UID is a pseudo column,it’ll display the user ID. For every user the oracle server has produces the unique ID for every user.

Q254) What is a relational database management system?

Answer:
RDBMS

  • A database management software system that organizes data into a series of records that are stored in linked tables. This provides the ability to relate different records, fields and tables, and aids data access and data transformation.
  • A relational Databse is collections of relation (or) two dimensional tables. The organization wants to store the information about all the employees in your company. In the relational databases the informational can store in the different tables. All the table have relations

Q255) State the difference between a primary key and foreign key?

Answer:

PRIMARY KEY FOREIGN KEY
Uniquely identifies each row of the table It is referencial identifier
It’ll not allow the null values It’ll allows the null values
It’ll not allow the duplicate values It’ll allows the duplicate values
Only one primary key allow in a table Each table can have more than one foreign key
Primary key is unique foreign key reference as Primary key in another table
Oracle server enforce the uniqueness by creating a unique index on the primary key column Oracle server doesn’t enforce such unique index for the foreign key column.

Q256) What is a synonym?

Answer:
SYNONYM is an alternative name for an object. The advantages are

  1. creating easy reference to a table.
  2.  shorter length object name.

SYNTAX
To create synonym
CREATE SYNONYM SEMP for EMPLOYEES;
To drop synonym
Drop synonym SEMP;
To create the public synonym
Create public synonym dept for deparments;
The public synonym is created so that the all users can access the synonyms. The database administrator can create the public synonym.  And the removal of public synonym can be done only by the database administrator.
Drop public synonym dept;
To display all the synonyms created by the user
Select * from user_synonyms;

Q257) What is a view?

Answer:
A VIEW is a virtual table.

  • View logicaly a subset of data from two or more table.
  • Views contains no data of its own
  • Its a window through which the data from table can view.

Types of Views        

SIMPLE VIEWS COMPLEX VIEWS
ONE Number of tables One or more
NO Contains Functions YES
NO Contains groups of data YES
YES DML operations through view Not always
SYNTAX
To create the view
Create or replace view [View_name] AS
Select statement
To drop the view
Drop view [view_name] WITH READ ONLY keyword
Create or replace view [View_name] AS
Select statement
With read only
By adding “with read only” keyword, the DML operators are restricted in the particular view.
Advantages

  • It is used to restrict data access
  • It makes complex query easy
  • Views can hide the complexity of data; for example a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table
  • Views take very little space to store; the database contains only the definition of a view, not a copy of all the data it presents.

Q258) What is a schema?

Answer:

  • A SCHEMA is a collection of database objects.
  • A schema is owned by a database user and has the same name as that user.
  • Schema objects are logical structures created by users to contain, or reference, their data.
  • Schema objects include structures like tables, views, and indexes.

Q259) What is a join, explain the types of joins?

Answer:

  • Selecting data from 2 or more tables is called as joins.
  • To link the two table, first we have to find out the column which is exist in the both the table

Types of joins

  • Natural joins
  • Cross joins
  • Outer joins
  • Equi joins
  • Nonequijoins
  • Self joins

NATURAL JOINS

  • Natural joins clause is based on all columns in the two tables that have the same name.
  • The selected rows frim the two tables that have equal values in all matched column.
  • The natural joins automatically select the column which is common in the both tables. But the column name in the both table should be the same and data types as well

SYNTAX

Select column_name1,column_name2
From table1
NATURAL JOIN table2
CROSS JOINS
The cross join clause produces the cross product of two tables.
SYNTAX
Select column_name1,column_name2
From table1
CROSS JOIN table2
OUTER JOINS
Selecting matched, Un-matched records is called as outer joins.
Types of outer joins

  • left outer joins
  • Right outer joins
  • Full outer joins

Left Outer Joins
A joins between two tables that returns the matched rows as well as the unmatched rows from the left table is called as left outer join
SYNTAX

select e.department_id,
d.department_name,
from departments d left outer join employees e
on (e.department_id = d.department_id);
Right Outer Joins
A joins between two tables that returns the matched rows as well as the unmatched rows from the Right table is called as Right outer join
SYNTAX
select e.department_id,
d.department_name,
from departments d Right outer join employees e
on (e.department_id = d.department_id);
Full Outer joins
A join between that returns the matched rows and the unmatched rows from the both left and right tables is called as full outer joins.
SYNTAX
select e.department_id,
d.department_name,
from departments d full outer join employees e
on (e.department_id = d.department_id);

Q260) What command is used to get back the privileges offered by the GRANT command?

Answer:

  • REVOKE command is used to get back the privileges offered by the GRANT command.
  • The SQL command revoke allows to take away System privileges and object privileges from users and roles

Q261) What command is used to create a table by copying the structure of another table?

SYNTAX

CREATE TABLE EMP AS
SELECT * FROM EMPLOYEES
WHERE 1=2

Invalid Condition
If we doesn’t give the invalid condition in the where clause the whole data will copy to the new table (EMP table). For copying the structure alone we have to give one invalid condtion in the where clause.

Q262) How can variables be passed to a SQL routine?

Answer:

  • using the & symbol a variables can be passed to a SQL routine.
  • It is a SQLPLUS command.

Entering the data in a row
SYNTAX

Insert into t1 values(&a,&b);
We can assign the both the values every time.
Insert into t1 values (&&a,&b);
We can fix the a value as constant and b value will change

Q263) What is the use of the DROP option in the ALTER TABLE command?

Answer:
The use of the DROP option in the ALTER TABLE command is to drop a specific COLUMN.
SYNTAX

ALTER TABLE TABLE_NAME
DROP COLUMN COLUMN_NAME

Q264) What is a Cartesian product?

Answer:

  • A Cartesian product is a result set which contains all the possible combinations of each row in each table included in a query.
  • A Cartesian product is almost always an incorrect result.
  • When a join condition is invalid or omitted completely the result is known as Cartesian product.
  • By using the cross join , the Cartesian product will produce.
  • To avoid the Cartesian product, insert the valid join condition.

Q265) What is the usage of SAVEPOINTS, COMMIT, ROLLBACK?

Answer:
COMMIT is used to confirm the changes done whenever a DML(INSERT/UPDATE/DELETE) statement is issued on a table.
SYNTAX

COMMIT;
ROLLBACK is used to undo the changes done issued by a DML(INSERT/UPDATE/DELETE) statement command.
SYNTAX
ROLLBACK;
Note:- we can undo the changes unless a COMMIT is not issued.
SAVEPOINT

  • We specify the savepoint after the current transaction with specified name.
  • We can rollback to that particular point by “rollback to savepoint_name”

Example:-
Insert1
Update1
Savepoint A      —-First savepoint
Insert 2
Update2
Savepoint B     —–Second savepoint
If we gave the rollback command as
ROLLBACK TO A;
The rollback operators execute upto the savepoint A. The update2 and insert2 will not be committed until we gave “commit” command.

Q266) What is difference between CHAR and VARCHAR2?  What is the maximum SIZE allowed for each type?

Answer:

CHAR VARCHAR
The char is a fixed-length character data type. The varchar is a variable-length character data type.
The storage size of the char value is equal to the maximum size for this column. The storage size of the varchar value is the actual length of the data entered, not the maximum size for this column.
A char can be used when the data entries in a column are expected to be the same size. A varchar can be used when the data entries in a column are expected to vary considerably in size.
Maximum size is 2000 and (minimum/default) is 1 Maximum size is 4000 and (minimum/default) is 1

Q267). How many LONG columns are allowed in a table? Is it possible to use LONG columns in WHERE clause or ORDER BY?

Answer:

  • Only One LONG column is allowed in each table.
  • Long column cannot be included in group by clause (or) order by clause.
  • Long column is not copied when a table is created using sub-query.
  • No constraints can be defined on a long column.

Q268) Can a view be updated/inserted/deleted? If Yes – under what conditions?

Answer:

  • You cannot add the data through a view, if the view contains the following.
  • Group Functions
  • Group by Clause
  • DISTINCT Keyword
  • Pseudo column ROWNUM keyword
  • Columns defined by expressions
  • NOT NULL column in the base table that are not selected by view.

 Q269) What are the advantages of VIEW?

Answer:

  • To restrict data access
  • To make complex queries easy
  • To provide data independence
  • To present different views of same data

Q270) How can I find the total number of records in a table?

Answer:
To find the number of records in a table we can use the COUNT(*) function.
SYNTAX

SELECT COUNT(*) FROM EMPLOYEES;

Q271) How can you compare a part of the name rather than the entire name?

Answer:
By using LIKE operator in the where clause we can compare the part of the name.
Example:-
Select employee_id,last_name from employees
Where last_name like ‘%A’;

Q272) What is GROUP BY?

Answer:

  • The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
  • The GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns.
  • If the arguement / Expression is added in the select statement with the group function. That expression will include with the group by clause in the statement.
  • If you fail to include the expression in the GROUP BY clause, the ORACLE server through the error.
  • The GROUP BY column doesn’t have to be in the select list.

SYNTAX

SELECT EMPLOYEE_ID,FIRST_NAME,MAX(SALARY) FROM EMPLOYEES
WHERE SALARY >15000
GROUP BY EMPLOYEE_ID,FIRST_NAME;

Q273) How can I change my Oracle password?

Answer:
ALTER user hr identified by admin;

Social Share:

0 responses on "SQL Interview Questions and Answers"

Leave a Message

Your email address will not be published. Required fields are marked *