Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Oracle SQL > Cards > Flashcards

Cards Flashcards

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);
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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);
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Delete a column in a table

A

ALTER TABLE table_name

DROP COLUMN column_name;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Rename a column in a table

A

ALTER TABLE table_name

RENAME COLUMN old_name TO new_name;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Retrieve data from a table

A

SELECT expressions
FROM tables
[WHERE conditions];

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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 );

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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];
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Update existing records in a table

A 
UPDATE table
SET column1 = expression1,
    column2 = expression2,
    ...
    column_n = expression_n
[WHERE conditions];
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Remove records from a table

A

DELETE FROM table

[WHERE conditions];

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What are the 10 DDL Statements?

A 
CREATE
ALTER
DROP
RENAME
TRUNCATE
GRANT
REVOKE
FLASHBACK
PURGE
COMMENT
  • all auto-commit
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What are the 5 DML Statements?

A 
SELECT
INSERT
UPDATE
DELETE
MERGE
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What are the 3 TCL Statements?

A

COMMIT
ROLLBACK
SAVEPOINT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What are the 8 Main DB objects?

A 
Constraints
Indexes
Roles
Sequences
Synonyms
Tables
Users
Views
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What is a Schema?

A

A collection of certain DB objects all owned by a user account.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Non-Schema Objects

A

Users
Roles
Public Synonyms

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Add a new table

A 
CREATE TABLE table_name
(column_1     datatype,
column_2     datatype,
...
column_n     datatype);
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Add a user

A

CREATE USER user_name

IDENTIFIED BY password;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Change a user’s password

A

ALTER USER user_name

IDENTIFIED BY password;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What are the 5 system variables (pseudo columns)?

A 
SYSDATE
CURRENT_DATE
SYSTIMESTAMP
LOCALTIMESTAMP
USER

-these can be directly retrieved from dual

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

What is the purpose of DDL?

A

Used to build database objects.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

What is the purpose of DML?

A

Used to work with data in database objects.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

Define COMMIT

A

Saves DML modifications in current session.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

Define SAVEPOINT

A

Marks a point in the current session to revert to in a ROLLBACK.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

Define ROLLBACK

A

Undoes a set of DML modifications during the current session.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

Define CREATE

A

Used to create a user, table, view, index, synonym, or other object in the database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

Define ALTER

A

Used on an existing object in the database to modify that object’s structure, name, or some other attribute.

31
Q

Define DROP

A

Used to remove a database object from the database that has already been created with the CREATE statement.

32
Q

Define RENAME

A

Changes the name of an existing database object.

33
Q

Define TRUNCATE

A

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
Q

Define GRANT

A

Provides privileges, or rights, to user objects to enable them to perform various tasks in the database.

35
Q

Define REVOKE

A

Removes privileges that have been issued with the GRANT statement.

36
Q

Define FLASHBACK

A

Restores an earlier version of a table or database.

37
Q

Define PURGE

A

Irrevocably removes database objects from the recycle bin.

38
Q

Define COMMENT

A

Adds comments to the data dictionary for an existing

database object.

39
Q

Define MERGE

A

Performs a combination of INSERT, UPDATE, and DELETE statements in one statement.

40
Q

Define a TABLE

A

A structure that can store data. All data is stored in columns and rows. Each column’s data type is explicitly defined.

41
Q

Define a INDEX

A

An object designed to support faster searches in a table.

42
Q

Define a VIEW

A

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
Q

Define a SEQUENCE

A

A counter, often used to generate unique numbers as

identifiers for new rows as they are added to a table.

44
Q

Define a SYNONYM

A

An alias for another object in the database, often used to specify an alternative name for a table or view.

45
Q

Define a CONSTRAINT

A

A small bit of logic defined by you to instruct a particular table about how it will accept, modify, or reject incoming data.

46
Q

Define USERS

A

The “owners” of database objects.

47
Q

Define ROLES

A

A set of one or more privileges that can be granted to a user.

48
Q

Define the CHAR(n) datatype

A

Accepts alphanumeric character input of fixed-length n. Padded with spaces as necessary. Max 2000.

49
Q

Define the VARCHAR2(n) datatype

A

Accepts alphanumeric character input of variable-length n. Max 4000.

50
Q

Define the NUMBER(n,m) datatype

A

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
Q

Define the DATE datatype

A

This accepts date and time information. The fields stored include year, month, date, hour, minute, and second.

52
Q

Define TIMESTAMP(n) WITH LOCAL TIME ZONE

A

The time zone offset is not stored with the column’s value, and the value retrieved is user’s local session time zone.

53
Q

Define TIMESTAMP(n)

A

Stores year, month, day, hours, minutes, seconds, and fractional seconds. The value for n specifies the precision for fractional seconds.

54
Q

Define INTERVAL YEAR(n) TO MONTH

A

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
Q

Define INTERVAL DAY(n1) TO SECOND(n2)

A

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
Q

Define BLOB

A

Abbreviation for “binary large object.” BLOB accepts large binary objects, such as image or video files. Declaration is made without precision or scale.

57
Q

Define NCLOB

A

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
Q

Define CLOB

A

Abbreviation for “character large object.” CLOB accepts large text data elements. Declaration is made without
precision or scale.

59
Q

What are the 5 CONSTRAINT types?

A 
PRIMARY KEY
FOREIGN KEY
UNIQUE
CHECK
NOT NULL
60
Q

Add a NOT NULL constraint

A
  • 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
Q

Define an INNER JOIN

A

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
Q

Define a LEFT OUTER JOIN

A

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
Q

Define a RIGHT OUTER JOIN

A

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
Q

Define a FULL OUTER JOIN

A

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
Q

Add a CHECK CONSTRAINT

A

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK
(column_name condition) [DISABLE];

66
Q

Enable a CONSTRAINT

A

ALTER TABLE table_name

ENABLE CONSTRAINT constraint_name;

67
Q

Disable a CONSTRAINT

A

ALTER TABLE table_name

DISABLE CONSTRAINT constraint_name;

68
Q

Drop a CONSTRAINT

A

ALTER TABLE table_name

DROP CONSTRAINT constraint_name;

69
Q

Rules of CHECK CONSTRAINT

A
  • 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
Q

Create a table with a CHECK CONSTRAINT

A

CREATE TABLE table_name
( column1 datatype null/not null,
column2 datatype null/not null, …
CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE] );

71
Q

Create a table with a UNIQUE CONSTRAINT

A

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
Q

Add a UNIQUE CONSTRAINT

A

ALTER TABLE table_name

ADD CONSTRAINT constraint_name UNIQUE (uc_col1, uc_col2, … uc_col_n);

73
Q

What are the character conversion functions?

A

LOWER (column_name | expression)
UPPER (column_name | expression)
INITCAP (column_name | expression)

74
Q

What are the character manipulation functions?

A

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 (1 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions