Cybersecurity, Cyber, IT, news, computer, security, hacker, whitehat, blackhat, gray hat, education, penetration tester, information security, hacking, video, operating system, OS, google,googledork, VM, online safety, internet security research, article, ethical, elearning, security+, technology, google hacking, windows administrator, tools, free tools, networking resources, SMB,help, cyber-security

Microsoft SQL Statement Samples

This page provides some sample Microsoft SQL Server statements that administrators may find useful as templates.   

Create Schema 

CREATE SCHEMA Users; AUTHORIZATION kevinS

Notes:  The name of the database Schema Users will be created.  This statement also provides the option of granting the user KevinS as a Schema owner (vs. the DBA which occurs by default).

Create Table

CREATE TABLE Employee
(
EmployeeID IN
IDENTITY(1,1),
LastName VARCHAR(25)
NOT NULL,
FirstName VARCHAR(25) NOT NULL,
Street VARCHAR(35),
City VARCHAR(30),
St CHAR(2),
Zipcode NVARCHAR(5),
Phone NVARCHAR(10) NOT NULL,
StartEmployment DATE NOT NULL,
EndEmployment DATE,
Stats VARCHAR(11) NOT NULL,

Constraint Empl_EmployeeID_PK Primary Key (EmployeeID)
);

Notes: 
The Table Employee will be created.  This statement sets the EmployeeID as a unique Identity field, that begins numbering at 1 and increments by 1 for each new record. 

This example shows various data types used and examples of setting some fields to not allow for NULL entries at time of input.

The Constraint lines performs two functions.  First is provides an Alias for the constraint (otherwise SQL will give its a guid, and it identifies the primary key for the table.  This is more graceful method then appending Primary key to tables where both a Primary Key (PK) and Foreign Key (FK) are present.

Simple Records Inserts

INSERT INTO Employee (LastName, FirstName, Street, City, St, Zipcode, Phone, StartEmployment, EndEmployment, Stats)

VALUES

('Williams', 'Sally', '123 Q Ave', 'Auburn', 'CA', 95602, 9165002313, 20090101, NULL, 'A'),
(Everlast', 'Billy', '12 Smith St', 'Roseville', 'CA', 95678, 9165896102, 20090801, NULL, 'A'),
('Glenn', 'Gregory', '1233 Treeglenn Road # 1250', 'Grass Valley', 'CA', 95945, 9168642368, 20091213, 20120401, 'I'),
('Van Muir', 'Kent', '3419 Susan Circle', 'Roseville', 95678, 9165326500, 20091113, NULL, 'A'),
('Bender', 'Kyle', '666 San Surf Way', 'Loomis', 'CA', 95650, 9161251654, 20101231, NULL, 'A'),
('Kendall', 'George', '314905 Garden Glen Rd # 111', 'Roseville', 'CA', 95661, 9165953212, 20101115, NULL, 'A'),
('Phillips', 'Lynn', '13457890 College Way', 'Grass Valley', 'CA', 95945, 9165300254, 20101201, NULL, 'A'),
('Lowery', 'Thomas', '15667 C Ave Apt D', 'Roseville', 'CA', 95661, 9165842316, 20101215, NULL, 'U')
;

Notes: 
The Table Employee once created has an Identity field, which creates a unique EmployeeID entry for each added record.  There is no need to fill this field, so it is left out of the insert statement.

Test values are surrounded by single quotes.  Numeric fields do not require single quotes (but may be used to force explicit entries supported by the data type if needed.

If you do not want to insert data into a field that is not set to NOT NULL, then you can leave the field out of the definition. 

The order in which you define the fields in the INSERT INTO statement is the order you must provide the data.