6) What is data definition language?

7) What is data manipulation language?

Data manipulation language makes user able to access and
manipulate data. It is used to perform following operations.

Insert data into database

Retrieve data from the database

Update data in the database

Delete data from the database

8) What is data control language?

Data control language allows you to control access to the
database. It includes two commands GRANT and REVOKE.

GRANT:to
grant specific user to perform specific task.

REVOKE:to
cancel previously denied or granted permissions.

9) What are tables and fields in database?

A table is a set of organized data. It has columns and rows.
Columns can be categorized as vertical, and Rows are horizontal.

A table contains specified number of column called fields but can
have any number of rows which is known as record.

10) What is a primary key?

A primary key is a combination of fields which uniquely specify a
row. This is a special kind of unique key. Primary key values cannot be NULL.

11) What is a foreign key?

A foreign key is specified as a key which is related to the
primary key of another table. Relationship needs to be created between two
tables by referencing foreign key with the primary key of another table.

12) What is a unique key?

A Unique key constraint uniquely identifies each record in the
database. This provides uniqueness for the column or set of columns.

13) What are the type of operators available
in SQL?

Arithmetic operators

Logical operators

Comparison operator

14) What is view in SQL?

A view is a virtual table which contains a subset of data within a
table. Views are not virtually present, and it takes less space to store. View
can have data of one or more tables combined, and it is depending on the
relationship.

15) What is an Index in SQL?

Index is used to increase the performance and allow faster
retrieval of records from the table. An index creates an entry for each value
and it will be faster to retrieve data.

16) Which are the different types of indexes
in SQL?

There are three types of Indexes in SQL:

Unique Index

Clustered Index

NonClustered Index

17) What is Unique Index?

Unique Index:

This indexing does not allow the field to have duplicate values if
the column is unique indexed. Unique index can be applied automatically when
primary key is defined.

18) What is Clustered Index in SQl?

Clustered Index:

The clustered index is used to reorder the physical order of the
table and search based on the key values. Each table can have only one
clustered index.

19) What is NonClustered Index in SQL?

NonClustered Index:

NonClustered Index does not alter the physical order of the table
and maintains logical order of data. Each table can have 999 non-clustered
indexes.

20) What is the difference between SQL, MySQL
and SQL Server?

SQL or Structured Query Language is a language which is used to
communicate with a relational database. It provides a way to manipulate and
create databases. On the other hand, MySQL and Microsoft's SQL Server both are
relational database management systems that use SQL as their standard
relational database language.

21) What is the difference between SQL and
PL/SQL?

SQL or Structured Query Language is a language which is used to
communicate with a relational database. It provides a way to manipulate and
create databases. On the other hand, PL/SQL is a dialect of SQL which is used
to enhance the capabilities of SQL. It was developed by Oracle Corporation in
the early 90's. It adds procedural features of programming languages in SQL.

22) Is it possible to sort a column using a
column alias?

Yes. You can use column alias in the ORDER BY clause for sorting.

23) What is the difference between clustered
and non clustered index in SQL?

There are mainly two type of indexes in SQL, Clustered index and
non clustered index. The differences between these two indexes is very
important from SQL performance perspective.

One table can have only one clustered
index but it can have many non clustered index.(approximately 250).

clustered index determines how data is
stored physically in table. Actually clustered index stores data in
cluster, related data is stored together so it makes simple to retrieve
data.

reading from a clustered index is much
faster than reading from non clustered index from the same table.

clustered index sort and store data
rows in the table or view based on their key value, while non cluster have
a structure separate from the data row.

24) What is the SQL query to display current
date?

There is a built in function in SQL called GetDate() which is used
to return current timestamp.

25) Which are the most commonly used SQL
joins?

Most commonly used SQL joins are INNER JOIN and (left/right) OUTER
JOIN.

26) What are the different types of joins in
SQL?

Joins are used to merge two tables or retrieve data from tables.
It depends on the relationship between tables.

Following are the most commonly used joins in SQL:

Inner Join

Right Join

Left Join

Full Join

27) What is Inner Join in SQL?

Inner join:

Inner join returns rows when there is at least one match of rows
between the tables.

28) What is Right Join in SQL?

Right Join:

Right join is used to retrieve rows which are common between the
tables and all rows of Right hand side table. It returns all the rows from the
right hand side table even though there are no matches in the left hand side
table.

29) What is Left Join in SQL?

Left Join:

Left join is used to retrieve rows which are common between the
tables and all rows of Left hand side table. It returns all the rows from Left
hand side table even though there are no matches in the Right hand side table.

30) What is Full Join in SQL?

Full Join:

Full join return rows when there are matching rows in any one of
the tables. This means, it returns all the rows from the left hand side table
and all the rows from the right hand side table.

31) What is "TRIGGER" in SQL?

Trigger allows you to execute a batch of SQL code when an insert,
update or delete command is executed against a specific table.

Actually triggers are special type of stored procedures that are
defined to execute automatically in place or after data modifications.

32) What is self join and what is the
requirement of self join?

Self join is often very useful to convert a hierarchical structure
to a flat structure. It is used to join a table to itself as like if that is
the second table.

33) What are set operators in SQL?

Union,IntersectorMinusoperators are called set operators.

34) What is the difference between BETWEEN
and IN condition operators?

The BETWEEN operator is used to display rows based on a range of
values. The IN condition operator is used to check for values contained in a
specific set of values.

35) What is a constraint? Tell me about its
various levels.

Constraints are representators of a column to enforce data entity
and consistency. There are two levels :

