Page 49 - SQL

Id int identity(1,1) not null

Value Meaning

Id the column's name.

int is the data type.

states that column will have auto generated values starting at 1 and
identity(1,1)
incrementing by 1 for each new row.

primary key states that all values in this column will have unique values

not null states that this column cannot have null values

Create Table From Select

You may want to create a duplicate of a table:

CREATE TABLE ClonedEmployees AS SELECT * FROM Employees;

You can use any of the other features of a SELECT statement to modify the data before passing it
to the new table. The columns of the new table are automatically created according to the selected
rows.

CREATE TABLE ModifiedEmployees AS
SELECT Id, CONCAT(FName," ",LName) AS FullName FROM Employees
WHERE Id > 10;

Duplicate a table

To duplicate a table, simply do the following:

CREATE TABLE newtable LIKE oldtable;
INSERT newtable SELECT * FROM oldtable;

CREATE TABLE With FOREIGN KEY

Below you could find the table Employees with a reference to the table Cities.

CREATE TABLE Cities(
CityID INT IDENTITY(1,1) NOT NULL,
Name VARCHAR(20) NOT NULL,
Zip VARCHAR(10) NOT NULL
);

CREATE TABLE Employees(
EmployeeID INT IDENTITY (1,1) NOT NULL,
FirstName VARCHAR(20) NOT NULL,

https://riptutorial.com/ 31