Top posts
-
SQL server System Stored Procedures
The Transact SQL language allow you to use various system stored procedures like: sp_tables, sp_table_privileges, sp_stored_procedures, sp_cursor, sp_executesql, sp_rename, sp_lock, sp_execute, sp_help. Sp_addextendedproperty Sp_columns Sp_column_privileges...
-
sql server dba Filegroups
AAFiles: There 3 types of file are there. 1. .MDF [(Master Data File, Main Data File, Primary Data File] 2. .LDF 3. .NDF 1. Primary data file (.MDF) --Primary data files The primary data file is the starting point of the database --Also primary data file...
-
Oracle 11g New Features
Oracle 11g New Features Today I will cover some of the new features of Oracle 11g for developers. 1. The new REGEXP_COUNT function returns the number of times the search pattern appears in source string. 2. SIMPLE_INTEGER Datatype The SIMPLE_INTEGER datatype...
-
What is a sub query? What are its various types?
What is a sub query? What are its various types? - Sub Query also termed as Nested Query or Inner Query is used to get data from multiple tables. - A sub query is added in the where clause of the main query. There can be two types of subqueries: a.) Correlated...
-
Varray type parameter in Function
> create or replace type numberlist_t 2 as varray(4) of varchar2(20); 3 / Type created. SQL> SQL> column numlist format a60 SQL> SQL> alter table e add (numlist numberlist_t) SQL> create or replace function ext 2 (p_varray_in numberlist_t) 3 return varchar2...
-
using aggragrate functions for reporting
Advanced Grouping in oracle, more helpful for report generate. SQL> SELECT * FROM EMP; -- GROUP BY CLAUSE SQL> SELECT deptno, SUM(sal) FROM emp WHERE sal <= 4000 GROUP BY deptno; -- ROLLUP SQL> SELECT deptno, SUM(sal) FROM emp WHERE sal <= 4000 GROUP...
-
SQL Query – Order of execution
When you execute a SQL Query, it will be executed in the following sequence. FROM ON JOIN WHERE GROUP BY HAVING SELECT DISTINCT ORDER BY TOP When a query is submitted to the database, it is executed in the following order: FROM clause WHERE clause...
-
PL/SQL - Forward Declarations
1 . What are the Forward Declarations? PL/SQL is rather fussy about its requirement that you declare a variable, cursor, or module before you use it in your code. Otherwise, how can PL/SQL be sure that the way you are using the object is appropriate?...
-
TATA TECHNOLOGY PL/SQL Interview Questions
Tell me about your self? Which version database using in your company? Can you please major differences in oracle 11h,12c ? What does instr () and substr () returns. Write syntax for both? Write a query to delete duplicate rows from a table? Can use dml...
-
sql dba CheckPoint VSLazy Writer
CheckPoint Lazy Writer 1. Flush dirty pages to Disk 1. Flush dirty pages to disk. 2. Flush only Data pages to disk 2. Check for available memory and removed Buffer pool (execution plan/compile plan/ Data pages /Memory objects) 3. Default, Occurs approximately...
-
Maintenance Plan Wizard in SQL Server
Maintenance Plan Wizard in SQL Server ----------------------------------------------------- SQL Server maintenance plan wizard is inbuilt feature which helps in creating automated maintenance jobs for database integrity checks, rebuild and reorganize...
-
Bulk Exceptions in Oracle
Bulk Exceptions Using BULK COLLECT in PL/SQL blocks and procedures can dramatically speed array processing, but it can, if the DBA isn't prepared, 'hide' any errors that occur in the bulk processing list. A 'plain vanilla' EXCEPTION handler may not report...
-
PRAGMA INLINE IN ORACLE
The INLINE pragma specify that a sub program call is, or is not, to be inlined. Inlining replaces a sub program call (to a sub program in the same program unit) with a copy of the called sub program. To reiterate, with sub program inlining, Oracle will...
-
SQL DBA Interview Questions and Answers for Freshers
SQL DBA Interview Questions and Answers for Freshers 1. What are the different components in SQL Server? Database Engine, Analysis Services, Reporting Services, Integration Services, Master Data Services, Data Mining, PowerPivot, Replication etc. 2. Tell...
-
SQL Server Database Migration
SQL Server Database Migration Migrating a database is very critical and time bound process, if anything goes wrong then its very difficult to rollback things and move back to existing environment again. So its very important to prepare a checklist before...
-
CLUSTERING INTERVIEW QUESTIONS.
CLUSTERING INTERVIEW QUESTIONS. 1 )What is Clustering? Clustering is a technology in which one or more computers work together as a single instance by sharing their resources. Clustering is used mainly for redundancy of the services hosted on it. In other...
-
RDBMS
RDBMS RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. The data in RDBMS is stored in database objects called tables....
-
SQL Aggregate Functions
SQL Aggregate Functions SQL aggregate functions return a single value, calculated from values in a column. Useful aggregate functions: AVG() - Returns the average value COUNT() - Returns the number of rows FIRST() - Returns the first value LAST() - Returns...
-
SQL PRIMARY KEY Constraint
SQL PRIMARY KEY Constraint The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain UNIQUE values. A primary key column cannot contain NULL values. Most tables should have a primary key, and each table...
-
SQL CHECK Constraint
SQL CHECK Constraint The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a single column it allows only certain values for this column. If you define a CHECK constraint on a table it...
-
VIEW
VIEW This is an important concept in SQL. A View is nothing but a window of an existing table. In other words it is a logical representation that is created from one or existing base table. After creation of view it can be used just as a base table. That...
-
What is Recovery Model? Benefits of Recovery Models?
What is Recovery Model? Benefits of Recovery Models? “A recovery model is a Database property that control the basic behavior of the backup and restore operations for a DB”. Recovery models are designed to control transaction log maintenance. Explain...
-
SQL UNIQUE Constraint
SQL UNIQUE Constraint The UNIQUE constraint uniquely identifies each record in a database table. The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has...
-
SQL DEFAULT Constraint
SQL DEFAULT Constraint The DEFAULT constraint is used to insert a default value into a column. The default value will be added to all new records, if no other value is specified. SQL DEFAULT Constraint on CREATE TABLE CREATE TABLE Persons ( P_Id int NOT...
-
SQL LEFT JOIN
SQL LEFT JOIN Keyword The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match. SQL LEFT JOIN Syntax SELECT column_name(s) FROM...