Oracle Database SQL - Exam 1Z0-071
SELECT
A DML statement that retrieves zero or more rows of data from one or more database tables or database views. Example: SELECT * FROM employees;
SEQUENCE
A database object from which multiple users may generate unique integers. Example: CREATE SEQUENCE seq_order_id START WITH 1 INCREMENT BY 1;
SEQUENCE
A database object that allows multiple users to generate unique integers. Example: CREATE SEQUENCE seq_employee_id START WITH 1 INCREMENT BY 1;
INDEX
A database object used to speed up the retrieval of rows by using a pointer. Example: CREATE INDEX idx_emp_name ON employees(name);
FOREIGN KEY
A field (or collection of fields) in one table that uniquely identifies a row of another table. Example: CREATE TABLE departments (dept_id NUMBER PRIMARY KEY, emp_id NUMBER REFERENCES employees(id));
PRIMARY KEY
A field (or combination of fields) with a unique value for each record. Example: CREATE TABLE employees (id NUMBER PRIMARY KEY, name VARCHAR2(50));
CHAR
A fixed-length character data type. Example: CREATE TABLE employees (initials CHAR(3));
NUMBER
A numeric data type for holding numbers. Example: CREATE TABLE employees (salary NUMBER);
CONSTRAINT
A rule enforced on the data in tables. Example: ALTER TABLE employees ADD CONSTRAINT emp_id_pk PRIMARY KEY (id);
INDEX
A schema object that contains an entry for each value that appears in the indexed column(s) of the table or cluster. Example: CREATE INDEX idx_emp_name ON employees(name);
VARCHAR2
A variable-length character data type. Example: CREATE TABLE employees (name VARCHAR2(50));
VIEW
A virtual table based on the result-set of an SQL statement. Example: CREATE VIEW view_employee_details AS SELECT id, name, salary FROM employees;
VIEW
A virtual table, based on the result-set of an SQL statement. Example: CREATE VIEW view_employees AS SELECT id, name, salary FROM employees WHERE department_id = 1;
COMMIT
An SQL command that ends your current transaction and makes permanent all changes performed in the transaction. Example: UPDATE employees SET salary = salary * 1.1; COMMIT;
ROLLBACK
An SQL command that undoes all the changes made in the current transaction. Example: UPDATE employees SET salary = salary * 1.1; ROLLBACK;
GRANT
An SQL command used to provide privileges to database users. Example: GRANT SELECT, INSERT, DELETE ON employees TO user1;
REVOKE
An SQL command used to take back privileges from a database user. Example: REVOKE SELECT ON employees FROM user1;
SYNONYM
An alias for a schema object. Example: CREATE SYNONYM emp_for_employees FOR hr.employees;
BLOB
Binary Large Object, used to store binary data. Example: CREATE TABLE employees (photo BLOB);
CLOB
Character Large Object, used to store character data. Example: CREATE TABLE employees (bio CLOB);
FETCH
Clause in Oracle SQL used to retrieve a limited number of rows. Example: SELECT * FROM employees FETCH FIRST 5 ROWS ONLY;
FOR UPDATE
Clause in SELECT statement to lock the selected rows in the order in which they are retrieved. Example: SELECT * FROM employees WHERE department_id = 1 FOR UPDATE;
FROM
Clause that specifies the tables from which to retrieve data. Example: SELECT * FROM employees;
WHERE
Clause used to filter records. Example: SELECT * FROM employees WHERE salary > 50000;
UNDEFINE
Command in SQL*Plus to delete a substitution variable. Example: UNDEFINE dept_id;
VERIFY
Command in SQL*Plus to show the old and new settings for a substitution variable. Example: VERIFY ON;
DEFINE
Command in SQL*Plus to specify a substitution variable and its value. Example: DEFINE dept_id = 10;
TO_CHAR
Conversion function that converts a date or number to a string. Example: SELECT TO_CHAR(sysdate, 'YYYY-MM-DD') FROM dual;
TO_DATE
Conversion function that converts a string to a date. Example: SELECT TO_DATE('2020-12-31', 'YYYY-MM-DD') FROM dual;
TO_NUMBER
Conversion function that converts a string to a number. Example: SELECT TO_NUMBER('123') FROM dual;
REVOKE
DCL statement used to take back privileges granted to users. Example: REVOKE SELECT ON employees FROM user;
ALTER
DDL statement used to alter the structure of an existing database object. Example: ALTER TABLE employees ADD salary NUMBER;
CREATE
DDL statement used to create a new table, view, or other database objects. Example: CREATE TABLE employees (id NUMBER, name VARCHAR2(50));
DROP
DDL statement used to drop an existing database object. Example: DROP TABLE employees;
TRUNCATE
DDL statement used to remove all records from a table, including all spaces allocated for the records are removed. Example: TRUNCATE TABLE employees;
RENAME
DDL statement used to rename an existing database object. Example: RENAME TABLE employees TO staff;
INSERT
DML statement used to insert new rows into a database table. Example: INSERT INTO employees (id, name) VALUES (1, 'John Doe');
UPDATE
DML statement used to modify rows in a table. Example: UPDATE employees SET salary = 60000 WHERE id = 1;
DELETE
DML statement used to remove rows from a table. Example: DELETE FROM employees WHERE id = 1;
MERGE
DML statement used to select rows from one or more data sources for update or insertion into a table or view. Example: MERGE INTO employees e USING updated_employees u ON (e.id = u.id) WHEN MATCHED THEN UPDATE SET e.salary = u.salary;
GRANT
Data Control Language (DCL) statement used to provide privileges to users. Example: GRANT SELECT ON employees TO user;
VIEW
Database object that is a logical representation of one or more tables. Example: CREATE VIEW employee_view AS SELECT id, name FROM employees;
SEQUENCE
Database object that is used to automatically generate unique integers. Example: CREATE SEQUENCE seq START WITH 1 INCREMENT BY 1;
TABLE
Database object that stores data in rows and columns. Example: CREATE TABLE employees (id NUMBER, name VARCHAR2(50));
INDEX
Database object used to improve the performance of data retrieval. Example: CREATE INDEX idx_employee_id ON employees (id);
SYNONYM
Database object used to provide an alternative name for another database object. Example: CREATE SYNONYM emp FOR employees;
INTERVAL YEAR TO MONTH
Datatype that stores a period of time in years and months. Example: SELECT INTERVAL '4-3' YEAR TO MONTH FROM dual;
NOT NULL
Ensures that a column cannot have a NULL value. Example: CREATE TABLE employees (id NUMBER NOT NULL, name VARCHAR2(50));
UNIQUE
Ensures that all values in a column are different. Example: CREATE TABLE employees (id NUMBER UNIQUE, name VARCHAR2(50));
CHECK
Ensures that all values in a field satisfy certain conditions. Example: CREATE TABLE employees (id NUMBER, salary NUMBER CHECK(salary > 0));
DATE
For holding dates. Example: CREATE TABLE employees (hire_date DATE);
TIMESTAMP
For holding values that include both date and time. Example: CREATE TABLE employees (timestamp TIMESTAMP);
DECODE
Function has the functionality of an IF-THEN-ELSE statement. Example: SELECT DECODE(salary, 50000, 'High', 'Low') AS salary_level FROM employees;
NULLIF
Function returns null if the two arguments are equal. Example: SELECT NULLIF(salary, 50000) FROM employees;
DATE_ADD
Function that adds a specified time period to a date. Example: SELECT DATE_ADD(hire_date, INTERVAL 1 YEAR) FROM employees;
NVL
Function that lets you substitute a value when a null value is encountered. Example: SELECT NVL(salary, 50000) FROM employees;
DATEDIFF
Function that returns the difference between two date values, based on the interval specified. Example: SELECT DATEDIFF(YEAR, hire_date, SYSDATE) FROM employees;
DATE_SUB
Function that subtracts a specified time period from a date. Example: SELECT DATE_SUB(hire_date, INTERVAL 1 YEAR) FROM employees;
LOWER
Function to convert string to lowercase. Example: SELECT LOWER(name) AS lower_name FROM employees;
UPPER
Function to convert string to uppercase. Example: SELECT UPPER(name) AS upper_name FROM employees;
SUBSTR
Function to return a part of a string. Example: SELECT SUBSTR(name, 1, 3) AS abbreviation FROM employees;
LENGTH
Function to return the length of a string. Example: SELECT LENGTH(name) AS name_length FROM employees;
INSTR
Function to return the position of a substring in a string. Example: SELECT INSTR(name, 'a') as pos FROM employees;
EXTRACT
Function used to extract a field from a date or interval value. Example: SELECT EXTRACT(YEAR FROM hire_date) as hire_year FROM employees;
ROUND
Function used to round a column or expression to n decimal places. Example: SELECT ROUND(salary, 2) FROM employees;
TRUNC
Function used to truncate a column or expression to n decimal places. Example: SELECT TRUNC(salary, 2) FROM employees;
GROUP BY
Groups the result-set by one or more columns. Example: SELECT COUNT(id), department_id FROM employees GROUP BY department_id;
SAVEPOINT
Identifies a point in a transaction to which you can later roll back. Example: SAVEPOINT sp1; UPDATE employees SET salary = salary * 1.1; ROLLBACK TO sp1;
OFFSET
Keyword in Oracle SQL to skip a specified number of rows before returning the query results. Example: SELECT * FROM employees OFFSET 5 ROWS;
IN
Operator allows you to specify multiple values in a WHERE clause. Example: SELECT * FROM employees WHERE department_id IN (1, 2, 3);
BETWEEN
Operator selects values within a given range inclusive. Example: SELECT * FROM employees WHERE salary BETWEEN 50000 AND 70000;
AND
Operator that allows the existence of multiple conditions in an SQL statement's WHERE clause. Example: SELECT * FROM employees WHERE salary > 50000 AND department_id = 1;
OR
Operator that retrieves records if at least one of the conditions separated by OR is TRUE. Example: SELECT * FROM employees WHERE department_id = 1 OR department_id = 2;
LIKE
Operator used in a WHERE clause to search for a specified pattern in a column. Example: SELECT * FROM employees WHERE name LIKE 'J%';
UNION ALL
Operator used to combine the result-set of two or more SELECT statements (Allows duplicate values). Example: SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;
UNION
Operator used to combine the result-set of two or more SELECT statements (Only distinct values). Example: SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
NOT
Operator used to display a record if the condition(s) is NOT TRUE. Example: SELECT * FROM employees WHERE NOT department_id = 1;
MINUS
Operator used to return all the records from the first SELECT statement that are not in the second SELECT statement. Example: SELECT column_name(s) FROM table1 MINUS SELECT column_name(s) FROM table2;
INTERSECT
Operator used to return the records that two SELECT statements have in common. Example: SELECT column_name(s) FROM table1 INTERSECT SELECT column_name(s) FROM table2;
ROWID
Pseudocolumn that represents the unique address of a row in its table. Example: SELECT ROWID, name FROM employees;
ROWNUM
Pseudocolumn which indicates the row number in a result set of a query. Example: SELECT ROWNUM, name FROM employees;
NULL
Represents missing or unknown data. Example: SELECT * FROM employees WHERE department_id IS NULL;
LEFT (OUTER) JOIN
Returns all records from the left table (table1), and the matched records from the right table (table2). Example: SELECT * FROM employees LEFT JOIN departments ON employees.department_id = departments.id;
RIGHT (OUTER) JOIN
Returns all records from the right table (table2), and the matched records from the left table (table1). Example: SELECT * FROM employees RIGHT JOIN departments ON employees.department_id = departments.id;
FULL (OUTER) JOIN
Returns all records when there is a match in either left (table1) or right (table2) table records. Example: SELECT * FROM employees FULL JOIN departments ON employees.department_id = departments.id;
INNER JOIN
Returns records that have matching values in both tables. Example: SELECT * FROM employees INNER JOIN departments ON employees.department_id = departments.id;
CROSS JOIN
Returns the Cartesian product of sets of records from the two (or more) joined tables. Example: SELECT * FROM employees CROSS JOIN departments;
JOIN
SQL operation performed to combine rows from two or more tables based on a related column. Example: SELECT * FROM employees JOIN departments ON employees.id = departments.emp_id;
ORDER BY
Sorts the result-set in ascending or descending order. Example: SELECT * FROM employees ORDER BY salary DESC;
CASE
Statement goes through conditions and returns a value when the first condition is met. Example: SELECT CASE WHEN salary > 50000 THEN 'High' ELSE 'Low' END AS salary_level FROM employees;
TRUNCATE TABLE
Statement is a Data Definition Language (DDL) operation that is used to mark the extents of a table for deallocation (empty for reuse). Example: TRUNCATE TABLE employees;
MERGE
Statement is used to select rows from one or more sources for update or insertion into a table or view. Example: MERGE INTO employees e USING updated_employees ue ON (e.id = ue.id) WHEN MATCHED THEN UPDATE SET e.name = ue.name, e.salary = ue.salary WHEN NOT MATCHED THEN INSERT (id, name, salary) VALUES (seq_employee_id.NEXTVAL, ue.name, ue.salary);
ALTER TABLE
Statement used to add, delete/drop or modify columns in an existing table. Example: ALTER TABLE employees ADD email VARCHAR2(50);
SYSDATE
System function to return the current date and time. Example: SELECT SYSDATE FROM dual;
CONSTRAINT
The rules enforced on data columns of a table. Types: NOT NULL, UNIQUE, CHECK, PRIMARY Key, and FOREIGN Key. Example: CREATE TABLE employees (id NUMBER(10) NOT NULL, name VARCHAR2(100), salary NUMBER(10,2), CONSTRAINT employees_pk PRIMARY KEY (id));
SAVEPOINT
Transaction Control statement that creates points within groups of transactions in which to ROLLBACK. Example: SAVEPOINT savepoint_name;
ROLLBACK
Transaction Control statement used to restore the database to the last committed state. Example: ROLLBACK;
COMMIT
Transaction Control statement used to save work done in the current transaction. Example: COMMIT;
HAVING
Used instead of WHERE with aggregate functions. Example: SELECT COUNT(id), department_id FROM employees GROUP BY department_id HAVING COUNT(id) > 5;
