Database Design/Construction Commands Flashcards
What is the purpose of SQL commands in database design and construction?
They are used to set up and modify the database structure.
What command is used to create a database?
CREATE DATABASE database_name;
What command deletes a database?
DROP DATABASE database_name;
Why would you use ‘IF NOT EXISTS’ in CREATE TABLE?
To avoid errors if the table already exists.
What is the basic syntax to create a table in SQL?
CREATE TABLE [IF NOT EXISTS] table_name (column1 datatype constraint, column2 datatype constraint, …, PRIMARY KEY (column1), FOREIGN KEY (column) REFERENCES parent_table(parent_column));
Why do we specify data types when creating columns?
To define the kind of data the column will hold and enforce data integrity.
What is the purpose of constraints in table creation?
To enforce rules on the data, such as uniqueness, non-null values, or referential integrity.
List four categories of SQL data types.
Numerical, String, Date-Time, Boolean.
Give examples of numerical data types.
Int, Float, Double.
Give examples of string data types.
Varchar(n), Text.
Give examples of date-time data types.
Date, Time, Datetime, Timestamp.
What is the boolean data type used for?
To store TRUE or FALSE values.
What does the PRIMARY KEY constraint do?
Ensures the column holds unique, non-null values.
What does the FOREIGN KEY constraint do?
Links tables by ensuring a column’s value exists in another table’s primary key.
What does the NOT NULL constraint do?
Ensures a column cannot contain null values.
What does the UNIQUE constraint do?
Ensures all values in the column are unique.
What does the DEFAULT constraint do?
Sets a default value for a column if no value is provided.
What does the CHECK constraint do?
Enforces a rule for acceptable values in a column.
Give an example of using the DEFAULT constraint.
column1 datatype DEFAULT value.
Give an example of using the CHECK constraint.
column1 datatype CHECK (condition).
What command shows the structure of a table?
DESC table_name;
What command adds a column to a table?
ALTER TABLE table_name ADD column_name datatype constraints;
What command deletes a column from a table?
ALTER TABLE table_name DROP column_name;
What command modifies a column in a table?
ALTER TABLE table_name MODIFY COLUMN column_name new_data_type [constraint];
What command renames a column in a table?
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
Can multiple ALTER operations be combined in one command?
Yes, multiple altering operations can be combined.
If you want to ensure a column never contains null values, which constraint should you use?
NOT NULL constraint.
If you want to prevent duplicate values in a column, what constraint should you apply?
UNIQUE constraint.
If you want to link two tables, what constraint should you use?
FOREIGN KEY constraint.
If you want to provide a default value for a column, what should you do?
Use the DEFAULT constraint.
If you want to enforce a specific rule on the values of a column, what should you use?
CHECK constraint.
Spot the mistake: ‘CREATE TABLE employees (id INT PRIMARY, name VARCHAR(100));’
Error: PRIMARY should be PRIMARY KEY.
Spot the mistake: ‘ALTER TABLE employees MODIFY name VARCHAR(100);’
Correct syntax is: ALTER TABLE employees MODIFY COLUMN name VARCHAR(100);
Explain in your own words the role of a foreign key.
A foreign key links two tables and ensures data consistency between related records.
Describe why constraints are important when designing a database.
Constraints enforce data validity, ensure accuracy, and maintain integrity.
If you forget to use PRIMARY KEY in table design, what problem could arise?
Duplicate or null records could enter, compromising data integrity.
What happens if you skip data types when creating columns?
The database will not know how to store or validate the data properly.
