2. How Introduction to Tables in Databases can keep you out of trouble (SQL Fundamentals)

The series so far:

1. SQL Server Fundamentals - Part 1: Life of Databases
2. SQL Server Fundamentals - Part 2: How Introduction to Tables in Databases
   can keep you out of trouble
3. SQL Server Fundamentals - Part 3: Working with data in the Table taboos you should Break
4. SQL Server Fundamentals - Part 4: How Indexes can help you live a better life
5. 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);

1. Schema: Container that holds the table. Store and organise related objects and acts as security wrapper. Its conceived as namespace and encapsulator for security

2. TableName: Name of the table

3. Column
   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

4. 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;

5. 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

6. Delete Table: Delete data in a table

DDL: Data Definition Language

Create, Delete and Drop commands are called DDL.

Inserting data in a table

1. Insert data into a table using the INSERT statement
2. 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

1. Select data from a table using the SELECT statement
2. Specify the columns or expressions you want to return
3. 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;

Where Clause

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

Data Types defined in SQL Server

Character Strings

Unicode - NVARCHAR
ASCII - VARCHAR
Examples of storing strings in SQL
Fixed length - Phone
Variable length - Email
Large text - Transcript

Numbers

Integers - SMALLINT, BIGINT
Exact decimals
Approximate decimals

Temporal Values

Date and times: DATE and TIME
Offsets: DATETIMEOFFSET
Dates (Year Month Day and Times):
SMALLDATETIME, DATETIME and DATETIME2

Others

BIT (True/False)
BINARY - Photo
UNIQUEIDENTIFIER - GUID
XML
Spatial - Coordinates or Shapes
Timestamp - Integer values often used in UNIX Systems

Considerations

1. 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

2. Some data types support implicit conversion; others must be explicitly converted

• SQL Server provides functions to perform data conversion

Working with Multiple Tables

1. Most databases contain multiple tables

• This is a direct result of normalisation

2. 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

3. 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);

Summary

1. Use the CREATE TABLE statement to create tables
2. Use the INSERT statement to add rows to a table
3. Use the SELECT statement to retrieve rows from a table
4. Use constraints that define relationships between primary keys and foreign keys to enforce referential integrity