Microsoft SQL Server

Chapter 3 - Database Tables, Columns and Rows

Database Table

In Microsoft SQL Server, a database table is an organized collection of data structured into rows and columns. Each table consists of fields (columns) that define the attributes of the data being stored and rows that contain the actual data. A table is essential in databases because it provides a structured format to store and retrieve information efficiently. Tables are typically created with SQL statements and serve as the foundation for the database structure, such as storing data for customers, products, and sales in a retail store.

Creating a Database Table
To create a table, you define the table's name and specify the columns, their data types, and any constraints. Here's a sample SQL statement to create a table for storing product information:
```
CREATE TABLE Products (
  ProductID INT PRIMARY KEY,
  ProductName NVARCHAR(100),
  Price DECIMAL(10, 2),
  QuantityInStock INT
);
```
- Products is the table name.
- ProductID is the primary key column, uniquely identifying each product.
- ProductName, Price, and QuantityInStock are columns with data types such as NVARCHAR for text and DECIMAL for numbers.
Inserting Data into a Table
After creating the table, data can be inserted into the rows using the INSERT INTO statement. Here's how to insert a product into the Products table:
```
INSERT INTO Products (ProductID, ProductName, Price, QuantityInStock)
VALUES (1, 'Laptop', 899.99, 50);
```
- This adds a new row to the Products table with the specified values for ProductID, ProductName, Price, and QuantityInStock.
Selecting Data from a Table
To retrieve data from a table, the SELECT statement is used. For example, to retrieve all products in the Products table:
```
SELECT * FROM Products;
```
- The * symbol means all columns, and this will display all rows in the Products table.
Updating Data in a Table
If you want to update existing data in a table, you use the UPDATE statement. Here's how to update the price of a product:
```
UPDATE Products
SET Price = 799.99
WHERE ProductID = 1;
```
- This changes the price of the product with ProductID 1 to 799.99.
Deleting Data from a Table
To remove data from a table, the DELETE statement is used. Here's how to delete a product:
```
DELETE FROM Products
WHERE ProductID = 1;
```
- This removes the product with ProductID 1 from the Products table.

Understanding how to work with tables, columns, and rows is fundamental in SQL Server. With these basic commands, beginners can create, manage, and manipulate data effectively.

Tansy SQL Course - Database Table - Video Thumbnail

Database Tables and Their Components

Database tables are designed to store data in a structured format, using rows and columns. Each table represents a specific type of entity, such as users, products, or orders, with the columns representing attributes of that entity. Understanding these components is crucial for effective database design and management.

Components of Database Tables

1. Table Name

The unique identifier for a table within a database, descriptive of the data it holds.

2. Columns/Fields

Columns represent the attributes of the entity. Each column has a specific data type and can be defined with various constraints:

Data Type: The kind of data a column can hold (e.g., VARCHAR, INT, DATE).
Constraints: Rules for the data stored in a column, including NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT.

3. Rows/Records

Individual instances of the entity, with each row having a unique identifier through the primary key.

4. Indexes

Special lookup tables that speed up data retrieval, analogous to an index in a book.

5. Relationships

Defines how tables relate to each other, including One-to-One, One-to-Many, and Many-to-Many relationships.

Sample SQL Code

Creating a Table

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Age INT CHECK (Age > 0),
    EnrollmentDate DATE DEFAULT CURRENT_DATE
);

Inserting Data

INSERT INTO Students (StudentID, FirstName, LastName, Email, Age)
VALUES (1, 'John', 'Doe', 'john.doe@example.com', 20);

Querying Data

SELECT * FROM Students
WHERE Age >= 18;

Updating Data

UPDATE Students
SET Email = 'new.email@example.com'
WHERE StudentID = 1;

Deleting Data

DELETE FROM Students
WHERE StudentID = 1;

DATA TABLE - TOP 10 WEBSITES

DATA TABLE - TOP 10 POPULATED COUNTRIES

DATA TABLE - TOP 10 FOOTBALL TEAMS

DATA TABLE - TOP 10 CRICKET TEAMS

POSTGRESQL - EMPLOYEE TABLE WITH DATA

POSTGRESQL - CLIENTS TABLE WITH DATA

MYSQL TABLE LISTING

EXCEL DATA TABLE - PATIENT DATA

GRAPH DATA - NOT A SQL TABLE

Employee Table with Data

EMPLOYEE JSON DOCUMENT, NOT A SQL TABLE

[
{
    "employee_id": 1,
    "employee_number": "EMP-01",
    "first_name": "George",
    "last_name": "Bush",
    "extension": 101,
    "email": "George.Bush@tansyacademy.com",
    "designation": "CEO",
    "date_of_birth": null,
    "salary": 130000,
    "city": "Niagara Falls",
    "state": "NY",
    "department_id": 2
},
{
    "employee_id": 2,
    "employee_number": "EMP-02",
    "first_name": "Joe",
    "last_name": "Biden",
    "extension": 102,
    "email": "Joe.biden@tansyacademy.com",
    "designation": "CTO",
    "date_of_birth": null,
    "salary": 65000,
    "city": "Long Beach",
    "state": "NY",
    "department_id": 2
}
]

Previous Next

Comments(0 comments)

Comments Not Found