the best sql interview questions answers 2018 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SQL Interview Questions & Answers 2018 | Mindmajix PowerPoint Presentation
Download Presentation
SQL Interview Questions & Answers 2018 | Mindmajix

Loading in 2 Seconds...

play fullscreen
1 / 30

SQL Interview Questions & Answers 2018 | Mindmajix - PowerPoint PPT Presentation


  • 3 Views
  • Uploaded on

If you\'re looking for SQL Interview Questions for Experienced or Freshers, you are at right place. There are lot of opportunities from many reputed companies in the world. SQL database market continue even stronger by 2020 will be 70%. According to a research SQL by Microsoft grew at 10.32%, while Oracle grew 3.5%. In the upcoming era, competition will be more heated than is has been for years. So, You still have opportunity to move ahead in your career in SQL certification guide. Mindmajix offers Advanced SQL Interview Questions 2018 that helps you in cracking your interview & acquire dream career as SQL Developer.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SQL Interview Questions & Answers 2018 | Mindmajix' - ConsuellaHunter


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
the best sql interview questions answers 2018

The Best SQL Interview Questions & Answers 2018

Q. What is SQL?

SQL (​Structured Query Language​), It is also pronounced as “SEQUEL” and it an Non-procedural

Language which is used to operate all relational database. And it is used to communicate with the

Database. This is a standard language used to perform tasks such as retrieval, updation, insertion

and deletion of data from a database.

Features of SQL:

1. Portability

2. Client server architecture,

3. Dynamic data definition,

4. Multiple views of data,

5. Complete database language,

6. Interactive,

7. High level,

8. Structure and SQL standards.

Q. Difference between SQl & MYSQL.

SQL

SQL

Language

Allow for accessing and manipulating

db's

Basically works as the prompter to a

DBMS

SQL codes & commands are used in

various DBMS and RDBMS systems

such as MySQL.

MYSQL

MySQL is a RDMS (Relational Database Management

System)

MySQL is a database management system, like SQL

Server, Oracle, Postgres, Informix etc

It Facilitates multi-user access to a huge number of DBs

stands

for

Structured

Query

mostly

MySQL has SQL at its core, and requires future upgrades

Visit For More Info​​:: ​https://mindmajix.com

q what is sql server

Q. What is SQL Server?

SQL Server is ​Microsoft's relational database management system (RDBMS). End user cannot

interact directly with database server. If we want to interact with ​SQL database server then we have

to interact with SQL.  

1

Q.SQL Servers Versions:

SQL Server

SQL Server 2017

SQL Server 2016

SQL Server 2012

SQL Server 2008 R2

SQL Server 2008

SQL Server 2005

SQL Server 2000

SQL Server 7.0

Code Name

vNext

Helsinki

Denali

Kilimanjaro

Katmai

Yukon

Shiloh

Sphinx

Q. What are the different types of SQL’s statements?

1​​. ​DQL ​​- Data Query Language ( or) Data Retrival Language

SELECT ​Statement

2​​. ​DML ​​– Data Manipulation Language

DML is used for manipulation of the data itself.

INSERT ​Statement

UPDATE ​Statement

DELETE ​Statement

3​​. ​DDL ​​– Data Definition Language

DDL is used to define the structure that holds the data.

CREATE ​Statement

ALTER ​Statement

DROP ​Statement

RENAME ​Statement

1

Visit For More Info​​:: ​https://mindmajix.com

slide3

TRUNCATE​ Statement

4. DCL​​ – Data Control Language

DCL is used to control the visibility of data.

GRANT ​Statement

REVOKE ​Statement

5. TCL​​ - Transaction Control Language

COMMIT ​Statement

ROLLBACK ​Statement

SAVEPOINT ​Statement

Q. What are various DDL commands in SQL? Give brief description of their purposes.

DDL Commands are used to define structure of the table

1. CREATE

It is used to create database objects like tables, views, synonyms, indexes.

Creating Table​​:

Syntax​​-

Create

table

table_name(columname1

datatype(size),

columname2

datatype(size),....);

2. ALTER

It is used to change existing table structure.

Alter​​:: a) add

b) modify

c)drop

Visit For More Info​​:: ​https://mindmajix.com

a add

a) Add​​:

It is used to add columns into existing table

Syntax​​:

Alter

table

table_name

add(columnname1

datatype(size),

columname2

datatype(size),....);

b) Modify:

It is used to change column Datatype or datatype size only.

Syntax​​:

​Alter

