SQL Flashcards
WHAT IS CRUD
CREATE
READ
UPDATE
DELETE
crud CREATE
MAKES NEW DATA
crud READ
ACCESS DATA
crud UPDATE
MAKE CHANGES TO DATA
crud DELETE
REMOVE DATA
SUB LANGUAGES OF SQL
DDL - DATA DEFINITION LANGUAGE DML- DATA MANIPULATION LANGUAGE DQL - DATA QUERY LANGUAGE DCL - DATA CONTROL LANGUAGE TCL TRANSACTION CONTROL LANGUAGE
DDL - DATA DEFINITION LANGUAGE
THE STRUCTURE OF THE DATABASE.
AUTO COMMIT, CAN NOT BE ROLLED BACK
COMMANDS: CREATE ALTER DROP TRUNCATE
DML- DATA MANIPULATION LANGUAGE
THE DATA ENTRIES THEMSELVES
NOT AUTO COMMIT.
COMMANDS
INSERT
UPDATE
DELETE
DQL - DATA QUERY LANGUAGE
USED TO MAKE QUERIES IN DATABASES AND INFORMATION SYSTEMS.
COMMAND
SELECT
DCL - DATA CONTROL LANGUAGE
CONTROLS ACCESS OF WHO CAN SEE / USE DATA
COMMANDS
GRANT
REVOKE
TCL TRANSACTION CONTROL LANGUAGE
CONTROLS TRANSACTIONS
COMMANDS
COMMIT
SAVEPOINT
ROLLBACK
DDL CREATE
A PART OF DDL
CREATES/ADDS A TABLE TO THE DATABASE
CREATE TABLE BEAR(
BEAR_ID INTEGER PRIMARY KEY);
DDL ALTER
PART OF DDL
MAKES CHANGES TO THE TABLE
ALTER TABLE BEAR, DROP COLUMN D;
DDL DROP
PART OF DDL
DELETES A TABLE FROM THE DATABASE
DROP TABLE BEAR;
DDL TRUNCATE
PART OF DDL
DELETES DATA W/O DELETING THE TABLE
TRUNCATE TABLE BEAR_TABLE;
DML INSERT
PART OF DML
INSERTING A ROW (ROW DATA ENTRY)
INSERT INTO BEAR_TYPE VALUES (1, ‘BROWN’);
INSERT DATA FROM ANOTHER TABLE
INSERT INTO BEAR_TYPE VALUES (1, 'BROWN')
[(COLUMN1, COLUMN2)]
SELECT {('COLUMN1', 'COLUMN2')]
FROM TABLE_NAME2
[WHERE CONDITIONS]
DML UPDATE
PART OF DML
MODIFY A ROW (DATA MODIFICATION)
UPDATE BEAR BEAR_NAME = DALE ‘DALE’ WHERE NAME = ‘BOB’;
DML DELETE
PART OF DML
DELETE A ROW OR MULTIPLE ROWS
DELETE FROM BEAR WHERE NAME =’BOB’
SELECT
PART OF DQL
GRABS DATA FROM THE DATABASE
SELECT * FROM* - WILL GRAB EVERYTHING
SELECT BEAR_NAME FROM BEAR - WILL JUST GRAB THE NAME.
COMMIT
PART OF TCL
PERMANENT SAVE - NO GOING BACK
SAVEPOINT
PART OF TCL
SAVES A POINT LOCALLY TO YOUR COMPUTER NOT TO THE SERVER
SAVEPOINT(NAME OF SAVEPOINT)
ROLLBACK
PART OF TCL
LETS YOU REVERT BACK TO LAST COMMIT OR SPECIFIED SAVEPOINT.
ROLLBACK(NAME OF SAVEPOINT)
CAN YOU HAVE NON RELATIONAL DATABASES?
YES
PRIMARY ID
A UNIQUE # ASSIGNED TO A COLUMN IN DATABASE TABLE.
MUST BE UNIQUE AND NOT NULL
YOU NEVER WANT TO CHANGE PRIMARY KEY AS IT CAN BREAK REFERENTIAL INTEGRITY
FOREIGN KEY
IS A CONSTRAINT THAT’S ADDED TO A TABLE.
IT ALLOWS YOU TO SPECIFY THAT A COLUMN IN A TABLE REFERS TO THE PRIMARY KEY OF ANOTHER TABLE.
THEY ARE UNIQUE AND NOT NULL
REFERENTIAL INTEGRITY
Referential integrity ensures that the relationship between two tables remains synchronized during updates and deletes.
referential integrity will prevent users from:
Adding records to a related table if there is no associated record in the primary table.
Changing values in a primary table that result in orphaned records in a related table.
Deleting records from a primary table if there are matching related records.
COMPOSITE KEY
IS A COMBINATION OF TWO OR MORE COLUMNS IN A TABLE THAT CAN BE USED TO UNIQUELY IDENTIFY EACH ROW IN THE TABLE WHEN THE COLUMNS ARE COMBINED.
SURROGATE KEY
ANY COLUMN OR SET OF COLUMNS THAT CAN BE DECLARED AS THE PRIMARY KEY, INSTEAD OF A REAL KEY.
Value generated solely for the purpose of becoming a primary key.
NATURAL KEY
A key that is formed of attributes that already exist in the real world
ex SSN or Drivers License
Value that already exists in our table being used as a primary key.
FIRST NORMAL FORM
THE INFORMATION IS STORED IN A RELATIONAL TABLE.
EACH COLUMN CONTAINS ATOMIC VALUES.
THERE ARE NO REPEATING COLUMNS
SECOND NORMAL FORM
THE TABLE IS IN THE FIRST NORMAL FORM AND ALL THE COLUMNS DEPEND ON THE PRIMARY KEY
THIRD NORMAL FORM
THE TABLE IS IN THE SECOND FORM AND ALL OF ITS COLUMNS ARE NOT TRANSITIVELY DEPENDENT ON THE PRIMARY KEY
ATOMIC
MAKING SURE EACH COLUMN HOLDS THE SMALLEST AMOUNT OF INFORMATION
EX INSTEAD FIRST AND LAST NAME AS 1 COLUMN
IT SHOULD BE COLUMN: FIRST NAME, COLUMN: LAST NAME.
RULES FOR THIRD NORMAL FORM
THERE ARE NO INTERNAL DEPENDENCIES WITH THE COLUMNS.
MUST MAKE A NEW TABLE IF THERE’S AN INTERNAL DEPENDENCY, EVEN IF THEY ARE RELATED TO THE PRIMARY KEY.8
DEFAULT CONTRAINT
IF YOU DON’T SPECIFY A VALUE THE TABLE WOULD DEFAULT TO THE NUMBER ASSIGNED
EX. BEAR_WEIGHT INTEGER DEFAULT 200
Constraints
Constraints are the rules enforced on the data columns of a table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.
Constraints could be either on a column level or a table level. The column level constraints are applied only to one column, whereas the table level constraints are applied to the whole table.
UNIQUE Constraint
Ensures that all values in a column are different.
NOT NULL Constraint
Ensures that a column cannot have NULL value.
CHECK Constraint
The CHECK constraint ensures that all the values in a column satisfies certain conditions.
INDEX
Used to create and retrieve data from the database very quickly.
CARDINALITY
how many elements are in a set.
MULTIPLICITY
is made up of a lower and an upper cardinality
Relational databases
databases are structured. You have tables and these tables may have dependencies on each other, or relationship
Popular Relational SQL Database Systems Microsoft SQL Server Oracle MySQL / MariaDB PostgreSQL Microsoft Azure SQL
Non-Relational databases
databases are document-oriented. This so called document type storage allows multiple ‘categories’ of data to be stored in one construct or Document.
Popular Non-Relational No-SQL Databases MongoDB Oracle NoSQL Apache CouchDB Redis
What are some special types of primary keys?
composite key
natural key
surrogate key
What is an orphan record?
An orphan record refers to a record whose foreign key references a deleted/nonexistant
record.
In Oracle SQL, we can’t have orphan records because it enforces referential integrity
What is a cascade delete?
A foreign key with cascade delete means that if a record in the parent table is deleted,
then the corresponding records in the child table will automatically be deleted.
What is normalization?
Normalization is the process of organizing data in a database. We use normalization to
eliminate redundancy and inconsistent dependencies. There are six normal forms,
though many organizations only use the first three.
What is a transaction?
A transaction is a change made to your database. Essentially, every time we you see a
commit after 1 or more SQL statements.
What is GRANT and REVOKE?
GRANT and REVOKE deal with read and write privileges for a user within the DB.
GRANT privileges ON object TO user;
REVOKE privileges ON object FROM user;
