SQL Questions - Page 1

1. What is SQL ?

SQL stands for Structured Query Language. It is a database tool which is used to create and access database to support software application. Read more...

2. What is Dynamic SQL ?

Dynamic SQL is a programming technique that enables you to build SQL statements dynamically at runtime. Dynamic SQL is easy to implement and very powerful. When creating database objects, such as Creating index on a table then run big query and drop index by using execute immediate. Read more...

3. Difference b/w SQL and PL/SQL ?

SQL is a structured query language to create and access databases whereas PL/SQL comes with procedural concepts of programming languages.

Sr. No.	Key	SQL	PL/SQL
1	Definition	SQL, is Structural Query Language for database.	PL/SQL is a programming language using SQL for a database.
2	Variables	SQL has no variables.	PL/SQL has variables, data types etc.
3	Control Structures	SQL has no FOR loop, if control and similar structures.	PL/SQL has FOR loop, while loop, if controls and other similar structures.
4	Operations	SQL can execute a single operation at a time.	PL/SQL can perform multiple operation at a time.
5	Language Type	SQL is a declarative language.	PL/SQL is a procedural language.
6	Embedded	SQL can be embedded in a PL/SQL block.	PL/SQL can also be embedded in SQL code.
7	Interaction	SQL directly interacts with database server.	PL/SQL does not directly interacts with database server.
8	Orientation	SQL is data oriented language.	PL/SQL is application oriented language.
9	Objective	SQL is used to write queries, create and execute DDL and DML statements.	PL/SQL is used to write program blocks, functions, procedures, triggers and packages.

4. What are the types of statements supported by SQL ?

1.DDL (Data Definition Language): It is used to define the database structure such as tables. It includes three statements such as Create, Alter, and Drop.

2.DML (Data Manipulation Language): These statements are used to manipulate the data in records. Commonly used DML statements are Insert, Update, and Delete.

3.DCL (Data Control Language): These statements are used to set privileges such as Grant and Revoke database access permission to the specific user.

5. Type of clauses used in SQL?

From , Where, Group by, Having, Select, Order by. (Execute in same sequence). Read more...

6. What are the SQL constraints ?

Constraints are used to set the rules for all records in the table. If any constraints get violated then it can abort the action that caused it. 

Example - Primary Key Constraint, Foreign Key Constraint ,Unique Key Constraint, Not null Constraint, Check Constraint. Read more...

7. What is Primary Key Constraint ?

A Primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused.

8. What is Foreign Key Constraint ?

When a table's primary key field is added to related tables in order to create the common field which relates the two tables, it called a foreign key in other tables. Foreign Key constraints enforce referential integrity.

9. What is Check Constraint ?

A check constraint is used to limit the values or type of data that can be stored in a column. They are used to enforce domain integrity.

10. What is Unique Key Constraint ?

A unique constraint prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.

11. What is the difference b/w UNIQUE and PRIMARY KEY ?

A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys. The primary key cannot contain Null values whereas Unique key can contain Null values.

12. What is Composite Primary Key ?

Primary key created on more than one column is called composite primary key.

13. Is it possible more than one foreign key ?

Yes, a table can have many foreign keys and only one primary key.

14. Difference between ROWID and Rownum ?

ROWNUM is representative of the sequence allocated to any data retrieval bunch. ROWID is the permanent identity or address of a row. 

ROWID	ROWNUM
ROWID is representative of the allocation of physical memory.	ROWNUM is representative of the sequence allocated to any data retrieval bunch.
ROWID is the permanent identity or address of a row.	ROWNUM is a temporarily assigned sequence to a row.
ROWID is a 16-digit Hexadecimal number in the format BBBBBBBB.RRRR.FFFF. Here B is Block, R is Row, and F is File.	ROWNUM is a numeric sequence number.
The output of ROWID is the physical address of a row.	The output of ROWNUM is the sequence number of a row.
ROWID helps to retrieve data from a row.	ROWNUM allows retrieving a row containing data.
ROWID comprises of the position of the row, data object number, the data block in the data file, as well as data file in which row resides.	ROWNUM comprises of sequence numbers of the rows.
Oracle automatically generates a unique ROWID at the time of insertion of a row.	ROWNUM is a dynamic value that is retrieved automatically with specific statement outputs.
The fastest way of accessing data is by using ROWID.	Accessing data is unrelated to ROWNUM.

15. What is Commit ?

It is used to save all changes made through the transaction.

16. What is Rollback ?

It is used to roll back the transaction such as all changes made by the transaction are reverted back and database remains as before.

17. What is Savepoint ?

It is used to set the point from where the transaction is to be rolled back.

18. Difference between DELETE and TRUNCATE ?

The basic difference is DELETE is  a DML command and TRUNCATE is DDL. DELETE is used to delete a specific row from the table whereas TRUNCATE is used to remove all rows from the table. We can use DELETE with WHERE clause but cannot use TRUNCATE with it.

19. Order of Precedence used in executing SQL statements ?

1. Arithmetic operators (*, /, +, -)

2. Concatenation operators (||)

3. Comparison conditions

4. Is[NOT] NULL, LIKE, [NOT] IN

5. [NOT] BETWEEN

6. NOT Logical condition

7. AND logical condition

8. OR logical condition

20. What is Dual Table ?

DUAL is a table automatically created by Oracle Database along with the data dictionary. DUAL is in the schema of the user SYS but is accessible by the name DUAL to all users. It has one column, DUMMY , defined to be VARCHAR2(1) , and contains one row with a value X .

21. What is Data Type and their types?

Database data type refers to the format of data storage that can hold a distinct type or range of values. When computer programs store data in variables, each variable must be designated a distinct data type. 

Types - Char, Varchar, Varchar2, Date, Boolean, Long, Number, Nchar, Nvarchar etc. Read more...

22. What are the LOB Data Types ?

The LOB datatypes BLOB, CLOB, NCLOB, and BFILE enable you to store and manipulate large blocks of unstructured data (such as text, graphic images, video clips, and sound waveforms) in binary or character format.

23. Type of String Function ?

String functions perform various character manipulations. They operate on character strings.

S.No	Function & Purpose
1	CONCAT(x, y); Concatenates the strings x and y and returns the appended string.
2	LENGTH(x); Returns the number of characters in x.
3	LOWER(x); Converts the letters in x to lowercase and returns that string.
4	REPLACE(x, search_string, replace_string); Searches x for search_string and replaces it with replace_string.
5	TRIM([trim_char FROM) x); Trims characters from the left and right of x.
6	UPPER(x); Converts the letters in x to uppercase and returns that string.

24. What is Date Functions and their Type?

Most commonly used Oracle date functions that help you handle date and time data easily and more effectively.

ADD_MONTHS : Add a number of months (n) to a date and return the same day which is n of months away.

MONTHS_BETWEEN: Return the number of months between two dates.

NEXT_DAY: Get the first weekday that is later than a specified date.

SYSDATE: Return the current system date and time of the operating system where the Oracle Database resides.

TO_CHAR: Convert a date which is in the character string to a DATE value.

TRUNC: Return a date truncated to a specific unit of measure.

25. What are the Aggregate Function ?

An aggregate function is a mathematical computation involving a range of values that results in just a single value expressing the significance of the accumulated data it is derived from.

Aggregate Function - Avg(), Count(), Min(), Max(), Sum()