SQL

What are the types of relations between tables?

1-1 1-many many-many

What is a relational database?

A database which holds information in tables related to each other.

Command for removing data in a table:

DELETE

Command for adding data to a table:

INSERT INTO

Name the most used constraints in SQL(3):

1. Primary key 2. Foreign key 3. Not null

What does ACID stand for in SQL?

A - Atomic - all statements in a transaction succeed or none of them do. C - Consistent - The database moves from one consistent committed state to another. I - Isolated - Concurrent transactions do not cause consistency issues. D - Durable - Transactions fail gracefully and do not cause your database to crash or something to happen.

What is an orphan record in SQL?

A record that references a key that no longer exists.

What is normalization used for?

Reducing redundancy in a database.

What is a RDBMS and what is it used for?

Relational Database Management System - Software designed to manage a database.

What does a scalar function return?

Returns a single output from a single input. -round -trim -subsr -to_char

What is a constraint in SQL?

Rules used to specify what data can be added to a column.

Command for retrieving data from a table:

SELECT

What is SQL's DCL sub-language and what actions can you do?

Data Control Language - For granting permissions to users. -Grant -Revoke

What is SQL's DDL sub-language and what actions can you do?

Data Definition Language - Defines the rules and structures of a DB. -Create -Alter -Rename -Drop -Truncate

What is SQL's DML sub-language and what actions can you do?

Data Manipulation Language - Adds, removes, or edits data in the database. -Insert -Delete -Update

What is SQL's DQL sub-language and what actions can you do?

Data Query Language - For reading information. -Select

What is an ERD in SQL?

Entity Relationship Diagram- Data modeling technique that represents the relationship between tables.

What does JDBC stand for and why do we use it?

JDBC stands for Java Database Connectivity and we use it to connect to our database/run SQL commands in Java, which persists our data.

What does SQL stand for and what is it used for?

Structured Query Language - Used for managing data held in a RDBMS.

When creating many-to-many relations in SQL, we need to create a ______________ table where each table has a 1-many relationship.

junction

What is a SQL view?

Virtual table based on the result of a query.

What does an aggregate function return?

A single output from an array of inputs. -AVG -SUM -COUNT -MIN -MAX

What does it mean for a DB to be in 3NF?

There are no transitive dependencies - You cannot find a particular value somewhere else in the DB. All data is atomic. There are no functional dependencies.

What is SQL's TCL sub-language and what actions can you do?

Transaction Control Language - For generating transactions. -Commit -Rollback -Savepoint

Command for updating records in a table:

UPDATE SET

What is the difference between UNION and UNION ALL?

Union - Doesn't include duplicate data. Union all - Includes duplicate data/rows.