Questions and Answers
What is the difference between a clustered index and a non-clustered index?
What is the purpose of the PRIMARY KEY constraint in a table?
What is the effect of dropping a table in a database?
What is the purpose of the ALTER TABLE statement?
Signup and view all the answers
What is the effect of creating an index on a column?
Signup and view all the answers
What is the purpose of the CHECK constraint?
Signup and view all the answers
What is the syntax to create a composite index on two columns?
Signup and view all the answers
What is the purpose of the FOREIGN KEY constraint?
Signup and view all the answers
What is the primary purpose of the DROP TABLE statement?
Signup and view all the answers
Which of the following data types is used to store dates and times in a table?
Signup and view all the answers
What is the purpose of the PRIMARY KEY constraint in a table?
Signup and view all the answers
Which statement is used to add a new column to an existing table?
Signup and view all the answers
What is the purpose of the FOREIGN KEY constraint in a table?
Signup and view all the answers
Which of the following is an example of a basic syntax for creating a table?
Signup and view all the answers
Study Notes
SQL DDL
CREATE TABLE
- Used to create a new table in a database
- Syntax:
CREATE TABLE table_name (column1 datatype, column2 datatype, ...);
- Example:
CREATE TABLE customers (id INT, name VARCHAR(255), email VARCHAR(255));
- Can specify constraints, such as PRIMARY KEY, FOREIGN KEY, and CHECK
- Can also specify default values and auto-incrementing columns
ALTER TABLE
- Used to modify the structure of an existing table
- Syntax:
ALTER TABLE table_name ADD|DROP|MODIFY column_name datatype;
- Example:
ALTER TABLE customers ADD address VARCHAR(255);
- Can add or remove columns, modify data types, and change constraints
- Can also rename columns or tables
DROP TABLE
- Used to delete a table from a database
- Syntax:
DROP TABLE table_name;
- Example:
DROP TABLE customers;
- Deletes the table and all its data
- Be cautious when using, as it permanently deletes data
INDEXING
- Used to improve query performance by creating an index on one or more columns
- Syntax:
CREATE INDEX index_name ON table_name (column_name);
- Example:
CREATE INDEX idx_name ON customers (name);
- Types of indexes:
- Unique index: ensures uniqueness of values in a column
- Composite index: indexes multiple columns
- Clustered index: physically reorders table data to match index
- Indexes can be created on columns used in WHERE, JOIN, and ORDER BY clauses
CONSTRAINTS
- Used to enforce data integrity and consistency
- Types of constraints:
- PRIMARY KEY: uniquely identifies each row in a table
- FOREIGN KEY: links to the PRIMARY KEY of another table
- UNIQUE: ensures uniqueness of values in a column
- CHECK: enforces a condition on a column or set of columns
- DEFAULT: sets a default value for a column
- Syntax:
CREATE TABLE table_name (column_name datatype CONSTRAINT constraint_name);
- Example:
CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(255));
- Can be used to enforce business rules and maintain data consistency
SQL DDL
Creating Tables
- The
CREATE TABLE
statement is used to create a new table in a database - The syntax for creating a table is
CREATE TABLE table_name (column1 datatype, column2 datatype,...);
- A table can be created with columns that have different data types, such as integers and strings
- Constraints can be specified during table creation, including PRIMARY KEY, FOREIGN KEY, and CHECK
- Default values and auto-incrementing columns can also be specified during table creation
Modifying Tables
- The
ALTER TABLE
statement is used to modify the structure of an existing table - The syntax for modifying a table is
ALTER TABLE table_name ADD|DROP|MODIFY column_name datatype;
- Columns can be added to or removed from an existing table
- The data type of an existing column can be modified
- Constraints can be added or removed from an existing table
- Columns or tables can be renamed using the
ALTER TABLE
statement
Deleting Tables
- The
DROP TABLE
statement is used to delete a table from a database - The syntax for deleting a table is
DROP TABLE table_name;
- Deleting a table also deletes all the data stored in the table
- The
DROP TABLE
statement should be used with caution, as it permanently deletes data
Indexing
- An index is a data structure that improves query performance
- Indexes can be created on one or more columns of a table
- The syntax for creating an index is
CREATE INDEX index_name ON table_name (column_name);
- Unique indexes ensure that all values in a column are unique
- Composite indexes are created on multiple columns
- Clustered indexes physically reorder table data to match the index
- Indexes can be created on columns used in WHERE, JOIN, and ORDER BY clauses
Constraints
- Constraints are used to enforce data integrity and consistency
- PRIMARY KEY constraints uniquely identify each row in a table
- FOREIGN KEY constraints link to the PRIMARY KEY of another table
- UNIQUE constraints ensure that all values in a column are unique
- CHECK constraints enforce a condition on a column or set of columns
- DEFAULT constraints set a default value for a column
- Constraints can be used to enforce business rules and maintain data consistency
SQL DDL
CREATE TABLE
- Creates a new table in a database
- Basic syntax:
CREATE TABLE table_name (column1 datatype, column2 datatype,...)
- Includes column definitions with datatypes (e.g., INT, VARCHAR, DATE, TIME, etc.)
- Example:
CREATE TABLE Customers (CustomerID INT, CustomerName VARCHAR(255), ContactName VARCHAR(255), Address VARCHAR(255), City VARCHAR(255), PostalCode VARCHAR(255), Country VARCHAR(255));
ALTER TABLE
- Modifies the structure of an existing table
- Used for:
- Adding new columns
- Deleting existing columns
- Modifying column definitions
- Basic syntax:
ALTER TABLE table_name ADD|DROP|MODIFY column_name datatype
- Examples:
- Adding a new column:
ALTER TABLE Customers ADD Email VARCHAR(255);
- Dropping an existing column:
ALTER TABLE Customers DROP COLUMN ContactName;
- Modifying a column definition:
ALTER TABLE Customers MODIFY CustomerName VARCHAR(300);
- Adding a new column:
DROP TABLE
- Deletes an existing table from a database
- Basic syntax:
DROP TABLE table_name;
- Example:
DROP TABLE Customers;
- Note: Use with caution, as it permanently deletes the table and its data.
CONSTRAINTS
- Define rules for data in a table
- Types of constraints:
- PRIMARY KEY: ensures unique values in each row
- UNIQUE: ensures unique values in a column
- FOREIGN KEY: ensures values match another table
- CHECK: ensures conditions are met before inserting or updating
- NOT NULL: ensures columns cannot be left blank
- Basic syntax varies by type:
- PRIMARY KEY:
CREATE TABLE table_name (column1 datatype PRIMARY KEY,...);
- UNIQUE:
CREATE TABLE table_name (column1 datatype UNIQUE,...);
- FOREIGN KEY:
CREATE TABLE table_name (column1 datatype, FOREIGN KEY (column1) REFERENCES other_table_name(column1));
- CHECK:
CREATE TABLE table_name (column1 datatype CHECK (condition),...);
- NOT NULL:
CREATE TABLE table_name (column1 datatype NOT NULL,...);
- PRIMARY KEY:
- Examples:
- PRIMARY KEY:
CREATE TABLE Customers (CustomerID INT PRIMARY KEY, CustomerName VARCHAR(255),...);
- FOREIGN KEY:
CREATE TABLE Orders (OrderID INT, CustomerID INT, FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID));
- PRIMARY KEY:
Studying That Suits You
Use AI to generate personalized quizzes and flashcards to suit your learning preferences.
Description
Quiz on SQL Data Definition Language, covering CREATE TABLE and ALTER TABLE statements, including syntax, examples, and constraints.