Databases

Question 1

define: data

Answer 1

“known facts that can be recorded and have implicit meaning”

Question 2

define: database

Answer 2

collection of related data

Question 3

define: DBMS

Answer 3

acronym for DataBase Management System

a collection of programs that enables users to create and maintain a database with processes for creating and maintaining a database (that would otherwise be manually maintained)

These are the processes:
- defining (info type, structures, constraints)
- construction (process of storing data)
- manipulation (query, update, generate reports)
- sharing (between multiple users)

Question 4

define: database system

Answer 4

database + DBMS

Question 5

define: DBA

Answer 5

aka DataBase Administrator

Question 6

define: persistent object

Answer 6

permanent

Question 7

What does SQL stand for?

Answer 7

Structured Query Language

Question 8

What is SQL?

Answer 8

programming language designed to manage data stored in relational databases

Question 9

How does SQL operate?

Answer 9

through simple, declarative statements

Question 10

Why does SQL operate through simple, declarative statements?

Answer 10

keeps data accurate and secure, and helps maintain the integrity of databases, regardless of size.

Question 11

What is a relational database?

Answer 11

A database that organizes information into one or more tables

Question 12

What is a table?

Answer 12

A table is a collection of data organized into rows and columns.

Question 13

What’s another word for tables?

Answer 13

Tables are sometimes referred to as relations.

Question 14

Identify the parts of this diagram in SQL terms

Answer 14

Refer to attached picture

Question 15

How can you create a table from scratch?

Relation: celebs with properties
id (integer)
name
age (integer)

Answer 15

Question 16

How do you insert a new row into a table? Given this data

1, Justin Beiber, 22
2, Beyonce Knowles, 33
3, Jeremy Lin, 27
4, Taylor Swift, 30

Answer 16

Question 17

What’s the faster way to insert multiple rows into a table?

Answer 17

Question 18

What happens if we try to create a table with an existing name?

Answer 18

SQLite is case insensitive for most syntax
it will throw an error, because the table name already exists

https://discuss.codecademy.com/t/what-happens-if-we-try-to-create-a-table-with-an-existing-name/376312

Question 19

How do you return column ‘name’ from relation ‘celebs’?

Answer 19

SELECT name FROM celebs;

Question 20

What does SELECT statement do?

Answer 20

always return a new table called the result set

Question 21

How do you select all data from relation?

Answer 21

SELECT * FROM celebs;

Question 22

How would you add a new column to celebs table

twitter_handle
data type: text

Answer 22

Question 23

What is NULL?

Answer 23

A special value in SQL that represents missing or unknown data.
∅

Question 24

Can we change the order a column is added to a table?

Answer 24

No. By default, a new column will always be added at the end of the table

however, you can always select the columns in any order
SELECT col3, col1, col2

source: https://discuss.codecademy.com/t/can-we-add-a-column-at-a-specific-position-to-a-table/376656

Question 25

How would you change Taylor Swift’s twitter handle to ‘@taylorswift13’ ?

Answer 25

UPDATE celebs
SET twitter_handle = ‘@taylorswift13’
WHERE id = 4;

1. UPDATE is a clause that edits a row in the table.
2. celebs is the name of the table.
3. SET is a clause that indicates the column to edit.

Question 26

ALTER vs UPDATE statement

Answer 26

The ALTER statement is used to modify columns. With ALTER, you can add columns, remove them, or even modify them.

The UPDATE statement is used to modify rows. However, UPDATE can only update a row, and cannot remove or add rows.

https://discuss.codecademy.com/t/how-is-alter-different-from-update/376655

Question 27

How to delete existing records in the celebs table with no twitter_handle

Answer 27

DELETE FROM celebs
WHERE twitter_handle IS NULL;

Question 28

How do you delete only a certain number of rows?

Answer 28

DELETE FROM table
WHERE condition
LIMIT 5;

Question 29

What are constraints?

Answer 29

rules/restrictions around what kind of data type can be input into a table. Rejects data that doesn’t stick to the rules

Question 30

What does the PRIMARY KEY constraint do?

Answer 30

Uniquely identify the row.

Attempts to insert a row with an identical value to a row already in the table will result in a constraint violation which will not allow you to insert the new row.

Question 31

What does the UNIQUE constraint do?

Answer 31

UNIQUE columns have a different value for every row.

Question 32

PRIMARY KEY vs UNIQUE

Answer 32

A table can have many different UNIQUE columns, there can only be one PRIMARY KEY

Question 33

What does the NOT NULL constraint do?

Answer 33

NOT NULL columns must have a value.

Attempts to insert a row without a value for a NOT NULL column will result in a constraint violation and the new row will not be inserted.

Question 34

What does DEFAULT do?

Answer 34

DEFAULT columns take an additional argument that will be the assumed value for an inserted row if the new row does not specify a value for that column.

Question 35

How would you add the following constraints for the celebs table?

id is primary key
name is unique
date_of_birth must have a value
date_of_death must have a default value

Answer 35

CREATE TABLE celebs (
id INTEGER PRIMARY KEY,
name TEXT UNIQUE,
date_of_birth TEXT NOT NULL,
date_of_death TEXT DEFAULT ‘Not Applicable’
);

Question 36

What command should you use to permanently remove a table from a database?

Answer 36

DROP TABLE

Question 37

When inserting, we need to check which constraint?

Answer 37

➢ that the candidate keys are not already present,
➢ that the value of each foreign key either
– is all NULL, or
– is all non-NULL and occurs in the referenced relation

Question 38

When deleting, we need to check which constraint?

Answer 38

referential integrity – check whether the primary key
occurs in another relation.

(slide 28, lec 2.1)

Question 39

define relational database schema

Answer 39

set of relation schema
{R1, . . . , Rm} and a set of integrity constraints.

(slide 31, lec 2.1)

Question 40

relational database instance is a

Answer 40

set of relation instances {r1, . . . , rm} such that each ri
is an instance of Ri, and the integrity constraints are satisfied.

(slide 31, lec 2.1)