column level constraint

table level constraint

36) Write an SQL query to find names of
employee start with 'A'?

1.SELECT * FROM Employees WHERE EmpName like'A%'

37) Write an SQL query to get third maximum
salary of an employee from a table named employee_table.

1.SELECTTOP 1 salary

2.FROM (

3.SELECTTOP 3 salary

4.FROM employee_table

5.ORDERBY salary DESC ) AS emp

6.ORDERBY salary ASC;

38) What is the difference between DELETE and
TRUNCATE statement in SQL?

The main differences between SQL DELETE and TRUNCATE statements
are given below:

No.

DELETE

TRUNCATE

1)

DELETE is aDML
command.

TRUNCATE is aDDL
command.

2)

Wecan use
WHEREclause in DELETE
command.

Wecannot use
WHEREclause with TRUNCATE

3)

DELETE statement is usedto
delete a rowfrom a table

TRUNCATE statement is usedto
remove all the rowsfrom a
table.

4)

DELETE isslowerthan TRUNCATE statement.

TRUNCATE statement isfasterthan DELETE statement.

5)

Youcan
rollbackdata after using
DELETE statement.

It isnot
possible to rollbackafter
using TRUNCATE statement.

39) What is ACID property in database?

ACID property is used to ensure that the data transactions are
processed reliably in a database system.

A single logical operation of a data is called transaction.

ACID is an acronym for Atomicity, Consistency, Isolation,
Durability.

Atomicity:it
requires that each transaction is all or nothing. It means if one part of the
transaction fails, the entire transaction fails and the database state is left
unchanged.

Consistency:the
consistency property ensure that the data must meet all validation rules. In
simple words you can say that your transaction never leaves your database
without completing its state.

Isolation:this
property ensure that the concurrent property of execution should not be met.
The main goal of providing isolation is concurrency control.

Durability:durability
simply means that once a transaction has been committed, it will remain so,
come what may even power loss, crashes or errors.

40) What is the difference among NULL value,
zero and blank space?

Ans: A NULL value is not
same as zero or a blank space. A NULL value is a value which is 'unavailable,
unassigned, unknown or not applicable'. On the other hand, zero is a number and
blank space is treated as a character.

41) What is the usage of SQL functions?

SQL functions are used for following purpose:

To perform calculations on data.

To modify individual data items.

To manipulate the output.

To format dates and numbers.

To convert data types.

42) Which are the different case manipulation
functions in SQL?

There are three case manipulation functions in SQL:

LOWER

UPPER

INITCAP

43) What is the usage of NVL function?

The NVL function is used to convert NULL value to a actual value.

44) Which function is used to return
remainder in a division operator in SQL?

The MOD function returns the remainder in a division operation.

45) What is the syntax and use of the
COALESCE function?

The syntax of COALESCE function:

1.COALESCE(exp1, exp2, ... expn)

The COALESCE function is used to return the first non-null
expression given in the parameter list.

46) What is the usage of DISTINCT keyword?

The DISTINCT keyword is used to ensure that the fetched value is
only a non-duplicate value.

Wednesday, 3 May 2017

SQL or Structured Query Language is a language; language that
communicates with a relational database thus providing ways of
manipulating and creating databases. MySQL and Microsoft’s SQL Server
both are relational database management systems that use SQL as their
standard relational database language.

What is the difference between SQL and PL/SQL?

PL/SQL is a dialect of SQL that adds procedural features of
programming languages in SQL. It was developed by Oracle Corporation in
the early 90's to enhance the capabilities of SQL.

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

Following are various DDL or Data Definition Language commands in SQL −

CREATE − it creates a new table, a view of a table, or other object in database.

ALTER − it modifies an existing database object, such as a table.

DROP − it deletes an entire table, a view of a table or other object in the database.

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

Following are various DML or Data Manipulation Language commands in SQL −

SELECT − it retrieves certain records from one or more tables.

INSERT − it creates a record.

UPDATE − it modifies records.

DELETE − it deletes records.

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

Following are various DCL or Data Control Language commands in SQL −

GRANT − it gives a privilege to user.

REVOKE − it takes back privileges granted from user.

Can you sort a column using a column alias?

Yes. A column alias could be used in the ORDER BY clause.

Is a NULL value same as zero or a blank space? If not then what is the difference?

A NULL value is not same as zero or a blank space. A NULL value is a
value which is ‘unavailable, unassigned, unknown or not applicable’.
Whereas, zero is a number and blank space is a character.

Say True or False. Give explanation if False.
If a column value taking part in an arithmetic expression is NULL, then the result obtained would be NULLM.

True.

If a table contains duplicate
rows, does a query result display the duplicate values by default? How
can you eliminate duplicate rows from a query result?

A query result displays all rows including the duplicate rows. To
eliminate duplicate rows in the result, the DISTINCT keyword is used in
the SELECT clause.

What is the purpose of the condition operators BETWEEN and IN?

The BETWEEN operator displays rows based on a range of values. The IN
condition operator checks for values contained in a specific set of
values.

How do you search for a value in a database table when you don’t have the exact value to search for?

In such cases, the LIKE condition operator is used to select rows
that match a character pattern. This is also called ‘wildcard’ search.

What is the default ordering of data using the ORDER BY clause? How could it be changed?

The default sorting order is ascending. It can be changed using the DESC keyword, after the column name in the ORDER BY clause.

What are the specific uses of SQL functions?

SQL functions have the following uses −

Performing calculations on data

Modifying individual data items

Manipulating the output