table

table_name

modify(columnname1

datatype(size),

columnname2

datatype(size),....);

c) Drop:

It is used to drop columns from the table.

Method1:

If we want to drop single column at a time without using parentheses then we are using following

syntax.

Syntax​​:

alter table table_namedrop column col_name1; -- drop ONE column

Method2​​:

If we want to drop single or multiple columns at a time with using paranthesis then we are using

following syntax.

Syntax​​:

alter table table_name drop(column_name_list);

Note​​:

Visit For More Info​​:: ​https://mindmajix.com

in all databases we can t drop all columns

In all databases we can’t drop all columns in the table.

3. DROP

It is used to remove database objects from database.

Syntax​​:

Drop object object_name;

(or)

Drop table table_name;

(or)

Drop view view_name;

4. RENAME

It is used to renaming a table.

Syntax​​:

Rename old table_name to new table_name;

Renaming a column:

Syntax​​:

Alter table table_name rename column old column_name to new column_name;

5. TRUNCATE

Oracle 7.0 introduced truncate table command it is used to delete all rows permanently from the

table.

Syntax​​:

truncate table table_name;

Visit For More Info​​:: ​https://mindmajix.com

q what are various dml commands in sql give brief

Q. What are various DML commands in SQL? Give brief description of their purposes.

DML Commands are used to manipulate data within a table.

There are:: INSERT, UPDATE, DELETE

1​​. ​INSERT ​​− It is used to insert data into in the table

Method1​​:

​Syntax​​:

​Insert into table_name values(values1, value2, value3,……);

Method2​​:- Using Substitutional operator (&)

Synatx​​:

​Insert into table_name values(& columnname1, columnname2,.....);

Method3​​:- Skipping columns

Syntax​​:

​Insert into table_name(col1, col2,...) values(val1, val2, val3,...);

2. UPDATE​​ - It is used to change data in a table.

Syntax​​:

​Update table_name set columnname=new value where columnname=old value;

Note​​: In all databases we can also use update statement for inserting data into particular shell.

3. DELETE​​ - It is used to delete rows or particular rows from a table.

Syntax​​:

​Delete from table_name;

(or)

​Delete from tablename where condition;

Visit For More Info​​:: ​https://mindmajix.com

q difference between delete truncate

Q. Difference Between Delete & Truncate?

DELETE

It is DML Command

It is used to delete all the records row by row

TRUNCATE

It is DDL Command

It is used to delete all the records at a time

By using delete command we can delete specific

record

By using truncate we cannot delete specific

record

Where

command

Delete will work slow compare with truncate

condition

we

can

use

with

delete

Where condition will not work with truncate

Truncate will work fast compare with delete

Delete will not reset auto generate id. Once when

we delete all the records from the table.

Truncate will reset auto generate id from

starting number.

Q. About The SQL Buffer?

All Commands of SQL are Typed at the SQL prompt.

Only One SQL Statements is Managed in The SQL Buffer.

The Current SQL Statement Replaces the Previous SQL Statement in the Buffer.

The SQL Statement Can be Divided Into Different Lines Within The SQL Buffer.

Only One Line i.e., The Current Line Can be Active at a Time in the SQL Buffer.

At SQL Prompt, Editing is Possible Only in The Current SQL Buffer Line.

Every Statement of SQL should be terminated Using Semi Colon ”;”

One SQL Statement can Contain Only One Semo Colon.

To Run the Previous OR Current SQL Statement in the Buffer Type “/” at SQL Prompt.

To Open The SQL Editor Type “ED” at SQL Prompt.

Q. What are Important SQL Functions?

LOWER Function​​:(Column/Expression):

1. It Converts Alpha Character Values to Lower Case.

2. The Return Value Has The Same Data Type as Argument CHAR Type (CHAR or

VARCHAR2)

UPPER Function:

1. It Converts the Alpha Character Values to Upper Case.

2. The Return Value Has The Same Data Type as Argument CHAR.

Visit For More Info​​:: ​https://mindmajix.com

initcap function

INITCAP Function:

1. It Converts Alpha Character Values into Upper Case For The First Letter of Each Word,

keeping all Other Letter in Lower Case.

2. Words are Delimited by White Space or Characters That are Not Alphanumeric

LPAD Function:

1. Pads The Character Value Right Justified to a Total Width of ‘n’ Character Positions.

2. The Default Padding Character in Space.

RPAD Function:

1. Pads the Character Value Left Justified to a Total Width of ‘n’ Character ositions.

