Tag: databases

SELECT employee_id, department_id, department_name, Salary FROM employees WHERE department_id IN (SELECT department_id FROM departments);

SELECT employee_id, department_id, department_name, salary FROM employees NATURAL JOIN departments;

SELECT employee_id, d.department_id, department_name, salary FROM employees e JOIN departments d ON e.department_id = d.department_id;

SELECT employee_id, department_id, department_name, salary FROM employees JOIN departments USING (e.department_id, d.department_id);

The indexed column is declared as NOT NULL.

The indexed columns are used in the FROM clause.

The indexed columns are part of an expression.

The indexed column contains a wide range of values.

To simplify the process of creating new users using the CREATE USER xxx IDENTIFIED by yyy statement

to grant a group of related privileges to a user

when the number of people using the database is very high

to simplify the process of granting and revoking privileges

to simplify profile maintenance for a user who is constantly traveling

You use a NEXTVAL pseudo column to look at the next possible value that would be generated from a sequence, without actually retrieving the value.

You use a CURRVAL pseudo column to look at the current value just generated from a sequence, without affecting the further values to be generated from the sequence.

You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value from the sequence.

You use a CURRVAL pseudo column to generate a value from a sequence that would be used for a specified database column.

If a sequence starting from a value 100 and incremented by 1 is used by more than one application, then all of these applications could have a value of 105 assigned to their column whose value is being generated by the sequence.

You use a REUSE clause when creating a sequence to restart the sequence once it generates the maximum value defined for the sequence.

=

LIKE

BETWEEN

NOT IN

IS

<>

ALTER VIEW emp_dept_vu (ADD manager_id NUMBER);

MODIFY VIEW emp_dept_vu (ADD manager_id NUMBER);

ALTER VIEW emp_dept_vu AS SELECT employee_id,employee_name, department_name,manager_id FROM employees e,departments d WHERE e.department_id = d.department_id;

MODIFY VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id;

CREATE OR REPLACE VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id=d.department_id;

UPDATE new_employees SET name = (SELECT last_name|| first_ name FROM employees WHERE employee_id =180) WHERE employee_id =180;

UPDATE new_employees SET name = (SELECT last_name ||first_name FROM employees ) WHERE employee_ id =180;

UPDATE new_employees SET name = (SELECT last_name || first_name FROM employees WHERE employee_id =180) WHERE employee_id =(SELECT employee_id FROM new employees);

UPDATE new_employees SET name = (SELECT last name || first_name FROM employees WHERE employee_id = (SELECT employee_id FROM new_employees)) WHERE employee_id =180;

"&"

ACCEPT

PROMPT

"&&"

SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department_id);

SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);

SELECT distinct department_id FROM employees WHERE salary > ANY(SELECT AVG(salary) FROM employees GROUP BY department_id);

SELECT department_id FROM employees WHERE salary > ALL(SELECT AVG(salary) FROM employees GROUP BY department_id);

SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY));

SELECT ord_id, cust_id, ord_total FROM orders, customers WHERE cust_name='Martin' AND ord_date IN ('18-JUL-2000','21-JUL-2000');

SELECT ord_id, cust_id, ord_total FROM orders WHERE ord_date IN (SELECT ord_date FROM orders WHERE cust_id = (SELECT cust_id FROM customers WHERE cust_name = 'Martin'));

SELECT ord_id, cust_id, ord_total FROM orders WHERE ord_date IN (SELECT ord_date FROM orders, customers WHERE cust_name = 'Martin');

SELECT ord_ id, cust_id, ord_total FROM orders WHERE cust_id IN (SELECT cust_id FROM customers WHERE cust name = 'Martin');