Formatting dates and numbers

Converting data types

What are the case manipulation functions of SQL?

LOWER, UPPER, INITCAP

Which function returns the remainder in a division operation?

The MOD function returns the remainder in a division operation.

What is the purpose of the NVL function?

The NVL function converts a NULL value to an actual value.

What is the difference between the NVL and the NVL2 functions?

The NVL(exp1, exp2) function converts the source expression (or
value) exp1 to the target expression (or value) exp2, if exp1 contains
NULL. The return value has the same data type as that of exp1.
The NVL2(exp1, exp2, exp3) function checks the first expression exp1,
if it is not null then, the second expression exp2 is returned. If the
first expression exp1 is null, then the third expression exp3 is
returned.

What is the use of the NULLIF function?

The NULLIF function compares two expressions. If they are equal, the
function returns null. If they are not equal, the first expression is
returned.

Discuss the syntax and use of the COALESCE function?

The COALESCE function has the expression COALESCE(exp1, exp2, …. expn)
It returns the first non-null expression given in the parameter list.

Which expressions or functions allow you to implement conditional processing in a SQL statement?

There are two ways to implement conditional processing or IF-THEN-ELSE logic in a SQL statement.

Using CASE expression

Using the DECODE function

You want to display a result
query from joining two tables with 20 and 10 rows respectively.
Erroneously you forget to write the WHERE clause. What would be the
result?

The result would be the Cartesian product of two tables with 20 x 10 = 200 rows.

What is the difference between cross joins and natural joins?

The cross join produces the cross product or Cartesian product of two
tables. The natural join is based on all the columns having same name
and data types in both the tables.

What is the purpose of the group functions in SQL? Give some examples of group functions.

Group functions in SQL work on sets of rows and returns one result
per group. Examples of group functions are AVG, COUNT, MAX, MIN, STDDEV,
SUM, VARIANCE.

Say True or False. Give explanation if False.By default the group functions consider only distinct values in the set.

By default, group functions consider all values including the duplicate values.

A subquery is a SELECT statement embedded in a clause of another
SELECT statement. It is used when the inner query, or the subquery
returns a value that is used by the outer query. It is very useful in
selecting some rows in a table with a condition that depends on some
data which is contained in the same table.

Say True or False. Give explanation if False.A single row subquery returns only one row from the outer SELECT statement

False. A single row subquery returns only one row from the inner SELECT statement.

Say True or False. Give explanation if False.A multiple row subquery returns more than one row from the inner SELECT statement.

True.

Say True or False. Give explanation if False.Multiple column subqueries return more than one column from the inner SELECT statement.

True.

What’s wrong in the following query?

SELECT student_code, name
FROM students
WHERE marks =(SELECT MAX(marks)
FROM students
GROUP BY subject_code);

Here a single row operator = is used with a multiple row subquery.

What are the various multiple row comparison operators in SQL?

IN, ANY, ALL.

What is the pupose of DML statements in SQL?

The DML statements are used to add new rows to a table, update or
modify data in existing rows, or remove existing rows from a table.

Which statement is used to add a new row in a database table?

The INSERT INTO statement.

Say True or False. Give explanation if False.While inserting new rows in a table you must list values in the default order of the columns.

True.

How do you insert null values in a column while inserting data?

Null values can be inserted into a table by one of the following ways −

Implicitly by omitting the column from the column list.

Explicitly by specifying the NULL keyword in the VALUES clause.

Say True or False. Give explanation if False.INSERT statement does not allow copying rows from one table to another.

False. INSERT statement allows to add rows to a table copying rows from an existing table.

How do you copy rows from one table to another?

The INSERT statement can be used to add rows to a table by copying
from another table. In this case, a subquery is used in the place of the
VALUES clause.

What happens if you omit the WHERE clause in the UPDATE statement?

All the rows in the table are modified.

Can you modify the rows in a table based on values from another table? Explain.

Yes. Use of subqueries in UPDATE statements allow you to update rows in a table based on values from another table.

Say True or False. Give explanation if False.The DELETE statement is used to delete a table from the database.

False. The DELETE statement is used for removing existing rows from a table.

What happens if you omit the WHERE clause in a delete statement?

All the rows in the table are deleted.

Can you remove rows from a table based on values from another table? Explain.

Yes, subqueries can be used to remove rows from a table based on values from another table.

Say True or False. Give explanation if False.Attempting to delete a record with a value attached to an integrity constraint, returns an error.

True.

Say True or False. Give explanation if False.You can use a subquery in an INSERT statement.

True.

What is the purpose of the MERGE statement in SQL?

The MERGE statement allows conditional update or insertion of data
into a database table. It performs an UPDATE if the rows exists, or an
INSERT if the row does not exist.

Monday, 6 March 2017

Before we access oracle database, we must start
oracle database. Starting up oracle database means starting oracle instance and
associating oracle instance with an oracle database so that oracle instance can
access the database.

The process is very length and complicated.
Several steps are involved in it. But fortunately we do not have to know all
that happens when a database starts. We just need to select an option or two to
startup database.

Generally you do not have to startup database in
case of Oracle Server running on Windows NT/Windows 2000 as oracle server
automatically starts in this case. However, if you ever have to start oracle
database on Windows NT/Windows 2000, follow the steps given below:

1.Start services program using Administrative Tools -> Service in
Windows/2000 or Control Panel -> Service on Windows NT.

2.If service OracleServiceOracle8i has not yet started,
click on it with right button and select start option from popup menu.

