- It specifies that table should not be dropped if any dependencies (i.e. triggers, stored procedure, primary key, foreign key etc) exist.
- Therefore, if there are dependencies then error is generated and the object is not dropped.
It is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update in database. Examples: SELECT, INSERT, UPDATE, DELETE, MERGE, CALL, EXPLAIN PLAN, LOCK TABLE
It is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database. Examples: CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME
It is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it. Examples: GRANT, REVOKE statements
It is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database. Examples: COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION statements.
- It can substitute for one or more characters when searching for data in a database.
- SQL wildcards must be used with the SQL LIKE operator.
- With SQL, the following wildcards can be used %, -, [charlist] etc.
- It perform a calculation on a set of values and return a single value.
- Except for COUNT, aggregate functions ignore null values.
- Aggregate functions are frequently used with the GROUP BY clause of the SELECT statement.
- Aggregate functions can be used as expressions only in the following:
- The select list of a SELECT statement (either a SubQuery or an outer query).
- A COMPUTE or COMPUTE BY clause.
- A HAVING clause.
- ROLLUP clause is used to do aggregate operation on multiple levels in hierarchy.
- It work with the “Group By ” clause its main functioning comes into existence when we use Group by.
We can get sub-total of row by using the Rollup function.
- When result is return by Group By class first row display grand total or we can say that the main total.
- Syntax:- select firstcolumn, secondcolumn, sum(thirdcolumn) from tablename group by firstcolumn, secondcolumn with rollup order by firstcolumn.
- The CUBE operator generates a result set that is a multidimensional cube.
- It is a result set that contains a cross tabulation of all the possible combinations of the dimensions.
- The CUBE operator is specified in the GROUP BY clause of a SELECT statement.
- The select list contains the dimension columns and aggregate function expressions.
- The GROUP BY specifies the dimension columns and the keywords WITH CUBE.
- CUBE generates a result set that shows aggregates for all combinations of values in the selected columns.
- ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.
Disclaimer – F5debug Interview Questions & Answers Series:
You may recopy extracts from these pages (“the material”) to individual third party websites or to any intranet websites, but only if:
You acknowledge www.f5debug.net as the source of the material. Such acknowledgment should include reference to www.f5debug.net in the copy of the material and should also include “© Karthikeyan Anbarasan, www.f5debug.net “. You inform the third party that these conditions apply to him/her and that he/she must comply with them.