CREATE TABLE - creates a new database table ALTER TABLE - alters (changes) a database table DROP TABLE - deletes a database table CREATE INDEX - creates an index (search key) DROP INDEX -... Read More

1. Statement and Row 2. Statement only 3. Row only 4. Oracle Forms trigger Read More

Normalisation means refining the redundancy and maintain stablisation. there are four types of normalisation are first normal forms, second normal forms, third normal forms and fourth Normal forms. Read More

I Think here we r discussing abt SQL.Replace function is used to replace a string as well as character to another string or character,Translate function exists in ORACLE for translating single character to some other character........ Read More

©http://www.sqlauthority.com ©http://www.sqlauthority.com Using the NOLOCK query optimiser hint is generally considered good practice in order to improve concurrency on a busy system. When the NOLOCK hint is... Read More

Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.... Read More

List few advantages of Stored Procedure. · Stored procedure can reduced network traffic and latency, boosting application performance. · Stored procedure execution plans can be reused, staying cached in SQL Server's... Read More

select top 1 from (select top 2 from (select salary from employee order by salary) order by salary dsc) Read More

Primary key Advantages 1) It is a unique key on which all the other candidate keys are functionally dependent Disadvantage 1) There can be more than one keys on which all the other attributes are dependent on. Foreign Key ... Read More

Indeed both equi join and the Union are very different. Equi join is used to establish a condition between two tables to select data from them.. eg select a.employeeid, a.employeename, b.dept_name from employeemaster a , DepartmentMaster b ... Read More

STUFF function to overwrite existing characters. Using this syntax, STUFF(string_expression, start, length, replacement_characters), string_expression is the string that will have characters... Read More

FOR XML (ROW, AUTO, EXPLICIT) You can execute SQL queries against existing relational databases to return results as XML rather than standard rowsets. These queries can be executed directly or from within stored procedures.... Read More

Select distinct a.au_lname, a.au_fname from titleauthor ta inner join titles t on ta.title_id = t.title_id inner join authors a on ta.au_id = a.au_id Read More

First I will give one example using 'decode' sql>SELECT ENAME,SAL,DECODE(DEPTNO,10,'ACCOUNTING',20,'RESEARCH',30,'SALES',40,'OPERATIONS','OTHERS') "DEPARTMENTS" FROM EMP; I... Read More

-Have an index on TER_MASTER.REPNO and one on ERMAST.REPNO -Be sure to get familiar with EXPLAIN PLAN. This can help you determine the execution path that Oracle takes. If you are using Cost Based Optimizer mode, then be... Read More

Dropping :  (Table structure  + Data are deleted), Invalidates the dependent objects ,Drops the indexes Truncating:  (Data alone deleted), Performs an automatic commit, Faster than delete Delete : (Data alone... Read More

Bit datatype is used to store boolean information like 1 or 0 (true or false). Untill SQL Server 6.5 bit datatype could hold either a 1 or 0 and there was no support for NULL. But from SQL Server 7.0 onwards, bit datatype ... Read More

This particular issue isn't so much about doing something right or wrong, it is about understanding your options so you choose the right one. Both @@IDENTITY and SCOPE_IDENTITY() return the last identity value (primary key) that was entered by... Read More

Pages are low level unit to store the exact data in sql server. Basically, the data will be stored in the mdf, ldf, ndf files. Inturn, pages are logical units available in sql server.The size of the page is 8KB. Eight... Read More

If changes are made to the tables and these tables are the base tables of a view, then the changes will be reference on the view. Read More

Cursors allow row-by-row prcessing of the resultsets. Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information. ... Read More

As the name indicates, denormalization is the reverse process of normalization. It's the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could ... Read More

index are those which are used to speed up row retrieval from a table. oracle automatically creates an index for primary and unique keys. Syntax:Create index index_name ontable_name (column1_name,column2_name,.....)[no sort] Read More

Level: Intermediate Expected answer: This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the... Read More

FLOOR (Transact-SQL) Returns the largest integer less than or equal to the specified numeric expression. Syntax: FLOOR ( numeric_expression ) Arguments: numeric_expression -> Is an expression of the exact numeric or approximate... Read More

i need sql & asp db2 asp.net all other interview questuions Read More

1. cursor action_cursor is 2. select name, rate, action 3. into action_record 4. from action_table; 5. There are no errors in this statement. Read More

8060 bytes. Don't be surprised with questions like 'what is the maximum number of columns per table'. Check out SQL Server books online for the page titled: "Maximum Capacity Specifications". Read More

Triggers are fired implicitly on the tables/views on which they are created. There are various advantages of using a trigger. Some of them are: - Suppose we need to validate a DML statement(insert/Update/Delete) that modifies a table then we can... Read More

The integrity constraints are stored in USER_CONSTRAINTS. Read More

SQL Server can be started from command line, using the SQLSERVR.EXE. This EXE has some very important parameters with which a DBA should be familiar with. -m is used for starting SQL Server in single user mode and -f is used... Read More

Information related to Stored procedure you can see in USER_SOURCE,USER_OBJECTS(current user) tables. Information related to triggers stored in USER_SOURCE,USER_TRIGGERS (current user) Tables. Stored procedure can't be inactive but trigger... Read More

No answer available currently. Be the first one to reply to this question by submitting your answer from the form below.   Read More

Only one LONG column is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause. Read More

10. CREATE OR REPLACE PROCEDURE find_cpt 11. (v_movie_id {Argument Mode} NUMBER, v_cost_per_ticket {argument mode} NUMBER) 12. IS 13. BEGIN 14. IF v_cost_per_ticket > 8.5 THEN 15. SELECT cost_per_ticket ... Read More

Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first. Read up the following topics in... Read More

Oracle's default format for DATE is "DD- MON-YY" Read More

COPY FROM database TO database action - destination_table (column_name, column_name...) USING query eg copy from scott/tiger@ORCL92 - to scott/tiger@ORCL92- create new_emp ? using select * from emp; Read More

Applications that have outgrown their environment are re-engineered to run in a larger environment. This is upsizing. Read More

.if more than one attribute has sane name then it will create the problem. 2.Attribute is the common media through which we can get the information about a entity. Read More

you can hide the table name by creating synonyms. e.g) you can create a synonym y for table x create synonym y for x; Read More

select * from emp group by (empid) having count(empid)>1 Read More

Use DBMS_JOB for scheduling a cron job and DBMS_MAIL to send the results throught email. Read More

Sub-query is a query whose return values are used in filtering conditions of the main query. Read More

- To protect some of the columns of a table from other users. - To hide complexity of a query. - To hide complexity of calculations. Read More