The exact name of the
service depends on the name you have given to oracle instance at the time of
installing it.

Note: Starting
and shutting down the database is the job of Database Administrator. As this
books assumes that you are an application developer, it doesn’t get into those
details.

Starting up database in Personal Oracle

Unlike Oracle Server in Personal Oracle, Oracle
Instance doesn’t start on its own. The Oracle Instance must be explicitly
started. The following are the steps to start oracle on Personal Oracle:

1.Select start database
option in Personal Oracle8i for windows menu.

2.When a dialog box is displayed
wait until the message Oracle Instance Started appears.

3.Click on Close button to
close the dialog box.

Starting SQL*PLUS

Sql*plus is a tool that comes along with Oracle.
It is used to issue SQL and SQL*PLUS commands. It provides command line
interface through which we can enter SQL and SQL*PLUS command.

To start SQL*PLUS, take the steps given below:

1.Select start->programs->Oracle
- Oracle8i.

Oracle8i is the name of the instance. It
may be different on your system.

2.Then select Application
Development -> SQL Plus.

3.When Log On dialog box
is displayed, enter username, password and Host string.
Use tab key to move from one field to another.For more information about each of these fields, see next section.

4.Click on OK.

5.If the information supplied is
valid then you enter into Oracle and SQL*PLUS will display SQL> prompt.

Username, Password and Host String

Oracle is a multi-user database. Whoever is
access the database must log on to database.To log on we have to supply username and password.When the given username and password are
recognized by Oracle, it will allow us to access data.A user can access only the data that belongs
to his/her and not the data of others.However, it is possible for a user to grant privileges to others so that
other can access his/her data.

Creation of users and management of overall
security is the responsibility of Database Administrator (DBA).DBA is the person who makes sure that
database is functioning smoothly. He is responsible for operations such as
taking backup of the database, recovering the database in the event of failure,
fine tuning database to get best performance. So, if you want to have a new
account under your name, please consult administrator of your database.

Username & Password

Every user who wants to access oracle database
must have an account in the database. These accounts are created by DBA. Each
account is associated with username and password.

Oracle comes with a set of predefined accounts.
The following are the usernames and passwords of these accounts.

UsernamePassword

systemmanager

syschange_on_install

Scotttiger

Demodemo

Note: when you
enter into oracle using either system or sys then you become DBA.
That means you get special privileges to perform major operations such as
creating users etc.

Host String

Host string is a name that is used to access
oracle server that is running on a different machine from client. This is
required only when you are trying to access oracle server that is not on the
current machine. That means, you never need to use host string for Personal
Oracle as client and oracle always run on the same machine in Personal Oracle.

Host string is required when you are trying to
connect to Oracle Server running on remote machine.Host string is actually called as net
service name. Net service name is a name that is stored in TNSNAMES.ORA
file on the client to provide the following information.

Host

Name of the machine or IP address of the
machine on which oracle server is running.

Instance name

Name of the Oracle Instance running on the
remote machine.

Port Number

Port number of the listener, a program that
takes requests from clients.Port
number is an integer that uniquely identifies the program on the server.

How to enter SQL statements?

SQL*PLUS allow to types of command to entered at
the prompt- SQL and SQL*PLUS.

SQL commands include commands of ANSI/ISO SQL
and extra commands added to ANSI SQL by oracle.

The following are the rules to be followed while
entering SQL commands.

1.An SQL statement may be entered
in multiple lines.

2.It is not possible to break a
word across lines.

3.SQL statement must be
terminated by semicolon (;).

The following is an example of SQL command.What this command does is not important at
this moment.

SQL> select ccode,name

2from courses

3where fee > 5000;

In the above command, we entered the command in
three lines. When you enter semicolon and press enter key then SQL*PLUS will
take it as the end of the command. Also note that you have to press enter key
at the end of each line.

Note: Both SQL
and SQL*PLUS commands are NOT case sensitive.

How to enter SQL*PLUS statements?

SQL*Plus statements are available only in
SQL*PLUS. They are not part of standard SQL. SQL*Plus commands are mainly used
for two purposes – editing SQL commands and formatting result of query.

The following rules are to be followed while
entering these commands.

1.The entire command must be
entered on a single line.

2.No need to terminate command
with semicolon (;).

3.Commands can be abbreviated.
However, the amount of abbreviation is not fixed.Some commands are abbreviated to one letter
some are abbreviated to 2 and so on.

The following example show how to use CLEAR
SCREEN command of SQL*PLUS.

SQL>clear screen

Or it can be abbreviated to

SQL>cl scr

Common Errors

The following are the common errors that you get
while you are trying to log on to Oracle.

Ora-01017: invalid username/password; login
denied

The reason for this error is that you have entered
a username or password that is not valid. Check whether username you are
entering is really existing and password is correctly typed.Sql*plus gives you three chances to type
username and password correctly. If youcannot log on successfully in three chances then Sql*plus will exit.
However, you can restart Sql*plus again.

ORA-01034: ORACLE not available

The reason for this message is that Oracle
Instance is not up and running. You have to first make sure you have started
Oracle Instance. Actually there are afew other problems that occurs when Oracle Instance has not started
successfully. If this is case in Oracle Server, notify administrator. If this
is the case with Personal Oracle, make sure you start database as mentioned in
“starting up database” section.

Summary

In this chapter, we have seen what is Oracle and
what is the difference between Oracle Server and Personal Oracle. We have seen
how to connect to Oracle through SQL*Plus.

In the next chapter, we start creating tables
and understanding elementary statements in SQL.

