Category: sql Certificate

SQL – Using Manipulating Data ”; Previous Next Oracle provide Data Manipulation Language commands to exercise data operations in the database.Data operations can be populating the database tables with the application or business data,modifying the data and removing the data from the database,whenever required. Besides the data operations,there are set of commands which are used to control these operations.These commands are grouped as Transaction Control Language. There are three types of DML statements involved in a logical SQL transaction namely, Insert, Update, Delete and Merge.A transaction is the logical collection of DML actions within a database session. INSERT statement The INSERT command is used to store data in tables. The INSERT command is often used in higher-level programming languages such as Visual Basic.NET or C++ as an embedded SQL command; however,this command can also be executed at the SQL*PLUS prompt in command mode.There are two different forms of the INSERT command. The first form is used if a new row will have a value inserted into each column of the row. The second form of the INSERT command is used to insert rows where some of the column data is unknown or defaulted from another business logic.This form of the INSERT command requires that you specify column names for which data are being stored. Syntax: The below syntax can be followed if the values for all the columns in the table is definite and known. INSERT INTO table VALUES (column1 value, column2 value, …); The below syntax can be used if only few columns from the table have to be populated with a value. Rest of the columns can deduce their values either as NULL or from a different business logic. INSERT INTO table (column1 name, column2 name, . . .) VALUES (column1 value, column2 value, . . .); The INSERT statement below creates a new employee record in the EMPLOYEES table. Note that it inserts the values for the primary columns EMPLOYEE_ID, FIRST_NAME, SALARY and DEPARTMENT_ID. INSERT INTO employees (EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID) VALUES (130, ”KEMP”, 3800, 10); Otherwise, complete employee data can be inserted in the EMPLOYEES table without specifying the column list using the below INSERT statement – provided the values are known beforehand and must be in compliance with the data type and position of columns in the table. INSERT INTO employees VALUES (130, ”KEMP”,”GARNER”, ”[email protected]”, ”48309290”,TO_DATE (”01-JAN-2012”), ”SALES”, 3800, 0, 110, 10); Values to be inserted must be compatible with the data type of the column. Literals, fixed values and special values like functions, SYSDATE, CURRENT_DATE, SEQ.CURRVAL (NEXTVAL), or USER can be used as column values. Values specified must follow the generic rules. String literals and date values must be enclosed within quotes. Date value can be supplied in DD-MON-RR or D-MON-YYYY format, but YYYY is preferred since it clearly specifies the century and does not depend on internal RR century calculation logic. INSERT-AS-SELECT (IAS) statement Data can be populated into the target table from the source table using INSERT..AS..SELECT (IAS) operation. Its a direct path read operation.Its a simple way of creating copy of the data from one table to another or creating a backup copy of the table which the source table operations are online. For example, data can be copied from EMPLOYEES table to EMP_HISTORY table. INSERT INTO EMP_HISTORY SELECT EMPLOYEE_ID, EMPLOYEE_NAME, SALARY, DEPARTMENT_ID FROM employees; UPDATE statement The UPDATE command modifies the data stored in a column.It can update single or multiple rows at a time depending on the result set filtered by conditions specified in WHERE clause. Note that Updating columns is different from altering columns. Earlier in this chapter, you studied the ALTER command.The ALTER command changes the table structure, but leaves the table data unaffected.The UPDATE command changes data in the table, not the table structure. Syntax: UPDATE table SET column = value [, column = value …] [WHERE condition] From the syntax, The SET column = expression can be any combination of characters, formulas, or functions that will update data in the specified column name.The WHERE clause is optional, but if it is included, it specifies which rows will be updated.Only one table can be updated at a time with an UPDATE command. The UPDATE statement below updates the salary of employee JOHN to 5000. UPDATE employees SET salary = 5000 WHERE UPPER (first_name) = ”JOHN”; Though WHERE predicates are optional, but must be logically appended so as to modify only the required row in the table. The UPDATE statement below updates the salaries of all the employees in the table. UPDATE employees SET salary = 5000; Multiple columns can also be updated by specifying multiple columns in SET clause separated by a comma. For example, if both salary and job role has to be changed to 5000 and SALES respectively for JOHN, the UPDATE statement looks like, UPDATE employees SET SALARY = 5000, JOB_ID = ”SALES” WHERE UPPER (first_name) = ”JOHN”; 1 row updated. Another way of updating multiple columns of the same row shows the usage of subquery. UPDATE employees SET (SALARY, JOB_ID) = (SELECT 5000, ”SALES” FROM DUAL) WHERE UPPER (ENAME) = ”JOHN” DELETE statement The DELETE command is one of the simplest of the SQL statements. It removes one or more rows from a table. Multiple table delete operations are not allowed in SQL.The syntax of the DELETE command is as below. DELETE FROM table_name [WHERE condition]; The DELETE command deletes all rows in the table that satisfy the condition in the optional WHERE clause. Since the WHERE clause is optional, one can easily delete all rows from a table by omitting a WHERE clause since the WHERE clause limits the scope of the DELETE operation. The below DELETE statement would remove EDWIN”s details from EMP table. DELETE employees WHERE UPPER (ENAME) = ”EDWIN” 1 row deleted. Note: DELETE [TABLE NAME] and DELETE FROM [TABLE NAME] hold the same meaning. The WHERE condition in the conditional delete statements can make use of subquery as shown below. DELETE FROM employees