SQL stands for 'Structured Query Language'. Read More

Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table. In SQL Server 6.5 you could define only 3 triggers per table, one for INSERT,... Read More

There is no difference between Cartesian product and cross join. although there syntax are different but they work as a same. Cartesian or cross product selects all rows from both the table. example tableA 6 rows tableB 6 rows Cartesian... Read More

An event handler is a routine that is written to respond to a particular event. Read More

DROP TABLE &TABLE NAME; Variable names should start with an alphabet. Here the table name starts with an '&' symbol. Read More

USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD Read More

& Read More

TRUNCATE commits after deleting entire table i.e., cannot be rolled back. Database triggers do not fire on TRUNCATE DELETE allows the filtered deletion. Deleted records can be rolled back or committed. Database triggers fire... Read More

SAVEPOINTS are used to subdivide a transaction into smaller parts. It enables rolling back part of a transaction. Maximum of five save points are allowed. Read More

1. DBMS_DISPLAY 2. DBMS_OUTPUT 3. DBMS_LIST 4. DBMS_DESCRIBE Read More

Because SALARY is not declared to have NULLs and the employees for whom the salary is not known are also counted. Read More

SELECT FIRSTNAME || ‘ ‘ || LASTNAME FROM EMP Read More

Varchar means fixed length character data(size) ie., min size-1 and max-2000 Varchar2 means variable length character data ie., min-1 to max-4000 Read More

1.RDBMS=DBMS+Refrential Integrity 2. An RDBMS ia one that follows 12 rules of CODD. Read More

Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query. Nested subquery runs only once for the entire nesting (outer) query. It does not contain any... Read More

JOIN is the form of SELECT command that combines info from two or more tables. Types of Joins are Simple (Equijoin & Non-Equijoin), Outer & Self join. Equijoin returns rows from two or more tables joined together based upon a equality... Read More

Foreground process and Background process. Read More

SELECT column_name(s) FROM table_name SELECT DISTINCT column_name(s) FROM table_name SELECT column FROM table WHERE column operator value SELECT column FROM table WHERE column LIKE pattern SELECT column,SUM(column)... Read More

When this clause is used with the DROP command, a parent table can be dropped even when a child table exists. Read More

SELECT REPLACE(TRANSLATE(LTRIM(RTRIM('!! ATHEN !!','!'), '!'), 'AN', '**'),'*','TROUBLE') FROM DUAL;? Read More

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. Read More

UPDATE EMP SET SAL = SAL + 1000, COMM = SAL*0.1;? sal = 11000, comm = 1000. Read More

SQL Injection is when form data contains an SQL escape sequence and injects a new SQL query to be run. Read More

Using ROWID. CONSTRAINTS Read More

When ON DELETE CASCADE is specified Oracle maintains referential integrity by automatically removing dependent foreign key values if a referenced primary or unique key value is removed. Read More

CHAR pads blank spaces to the maximum length. VARCHAR2 does not pad blank spaces. Page 4 For CHAR the maximum length is 255 and 2000 for VARCHAR2. Read More

CYCLE specifies that the sequence continue to generate values after reaching either maximum or minimum value. After pan-ascending sequence reaches its maximum value, it generates its minimum value. After a descending... Read More

1. %found 2. %too_many_rows 3. %notfound 4. %rowcount 5. %rowtype Read More

1. When the transaction is committed 2. During the data manipulation statement 3. When an Oracle supplied package references the trigger 4. During a data manipulation statement and when the transaction is committed... Read More

71. BEGIN 72. theater_pck.v_total_seats_sold_overall := theater_pck.get_total_for_year; 73. END; For this code to be successful, what must be true? 1. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the... Read More

select * from (select rownum a, CLASS_CODE,CLASS_DESC from clm) where a > ( select (max (rownum)-10) from clm) Here N = 10 The following query has a Problem of performance in the execution of the following query... Read More

Level: High Expected answer: Oracle tables always have one guaranteed unique column, the rowid column. If you use a min/max function against your rowid and then select against the proposed primary key you can squeeze out the... Read More

User-Defined Functions allow to define its own T-SQL functions that can accept 0 or more parameters and return a single scalar data value or a table data type. Read More

SELECT * FROM table1 SELECT COUNT(*) FROM table1 SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2 Read More

This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it ... Read More

This displays the total salary of all employees. The null values in the commission column will be replaced by 0 and added to salary. Read More

we can use distinct clause for this type of query.distinct clause is used to remove duplicates from table like select rowid distinct from table name Read More

select max(field1) from tname1 where field1=(select max(field1) from tname1 where field1<(select max(field1) from tname1); Field1- Salary field Tname= Table name. Read More

_ for single character substitution and % for multi-character substitution. Read More

Cache is a special kind of memory. It is used for faster access. Cache has memory allocations and is used for variables or functions that are frequently called for. the cache has limited allocations and thus it is easier to search in cache... Read More

INSERT INTO table_name VALUES (value1, value2,....) INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....) Read More

IS NULL operator. Read More

_ for single character substitution and % for multi-character substitution. Read More

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... Read More

Correlated sub-query is a sub-query, which has reference to the main query. Read More

Retrieves rows in hierarchical order eg. select empno, ename from emp where. Read More

