MS SQL Server: An Overview

• MS SQL Server is a relational database management system (RDBMS) and an object-relational database management system (ORDBMS) developed by Microsoft.
• MS SQL Server is platform-dependent.
• MS SQL Server has both GUI (Graphical User Interface) and command-based functionalities.
• MS SQL Server supports SQL (Structured Query Language), originally developed by IBM.
• SQL is a non-procedural, case-insensitive language for database management.
• Microsoft SQL Server is the query language used for data definition and manipulation.
• One can create and also maintain databases thanks to SQL Server
• It helps to analyse data through SQL Server Analysis Services (SSAS) and to generate reports through SQL Server Reporting Services (SSRS).
• MS SQL Server helps to in carrying out ETL (Extract, Transform, Load) operations through SQL Server Integration Services (SSIS).
• Featured highlights of SQL Server 2022 (16.x) include: Analytics, Availability, Security, Performance, Query Store & Intelligent Query Processing, Management, and Platform improvements.

Similarities between SQL Server and MySQL

• Both SQL Server and MySQL use Structured Query Language (SQL) for interacting with relational databases.
• Both are scalable and high-performing database systems that support multiple databases.
• They share three primary data types: Numeric, Date & Time, and String.

Differences between Microsoft SQL Server and MySQL

• Microsoft SQL Server 2019 is a commercial product and requires a license, versus MySQL, which is open-source.
• MS SQL Server was initially designed for Windows; it became available for Mac & Linux in 2016.
• MySQL is compatible with Windows, Linux, macOS, Solaris, and FreeBSD.
• MS SQL Server supports Windows Server 2019, 2016, 2022, as well as Windows 10 & 11.
• Both SQL Server and MySQL support multiple languages, including C++, Visual Basic, Python, Go, R, and Java.
• PHP, Ruby and Delphi are also supported
• Both SQL Server and MySQL are EC2 compliant and offer data encryption, authentication, and authorization.
• MS SQL Server 2019 has advanced security features, requiring an instance of the database to be run for access.
• MySQL allows direct file manipulation, which can pose a security risk.
• Both databases use indexes to accelerate performance and can host multiple databases on a single server.
• MySQL locks the entire database during backup, which can cause disruptions in large-scale databases.
• MS SQL Server supports only one default storage engine (InnoDB), while MySQL supports multiple storage engines, including MyISAM and InnoDB.
• Both databases use similar syntax, but there are differences in CRUD (Create, Read, Update, Delete) statements.
• MS SQL syntax is simpler and easier to use, while MySQL syntax is slightly more complex.
• MySQL allows filtering of tables, rows, and users but only on an individual database basis, and multiple queries must be executed separately.
• Both databases use the SELECT command for retrieving data but have different accompanying syntax and also for string concatenation.
• MySQL: SELECT CONCAT('My', 'SQL');
• MS SQL Server: SELECT ('SQL' + 'SERVER');
• MySQL: SELECT UUID();
• MS SQL Server: SELECT NEWID();

Structured Query Language (SQL)

• SQL (Structured Query Language) is a database computer language designed for managing data in relational database management systems (RDBMS).
• SQL is a standardized computer language originally developed by IBM for querying, altering, and defining relational databases using declarative statements.
• SQL can execute queries against a database and retrieve data from a database.
• SQL can insert, update, and delete records from a database.
• SQL can create new databases, tables, and stored procedures in a database.
• SQL can create views in a database and set permissions on tables, procedures, and views.

Popular Database Management Systems

• Microsoft SQL Server
  • Enterprise, Developer versions, etc.
  • Express version is free of charge.
• Oracle
• MySQL (Oracle, previously Sun Microsystems)
  • Can be used free of charge (open-source license).
  • Popular websites using MySQL: YouTube, Wikipedia, Facebook.
• Microsoft Access
• IBM DB2
• Sybase

SQL Data Types

• char(n) - Fixed-length character string with user-specified length n.
• varchar(n) – Variable-length character string with user-specified max length n.
• int - Integer (machine-dependent finite subset of integers).
• smallint - Small integer (subset of int).
• numeric(p,d) – Fixed-point number with precision p and d digits to the right of the decimal. (e.g., numeric(3,1) allows 44.5 but not 444.5 or 0.32).
• real, double precision – Floating-point and double-precision floating-point numbers.
• float(n) - Floating-point number with at least n digits of precision.

Categories of SQL Commands

• Data Definition Language (DDL) – Defines database structure.
• Data Manipulation Language (DML) – Modifies data within tables.
• Data Query Language (DQL) – Retrieves data from databases.
• Data Control Language (DCL) – Manages access control.

Data Definition Language (DDL)

• DDL manages table and index structures.
• CREATE – Creates an object (e.g., a table) in the database.
• DROP – Deletes an object from the database, often irreversibly.
• ALTER – Modifies the structure of an existing object (e.g., adding a column to a table).

Data Manipulation Language (DML)