2. The Default Padding Character is Space.

LTRIM Function:

1. It Enables to TRIM Heading Character From a Character String.

2. All The Leftmost Character That Appear in The SET are Removed.

RTRIM Function:

1. It Enables the Trimming of Trailing Character From a Character STRING.

2. All the Right Most Characters That Appear in The Set are Removed.

TRIM Function:

1. It Enables to TRIM Heading or Trailing Character or Both From a Character String.

2. If LEADING is Specified Concentrates On Leading Characters.

3. If TRAILING is Specified Concentrates on Trailing Characters.

4. If BOTH OR None is Specified Concentrates Both on LEADING and TRAILING.

5. Return the VARCHAR2 Type.

Visit For More Info​​:: ​https://mindmajix.com

advanced sql interview questions answers

Advanced SQL Interview Questions & Answers

Q. How to Open SQL Server?

Goto -> Start -> All Programms -> Microsoft ​SQL Server 2008 R2 -> SQL Server management

Studio.

Q. What is SQL Injections? And How to Prevent SQL Injection Attacks?

It is a mechanism of getting secure data from database.

SQL Injection Attacks::

By providing proper validations for input fields.

By using parameterised queries.

By using stored procedures

By using frequent code reviews

We must not display database error messages in frontend

SQL injection is a code injection technique, used to attack data-driven applications.

Q. Difference Between Scalar Valued Functions & Table Valued Functions?

Scalar Valued Functions

It will process on single row ata time & return

only one value of any database

Table Valued Functions

It will process on multiple rows at a time & return

multiple rows (or) single row from table

The return type of scalar valued function is

datatype

Scalar valued function will have as begin

block end

The return type of table valued function is table

Table valued function will not have as begin end

Syntax to call Scalar Valued Functions is::

Syntax to call Table Valued Functions is::

SELECT dbo. funname(values);

SELECT * FROM dbo.funname(values);

Visit For More Info​​:: ​https://mindmajix.com

q difference between stored procedure functions

Q. Difference Between Stored Procedure & Functions?

Stored Procedure

It is a set of pre-compiled SQL Statements which

will gets executed when we call it

Compile only one time

Stored Procedure will have execution plan

Support DML Commands

Support TCL Commands

It is may or may not have input parameter

Functions

It will take input from user and return only one

value of any data type

Compile every time

Function will not have execution plan

Not supported DML Commands

Not supported TCL Commands

Function

must

have

parameter

Doesn’t have output parameters

at

least

one

input

It is accept both input and output parameters

We call call stored procedure in another stored

procedure

It is support Exception Handling

We can call function in stored Procedure

We can call function in another function

It is doesn’t support Exception Handling

We can’t call stored procedure in function

Q. Write a Query to view the indexes that are applied on the table?

​stored procedure_helpindex table_name

Q. Difference Between Long & Lob Datatypes?

Long

It stores upto 2GB Data

A table can contain only one long

column

Subquery

cannot

datatype column

Lob

It stores upto 4 GB Data

A table can contain more than Lob

column

Subquery can select Lob Column

select

a

Long

Q. Difference Between (Null Value Function) nvl() & Coalesce()

Nvl is an oracle function whereas Coalesce is an ANSI Function and also coalesce

performance is very high as compare to NVL Function.

NVL Function internally uses implicit conversions i.e NVL Function returns a value if the

exp1, exp2 is not belong to same datatype also if exp2 automatically converted into exp1

where as in coalesce function exp1, exp2 must belongs to same datatype.

Examples1​​::

SELECT nvl(‘a’, sysdate) FROM dual;

Visit For More Info​​:: ​https://mindmajix.com

output

Output​​::

a

Examples2::

SELECT Coalesce(‘a’, sysdate) FROM dual;

Error​​: inconsistent datatypes: expected CHAR got DATE

Q. Difference Between Views & Materialized Views?

Views

View does not store data

Security purpose

When we ar4e dropping base table

then view can’t be accessible

Materialized Views

Materialized view stores data

Improved performance purpose

When we are dropping base table also

materialized view can be accessible

Through the view we can perform

DML Operation

We can’t perform DML operation

​Q. What is Tuple?

Tuples are the members of a relation. An entity type having attributes can be represented by set of

these attributes called tuple.

Q. What is Query & Query Language?

A query is a statement requesting the retrieval of information.

The portion of dimly that involves information retrieval is called a query language.

Q. What are the different aggregate functions in SQL?

