Enterprise: Week 2 Flashcards
(141 cards)
1
Q
What is SQL?
A
SQL Stand for Structured Query Language
2
Q
What does SQL do?
A
t is a domain-specific language for working with certain databases called relational databases.
3
Q
Is SQL a programming language?
A
SQL is not a programming language, although there are extensions to the specification like PL/SQL that add familiar programming constructs.
Instead, it is an English-like syntax that lets developers and database administrators abstract away the process of manipulating data and focus on the WHAT instead of HOW in the database.
4
Q
What is a Relational Database?
A
A relational database is a type of database that stores information in tables - that is, the data is stored in rows and columns, similar to a spreadsheet.
5
Q
What is each row in the table?
A
Each row in the table is a record, and each record has properties which correspond to the different columns in the table.
6
Q
What type of software is used to manipulate a database?
A
relational database management system, or RDBMS
7
Q
Is there a difference between relational databases and non-relational databases?
A
There are major differences. Nonrelational databases do not store data within tables that relate to each other, and thus do not use SQL to interact with the database.
8
Q
What type of data provides meaning to an enterprise?
A
Related data is what provides meaning and organizes the structure of data.
9
Q
Normally where is relational databases used in?
A
OLTP (online transaction processing) environment, which means the idea of having related data is preferable in a very transactional system and that are normally row-based
10
Q
For non-transactional environments, what is used?
A
OLAP - Online analytic processing: normally columnar-based, which is faster for reading but slower for manipulation
11
Q
How many zones does AWS span out to?
A
77 available zones within 24 geographic regions around the world
12
Q
What are AWS regions?
A
An AWS region is a geographical location with a collection of availability zones mapped to physical data centers in that region. Every region is physically isolated from and independent of every other region in terms of location, power, water supply, etc.
13
Q
Why is this level of isolation important for AWS regions?
A
This level of isolation is critical for workloads with compliance and data sovereignty requirements where guarantees must be made that user data does not leave a particular geographic region.
14
Q
Why is the presence of AWS regions also important?
A
The presence of AWS regions worldwide is also important for workloads that are latency-sensitive and need to be located near users in a particular geographic area.
15
Q
What is an Availability Zone?
A
An availability zone is a logical data center in a region available for use by any AWS customer.
16
Q
What does each zone contain?
A
Each zone in a region has redundant and separate power, networking and connectivity to reduce the likelihood of two zones failing simultaneously.
17
Q
What is Amazon RDS?
A
Amazon Relational Database Service (Amazon RDS) is a web service that makes it easier to set up, operate, and scale a relational database in the AWS Cloud.
18
Q
What does Amazon RDS provide?
A
It provides cost-efficient, resizable capacity for an industry-standard relational database and manages common database administration tasks.
19
Q
What does RDS automate?
A
RDS automates expensive and time consuming tasks such as managing backups, software patching, automatic failure detection, and recovery.
20
Q
What does RDS help control?
A
You can help control who can access your RDS databases by using AWS Identity and Access Management (IAM) to define users and permissions.
21
Q
RDS is available on several database instance types. What do instance types comprise of?
A
Instance types comprise varying combinations of CPU, memory, storage, and networking capacity and give you the flexibility to choose the appropriate mix of resources for your database.
22
Q
What 6 database engines does the RDS provide?
A
Amazon Aurora, PostgreSQL, MySQL, MariaDB, Oracle Database, and SQL Server.
23
Q
Why use RDS?
A
- AWS offers 15 database engines including relational, key-value, document, in-memory, graph, time series, and ledger databases.
- Don’t need to worry about database mgmt tasks such as backups, recovery, configurations. It is managed by RDS
- RDS is a relational database service, organizes data within tables in rows and columns
24
Q
What are availability zones (AZs)?
A
distinct locations in AWS Regions
25
How is each availability zone engineered?
To be isolated from failures in other Availability Zones. Each is engineered to provide inexpensive, low-latency network connectivity to other Availability Zones in the same AWS Region.
26
What is Fault Tolerance?
Fault-tolerance defines the ability for a system to remain in operation even if some of the components used to build the system fail.
27
What does the security group do?
A security group controls the access to a DB instance. It does so by allowing access to IP address ranges or Amazon EC2 instances that you specify. ou can set security groups when configuring your RDS instance.
28
What are the 3 ways of interacting with Amazon RDS?
AWS Management Console, Command Line Interface, Programatically Accessing Amazon RDS
29
What are some key differences between AWS RDS and AWS EC2 Instance?
- DB services (like RDS) are managed services, meaning that you have limited control over the actual database. EC2 (Elastic Cloud Compute), gives you maximum control over the software stack, db, and operating system
- EC2 allows you to compute capacity and SUPER privileges, increasing flexibility
- RDS is more cost-effective and is automated, but limits control over the DB
- RDS takes care of your DB from end-to-end by managing, maintaining, and securing it, eliminiating overhead costs and the role of a DBA
30
What is Cloud computing?
Cloud computing is the on-demand delivery of compute power, database storage, applications and other IT resources through a cloud services platform via the Internet with pay-as-you-go pricing.
31
What does cloud computing provide?
Provides a simple way to access servers, storage, databases and a broad set of application services over the Internet.
32
What are the 6 advantages of cloud computing?
- Trade capital expense for variable expense.
- Benefit from massive economies of scale.
- Stop guessing about capacity.
- Increase speed and agility.
- Eliminate overhead cost of maintaining data centers
- Go global in minutes.
33
What is Trade Capital Expense for Variable Expense?
Instead of having to invest heavily in data centers and servers before you know how you’re going to use them, you can pay only when you consume computing resources, and pay only for how much you consume.
34
What is Benefit from Massive Economics of Scale?
By using cloud computing, you can achieve a lower variable cost than you can get on your own. Because usage from hundreds of thousands of customers is aggregated in the cloud, providers such as AWS can achieve higher economies of scale, which translates into lower pay as-you-go price.
35
What is Stop guessing about capacity?
Eliminate guessing on your infrastructure capacity needs. When you make a capacity decision prior to deploying an application, you often end up either sitting on expensive idle resources or dealing with limited capacity.
36
What is Increase speed and agility?
In a cloud computing environment, new IT resources are only a click away, which means that you reduce the time to make those resources available to your developers from weeks to just minutes.
37
What is Eliminate overhead cost of maintaining data centers?
Cloud computing lets you focus on your own customers, rather than on the heavy lifting of racking, stacking, and powering servers (infrastructure).
38
What is Go global in minutes?
Easily deploy your application in multiple regions around the world with just a few clicks. This means you can provide lower latency and a better experience for your customers at minimal cost.
39
What are the 3 models of cloud computing?
IaaS, Paas, Saas
40
What is Iaas?
Infrastructure as a Service (IaaS) is a self-service model for managing remote data center infrastructures. AWS offers IaaS in the form of data centers.
41
What is PaaS?
Platform as a Service (PaaS) allows organizations to build, run and manage applications without the IT infrastructure. This makes it easier and faster to develop, test and deploy applications.
42
What is SaaS?
Software as a service (SaaS) replaces the traditional on-device software with software that is licensed on a subscription basis. It is centrally hosted in the cloud. A good example is Salesforce.com.
43
What are the common data types in SQL?
Numeric, Date/Time, Character/String, Binary, Misc.
44
What is the convention for SQL?
SQL is a case-insensitive language, but the convention is to use UPPERCASE to refer to SQL keywords and lowercase for non-SQL specific entities (like table or column names). This helps distinguish between SQL keywords and other words.
Also, for readability purposes we should split long commands or queries into multiple lines.
45
What is DML?
Data Manipulation Language statements are used to perform CRUD operations on the actual data. Operations are normally performed by row in a relational database.
46
What is INSERT?
INSERT, to insert a new row into a table.
INSERT INTO TABLE_NAME VALUES (V1, V2, ..., VN)
INSERT INTO TABLE_NAME (C1, C2, C3) VALUES (V1, V2, V3)
47
What is UPDATE?
UPDATE, to update one or more rows column values of a table that match a specific WHERE clause.
UPDATE TABLE_NAME SET C1 = V1, ... , CN = VN WHERE X = Y
48
What is DELETE?
DELETE, to delete one or more rows of a table that match a specific WHERE clause.
DELETE TABLE_NAME WHERE [condition]
49
What is SELECT?
SELECT, to obtain one or more rows of a table that match a specific WHERE clause. In ORACLE databases this one is considered DML. This is how we perform queries in a database.
SELECT C1, ..., CN FROM TABLE_NAME [table] WHERE [condition] GROUP BY [expression]
HAVING [condition] ORDER BY table.field
50
What is DQL?
Data Query Language, not really a sub-language within Oracle databases, is the sub language where only the SELECT statement exists.
51
What are DQL Clauses?
The GROUP BY clause will combine all rows by a column specified in a query and perform any aggregate functions which are stated.
SELECT NAME, COUNT(NAME) FROM STUDENT GROUP BY (NAME)
The HAVING clause will pass another filter similar to the WHERE clause after everything has been filtered and grouped.
SELECT NAME, COUNT(NAME) FROM STUDENT GROUP BY (NAME) HAVING COUNT(NAME) > 5;
**If you try to perform this HAVING clause in a WHERE clause, a SQL error will be thrown, and it makes sense - the RDBMS doesn't want you to perform an aggregate function combining all rows, per each row. It's a performance safety measure.
52
What are Scalar Functions?
Scalar functions operate on individual values and will perform some operation per row, and can be used in the SELECT or WHERE clause.
TO_CHAR(DATE,'DATE_FORMAT')
TO_DATE(DATE,'DATE_FORMAT')
UPPER('VALUE')
LOWER('VALUE')
To write them in a query:
```
SELECT UPPER(NAME) FROM STUDENT;
SELECT NAME FROM STUDENT WHERE UPPER(NAME) LIKE 'P%'.
```
53
What are Aggregate Functions?
Aggregate functions operate on multiple values (multiple rows). These functions are used to combine (aggregate) the values existing in one column.
These functions are used in the SELECT clause. They can't be used in the WHERE clause.
If there is more than one column being selected in the SELECT column section of a query which is not aggregating, a GROUP BY clause is required.
In order to perform similar WHERE clause Boolean operations with aggregate functions, the HAVING clause can be used.
54
What is MAX(COLUMN)?
Returns the max value on a column
55
What is MIN(COLUMN)?
Returns the minimum value on a column
56
What is AVG(COLUMN)?
Returns the average value of the column
57
What is SUM(COLUMN)?
Returns the sum of the column
58
What is COUNT(COLUMN)?
Returns the count of elements in a column
59
What is TCL?
Transaction Control Language statements are utilized to manage transactions within a relational database.
60
What are some of the TCL Commands?
- COMMIT, any DML operations that were executed before the statements will be persisted permanently.
- ROLLBACK, any DML operations between two COMMIT statements will be completely erased (something like Ctrl + Z that will stop only when it reaches last time you opened the specific file). Committed transactions cannot be rollbacked.
- SAVEPOINT, utilized to ROLLBACK to a specific point in time.
The general flow of using TCL could be as follows:
[Many DML Operations]
SAVEPOINT A
[Many DML Operations]
ROLLBACK TO A
61
What is a Schema?
A database schema refers to the formal structure of data defined in a relational database. This includes the various tables in the database as well as their columns, data types, and the relationship between tables.
62
How are schemas enforced?
Schemas are enforced using constraints when defining tables, and we can visualize the schema of relational databases through entity-relationship diagrams, or ERDs.
63
What are some of the constraints in SQL?
We can put integrity constraints on specific columns in our database when defining tables, which allow us to enforce the schema by ensuring consistency and integrity of the data in the table. The different constraints are listed below:
```
PRIMARY KEY
FOREIGN KEY
NOT NULL
UNIQUE
CHECK
DEFAULT
AUTO INCREMENT
```
64
What is a PRIMARY KEY?
A primary key is a constraint that uniquely identifies a record in a table. Often, this constraint will be enforced on some sort of "ID" field, such as "employee_id". A primary key is inherently composed of two other constraints - unique and not null. Thus, a primary key MUST be provided when inserting a record into a table, unless the RDBMS system is generating it automatically behind the scenes.
65
What is a FOREIGN KEY?
A foreign key constraint signifies that a column represents a reference to the primary key of another table. This allows us to create relationships between tables. For example, if we are modeling cars and the owners of those cars, we might have a Car table with an owner_id foreign key that references the user_id field in the People table. We can then lookup the owner of any car by fetching the owner_id of the car and finding the matching user_id in the People table.
66
What is NOT NULL?
A not null constraint simply enforces that all records must have a field for the column on which this constraint is applied. For example, we know that every person has a social security number, so we might want to consider placing a not null constraint on that field in our users table (assuming we want to store the social security numbers). This prevents users of the database from leaving the table in an inconsistent or invalid state.
67
What is UNIQUE?
The unique constraint works similarly - records cannot be inserted if another record already has the same value for the column on which this is declared.
68
What is the CHECK constraint?
The check constraint provides a way of performing validation on values before records are entered into the table. For example, we may want to ensure that a bank account can never have a negative balance, so we might set a check constraint (CHECK (balance >= 0)).
69
What is the DEFAULT constraint?
Finally, a default constraint allows setting default values on columns for records that are inserted into the table
70
What is the AUTO-INCREMENT constraint?
Finally Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Very often the primary key of a table needs to be created automatically, and we define that field as AUTO INCREMENT field. Following is the syntax for creating an AUTO INCREMENT field.
CREATE TABLE TableName (
Column1 DataType AUTO_INCREMENT PRIMARY KEY,
Column2 DataType,
);
71
What are Candidate Keys?
Usually a primary key consists of a single column; however, sometimes we may have a scenario in which there could be multiple columns that together create a primary key to uniquely identify rows. We call these candidate keys.
72
What are Composite Keys?
Once we identify the actual combination of columns to use as the primary key, we call this a composite key.
For example, if you are modeling your CD collection, you might have fields such as track_no, album_id, and genre in the Track table. The track_no itself cannot work as a primary key because many different albums can have a track #1, for example. So we would need to create a composite key here, consisting of the track_no and the album_id columns. Using both of these columns together we can find the specific track we are looking for.
73
What is DDL?
Data Definition Language statements are utilized to define the database schema or skeleton. It is how we implement the design structure of it. Some of the keywords in this sublanguage are:
Some keywords are:
CREATE, to create new objects or tables.
CREATE TABLE TABLE_NAME (C_NAME C_TYPE C_SYZE [NULL | NOT NULL], [CONSTRAINT])
ALTER, to modify existing objects or tables.
ALTER TABLE TABLE_NAME [ADD | MODIFY | DROP] C_NAME
ALTER USER IDENTIFIED BY PASSWORD
DROP, to delete existing objects or tables.
DROP TABLE TABLE_NAME [CASCADE]
TRUNCATE, to delete all the data existing within a table leaving the skeleton of the table only.
TRUNCATE TABLE_NAME
This is similar to performing DELETE TABLE with no where clause, the key difference is that TRUNCATE commits at the end of the operation.
All DDL operations cannot be rolled back, which means that any change made by these are permanent.
74
What does normalization refer to?
Normalization refers to an optimization process of structuring a relational database in a way that reduces redundancy of data and improves data integrity and consistency.
75
What is the first normal form?
1NF - must have a primary key, no repeating groups, and atomic columns
The first normal form enforces that a table must:
- Have a primary key
- Each column should be as granular as possible (e.g. "Name" column should be broken up into: "First Name", "Last Name", "Middle Name", etc..)
○ granularizing the table information
○ Needs to have a primary key
76
What is the second normal form?
To be in second normal form, a table must also:
- Cannot have columns that are dependent on only one part of the key
- If there are no composite primary keys, you are automatically in 2NF
2NF - must already be in 1NF, plus have no partial dependencies
○ Must be in first normal form
○ No partial dependencies - no composite key, no partial dependencies
77
What is the third normal form?
To get to third normal form, a table must also:
- Not have transitive dependencies
- This means that if column C relates to column B which relates to column A which is the primary key, this is not in 3NF because C is related to the primary key but indirectly (it is a transitive dependency)
3NF - must already be in 2NF, plus have no transitive dependencies
○ Must be in second normal form
○ No transitive dependencies - which means
78
What is a good way to memorize the 3 forms of normalization?
A good way of remembering these normal forms in order is to remember the legal proceeding of swearing to tell the truth, the whole truth, and nothing but the truth. In relational databases, we must have the key (1NF), the whole key (2NF), and nothing but the key (3NF) so help me Codd.
79
What is a JDK?
JDK includes source files, includes the debugger and the compiler to develop code efficiently,
80
What is a JRE?
JRE - it is the surrounding environment and holds the core functionality
81
What is a JVM?
JVM - it can be portable and runs in a particular type of way, Scala and Kotlin are other types of systems that use the JVM
82
What is a native?
It's a C or C++ implementation that is implemented in the JVM
83
Is java pass by value or pass by reference?
It is pass-by-value
84
What is a table in database management?
It is a series of columns and rows
| Think of tables as your classes and rows as objects
85
What is DML?
- It is data manipulation language, to manipulate data
- It is how we are going to use to work with the objects using the CRUD operations
- CRUD: create, read, update, delete
- What are the main keywords? Insert, select, update, delete
86
What is DDL?
- Data definition language, for creating and altering objects in the DMS (tables, functions, schemas, databases, users, roles, triggers, etc)
- Keywords: create, alter, truncate, drop
87
What is DCL?
- Sub-language that controls, Data Control Language
| - Keywords: grant, revoke
88
What is BCNF?
○ Boyce Codd Normal Form
○ States that every field should rely on the primary key
○ The key, the whole key, nothing but the key, so help me Codd
89
What is the fourth normal form?
○ Must be BCNF
| ○ No multi valued dependencies
90
What is referential integrity?
One table cannot point to a non existent entry in another table
91
What is an orphaned (Batman) record?
- No orphans allowed, must eliminate the child first before getting rid of the parent record
- When a child table points to a parent table, and you want to delete the parent table. The child must either point to a different record in the parent table or be deleted first to avoid orphaned records
92
What is Multiplicity?
- Relationship between tables
- 1-m or m-1: foreign key goes in the many table
- M-m - intermediate table, aka junction table, to hold foreign keys of the other two tables
1-1 - in order to achieve this, make the column unique, no matter the table
93
What is Multiplicity?
- Relationship between tables
- 1-m or m-1: foreign key goes in the many table
- M-m - intermediate table, aka junction table, to hold foreign keys of the other two tables
- 1-1 - in order to achieve this, make the column unique, no matter the table
94
What is TCL?
Transaction control language
- Keywords: begin, end, savepoint, rollback, commit
- Begin: tells to open transaction block, talks to postgres
- Commit - tells it ready to be persisted, enter into the database
- Rollback - don't like so goes back to the last commit
- Savepoint - a saving point but not yet committed
95
What does Begin do in TCL?
- Begin will create the transaction block and gather the transaction information from the dbms, tells to open transaction block, talks to postgres
96
What does End do in TCL?
- End will end the transaction block,
97
What does Savepoint do in TCL?
- Savepoint will create savepoints within the transaction that you can rollback to if needed; a saving point but not yet committed
98
What is a Rollback in TCL?
- Rollback will revert the changes back to the original state of the transaction or to a state in a savepoint; don't like so goes back to the last commit
99
What is a Commit in TCL?
- Commit will commit the changes and end the transaction, sending the chagnes to the db for persistent alterations; tells it ready to be persisted, enter into the database
100
How can table relationships be defined?
By using foreign key constraints
101
What are several different kinds of relations that exist in a relational database?
1-to-1
1-to-M/M-to-1
M-to-M
102
What is a one-to-one relationship?
A one-to-one relationship means that each entity in the table only relates to a single entity in the other table.
Ex:
If we are modeling a school, where each classroom has a single projector in it, we would want to make this relationship a one to one between the Classroom and the Projector tables. In our database, we can provide the classroom table a projector_id foreign key and provide the projector table a classroom_id foreign key.
To enforce the one to one aspect, we should also apply a unique constraint on the foreign key columns. Otherwise, a user could add another projector record with the same classroom_id as an existing record, and then our one to one relationship would be broken.
103
What is a one-to-many relationship?
A one-to-many (or vice versa, many to one) relationships is where one entity can belong to, own, or otherwise relate to multiple other entities.
Ex:
A Student could have many books, so this would be a one-to-many relationship. To create this in the database, we add the foreign key only on the many sides of the relationship - so a book entity would have a field such as student_id as a foreign key to identify the owning student.
104
What is a many-to-many relationship?
A many-to-many relationship implies a one-to-many relationship in both directions on the entities.
Ex:
A Teacher can have many Students, but a Student could have many Teachers as well. In this case, we cannot provide a direct link between the tables in the database - instead, we need to create what is called a junction table or bridge table to relate the two tables.
So, in our student-teacher example, we could create a Class table which contains two foreign keys - one that refers to the Teacher table's primary key and one that refers to the Student table's primary key. This creates a list of unique Teacher-Student mappings that can be used to look up which students a particular teacher teaches, or which teachers a particular student has.
105
What is referential integrity?
When we create table relationships, it is important that our data remains in a consistent state throughout the database. For example, we never want a record on our class table to be pointing to a record in either the Teacher or the Student table that does not exist. We call enforcing this property as maintaining referential integrity.
106
What is an orphan record?
When we break referential integrity, we will find orphan records in the database - these are records whose foreign keys do not point to an existing record in the other table.
107
What is a way to prevent orphan records, breaking referential integrity?
One way of preventing this from occuring is by using a setting called CASCADE DELETE - when we enable this, deleting a record in the table will also cascade that operation and delete any records in tables that reference the that record via foreign keys.
108
What are the three forms of normalization and describe them?
Normalization is the process of designing your database in such a way that you have no composite columns, little to no redundant data, and that your data dependencies make sense.
1. To reach First Normal Form, we must get rid of composite columns, and we must have a primary key
2. To reach Second Normal Form, we create separate tables to track data that causes duplication of otherwise unique data.
3. To get to Third Normal Form, you promise that, every column will describe the key, the whole key, and nothing but the key!
109
What are the 4 properties that a transaction has?
A - Atomicity
C - Consistency
I - Isolation
D - Durability
110
What is a transaction?
Any amount of DML statements before a COMMIT statement is considered a transaction
Transactions are considered to be logical units of work
111
What is ATOMICITY in a transaction?
Atomicity - Atomicity means that either all of the transactions will execute successfully or none of them will.
- "All or nothing", if any statement on the transaction fails, the whole transaction fails.
- transactions when all goes in or none goes in, all of the transaction persists, or none
112
What is CONSISTENT in a transaction?
Consistency - Consistency means that constraints are enforced for every committed transaction. That indicates that all Keys, Data types, Checks, and Triggers are successful and no constraint violation is triggered.
CONSISTENT - If the database was in a consistent state before the transaction, it should be after it.
- If the database was in a consistent state before the transaction, it should be after it.
- the state of the database will change and persist through a transaction
113
What is ISOLATED?
Isolation - If two transactions are executing concurrently and working on the same data, then one transaction should not disturb the other transaction. Isolation guarantees that concurrently running transactions should not affect each other.
ISOLATED - One transaction shouldn't affect other transactions. It can be applied in different levels.
- One transaction shouldn't affect other transactions. It can be applied in different levels.
114
What is DURABLE?
Durability - Durability means that once a transaction is complete, it guarantees that all of the changes are recorded in the database. If our system is suddenly affected by a system crash or a power outage, then all unfinished committed transactions may be replayed.
DURABLE - Persisted data should be saved permanently, even in the case of power loss or catastrophic software or hardware failure.
115
What are the isolation levels?
- serializable
- repeatable reads
- read committed
read uncommitted
116
What is serializable?
Serializable
- Allowed in Oracle
- Read/Write locks
- Applies range locks even in the WHERE clauses of a select statement
- Phantom reads can't happen because of this
- Table that is being read can't be modified until the reading is done (no INSERTS, no UPDATES, no DELETES)
117
What are repeatable reads?
Repeatable Reads
- Not used often
- Read/Write locks
- Doesn't provide range locks, that means phantom reads can happen
- Doesn't lock the whole SELECT statement, nor INSERTS, nor UPDATES, nor DELETS
118
What are read committed?
Read Committed
- Oracle default
- Write only locks
- Only data that is committed will be seen by other transactions
- Dirty reads can't happen, but Phantom reads can
- This is why it is recommended to not perform very long transactions
119
What is read uncommitted?
Read Uncommitted
- A disaster
- Dirty reads are normal, any transaction can see any uncommitted data
- Very inconsistent
120
What are the 3 read phenomena?
- Dirty Read
- Non-repeatable read
- Phantom read
121
What is a dirty-read?
Dirty Read: reading data that is uncommitted
122
What is a Non-repeatable read?
Non-repeatable read: when a row is read twice in a transaction and the values are different
123
What is a phantom read?
Phantom Read: reading data that is being added or modified by a running transaction
124
What is DCL?
Data Control Language - shows who can control which information; used to manage the security and control of database systems.
Keywords: GRANT, REVOKE:
125
What does the GRANT command do?
GRANT, to grant any permissions to an existing user.
| GRANT PERMISSION TO USERNAME
126
What does the REVOKE command do?
REVOKE, to revoke any permissions of an existing user.
| REVOKE PERMISSION TO USERNAME
127
What does PL/SQL stand for?
PL/SQL stands for the Procedural Language extension to SQL and it is a complete programming language which also allows SQL statements.
128
What is a sequence?
Sequence
A sequence is an object which holds a numeric number that starts from a certain point and it also contains a max. It increments by a specific amount every time NEXTVAL is called.
They can be combined with Triggers to auto increment primary key columns.
CREATE [OR REPLACE] SEQUENCE START WITH 1 INCREMENT BY 1
129
What are triggers?
Trigger
A trigger is a block of code that executes when a specific event happens. These events can be INSERT, UPDATE or DELETE statements, and they can happen AFTER or BEFORE.
Syntax:
```
CREATE [OR REPLACE] TRIGGER
BEFORE INSERT ON TABLE_NAME
FOR EACH ROW
BEGIN
(PL/SQL code)
END;
```
130
What is a cursor?
Pointers to a result set. They can be used to loop programmatically on the output of a SELECT statement (similar to iterators in Java).
Oracle provides SYS_REFCURSOR, its own type of REFCURSOR. This means you can create your own type of REFCURSOR
131
What is a Stored Procedure and what are some of its properties?
Stored Procedure
PL/SQL code that can be executed in certain ways and has some properties:
- They don't return anything.
- They may or may not contain IN (by value) and OUT (by reference) parameters.
- They allow any DML statements within.
- These means transactions can be created in a stored procedure.
- Stored procedures can call other procedures and functions.
- Can NOT use stored procedures in DML statements.
EXEC STORED_PROCEDURE
132
What is a User Defined Function?
Function:
User Defined Functions, these are like stored procedures but have some other restrictions or abilities:
- They must return something.
- Cursors are allowed.
- It should be a single value.
- They may or may not contain IN parameters (by default).
- Only SELECT statements are allowed.
- Functions can only call other functions (no stored procedures).
- They can be used in any DML statement.
- To call a function, you have to use a DML statement (you can't EXEC).
- Use FROM DUAL if you are not selecting from a specific table.
- DUAL is a dummy table which returns anything your throw at it.
133
What are PL/SQL Types?
Types:
Similar to classes, they can be used as your own datatypes for columns. We are not going to use this, because this is a high level technique which doesn't follow the normal forms.
134
What are PL/SQL VARRAY?
VARRAY
| Arrays of any Oracle data type or your own type, they can be used for columns.
135
What are Nested Tables in PL/SQL?
Nested Table
| Infinite arrays, they can be seen as tables in columns. They have to be of a specific data type
136
What are the 5 set operators?
- UNION
- UNION ALL
- INTERSECT
- MINUS
- EXCEPT
137
What does UNION and UNION ALL do?
UNION does not keep duplicates, but UNION ALL will
138
What is an INTERSECT?
Only returns records in common between the queries
139
What is a MINUS?
Removes from the first result set any rows that appear in the second result set and returns what remains
140
What is an EXCEPT?
Same as MINUS, but for SQLServer instead of Oracle, Removes from the first result set any rows that appear in the second result set and returns what remains
141
What are SET OPERATORS?
Set operators are different from joins. Instead of combining columns of two tables, set operators combine the rows of different result sets. Essentially, set operators perform some kind of (set) operation on two different queries.
TOP -> DOWN table model instead of a SIDE-TO-SIDE
