The series so far:
- SQL Server Fundamentals - Part 1: Life of Databases
- SQL Server Fundamentals - Part 2: How Introduction to Tables in Databases
can keep you out of trouble
- SQL Server Fundamentals - Part 3: Working with data in the Table taboos you should Break
- SQL Server Fundamentals - Part 4: How Indexes can help you live a better life
- SQL Server Fundamentals - Part 5: Non Relational Data Types in 2 Easy Steps
Create tables using the CREATE TABLE statement
Specify a table name and column specifications
Create Table <Schema.TableName>
CREATE TABLE Sales.Product (ProductID INTEGER IDENTITY PRIMARY KEY, Name VARCHAR(20), Price DECIMAL NULL);
Schema: Container that holds the table. Store and organise related objects and acts as security wrapper. Its conceived as namespace and encapsulator for security
TableName: Name of the table
a. Column Name
b. Data type: defines the data type of column, for example,
VARCHAR- varying with characters think of it as a string.
c. Identity Column: performs an auto-increment feature. By default, the starting value for IDENTITY is 1, and it will increment by 1 for each new record.
d. Primary Key: Only one column can be the primary key.
e. Constraints: Rules that apply in particular column
i. Default Constraint: It provides a default value for a column.
ii. NULL Constraint: Can accept null value not implicit to have a value always
Alter Table: to alter table after its created
Modify table definition using the ALTER TABLE statement. Add or remove columns, constraints, keys.
ALTER TABLE Sales.Product ADD Supplier INTEGER NOT NULL CONSTRAINT def_supplier DEFAULT 1;
- Drop table: Remove tables using the DROP TABLE statement from the database.
DROP TABLE Sales.Product;
Note: No undo we need to restore the backup
- Delete Table: Delete data in a table
DDL: Data Definition Language
Create, Delete and Drop commands are called DDL.
Inserting data in a table
- Insert data into a table using the INSERT statement
- Specify explicit columns where others support defaults or NULLs
By default after create table, the table is empty.
We don't have to specify the value of identity and order matters. If we define column names, then order doesn't matter. If we left out the specific column, it would take default values specified in column definitions. We also need the correct data type for each column. Enforce integrity of data using constraints.
INSERT INTO Product (Name) VALUES (‘Knickknack’);
Each time we make insert statement, SQL Server ensures its atomic either the whole gets inserted or nothing.
Select data from the table
- Select data from a table using the SELECT statement
- Specify the columns or expressions you want to return
- Use aliases for column names in the result set
Use the WHERE clause to filter rows
Don't use Select * generally; we use specific columns since columns grow overtimes.
SELECT * FROM Product
Order in which we list in select the data in that order. We can bring column as separate name and also allow arithmetic operation over data. Default behaviour any activity on null results in null.
SELECT Name AS Product, Price * 0.9 AS SalePrice FROM Product;
Allows row filter
Order in which Database engine is interpreting that statement. First From clause and then apply Where filters and then select columns.
SELECT Name, Price FROM Product WHERE Supplier = 2;
Data Types defined in SQL Server
Examples of storing strings in SQL
Fixed length - Phone
Variable length - Email
Large text - Transcript
Date and times:
Dates (Year Month Day and Times):
BINARY - Photo
UNIQUEIDENTIFIER - GUID
Spatial - Coordinates or Shapes
Timestamp - Integer values often used in UNIX Systems
- Choose the most appropriate data type and size for each column
- SQL Server provides data types for strings, numbers, dates, and others
- Specify an appropriate size that will support all data without wasted space
- Some data types support implicit conversion; others must be explicitly converted
- SQL Server provides functions to perform data conversion
Working with Multiple Tables
- Most databases contain multiple tables
- This is a direct result of normalisation
- Use keys to determine relationships between rows in different tables
- A primary key identifies a unique row in a table
- A foreign key references a row in another table
- Create foreign key constraints to enforce referential integrity
- This prevents invalid data insertions
- It can be used to avoid deletions or updates that will “orphan” rows
ALTER TABLE Product ADD CONSTRAINT fk_product_supplier FOREIGN KEY (Supplier) REFERENCES Supplier(SupplierID);
- Use the CREATE TABLE statement to create tables
- Use the INSERT statement to add rows to a table
- Use the SELECT statement to retrieve rows from a table
- Use constraints that define relationships between primary keys and foreign keys to enforce referential integrity