AVG(), MIN(), MAX(), SUM(), COUNT()

Q.What is data independence?

A database system keeps data separate from software data structure.

Q. What is data integrity?

Visit For More Info​​:: ​https://mindmajix.com

data must satisfy the integrity constraints

Data must satisfy the integrity constraints of the system for data Quality.

Q. What is Dead locking?

It is the situation where two transactions are waiting for other to release a lock on an item.

Q. What is decryption?

Taking encoded text and converting it into text that you are able to read.

Q. What is two phase locking?

It is a most common mechanism that is used control currency in two phases for achieving the

serializability. The two phases are growing and shrinking.

A transaction acquires locks on data items it will need to complete the transaction. This is called

growing process. A transaction may obtain lock but may not release any lock.

One lock is released no other lock may be acquired this is called shrinking process. A transaction

may release locks but may not obtain any new locks.

Q. What is projection?

The Projection of a relation is defined as projection of all its tuples over a set of attributes. It yields

vertical subset of the relation. The projection operation is used to view the number of attributes in the

resultant relation or to reorder attributes.

Q. What is Encryption?

Encryption is the coding or scrambling of data so that humans can not read them directly.

Q. What is cardinality?

The number of instances of each entity involved in an instance of a relation of a relationship describe

how often an entity can participate in relation ship. (1:1, 1:many, many:many).

Q. What is Transaction Control?

Oracle Server Ensures Data Consistency Based Upon Transaction.

Visit For More Info​​:: ​https://mindmajix.com

transactions consist of dml statements that make

Transactions Consist of DML Statements That Make Up One Consistent Change To The Data,

Q. What are the Transaction Start & End Cases?

1. A COMMIT OR ROLLBACK is Issued

2. A DDL Statement Issued.

3. A DCL Statement Issued.

The Usr Exists The SQL * Plus

Failure of Machine OR System Crashes.

A DDL Statement OR A DCL Statement is Automatically Committed And Hence Implicitly

Ends A Transaction.

A Transaction Begins When The First Executable SQL Statement is Encountered.

The Transaction Terminates When The Following Specifications Occur.

Q. GRANT Command?

Syntax​​::

SQL> GRANT< Privilage Name1>, ,

ON

TO ;

GRANT Command is Used When We Want The Database To Be Shared With Other Users.

The Other Users Are GRANTED With Certain Type of RIGHTS.

GRANT Command Can Be issued Not Only on TABLE OBJECT, But Also on VIEWS, SYNONYMS,

INDEXES, SEQUENCES Etc.

SQL> GRANT SELECT

ON EMP

TO ENDUSERS;

SQL> GRANT INSERT, SELECT, DELET

ON EMP

TO OPERATORS;

SQL> GRANT INSERT (Empno, Ename, Job)

ON Emp

To EndUsers;

Q. REVOKE Command?

Visit For More Info​​:: ​https://mindmajix.com

syntax

Syntax​​::

SQL> REVOKE< Privilage Name1>, ,

ON

FROM;

REVOKE Command is Used When We Want One Database To Stop Sharing The Information With

Other Users.

Revoke Privileges is Assigned Not Only On TABLE Object, But Also on VIEWS, SYNONYMS,

INDEXES Etc.

SQL> REVOKE INSERT, DELETE

ON EMP

FROM Operators;

Q. Connecting to Oracle OR SQL * Plus?

Double Click the SQL*Plus ShortCut on the Desktop.

Start -> Run -> Type SQLPlus OR SQLPlusW in Open Box and Click OK.

Start -> Programs -> Oracle -> Application Development -> SQL*Plus

In the Login Box OR Login Prompt Type the User Name and Password as Supplied by the

Administrator.

The Host String is Optional and is provided by the Administrator.

Q. About PL/SQL Tables?

Objects of Type “TABLE” Are Called ​PL/SQL​ Tables.

They Are Modeled As Database Tables, But Are Not Same.

PL/SQL TABLES Use A “PRIMARY KEY” To Give Array Like Access To Rows.

PL/SQL Tables Are Very Dynamic in Operation, Giving The Simulation To Pointers in ‘C’

Language.

They Help in Integrating The Cursors For Dynamic Management of Records At Run Time.

They Make Runtime Management of Result Sets Very Convenient.

Visit For More Info​​:: ​https://mindmajix.com

q difference between sql and pl sql

Q. ​​Difference Between SQL and PL/SQL?

SQL

It’s complete name is structured query

language

PL/SQL

