-
Which of the following statements is not true about views?A. You can create an index on viewsB. Views do not have permanent dataC. Views can be based on one or more tablesD. Views can be dropped without dropping tables
-
Remove Primary Key
What happens if we remove primary key from SQL table?
pravesh28
- Mar 9th, 2010
No problem, we use rename operationand we relace the forigen key inti primary key
bikash_tamang
- Jul 10th, 2009
Once the primary key is removed two things will happen:1. The clustered index is also removed which decreases performance and2. The table would then allow duplicate data to be inserted causing data corruption.
-
Which system table contains information on constraints on all the tables created?
USER_CONSTRAINTS
psingla
- Mar 5th, 2010
For SQL Server users:SELECT object_name(id) objname,object_name(constid) constraintname,* from sysconstraints where object_name(id)= ''
malapati
- Oct 8th, 2008
Desc user_constraints;to see constraints on a particular table like emp select constraint_name,constraint_type,table_name from user_constraints;
-
How to display duplicate rows in a table?
Select * from emp where deptno=any(select deptno from emp having count(deptno)>1 group by deptno);
ashwani7jul
- Jan 20th, 2010
DELETE FROM test USING test, test AS vtable WHERE (test.ID > vtable.ID) AND (test.name=vtable.name)Deleted duplicate records query
mayank1710
- Mar 26th, 2009
Select * from acctcycle where acct_nbr in (select acct_nbr from acctcycle group by acct_nbr having count(acct_nbr)>1);
-
-
Difference between Store Procedure and Trigger?
jpundalik
- Dec 30th, 2009
Below are the few difference between Stored Procedure and TriggersBoth are stored objects and stored in data base dictionariesStored Procedure- Can pass parameters- Manual execution requiredTriggers:- No parameters can be passed - Automatical executed when the event occurs
chennoju
- Aug 3rd, 2006
Stored procedure and Trigger both will be in the database in precompiled formate which ready execute, the main difference between these two isto execute the stored procedure programe need to type...
-
User-defined Data Type
Write an SQL statement to create user-defined data types?
jpundalik
- Dec 29th, 2009
Below is the syntax can be used for creating user defined data type in Oraclecreate or replace type type_name as object(add fields and types..)Example:create or replace type address as object(addr1 varchar2(100),City varchar2(30),phone varchar2(15));
-
Oracle Water mark
What is water mark apporach in SQL?
godsrikanth
- Dec 3rd, 2009
A High Water Mark is when a database (or database table) has reached its maximum capacity, for example, the EMPLOYEE table including current and past employees, could have 1000 rows. That would be its...
-
Can you create a table by using pl/sql?if u so justify your answer;
-
When using a count(distinct) is it better to use a self-join or temp table to find redundant data, and provide an example?
gunjan28
- Nov 11th, 2009
When using a count(distinct) it is better to use self-joinExp- in employees table to get manager name and total employees including manager querry will be-id first_name last_name m...
Sujatha
- Aug 1st, 2006
Hi All,Instead of this we can use GROUP BY Clause with HAVING condition.For ex,Select count(*),lastname from tblUsers group by lastname having count(*)>1This query return the duplicated lastnames values in the lastname column from tblUsers table.
-
Given an unnormalized table with columns:Name,Phone number,Address You notice that some records in Name column are duplicate. Write a T-SQL statement to delete all duplicate record under Name column.
goksn
- Sep 7th, 2009
Delete from table-name where Name in (Select Name from table_name group by name having count(name)>1)
rroplekar
- Nov 16th, 2007
I think your query is perfect.select t.name from table t where t.rowid not in ( select max(t1.rowid) from table t1 group by name)
-
What does the following query do?
SELECT SAL + NVL(COMM,0) FROM EMP;This displays the total salary of all employees. The null values in the commission column will be replaced by 0 and added to salary.
-
What are the wildcards used for pattern matching?
_ for single character substitution and % for multi-character substitution
Bhuvaneswari.V
- Aug 22nd, 2009
_ for matching single characters% for matching mutiple characters[] for matching either characters[^] for not matching characteregTable having a column name thenName LIKE 'A_' returns the name...
pujavipin
- Jun 4th, 2009
% = for matching one or more characters._ = for matching excatly one character.[abc]= for matching either 'a' or 'b' or 'c'.[^abc]= for matching character other than 'a','b'and 'c'.
-
What command is used to create a table by copying the structure of another table?
Answer :CREATE TABLE .. AS SELECT commandExplanation : To copy only the structure, the WHERE clause of the SELECT command should contain a FALSE statement as in the following.CREATE TABLE NEWTABLE AS SELECT * FROM EXISTINGTABLE WHERE 1=2;If the WHERE condition is true, then all the rows or rows satisfying the condition will be copied to the new table.
Bhuvaneswari.V
- Aug 22nd, 2009
SELECT * INTO NewTable FROM ExistingTable WHERE 1 = 2 will copy the structure of the existing table without any data. If we need the NewTable with data we can filler the records in WHERE clause.
padma.rkp
- Apr 28th, 2007
IN ORACLE:create table new_table_name asselect col1,col2 from old_table_name where 1=2IN SQL SERVERSelect * into new_table_name from old_table_name where 1 = 2
-
What will be the output of the following query?
SELECT REPLACE(TRANSLATE(LTRIM(RTRIM('!! ATHEN !!','!'), '!'), 'AN', '**'),'*','TROUBLE') FROM DUAL;TROUBLETHETROUBLE
sharathtest
- Aug 16th, 2007
The result is ' TROUBLETHETROUBLE ' Space before and after the text.
-
What is table space?
I want to know the answer
amitarudra
- Aug 7th, 2009
Every database is divided into logical storage unit? called Tablespace. This Tablespace is used to group related logical strutures together.
pujavipin
- Jun 9th, 2009
Table space is the logical storage area on which different users would have quota to create and maintain their objects and data.
-
What operator performs pattern matching?
LIKE operator
pujavipin
- Jun 4th, 2009
LIKE operator can be used in a WHERE clause to search for a particular pattern from columns in a table.Syntax: SELECT column_name(s) from table_name WHERE column_name LIKE pattern.
-
Why the Stored functions cannot called from DML statements?
Ayush.sh
- Mar 31st, 2009
To add in above mentioned constraints for using functions in DML statements:- Called function should not end a transaction
raji_4u
- Sep 19th, 2008
Who says we cannot?We can call stored functions from DML statements but follow some rules
The rules:----------1. Function should use only IN parameter mode. Don't use OUT or IN OUT mode.2. Functions used in Select statements should not update database tables, where as others can update database. -
Which of the following is not true in case of command line arguments?A. The argc parameter is used to hold the number of arguments in the command line and is an integerB. The argv parameter is a pointer to an array of a character pointer and each one points to command line argumentsC. The argv[1] always point to program nameD. None of above
C
Bhoomika sharma
- Dec 16th, 2008
Yes, the argc[] parameter contains an integer i.e. used to hold the number of argumnets in the command line.char*argv[] is used as a pointer to an array of a character pointer and points to the comman...
-
What is the output of the program?#include<stdio.h>#include<conio.h>#define sqr(a) a*avoid main(){int a=10,b=1,c;c=sqr(10+1);printf("Sqr Root of (10+1)is %d", c);getch();}A. 121B. 21C. 13D. Syntax Error
B
Bhoomika sharma
- Dec 16th, 2008
Actually, you can see:sqr(a) is defined as:a*a;=> sqr(10+1)=10+1*10+1=> 10+10+1=>21is the right answer.
SQL Interview Questions
Ans