1
Q
Add columns to a table
A
ALTER TABLE table_name
ADD (column_1 column_definition,
column_2 column_definition,
...
column_n column_definition);
2
Q
Modify columns in a table
A
ALTER TABLE table_name
MODIFY (column_1 column_type,
column_2 column_type,
...
column_n column_type);
3
Q
Delete a column in a table
A
ALTER TABLE table_name
DROP COLUMN column_name;
4
Q
Rename a column in a table
A
ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;
5
Q
Retrieve data from a table
A
SELECT expressions
FROM tables
[WHERE conditions];
6
Q
Insert a single record in a table
A
- INSERT statement with VALUES keyword
INSERT INTO table
column1, column2, … column_n
VALUES
(expression1, expression2, … expression_n );
7
Q
Insert multiple records a table from another table
A
- INSERT statement with SELECT keyword
INSERT INTO table (column1, column2, ... column_n ) SELECT expression1, expression2, ... expression_n FROM source_table [WHERE conditions];
8
Q
Adding multiple rows with a single statement
A
- Use INSERT ALL
INSERT ALL
INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
SELECT * FROM dual;
9
Q
Update existing records in a table
A
UPDATE table
SET column1 = expression1,
column2 = expression2,
...
column_n = expression_n
[WHERE conditions];
10
Q
Remove records from a table
A
DELETE FROM table
[WHERE conditions];
11
Q
Remove all records from a table
A
TRUNCATE TABLE [schema_name.]table_name
[ PRESERVE MATERIALIZED VIEW LOG | PURGE MATERIALIZED VIEW LOG ]
[ DROP STORAGE | REUSE STORAGE ] ;
- Not: this cannot be rolled back
12
Q
Return all rows from multiple tables based on a matching condition
A
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
13
Q
What are the 10 DDL Statements?
A
CREATE ALTER DROP RENAME TRUNCATE GRANT REVOKE FLASHBACK PURGE COMMENT
- all auto-commit
14
Q
What are the 5 DML Statements?
A
SELECT INSERT UPDATE DELETE MERGE
15
Q
What are the 3 TCL Statements?
A
COMMIT
ROLLBACK
SAVEPOINT
16
Q
What are the 8 Main DB objects?
A
Constraints Indexes Roles Sequences Synonyms Tables Users Views
17
Q
What is a Schema?
A
A collection of certain DB objects all owned by a user account.
18
Q
Non-Schema Objects
A
Users
Roles
Public Synonyms
19
Q
Add a new table
A
CREATE TABLE table_name (column_1 datatype, column_2 datatype, ... column_n datatype);
20
Q
Add a user
A
CREATE USER user_name
IDENTIFIED BY password;
21
Q
Change a user’s password
A
ALTER USER user_name
IDENTIFIED BY password;
22
Q
Create a table from an existing table
A
CREATE TABLE new_table
AS (SELECT * FROM old_table);
- This will copy the records as well if they exist
23
Q
What are the 5 system variables (pseudo columns)?
A
SYSDATE CURRENT_DATE SYSTIMESTAMP LOCALTIMESTAMP USER
-these can be directly retrieved from dual
24
Q
What is the purpose of DDL?
A
Used to build database objects.
25
What is the purpose of DML?
Used to work with data in database objects.
26
Define COMMIT
Saves DML modifications in current session.
27
Define SAVEPOINT
Marks a point in the current session to revert to in a ROLLBACK.
28
Define ROLLBACK
Undoes a set of DML modifications during the current session.
29
Define CREATE
Used to create a user, table, view, index, synonym, or other object in the database.
30
Define ALTER
Used on an existing object in the database to modify that object’s structure, name, or some other attribute.
31
Define DROP
Used to remove a database object from the database that has already been created with the CREATE statement.
32
Define RENAME
Changes the name of an existing database object.
33
Define TRUNCATE
Removes all the rows—in other words, data—from an
| existing table in the database. TRUNCATE cannot be rolled back like the DELETE statement.
34
Define GRANT
Provides privileges, or rights, to user objects to enable them to perform various tasks in the database.
35
Define REVOKE
Removes privileges that have been issued with the GRANT statement.
36
Define FLASHBACK
Restores an earlier version of a table or database.
37
Define PURGE
Irrevocably removes database objects from the recycle bin.
38
Define COMMENT
Adds comments to the data dictionary for an existing
| database object.
39
Define MERGE
Performs a combination of INSERT, UPDATE, and DELETE statements in one statement.
40
Define a TABLE
A structure that can store data. All data is stored in columns and rows. Each column’s data type is explicitly defined.
41
Define a INDEX
An object designed to support faster searches in a table.
42
Define a VIEW
A “filter” through which you can search a table and interact with a table but that stores no data itself and simply serves as a “window” onto one or more tables.
VIEW objects can be used to mask portions of the
underlying table logic for various reasons—perhaps to simplify business logic or to add a layer of security by hiding the real source of information.
A VIEW can be used to display certain parts of a table while hiding other parts of the same table.
43
Define a SEQUENCE
A counter, often used to generate unique numbers as
| identifiers for new rows as they are added to a table.
44
Define a SYNONYM
An alias for another object in the database, often used to specify an alternative name for a table or view.
45
Define a CONSTRAINT
A small bit of logic defined by you to instruct a particular table about how it will accept, modify, or reject incoming data.
46
Define USERS
The “owners” of database objects.
47
Define ROLES
A set of one or more privileges that can be granted to a user.
48
Define the CHAR(n) datatype
Accepts alphanumeric character input of fixed-length n. Padded with spaces as necessary. Max 2000.
49
Define the VARCHAR2(n) datatype
Accepts alphanumeric character input of variable-length n. Max 4000.
50
Define the NUMBER(n,m) datatype
Accepts numeric data, including zero, negative, and positive numbers, where n specifies the “precision" (total number of digits) and m is the “scale,” (total number of digits right of the decimal).
Both n and m are optional; but good practice to use.
51
Define the DATE datatype
This accepts date and time information. The fields stored include year, month, date, hour, minute, and second.
52
Define TIMESTAMP(n) WITH LOCAL TIME ZONE
The time zone offset is not stored with the column’s value, and the value retrieved is user’s local session time zone.
53
Define TIMESTAMP(n)
Stores year, month, day, hours, minutes, seconds, and fractional seconds. The value for n specifies the precision for fractional seconds.
54
Define INTERVAL YEAR(n) TO MONTH
This stores a span of time defined in only year and month values, where n is the number of digits used to define the YEAR value. The range of acceptable values for n is 0–9; the default for n is 2.
This data type is useful for storing the difference between two date values.
55
Define INTERVAL DAY(n1) TO SECOND(n2)
This stores a span of time defined in days, hours, minutes, and seconds, where n1 is the precision
for days, and n2 is the precision for seconds.
56
Define BLOB
Abbreviation for “binary large object.” BLOB accepts large binary objects, such as image or video files. Declaration is made without precision or scale.
57
Define NCLOB
This accepts CLOB data in Unicode — it is an alternative to ASCII and supports all major languages more easily. Officially recommending the use of Unicode as the database national character set for all new system development.
58
Define CLOB
Abbreviation for “character large object.” CLOB accepts large text data elements. Declaration is made without
precision or scale.
59
What are the 5 CONSTRAINT types?
```
PRIMARY KEY
FOREIGN KEY
UNIQUE
CHECK
NOT NULL
```
60
Add a NOT NULL constraint
- must be done in-line only
- can be named if desired
ALTER TABLE table_name
MODIFY column_name NOT NULL;
or
ALTER TABLE table_name
MODIFY column_name CONSTRAINT constraint_name NOT NULL;
61
Define an INNER JOIN
Returns all rows from multiple tables where the join condition is met.
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
62
Define a LEFT OUTER JOIN
Returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the join condition is met.
SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;
63
Define a RIGHT OUTER JOIN
Returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the join condition is met.
SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;
64
Define a FULL OUTER JOIN
Returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the join condition is not met.
SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON table1.column = table2.column;
65
Add a CHECK CONSTRAINT
ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK
(column_name condition) [DISABLE];
66
Enable a CONSTRAINT
ALTER TABLE table_name
| ENABLE CONSTRAINT constraint_name;
67
Disable a CONSTRAINT
ALTER TABLE table_name
| DISABLE CONSTRAINT constraint_name;
68
Drop a CONSTRAINT
ALTER TABLE table_name
| DROP CONSTRAINT constraint_name;
69
Rules of CHECK CONSTRAINT
- A check constraint can NOT be defined on a SQL View.
- The check constraint defined on a table must refer to only columns in that table. It can not refer to columns in other tables.
- A check constraint can NOT include a SQL Subquery.
70
Create a table with a CHECK CONSTRAINT
CREATE TABLE table_name
( column1 datatype null/not null,
column2 datatype null/not null, ...
CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE] );
71
Create a table with a UNIQUE CONSTRAINT
CREATE TABLE table_name
( column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ], ...
CONSTRAINT constraint_name UNIQUE (uc_col1, uc_col2, ... uc_col_n));
72
Add a UNIQUE CONSTRAINT
ALTER TABLE table_name
| ADD CONSTRAINT constraint_name UNIQUE (uc_col1, uc_col2, ... uc_col_n);
73
What are the character conversion functions?
LOWER (column_name | expression)
UPPER (column_name | expression)
INITCAP (column_name | expression)
74
What are the character manipulation functions?
CONCAT (column_name1 | expression1, column_name2 | expression2)
- same as ||
SUBSTR (column_name | expression, m,n)
- characters from m, n long, or to end if n omitted
LENGTH (column_name | expression)
- returns total number of chars
INSTR (column_name | expression, m)
- m is 'string' or number
- returns position of character
LPAD (column_name | expression, n,m)
- m is 'string' or number
- n is number for total width
Oracle SQL
flashcards
Decks in class (1)
# Cards