It’s complete name is procedural

Language

/

Structured

Language

It has the complete

branching or looping

In PL/SQL, a complete block of

statements can be sent to Oracle

engine at a time, reducing traffic

In

PL/SQL,

the

statements

can

variables and can be used further as

per the requirement

It

is

fully

support

language

variables and can be used further as

per the requirement

Query

It doesn’t have the any facility of

branching or looping

In SQL, only one statement can be

sent to Oracle Engine. It increase the

execution time

In SQL, the use of variables is not

possible

facility of

results

be

of

the

in

stored

It

procedural language

In SQL, there is no facility of error

management.

In

condition, It is the Oracle Engine that

tracks it.

doesn’t

have

the

capacity

for

procedural

case

of

In

statements

PL/SQL,

the

can

results

be

of

the

in

error

stored

Q. What is the Difference Between SQL & MYSQL?

SQL is more natural than MYSQL. MySQL is a computer application. whose DBMS allows multiple

users. It enables access to several database application and management system. SQL is more

natural and standard language that is used with different applications alike. But, however no

organization actually employs this standard language, rather every software firm follows its own kind

of SQL version.

Q. What is the Temporary Tables?

Syntax​​: CREATE TABLE #TempTab()

A Temporary Table or Temp-Table is created on disk in the tempDB system database. The name of

this Temp-Table is suffixed with a session-specific ID so that it can be differentiated with other

similar named tables created in other sessions. The name is limited 116 chars.

Visit For More Info​​:: ​https://mindmajix.com

example

Example:

Here is an example showing you the usage of a temporary table.

mysql> CREATE TEMPORARY TABLE SALESSUMMARY (

-> product_name VARCHAR(50) NOT NULL

-> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00

-> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00

-> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0

);

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SALESSUMMARY

-> (product_name, total_sales, avg_unit_price, total_units_sold)

-> VALUES

-> ('cucumber', 100.25, 90, 2);

mysql> SELECT * FROM SALESSUMMARY;

+--------------+-------------+----------------+------------------+

| product_name | total_sales | avg_unit_price | total_units_sold |

+--------------+-------------+----------------+------------------+

| cucumber | 100.25 | 90.00 | 2 |

+--------------+-------------+----------------+------------------+

1 row in set (0.00 sec)

Visit For More Info​​:: ​https://mindmajix.com

q what are the types of sql operators

Q. What are the types of SQL operators?

SQL Arithmetic Operators

Operator

+

-

*

/

%

Description

Add

Subtract

Multiply

Divide

Modulo

SQL Bitwise Operators

Operator

&

|

^

Description

Bitwise AND

Bitwise OR

Bitwise exclusive OR

SQL Compound Operators

Operator

+=

-=

*=

/=

%=

&=

^-=

|*=

Description

Add equals

Subtract equals

Multiply equals

Divide equals

Modulo equals

Bitwise AND equals

Bitwise exclusive equals

Bitwise OR equals

SQL Comparison Operators

Operator

=

>

<

>=

<=

<>

Description

Equal to

Greater than

Less than

Greater than or equal to

Less than or equal to

Not equal to

Visit For More Info​​:: ​https://mindmajix.com

sql logical operators

SQL Logical Operators

Operator

ALL

AND

ANY

Description

TRUE if all of the subquery values meet the condition

TRUE if all the conditions separated by AND is TRUE

TRUE

if

any

of

the

subquery

condition

BETWEENTRUE

if

the

operand

comparisons

EXISTS

TRUE if the subquery returns one or more records

IN

TRUE if the operand is equal to one of a list of

expressions

LIKE

TRUE if the operand matches a pattern

NOT

Displays a record if the condition(s) is NOT TRUE

OR

TRUE if any of the conditions separated by OR is TRUE

SOME

TRUE

if

any

of

the

condition

values

meet

the

is

within

the

range

of

subquery

values

meet

the

Q. What is a CURSOR?

CURSOR is a Handle, OR Pointer To The CONTEXT AREA

Q. What is The CURSOR Usage?

Using a CURSOR, The PL/SQL program can control the CONTEXT AREA, As the SQL Statement is

being processed.

Q. What are the CURSOR Features?

>>CURSOR Allows to FETCH and process Rows returned by a SELECT statement, One Row at a

time.

>>A CURSOR is named, such that it can be referenced by the PL/SQL programmer dynamically at

run time.

Visit For More Info​​:: ​https://mindmajix.com

q what are the cursor types

Q. What are the Cursor Types?

