Creating tables and loading data

Question 1

What are SQL statements used for?

Answer 1

Interacting with tables, columns, and rows in relational databases

Question 2

What are the two main categories of SQL statements?

Answer 2

Data Definition Language (DDL) and Data Manipulation Language (DML)

Question 3

What does DDL stand for?

Answer 3

Data Definition Language

Question 4

What does DML stand for?

Answer 4

Data Manipulation Language

Question 5

What are DDL statements used for?

Answer 5

Defining, changing, or deleting database objects like tables

Question 6

Name four common DDL statements

Answer 6

CREATE, ALTER, TRUNCATE, DROP

Question 7

What does CREATE do in SQL?

Answer 7

Creates tables and defines columns

Question 8

What does ALTER do in SQL?

Answer 8

Modifies table structure (add/drop columns, change data types)

Question 9

What does TRUNCATE do in SQL?

Answer 9

Deletes all data in a table but keeps the table structure

Question 10

What does DROP do in SQL?

Answer 10

Deletes a table completely

Question 11

What are DML statements used for?

Answer 11

Reading and modifying data in tables

Question 12

What does CRUD stand for?

Answer 12

Create, Read, Update, Delete

Question 13

Name four common DML statements

Answer 13

INSERT, SELECT, UPDATE, DELETE

Question 14

What does INSERT do?

Answer 14

Adds new rows to a table

Question 15

What does SELECT do?

Answer 15

Retrieves data from a table

Question 16

What does UPDATE do?

Answer 16

Modifies existing rows in a table

Question 17

What does DELETE do?

Answer 17

Removes rows from a table

Question 18

Which category does CREATE belong to?

Answer 18

DDL

Question 19

Which category does INSERT belong to?

Answer 19

DML

Question 20

Which category does DROP belong to?

Answer 20

DDL

Question 21

Which category does SELECT belong to?

Answer 21

DML

Question 22

What is the key difference between DDL and DML?

Answer 22

DDL changes structure; DML changes data

Question 23

What are key considerations before creating a table?

Answer 23

Choose schema, define table name, define column names and data types, decide on NULLs and duplicates, reference ERD

Question 24

Why are schemas important when creating tables?

Answer 24

They logically organize database objects

Question 25

What information is needed before creating a table?

Answer 25

Table name, column names, data types, constraints

Question 26

What tool helps design tables before creation?

Answer 26

Entity Relationship Diagram (ERD)

Question 27

What are three common methods for creating tables?

Answer 27

UI tools, SQL statements, APIs

Question 28

Which SQL statement is used to create a table?

Answer 28

CREATE TABLE

Question 29

When is using a UI tool for table creation useful?

Answer 29

For small-scale or occasional tasks

Question 30

Give an example of a Db2 UI tool

Answer 30

Db2 on Cloud console

Question 31

Give an example of a MySQL UI tool

Answer 31

phpMyAdmin

Question 32

Give an example of a PostgreSQL UI tool

Answer 32

pgAdmin

Question 33

Why use SQL scripts for table creation?

Answer 33

To automate creation of multiple tables

Question 34

What do administrative APIs allow?

Answer 34

Programmatic creation and management of tables

Question 35

What is the first step in creating a table in Db2 on Cloud?

Answer 35

Select a schema

Question 36

What is the default schema in Db2?

Answer 36

The username

Question 37

What is a fully qualified table name?

Answer 37

SchemaName.TableName

Question 38

What must you define when adding a column?

Answer 38

Name, data type, NULL setting, length/scale

Question 39

What action finalizes table creation in Db2 on Cloud?

Answer 39

Click Create

Question 40

Can a table structure be modified after creation?

Answer 40

Yes

Question 41

Which SQL command modifies a table structure?

Answer 41

ALTER TABLE

Question 42

What does DROP TABLE do?

Answer 42

Deletes the table

Question 43

What can you modify after table creation?

Answer 43

Add columns, set constraints, alter structure

Question 44

What are post-creation actions available?

Answer 44

Drop table, generate SQL, modify structure, explore dependencies

Question 45

What does NULL setting determine?

Answer 45

Whether a column can store empty values

Question 46

Why confirm all table details before creation?

Answer 46

To avoid later structural issues

Question 47

What type of SQL statement is CREATE TABLE?

Answer 47