SQL – Conditional Expressions Questions ”; Previous Next 1. What is true about data types in Oracle DB? They are given to columns for sorting purposes. They are given to columns for a structured representation in a table. They are given to columns to constrain the nature of the data it can store. They are not mandatory. Answer: C. Data types define the nature of data which a column can store in a table. A column can store only one type of data. The primary data types available in Oracle are NUMBER, VARCHAR2, and DATE. 2. What is true about nested functions? There is a limit to use the Nested functions only 5 times in Oracle DB. They are evaluated from the outermost level to the innermost level. They are evaluated from the innermost level to the outermost level. All the functions in a Nested expression return the same data type. Answer: C. Single row functions can group functions can be nested in a SELECT query in which the innermost function is the first one to be executed. The result of the execution of innermost function serves as the input for the outer function. 3. Which of the following functions simplify working with columns that potentially contain null values? Nested functions General functions Conditional functions None of the above Answer: B. The general functions like NVL, NVL2, NULLIF, and COALESCE are used to pacify the effect of NULL while displaying the query results. They bypass the NULL values by assigning an alternative value. 4. Which of the following data types are appropriate for general functions? VARCHAR2 NUMBER DATE All Datatypes Answer: D. General functions are usually compatible with all primary data types like NUMBER, VARCHAR2 and DATE. 5. What is true about the COALESCE function? It accepts minimum 2 and maximum 5 input parameters It always returns the first NULL value among the input parameters It can accept unlimited number of input parameters It returns the first non-null parameter else it returns a null. Answer: C, D. The COALESCE function takes two mandatory parameters and any number of optional parameters. The syntax is COALESCE(expr1, expr2,Ö,exprn), where expr1 is returned if it is not null, else expr2 if it is not null, and so on. COALESCE is a general form of the NVL function, as the following two equations illustrate: COALESCE(expr1,expr2) = NVL(expr1,expr2), COALESCE(expr1,expr2,expr3) = NVL(expr1,NVL(expr2,expr3)) 6. How many input parameters are mandatory in NVL function? 0 1 2 3 Answer: C. The NVL function takes two mandatory parameters. Its syntax is NVL(original, ifnull), where original represents the term being tested and ifnull is the result returned if the original term evaluates to null. The data types of the original and ifnull parameters must always be compatible. They must either be of the same type, or it must be possible to implicitly convert ifnull to the type of the original parameter. The NVL function returns a value with the same data type as the original parameter. 7. What is wrong in the following statement? NVL (ifnull, original) There is nothing wrong The parameter original is not required The parameter ”ifnull” is not required The correct statement is NVL (original,ifnull) Answer: D. The NVL function evaluates whether a column or expression of any data type is null or not. If the term is null, an alternative not null value is returned; otherwise, the initial term is returned. 8. What will be the output of the following query? SELECT NVL(1234) FROM dual; 1234 1000 NULL ORA-00909:invalid number of arguments error Answer: D. he NVL function takes two mandatory parameters. Its syntax is NVL(original, ifnull), where original represents the term being tested and ifnull is the result returned if the original term evaluates to null. 9. What will be output of the following query? SELECT NVL(1234,” ”) FROM dual; A white space i.e. ” ” 1234 NULL value ORA-01722: invalid number Answer: D. The data types of the original and ifnull parameters must always be compatible. They must either be of the same type, or it must be possible to implicitly convert ifnull to the type of the original parameter. The NVL function returns a value with the same data type as the original parameter. The 1234 should be in single quotes. Implicit conversion of data type doesn”t happen in this case. 10. What will be outcome of the following query? SELECT NVL(SUBSTR(”abc”,-4),”SUBSTR didn”t work”) FROM dual; abc bc c SUBSTR didn”t work Answer: D. 11. You need to extract a report which gives the first name, last name and the commission percentage earned by all the employees in department 100. The report should not have any columns which are empty. All the columns should have at least a ”0” if there is no value for them. Which of the following queries will fulfill this requirement? (Consider the table structure as given) SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) SELECT first_name, last_name, commission_pct*salary FROM employees WHERE department_id = 100; SELECT first_name, last_name, NVL(commission_pct*salary,0) monthly_commission FROM employees WHERE department_id = 100; SELECT first_name, last_name, NVL(commission_pct,0)*salary monthly_commission FROM employees WHERE department_id = 100; SELECT first_name, last_name, commission_pct*salary FROM employees; Answer: B, C. 12. What are the types of Data conversions in Oracle DB? Implicit conversions Explicit conversions External conversions Physical conversions Answer: A, B. TO_CHAR, TO_NUMBER and TO_DATE are the three most widely used conversion functions and are discussed in detail. The TO_CHAR function converts numeric and date information into characters, while TO_NUMBER and TO_DATE convert character data into numbers and dates, respectively. 13. What happens during an implicit conversion in Oracle DB? Oracle DB implicitly converts one data type to the expected data type The user has to convert the data type to the expected data type Oracle DB doesn”t convert any data type Implicit conversion can

