SQL Questions - Page 3

 

51. Is a NULL value is equal zero or a blank space? If not then what is the difference?

A NULL value is not equal as zero or a blank space. But still it’s a value which can occupy space. But blank space and 0 are the character and number respectively.

 

 

52. For a materialized view, is that possible to auto refresh the structure of the base table ? So that whenever that is changed, the corresponding mat view is also changed?

 No, it’s not possible. Mat view can be refreshed only for data, not for the table structure. So, if the structure is changed, we need to drop and re-create the mat view.

 

53. Can any view have any DML operation?

 Yes simple view it can be updated directly and to update complex view use instead of triggers.

 

 

54. Can sorting be possible using a column alias?

Yes. A column alias instead of the actual column name can be used in the ORDER BY clause.

 

 

55. How do you find a number as integer or floating ?

 Select case when 111 – floor(111. < 1 and 111- floor(111. > 0 then ‘Decimal’ else ‘Integer’ end from dual;

Note : Taking example of an integer value : 111

 

 

56. How to find the 4th highest salary from the Emp table? Write the query.

SELECT Salary, salary_rank FROM

(

SELECT DISTINCT Salary,RANK(. OVER (ORDER BY salary DESC. AS salary_rank  FROM hr.Employees

) where salary_rank <= 4;

 

 

57. How do you find a duplicate record?

select attribute id,count(1. from attr_adi group by attribute id having count(1. > 1;

 

 

58. How to Delete a duplicate record?

delete from attr_adi where rowid not in (select min(rowid. from attr_adi group by attributeid.;

 

 

59. How to create a similar table structure, but without records of an existing table ?

 create table emp_1 as select * from emp where 1=2 ;

 

 

60. How to replace all the numeric characters with ‘NUMBERS’ and Alphabets with ‘ALPHABETS’?

Example: [‘a’, ‘z’, ‘E’, 3, ‘f’, 6, 2, 0, ‘’] should output like:

[‘ALPHABETS’, ‘ALPHABETS’, ‘ALPHABETS’, ‘NUMBERS’, ‘ALPHABETS’,’NUMBERS’, ‘NUMBERS’, ‘NUMBERS’, ‘ALPHABETS’]

SELECT col1, case when upper(col1. = lower(col1. then ‘NUMBERS’ else ‘ALPHABETS’ end as alphanumeric from table;

 

 

61.  What is query to find Nth highest salary for employee using With Clause

WITH NTH AS

( SELECT Name, Sal, EID, RN = ROW_NUMBER(.

OVER (ORDER BY Sal DESC.

FROM Emp

)

SELECT Name, Sal, EID

FROM NTH

WHERE RN = N

Note : N means any numbers can be put

 

 

62. How to find a table name with its owner ?

Select owner,table_name from all_tables where table_name = <table> ;

 

 

63. How to put a condition with case insensitivity

 select * from emp where lower(ename. = lower(‘John Smith’.;

 

 

64. Whether Foreign key contains null values ?

No. Although logically it can’t be as any primary key can not contain any null values. But Oracle gives this facility for data convenience.

 

 

 

65. How do you declare a user-defined exception?

User defined exceptions are declared under the DECLARE section, with the keyword EXCEPTION.

Syntax:

<exception_name> EXCEPTION;

 

 

 

66. How to add the email validation using only one query?

SELECT email from emp where NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}’, ‘i’.;

 

67. What is the main difference between a nested table and the normal table?

The main difference between a nested table and the normal table is that the nested table consists of databases that are stored in columns of the table while the normal table consists of all the nested table references. The nested tables have no rows.

 

 

68. Can images be saved in the database?

Yes, images can be saved in the database.

 

 

69. How can one save an image in the database?

An image can be saved in the database by using BLOB. BLOB stands for Binary Large Object. It is a data type which is used for saving images, videos and audio files in the database. The holding capacity of the data type is up to 4GB.