Chapter 2 : Getting Started with SQL

2.1 SQL Basics

SQL (Structured Query Language) is a standardized programming language used to manage and manipulate relational databases, including Microsoft SQL Server. SQL basics involve understanding the fundamental commands and syntax used to interact with databases. Here are some essential SQL basics for Microsoft SQL Server:

SELECT Statement:

The SELECT statement retrieves data from one or more tables. It is the primary command for querying and retrieving data from a database.

SELECT column1, column2, ...

FROM table_name;

SELECT: Specifies that you want to retrieve data.

column1, column2, ...: Names of the columns you want to retrieve. You can use * to select all columns.

FROM: Specifies the table from which you want to retrieve data.

table_name: Name of the table containing the data.

Example:

Filtering Data with WHERE Clause:

The WHERE clause is used to filter rows based on specific conditions.

SELECT column1, column2, ...

FROM table_name

WHERE condition;

condition: The condition that rows must satisfy.

Example:

Sorting Data with ORDER BY Clause:

The ORDER BY clause sorts the result set based on one or more columns.

SELECT column1, column2, ...

FROM table_name

ORDER BY column_to_sort [ASC|DESC];

SELECT column1, column2, ...

FROM table_name

ORDER BY column_to_sort [ASC|DESC];

Example:

Inserting Data with INSERT INTO:

The INSERT INTO statement is used to add new records (rows) to a table.

INSERT INTO table_name (column1, column2, ...)

VALUES (value1, value2, ...);

Example:

Updating Data with UPDATE:

UPDATE table_name

SET column1 = new_value1, column2 = new_value2, ...

WHERE condition;

Example:

Deleting Data with DELETE:

The DELETE statement removes records from a table.

DELETE FROM table_name

WHERE condition;

Example:

These SQL basics are the foundation for working with Microsoft SQL Server databases. They enable you to perform various data manipulation tasks, from querying and retrieving data to inserting, updating, and deleting records. As you become more familiar with SQL, you can explore more advanced topics and queries to harness the full power of your database system.

2.2 SQL Data Types

In Microsoft SQL Server, data types define the kind of data that can be stored in a database column. SQL Server provides a variety of data types to accommodate different types of data, such as integers, text, dates, and more. Let's explore the SQL data types in SQL Server in detail:

Numeric Data Types:

These data types are used for storing numeric values, including integers and decimals.

Common numeric data types include:

INT: Represents a 32-bit signed integer.

BIGINT: Stores a 64-bit signed integer.

SMALLINT: Holds a 16-bit signed integer.

DECIMAL or NUMERIC: Used for fixed-point numbers with a specified precision and scale.

FLOAT: Stores approximate numeric values with a floating-point precision.

Character String Data Types:

These data types are used for storing character strings and textual data.

Common character string data types include:

CHAR(n): Fixed-length character string with a specified length n.

VARCHAR(n): Variable-length character string with a maximum length of n.

TEXT: Stores large blocks of text data, suitable for long documents.

Binary Data Types:

These data types are used for storing binary data, such as images or files.

Common binary data types include:

BINARY(n): Fixed-length binary data with a specified length n.

VARBINARY(n): Variable-length binary data with a maximum length of n.

IMAGE: Stores large binary data, such as images or multimedia files.

Date and Time Data Types:

These data types are used for storing date and time values.

Common date and time data types include:

DATE: Stores date values only.

TIME: Stores time values.

DATETIME or DATETIME2: Stores date and time values with fractional seconds precision.

SMALLDATETIME: Stores date and time values with reduced precision for storage efficiency.

Boolean Data Type:

SQL Server does not have a dedicated Boolean data type. Instead, it uses BIT, which can represent 0 (false), 1 (true), or NULL (unknown).

Uniqueidentifier Data Type:

UNIQUEIDENTIFIER stores a globally unique identifier (GUID). It is often used as a primary key for replication and distributed systems.

Other Specialized Data Types:

SQL Server provides several specialized data types for specific purposes, including:

XML: Stores XML data.

SQL_VARIANT: Stores values of different data types.

GEOMETRY and GEOGRAPHY: Used for spatial data, such as geographic coordinates.

HIERARCHYID: Stores hierarchical data.

TIMESTAMP (deprecated): Used for versioning and tracking changes.

User-Defined Data Types (UDTs):

SQL Server allows you to create custom data types based on existing data types. These user-defined data types can simplify database design and enforce data consistency.

Choosing the appropriate data type is essential for efficient storage and data integrity. It's important to consider factors like data size, precision, and the nature of the data when selecting a data type for a column in your database schema. Proper data type selection can help optimize database performance and ensure data accuracy.

2.3 Creating and Managing Databases

Creating and managing databases in Microsoft SQL Server is a fundamental aspect of database administration. Here, we'll explain the process of creating and managing databases in SQL Server:

Creating a Database:

To create a new database in SQL Server, you can use SQL Server Management Studio (SSMS) or write SQL statements. Here's how to create a database using SSMS:

Open SQL Server Management Studio.

Connect to your SQL Server instance.

In the Object Explorer, right-click on "Databases."

Select "New Database."

Provide a name for your database.

Configure the database settings, such as the file locations, initial size, and growth options.

Click "OK" to create the database.

If you prefer using SQL statements, you can use the CREATE DATABASE command:

Managing Database Properties:

After creating a database, you can manage its properties to fine-tune its behavior. Some important properties include:

Filegroups: SQL Server allows you to organize database files into filegroups. You can create and manage filegroups to optimize data storage and backups.

Collation: Specify the collation for the database, which determines how string comparisons and sorting are performed.

Recovery Model: Choose the appropriate recovery model based on your backup and recovery requirements. Options include Full, Simple, and Bulk-Logged.

Compatibility Level: Set the compatibility level to control how the database behaves with regard to older SQL Server versions.

Creating Tables:

Once the database is created, you can create tables to store data. Use the CREATE TABLE statement to define the table's structure, including column names, data types, constraints, and indexes.

Managing Data:

You can insert, update, and delete data in your tables using SQL statements. For example:

INSERT INTO is used to add new rows of data.

UPDATE is used to modify existing data.

DELETE is used to remove data.

INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate)

VALUES (1, 'Ram', 'Sharma', '2022-01-15');

UPDATE Employees

SET FirstName = 'Sham'

WHERE EmployeeID = 1;

DELETE FROM Employees

WHERE EmployeeID = 1;

Output:

Indexing:

To optimize query performance, you can create indexes on columns that are frequently used in search and join operations. SQL Server provides different types of indexes, including clustered and non-clustered indexes.

Backup and Restore:

Regularly back up your databases to prevent data loss. SQL Server offers various backup and restore options. You can use SSMS or SQL statements like BACKUP DATABASE and RESTORE DATABASE to manage backups.

Security and Permissions:

SQL Server allows you to control access to your database by setting permissions and roles. You can create logins, users, and assign specific permissions to control who can view, modify, or administer the database.

Maintenance Plans:

SQL Server includes tools for creating maintenance plans to automate tasks like backups, index rebuilds, and database consistency checks.

Monitoring and Optimization:

Use SQL Server's built-in tools and performance monitoring features to identify and resolve performance issues. SQL Profiler, Query Store, and Dynamic Management Views (DMVs) are valuable resources for optimization.

Dropping Databases:

To remove a database, you can use the `DROP DATABASE` statement:

DROP DATABASE YourDatabaseName;

Be cautious when dropping databases, as this action permanently deletes all data and objects associated with the database.

2.4 Creating and Modifying Tables

In Microsoft SQL Server, creating and modifying...