What is SQL and also describe types of SQL statements?
SQL stands for Structured Query Language. SQL is a language used to communicate with the server to access, manipulate and control data.
There are 5 different types of SQL statements.
Data Retrieval: SELECT
Data Manipulation Language (DML): INSERT, UPDATE, DELETE, MERGE
Data Definition Language (DDL): CREATE, ALTER, DROP, RENAME, TRUNCATE.
Transaction Control Statements: COMMIT, ROLLBACK, SAVEPOINT
Data Control Language (DCL): GRANT, REVOKE
What is an alias in SQL statements?
Alias is a user-defined alternative name given to the column or table. By default column alias headings appear in upper case. Enclose the alias in a double quotation marks (“ “) to make it case sensitive. “AS” Keyword before the alias name makes the SELECT clause easier to read.
For ex: Select empname AS name from employee; (Here AS is a keyword and “name” is an alias).
What is a Literal? Give an example where it can be used?
A Literal is a string that can contain a character, a number, or a date that is included in the SELECT list and that is not a column name or a column alias. Date and character literals must be enclosed within single quotation marks (‘ ‘), number literals need not.
For ex: Select last_name||’is a’||job_id As “emp details” from employee; (Here “is a” is a literal).
Define the order of Precedence used in executing SQL statements.
Order of Precedence used in executing SQL statements:
Arithmetic operators (*, /, +, -)
Concatenation operators (||)
Comparison conditions
Is[NOT] NULL, LIKE, [NOT] IN
[NOT] BETWEEN
NOT Logical condition
AND logical condition
OR logical condition
What are SQL functions? Describe in brief different types of SQL functions?
SQL Functions are very powerful feature of SQL. SQL functions can take arguments but always return some value.
There are two distinct types of SQL functions:
1) Single-Row functions: These functions operate on a single row to give one result per row.
Types: Character, Number, Date, Conversion, General
2) Multiple-Row functions: These functions operate on groups of rows to give one result per group of rows.
Types: AVG, COUNT, MAX, MIN, SUM, STDDEV, VARIANCE
Explain character, number and date function in detail?
Character functions: accept character input and return both character and number values. Types of character function are:
a) Case-Manipulation Functions: LOWER, UPPER, INITCAP
b) Character-Manipulation Functions: CONCAT, SUBSTR, LENGTH, INSTR, LPAD/RPAD, TRIM, REPLACE
Number Functions: accept Numeric input and return numeric values. Number Functions are: ROUND, TRUNC and MOD
Date Functions: operates on values of the Date data type. (All date functions return a value of DATE data type except the MONTHS_BETWEEN Function, which returns a number. Date Functions are MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, TRUNC.
What is a Dual Table?
Dual table is owned by the user SYS and can be accessed by all users. It contains one columnDummy and one row with the value X. The Dual Table is useful when you want to return a value only once. The value can be a constant, pseudocolumn or expression that is not derived from a table with user data.
Explain Conversion function in detail?
Conversion Functions converts a value from one data type to another. Conversion functions are of two types:
Implicit Data type conversion:
VARCHAR2 or CHAR To NUMBER, DATE
NUMBER To VARCHAR2
DATE To VARCHAR2
Explicit data type conversion:
TO_NUMBER function is used to convert Character string to Number format. TO_NUMBER function use fx modifier. Format: TO_NUMBER ( char[, ‘ format_model’] ). fx modifier specifies the exact matching for the character argument and number format model of TO_NUMBER function.
TO_CHAR function is used to convert NUMBER or DATE data type to CHARACTER format. TO_CHAR Function use fm element to remove padded blanks or suppress leading zeros. TO_CHAR Function formats:TO_CHAR (date, ‘format_model’).Format model must be enclosed in single quotation marks and is case sensitive.
For ex: Select TO_CHAR (hiredate, ‘MM/YY’) from employee.
TO_DATE function is used to convert Character string to date format. TO_DATE function use fx modifier which specifies the exact matching for the character argument and date format model of TO_DATE function. TO_DATE function format: TO_DATE ( char[, ‘ format_model’] ).
For ex: Select TO_DATE (‘may 24 2007’,’mon dd rr’) from dual;
Describe different types of General Function used in SQL?
General functions are of following types:
NVL: Converts a null value to an actual value. NVL (exp1, exp2) .If exp1 is null then NVL function return value of exp2.
NVL2: If exp1 is not null, nvl2 returns exp2, if exp1 is null, nvl2 returns exp3. The argument exp1 can have any data type. NVL2 (exp1, exp2, exp3)
NULLIF: Compares two expressions and returns null if they are equal or the first expression if they are not equal. NULLIF (exp1, exp2)
COALESCE: Returns the first non-null expression in the expression list. COALESCE (exp1, exp2… expn). The advantage of the COALESCE function over NVL function is that the COALESCE function can take multiple alternative values.
Conditional Expressions: Provide the use of IF-THEN-ELSE logic within a SQL statement. Example: CASE Expression and DECODE Function.
What is difference between COUNT (*), COUNT (expression), COUNT (distinct expression)? (Where expression is any column name of Table)
COUNT (*): Returns number of rows in a table including duplicates rows and rows containing null values in any of the columns.
COUNT (EXP): Returns the number of non-null values in the column identified by expression.
COUNT (DISTINCT EXP): Returns the number of unique, non-null values in the column identified by expression.
Difference between where and having clause?
WHERE is used to filter records before any groupings take place. HAVING is used to filter values after they have been groups.
Main difference between WHERE and HAVING clause in SQL is that, condition specified in WHERE clause is used while fetching data (rows) from table, and data which doesn’t pass the condition will not be fetched into result set, on the other hand HAVING clause is later used to filter summarized data or grouped data.
What is a Sub Query? Describe its Types?
A sub query is a SELECT statement that is embedded in a clause of another SELECT statement. Sub query can be placed in WHERE, HAVING and FROM clause.
Guidelines for using sub queries:
– Enclose sub queries within parenthesis
– Place sub queries on the right side of the comparison condition.
– Use Single-row operators with single-row sub queries and Multiple-row operators with multiple-row sub queries.
Types of sub queries:
Single-Row Sub query: Queries that return only one row from the Inner select statement. Single-row comparison operators are: =, >, >=, <, <=, <>
Multiple-Row Sub query: Queries that return more than one row from the inner Select statement. There are also multiple-column sub queries that return more than one column from the inner select statement. Operators includes: IN, ANY, ALL.
What is difference between ANY and ALL operators?
ANY Operator compares value to each value returned by the subquery. ANY operator has a synonym SOME operator.
> ANY means more than the minimum.
< ANY means less than the maximum
= ANY is equivalent to IN operator.
ALL Operator compares value to every value returned by the subquery.
> ALL means more than the maximum
< ALL means less than the minimum
<> ALL is equivalent to NOT IN condition.
What is a MERGE statement? What is a difference between “VERIFY” and “FEEDBACK” command?
The MERGE statement inserts or updates rows in one table, using data from another table. It is useful in data warehousing applications.
VERIFY Command: Use VERIFY Command to confirm the changes in the SQL statement (Old and New values). Defined with SET VERIFY ON/OFF.
Feedback Command: Displays the number of records returned by a query.
What is the use of Double Ampersand (&&) in SQL Queries? Give example?
Use “&&” if you want to reuse the variable value without prompting the user each time.
For ex: Select empno, ename, &&column_name from employee order by &column_name;
What are Joins and how many types of Joins are there?
Joins are used to retrieve data from more than one table.
There are 5 different types of joins.
Oracle 8i and Prior – SQL: 1999 (9i)
Equi Join – Natural/Inner Join
Outer Join – Left Outer/ Right Outer/ Full Outer Join
Self Join – Join ON
Non-Equi Join – Join USING
Cartesian Product – Cross Join
Explain all Joins used in Oracle 8i?
Cartesian Join: When a Join condition is invalid or omitted completely, the result is a Cartesian product, in which all combinations of rows are displayed. To avoid a Cartesian product, always include a valid join condition in a “where” clause. To Join ‘N’ tables together, you need a minimum of N-1 Join conditions. For ex: to join four tables, a minimum of three joins is required. This rule may not apply if the table has a concatenated primary key, in which case more than one column is required to uniquely identify each row.
Equi Join: This type of Join involves primary and foreign key relation. Equi Join is also called Simple or Inner Joins.
Non-Equi Joins: A Non-Equi Join condition containing something other than an equality operator. The relationship is obtained using an operator other than equal operator (=).The conditions such as <= and >= can be used, but BETWEEN is the simplest to represent Non-Equi Joins.
Outer Joins: Outer Join is used to fetch rows that do not meet the join condition. The outer join operator is the plus sign (+), and it is placed on the side of the join that is deficient in information. The Outer Join operator can appear on only one side of the expression, the side that has information missing. It returns those rows from one table that has no direct match in the other table. A condition involving an Outer Join cannot use IN and OR operator.
Self Join: Joining a table to itself.
Explain all Joins used in Oracle 9i and later release?
Cross Join: Cross Join clause produces the cross-product of two tables. This is same as a Cartesian product between the two tables.
Natural Joins: Is used to join two tables automatically based on the columns which have matching data types and names, using the keyword NATURAL JOIN. It is equal to the Equi-Join. If the columns have the same names but different data types, than the Natural Join syntax causes an error.
Join with the USING clause: If several columns have the same names but the data types do not match, than the NATURAL JOIN clause can be modified with the USING clause to specify the columns that should be used for an equi Join. Use the USING clause to match only one column when more than one column matches. Do not use a table name or alias in the referenced columns. The NATURAL JOIN clause and USING clause are mutually exclusive.
For ex: Select a.city, b.dept_name from loc a Join dept b USING (loc_id) where loc_id=10;
Joins with the ON clause: Use the ON clause to specify a join condition. The ON clause makes code easy to understand. ON clause is equals to Self Joins. The ON clause can also be used to join columns that have different names.
Left/ Right/ Full Outer Joins: Left Outer Join displays all rows from the table that is Left to the LEFT OUTER JOIN clause, right outer join displays all rows from the table that is right to the RIGHT OUTER JOIN clause, and full outer join displays all rows from both the tables either left or right to the FULL OUTER JOIN clause.
What is a difference between Entity, Attribute and Tuple?
Entity: A significant thing about which some information is required. For ex: EMPLOYEE (table). Attribute: Something that describes the entity. For ex: empno, empname, empaddress (columns). Tuple: A row in a relation is called Tuple.
What is a Transaction? Describe common errors can occur while executing any Transaction?
Transaction consists of a collection of DML statements that forms a logical unit of work.
The common errors that can occur while executing any transaction are:
The violation of constraints.
Data type mismatch.
Value too wide to fit in column.
The system crashes or Server gets down.
The session Killed.
Locking take place. Etc.
What is locking in SQL? Describe its types?
Locking prevents destructive interaction between concurrent transactions. Locks held until Commit or Rollback. Types of locking are:
Implicit Locking: Occurs for all SQL statements except SELECT.
Explicit Locking: Can be done by user manually.
Further there are two locking methods:
Exclusive: Locks out other users
Share: Allows other users to access
What is a difference between Commit, Rollback and Savepoint?
COMMIT: Ends the current transaction by making all pending data changes permanent.
ROLLBACK: Ends the current transaction by discarding all pending data changes.
SAVEPOINT: Divides a transaction into smaller parts. You can rollback the transaction till a particular named savepoint.
What are the advantages of COMMIT and ROLLBACK statements?
Advantages of COMMIT and ROLLBACK statements are:
Ensure data consistency
Can preview data changes before making changes permanent.
Group logically related operations.
What is the difference between ROLLBACK and ROLLBACK TO statements?
The transaction is completely ended after ROLLBACK statement i.e. ROLLBACK command completely undoes a transaction and release all locks.
On the other hand, a transaction is still active and running after ROLLBACK TO command as it undoes a part of the transaction up till the given SAVEPOINT.
Describe naming rules for creating a Table?
Naming rules to be consider for creating a table are:
Table name must begin with a letter,
Table name can be 1-30 characters long,
Table name can contain only A-Z, a-z, 0-9,_, $, #.
Table name cannot duplicate the name of another object owned by the same user.
Table name cannot be an oracle server reserved word.
What is a DEFAULT option in a table?
A column can be given a default value by using the DEFAULT option. This option prevents null values from entering the column if a row is inserted without a value for that column. The DEFAULT value can be a literal, an expression, or a SQL function such as SYSDATE and USER but the value cannot be the name of another column or a pseudo column such as NEXTVAL or CURRVAL.
What is a difference between USER TABLES and DATA DICTIONARY?
USER TABLES: Is a collection of tables created and maintained by the user. Contain USER information.
DATA DICTIONARY: Is a collection of tables created and maintained by the Oracle Server. It contains database information. All data dictionary tables are owned by the SYS user.
Describe few Data Types used in SQL?
Data Types is a specific storage format used to store column values. Few data types used in SQL are:
VARCHAR2(size): Minimum size is ‘1’ and Maximum size is ‘4000’
CHAR(size): Minimum size is ‘1’and Maximum size is ‘2000’
NUMBER(P,S): ” Precision” can range from 1 to 38 and the “Scale” can range from -84 to 127.
DATE
LONG: 2GB
CLOB: 4GB
RAW (size): Maximum size is 2000
LONG RAW: 2GB
BLOB: 4GB
BFILE: 4GB
ROWID: A 64 base number system representing the unique address of a row in the table.
In what scenario you can modify a column in a table?
During modifying a column:
You can increase the width or precision of a numeric column.
You can increase the width of numeric or character columns.
You can decrease the width of a column only if the column contains null values or if the table has no rows.
You can change the data type only if the column contains null values.
You can convert a CHAR column to the VARCHAR2 data type or convert a VARCHAR2 column to the CHAR data type only if the column contains null values or if you do not change the size.
Describe few restrictions on using “LONG” data type?
A LONG column is not copied when a table is created using a sub query. A LONG column cannot be included in a GROUP BY or an ORDER BY clause. Only one LONG column can be used per table. No constraint can be defined on a LONG column.
What is a SET UNUSED option?
SET UNUSED option marks one or more columns as unused so that they can be dropped when the demand on system resources is lower. Unused columns are treated as if they were dropped, even though their column data remains in the table’s rows. After a column has been marked as unused, you have no access to that column. A select * query will not retrieve data from unused columns. In addition, the names and types of columns marked unused will not be displayed during a DESCRIBE, and you can add to the table a new column with the same name as an unused column. The SET UNUSED information is stored in the USER_UNUSED_COL_TABS dictionary view.
What is a difference between Truncate and Delete?
Truncate: Removes all rows from a table and releases storage space used by that table.
Delete: Removes all rows from a table but does not release storage space used by that table.
TRUNCATE Command is faster while DELETE command is slower.
TRUNCATE Is a DDL statement and cannot be Rollback while DELETE Is a DDL statement and can be Rollback.
Database Triggers do not fire on TRUNCATE while Database Triggers fire on DELETE.
What is a main difference between CHAR and VARCHAR2?
CHAR pads blank spaces to a maximum length, whereas VARCHAR2 does not pad blank spaces.
What are Constraints? How many types of constraints are there?
Constraints are used to prevent invalid data entry or deletion if there are dependencies. Constraints enforce rules at the table level. Constraints can be created either at the same time as the table is created or after the table has been created. Constraints can be defined at the column or table level. Constraint defined for a specific table can be viewed by looking at the USER-CONSTRAINTS data dictionary table. You can define any constraint at the table level except NOT NULL which is defined only at column level. There are 5 types of constraints:
Not Null Constraint
Unique Key Constraint
Primary Key Constraint
Foreign Key Constraint
Check Key Constraint.
Describe types of Constraints in brief?
NOT NULL: NOT NULL Constraint ensures that the column contains no null values.
UNIQUE KEY: UNIQUE Key Constraint ensures that every value in a column or set of columns must be unique, that is, no two rows of a table can have duplicate values in a specified column or set of columns. If the UNIQUE constraint comprises more than one column, that group of columns is called a Composite Unique Key. There can be more than one Unique key on a table. Unique Key Constraint allows the input of Null values. Unique Key automatically creates index on the column it is created.
PRIMARY KEY: Uniquely identifies each row in the Table. Only one PRIMARY KEY can be created for each table but can have several UNIQUE constraints. PRIMARY KEY ensures that no column can contain a NULL value. A Unique Index is automatically created for a PRIMARY KEY column. PRIMARY KEY is called a Parent key.
FOREIGN KEY: Is also called Referential Integrity Constraint. FOREIGN KEY is one in which a column or set of columns take references of the Primary/Unique key of same or another table. FOREIGN KEY is called a child key. A FOREIGN KEY value must match an existing value in the parent table or be null.
CHECK KEY: Defines a condition that each row must satisfy. A single column can have multiple CHECK Constraints. During CHECK constraint following expressions is not allowed:
1) References to CURRVAL, NEXTVAL, LEVEL and ROWNUM Pseudo columns.
2) Calls to SYSDATE, UID, USER and USERENV Functions
What is a primary key, foreign key, unique key, Candidate 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.
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.
A Unique key constraint uniquely identifies each record in the database. This provides uniqueness for the column or set of columns.
The columns in a table that can act as a Primary Key are called Candidate Key.
What is the main difference between Unique Key and Primary Key?
A table can have more than one Unique Key & A table can have only one Primary Key.
Unique key column can store NULL values while Primary key column cannot store NULL values.
Unique key can identify each value in a column while primary key can identify each row in a table.
What are the different types of joins in SQL?
Inner join: Inner join returns rows when there is at least one match of rows between the tables.
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.
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.
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.
What is a difference between ON DELETE CASCADE and ON DELETE SET NULL?
ON DELETE CASCADE Indicates that when the row in the parent table is deleted, the dependent rows in the child table will also be deleted. ON DELETE SET NULL Coverts foreign key values to null when the parent value is removed. Without the ON DELETE CASCADE or the ON DELETE SET NULL options, the row in the parent table cannot be deleted if it is referenced in the child table.
What are Views and why they are used?
A View logically represents subsets of data from one or more table. A View is a logical table based on a table or another view. A View contains no data of its own but is like a window through which data from tables can be viewed or changed. The tables on which a view is based are called Base Tables. The View is stored as a SELECT statement in the data dictionary. View definitions can be retrieved from the data dictionary table: USER_VIEWS.
Views are used:
To restrict data access
To make complex queries easy
To provide data Independence
Views provide groups of user to access data according to their requirement.
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.
Which are the different types of indexes in SQL?
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.
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.
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.
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.
What is a difference between Simple and Complex Views?
Simple View: Derives data from only one table.
Complex View: Derives data from many tables.
Simple View: Contains no functions or group of data
Complex View: Contain functions or groups of data.
Simple View:Can perform DML operations through the view.
Complex View: Does not always allow DML operations through the view.
What are the restrictions of DML operations on Views?
Few restrictions of DML operations on Views are:
You cannot DELETE a row if the View contains the following:
Group Functions
A Group By clause
The Distinct Keyword
The Pseudo column ROWNUM Keyword.
You cannot MODIFY data in a View if it contains the following:
Group Functions
A Group By clause
The Distinct Keyword
The Pseudo column ROWNUM Keyword.
Columns defined by expressions (Ex; Salary * 12)
You cannot INSERT data through a view if it contains the following: