Introduction to Microsoft SQL Server

 ORACLE DATABASE

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database, it is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network (including the Internet). There are at least a dozen different editions of Microsoft SQL Server aimed at different audiences and for workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users. Its primary query languages are T-SQL and ANSI SQL.

Reasons to Choose Microsoft SQL Server

High performance and scalability

In many situations, Microsoft SQL Server offers better performance than an Access database. SQL Server also provides support for very large databases, up to one terabyte, which is much larger than the current limit for an Access database of two gigabytes. Finally, SQL Server works very efficiently on Microsoft Windows Servers by processing queries in parallel (using multiple native threads within a single process to handle user requests) and minimizing additional memory requirements when more users are added.

Increased availability

Microsoft SQL Server databases, can be backuped, either incremental or complete, while the database is in use. Consequently, you do not have to force users to exit the database to back up data. This means your database can be running up to 24 hours a day, seven days a week.

Improved security

Microsoft SQL Server can integrate with the Windows Server operating system security to provide a single log on to the network and the database. This makes it much easier for you to administer complex security schemes. An SQL Server database on a server is also better protected because unauthorized users can't get to the database file directly but must access the server first. Inbuilt Access security has been removed in later versions. Relying on application security features in Access is never as secure as in SQL Server.

Immediate recoverability

In case of system failure (such as an operating system crash or power outage), Microsoft SQL Server has an automatic recovery mechanism that recovers a database to the last state of consistency in a matter of minutes, with no database administrator intervention. Critical applications can be up and running again right away.

Reliable distributed data and transactions

Transaction processing is a vital requirement for a system that is designed to support critical applications, such as banking and online order entry. Microsoft SQL Server supports atomic transactions with transaction logging, which guarantees that all changes performed within a transaction are either committed or rolled back.

Consistency and recoverability of a database transaction are guaranteed even in the case of system failure and in the middle of complex updates by more than one user. SQL Server treats all database changes inside a transaction as a single unit of work. By definition, either an entire transaction is completed safely and all resulting changes are reflected in the database, or the transaction is rolled back—and all changes to the database are undone.

Using a two-phase commit protocol, SQL Server can even support synchronized transactions that span more than one server—ensuring that all servers on the network are maintained in a consistent state.

Server-based processing

Microsoft designed Microsoft SQL Server from the beginning as a client/server database. Data and indexes reside on a single server computer that is often accessed over the network by many client computers. SQL Server reduces network traffic by processing database queries on the server before sending results to the client. Thus client/server application can do processing where it's done best - on the server.

Applications can also use stored procedures and triggers to centralise and share application logic, business rules and policies, complex queries, and data validation and referential integrity code on the server, rather than on the client.

The Future

SQL Server is an integral part of Microsoft's core component and it's future strategy.

Increasingly, the new versions of Access, are making it easier to use Access Front End with SQL Server database, and to port access applications to SQL Server.

Course Content(MS SQL Server)

Introduction to MySQL

  • Course Goals
  • Course Lesson Map
  • MySQL Overview
  • MySQL Database Server Editions
  • MySQL Products
  • MySQL Services and Support
  • MySQL Resources
  • Example Databases

MySQL Server and Client

  • MySQL Client/Server Model
  • Communication protocols
  • MySQL Connectors
  • The LAMP Stack
  • Installation of the MySQL server
  • MySQL Server and Client Startup
  • Keyboard Editing
  • Session Logging With the tee File

Database Basics

  • Basics of Relational Databases
  • Spreadsheet Versus Database
  • Entities and Relationships
  • Relationship Categories
  • SQL Language and MySQL
  • SQL data definition language
  • SQL data manipulation language

Database Design

  • Database Modeling
  • Structure and Cardinality Diagram (ERD)
  • Keys
  • Normalization
  • Database Design
  • Viewing and Evaluating a Database

Table Data Types

  • Data Types as Part of Database Design
  • Numeric Data Types
  • Temporal Data Types
  • Character String Data Types
  • Character Set and Collation Support
  • Binary String Data Types
  • Data Type Considerations
  • The Meaning of NULL

Database and Table Creation

  • Creating a Database
  • Creating a Table
  • Showing How a Table Was Created
  • Column Options
  • Table Options
  • Table Indexing
  • Table Constraints

Basic Queries

  • The SELECT Statement
  • Troubleshooting
  • SQL Modes for Syntax Checking
  • Common SQL Modes
  • MySQL Workbench for SQL Development

Database and Table Maintenance

  • Deleting databases and tables
  • Creating a new table using an existing table
  • Confirming the creation of a new table
  • Copying an existing table structure
  • Creating a temporary table
  • Adding, removing and modifying table columns
  • Adding, removing and modifying indexes and constraints

Table Data Manipulation

  • Manipulating Table Row Data
  • The INSERT Statement
  • The REPLACE Statement
  • The UPDATE Statement
  • The DELETE Statement

Functions

  • Functions in MySQL Expressions
  • Using Functions
  • String Functions
  • Temporal Functions
  • Numeric Functions
  • Control Flow Functions
  • Aggregate Functions
  • Spaces in Function Names

Exporting and Importing Data

  • Exporting with a Query
  • Exporting with a MySQL Utility
  • Importing from a Data File
  • Importing with a MySQL Utility

Joining Tables

  • Combining Multiple Tables
  • Joining Tables with SELECT
  • Comma-Separated Joins
  • Inner Joins
  • Outer Joins
  • Table Name Aliases

Table Subqueries

  • Advantages of Using a Subquery
  • Placement of Subqueries
  • Subquery Categories
  • Subquery Result Table Types
  • Subquery Type/Placement
  • Finding Mismatches
  • Modifying Tables using Subqueries
  • Converting Joins to Subqueries

MySQL Graphical User Interface Tools

  • MySQL Workbench
  • MySQL Enterprise Monitor

Supplementary Information

  • Storage Engines
  • Creating Views
  • Transactions
  • Retrieving Metadata
  • Performance Schema
  • MySQL Enterprise Backup

Conclusion

  • Course Goals
  • MySQL Curriculum Path
  • MySQL Resources
  • Evaluation
  • Final Q&A