SQL – Subqueries to Solve Queries ”; Previous Next A subquery is best defined as a query within a query. Subqueries enable you to write queries that select data rows for criteria that are actually developed while the query is executing at run time. More formally, it is the use of a SELECT statement inside one of the clauses of another SELECT statement. In fact, a subquery can be contained inside another subquery, which is inside another subquery, and so forth. A subquery can also be nested inside INSERT, UPDATE, and DELETE statements. Subqueries must be enclosed within parentheses. A subquery can be used any place where an expression is allowed providing it returns a single value. This means that a subquery that returns a single value can also be listed as an object in a FROM clause listing. This is termed an inline view because when a subquery is used as part of a FROM clause, it is treated like a virtual table or view. Subquery can be placed either in FROM clause, WHERE clause or HAVING clause of the main query. Oracle allows a maximum nesting of 255 subquery levels in a WHERE clause. There is no limit for nesting subqueries expressed in a FROM clause.In practice, the limit of 255 levels is not really a limit at all because it is rare to encounter subqueries nested beyond three or four levels. A subquery SELECT statement is very similar to the SELECT statement used to begin a regular or outer query.The complete syntax of a subquery is: ( SELECT [DISTINCT] subquery_select_parameter FROM {table_name | view_name} {table_name | view_name} … [WHERE search_conditions] [GROUP BY column_name [,column_name ] …] [HAVING search_conditions] ) Types of Subqueries Single Row Sub Query: Sub query which returns single row output. They mark the usage of single row comparison operators, when used in WHERE conditions. Multiple row sub query: Sub query returning multiple row output. They make use of multiple row comparison operators like IN, ANY, ALL. There can be sub queries returning multiple columns also. Correlated Sub Query: Correlated subqueries depend on data provided by the outer query.This type of subquery also includes subqueries that use the EXISTS operator to test the existence of data rows satisfying specified criteria. Single Row Sub Query A single-row subquery is used when the outer query”s results are based on a single, unknown value. Although this query type is formally called “single-row,” the name implies that the query returns multiple columns-but only one row of results. However, a single-row subquery can return only one row of results consisting of only one column to the outer query. In the below SELECT query, inner SQL returns only one row i.e. the minimum salary for the company. It in turn uses this value to compare salary of all the employees and displays only those, whose salary is equal to minimum salary. SELECT first_name, salary, department_id FROM employees WHERE salary = (SELECT MIN (salary) FROM employees); A HAVING clause is used when the group results of a query need to be restricted based on some condition. If a subquery”s result must be compared with a group function, you must nest the inner query in the outer query”s HAVING clause. SELECT department_id, MIN (salary) FROM employees GROUP BY department_id HAVING MIN (salary) < (SELECT AVG (salary) FROM employees) Multiple Row Sub Query Multiple-row subqueries are nested queries that can return more than one row of results to the parent query. Multiple-row subqueries are used most commonly in WHERE and HAVING clauses. Since it returns multiple rows,it must be handled by set comparison operators (IN, ALL, ANY).While IN operator holds the same meaning as discussed in earlier chapter, ANY operator compares a specified value to each value returned by the sub query while ALL compares a value to every value returned by a sub query. Below query shows the error when single row sub query returns multiple rows. SELECT first_name, department_id FROM employees WHERE department_id = (SELECT department_id FROM employees WHERE LOCATION_ID = 100) department_id = (select * ERROR at line 4: ORA-01427: single-row subquery returns more than one row Usage of Multiple Row operators [> ALL] More than the highest value returned by the subquery [< ALL] Less than the lowest value returned by the subquery [< ANY] Less than the highest value returned by the subquery [> ANY] More than the lowest value returned by the subquery [= ANY] Equal to any value returned by the subquery (same as IN) Above SQL can be rewritten using IN operator like below. SELECT first_name, department_id FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE LOCATION_ID = 100) Note in the above query, IN matches department ids returned from the sub query,compares it with that in the main query and returns employee”s name who satisfy the condition. A join would be better solution for above query, but for purpose of illustration, sub query has been used in it. Correlated Sub Query As opposed to a regular subquery, where the outer query depends on values provided by the inner query,a correlated subquery is one where the inner query depends on values provided by the outer query. This means that in a correlated subquery,the inner query is executed repeatedly, once for each row that might be selected by the outer query. Correlated subqueries can produce result tables that answer complex management questions. Consider the below SELECT query. Unlike the subqueries previously considered, the subquery in this SELECT statement cannot be resolved independently of the main query. Notice that the outer query specifies that rows are selected from the employee table with an alias name of e1. The inner query compares the employee department number column (DepartmentNumber) of the employee table with alias e2 to the same column for the alias table name e1. SELECT EMPLOYEE_ID, salary, department_id FROM employees E WHERE salary > (SELECT AVG(salary) FROM EMP T WHERE E.department_id = T.department_id) Multiple Column Sub Query A multiple-column subquery returns more than