INSTR (String1, String2 (n, (m)), INSTR returns the position of the m-th occurrence of the string 2 in string1. The search begins from nth position of string1. SUBSTR (String1 n, m) SUBSTR returns a character... Read More

1. Implicit cursors are used for SQL statements that are not named. 2. Developers should use implicit cursors with great care. 3. Implicit cursors are used in cursor for loops to handle data processing. 4. Implicit... Read More

respectively. How would the developer define this variable using referential datatypes? 1. Use employee.lname%type. 2. Use employee.lname%rowtype. 3. Look up datatype for EMPLOYEE column on LASTNAME table and use that. ... Read More

Therefore, the SELECT statement cannot be hard-coded and must be created dynamically when the function is executed. Which Oracle supplied package will enable this feature? 1. DBMS_DDL 2. DBMS_DML 3. DBMS_SYN ... Read More

Having clause is used only with group functions whereas Where is not used with. Read More

The global variable @@Version stores the build number of the sqlservr.exe, which is used to determine the service pack installed. To know more about this process visit SQL Server service packs and versions. Read More

1NF: Eliminate Repeating Groups Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain. 2NF: Eliminate Redundant... Read More

1200. Read More

NO. Explanation : The query checks whether a given string is a numerical digit. Read More

step by step i will explain: 1st step : rtrim('!!athen!!','!') its out put come like this "!!athen" 2nd step: ltrim('!!athen','!') its out put come "athen" 3rd step :trancelate('athen','an&... Read More

Question : If Delete Any Table In Back-End Then What Are The Triggers will Fire Automatically (Those Triggers Are Back-End Triggers Only)  Answers: Oracle has Schema triggers (CREATE OR REPLACE TRIGGER ... ON SCHEMA ... Read More

What you want to use here is called a left outer join with Employee table on the left side. A left outer join as the name says picks up all the records from the left table and based on the joint column picks the matching records from the right table... Read More

SINGLE ROW SUBQUERY RETURNS A VALUE WHICH IS USED BY WHERE CLAUSE , WHEREAS SCALAR SUBQUERY IS A SELECT STATEMENT USED IN COLUMN LIST CAN BE THOUGHT OF AS AN INLINE FUNCTION IN SELECT COLUMN LIST Read More

Mutating Table is a table that is currently being modified by an Insert, Update or Delete statement. Constraining Table is a table that a triggering statement might need to read either directly for a SQL statement or indirectly for a declarative... Read More

DELETE FROM table_name WHERE column_name = some_value Delete All Rows: DELETE FROM table_name or DELETE * FROM table_name Read More

SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2 The values can be numbers, text, or dates. Read More

Insert, update, delete, select, references, index, execute, alter, all. Read More

DESC has two purposes. It is used to describe a schema as well as to retrieve rows from table in descending order. Explanation : The query SELECT * FROM EMP ORDER BY ENAME DESC will display the output sorted on ENAME ... Read More

INTERSECT - returns all distinct rows selected by both queries. MINUS - returns all distinct rows selected by the first query but not by the second. UNION - returns all distinct rows selected by either query UNION... Read More

1. RAISE_ERROR 2. SQLERRM 3. RAISE_APPLICATION_ERROR 4. RAISE_SERVER_ERROR Read More

A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key,... Read More

Self join is just like any other join, except that two instances of the same table will be joined in the query. Here is an example: Employees table which contains rows for normal employees as well as managers. So, to find... Read More

UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables. ... Read More

They get stored in master db in the sysxlogins table. Read More

Unindexed table or Heap. Microsoft Press Books and Book On Line (BOL) refers it as Heap. A heap is a table that does not have a clustered index and, therefore, the pages are not linked by pointers. The IAM pages are the only... Read More

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number... Read More

In OLTP - online transaction processing systems relational database design use the discipline of data modeling and generally follow the Codd rules of data normalization in order to ensure absolute data integrity. Using these... Read More

1433 is default tcp/ip socket sql server. Read More

sal = 11000, comm = 1000. Read More

Question : There is a eno & gender in a table. Eno has primary key and gender has a check constraints for the values 'M' and 'F'. While inserting the data into the table M was misspelled as F and F as M. What is... Read More

To retrieve data with SQL one row at a time you need to use cursor processing.Not all relational databases support this, but many do.Here I show this in Oracle with PL/SQL, which is Procedural Language SQL.Cursor processing is done in several... Read More

Denormalization is the process of attempting to optimize the performance of a database by adding redundant data or by grouping data. Normalization is the process of de-composing a relation with anomalies into a well structured relation. Read More

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples: CREATE - to create objects in the database ALTER - alters the structure of the database DROP - delete objects from the database ... Read More

Question : What is difference between Oracle and MS Access? What are disadvantages in Oracle and MS Access? What are features & advantages in Oracle and MS Access?                 ... Read More

Correlated Subquery is a subquery that is evaluated once for each row processed by the parent statement. Parent statement can be Select, Update or Delete. Use CRSQ to answer multipart questions whose answer depends on the value in each row processed... Read More

XML is a very powerful DOM language. Often this language is used with many database applications as a front end, SQL acts as a backend to support the Database queries. This specification has several extensions which defines routines, functions,... Read More

An API, in a Client/ Server context, is a specification of a set of functions for communication between the client and the server. Read More

= Equal <> or != Not equal > Greater than < Less than >= Greater than or equal <= Less than or equal BETWEEN Between an inclusive range LIKE Search for a pattern Read More

SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source WHERE column_name operator value Read More

UPDATE table_name SET column_name = new_value WHERE column_name = some_value Read More

SELECT column1, column2, ... FROM table_name ORDER BY columnX, columnY, .. SELECT column1, column2, ... FROM table_name ORDER BY columnX DESC SELECT column1, column2, ... FROM table_name ORDER BY columnX DESC, columnY ASC Read More

SELECT column_name FROM table_name WHERE column_name IN (value1,value2,..) Read More

USER_CONSTRAINTS. Read More

True. Read More

True. Read More

The privilege receiver can further grant the privileges he/she has obtained from the owner to any other user. Read More

START or @. Read More

RUN. Read More

REVOKE. Read More

('A','1234567890','1111111111'), '1','YES', 'NO' );? NO. Explanation : The query checks whether a given string is a numerical digit. Read More

MONTHS_BETWEEN. Read More

LIKE operator. Read More

It is used to drop constraints specified on the table. Read More

FLOOR. Read More

Data Definition Language (DDL). Read More

CREATE TABLE .. AS SELECT command Explanation: 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... Read More

Will the outputs of the above two commands differ? Both will result in deleting all the rows in the table EMP.. Read More

1200. Read More

ROWID is a pseudo column attached to each row of a table. It is 18 characters long, blockno, rownumber are the components of ROWID. Read More

Integrity constraint is a rule that restricts values to a column in a table. Read More

Maintaining data integrity through a set of rules that restrict the values of one or more columns of the tables based on the values of primary key or unique key of the referenced table. Read More

CHAR, VARCHAR2, NUMBER, DATE, RAW, LONG and LONG RAW. Read More

- To modify the datatype of a column the column must be empty. - To add a column with NOT NULL constrain, the table must be empty. Read More

The integrity constraints can be enabled or disabled by ALTER TABLE ENABLE CONSTRAINT / DISABLE CONSTRAINT. Read More

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

Database link is a named path through which a remote database can be accessed. Read More

Sequence name CURRVAL, sequence name NEXTVAL. It is not possible. Only if you access next value in the session, current value can be accessed. Read More

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. Read More

1. Record type declaration. 2. Opening and parsing of SQL statements. Page 5 3. Fetches records from cursor. 4. Requires exit condition to be defined. Read More

1. LOOP 2. END LOOP 3. IF-THEN 4. EXIT Read More

1. open 2. fetch 3. parse 4. None, cursor for loops handle cursor opening implicitly. Read More

1. It causes the cursor to close 2. It causes the cursor to open 3. It loads the current row values into variables 4. It creates the variables to hold the current row values Read More

1. Only one 2. All that apply 3. All referenced 4. None Read More

22. CREATE OR REPLACE TRIGGER update_show_gross 23. {trigger information} 24. BEGIN 25. {additional code} 26. END; The trigger code should only execute when the column, COST_PER_TICKET, is greater than $3. ... Read More

CREATE OR REPLACE FUNCTION get_budget(v_studio_id IN NUMBER) RETURN number IS v_yearly_budget NUMBER; BEGIN SELECT yearly_budget INTO v_yearly_budget FROM studio WHERE id = v_studio_id; RETURN... Read More

1. An user defined exception must be declared and associated with the error code and handled in the EXCEPTION section. 2. Handle the error in EXCEPTION section by referencing the error code directly. 3. Handle the... Read More

40. CREATE OR REPLACE PROCEDURE calculate_budget IS 41. v_budget studio.yearly_budget%TYPE; 42. BEGIN 43. v_budget := get_budget(11); 44. IF v_budget < 30000 45. THEN 46. set_budget(11,30000000); ... Read More

1. ALTER TRIGGER check_theater ENABLE; 2. ENABLE TRIGGER check_theater; 3. ALTER TABLE check_theater ENABLE check_theater; 4. ENABLE check_theater; Read More

52. CREATE OR REPLACE TRIGGER prevent_gross_modification 53. {additional trigger information} 54. BEGIN 55. IF TO_CHAR(sysdate, DY) = MON 56. THEN 57. RAISE_APPLICATION_ERROR(-20000,Gross receipts cannot be... Read More

1. BEFORE DELETE ON gross_receipt 2. AFTER DELETE ON gross_receipt 3. BEFORE (gross_receipt DELETE) 4. FOR EACH ROW DELETED FROM gross_receipt Read More

61. CREATE OR REPLACE FUNCTION set_budget 62. (v_studio_id IN NUMBER, v_new_budget IN NUMBER) IS 63. BEGIN 64. UPDATE studio 65. SET yearly_budget = v_new_budget WHERE id = v_studio_id; IF SQL%FOUND THEN ... Read More

1. Add RETURN right before the IS keyword. 2. Add RETURN number right before the IS keyword. 3. Add RETURN boolean right after the IS keyword. 4. Add RETURN boolean right before the IS keyword. Read More

1. Altering the argument list of one of the package constructs 2. Any change made to one of the package constructs 3. Any SQL statement change made to one of the package constructs 4. Removing a local variable from the... Read More

1. Any DML statements issued by the construct are still pending and can be committed or rolled back. 2. Any DML statements issued by the construct are committed 3. Unless a GOTO statement is used to continue processing... Read More

Method 1: Select length (translate(trim (column_name),'+-.0123456789',''))from dual; Will give you a zero if it is a number or greater than zero if not numeric (actually gives the count of non... Read More

DELETE removes one or more records in a table, checking referential Constraints (to see if there are dependent child records) and firing any DELETE triggers. In the order you are deleting (child first then parent) There will... Read More

Level: Low Expected answer: %ROWTYPE allows you to associate a variable with an entire table row. The %TYPE associates a variable with a single column type. Read More

Oracle provides the DBMS_ series of packages. There are many which developers should be aware of such as DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB... Read More

PL/SQL tables are scalar arrays that can be referenced by a binary integer. They can be used to hold values for use in later queries or calculations. In Oracle 8 they will be able to be of the %ROWTYPE... Read More

The DECLARE statement is used in PL/SQL anonymous blocks such as with stand alone, non-stored PL/ SQL procedures. It must come first in a PL/SQL stand alone file if it is used. Read More

OPEN then FETCH then LOOP followed by the exit when. If not specified in this order will result in the final return being done twice because of the way the %NOTFOUND is handled by PL/SQL. Read More

SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an... Read More

Use the %ISOPEN cursor status variable. Read More

Use the DBMS_OUTPUT package. Another possible method is to just use the SHOW ERROR command, but this only shows errors. The DBMS_OUTPUT package can be used to show intermediate results from loops and the status of variables... Read More

There are 12 types of triggers in PL/SQL that consist of combinations of the BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and Page 11 ALL key words: BEFORE ALL ROW INSERT AFTER ALL ROW INSERT BEFORE... Read More

By use of the & symbol. For passing in variables the numbers 1-8 can be used (&1, &2,...,&8) to pass the values after the command into the SQLPLUS session. To be prompted for a specific variable, place the... Read More

The best method is to use the CHR() function (CHR(10) is a return/linefeed) and the concatenation function "||". Another method, although it is hard to document and isn?t always portable is to use the return/linefeed as a... Read More

By use of the EXECUTE (short form EXEC) command. Read More

By use of the exclamation point "!" (in UNIX and some other OS) or the HOST (HO) command. Read More

This is called dynamic SQL. An example would be: set lines 90 pages 0 termout off feedback off verify off spool drop_all.sql select ?drop user ?||username||? cascade;? from dba_users where username not in ("SYS?,... Read More

This is best done with the COLUMN command. Read More

The only column that can be grouped on is the "item_no" column, the rest have aggregate functions associated with them. Read More

Level: Intermediate to high Expected answer: The COST based system allows the use of HINTs to control the optimizer path selection. If they can give some example hints such as FIRST ROWS, ALL ROWS, USING INDEX, STAR, even... Read More

A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each... Read More

Level: High Expected answer: Push the processing of the remote data to the remote instance by using a view to pre-select the information for the join. This will result in only the data required for the join being sent across... Read More

Ascending Read More

Level: Intermediate to high Expected answer: The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and... Read More

Level: Intermediate to high Expected answer: The EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must have an explain_table generated in the user you are running the explain plan for. This is created... Read More

Level: Low Expected answer: The SET command in SQLPLUS is used to control the number of lines generated per page and the width of those lines, for example SET PAGESIZE 60 LINESIZE 80 will generate reports that are 60 lines... Read More

Level: Low Expected answer: The SET option TERMOUT controls output to the screen. Setting TERMOUT OFF turns off screen output. This option can be shortened to TERM. Read More

Level: Low Expected answer: The SET options FEEDBACK and VERIFY can be set to OFF. Read More

By use of the SPOOL command Read More

With a WHERE condition in the DELETE statement. Every basic SQL tutorial covers this. Read More

Use an INNER JOIN. Actually not a bad question, if you've never seen or heard of joins, which many new programmers apparently have not. Read More

Also quite a reasonable question. They aren't synonyms in SQL, because each can be used only in very specific places in SQL statements—DISTINCT in a SELECT clause or COUNT(DISTINCT) expression, and UNIQUE... Read More

Outer joins return unmatched rows. Read More

One does grouping, the other does ordering. That might sound flippant, but it is not meant to be. We really do like the short questions, because it gives us so much latitude in choosing the examples with which to... Read More

Write a query to get the second highest amount in the table List last name and hire date of any employee in the same department as Zlotkey I always get puzzled up when asked some typical questions in interview like the... Read More

The SELECT statement lets you select a set of values from a table in a database. The values selected from the database table would depend on the various conditions that are specified in the SQL query. Read More

SELECT * FROM people WHERE empname LIKE '%ab%' Would return a recordset with records consisting empname the sequence 'ab' in empname . Read More

The INSERT statement lets you insert information into a database. Read More

Use the DELETE statement to remove records or any particular column values from a database. Read More

You can sort the results and return the sorted results to your program by using ORDER BY keyword thus saving you the pain of carrying out the sorting yourself. The ORDER BY keyword is used for sorting. SELECT empname, age, city... Read More

You could use the COUNT keyword , example SELECT COUNT(*) FROM emp WHERE age>40 Read More

The GROUP BY keywords have been added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called. Without the GROUP BY functionality, finding the sum for each individual group of... Read More

Blob and Clob. Read More

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major... Read More

Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table. Triggers can't be invoked on demand. They get triggered only when an... Read More

Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table. Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are... Read More

Self join is just like any other join, except that two instances of the same table will be joined in the query. Read More

Use SELECT COUNT(*) ... in query Read More

Use SELECT DISTINCT ... in SQL query Read More

Using SQL INSERT statement Read More

Using DELETE statement Example : DELETE FROM EMP Read More

Specify the indexed columns in the WHERE clause of query. Read More

Use SELECT MAX(...) .. in query Read More

SELECT SUBSTR(FIRSTNAME,1,5) FROM EMP Read More

UNION : eliminates duplicates UNION ALL: retains duplicates Both these are used to combine the results of different SELECT statements. Read More

Once. Read More

BETWEEN supplies a range of values while IN supplies a list of values. Read More

Yes. Read More

LIKE is used for partial string matches. ‘%’ ( for a string of any character ) and ‘_’ (for any single character ) are the two wild card characters. Read More

To do partial search e.g. to search employee by name, you need not specify the complete name; using LIKE, you can search for partial string matches.      Example SQL :  SELECT EMPNO FROM EMP   ... Read More

GROUP BY partitions the selected rows on the distinct values of the column on which you group by. HAVING selects GROUPs which match the criteria specified Read More

SQL  :  SELECT EMPNO         FROM EMP         WHERE PROJECT IS null; Read More

Blob , Clob ( Binary Large Objects, Character Large Objects) Read More

INNER JOIN OUTER JOIN LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN Read More

Transaction - A transaction is a logicl unint of work. All steps must be commited or rolled back. ACID - Atomicity, Consistency, Isolation and Duralbility, these are properties of a transaction. Read More

Materialized Query Tables or MQTs are also known as automatic summary tables. A materialized query table (MQT) is a table whose definition is based upon the result of a query. The... Read More

One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary ... Read More

User defined datatypes let you extend the base SQL Server datatypes by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many... Read More

A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can't have defaults bound to them. Read More

A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction. Read More

An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. Here are the other isolation levels (in the ascending order of ... Read More

Non-clustered index. Important thing to note: By default a clustered index gets created on the primary key, unless specified otherwise. Read More

Hopefully you have experience setting up cluster servers. But if you don't, at least be familiar with the way clustering works and the two clusterning configurations Active/Active and Active/Passive. Read More

Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks). Every lock is a memory structure too many locks would mean, more memory being ... Read More

DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, ... Read More

Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults. Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY For an... Read More

I create a separate index on each column of a table. what are the advantages and disadvantages of this approach? Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker. ... Read More

RAID stands for Redundant Array of Inexpensive Disks, used to provide fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance. Read More

This is a very open ended question and there could be a lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be: No indexes, table scans, missing or out of date ... Read More

Preferring NT authentication, using server, databse and application roles to control access to the data, securing the physical database files using NTFS permissions, using an unguessable SA password, restricting physical... Read More

Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process would wait indefinitely for the other to release the lock, unless one of the ... Read More

Many of us are used to craeting databases from the Enterprise Manager or by just issuing the command: CREATE DATABAE MyDB. But what if you have to create a database with two filegroups, one on drive C and the other on drive... Read More

DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKCATALOG, DBCC CHECKALLOC, DBCC SHOWCONTIG, DBCC SHRINKDATABASE, DBCC SHRINKFILE etc. But there are a whole load of DBCC commands which are very useful for DBAs. Read More

Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these indexes in ... Read More

There are lots of options available, you have to choose your option depending upon your requirements. Some of the options you have are: BACKUP/RESTORE, dettaching and attaching databases, replication, DTS, BCP, logshipping,... Read More

Types of backups you can create in SQL Sever 7.0+ are Full database backup, differential database backup, transaction log backup, filegroup backup. Check out the BACKUP and RESTORE commands in SQL Server books online. Be... Read More

Replication is the process of copying/moving data between databases on the same or different servers. SQL Server supports the following types of replication scenarios: * Snapshot replication * Transactional replication ... Read More

Yes, very much. Check out BEGIN TRAN, COMMIT, ROLLBACK, SAVE TRAN and @@TRANCOUNT Read More

An extended stored procedure is a function within a DLL (written in a programming language like C, C++ using Open Data Services (ODS) API) that can be called from T-SQL, just the way we call normal stored procedures using... Read More

USER_ID(). Also check out other system functions like USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME(). Read More

Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a... Read More

Database normalization is a data design and organization process applied to data structures based on rules that help build relational databases. In relational database design, the process of organizing data to minimize... Read More

A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by... Read More

A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS.Triggers are used to maintain the referential ©http://www... Read More

A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created... Read More

An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and... Read More

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. ... Read More

A table can have one of the following index configurations: No indexes A clustered index A clustered index and many nonclustered indexes A nonclustered index Many nonclustered indexes Read More

Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. ©http://www.sqlauthority.com ... Read More

DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks. E.g. DBCC CHECKDB - Ensures that tables in the db and the... Read More

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying casesensitivity, accent marks,... Read More

Case sensitivity A and a, B and b, etc. Accent sensitivity a and á, o and ó, etc. Kana Sensitivity When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana ... Read More

One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary... Read More

Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the... Read More

This command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into... Read More

Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table. Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs... Read More

Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of parentheses. Sub... Read More

SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For... Read More

There are three types of User-Defined functions in SQL Server 2000 and they are Scalar, Inline Table- Valued and Multi-statement Table-valued. Scalar User-Defined Function A Scalar user-defined function returns one of... Read More

SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties –> Port number.both on client and the server. Read More

Windows mode and mixed mode (SQL & Windows). To change authentication mode in SQL Server click Start, Programs, Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL... Read More

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition') Read More

SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA). It is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the implementation of... Read More

Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to... Read More

The @@ERROR automatic variable returns the error code of the last Transact-SQL statement. If there was no error, @@ERROR returns zero. Because @@ERROR is reset after each Transact-SQL statement, it must be saved to a... Read More

Stored procedures report errors to client applications via the RAISERROR command. RAISERROR doesn't change the flow of a procedure; it merely displays an error message, sets the @@ERROR automatic variable, and... Read More

Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports log shipping.... Read More

A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement. A global temporary table remains in the database permanently, but... Read More

sp_renamedb ‘oldname’ , ‘newname’ If someone is using db it will not accept sp_renmaedb. In that case first bring db to single user using sp_dboptions. Use sp_renamedb to rename database. Use... Read More

Use sp_configure to display or change server-level settings. To change database-level settings, use ALTER DATABASE. To change settings that affect only the current user session, use the SET statement. Read More

The SQL Server 2000-supported replication types are as follows: · Transactional · Snapshot · Merge Snapshot replication distributes data exactly as it appears at a specific moment in time and does not ... Read More

GRANT, DENY, and REVOKE. Read More

When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must... Read More

Shutdown Microsoft SQL Server 2000, and then run Rebuildm.exe. This is located in the Program FilesMicrosoft SQL Server80ToolsBinn directory. In the Rebuild Master dialog box, click Browse. In the Browse for Folder... Read More

Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A... Read More

Data integrity is an important feature in SQL Server. When used properly, it ensures that data is ©http://www.sqlauthority.com ©http://www.sqlauthority.com accurate, correct, and valid. It also acts as a trap... Read More

Relational tables have six properties: · Values are atomic. · Column values are of the same kind. · Each row is unique. · The sequence of columns is insignificant. · The sequence of rows is insignificant.... Read More

De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS... Read More

If @@Rowcount is checked after Error checking statement then it will have 0 as the value of @@Recordcount as it would have been reset. And if @@Recordcount is checked before the error-checking statement then @@Error would... Read More

Scheduled tasks let user automate processes that run on regular or predictable cycles. User can schedule administrative tasks, such as cube processing, to run during times of slow business activity. User can also determine... Read More

BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination. Read More

BulkCopy is a tool used to copy huge amount of data from tables. BULK INSERT command helps to Imports a data file into a database table or view in a user-specified format. Can we rewrite subqueries into simple select... Read More

SQL Server can be lined to any server provided it has OLE-DB provider from Microsoft to allow a link. E.g. Oracle has a OLE-DB provider for oracle that Microsoft provides to add it as linked server to SQL Server group. Read More

SELECT table_name,index_name FROM user_constraints Read More

Microsoft SQL Server 2000 Data Transformation Services (DTS) is a set of graphical tools and programmable objects that lets user extract, transform, and consolidate data from disparate sources into single or multiple... Read More

· Subject-oriented, meaning that the data in the database is organized so that all the data elements relating to the same real-world event or object are linked together; · Time-variant, meaning that the changes to the data... Read More

An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a... Read More

TRUNCATE is a DDL command whereas DELETE is a DML command. Hence DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back.<br>WHERE clause can be used with DELETE and not with TRUNCATE. Read More

USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD. Read More

It is used to drop constraints specified on the table. Read More

Answer:-There is one simple but awkward method is there to find out when the SQL server service is started. The following query will give you the time when the SQL server service is started; select crdate from [master].dbo.sysdatabases ... Read More

A delete statement deletes all records in the table, but it does not free any space. A Truncate statement frees the space as well. However, a truncate can not be rolled back but delete can be rolled back. Read More

USER_CONSTRAINTS. Read More

Question : State true or false. !=, <>, ^= all denote the same operation.       Answers: True. ^= Not same as <> or !=     Read More

The Left Outer Join returns all the rows from the Left Table with the values that match with the Key from the second table. The Right Outer Join returns all the rows from the Right Table with the values that match with the key from the first... Read More

Question : Write a SQL statement to draw the second highest salary in Employee Table (Using Sub query) Table structure is: Employe ID Name Salary             Answers:   For MS SQL... Read More

Select * from global_name; This will give the datbase name which u r currently connected to..... Read More

Inserting a new Column into a table: General Syntax: insert into tablename(columnname datatype); Example: insert into employee(salary number(10)); Read More

select * from emp where rowid in ( select rowid from emp where rownum <=&upto minus select rowid from emp where rownum <&startfrom) from this you can select between any range. Read More

Question : lets i have a table "bapi" having 6 field like this f1 f2 f3 f4 f5 f6 ... ... .. ... ... ... p r o m o d i want to retrive all the data by using select statement and my output will be look like this: xyz ... p r ... Read More

Instead of this we can use GROUP BY Clause with HAVING condition. For ex, Select count(*),lastname from tblUsers group by lastname having count(*)>1 This query return the duplicated lastnames values in the lastname column from tblUsers... Read More

There are clustered and nonclustered indexes. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index... Read More

SELECT COL1 FROM TAB1 WHERE COL1 IN (SELECT MAX(COL1) FROM TAB1 GROUP BY COL1 HAVING COUNT(COL1) > 1 ) Read More

Question : on index why u need indexing ? where that is stroed and what u mean by schema object? for what purpose we are using view Answers: We cant create an Index on Index.. Index is stoed in user_index table.Every... Read More

It is set of rules that have been established to aid in the design of tables that are meant to be connected through relationships. This set of rules is known as Normalization. Benefits of normalizing your database will include: √ Avoiding... Read More

On Primary Key column. Read More

Oracle is based on RDBMS. SQL is Structured Query Language. SQL Server is another tool for RDBMS provided by MicroSoft. Read More

True. Read More

No answer available currently. Be the first one to reply to this question by submitting your answer from the form below.   Read More

When u give SELECT * FROM EMP; the server check all the data in the EMP file and it displays the data of the EMP file Read More

Both will result in deleting all the rows in the table .TRUNCATE call cannot be rolled back as it is a DDL command and all memory space for that table is released back to the server. TRUNCATE is much faster.Whereas DELETE call is an DML command and... Read More

A materialized view is a database object that contains the results of a query. They are local copies of data located remotely or used to create summary tables based on aggregation of a tables data. Materialized views which store data based on the... Read More

Union- this gives distinct rows and also sort the data in ascending order. Union All- this gives all the rows and does not sort the rows. Read More

Both primary key and unique key are used to identify a record(row)of a table. Unique key accepts null value but primary key doesn't accept null values. primary key creates clustered index and unique key creates non clustered index. Read More

Use the command datapart(dw,filed) and if it returns 1 then it is first day of the week(Monday) Read More

There are 5 normal forms. It is necessary for any database to be in the third normal form to maintain referential integrity and non-redundance.<br><br>First Normal Form: Every field of a table (row,col) must contain an atomic value<br... Read More

It depends upon number of rows in table. This query will print 2 as many times as rows in table. Read More

Uset the LIST or L command to get the recent one from SQL Buffer Read More

TRANSFORM Count(Roll_no) AS CountOfRoll_no SELECT Academic_Status FROM tbl_enr_status GROUP BY Academic_Status PIVOT Curnt_status; Read More

We can do it by the following command:create or replace directory as 'c: mp' Read More

For case sensitive string comparison in SQL one has to use substring() and ascii() functions in the following way. Get first character of both strings using substring function as substring(str1, 1, 1) Find ascii value of both characters and... Read More

Joins are used to combine tables. There are 3 type of joins. self joines, outer joines and inner joins Read More

If you have the better answer, then send it to us. We will display your answer after the approval. Read More

Table - Tbl_Test_Salary Column - int_salary select max(int_salary) from Tbl_Test_Salary where int_salary in (select top 10 int_Salary from Tbl_Test_Salary order by int_salary) Read More

A database is a collection of data that is organized so that its contents can easily be accessed, managed and updated. another definition is: A collection of information organized in such a way that a computer program can quickly select desired... Read More

Table-space is a physical concept.it has pages where the records of the database is stored with a logical perception of tables.so tablespace contains tables. Read More

First it will look into the System Global Area (SGA) weather the query is been exectued earlier. If it exist, it would retrive the same output present in memory. If not the query we typed is complied and the resulting parse tree and excution plan... Read More

u can backup the sql files through backup utilities or some backup command in sql . SAP is ERP software for the organization to integrate the software . Read More

Assures database data and structures reflects all changes made to them in the correct sequence. Locks ensure data integrity and maximum concurrent access to data. Commit statement releases all locks. Types of locks are given below. Data Locks... Read More

When a stored procedure is called, only the procedure call is sent to the server and not the statements that the procedure contains. Read More

Inheritance is a method by which properties and methods of an existing object are automatically passed to any object derived from it. Read More

No answer available currently. Be the first one to reply to this question by submitting your answer from the form below.   Read More

SQL: is a structured query language which subdivided in to 5 categories like<br>DDL, DML, DQL, DCL, TCL<br>PLSQL: is a programming language structured query language which consists of procedure, function, triggers, collection. Read More

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. Read More

From execution plan, first you have to check whether the query uses index scan or index seek. if it is index scan then you have to make it as index seek. and then you have to see the subtree cost of the query. always it should be 0.0003 m.seconds. ... Read More

Answers: SELECT SAL + NVL(COMM,0) FROM EMP; It gives the added value of sal and comm for each employee in the emp table. NVL(null value) replaces null with 0. Read More

EXISTS is efficient bcose, 1.Exists is faster than IN clause. 2.IN check returns values to main query where as EXISTS returns Boolean (T or F). Read More

Open Query Analyzer And then Press Ctrl + T After that run the query . It will ask for the path to save the file. Read More

COMMIT command is used if you are satisfied with the database and you don?t want any changes to be done to the database. Changes are permanent when you use COMMIT. ROLLBACK changes or discards all the data prior to COMMIT command. Database changes... Read More

The main difference between the IN and EXISTS predicate in subquery is the way in which the query gets executed. IN -- The inner query is executed first and the list of values obtained as its result is used by the outer query.The inner query is... Read More

Clustered Index:- A Clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table may have only one clustered index.Non-Clustered Index:- A Non-Clustered index is a special type of... Read More

select empname,max(salary) from employee group by empname Read More

The query will be :delete from tabname where rowid not in (select max(rowid) from tabname group by name )Here tabname is the table name. Read More

it is assumed that u have created a single table for populating data of Employee and Manager (.a Manager is also an Employee so s/he will be residing in the same table) .. The sample data would be like this EmployeeID ----- EmployeeName -------... Read More

There is a slight chance the column "a" has a value of 0 which is not null. In that case, You'll loose the information. There is another way of searching the first not null value of a column: select column_name from table_name where... Read More

The SQL JOIN statement is used to combine the data contained in two relational database tables based upon a common attribute. <br><br>Basically there are two types of Joins<br><br>1. Inner Join<br><br>An inner... Read More

IF u get this error, then there is a problem with the Java program, U may open the cursor with the Readonly mode.Change the Resultset type then you will get it.This is not problem with the Oracle Read More

We can call user defined packaged functions in the SELECT statement.<br><br>e.g. SELECT pkg.test(10) FROM DUAL;<br> Read More

Pattern matching operator is LIKE and it has to used with two attributes <br><br>1. % and <br><br>2. _ ( underscore )<br><br>% means matches zero or more characters and under score means matching exactly one... Read More

Refereence cursor is dynamic cursor used with SQL statement like For select* from emp Read More

SQLServer is an RDBMS just like oracle,DB2 from Microsoft<br><br>whereas <br><br>Structured Query Language (SQL), pronounced "sequel", is a language that provides an interface to relational database systems. It was developed... Read More

SELECT COL1 FROM TAB1 WHERE COL1 IN (SELECT MAX(COL1) FROM TAB1 GROUP BY COL1 HAVING COUNT(COL1) > 1 ) Read More

it's right TROUBLETHETROUBLE Read More

SQLPlus is an application that recognizes & executes SQL commands & specialized SQL*Plus commands that can customize reports, provide help & edit facility & maintain system variables. Read More

Null value function converts a null value to a non-null value for the purpose of evaluating an expression. Numeric Functions accept numeric I/P & return numeric values. They are MOD, SQRT, ROUND, TRUNC & POWER. Read More

Date Functions are ADD_MONTHS, LAST_DAY, NEXT_DAY, MONTHS_BETWEEN & SYSDATE. Read More

Character Functions are INITCAP, UPPER, LOWER, SUBSTR & LENGTH. Additional functions are GREATEST & LEAST. Group Functions returns results based upon groups of rows rather than one result per row, use group functions. They are AVG, COUNT, MAX... Read More

Compute command control computations on subsets created by the BREAK command. Read More

Spool command creates a print file of the report. Read More

Indexes are optional structures associated with tables used to speed query execution and/or guarantee uniqueness. Create an index if there are frequent retrieval of fewer than 10-15% of the rows in a large table and columns are referenced... Read More

Max. columns in a table is 255. Max. Char size is 255, Long is 64K & Number is 38 digits. Cannot Query on a long column. Char, Varchar2 Max. size is 2000 & default is 1 byte. Number(p,s) p is precision range 1 to 38, s is scale -84 to 127.... Read More

Where clause, Group By clause, Having clause, Order By clause & Select. Read More

Commit is an event that attempts to make data in the database identical to the data in the form. It involves writing or posting data to the database and committing data to the database. Forms check the validity of the data in fields and records... Read More

Deadlock is a unique situation in a multi user system that causes two or more users to wait indefinitely for a locked resource. First user needs a resource locked by the second user and the second user needs a resource locked by the first user. To... Read More

SQLLoader is a product for moving data in external files into tables in an Oracle database. To load data from external files into an Oracle database, two types of input must be provided to SQLLoader : The data itself and the control file. The... Read More

CREATE TABLE - creates a new database table ALTER TABLE - alters (changes) a database table DROP TABLE - deletes a database table CREATE INDEX - creates an index (search key) DROP INDEX - deletes an index Read More

TRUNCATE commits after deleting entire table i.e., cannot be rolled back. Database triggers do not fire on TRUNCATE DELETE allows the filtered deletion. Deleted records can be rolled back or committed. Database triggers fire on DELETE. Read More

The ANALYZE command can also be used to generate chained row information into the DBA_TABLES view. Actual chained-row rowids can be listed in a separate table if desired. Read More

SQL was originally designed to be a declarative and data manipulation language. Additions to the language occurred because of addition of new features from vendors such as constructs, data types, extensions and control flow statements. Important... Read More

It is an interactive and programming language which is used to modify, manage, and to pass queries. It is an ANSI and ISO compliant language. SQL entirely depends upon its command language for modifications, changes, updating, etc to the database.... Read More

SQL is known as structured query language. It is especially designed to retrieve and store information of data in relational database management systems. It creates, modifies and makes the data base object user access the control system. It is... Read More

To reduce the network traffic and for application sharing and implementing business rules. Read More

Preemptive refers to the fact that each task is alloted fixed time slots and at the end of that time slot the next task is started. Read More

In depth explanation of this interface is present in ISO/IEC 9075-3:2003. This extension defines components which can be used to execute SQL statements written in other programming languages. This extension is defined in such a way that the... Read More

This extension is very useful if you are planning to use SQL in Java. This extension defines the syntax and procedure to follow for SQL embedded in Java. It also makes sure of the syntax and procedures to follow which ensures the portability of... Read More

Database triggers can be used to enforce business rules, to maintain derived values and perform value-based auditing. Read More

This information and definition schema helps the user by giving necessary information about the tools and functions of SQL. It describes several tools and extensions some of them are object identifier, security, features provided by DBMS,... Read More

A transaction is a set of operations that begin when the first DML is issued and end when a commit or rollback is issued. BEGIN COMMIT/ROLLBACK are the boundries of a transaction. Read More

SQL is divided into several sub divisions such as ? Statements ? Queries ? Expressions ? Predicates ? Clauses ? White space ? Statement terminator Read More

Statements have a prolonged effect on the functioning and behavior of the data, care should be taken before defining a statement to the data. It may control transactions, query, sessions, connections and program flow Predicates specified conditions... Read More

Orderby clause identifies the columns which are used to sort the data and the order in which they should be sorted out. SQL query needs to specify orderby clause of they want the data to be returned in a defined manner of rows and columns. Read More

Where clause has a comparison predicate which restricts the number of rows as per the user generated query. This clause should be applied before the GROUP BY clause. This clause functions with the help of comparison predicate, when a comparison... Read More

Because it is easier to define an integrity constraint than a database trigger. Read More

Data retrieval syntax is often used in combination with data projection. This mechanism is used when there is a need for calculated result. This is used when there is a special need for calculated data and not the verbatim data, which is different... Read More

MERGE is used when you need to combine more than one table for a user generated query. This field can be aptly said as a combination of INSERT and UPDATE elements. This field is also given a different name (upsert) in some versions of SQL Read More

Because an integrity constraint is automatically checked while data is inserted into a table. A stored has to be specifically invoked. Read More

Data definition Language is used to define new tables and elements associated with it. Some of the basic data definition language elements are CREATE, TRUNCATE, ALTER, RENAME, etc. These are used to control the non standard features of the database. Read More

These are some of the criticisms of SQL<br>1) Implementation features vary from vendor to vendor. There are many features such as data, time, comparison sensitivity, etc<br>2) All possible combinations can be implemented in the language... Read More

Flexibility of the system, scalability, cost saving, centralised control and implementation of business rules, increase of developers productivity, portability, improved network and resource utilization. Read More

There are several reasons for the lack of portability some of them are 1) Main reason is the DATE and TIME variation between databases. 2) Database is free to have its own style of implementation. 3) Ambiguity and less defined semantics of... Read More

