PowerPoint 2 Flashcards
What are the different SQL Categories/Languages?
- Data Definition Language(DDL)
- Data Manipulation Language(DML)
- Data Retrieval Language(DRL)
- Transaction Control(TCL)
- Data Control Language(DCL)
Explain what DDL is
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.
What are the most commonly used DDL commands?
- CREATE
- DROP
- ALTER
- TRUNCATE
- COMMENT
- RENAME
What is CREATE used for?
CREATE is used to create the database or its objects.
What is DROP used for?
This command is used to delete objects from a database.
What is ALTER used for?
ALTER is used to alter the structure of the database.
What is TRUNCATE used for?
The TRUNCATE TABLE command deletes the data inside a table, but not the table itself.
What is COMMENT used for?
COMMENT is used to add comments to the data directory.
What is RENAME used for?
This is used to rename an object existing in the database.
What will this do?
USE <database_name></database_name>
In order to tell MySQL on which database will be working, you need to select the database.
What will this do?
CREATE DATABASE <database_name></database_name>
This will create a new database with the name specified.
What will this do?
CREATE TABLE <table_name> (</table_name>
<column1> datatype,
<column2> datatype,
<column3> datatype,
...
);
</column3></column2></column1>
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.
What is the ALTER TABLE statement used for?
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>
What will this do?
DROP TABLE <table_name>;</table_name>
The DROP TABLE statement is used to drop an existing table in a database.
What is a Datatype and what are the three kind of datatypes?
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.)
What are Constraints?
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.
What is the NOT NULL Constraint?
Ensures that a column cannot have a NULL value.
What is the UNIQUE Constraint?
Ensures that all values in a column are different.
What is the PRIMARY KEY Constraint?
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).
What is the FOREIGN KEY Constraint?
The FOREIGN KEY is a field that
Prevents actions that would destroy links between tables.
What is the CHECK Constraint?
Ensures that the values in a column satisfies a specific condition.
What is the DEFAULT Constraint?
Sets a default value for a column if no value is specified.
What is the AUTO_INCREMENT Constraint?
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.
What’s the difference between PRIMARY KEY and UNIQUE?
A table can have only one PRIMARY KEY constraint, but multiple UNIQUE constraints.
What are the ways you can add a Constraint to a table?
- Inline method
- Out-of-line method
What are the inline and out-of-line way of adding the primary key constraint to a table?
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>
What are the inline and out-of-line way of adding the foreign key constraint to a table?
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>
What are the inline and out-of-line way of adding the UNIQUE constraint to a table?
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>
What are the inline and out-of-line way of adding the NOT NULL constraint to a table?
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>
What are the inline and out-of-line way of adding the CHECK constraint to a table?
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>
What are the inline and out-of-line way of adding the DEFAULT constraint to a table?
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>
What is the inline way of adding the AUTO_INCREMENT constraint to a table?
inline way:
CREATE TABLE <table_name>(</table_name>
<column_name> datatype AUTO_INCREMENT PRIMARY KEY,
....
....
);
</column_name>