SQL – Using DDL Statements ”; Previous Next Using DDL Statements to Create and Manage Tables A schema is the collection of multiple database objects,which are known as schema objects.These objects have direct access by their owner schema.Below table lists the schema objects. Table – to store data View – to project data in a desired format from one or more tables Sequence – to generate numeric values Index – to improve performance of queries on the tables Synonym – alternative name of an object One of the first steps in creating a database is to create the tables that will store an organization”s data.Database design involves identifying system user requirements for various organizational systems such as order entry, inventory management, and accounts receivable. Regardless of database size and complexity, each database is comprised of tables. Creating the table To create a table in the database,a DBA must have certain information in hand – the table name, column name, column data types, and column sizes. All this information can be modified later using DDL commands. Table Naming Conventions – The name you choose for a table must follow these standard rules: The name must begin with a letter A-Z or a-z Can contain numbers and underscores Can be in UPPER of lower case Can be up to 30 characters in length Cannot use the same name of another existing object in your schema Must not be a SQL reserved word Following the above guidelines, ”EMP85” can be a valid table name.But 85EMP is not.Similarly, UPDATE cannot be a chosen as a table name since it a SQL reserved keyword. CREATE TABLE statement The CREATE TABLE is a DDL statement which is used to create tables in the database.The table gets created as soon as the CREATE TABLE script is executed and is ready to hold the data onwards.The user must have the CREATE TABLE system privilege to create the table in its own schema.But to create a table in any user”s schema, user must have CREATE ANY TABLE schema. Here is the syntax of a basic CREATE TABLE statement.There may be many additional clauses to explicitly provide the storage specifications or segment values. CREATE TABLE [schema.]table ( { column datatype [DEFAULT expr] [column_constraint] … | table_constraint} [, { column datatype [DEFAULT expr] [column_constraint] … | table_constraint} ]…) [AS subquery] In the above syntax, DEFAULT specifies default value which can be used during INSERT statement if the column is ignored. It cannot contain references to other table columns or pseudo columns (CURRVAL, NEXTVAL, LEVEL, and ROWNUM) except SYSDATE and USER, or date constants that are not fully specified. Constraints are the rules defined optionally at the column level or table level (covered later in this chapter).These rules are checked during any data action (Insert, update) on the table and raise error to abort the action upon its violation. For example, the CREATE TABLE statement below creates a table EMP_TEST. Note the column specifications, data type and precision. CREATE TABLE SCOTT.EMP_TEST (EMPID NUMBER, ENAME VARCHAR2(100), DEPARTMENT_ID NUMBER, SALARY NUMBER, JOB_ID VARCHAR2(3), HIREDATE DATE, COMM NUMBER); A user can refer the tables from other user”s schema by prefixing the username or schema with the table name.For example, a user GUEST wishes to query the employee name and salary from the EMP_TEST table which is owned by SCOTT. He can issue the below query – SELECT ENAME, SALARY, FROM GUEST.EMP_TEST; A column can hold a default value during the time of table creation.It helps to restrict the NULL values getting into the column. Default value can be deduced from either a literal, expression or SQL function which must return a compatible data type to the column. In the below CREATE TABLE statement, note that the LOCATION_ID column has default value 100. CREATE TABLE SCOTT.DEPARTMENT (DEPARTMENT_ID NUMBER, DNAME VARCHAR2 (100), LOCATION_ID NUMBER DEFAULT 100); CTAS – Create table using subquery A table can be created from an existing table in the database using a subquery option.It copies the table structure as well as the data from the table. Data can also be copied based on conditions.The column data type definitions including the explicitly imposed NOT NULL constraints are copied into the new table. The below CTAS script creates a new table EMP_BACKUP. Employee data of department 20 gets copied into the new table. CREATE TABLE EMP_BACKUP AS SELECT * FROM EMP_TEST WHERE department_id=20; Data types Data types are used to specify the basic behavior of a column in the table.On a broader basis,column behavior can either belong to number,character or a date family.There are multiple other subtypes which belong to these families. Number data type The NUMBER datatype encompasses both integer,fixed-point,and floating-point numeric values.Early versions of Oracle defined different datatypes for each of these different types of numbers,but now the NUMBER datatype serves all of these purposes.Choose the NUMBER datatype when a column must store numerical data that can be used in mathematical calculations.Occasionally,the NUMBER datatype is used to store identification numbers where those numbers are generated by the DBMS as sequential numbers. NUMBER (p, s), where p is the precision up to 38 digits and s is the scale (number of digits to the right of the decimal point).The scale can range between -84 to 127. NUMBER (p),is a fixed-point number with a scale of zero and a precision of p. FLOAT [(p)],where p is the binary precision that can range from 1 to 126. If p is not specified the default value is binary 126. Date data type For each DATE data type, Century, Year, Month, Day, Hour, Minute, Second are stored in database. Every database system has a default date format that is defined by the initialization parameter NLS_DATE_FORMAT. This parameter is usually set to DD-MON-YY.If you do not specify a time, the default time is 12:00:00 a.m. Character data type Oracle supports three predefined character datatypes including CHAR, VARCHAR, VARCHAR2, and LONG.VARCHAR and VARCHAR2 are actually synonymous, and Oracle recommends using VARCHAR2 instead of VARCHAR.Use the CHAR

