Have a grasp of both SQL DML (Data Manipulation Language) and DDL (Data Definition Language).
DML covers items such as
You should understand all the major clauses such as
ORDER BY. Should be comfortable with sub queries and joins.
DDL covers items such as
You should understand how to create and modify tables and indexes and know the difference between deleting records, truncating a table, or dropping it! And… don’t forget views!
The detail about DML&DDL click here
-- group by syntax
see the below related clause examples:
SELECT suppliers.name, subquery1.total_amt
SELECT tbls.owner, tbls.table_name,
- Oracle INNER JOIN (or sometimes called SIMPLE JOIN)
- Oracle LEFT OUTER JOIN (or sometimes called LEFT JOIN)
- Oracle RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
- Oracle FULL OUTER JOIN (or sometimes called FULL JOIN)
PL/SQL is a third generation language that has the expected procedural and namespace constructs, and its tight integration with SQL makes it possible to build complex and powerful applications. Because PL/SQL is executed in the database, you can include SQL statements in your code without having to establish a separate connection.
The main types of program units you can create with PL/SQL and store in the database are standalone procedures and functions, and packages. Once stored in the database, these PL/SQL components, collectively known as stored procedures, can be used as building blocks for several different applications.
-- Creating Procedures and Functions syntax
Procedures and functions that are defined within a package are known as packaged subprograms. Procedures and functions that are nested within other subprograms or within a PL/SQL block are called local subprograms; they exist only inside the enclosing block and cannot be referenced externally.
Packages usually have two parts: a specification and a body.
-- The standard package specification
The package body contains the code that implements these subprograms, the code for all private subprograms that can only be invoked from within the package, and the queries for the cursors. You can change the implementation details inside the package body without invalidating the calling applications.
CREATE OR REPLACE PACKAGE BODY package_name AS
License: CC BY-NC-ND 4.0