Heterogeneity of the system results in reduced reliability. May not be suitable for all applications. Managing and tuning networks becomes difficult. Read More

1. Manage resources optimally across multiple clients. 2. Controlling database access and security. 3. Protecting the database and recovering it from crashes. 4. Enforcing integrity rules globally. Read More

A file server just transfers all the data requested by all its client and the client processes the data while a database server runs the query and sends only the query output. Read More

1. An ODBC manager/administrator and 2. ODBC driver. Read More

The ODBC Manager manages all the data sources that exists in the system. Read More

The ODBC Driver allows the developer to talk to the back end database. Read More

The name of the DBMS, the location of the source and the database dependent information. Read More

create proc sp_UpdAccounts @crAccId varchar(5), @drAccId varchar(5), @amount decimal as update AccountDetails set amount = amount - @amount where accountId = @drAccId update AccountDetails set amount = amount + @amount where accountId = ... Read More

Sys.Objects table is contains all the tables details so we can retrieve from Sys.objects Select * from sys.objects where type='u' U means Table & S means Stored Procedure Read More

select ename, sal from (select ename, sal, rank() over(order by sal) sal_rank from emp) where sal_rank<=10; or select ename,sal from(select ename,sal,rank() over(order by sal desc)sal_rank from emp where sal_rank<=10; Read More

When we use WHERE clause with the GROUP BY ALL clause,it displays all groups including those excluded from the WHERE clause whereas the HAVING clause,if used with the GROUP BY ALL clause overrides the functionality of the ALL Clause and displays only... Read More

using BLOB object. we can store the images n access them. Read More