SQL – The SQL SELECT Statement ”; Previous Next Retrieving data using the SQL Select Statement SQL is a comprehensive database language. SQL, pronounced Sequel or simply S-Q-L, is a computer programming language used for querying relational databases following a nonprocedural approach. When you extract information from a database using SQL, this is termed querying the database. A relational database is implemented through the use of a Relational Database Management System (RDBMS). An RDBMS performs all the basic functions of the DBMS software mentioned above along with a multitude of other functions that make the relational model easier to understand and to implement. RDBMS users manipulate data through the use of a special data manipulation language. Database structures are defined through the use of a data definition language. The commands that system users execute in order to store and retrieve data can be entered at a terminal with an RDBMS interface by typing the commands, or entered through use of some type of graphical interface. The DBMS then processes the commands. Capabilities of the SELECT Statement Data retrieval from data base is done through appropriate and efficient use of SQL. Three concepts from relational theory encompass the capability of the SELECT statement: projection, selection, and joining. Projection: A project operation selects only certain columns (fields) from a table. The result table has a subset of the available columns and can include anything from a single column to all available columns. Selection: A select operation selects a subset of rows (records) in a table (relation) that satisfy a selection condition. The ability to select rows from out of complete result set is called Selection. It involves conditional filtering and data staging. The subset can range from no rows, if none of the rows satisfy the selection condition, to all rows in a table. Joining: A join operation combines data from two or more tables based on one or more common column values. A join operation enables an information system user to process the relationships that exist between tables. The join operation is very powerful because it allows system users to investigate relationships among data elements that might not be anticipated at the time that a database is designed. Consider the above table structures. Fetching first_name name, department_id and salary for a single employee from EMPLOYEES table is Projection. Fetching employee details whose salary is less than 5000, from EMPLOYEES table is Selection. Fetching employee”s first name, department name by joining EMPLOYEES and DEPARTMENTS is Joining. Basic SELECT statement The basic syntax for a SELECT statement is presented below. SELECT [DISTINCT | ALL] {* | select_list} FROM {table_name [alias] | view_name} [{table_name [alias] | view_name}]… [WHERE condition] [GROUP BY condition_list] [HAVING condition] [ORDER BY {column_name | column_# [ ASC | DESC ] } … The SELECT clause is mandatory and carries out the relational project operation. The FROM clause is also mandatory. It identifies one or more tables and/or views from which to retrieve the column data displayed in a result table. The WHERE clause is optional and carries out the relational select operation. It specifies which rows are to be selected. The GROUP BY clause is optional. It organizes data into groups by one or more column names listed in the SELECT clause. The optional HAVING clause sets conditions regarding which groups to include in a result table. The groups are specified by the GROUP BY clause. The ORDER BY clause is optional. It sorts query results by one or more columns in ascending or descending order. Arithmetic expressions and NULL values in the SELECT statement An arithmetic expression can be created using the column names, operators and constant values to embed an expression in a SELECT statement. The operator applicable to a column depends on column”s data type. For example, arithmetic operators will not fit for character literal values. For example, SELECT employee_id, sal * 12 ANNUAL_SAL FROM employees; The above query contains the arithmetic expression (sal * 12) to calculate annual salary of each employee. Arithmetic operators Operators act upon the columns (known as operands) to result into a different result. In case of multiple operators in an expression, the order of evaulation is decided by the operator precedence. Here are the elementary rules of precedence – Multiplication and division occur before Addition and Subtraction. Operators on the same priority are evaluated from left to right. Use paretheses to override the default behavior of the operators. Below table shows the precedence of the operators, in such cases. Precedence Level Operator Symbol Operation Description Operator Precedence Addition + Lowest Subtraction – Lowest Multiplication * Medium Division / Medium Brackets ( ) Highest Examine the below queries (a), (b), and (c) SQL> SELECT 2*35 FROM DUAL; SQL> SELECT salary + 1500 FROM employees; SQL> SELECT first_name, salary, salary + (commission_pct* salary) FROM employees; Query (a) multiplies two numbers, while (b) shows addition of $1500 to salaries of all employees. Query (c) shows the addition of commission component to employee”s salary. As per the precedence, first commission would be calculated on the salary, and then added to the salary. Column Alias An alias is used to rename a column or an expression during display. The alias to a column or an expression appears as the heading in the output of a query. It is useful in providing a meaningful heading to long expressions in the SELECT query. By default, the alias appears in uppercase in the query output without spaces. To override this behavior, alias must be enclosed within double quotes to preserve the case and spaces in the alias name. SELECT price * 2 as DOUBLE_PRICE, price * 10 “Double Price” FROM products; DOUBLE_PRICE Double Price ———— ———— 39.9 39.9 60 60 51.98 51.98 Concatenation operators Concatenation operator can be used to join two string values or expressions in a SELECT query. The double vertical bar symbol is used as string concatenation operator. It is applicable only for character and string column values resulting into a new

SQL – Using Single-Row Functions Questions ”; Previous Next 1. What will be the outcome of the following query? SELECT ROUND(144.23,-1) FROM dual; 140 144 150 100 Answer: A. The ROUND function will round off the value 144.23 according to the specified precision -1 and returns 140. Examine the structure of the EMPLOYEES table as given and answer the questions 2 and 3 that follow. SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) 2. You are currently located in New Jersey and have connected to a remote database in San Diego. You issue the following command. SELECT ROUND (sysdate-hire_date,0) FROM employees WHERE (sysdate-hire_date)/180 = 2; What is the outcome of this query? An error because the ROUND function cannot be used with Date arguments. An error because the WHERE condition expression is invalid. Number of days since the employee was hired based on the current San Diego date and time. Number of days since the employee was hired based on the current New Jersey date and time. Answer: C. The SYSDATE function will take the current time of the database which it is connecting to remotely. You must perform basic arithmetic operation to adjust the time zone. 3. You need to display the names of the employees who have the letter ”s” in their first name and the letter ”t” at the second position in their last name. Which query would give the required output? SELECT first_name, last_name FROM employees WHERE INSTR(first_name,”s”) 0 AND SUBSTR(last_name,2,1) = ”t”; SELECT first_name, last_name FROM employees WHERE INSTR(first_name,”s”) ”” AND SUBSTR(last_name,2,1) = ”t”; SELECT first_name, last_name FROM employees WHERE INSTR(first_name,”e”) IS NOT NULL AND SUBSTR(last_name,2,1) = ”t”; SELECT first_name, last_name FROM employees WHERE INSTR(first_name,”e”) 0 AND SUBSTR(last_name,LENGTH(first_name),1) = ”t”; Answer: A. The INSTR function returns the position of a given character in the required string. The SUBSTR function returns set of characters from the string from a given starting and end position. 4. Which of the following statements is true regarding the COUNT function? COUNT (*) counts duplicate values and NULL values in columns of any data type. COUNT function cannot work with DATE datatypes. COUNT (DISTINCT job_id) returns the number of rows excluding rows containing duplicates and NULL values in the job_id column. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause. Answer: A. The COUNT(*) function returns the number of rows in a table that satisfy the criteria of the SELECT statement, including duplicate rows and rows containing null values in any of the columns. If a WHERE clause is included in the SELECT statement, COUNT(*) returns the number of rows that satisfy the condition in the WHERE clause. In contrast, COUNT(expr) returns the number of non-null values that are in the column identified by expr. COUNT(DISTINCT expr) returns the number of unique, non-null values that are in the column identified by expr. 5. Which of the following commands is used to count the number of rows and non-NULL values in Oracle database? NOT NULL INSTR SUBSTR COUNT Answer: D. The COUNT (ALL column_name) is used to count number of rows excluding NULLs. Similarly, COUNT(*) is used to count the column values including NULLs. 6. What will be the outcome of the query given below? SELECT 100+NULL+999 FROM dual; 100 999 NULL 1099 Answer: C. Any arithmetic operation with NULL results in a NULL. 7. Which of the following statements are true regarding the single row functions? They accept only a single argument. They can be nested only to two levels. Arguments can only be column values or constants. They can return a data type value different from the one that is referenced. Answer: D. Single row functions can take more than one argument and the return type can be different from the data type of the inputs. 8. Which of the below queries will format a value 1680 as $16,80.00? SELECT TO_CHAR(1680.00,”$99G99D99”) FROM dual; SELECT TO_CHAR(1680.00,”$9,999V99”) FROM dual; SELECT TO_CHAR(1680.00,”$9,999D99”) FROM dual; SELECT TO_CHAR(1680.00,”$99G999D99”) FROM dual; Answer: A, D. The format model $99G999D99 formats given number into numeric, group separator, and decimals. Other format elements can be leading zeroes, decimal position, comma position, local currency, scientific notation, and sign. 9. Determine the output of the below query. SELECT RPAD(ROUND(”78945.45”),10,”*”) FROM dual; 78945***** **78945.45 The function RPAD cannot be nested with other functions 78945.45**** Answer: A. The LPAD(string, num, char) and RPAD(string, num, char) functions add a character to the left or right of a given string until it reaches the specified length (num) after padding. The ROUND function rounds the value 78945.45 to 78945 and then pads it with ”*” until length of 10 is reached. 10. Which of the following commands allows you to substitute a value whenever a NULL or non-NULL value is encountered in an SQL query? NVL NVLIF NVL2 LNNVL Answer: C. The NVL2 function takes minimum three arguments. The NVL2 function checks the first expression. If it is not null, the NVL2 function returns the second argument. If the first argument is null, the third argument is returned. 11. Which of the following type of single-row functions cannot be incorporated in Oracle DB? Character Numeric Conversion None of the above Answer: D. The types of single-row functions like character, numeric, date, conversion and miscellaneous as well as programmer-written can be incorporated in Oracle DB. 12. Out of the below clauses, where can the single-row functions be used? SELECT WHERE ORDER BY All of the above Answer: D. Single row function can be used in SELECT statement, WHERE clause and ORDER BY clause. 13. What is true regarding the NVL function in Oracle DB? The syntax of NVL is NVL (exp1, exp2) where exp1 and exp2 are expressions. NVL (exp1, exp2) will return the value of exp2 if the expression exp1 is NULL.

SQL – Using the Group Functions Questions ”; Previous Next 1. Which of the following is NOT a GROUP BY function? MAX MIN NVL AVG Answer: C. NVL is a general function used to provide alternate value to the NULL values. The functions MAX, MIN and AVG can be used as GROUP BY functions. 2. Which of the following functions can be used without GROUP BY clause in SELECT query? COUNT MAX MIN AVG Answer: A, B, C, D. All the listed group functions can be used in a query provided no other columns are selected in the SELECT query. 3. Which of the following SELECT query returns the department number with maximum salary compensated to an employee? (Consider the table structure as given) SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) SELECT department_id , max(salary ) FROM employees ; SELECT department_id , max(salary ) FROM employees GROUP BY department_id ; SELECT max(salary ) FROM employees GROUP BY department_id ; SELECT max(salary ) FROM employees ; Answer: B. The MAX function can be used to return the maximum salary in a department where each group is formed by a department. 4. Which of the following statements are true about the COUNT function? The COUNT function counts the number of rows The COUNT(*) function counts the number of rows with duplicates and NULL values The COUNT(DISTINCT) function counts the number of distinct rows COUNT(*) is equivalent to COUNT(ALL) Answer: B. The COUNT(*) counts the number of rows including duplicates and NULLs. Use DISTINCT and ALL keyword to restrict duplicate and NULL values. 5. What are the appropriate data types accepted by GROUP BY functions? Nested Tables NUMBER CLOB DATE Answer: B. The data types for the functions with an argument may be CHAR, VARCHAR2, NUMBER or DATE. 6. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query. SELECT COUNT (*) FROM t_count; 12 6 9 Throws exception because COUNT function doesn”t works with NULL values Answer: A. The COUNT(*) counts the number of rows including duplicates and NULLs. Use DISTINCT and ALL keyword to restrict duplicate and NULL values. 7. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query. SELECT COUNT (num) FROM t_count; 12 6 9 Throws exception because COUNT function doesn”t works with NULL values Answer: C. COUNT (column) ignores the NULL values but counts the duplicates. 8. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query. SELECT COUNT (ALL num) FROM t_count; 12 6 9 Throws exception because COUNT function doesn”t works with NULL values Answer: C. COUNT(ALL column) ignores the NULL values but counts the duplicates. 9. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query. SELECT COUNT (DISTINCT num) FROM t_count; 12 6 9 Throws exception because COUNT function doesn”t works with NULL values Answer: B. COUNT (DISTINCT column) counts the distinct not null values. 10. What happens when the below query is executed in SQL* Plus? SELECT COUNT() FROM dual; Executes successfully and returns no output Executes successfully and returns output as ”1” Throws exception “ORA-00909: invalid number of arguments” Throws exception “ORA-00904: “COUNT”: invalid identifier” because COUNT function doesn”t works with DUAL table Answer: C. COUNT function requires minimum one argument which can be either the column with [ALL | DISTINCT] modifier or ”*”. 11. Here are few statements about VARIANCE function in SQL. i. The function accepts multiple numeric inputs and returns variance of all the values ii. The function accepts a number column and returns variance of all column values including NULLs iii. The function accepts a number column and returns variance of all column values excluding NULLs Chose the correct combination from the below options. i and iii i and ii ii iii Answer: C. The VARIANCE function accepts single numeric argument as the column name and returns variance of all the column values considering NULLs. 12. Which of the following is NOT a GROUP BY extensions in SQL? GROUP BY GROUPING SETS CUBE ROLLUP Answer: A. GROUPING SETS operations can be used to perform multiple GROUP BY aggregations with a single query. 13. Select the correct statements about the below query. Consider the table structure as given. SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) SELECT department_id , SUM(salary ) FROM employees GROUP BY department_id ; SUM is a group by function because it processes group of employees working in a department SUM is an aggregate function because it produces one result per group of data SUM is a single row function because it returns single value for a group i.e. department SUM is a group by extension function because it uses GROUP BY clause to logically group the departments Answer: A. SUM is a group function which calculates the sum of salaries of a group of employees working in a department. 14. Which clause is used to filter the query output based on aggregated results using a group by function? WHERE LIMIT GROUP WHERE HAVING Answer: D. HAVING Clause is used for restricting group results. You use the HAVING clause to specify the groups that are to be displayed, thus further restricting the groups on the basis of aggregate information. The HAVING clause can precede the GROUP