6) What is data definition language?

7) What is data manipulation language?

Data manipulation language makes user able to access and
manipulate data. It is used to perform following operations.

Insert data into database

Retrieve data from the database

Update data in the database

Delete data from the database

8) What is data control language?

Data control language allows you to control access to the
database. It includes two commands GRANT and REVOKE.

GRANT:to
grant specific user to perform specific task.

REVOKE:to
cancel previously denied or granted permissions.

9) What are tables and fields in database?

A table is a set of organized data. It has columns and rows.
Columns can be categorized as vertical, and Rows are horizontal.

A table contains specified number of column called fields but can
have any number of rows which is known as record.

10) What is a primary key?

A primary key is a combination of fields which uniquely specify a
row. This is a special kind of unique key. Primary key values cannot be NULL.

11) What is a foreign key?

A foreign key is specified as a key which is related to the
primary key of another table. Relationship needs to be created between two
tables by referencing foreign key with the primary key of another table.

12) What is a unique key?

A Unique key constraint uniquely identifies each record in the
database. This provides uniqueness for the column or set of columns.

13) What are the type of operators available
in SQL?

Arithmetic operators

Logical operators

Comparison operator

14) What is view in SQL?

A view is a virtual table which contains a subset of data within a
table. Views are not virtually present, and it takes less space to store. View
can have data of one or more tables combined, and it is depending on the
relationship.

15) What is an Index in SQL?

Index is used to increase the performance and allow faster
retrieval of records from the table. An index creates an entry for each value
and it will be faster to retrieve data.

16) Which are the different types of indexes
in SQL?

There are three types of Indexes in SQL:

Unique Index

Clustered Index

NonClustered Index

17) What is Unique Index?

Unique Index:

This indexing does not allow the field to have duplicate values if
the column is unique indexed. Unique index can be applied automatically when
primary key is defined.

18) What is Clustered Index in SQl?

Clustered Index:

The clustered index is used to reorder the physical order of the
table and search based on the key values. Each table can have only one
clustered index.

19) What is NonClustered Index in SQL?

NonClustered Index:

NonClustered Index does not alter the physical order of the table
and maintains logical order of data. Each table can have 999 non-clustered
indexes.

20) What is the difference between SQL, MySQL
and SQL Server?

SQL or Structured Query Language is a language which is used to
communicate with a relational database. It provides a way to manipulate and
create databases. On the other hand, MySQL and Microsoft's SQL Server both are
relational database management systems that use SQL as their standard
relational database language.

21) What is the difference between SQL and
PL/SQL?

SQL or Structured Query Language is a language which is used to
communicate with a relational database. It provides a way to manipulate and
create databases. On the other hand, PL/SQL is a dialect of SQL which is used
to enhance the capabilities of SQL. It was developed by Oracle Corporation in
the early 90's. It adds procedural features of programming languages in SQL.

22) Is it possible to sort a column using a
column alias?

Yes. You can use column alias in the ORDER BY clause for sorting.

23) What is the difference between clustered
and non clustered index in SQL?

There are mainly two type of indexes in SQL, Clustered index and
non clustered index. The differences between these two indexes is very
important from SQL performance perspective.

One table can have only one clustered
index but it can have many non clustered index.(approximately 250).

clustered index determines how data is
stored physically in table. Actually clustered index stores data in
cluster, related data is stored together so it makes simple to retrieve
data.

reading from a clustered index is much
faster than reading from non clustered index from the same table.

clustered index sort and store data
rows in the table or view based on their key value, while non cluster have
a structure separate from the data row.

24) What is the SQL query to display current
date?

There is a built in function in SQL called GetDate() which is used
to return current timestamp.

25) Which are the most commonly used SQL
joins?

Most commonly used SQL joins are INNER JOIN and (left/right) OUTER
JOIN.

26) What are the different types of joins in
SQL?

Joins are used to merge two tables or retrieve data from tables.
It depends on the relationship between tables.

Following are the most commonly used joins in SQL:

Inner Join

Right Join

Left Join

Full Join

27) What is Inner Join in SQL?

Inner join:

Inner join returns rows when there is at least one match of rows
between the tables.

28) What is Right Join in SQL?

Right Join:

Right join is used to retrieve rows which are common between the
tables and all rows of Right hand side table. It returns all the rows from the
right hand side table even though there are no matches in the left hand side
table.

29) What is Left Join in SQL?

Left Join:

Left join is used to retrieve rows which are common between the
tables and all rows of Left hand side table. It returns all the rows from Left
hand side table even though there are no matches in the Right hand side table.

30) What is Full Join in SQL?

Full Join:

Full join return rows when there are matching rows in any one of
the tables. This means, it returns all the rows from the left hand side table
and all the rows from the right hand side table.

31) What is "TRIGGER" in SQL?

Trigger allows you to execute a batch of SQL code when an insert,
update or delete command is executed against a specific table.

Actually triggers are special type of stored procedures that are
defined to execute automatically in place or after data modifications.

32) What is self join and what is the
requirement of self join?

Self join is often very useful to convert a hierarchical structure
to a flat structure. It is used to join a table to itself as like if that is
the second table.

33) What are set operators in SQL?

Union,IntersectorMinusoperators are called set operators.

34) What is the difference between BETWEEN
and IN condition operators?

The BETWEEN operator is used to display rows based on a range of
values. The IN condition operator is used to check for values contained in a
specific set of values.

35) What is a constraint? Tell me about its
various levels.

Constraints are representators of a column to enforce data entity
and consistency. There are two levels :