Data Definition Language (DDL)

Question 48

What is the purpose of CREATE TABLE?

Answer 48

To create a new table in a database

Question 49

What comes immediately after CREATE TABLE in the syntax?

Answer 49

The table name

Question 50

Where are column definitions written in CREATE TABLE?

Answer 50

Inside parentheses ()

Question 51

How are column definitions separated?

Answer 51

With commas

Question 52

What must each column definition include?

Answer 52

Column name and data type

Question 53

What optional constraints can be added to columns?

Answer 53

PRIMARY KEY, NOT NULL, etc.

Question 54

What does PRIMARY KEY ensure?

Answer 54

Uniqueness and no duplicate values

Question 55

What does NOT NULL ensure?

Answer 55

The column cannot contain NULL values

Question 56

What is the difference between CHAR and VARCHAR?

Answer 56

CHAR is fixed length; VARCHAR is variable length

Question 57

In CREATE TABLE provinces, what does CHAR(2) mean?

Answer 57

A fixed-length string of 2 characters

Question 58

In CREATE TABLE provinces, what does VARCHAR(24) mean?

Answer 58

A variable-length string up to 24 characters

Question 59

What is a primary key?

Answer 59

A column that uniquely identifies each row

Question 60

Why is Author_ID defined as PRIMARY KEY?

Answer 60

To prevent duplicate author records

Question 61

Why are Firstname and Lastname marked NOT NULL?

Answer 61

Because an author must have a name

Question 62

What is a fully defined CREATE TABLE statement made up of?

Answer 62

Table name, column names, data types, constraints

Question 63

What happens if you issue a CREATE TABLE statement?

Answer 63

A new table is created in the database

Question 64

In the Author example, which column is the primary key?

Answer 64

Author_ID

Question 65

What constraint prevents duplicate values?

Answer 65

PRIMARY KEY

Question 66

What constraint prevents empty values?

Answer 66

NOT NULL

Question 67

What type of SQL statements are ALTER, DROP, and TRUNCATE?

Answer 67

Data Definition Language (DDL)

Question 68

What is ALTER TABLE used for?

Answer 68

To modify the structure of an existing table

Question 69

Can ALTER TABLE add columns?

Answer 69

Yes

Question 70

Can ALTER TABLE remove columns?

Answer 70

Yes

Question 71

Can ALTER TABLE change data types?

Answer 71

Yes

Question 72

Does ALTER TABLE use parentheses like CREATE TABLE?

Answer 72

No

Question 73

How do you add a column using ALTER TABLE?

Answer 73

ALTER TABLE table_name ADD COLUMN column_name datatype

Question 74

How do you modify a column data type?

Answer 74

ALTER TABLE table_name ALTER COLUMN column_name SET DATA TYPE datatype

Question 75

How do you remove a column from a table?

Answer 75

ALTER TABLE table_name DROP COLUMN column_name

Question 76

What does BIGINT store?

Answer 76

Large integers up to 19 digits

Question 77

What problem can occur when changing a column’s data type?

Answer 77

Existing data may not match the new type

Question 78

What happens if existing data is incompatible with a new data type?

Answer 78

The ALTER statement fails with an error

Question 79

What does DROP TABLE do?

Answer 79

Deletes a table and all its data

Question 80

What is the syntax for DROP TABLE?

Answer 80

DROP TABLE table_name

Question 81

What happens to data when DROP TABLE is executed?

Answer 81

Data is deleted with the table

Question 82

What does TRUNCATE TABLE do?

Answer 82

Deletes all rows in a table but keeps the table structure

Question 83

What is the syntax for TRUNCATE TABLE?

Answer 83

TRUNCATE TABLE table_name IMMEDIATE

Question 84

What does IMMEDIATE mean in TRUNCATE?

Answer 84

The action is processed immediately and cannot be reversed

Question 85

Is TRUNCATE faster than DELETE without WHERE?

Answer 85

Yes

Question 86

What is the difference between DROP TABLE and TRUNCATE TABLE?

Answer 86

DROP removes the table; TRUNCATE removes only data

Question 87

Which statement changes table structure?

Answer 87

ALTER TABLE

Question 88

Which statement removes a table completely?

Answer 88

DROP TABLE

Question 89

Which statement removes all rows but keeps the table?

Answer 89

TRUNCATE TABLE