SQL – Get Data from Multiple Tables ”; Previous Next Displaying Data from Multiple Tables The related tables of a large database are linked through the use of foreign and primary keys or what are often referred to as common columns. The ability to join tables will enable you to add more meaning to the result table that is produced. For ”n” number tables to be joined in a query, minimum (n-1) join conditions are necessary. Based on the join conditions, Oracle combines the matching pair of rows and displays the one which satisfies the join condition. Joins are classified as below Natural join (also known as an equijoin or a simple join) – Creates a join by using a commonly named and defined column. Non-equality join – Joins tables when there are no equivalent rows in the tables to be joined-for example, to match values in one column of a table with a range of values in another table. Self-join – Joins a table to itself. Outer join – Includes records of a table in output when there”s no matching record in the other table. Cartesian join (also known as a Cartesian product or cross join) – Replicates each row from the first table with every row from the second table.Creates a join between tables by displaying every possible record combination. Natural Join The NATURAL keyword can simplify the syntax of an equijoin.A NATURAL JOIN is possible whenever two (or more) tables have columns with the same name,and the columns are join compatible, i.e., the columns have a shared domain of values.The join operation joins rows from the tables that have equal column values for the same named columns. Consider the one-to-many relationship between the DEPARTMENTS and EMPLOYEES tables.Each table has a column named DEPARTMENT_ID.This column is the primary key of the DEPARTMENTS table and a foreign key of the EMPLOYEES table. SELECT E.first_name NAME,D.department_name DNAME FROM employees E NATURAL JOIN departments D; FIRST_NAME DNAME ———- —— MILLER DEPT 1 JOHN DEPT 1 MARTIN DEPT 2 EDWIN DEPT 2 The below SELECT query joins the two tables by explicitly specifying the join condition with the ON keyword. SELECT E.first_name NAME,D.department_name DNAME FROM employees E JOIN departments D ON (E.department_id = D.department_id); There are some limitations regarding the NATURAL JOIN.You cannot specify a LOB column with a NATURAL JOIN.Also, columns involved in the join cannot be qualified by a table name or alias. USING Clause Using Natural joins, Oracle implicitly identify columns to form the basis of join. Many situations require explicit declaration of join conditions. In such cases, we use USING clause to specify the joining criteria. Since, USING clause joins the tables based on equality of columns, it is also known as Equijoin. They are also known as Inner joins or simple joins. Syntax: SELECT <column list> FROM TABLE1 JOIN TABLE2 USING (column name) Consider the below SELECT query, EMPLOYEES table and DEPARTMENTS table are joined using the common column DEPARTMENT_ID. SELECT E.first_name NAME,D.department_name DNAME FROM employees E JOIN departments D USING (department_id); Self Join A SELF-JOIN operation produces a result table when the relationship of interest exists among rows that are stored within a single table. In other words, when a table is joined to itself, the join is known as Self Join. Consider EMPLOYEES table,which contains employee and their reporting managers.To find manager”s name for an employee would require a join on the EMP table itself. This is a typical candidate for Self Join. SELECT e1.FirstName Manager,e2.FirstName Employee FROM employees e1 JOIN employees e2 ON (e1.employee_id = e2.manager_id) ORDER BY e2.manager_id DESC; Non Equijoins A non-equality join is used when the related columns can”t be joined with an equal sign-meaning there are no equivalent rows in the tables to be joined.A non-equality join enables you to store a range”s minimum value in one column of a record and the maximum value in another column. So instead of finding a column-tocolumn match, you can use a non-equality join to determine whether the item being shipped falls between minimum and maximum ranges in the columns.If the join does find a matching range for the item, the corresponding shipping fee can be returned in the results. As with the traditional method of equality joins, a non-equality join can be performed in a WHERE clause. In addition, the JOIN keyword can be used with the ON clause to specify relevant columns for the join. SELECT E.first_name, J.job_hisal, J.job_losal, E.salary FROM employees E JOIN job_sal J ON (E.salary BETWEEN J.job_losal AND J.job_losal); We can make use all comparison parameter discussed earlier like equality and inequality operators, BETWEEN, IS NULL, IS NOT NULL, and RELATIONAL. Outer Joins An Outer Join is used to identify situations where rows in one table do not match rows in a second table, even though the two tables are related. There are three types of outer joins: the LEFT, RIGHT, and FULL OUTER JOIN. They all begin with an INNER JOIN, and then they add back some of the rows that have been dropped. A LEFT OUTER JOIN adds back all the rows that are dropped from the first (left) table in the join condition, and output columns from the second (right) table are set to NULL. A RIGHT OUTER JOIN adds back all the rows that are dropped from the second (right) table in the join condition, and output columns from the first (left) table are set to NULL. The FULL OUTER JOIN adds back all the rows that are dropped from both the tables. Right Outer Join A RIGHT OUTER JOIN adds back all the rows that are dropped from the second (right) table in the join condition, and output columns from the first (left) table are set to NULL. Note the below query lists the employees and their corresponding departments. Also no employee has been assigned to department 30. SELECT E.first_name, E.salary, D.department_id FROM employees E, departments D WHERE E.DEPARTMENT_ID (+) = D.DEPARTMENT_ID; FIRST_NAME SALARY DEPARTMENT_ID ———- ———- ———- JOHN