column level constraint

table level constraint

36) Write an SQL query to find names of
employee start with 'A'?

1.SELECT * FROM Employees WHERE EmpName like'A%'

37) Write an SQL query to get third maximum
salary of an employee from a table named employee_table.

1.SELECTTOP 1 salary

2.FROM (

3.SELECTTOP 3 salary

4.FROM employee_table

5.ORDERBY salary DESC ) AS emp

6.ORDERBY salary ASC;

38) What is the difference between DELETE and
TRUNCATE statement in SQL?

The main differences between SQL DELETE and TRUNCATE statements
are given below:

No.

DELETE

TRUNCATE

1)

DELETE is aDML
command.

TRUNCATE is aDDL
command.

2)

Wecan use
WHEREclause in DELETE
command.

Wecannot use
WHEREclause with TRUNCATE

3)

DELETE statement is usedto
delete a rowfrom a table

TRUNCATE statement is usedto
remove all the rowsfrom a
table.

4)

DELETE isslowerthan TRUNCATE statement.

TRUNCATE statement isfasterthan DELETE statement.

5)

Youcan
rollbackdata after using
DELETE statement.

It isnot
possible to rollbackafter
using TRUNCATE statement.

39) What is ACID property in database?

ACID property is used to ensure that the data transactions are
processed reliably in a database system.

A single logical operation of a data is called transaction.

ACID is an acronym for Atomicity, Consistency, Isolation,
Durability.

Atomicity:it
requires that each transaction is all or nothing. It means if one part of the
transaction fails, the entire transaction fails and the database state is left
unchanged.

Consistency:the
consistency property ensure that the data must meet all validation rules. In
simple words you can say that your transaction never leaves your database
without completing its state.

Isolation:this
property ensure that the concurrent property of execution should not be met.
The main goal of providing isolation is concurrency control.

Durability:durability
simply means that once a transaction has been committed, it will remain so,
come what may even power loss, crashes or errors.

40) What is the difference among NULL value,
zero and blank space?

Ans: A NULL value is not
same as zero or a blank space. A NULL value is a value which is 'unavailable,
unassigned, unknown or not applicable'. On the other hand, zero is a number and
blank space is treated as a character.

41) What is the usage of SQL functions?

SQL functions are used for following purpose:

To perform calculations on data.

To modify individual data items.

To manipulate the output.

To format dates and numbers.

To convert data types.

42) Which are the different case manipulation
functions in SQL?

There are three case manipulation functions in SQL:

LOWER

UPPER

INITCAP

43) What is the usage of NVL function?

The NVL function is used to convert NULL value to a actual value.

44) Which function is used to return
remainder in a division operator in SQL?

The MOD function returns the remainder in a division operation.

45) What is the syntax and use of the
COALESCE function?

The syntax of COALESCE function:

1.COALESCE(exp1, exp2, ... expn)

The COALESCE function is used to return the first non-null
expression given in the parameter list.

46) What is the usage of DISTINCT keyword?

The DISTINCT keyword is used to ensure that the fetched value is
only a non-duplicate value.

Wednesday, 3 May 2017

SQL or Structured Query Language is a language; language that
communicates with a relational database thus providing ways of
manipulating and creating databases. MySQL and Microsoft’s SQL Server
both are relational database management systems that use SQL as their
standard relational database language.

What is the difference between SQL and PL/SQL?

PL/SQL is a dialect of SQL that adds procedural features of
programming languages in SQL. It was developed by Oracle Corporation in
the early 90's to enhance the capabilities of SQL.

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

Following are various DDL or Data Definition Language commands in SQL −

CREATE − it creates a new table, a view of a table, or other object in database.

ALTER − it modifies an existing database object, such as a table.

DROP − it deletes an entire table, a view of a table or other object in the database.

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

Following are various DML or Data Manipulation Language commands in SQL −

SELECT − it retrieves certain records from one or more tables.

INSERT − it creates a record.

UPDATE − it modifies records.

DELETE − it deletes records.

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

Following are various DCL or Data Control Language commands in SQL −

GRANT − it gives a privilege to user.

REVOKE − it takes back privileges granted from user.

Can you sort a column using a column alias?

Yes. A column alias could be used in the ORDER BY clause.

Is a NULL value same as zero or a blank space? If not then what is the difference?

A NULL value is not same as zero or a blank space. A NULL value is a
value which is ‘unavailable, unassigned, unknown or not applicable’.
Whereas, zero is a number and blank space is a character.

Say True or False. Give explanation if False.
If a column value taking part in an arithmetic expression is NULL, then the result obtained would be NULLM.

True.

If a table contains duplicate
rows, does a query result display the duplicate values by default? How
can you eliminate duplicate rows from a query result?

A query result displays all rows including the duplicate rows. To
eliminate duplicate rows in the result, the DISTINCT keyword is used in
the SELECT clause.

What is the purpose of the condition operators BETWEEN and IN?

The BETWEEN operator displays rows based on a range of values. The IN
condition operator checks for values contained in a specific set of
values.

How do you search for a value in a database table when you don’t have the exact value to search for?

In such cases, the LIKE condition operator is used to select rows
that match a character pattern. This is also called ‘wildcard’ search.

What is the default ordering of data using the ORDER BY clause? How could it be changed?

The default sorting order is ascending. It can be changed using the DESC keyword, after the column name in the ORDER BY clause.

What are the specific uses of SQL functions?

SQL functions have the following uses −

Performing calculations on data

Modifying individual data items

Manipulating the output

