SQL ServerCreating and Modifying Tables

Creating a Table in SQL Server

When creating a table in SQL Server, you need to specify the table’s name, data type, and the constraints. Here is an example of creating a table named “employees”:

CREATE TABLE employees (
   employee_id INT PRIMARY KEY,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   email VARCHAR(100),
   phone_number VARCHAR(20),
   hire_date DATE,
   job_id VARCHAR(10),
   salary DECIMAL(8,2),
   commission_pct DECIMAL(2,2),
   manager_id INT,
   department_id INT
);

In this example, the “employees” table has 11 columns, including the primary key column “employee_id”. The data types of the columns are specified, such as VARCHAR for text and DECIMAL for numbers with decimal places. The constraints are added to columns, such as PRIMARY KEY and NOT NULL.

Modifying a Table in SQL Server

Modifying a table is an essential skill for working with databases. In SQL Server, you can use the ALTER TABLE statement to modify an existing table. There are several actions you can perform on a table using ALTER TABLE:

Adding a Column

To add a new column to an existing table, you can use the ADD clause followed by the column name and data type. Here is an example:

ALTER TABLE employees
ADD hire_date DATE;

In this example, we are adding a new column called “hire_date” to the “employees” table with a data type of DATE.

Modifying a Column

To modify the data type or size of an existing column, you can use the ALTER COLUMN clause. Here is an example:

ALTER TABLE employees
ALTER COLUMN first_name VARCHAR(100);

In this example, we are modifying the data type of the “first_name” column from VARCHAR(50) to VARCHAR(100).

Renaming a Column

To rename a column in an existing table, you can use the RENAME COLUMN clause. Here is an example:

ALTER TABLE employees
RENAME COLUMN email TO email_address;

In this example, we are renaming the “email” column to “email_address”.

Dropping a Column

To remove a column from an existing table, you can use the DROP COLUMN clause. Here is an example:

ALTER TABLE employees
DROP COLUMN phone_number;

In this example, we are removing the “phone_number” column from the “employees” table.

Adding a Constraint

To add a new constraint to a table, you can use the ADD CONSTRAINT clause. Here is an example:

ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary >= 0);

In this example, we are adding a check constraint to ensure that the “salary” column in the “employees” table is not negative.

Dropping a Constraint

To remove a constraint from a table, you can use the DROP CONSTRAINT clause. Here is an example:

ALTER TABLE employees
DROP CONSTRAINT chk_salary;

In this example, we are removing the check constraint named “chk_salary” from the “employees” table.

Summary

Learn how to create and modify tables in SQL Server with this beginner-friendly tutorial. We will cover everything from adding and dropping columns, to specifying table constraints. Altering tables can affect data, so be sure to consider the impact of each modification before making changes. Understanding how to modify tables is essential for anyone working with databases.