SQL Fundamentals Certification Resources Job Search Discussion (Oracle Certified Oracle Database 11g Administrator) Oracle”s Oracle Database 11g: SQL Fundamentals exam is part of the Oracle Certified Oracle Database 11g Administrator track, combining training, experience, and testing to endorse candidates with a strong foundation and expertise in the industry’s most advanced database management system. This certification is to put you on the short list for winning Oracle SQL-Based projects. An Oracle Technical Certification is a valuable, industry-recognized credential that signifies a proven level of knowledge and skill. Oracle Database: SQL Fundamentals I – only available as part of Oracle Database: Introduction to SQL (Bundle) in some regions and Exam 051 has been validated against Oracle Database 10g and 11g Release 2 version 11.2.0.1.0. This exam can be taken online as a non-proctored exam, or in a test center as a proctored exam. Exam Number 1Z0-051 Exam Name Oracle Database 11g: SQL Fundamentals Certification Track Oracle Certified Oracle Database 11g Administrator Exam Product Version SQL and PL/SQL Exam Fees US$ 125 Number of Questions 70 Duration 120 Minutes Passing Score 60% Questions format Multiple Choice Audience This certification is primarily good for developer, application developers, PL/SQL developer, forms developer, system analysts, business analysts and data warehouse administrator. It is also recommended to the entry-level and junior programmers wishing to start and/or continue down the path of using SQL technologies and same time software developers and technical leads wishing to solidify their SQL-related skill sets. Print Page Previous Next Advertisements ”;