CURSORS are broadly recognized as 2 types

1. Implicit Cursors

2. Explicit Cursors

Implicit Cursors:

It is a cursor that is automatically declared by oracle every time an SQL statement is

executed.

The programmer cannot control OR process the information in an Implicit Cursor.

Explicit Cursors:

It is a Cursor that is defined by the programmer within the program for any Query that returns

more than one row of data.

This Cursor is declared within the PL/SQL block, and allows sequential process of ach row of

the returned data from database.

Q. What are the Different Types of Constraints?

1. Null Constraint

2. Not Null Constarint

3. Primary Key Constraint

4. Unique Key Constraint

5. Foreign Key Constraint

6. Composite Primary Key Constraint

7. Default Constraint

8. Check Constraint

Visit For More Info​​:: ​https://mindmajix.com

q explain about different types of constraints

Q. Explain About Different Types of Constraints?

1. Null Constraint - ​​It allows Null Values

Syntax​​: ​create table table_name(columnname datatype, columnname datatype null);

2. NOT NULL Constraint - ​​A NOT NULL Constraint Prohibits a Column From Containing NULL

Values.

NOT NULL Should Be DEfined Ony At COLUMN Level.

The Default Constraint if Not Specified is NULL Constraint.

To Satisfy the Rule, Every Row in The Table Must Contain a Value For The Column.

Syntax​​:

CREATE Table Table_Name

(

Column_Name1(Width) NOT NULL,

Column_Name2(Width)

CONSTRAINT ConsName NOT NULL,

Column_NameN(Width)

);

3.UNIQUE Constraint - ​​The UNIQUE Constraint Designates a Column A s a UNIQUE Key.

A Composite UNIQUE Key Designates a Combination of Column As The UNIQUE Key.

A Composite UNIQUE Key is Always Declared At The Table Level.

To Satisfy a UNIQUE Constraint, No Two Rows in Tha Table Can Have The Same Value For

The UNIQUE Key.

UNIQUE Key Made Up of a Single Column Can Constraint NULL Values

Oracle Creates An Index Implicitly on The UNIQUE Key Cokumn.

Synatx​​:

Create Table

(

Column_Name1(Width) UNIQUE,

Column_Name2(Width)

CONSTRAINT ConsName UNIQUE,

Column_NameN(Width)

);

Visit For More Info​​:: ​https://mindmajix.com

4 primary key constraint a primary key constraint

4. PRIMARY KEY Constraint - ​​A PRIMARY KEY Constraint Designates a Column A sThe

PRIMARY KEY of a TABLE or VIEW

A COMPOSITE PRIMARY KEY Designates a Combination of Columns As The PRIMARY

KEY.

When The Constraint is Declared At Column Level Only PRIMARY KEY Keyword is Enough.

A Composite PRIMARY KEY is Always Defined At Table Level Only.

A PRIMARY KEY Constraint Combines a NOT NULL and UNIQUE Constraint in One

Declaration.

Synatx​​:

CREATE Table< Table_Name>

(

Column_Name1(Width)

CONSTRAINT ColNamePK PRIMARY KEY,

Column_Name2(Width),

Column_NameN(Width)

);

5. FOREIGN KEY Constraint -​​It is Also Called As REFERENTIAL INTEGRITY CONSTRAINT. It

Designates a Column as FOREIGN KEY And Establishes a RELATION Between The FOREIGN

KEY And a Specified PRIMARY OR UNIQUE KEY. A COMPOSITE FOREIGN KEY Designates a

Combination of Column As The FOREIGN KEY.

The TABLE or View Containing The FOREIGN KEY is Called the Child Object.

The TABLE or View Containing The REFERENCED KEY is Called the Parent Object.

The FOREIGN KEY And The REFERENCE KEY Can Be in The Same TABLE or VIEW.

The Corresponding Column or Columns of the FOREIGN KEY And The REFERENCE KEY

Must Match in ORDER and DATA TYPE.

A FOREIGN KEY CONSTRAINT Can Be Defined on a Single Key Column Either Inline or Out of

Line

A COMPOSITE FOREIGN KEY on Attributes Should Be Declared at Table LEVEL or Out of Line

Style. We Can Designate The Same Column or Combination of Columns as Both a FOREIGN KEY

and a PRIMARY or UNIQUE KEY. A COMPOSITE FOREIGN KEY CONSTRAINT, Must Refer To a

COMPOSITE UNIQUE KEY or a COMPOSITE PRIMARY KEY in the PARENT TABLE or VIEW.

