Microsoft SQL Server

Chapter 3 - Database Tables, Columns and Rows

ROWs and COLUMNs

In Microsoft SQL Server, tables consist of columns (fields) and rows (records). Columns define the type of data stored, such as text or numbers, while rows represent individual entries in the table. For example, in a table of products, columns might include "ProductName" and "Price," while rows will represent different products, each with their own name and price. Understanding rows and columns is essential for working with database tables, as they form the core structure for storing and retrieving data.

Columns in SQL Server
Columns define the attributes or properties of the data you want to store. Each column has a specific data type, such as INT, VARCHAR, or DATE. Here's how you define columns when creating a table for customer information:

    CREATE TABLE Customers (
      CustomerID INT PRIMARY KEY,
      CustomerName NVARCHAR(100),
      Email NVARCHAR(100),
      DateOfBirth DATE
    );

Each column (CustomerID, CustomerName, Email, DateOfBirth) is defined with a data type that specifies the kind of data that can be stored.
The CustomerID is an integer (INT) and is marked as the Primary Key, meaning it uniquely identifies each row in the table.

Rows in SQL Server
Rows represent individual records or entries in a table. Each row contains data for every column in the table. Here's an example of how you might insert a row into the Customers table:

   INSERT INTO Customers (CustomerID, CustomerName, Email, DateOfBirth)
   VALUES (1, 'John Doe', 'johndoe@example.com', '1990-01-15');

This adds a new row to the Customers table, with values for each column: CustomerID as 1, CustomerName as 'John Doe', Email as 'johndoe@example.com', and DateOfBirth as '1990-01-15'.
Each row in the table corresponds to a single customer's information.

Retrieving Data from Columns and Rows
To extract data from specific columns and rows, you use the SELECT statement. For example, to retrieve the names and email addresses of all customers:

   SELECT CustomerName, Email FROM Customers;

This query will return all the rows from the Customers table but only for the CustomerName and Email columns.

Updating Data in Rows
You can modify existing data in specific rows using the UPDATE statement. For example, to change the email address of a customer:

   UPDATE Customers
   SET Email = 'newemail@example.com'
   WHERE CustomerID = 1;

This updates the email of the customer with CustomerID 1.

Deleting Rows from a Table
If you need to remove specific rows from a table, use the DELETE statement. Here’s how to delete a row:

   DELETE FROM Customers
   WHERE CustomerID = 1;

This deletes the customer with CustomerID1 from the table.

Understanding how rows and columns work together in SQL Server is crucial for managing and organizing data in a structured and efficient way. These concepts form the basis of data storage and retrieval in relational databases.

Understanding Rows and Columns in SQL Tables

Columns

Columns represent the attributes or fields of the data table, each designed to hold a specific type of information.

DataType: Dictates the kind of data a column can store (e.g., integers, text, dates).
Column Name: A unique name within the table that identifies the column.

Rows

Rows represent individual records or data entries, containing a unique instance of data for the columns.

PrimaryKey: Uniquely identifies each row in the table.
Uniqueness: Each row should have a unique combination of values.

Example Table: Employees

EmployeeID	FirstName	LastName	Email	HireDate
1	John	Doe	john.doe@example.com	2020-01-10
2	Jane	Smith	jane.smith@example.com	2020-02-15

SQL Operations on Rows and Columns

Inserting Data

Adds new rows to the table.

INSERT INTO Employees (EmployeeID, FirstName, LastName, Email, HireDate) VALUES (3, 'Alice', 'Johnson', 'alice.johnson@example.com', '2020-03-20');

Querying Data

Retrieves data from the table, potentially filtering both rows and columns.

SELECT FirstName, LastName FROM Employees WHERE HireDate > '2020-01-01';

Updating Data

Modifies existing rows in the table.

UPDATE Employees SET Email = 'new.email@example.com' WHERE EmployeeID = 1;

Deleting Data

Removes rows from the table.

DELETE FROM Employees WHERE EmployeeID = 2;

SQL Column Components

SQL column components define the structure, constraints, and behavior of data within a database table. Below are the key components and attributes for SQL columns:

1. Data Type

Specifies the kind of data a column can store. Common types include:

INTEGER: For whole numbers.
VARCHAR(n): For variable-length strings, where n defines the maximum length.
CHAR(n): For fixed-length strings, with n defining the string length.
DATE: For dates.
FLOAT, DOUBLE: For floating-point numbers.

2. Default Value

Automatically assigns a specific value if no value is provided during row insertion.

Age INT DEFAULT 18

3. Not Null Constraint

Ensures a column cannot store a NULL value, requiring every row to have a value for this column.

Name VARCHAR(100) NOT NULL

4. Unique Constraint

Ensures all values in the column are unique across the table, important for non-primary key uniqueness.

Email VARCHAR(100) UNIQUE

5. Primary Key Constraint

A unique identifier for each row, cannot be NULL and must be unique. Can be a single column or a combination of columns.

CustomerID INT PRIMARY KEY

6. Foreign Key Constraint

Establishes a link between the data in two tables, referencing the primary key of another table to enforce data integrity.

OrderID INT FOREIGN KEY REFERENCES Orders(OrderID)

7. Check Constraint

Specifies a condition on a column that must be true for all rows, used to enforce domain integrity.

Age INT CHECK (Age >= 18)

8. Auto Increment

Automatically assigns a unique value to the column for each new row, commonly used for ID columns.

CustomerID INT AUTO_INCREMENT

Example

Combining these components, here's an example of a table creation statement in SQL:

CREATE TABLE Customers (
    CustomerID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Age INT DEFAULT 18 CHECK (Age >= 18),
    Address VARCHAR(255)
);

Each column component plays a specific role in defining how data is stored, validated, and related to other tables' data.

EMPLOYEE TABLE WITH DATA

Previous Next

Comments(0 comments)

Comments Not Found