• DML allows users to add, update, and delete data.
• CRUD Operations in SQL:
  • Create → INSERT
  • Read → SELECT
  • Update → UPDATE
  • Delete → DELETE

Data Query Language (DQL)

• DQL is used to retrieve information from schema objects.
• SELECT Command: most commonly used SQL statement, retrieves or fetches data from a database.

Data Control Language (DCL)

• DCL is used to control access to data in a database.
• GRANT – Allows specified users to perform specified tasks.
• REVOKE – Cancels previously granted or denied permissions.

Common SQL Commands

• DML (Data Manipulation Language)
  • INSERT - Adds new records.
  • UPDATE - Modifies existing records.
  • DELETE - Removes records.
• DDL (Data Definition Language)
  • CREATE - Defines new database objects.
  • ALTER - Modifies database structure.
  • DROP - Removes database objects.
  • RENAME - Renames database objects.
  • TRUNCATE – Removes all records from a table.
  • COMMENT - Adds comments to metadata.

Insert and Select SQL Command

• Insert SQL Command Syntax: INSERT INTO table_name VALUES (value1, value2, value3, ...);
• Select SQL Command Syntax: SELECT * FROM table_name;

SQL DDL (Data Definition Language) Overview

• SQL DDL is a subset of SQL and a part of DBMS (Database Management System).
• It consists of commands like CREATE, ALTER, TRUNCATE, DROP, and RENAME, which are used to define and modify database structures such as tables.

DDL Commands

• CREATE - Creates a new table.
• ALTER – Modifies an existing table.
• TRUNCATE – Removes all records from a table but retains its structure.
• DROP - Deletes a table completely.
• RENAME – Renames an existing table.

CREATE Command

• This command is used to create a new table in SQL. The user must specify the table name, column names, and their respective data types.
• Syntax:

CREATE TABLE table_name (
    column_1 datatype,
    column_2 datatype,
    column_3 datatype,
    ...
);

• Example:

CREATE TABLE tblDeptInfo (
    deptID INT,
    deptCode VARCHAR(30),
    deptName VARCHAR(30),
    PRIMARY KEY(deptID)
);

ALTER Command

• This command is used to add, delete, or modify columns in an existing table.
• The user must know the table name to make the required changes.
• Syntax (Adding a column): ALTER TABLE table_name ADD column_name datatype;
• Example: ALTER table tblDeptInfo ADD deptArea VARCHAR(30);

TRUNCATE Command

• Used to remove all rows from a table while keeping the table structure intact.
• Syntax: TRUNCATE TABLE table_name;
• Example: TRUNCATE TABLE tblDeptInfo;

DROP Command

• Used to remove an existing table along with its structure from the database.
• Syntax: DROP TABLE table_name;
• Example: DROP TABLE tblDeptInfo;

RENAME Command

• Used to change the name of an existing table in SQL.
• Syntax: SP_RENAME old_table_name, new_table_name;
• Example: SP_RENAME tblDeptInfo, tblDepartmentInfo;

QL DML (Data Manipulation Language) Overview

• Data Manipulation Language (DML) allows users to interact with existing data in a database by adding, modifying, retrieving, or deleting records.
• It provides a structured way to manipulate data within tables.

DML Commands

• SELECT - Retrieves data from the database.
• INSERT – Adds new records to a table.
• UPDATE – Modifies existing records in a table.
• DELETE – Removes specific records from a table.

SELECT Command

• Used to retrieve data from a database and fetch relevant data based on specific conditions.
• Syntax: SELECT * FROM table_name;
• Example to Retrieve all records: SELECT * FROM tblStudent;
• Syntax to Retrieve records where the grade is 95: SELECT * FROM tblStudent WHERE grade = 95;

INSERT Command

• Used to add new records into a table allowing for insertion of one or multiple rows.
• Syntax: INSERT INTO table_name (column_name1, column_name2, column_name3) VALUES (value1, value2, value3);
• Example of Single Record Insertion: INSERT INTO tblDeptInfo (deptID, deptCode, deptName) VALUES (1, 23001, 'CS');
• Example of Multiple Records Insertion:

INSERT INTO tblDeptInfo (deptID, deptCode, deptName)
VALUES
(2, 23002, 'IT'),
(3, 23003, 'COE');

UPDATE Command

• Used to modify existing records in a table based on specific conditions.
• Syntax:

UPDATE table_name
SET column_name = value
WHERE condition;

• Example to update the deptName of the department with deptID = 3:

UPDATE tblDeptInfo
SET deptName = 'Archi'
WHERE deptID = 3;

DELETE Command

• Used to remove one or more records from a table, deleting all records if no WHERE condition is specified.
• Syntax: DELETE FROM table_name WHERE condition;
• Example
  • Delete a specific record where deptID = 3: DELETE FROM tblDeptInfo WHERE deptID = 3;
  • Delete all records from the table (but keep the structure): DELETE FROM tblDeptInfo;