Visit For More Info​​:: ​https://mindmajix.com

syntax 1

Syntax​​:

Create

table

table_name(columnname

datatype

foreign

key

references

primary

key

table_name(primarykey column name);

6. COMPOSITE PRIMARY KEY Constraint - ​​Applying PRIMARY KEY Constraint for the

combination of columns is called as COMPOSITE PRIMARY KEY Constraints.

It will not allow duplicate values.

It will not allow null values

We cannot apply more than one COMPOSITE PRIMARY KEY Constraints on a single table.

Syntax​​:

Create table table_name(colname1 datatype, colname2 datatype PRIMARY KEY(col1,

col2));

7. CHECK Constraint -​​Check constraints are used to ensure the validity of data in a database and

to provide data integrity.

Syntax​​:

Create table table_name(columnname datatype check(condition));

8. DEFAULT Constraint:

It is used to insert default value instead of null values.

Syntax​​:

Create table table_name(columnname datatype, default value);

Q. %Found

>>This attribute returns boolean value either true or false.

>>This attribute returns true when fetch statement returns atleast one records.

Visit For More Info​​:: ​https://mindmajix.com

syntax 2

Syntax​​:

cursorname%found

SQL>declare

Cursor c1 is select * from emp

Where ename =’&ename’;

i emp% rowtype;

begin

open c1;

fetch c1 into i;

If c1%found then

dbms_outpit.put_line(your employee exists’||’ ‘||i.ename|| ‘ ‘||i.sal);

else if c1%not found then

dbms_output.put_line(‘your employee does not exists’);

end if;

close c1;

end;

/

Output​​: enter value for ename:murali

Employee doe snot exists

Output​​: enter value for ename:KING

Employee exists KING 7400

Q. Explain Eliminating Explicit Cursor Life Cycle (or) Cursor FOR Loops?

Visit For More Info​​:: ​https://mindmajix.com

using cursor for loop we are eliminating explicit

Using cursor for loop we are eliminating explicit cursor life cycle i.e whenever we are using cursor for

loop no need to use open, fetch, close statement explicitly i.e when we are using cursor for loop

oracle server only internally automatically open the cursor, and then fetch data from the cursor and

close the cursor.

Syntax​​:

For indexvarname in cursorname

Loop

stmts;

end loop;

Note​​:

In cursor for loop index variable internally behaves like a record type variable. (%row type)

Q. What are the Cursor Attributes?

Attribute

Name

%found

Return

Value

Condition

True

If fetch statement return at least one row

-------------

-------------------------------------------------

False

If fetch statement doesn't returns any row

%notfound

True

If fetch statement doesn't returns any row

------------

--------------------------------------------------

False

If fetch statement return at least one row

Visit For More Info​​:: ​https://mindmajix.com

isopen

%isopen

True

If cursor is already opened

----------

--------------------------------------------------

False

If cursor is not opened

%rowcount

If counts number of records number fetches from the

cursor

Number

Q. What is Autonomous Transaction?

Autonomous transactions are independent transaction used in anonymous blocks, procedures,

functions, triggers.

Generally we are defining autonomous transaction is child procedure.

Whenever we are calling autonomous procedure in main transaction and also main transaction TCL

commands never affected on autonomous TCL commands procedure, because these are

independence procedure.

If we want to procedure autonomous then we are using autonomous transaction pragma, commit i.e

in declare section of the procedure we are defining autonomous transaction pragma and also we

must use commit in procedure coding.

Q. What is Out Mode?

We can also use out mode parameter i function, but these functions are not allowed to execute by

using select statement. If we want to return more no.of values from a function then only we are

allowed to use out parameter. Here also out parameter behaves like a uninitialized variables.

Q. What is SQL Loader?

SQL Loader is an utility program which is used to transfer data from flat into oracle database. SQL

Loader always executes control file based on the type of flat file we are creating control file and then

submit control file to SQL loader then only SQL loader transfer file into flat file into oracle Data Base

during this file some other files also created.

Visit For More Info​​:: ​https://mindmajix.com

1 logfile 2 badfile 3 discardfile

1. Logfile

2. Badfile

3. Discardfile

Q. SQL%bulk_rowcount

Oracle introduced sql%bulk_rowcount attribute which is used to count affected number of rows

within each group in bulk bind process. (forall statements).

Syntax​​: ​sql%bulk_rowcount(index varname);

Q. Authid current_user

When a procedure have a authid current_user clause then those procedures are allowed to execute

only owner of the procedure.

These procedures are not allowed to executes by another users if any user givings permission also.

Generally whenever we are reading data from table and performs some DML operations then only

data security principles of view developers uses this clause in procedures.

This clause are used in procedures specification only.

Synatx​​:

Create or replace procedure procedurename(formal parameters)

Authid current_user

is/as

-----------

Begin

----------

[exeception]

------------

End[procedurename];

Q. What is Row-Level-Attribute?

In this method a single variables can represent all different data type into single unit. This variable is

also called as record type variable.

Row Level Attribute are represented by using %rowtype.

Visit For More Info​​:: ​https://mindmajix.com

syntax 3

Syntax​​:

variable_name table_name%rowtype;

Q. What are types of Blocks in PL/SQL?

PL/SQL having 2 types of blocks

1. Anonymous Block

2. Nammed Block

Anonymous Block

This block doesnot have a name

These blocks are not stored in oracle

database

Thess blocks are not allowed to call

in client application

Nammed Block

This block having a name

These

blocks

permanently stored in Database

These blocks are allowed to call in client

application

are

automatically

Q. Write a PL/SQL cursor program which is used to display total salary from emp table

without using sum() function by using cursor for loop?

sql​​> declare

cursor c1 is select*from emp

n number(10):=0;

begin

for i in c1

loop

n:=n+i.sal;

end loop;

dbms_output.put_line (‘total salary is: ‘||’ ‘||n);

end;

/

Output​​: total salary is: 42075

Q. What is Normalization?

Normalization is a scientific process which is called to decomposing a table into number of tables.

This process automatically reduces duplicate data and also automatically avoids insertion, updation,

deletion problems.

Visit For More Info​​:: ​https://mindmajix.com

in design phase of sdlc database designers

In design phase of SDLC database designers designs LOGICAL MODEL of the database in this

logical model only database designers uses normalization process by using normal forms.

Q. What is Super Key?

A columns or a combination of columns which uniquely identifying a record in a table is called a

Super Key.

Q. What is Candidate Key?

A minimal super key uniquely identifying a record a table is called candidate key

(or)

A super key which is a subset of another super key then those super keys are not a candidate key.

Q. What is Bad File?

This file extension is .bad

Bad file stores rejected records based on

1. Data type mismatch

2. Business rule violation

Bad file is automatically created as same name as Flat file, we can also create Bad file explicitally by

using bad file clause within control file.

Q. What is Discard File?

This file extension is​ .dsc

Discards file we must specify within control file by using discard file clause.

Discard file also stores rejected record based on when clause condition within control file. This

condition must be satisfied into table table_name clause.

Visit For More Info​​:: ​https://mindmajix.com

q what is autoincrement

Q. What is Autoincrement?

In all databases generating primary key value automatically is called auto increment concept. In

Oracle we are implementing auto increment concept by using row level triggers, sequences. i.e here

we creating sequence in sql and use this sequence in PL/SQL row level trigger.

SQL> create table test (sno number(10), primary key, name varchar2(10));

Q. What is Dynamic SQL?

It is the combination of SQL, PL/SQL i.e SQL statements are executed dynamically with PL/SQL

block using execute immediate clause.

Generally in PL/SQL block we are not allow to use DDL, DCL statements using Dynamic SQL DDL,

DCL statement within PL/SQL block.

Syntax:

begin

execute immediate ‘sql statement’

end;

/

Q. Write a dynamic SQL program to display number of records from emp table?

SQL​​> declare

z number(10);

begin

execute immediate ‘select count * FROM emp’

into z;

dbms_output.put_line(z);

end;

/

Q. Write a dynamic SQL program for passing department number 20 retrieve deptnames, Loc

from dept table?

SQL​​> declare

v_deptno number(10):=20;

v_dname varchar2(10);

v_loc varchar2(10);

begin

Visit For More Info​​:: ​https://mindmajix.com

execute immediate select dname loc from dept

execute immediate ‘select dname, loc FROM dept where deptno=1’ into v_dname, v_loc using

v_deptno;

dbms_output.put_line(v_dname ||’ ‘|| v_loc);

end;

/

List of Related Microsoft Certification Courses:

​SSIS

​SSAS

​SSRS

​SCCM

​Team Foundation Server

​Power BI

​SharePoint

​SQL Server DBA

​BizTalk Server

​BizTalk Server Administrator

Visit For More Info​​:: ​https://mindmajix.com