Brainscape's Knowledge GenomeTM

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


See full index

Java > SQL > Flashcards

SQL Flashcards

1
Q

WHAT IS CRUD

A

CREATE
READ
UPDATE
DELETE

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

crud CREATE

A

MAKES NEW DATA

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

crud READ

A

ACCESS DATA

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

crud UPDATE

A

MAKE CHANGES TO DATA

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

crud DELETE

A

REMOVE DATA

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

SUB LANGUAGES OF SQL

A 
DDL - DATA DEFINITION LANGUAGE
DML- DATA MANIPULATION LANGUAGE 
DQL - DATA QUERY LANGUAGE
DCL - DATA CONTROL LANGUAGE
TCL TRANSACTION CONTROL LANGUAGE
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

DDL - DATA DEFINITION LANGUAGE

A

THE STRUCTURE OF THE DATABASE.
AUTO COMMIT, CAN NOT BE ROLLED BACK

COMMANDS:
CREATE
ALTER
DROP
TRUNCATE
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

DML- DATA MANIPULATION LANGUAGE

A

THE DATA ENTRIES THEMSELVES
NOT AUTO COMMIT.

COMMANDS
INSERT
UPDATE
DELETE

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

DQL - DATA QUERY LANGUAGE

A

USED TO MAKE QUERIES IN DATABASES AND INFORMATION SYSTEMS.

COMMAND
SELECT

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

DCL - DATA CONTROL LANGUAGE

A

CONTROLS ACCESS OF WHO CAN SEE / USE DATA

COMMANDS
GRANT
REVOKE

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

TCL TRANSACTION CONTROL LANGUAGE

A

CONTROLS TRANSACTIONS

COMMANDS
COMMIT
SAVEPOINT
ROLLBACK

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

DDL CREATE

A

A PART OF DDL
CREATES/ADDS A TABLE TO THE DATABASE

CREATE TABLE BEAR(
BEAR_ID INTEGER PRIMARY KEY);

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

DDL ALTER

A

PART OF DDL
MAKES CHANGES TO THE TABLE

ALTER TABLE BEAR, DROP COLUMN D;

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

DDL DROP

A

PART OF DDL
DELETES A TABLE FROM THE DATABASE
DROP TABLE BEAR;

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

DDL TRUNCATE

A

PART OF DDL
DELETES DATA W/O DELETING THE TABLE

TRUNCATE TABLE BEAR_TABLE;

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

DML INSERT

A

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

DML UPDATE

A

PART OF DML
MODIFY A ROW (DATA MODIFICATION)

UPDATE BEAR BEAR_NAME = DALE ‘DALE’ WHERE NAME = ‘BOB’;

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

DML DELETE

A

PART OF DML
DELETE A ROW OR MULTIPLE ROWS

DELETE FROM BEAR WHERE NAME =’BOB’

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

SELECT

A

PART OF DQL
GRABS DATA FROM THE DATABASE

SELECT * FROM* - WILL GRAB EVERYTHING
SELECT BEAR_NAME FROM BEAR - WILL JUST GRAB THE NAME.

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

COMMIT

A

PART OF TCL

PERMANENT SAVE - NO GOING BACK

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

SAVEPOINT

A

PART OF TCL
SAVES A POINT LOCALLY TO YOUR COMPUTER NOT TO THE SERVER

SAVEPOINT(NAME OF SAVEPOINT)

22
Q

ROLLBACK

A

PART OF TCL
LETS YOU REVERT BACK TO LAST COMMIT OR SPECIFIED SAVEPOINT.

ROLLBACK(NAME OF SAVEPOINT)

23
Q

CAN YOU HAVE NON RELATIONAL DATABASES?

A

YES

24
Q

PRIMARY ID

A

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

25
Q

FOREIGN KEY

A

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

26
Q

REFERENTIAL INTEGRITY

A

Referential integrity ensures that the relationship between two tables remains synchronized during updates and deletes.

27
Q

referential integrity will prevent users from:

A

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.

28
Q

COMPOSITE KEY

A

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.

29
Q

SURROGATE KEY

A

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.

30
Q

NATURAL KEY

A

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.

31
Q

FIRST NORMAL FORM

A

THE INFORMATION IS STORED IN A RELATIONAL TABLE.
EACH COLUMN CONTAINS ATOMIC VALUES.
THERE ARE NO REPEATING COLUMNS

32
Q

SECOND NORMAL FORM

A

THE TABLE IS IN THE FIRST NORMAL FORM AND ALL THE COLUMNS DEPEND ON THE PRIMARY KEY

33
Q

THIRD NORMAL FORM

A

THE TABLE IS IN THE SECOND FORM AND ALL OF ITS COLUMNS ARE NOT TRANSITIVELY DEPENDENT ON THE PRIMARY KEY

34
Q

ATOMIC

A

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.

35
Q

RULES FOR THIRD NORMAL FORM

A

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

36
Q

DEFAULT CONTRAINT

A

IF YOU DON’T SPECIFY A VALUE THE TABLE WOULD DEFAULT TO THE NUMBER ASSIGNED

EX. BEAR_WEIGHT INTEGER DEFAULT 200

37
Q

Constraints

A

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.

38
Q

UNIQUE Constraint

A

Ensures that all values in a column are different.

39
Q

NOT NULL Constraint

A

Ensures that a column cannot have NULL value.

40
Q

CHECK Constraint

A

The CHECK constraint ensures that all the values in a column satisfies certain conditions.

41
Q

INDEX

A

Used to create and retrieve data from the database very quickly.

42
Q

CARDINALITY

A

how many elements are in a set.

43
Q

MULTIPLICITY

A

is made up of a lower and an upper cardinality

44
Q

Relational databases

A

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
45
Q

Non-Relational databases

A

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
46
Q

What are some special types of primary keys?

A

composite key
natural key
surrogate key

47
Q

What is an orphan record?

A

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

48
Q

What is a cascade delete?

A

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.

49
Q

What is normalization?

A

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.

50
Q

What is a transaction?

A

A transaction is a change made to your database. Essentially, every time we you see a
commit after 1 or more SQL statements.

51
Q

What is GRANT and REVOKE?

A

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;

Java (9 decks)

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