Formatting dates and numbers

Converting data types

What are the case manipulation functions of SQL?

LOWER, UPPER, INITCAP

Which function returns the remainder in a division operation?

The MOD function returns the remainder in a division operation.

What is the purpose of the NVL function?

The NVL function converts a NULL value to an actual value.

What is the difference between the NVL and the NVL2 functions?

The NVL(exp1, exp2) function converts the source expression (or
value) exp1 to the target expression (or value) exp2, if exp1 contains
NULL. The return value has the same data type as that of exp1.
The NVL2(exp1, exp2, exp3) function checks the first expression exp1,
if it is not null then, the second expression exp2 is returned. If the
first expression exp1 is null, then the third expression exp3 is
returned.

What is the use of the NULLIF function?

The NULLIF function compares two expressions. If they are equal, the
function returns null. If they are not equal, the first expression is
returned.

Discuss the syntax and use of the COALESCE function?

The COALESCE function has the expression COALESCE(exp1, exp2, …. expn)
It returns the first non-null expression given in the parameter list.

Which expressions or functions allow you to implement conditional processing in a SQL statement?

There are two ways to implement conditional processing or IF-THEN-ELSE logic in a SQL statement.

Using CASE expression

Using the DECODE function

You want to display a result
query from joining two tables with 20 and 10 rows respectively.
Erroneously you forget to write the WHERE clause. What would be the
result?

The result would be the Cartesian product of two tables with 20 x 10 = 200 rows.

What is the difference between cross joins and natural joins?

The cross join produces the cross product or Cartesian product of two
tables. The natural join is based on all the columns having same name
and data types in both the tables.

What is the purpose of the group functions in SQL? Give some examples of group functions.

Group functions in SQL work on sets of rows and returns one result
per group. Examples of group functions are AVG, COUNT, MAX, MIN, STDDEV,
SUM, VARIANCE.

Say True or False. Give explanation if False.By default the group functions consider only distinct values in the set.

By default, group functions consider all values including the duplicate values.

A subquery is a SELECT statement embedded in a clause of another
SELECT statement. It is used when the inner query, or the subquery
returns a value that is used by the outer query. It is very useful in
selecting some rows in a table with a condition that depends on some
data which is contained in the same table.

Say True or False. Give explanation if False.A single row subquery returns only one row from the outer SELECT statement

False. A single row subquery returns only one row from the inner SELECT statement.

Say True or False. Give explanation if False.A multiple row subquery returns more than one row from the inner SELECT statement.

True.

Say True or False. Give explanation if False.Multiple column subqueries return more than one column from the inner SELECT statement.

True.

What’s wrong in the following query?

SELECT student_code, name
FROM students
WHERE marks =(SELECT MAX(marks)
FROM students
GROUP BY subject_code);

Here a single row operator = is used with a multiple row subquery.

What are the various multiple row comparison operators in SQL?

IN, ANY, ALL.

What is the pupose of DML statements in SQL?

The DML statements are used to add new rows to a table, update or
modify data in existing rows, or remove existing rows from a table.

Which statement is used to add a new row in a database table?

The INSERT INTO statement.

Say True or False. Give explanation if False.While inserting new rows in a table you must list values in the default order of the columns.

True.

How do you insert null values in a column while inserting data?

Null values can be inserted into a table by one of the following ways −

Implicitly by omitting the column from the column list.

Explicitly by specifying the NULL keyword in the VALUES clause.

Say True or False. Give explanation if False.INSERT statement does not allow copying rows from one table to another.

False. INSERT statement allows to add rows to a table copying rows from an existing table.

How do you copy rows from one table to another?

The INSERT statement can be used to add rows to a table by copying
from another table. In this case, a subquery is used in the place of the
VALUES clause.

What happens if you omit the WHERE clause in the UPDATE statement?

All the rows in the table are modified.

Can you modify the rows in a table based on values from another table? Explain.

Yes. Use of subqueries in UPDATE statements allow you to update rows in a table based on values from another table.

Say True or False. Give explanation if False.The DELETE statement is used to delete a table from the database.

False. The DELETE statement is used for removing existing rows from a table.

What happens if you omit the WHERE clause in a delete statement?

All the rows in the table are deleted.

Can you remove rows from a table based on values from another table? Explain.

Yes, subqueries can be used to remove rows from a table based on values from another table.

Say True or False. Give explanation if False.Attempting to delete a record with a value attached to an integrity constraint, returns an error.

True.

Say True or False. Give explanation if False.You can use a subquery in an INSERT statement.

True.

What is the purpose of the MERGE statement in SQL?

The MERGE statement allows conditional update or insertion of data
into a database table. It performs an UPDATE if the rows exists, or an
INSERT if the row does not exist.

Monday, 6 March 2017

Before we access oracle database, we must start
oracle database. Starting up oracle database means starting oracle instance and
associating oracle instance with an oracle database so that oracle instance can
access the database.

The process is very length and complicated.
Several steps are involved in it. But fortunately we do not have to know all
that happens when a database starts. We just need to select an option or two to
startup database.

Generally you do not have to startup database in
case of Oracle Server running on Windows NT/Windows 2000 as oracle server
automatically starts in this case. However, if you ever have to start oracle
database on Windows NT/Windows 2000, follow the steps given below:

1.Start services program using Administrative Tools -> Service in
Windows/2000 or Control Panel -> Service on Windows NT.

2.If service OracleServiceOracle8i has not yet started,
click on it with right button and select start option from popup menu.

