Primary Keys , Foreign Keys, Identity column

 

                Primary Keys ,  Foreign Keys, Identity column

Watch YouTube Video:

                        

Primary Keys ,  Foreign Keys, Identity column

CREATE TABLE auto.Departments (

Id INT NOT NULL identity ,

Name VARCHAR (25 ) NOT NULL ,

PRIMARY KEY (Id ) 

) ;

INSERT INTO auto.Departments ( Name ) VALUES (  'HR' ) , ('Sales' ), ('Tech ' );

select * from auto.Departments

CREATE TABLE auto.Employees (

Id INT NOT NULL identity ,

FName VARCHAR (35 ) NOT NULL ,

LName VARCHAR (35 ) NOT NULL ,

PhoneNumber VARCHAR (11 ),

Managerid INT ,

Departmentid INT NOT NULL ,

Salary INT NOT NULL ,

HireDate DATETIME NOT NULL ,

PRIMARY KEY (Id )

) ;

(INSERT INTO auto.Employees 

(  FName , LName , PhoneNumber, Managerid , Departmentid , Salary , HireDate )

VALUES

('James','Smith','1234567890',NULL,1,1000,CONVERT(DATETIME,'2004-01-01')),

('John','Johnson','2468101214',1,1,400,CONVERT(DATETIME,'2007-03-23')),

('Michael','Williams','1357911131',1,2,600,CONVERT(DATETIME,'2009-05-12')),

('Johnathon','Smith','1212121212',2,1,500,CONVERT(DATETIME,'2017-09-09')) 

alter table auto.Employees add FOREIGN KEY (Managerid ) REFERENCES auto.Employees(Id );

alter table auto.Employees add FOREIGN KEY (Departmentid ) REFERENCES auto.Departments(Id );

CREATE TABLE auto.Customers (

Id INT NOT NULL identity ,

FName VARCHAR (35 ) NOT NULL ,

LName VARCHAR (35 ) NOT NULL ,

Email varchar(100) NOT NULL ,

PhoneNumber VARCHAR (11 ),

PreferredContact VARCHAR (5) NOT NULL ,

PRIMARY KEY (Id)

)

INSERT INTO auto.Customers

(FName,LName , Email, PhoneNumber, PreferredContact )

VALUES

( 'William' , 'Jones' , 'william.jones@example.com' , '3347927472' , 'PHONE' ),

('David' , 'Miller' , 'dmiller@example.net' , '2137921892' , 'EMAIL' ),

('Richard' , 'Davis' , 'richard0123@example.com' , NULL , 'EMAIL' ) 

CREATE TABLE auto.Cars (

Id INT NOT NULL identity ,

Customerid INT NOT NULL ,

Employeeid INT NOT NULL ,

Model varchar(50 ) NOT NULL ,

Status varchar(25 ) NOT NULL ,

TotalCost INT NOT NULL ,

PRIMARY KEY (Id ),

FOREIGN KEY (Customerid ) REFERENCES auto.Customers(Id ),

FOREIGN KEY (Employeeid ) REFERENCES auto.Employees(Id ) )

alter table auto.cars drop constraint FK__Cars__Customerid__2E1BDC42

alter table auto.cars drop constraint FK__Cars__Employeeid__2F10007B

INSERT INTO auto.Cars

(Customerid, Employeeid , Model , Status, TotalCost )

VALUES ( 1,2 , 'Ford F-150' , 'READY' , '230' ), (1,2 , 'Ford F-150' , 'READY' , '200' ),

( 2,1,'Ford Mustang' , 'WAITING' , '100' ),

( 3,3,'Toyota Prius' , 'WORKING' , '1254' ) 

For more Abinitio, AWS and data engineering videos please subscribe , view , like and share my YouTube channel 

Click DataPundit