PowerPoint 2

Question 1

What are the different SQL Categories/Languages?

Answer 1

1. Data Definition Language(DDL)
2. Data Manipulation Language(DML)
3. Data Retrieval Language(DRL)
4. Transaction Control(TCL)
5. Data Control Language(DCL)

Question 2

Explain what DDL is

Answer 2

Data Definition Language consists of the SQL commands that can be used to define the database schema.

DDL is a set of SQL commands used to CREATE, MODIFY, and DELETE database structures but not data.

Question 3

What are the most commonly used DDL commands?

Answer 3

1. CREATE
2. DROP
3. ALTER
4. TRUNCATE
5. COMMENT
6. RENAME

Question 4

What is CREATE used for?

Answer 4

CREATE is used to create the database or its objects.

Question 5

What is DROP used for?

Answer 5

This command is used to delete objects from a database.

Question 6

What is ALTER used for?

Answer 6

ALTER is used to alter the structure of the database.

Question 7

What is TRUNCATE used for?

Answer 7

The TRUNCATE TABLE command deletes the data inside a table, but not the table itself.

Question 8

What is COMMENT used for?

Answer 8

COMMENT is used to add comments to the data directory.

Question 9

What is RENAME used for?

Answer 9

This is used to rename an object existing in the database.

Question 10

What will this do?

USE <database_name></database_name>

Answer 10

In order to tell MySQL on which database will be working, you need to select the database.

Question 11

What will this do?

CREATE DATABASE <database_name></database_name>

Answer 11

This will create a new database with the name specified.

Question 12

What will this do?

CREATE TABLE <table_name> (</table_name>

<column1> datatype,
<column2> datatype,
<column3> datatype,
...
);
</column3></column2></column1>

Answer 12

The CREATE TABLE statement is used to create a new table in a database.

The column parameters specify the names of the columns of the table.

The datatype parameter specifies the type of data the column can hold.

Question 13

What is the ALTER TABLE statement used for?

Answer 13

The ALTER TABLE statement is used to ADD, DELETE, or MODIFY columns in an existing table.

The ALTER TABLE statement is also used to ADD and DROP various constraints on an existing table.

Examples:
- ALTER TABLE <table_name> ADD <column_name> datatype;
- ALTER TABLE <table_name> DROP COLUMN <column_name>;
- ALTER TABLE <table_name>
MODIFY COLUMN <column_name> datatype;</column_name></table_name></column_name></table_name></column_name></table_name>

Question 14

What will this do?

DROP TABLE <table_name>;</table_name>

Answer 14

The DROP TABLE statement is used to drop an existing table in a database.

Question 15

What is a Datatype and what are the three kind of datatypes?

Answer 15

A data type is a classification that specifies which type of value a variable has and what type of mathematical, relational or logical operations can be applied to it without causing an error.

The three datatypes are:
1. String datatypes(varchar, char, etc.)
2. Numeric datatypes(int, float, double, dec, etc.)
3. Date and Time Datatypes(date, datetime, timestamp etc.)

Question 16

What are Constraints?

Answer 16

SQL constraints are used to specify rules for the data in a table.

Constraints 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 table.

Question 17

What is the NOT NULL Constraint?

Answer 17

Ensures that a column cannot have a NULL value.

Question 18

What is the UNIQUE Constraint?

Answer 18

Ensures that all values in a column are different.

Question 19

What is the PRIMARY KEY Constraint?

Answer 19

A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table.

A table can have only ONE primary key; and in the table, the primary key can consist of single or multiple columns(fields).

Question 20

What is the FOREIGN KEY Constraint?

Answer 20

The FOREIGN KEY is a field that
Prevents actions that would destroy links between tables.

Question 21

What is the CHECK Constraint?

Answer 21

Ensures that the values in a column satisfies a specific condition.

Question 22

What is the DEFAULT Constraint?

Answer 22

Sets a default value for a column if no value is specified.

Question 23

What is the AUTO_INCREMENT Constraint?

Answer 23

This constraint automatically generates a unique number whenever we insert a new record into the table.
Generally, we use this constraint for the primary key field in a table.

Question 24

What’s the difference between PRIMARY KEY and UNIQUE?

Answer 24

A table can have only one PRIMARY KEY constraint, but multiple UNIQUE constraints.

Question 25

What are the ways you can add a Constraint to a table?

Answer 25

1. Inline method
2. Out-of-line method

Question 26

What are the inline and out-of-line way of adding the primary key constraint to a table?

Answer 26

inline way:
CREATE TABLE <table_name> (</table_name>

<column_name> datatype,
....
....
,
PRIMARY KEY(<column_name(s)>)
);

out-of-line way:
ALTER TABLE <table_name>
ADD PRIMARY KEY (<column_name(s)>);
</table_name></column_name>

Question 27

What are the inline and out-of-line way of adding the foreign key constraint to a table?

Answer 27

inline way:
CREATE TABLE <table_name> (</table_name>

<column_name> datatype,
....
....
,
FOREIGN KEY (<table_name>)
REFERENCES <table_name>(<column_name>)
);

out-of-line way:
ALTER TABLE <table_name>
ADD FOREIGN KEY (<column_name(s)>)
REFERENCES <table_name>(<column_name>);
</column_name></table_name></table_name></column_name></table_name></table_name></column_name>

Question 28

What are the inline and out-of-line way of adding the UNIQUE constraint to a table?

Answer 28

inline way:
CREATE TABLE <table_name> (</table_name>

<column_name> datatype UNIQUE,
....
....
);

out-of-line way:
ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
UNIQUE (<column_name(s)>);
</constraint_name></table_name></column_name>

Question 29

What are the inline and out-of-line way of adding the NOT NULL constraint to a table?

Answer 29

inline way:
CREATE TABLE <table_name> (</table_name>

<column_name> datatype NOT NULL,
....
....
);

out-of-line way:
ALTER TABLE <table_name>
MODIFY <column_name(s)> datatype NOT NULL;
</table_name></column_name>

Question 30

What are the inline and out-of-line way of adding the CHECK constraint to a table?

Answer 30

inline way:
CREATE TABLE <table_name> (</table_name>

<column_name> datatype,
....
....
,
CONSTRAINT <constraint_name>
CHECK (<column_name> = something)
);

out-of-line way:
ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
CHECK (<column_name> = something);
</column_name></constraint_name></table_name></column_name></constraint_name></column_name>

Question 31

What are the inline and out-of-line way of adding the DEFAULT constraint to a table?

Answer 31

inline way:
CREATE TABLE <table_name> (</table_name>

<column_name> datatype DEFAULT something,
....
....
);

out-of-line way:
ALTER TABLE <table_name>
ALTER <column_name> SET DEFAULT something;
</column_name></table_name></column_name>

Question 32

What is the inline way of adding the AUTO_INCREMENT constraint to a table?

Answer 32

inline way:
CREATE TABLE <table_name>(</table_name>

<column_name> datatype AUTO_INCREMENT PRIMARY KEY,
....
....
);
</column_name>