The exact name of the
service depends on the name you have given to oracle instance at the time of
installing it.

Note: Starting
and shutting down the database is the job of Database Administrator. As this
books assumes that you are an application developer, it doesn’t get into those
details.

Starting up database in Personal Oracle

Unlike Oracle Server in Personal Oracle, Oracle
Instance doesn’t start on its own. The Oracle Instance must be explicitly
started. The following are the steps to start oracle on Personal Oracle:

1.Select start database
option in Personal Oracle8i for windows menu.

2.When a dialog box is displayed
wait until the message Oracle Instance Started appears.

3.Click on Close button to
close the dialog box.

Starting SQL*PLUS

Sql*plus is a tool that comes along with Oracle.
It is used to issue SQL and SQL*PLUS commands. It provides command line
interface through which we can enter SQL and SQL*PLUS command.

To start SQL*PLUS, take the steps given below:

1.Select start->programs->Oracle
- Oracle8i.

Oracle8i is the name of the instance. It
may be different on your system.

2.Then select Application
Development -> SQL Plus.

3.When Log On dialog box
is displayed, enter username, password and Host string.
Use tab key to move from one field to another.For more information about each of these fields, see next section.

4.Click on OK.

5.If the information supplied is
valid then you enter into Oracle and SQL*PLUS will display SQL> prompt.

Username, Password and Host String

Oracle is a multi-user database. Whoever is
access the database must log on to database.To log on we have to supply username and password.When the given username and password are
recognized by Oracle, it will allow us to access data.A user can access only the data that belongs
to his/her and not the data of others.However, it is possible for a user to grant privileges to others so that
other can access his/her data.

Creation of users and management of overall
security is the responsibility of Database Administrator (DBA).DBA is the person who makes sure that
database is functioning smoothly. He is responsible for operations such as
taking backup of the database, recovering the database in the event of failure,
fine tuning database to get best performance. So, if you want to have a new
account under your name, please consult administrator of your database.

Username & Password

Every user who wants to access oracle database
must have an account in the database. These accounts are created by DBA. Each
account is associated with username and password.

Oracle comes with a set of predefined accounts.
The following are the usernames and passwords of these accounts.

UsernamePassword

systemmanager

syschange_on_install

Scotttiger

Demodemo

Note: when you
enter into oracle using either system or sys then you become DBA.
That means you get special privileges to perform major operations such as
creating users etc.

Host String

Host string is a name that is used to access
oracle server that is running on a different machine from client. This is
required only when you are trying to access oracle server that is not on the
current machine. That means, you never need to use host string for Personal
Oracle as client and oracle always run on the same machine in Personal Oracle.

Host string is required when you are trying to
connect to Oracle Server running on remote machine.Host string is actually called as net
service name. Net service name is a name that is stored in TNSNAMES.ORA
file on the client to provide the following information.

Host

Name of the machine or IP address of the
machine on which oracle server is running.

Instance name

Name of the Oracle Instance running on the
remote machine.

Port Number

Port number of the listener, a program that
takes requests from clients.Port
number is an integer that uniquely identifies the program on the server.

How to enter SQL statements?

SQL*PLUS allow to types of command to entered at
the prompt- SQL and SQL*PLUS.

SQL commands include commands of ANSI/ISO SQL
and extra commands added to ANSI SQL by oracle.

The following are the rules to be followed while
entering SQL commands.

1.An SQL statement may be entered
in multiple lines.

2.It is not possible to break a
word across lines.

3.SQL statement must be
terminated by semicolon (;).

The following is an example of SQL command.What this command does is not important at
this moment.

SQL> select ccode,name

2from courses

3where fee > 5000;

In the above command, we entered the command in
three lines. When you enter semicolon and press enter key then SQL*PLUS will
take it as the end of the command. Also note that you have to press enter key
at the end of each line.

Note: Both SQL
and SQL*PLUS commands are NOT case sensitive.

How to enter SQL*PLUS statements?

SQL*Plus statements are available only in
SQL*PLUS. They are not part of standard SQL. SQL*Plus commands are mainly used
for two purposes – editing SQL commands and formatting result of query.

The following rules are to be followed while
entering these commands.

1.The entire command must be
entered on a single line.

2.No need to terminate command
with semicolon (;).

3.Commands can be abbreviated.
However, the amount of abbreviation is not fixed.Some commands are abbreviated to one letter
some are abbreviated to 2 and so on.

The following example show how to use CLEAR
SCREEN command of SQL*PLUS.

SQL>clear screen

Or it can be abbreviated to

SQL>cl scr

Common Errors

The following are the common errors that you get
while you are trying to log on to Oracle.

Ora-01017: invalid username/password; login
denied

The reason for this error is that you have entered
a username or password that is not valid. Check whether username you are
entering is really existing and password is correctly typed.Sql*plus gives you three chances to type
username and password correctly. If youcannot log on successfully in three chances then Sql*plus will exit.
However, you can restart Sql*plus again.

ORA-01034: ORACLE not available

The reason for this message is that Oracle
Instance is not up and running. You have to first make sure you have started
Oracle Instance. Actually there are afew other problems that occurs when Oracle Instance has not started
successfully. If this is case in Oracle Server, notify administrator. If this
is the case with Personal Oracle, make sure you start database as mentioned in
“starting up database” section.

Summary

In this chapter, we have seen what is Oracle and
what is the difference between Oracle Server and Personal Oracle. We have seen
how to connect to Oracle through SQL*Plus.

In the next chapter, we start creating tables
and understanding elementary statements in SQL.