SQL – Using the Set Operators ”; Previous Next Set operators are used to join the results of two (or more) SELECT statements.The SET operators available in Oracle 11g are UNION,UNION ALL,INTERSECT,and MINUS. The UNION set operator returns the combined results of the two SELECT statements.Essentially,it removes duplicates from the results i.e. only one row will be listed for each duplicated result.To counter this behavior,use the UNION ALL set operator which retains the duplicates in the final result.INTERSECT lists only records that are common to both the SELECT queries; the MINUS set operator removes the second query”s results from the output if they are also found in the first query”s results. INTERSECT and MINUS set operations produce unduplicated results. All the SET operators share the same degree of precedence among them.Instead,during query execution, Oracle starts evaluation from left to right or from top to bottom.If explicitly parentheses are used, then the order may differ as parentheses would be given priority over dangling operators. Points to remember – Same number of columns must be selected by all participating SELECT statements.Column names used in the display are taken from the first query. Data types of the column list must be compatible/implicitly convertible by oracle. Oracle will not perform implicit type conversion if corresponding columns in the component queries belong to different data type groups.For example, if a column in the first component query is of data type DATE, and the corresponding column in the second component query is of data type CHAR,Oracle will not perform implicit conversion, but raise ORA-01790 error. Positional ordering must be used to sort the result set. Individual result set ordering is not allowed with Set operators. ORDER BY can appear once at the end of the query. For example, UNION and INTERSECT operators are commutative, i.e. the order of queries is not important; it doesn”t change the final result. Performance wise, UNION ALL shows better performance as compared to UNION because resources are not wasted in filtering duplicates and sorting the result set. Set operators can be the part of sub queries. Set operators can”t be used in SELECT statements containing TABLE collection expressions. The LONG, BLOB, CLOB, BFILE, VARRAY,or nested table are not permitted for use in Set operators.For update clause is not allowed with the set operators. UNION When multiple SELECT queries are joined using UNION operator, Oracle displays the combined result from all the compounded SELECT queries,after removing all duplicates and in sorted order (ascending by default), without ignoring the NULL values. Consider the below five queries joined using UNION operator.The final combined result set contains value from all the SQLs. Note the duplication removal and sorting of data. SELECT 1 NUM FROM DUAL UNION SELECT 5 FROM DUAL UNION SELECT 3 FROM DUAL UNION SELECT 6 FROM DUAL UNION SELECT 3 FROM DUAL; NUM ——- 1 3 5 6 To be noted, the columns selected in the SELECT queries must be of compatible data type. Oracle throws an error message when the rule is violated. SELECT TO_DATE(”12-OCT-03”) FROM DUAL UNION SELECT ”13-OCT-03” FROM DUAL; SELECT TO_DATE(”12-OCT-03”) FROM DUAL * ERROR at line 1: ORA-01790: expression must have same datatype as corresponding expression UNION ALL UNION and UNION ALL are similar in their functioning with a slight difference. But UNION ALL gives the result set without removing duplication and sorting the data. For example,in above query UNION is replaced by UNION ALL to see the effect. Consider the query demonstrated in UNION section. Note the difference in the output which is generated without sorting and deduplication. SELECT 1 NUM FROM DUAL UNION ALL SELECT 5 FROM DUAL UNION ALL SELECT 3 FROM DUAL UNION ALL SELECT 6 FROM DUAL UNION ALL SELECT 3 FROM DUAL; NUM ——- 1 5 3 6 3 INTERSECT Using INTERSECT operator, Oracle displays the common rows from both the SELECT statements, with no duplicates and data arranged in sorted order (ascending by default). For example,the below SELECT query retrieves the salary which are common in department 10 and 20.As per ISO SQL Standards, INTERSECT is above others in precedence of evaluation of set operators but this is not still incorporated by Oracle. SELECT SALARY FROM employees WHERE DEPARTMENT_ID = 10 INTRESECT SELECT SALARY FROM employees WHERE DEPARTMENT_ID = 20 SALARY ——— 1500 1200 2000 MINUS Minus operator displays the rows which are present in the first query but absent in the second query, with no duplicates and data arranged in ascending order by default. SELECT JOB_ID FROM employees WHERE DEPARTMENT_ID = 10 MINUS SELECT JOB_ID FROM employees WHERE DEPARTMENT_ID = 20; JOB_ID ————- HR FIN ADMIN Matching the SELECT statement There may be the scenarios where the compound SELECT statements may have different count and data type of selected columns. Therefore, to match the column list explicitly, NULL columns are inserted at the missing positions so as match the count and data type of selected columns in each SELECT statement. For number columns, zero can also be substituted to match the type of the columns selected in the query. In the below query, the data type of employee name (varchar2) and location id (number) do not match. Therefore, execution of the below query would raise error due to compatibility issue. SELECT DEPARTMENT_ID “Dept”, first_name “Employee” FROM employees UNION SELECT DEPARTMENT_ID, LOCATION_ID FROM departments; ERROR at line 1: ORA-01790: expression must have same datatype as corresponding expression Explicitly, columns can be matched by substituting NULL for location id and Employee name. SELECT DEPARTMENT_ID “Dept”, first_name “Employee”, NULL “Location” FROM employees UNION SELECT DEPARTMENT_ID, NULL “Employee”, LOCATION_ID FROM departments; Using ORDER BY clause in SET operations The ORDER BY clause can appear only once at the end of the query containing compound SELECT statements.It implies that individual SELECT statements cannot have ORDER BY clause. Additionally, the sorting can be based on the columns which appear in the first SELECT query only. For this reason, it is recommended to sort the compound query using column positions. The compund