Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

Oracle: SQL, PL/SQL

memorize.ai (lvl 286)

(0)

46 cards

()

Section 1

Preview this deck

SELECT

Front

1 / 46

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

6 years ago

Date created

Mar 1, 2020

Cards (46)

Section 1

(46 cards)

SELECT

Front

List the columns (and expressions) that should be returned from the query

Back

DROP TABLE

Front

statement allows you to remove tables from your schema: DROP TABLE CUSTOMER_T

Back

WHERE

Front

Indicate the conditions under which a row will be included in the result

Back

HAVING, difference from WHERE?

Front

Like a WHERE clause, but it operates on groups (categories), not on individual rows.

Back

AND, OR, and NOT

Front

Operators for customizing conditions in WHERE clause. Note: the LIKE operator allows you to compare strings using wildcards. For example, the % wildcard in '%Desk' indicates that all strings that have any number of characters preceding the word "Desk" will be allowed

Back

Relational Database

Front

A relational database is a DB that stores the data in the form of tables/relations with rows and columns. Every table shares at least one column with another table (Relationships).

Back

Sort

Front

Sorts results

Back

HAVING

Front

Indicate the conditions under which a group will be included

Back

Table/Relation

Front

A table/Relation is the category of data, like Staff. The columns are information about the category (Attributes), like name or address and the rows are the actual data or records.

Back

Schema

Front

The structure that contains descriptions of objects created by a user (base tables, views, constraints)

Back

Views

Front

Views provide users controlled access to tables. Base Table - table containing the raw data. Materialized View. Copy or replication of data. Data actually stored. Must be refreshed periodically to match the corresponding base tables.

Back

When is PL/SQL handy?

Front

When something is too complicated for SQL. When conditional branching and looping are needed

Back

VARCHAR2(N) - String type

Front

variable length character data, maximum 4000 bytes

Back

Cross Join

Front

A join in which each row from one table is combined with each row from another table.

Back

ALTER TABLE

Front

statement allows you to change column specifications: ALTER TABLE CUSTOMER_T ADD (TYPE VARCHAR(2))

Back

Advantages of Views

Front

Simplify query commands. Assist with data security. Enhance programming productivity. Contain most current base table data. Use little storage space. Provide customized view for user.

Back

SQL Joins

Front

A join is a query that combines rows from two or more relations. Joins allow database users to combine data from one table with data from one or more other tables or views, or synonyms, as long as they are relations

Back

DDL

Front

Data Definition Language (DDL). Commands that define a database, including creating, altering, and dropping tables and establishing constraints

Back

INSERT statement

Front

Adds data to a table

Back

NUMBER(p,q) - Numeric type

Front

general purpose numeric data type

Back

DATE - Date/time type

Front

fixed-length date/time in dd-mm-yy form

Back

FROM

Front

Indicate the table(s) or view(s) from which data will be obtained

Back

ORDER BY

Front

Sorts the result according to specified columns

Back

DML

Front

Data Manipulation Language (DML). Commands that maintain and query a database

Back

Data Definition Language (DDL)

Front

DDL is a descriptive language for defining the database schema. Some of the main SQL-DDL commands are: CREATE TABLE. ALTER TABLE. DROP TABLE.

Back

Data Manipulation Language (DML)

Front

DML is a language for retrieving and updating (insert, delete, & modify) the data in the DB. The main SQL-DML commands are: SELECT. INSERT INTO. UPDATE. DELETE FROM.

Back

PL/SQL characteristics

Front

Acts as host language for stored procedures and triggers. Provides the ability to add middle tier business logic to client/server applications. Provides Portability of code from one environment to another. Improves performance of multi-query transactions. Provides error handling

Back

UPDATE statment

Front

Modifies data in existing rows

Back

DELETE statment

Front

Removes rows from a table

Back

Outer Join

Front

includes all records from one table, and matches those records from the other table for which values in the joined fields are equal. Left Outer Join vs. Right Outer Join. includes all records from one table, and matches those records from the other table for which values in the joined fields are equal. Left Outer Join vs. Right Outer Join

Back

SQL

Front

Structured Query Language. nonprocedural language that represents a combination of DDL and DML

Back

Inner Join

Front

the default join that tells a query to combine rows from two tables that share a common value.

Back

Self Join

Front

Joing a table with itself. Used when you need to compare values in one column with other values in the same column in the same table

Back

Catalog

Front

A set of schemas that constitute the description of a database

Back

FLOAT(p) - Numeric type

Front

floating point in scientific notation with p binary digits precision

Back

DCL

Front

Data Control Language (DCL). Commands that control a database, including administering privileges and committing data

Back

CURSORS

Front

A cursor is a private set of records. An Oracle Cursor = VB recordset. Implicit cursors are created for every query made in Oracle. Explicit cursors can be declared by a programmer within PL/SQL.

Back

LONG - String type

Front

variable-length character data, up to 4GB. Maximum 1 per table

Back

Oracle

Front

powerful relational database management system (RDBMS) that offers a large feature set

Back

GROUP BY

Front

Indicate columns to group the results

Back

TRIGGERS

Front

PL/SQL code executed automatically in response to a database event, typically DML.

Back

CHAR(n) - String type

Front

fixed-length character data, n characters long Maximum length = 2000 bytes

Back

What is PL/SQL?

Front

Procedural Language - SQL. An extension to SQL with design features of programming languages (procedural and object oriented). PL/SQL and Java are both supported as internal host languages within Oracle products.

Back

Full Join

Front

returns rows when there is at least one match in one of the tables

Back

INGEGER(p) - Numeric type

Front

signed integer, p digits wide

Back

PL/SQL BLOCK STRUCTURE

Front

DECLARE (optional) - variable declarations BEGIN (required) - SQL statements - PL/SQL statements or sub-blocks EXCEPTION (optional) - actions to perform when errors occur END; (required)

Back