This test is used to test your skills in joins , functions
SELECT last_name, department_name FROM employees e LEFT OUTER JOIN departments d ON (e. department _ id = d. department _id);
SELECT last_name, department_name FROM employees JOIN departments(+);
SELECT last_name, department_name ON (e. department_ id = d. departments_id); FROM employees(+) e JOIN departments d
SELECT last_name, department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);
SELECT last_name, department_name FROM employees(+) , departments ON (e. department _ id = d. department _id);
SELECT emp_name, department_name, city FROM employees e JOIN departments d USING (department_id) JOIN locations 1 USING (location_id) WHERE salary > 10000;
SELECT emp_name, department_name, city FROM employees e, departments d, locations 1 JOIN ON (e.department_id = d.department id) AND (d.location_id =1.location_id) AND salary > 10000;
SELECT emp_name, department_name, city FROM employees e, departments d, locations 1 WHERE salary > 10000;
. SELECT emp_name, department_name, city FROM employees e, departments d, locations 1 WHERE e.department_id = d.department_id AND d.location_id = 1.location_id AND salary > 10000
SELECT emp_name, department_name, city FROM employees e NATURAL JOIN departments, locations WHERE salary > 10000;
SELECT employee_id "Emp_id", emp_name "Employee",salary,employee_id "Mgr_id", emp_name "Manager"FROM employees WHERE salary > 4000;
SELECT e.employee_id "Emp_id", e.emp_name "Employee",e.salary,m.employee_id "Mgr_id", m.emp_name "Manager"FROM employees e, employees m WHERE e.mgr_id = m.mgr_id AND e.salary > 4000;
SELECT e.employee_id "Emp_id", e.emp_name "Employee",e.salary,m.employee_id "Mgr_id", m.emp_name "Manager"FROM employees e, employees m WHERE e.mgr_id = m.employee_id AND e.salary > 4000;
SELECT e.employee_id "Emp_id", e.emp_name "Employee",e.salary,m.mgr_id "Mgr_id", m.emp_name "manager" FROM employees e, employees m WHERE e.mgr_id = m.employee_id AND e.salary > 4000;
SELECT e.employee_id "Emp_id", e.emp_name "Employee",e.salary,m.mgr_id "Mgr_id", m.emp_name "Manager"FROM employees e, employees m WHERE e.employee_id = m.employee_id AND e.salary > 4000;
You want all matched and unmatched data from only one table
You want all unmatched data from one table.
You want all matched data from both tables.
You want all unmatched data from both tables.
One of the tables has more data than the other.
ORDER BY SALARY > 5000
GROUP BY SALARY > 5000
HAVING SALARY > 5000
WHERE SALARY > 5000
To find the tax percentage for each of the employees.
To list the name, job id, and manager name for all the employees.
To find the name, salary, and department name of employees who are not working with Smith
To find the number of employees working for the Administrative department and earning less then 4000
To display name, salary, manager ID, and department name of all the employees, even if the employees do not have a department ID assigned.
TRIM
REPLACE
TRUNC
TO_DATE
MOD
You can join a maximum of two tables through an equijoin.
You can join a maximum of two columns through an equijoin.
You specify an equijoin condition in the SELECT or FROM clauses of a SELECT statement.
. To join two tables through an equijoin, the columns in the join condition must be primary key and foreign key columns
You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum of n-1 join conditions
WHERE
HAVING
RESTRICT
GROUP BY
ORDER BY
You cannot use IN operator in a condition that involves an outerjoin
You use (+) on both sides of the WHERE condition to perform an outerjoin
. You cannot link a condition that is involved in an outerjoin to another condition by using the OR operator
You use an outerjoin to see only the rows that do not meet the join condition.
In the WHERE condition, you use (+) following the name of the column in the table without matching rows, to perform an outerjoin.
A WHERE clause can be used to restrict both rows and groups.
A WHERE clause can be used to restrict rows only.
. A HAVING clause can be used to restrict both rows and groups.
A HAVING clause can be used to restrict groups only.
. A HAVING clause CANNOT be used in subqueries.
46 and 45
46 and 45.93
50 and 45.93
50 and 45.9
45 and 45.93
SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, 'a') FROM EMPLOYEES WHERE SUBSTR (ENAME, -1,1) = 'n';
SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, ,-1,1) FROM EMPLOYEES WHERE SUBSTR (ENAME, -1,1) = 'n';
SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR (ENAME, 1,1) = 'n';
SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR (ENAME, -1,1) = 'n';
SELECT ADD_MONTHS(MAX(hire_Date), 6) FROM EMP;
SELECT ROUND(hire_date) FROM EMP;
SELECT sysdate-hire_date FROM EMP;
SELECT TO_NUMBER(hire_date + 7) FROM EMP;
CREATE TABLE EMP9$# (empid number(2));
CREATE TABLE EMP*123 (empid number(2));
CREATE TABLE PACKAGE (packid number(2));
CREATE TABLE 1EMP_TEST (empid number(2));
0.34
1.345
1.35
1.3445
1.34
INTERSECT
MINUS
UNION
UNION ALL
The INTERSECT, because INTERSECT has higher precedence than MINUS
The MINUS, because MINUS has a higher precedence than INTERSECT.
The precedence is determined by the order in which they are specified.
It is not possible for a compound query to include both MINUS and INTERSECT
Because the columns EMPNO and EMP_ID have different names
Because the columns EMP.HIRED and EX_EMP.HIRED are different data types
Because there are two columns in the first query and three columns in the second query
For all the reasons above
The query will succeed.
The rows from each table will be grouped and within each group will be sorted on ENAME
The rows from each table will be grouped but not sorted.
The rows will not be grouped but will all be sorted on ENAME.
The rows will be neither grouped nor sorted.
Wait!
Here's an interesting quiz for you.