SQL*Plus interview Questions

Showing Questions 1 - 20 of 130 Questions
First | Prev | Next | Last Page
Sort by: 
 | 
Jump to Page:
  •  

    A table has following layout ; CITY, NAME , SEXHow do you write a query to get the count of Male, count of female in a given city XXX.Query result should be in a single row with count of male , count of female as columns?

    Bibhudatta Pand

    • Jun 18th, 2019

    Suppose below is the city table contains NAME SEX Bangalore FEMALE Bangalore MALE Bangalore MALE Bangalore FEMALE Bangalore MALE Bangalore MALE Hyderabad MALE Hyderabad MALE Hyderab...

    Karthik K Shetty

    • Jun 20th, 2016

    Code
    1. SELECT (SELECT COUNT(NAME) FROM TABLE_NAME WHERE UPPER(SEX) LIKE M%) AS COUNT_OF_MALE,
    2.            (SELECT COUNT(NAME) FROM TABLE_NAME WHERE UPPER(SEX) LIKE F%) AS COUNT_OF_FEMALE
    3. FROM DUAL;

  •  

    What is difference between CHAR and VARCHAR2

    Star Read Best Answer

    Editorial / Best Answer

    Answered by: srinivasan

    • Aug 16th, 2005


    A varchar2 datatype, when stored in a database table, uses only the space  
    allocated to it. If you have a varchar2(1999) and put 50 bytes in the table, we  
    will use 52 bytes (leading length byte). 
     
    A char datatype, when stored in a database table, always uses the maximum length  
    and is blank padded. If you have char(1999) and put 50 bytes into it, it will  
    consume 2001 bytes (leading length field is present on char's as well). 
     
    In the database -- a CHAR is a VARCHAR that is blank padded to its maximum  
    length. 

    Nitin Yadav

    • Nov 9th, 2017

    Char is fixed and varchar 2 is variable.

    gopalakrishna

    • Jul 28th, 2015

    Char is a data type based on the given size memory is used
    varchar2 based on the given characters memory is used

  •  

    Difference between a View and Materialized View

    Sai

    • Aug 22nd, 2017

    A view doesnt hold any data it just shows you the statements .... A materialized view holds data and it is used to connect to other server to access data from particular table and it just brings data...

    annaiah

    • Jun 26th, 2014

    In views query result in not stored in disk or database but materialized views allow to store query result in disk or database when we create views using any table rowid of view is same as original ta...

  •  

    What is CYCLE/NO CYCLE in a Sequence

    Answer posted by Scott on 2005-05-25 18:39:48: When you create a sequence with CYCLE option, you are saying that when the sequence reaches its MAXVALUE, it will start over at the MINVALUE. This is not wise if using the sequence for primary key creation.  When you create a sequence with NOCYCLE option, you are saying that when the sequence reaches its MAXVALUE, it will NOT start over at the...

    rahul

    • Aug 6th, 2017

    I tried ....sequence will start from 1

    feliz

    • Aug 15th, 2016

    Kirnapps, this is not correct - CYCLE will restart with the minvalue again - it will not go past the maxvalue, as kiranapps post reply states.

  •  

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

    A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.

    Maheswara Raju. Varakavi

    • Aug 29th, 2016

    If a view is updated, deleted , or inserted will the changes be reflected on the base table? YES--> If it is in the case of simple view (which consists only one base table). NO---->If it is in the c...

    Mahendra K Garnayak

    • Jun 7th, 2016

    Thanks M.R. Varakavi for providing a valuable information.
    could you please confirm in instead of trigger action will be performed on which table in case of join condition

  •  

    What is a join ? Explain the different types of joins ?

    Join is a query which retrieves related columns or rows from multiple tables.Self Join - Joining the table with itself.Equi Join - Joining two tables by equating two common columns.Non-Equi Join - Joining two tables by equating two common columns.Outer Join - Joining two tables in such a way that query can also retrieve rows that do not have corresponding join value in the other table.

    GOPAL

    • Jul 28th, 2015

    Joins are used to join the columns of different tables. Types of Joins: 1. Inner Joins, 2. Outer Joins Inner Joins are sub divided into two types 1. Self Join, 2. Equvi Join Self Join is used to joi...

    sukanya

    • Jun 28th, 2015

    Can any one tell me what is self join? What is difference between GROUP BY and HAVING?

  •  

    If a View on a single base table is manipulated will the changes be reflected on the base table ?

    If changes are made to the tables which are base tables of a view will the changes be reference on the view.

    Andy

    • Jul 9th, 2015

    You cannot associate a view with trigger

    Animesh Mondal

    • May 11th, 2015

    The view must have the Primary Key and NOT NULL columns of the base table for any insert to happen on a view

  •  

    If an unique key constraint on DATE column is created, will it validate the rows that are inserted with SYSDATE ?

    It won't, Because SYSDATE format contains time attached with it.

    Ashima Chhabra

    • Jun 20th, 2015

    No...you can save a single row with the sysdate but not more than 1 on same date because format of date is YYYY-MM-DD and format of datetime is YYYY-MM-DD hh:mm:ss[.nnn] and format of smalldatetime is YYYY-MM-DD hh:mm:ss

  •  

    How many types of joins are there in oracle and what are the difference between them

    Biswadeepak Swain

    • Jun 3rd, 2015

    This is not Cartesian products that one is Cartesian products. Thanks

  •  

    Difference between view and table

    mudassir

    • Mar 26th, 2014

    I have created VIEW in oracle with more than one table and i tried to delete the record by using the command "delete viewname where fieldname = 3 and the record is get delete. Can you plz explain why this happed

    fayaz ahmad khan

    • Sep 23rd, 2013

    View is used for simplicity and for security of a table in simply a query can also called a view and a stored query which is used a lot of time then it can waste our time so a view is used to reduce a time confusion

  •  

    What is Ref cursor when we use ref cursor andadvantage of ref cursor

    Deepika S Verma

    • Mar 10th, 2014

    Ref Cursors are used in two conditions
    (1) When we have a dynamic query, means when we get the information at runtime related to query(like column name, table name etc)
    (2) Or when we want to pass whole resultset to another proc/func.

    suresh reddi

    • Aug 19th, 2011

    Using ref cursor we can pass different values( i.e by changing parameters) to the cursor which is declared in the package specification from the package body. Ref cursors are of 2 types a) weak r...

  •  

    What is difference between TRUNCATE & DELETE

    Answer posted by Scott on 2005-05-25 18:30:04: TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is released back to the server. DELETE is a DML command and can be rolled back.  Both commands accomplish identical tasks (removing all data from a table), but TRUNCATE is much faster.

    Star Read Best Answer

    Editorial / Best Answer

    Answered by: Prangya Sahu

    • Nov 21st, 2005


    1>TRUNCATE is a DDL command whereas DELETE is a DML command.

    2>TRUNCATE is much faster than DELETE.

    Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.

    3>You cann't rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently.

    4>In case of TRUNCATE ,Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired.

    5>You cann't use conditions(WHERE clause) in TRUNCATE.But in DELETE you can write conditions using WHERE clause.

    Shahida Sultana

    • Mar 6th, 2014

    TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesnt use as much undo space as a DELETE.

    saravanan

    • Sep 18th, 2012

    Delete & truncate command will delete entries from a table.. where as we can delete a single entry using delete but incase of truncate it wont be possible..

    using truncate will delete the log files,buffer storage in a table permanently but incase of delete it wont

  •  

    What is ROWID

    Star Read Best Answer

    Editorial / Best Answer

    Answered by: Zimmy

    • Jul 10th, 2005


    ROWID is the psedo columns indicate the stored location of the data physically in the database.

    Ravi

    • Jul 3rd, 2013

    ROWID: It is pseudo column,it has physical address location of row in ORACLE database
    The format is: OOOOOO.FFF.BBBBBB.RRR
    OOOOOO is the object ID
    FFF is the file number
    BBBBBB is the block number
    RRR is the row number

    vikas

    • Dec 21st, 2012

    But according to you it has 16 digits ... and according to my oracle 10g it is showing 18 digits. like AAAC9EAAEAAAABXAAA .. why??

  •  

    Explain the different types of joins

    Star Read Best Answer

    Editorial / Best Answer

    krishna420  

    • Member Since Jul-2008 | Jul 23rd, 2008


    There are 5 types of joins available in sql server

    1) inner join
    2) outer join( left outer join,Right outer join)
    3) Full Join
    4) Cross Join
    5) Self Join

    1) inner Join : Two tables having the common column values go with inner join.

    Example:
    select * from emp inner join empsalary
    on
    emp.empid=empsalary.empid

    2) outer join:
    Outer join has two sub types left outer join,Right outer join
    Left outer join: Taking Left side values of the table checking into right side
    select * from emp left outer join empsalary
    on emp.empid=empsalary.empid
    Right Outer join:Taking Right side values of the table checking left side
    select * from emp right outer join empsalary
    on emp.empid=empsalary.empid

    3) Full join :
    Full join is combined with left outer join ,Right outer join.
    select * from emp full join employee
    on emp.empid=employee.empid

    4) cross Join: cross join is an carteasion product ,its like matrix format here it wont come on condidtion

    select * from emp cross join employee

    Self join :

    Self join can check within the table called self join.

    zunaid

    • Oct 12th, 2012

    Different types of joins

    Natural join
    Inner join
    outer join
    cross join
    equi join
    non equi join
    self join

    vamsi krishna devineni

    • Nov 27th, 2011

    Mainly we uses some joins in the real time environment.. that is 1) Inner join 2) left outer join 3) right outer join 4) full outer join 5) cross join i will explain in easy way so that u can underst...

Showing Questions 1 - 20 of 130 Questions
First | Prev | Next | Last Page
Sort by: 
 | 
Jump to Page: