Database Backup and Storage Strategies in SQL Server

what is SQL?SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in a relational database.SQL is the standard language for Relational Database System. All the Relational Database Management Systems (RDMS) like MySQL, MS Access, Oracle,

Why SQL?It’s easy to find people who know SQL. It’s easy to connect to standard tools. Structured Query Language (SQL) is a programming language used to communicate with databases. It is a powerful and complete language used interactively with databases containing hundreds and thousands of stored procedures, views and data. It is used to create and maintain databases. It also is used to manage database security. SQL is used for updating, retrieving and sharing data with users and external applications. If data needs to be ordered or structured in a different view, this can be done with SQL.

SQL CommandsThe SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP.

Different Types of SQL Commands

DDL (Data Definition Language)DML (Data Manipulation Language)DQL (Data Query Language)DCL (Data Control Language)Data administration commands.Transactional control commands.

Data Definition Language (DDL) - These SQL commands are used for creating, modifying, and dropping the structure of database objects. The commands are CREATE, ALTER, DROP, RENAME, and TRUNCATE.

Examples of DDL commands:

CREATE – is used to create the database objects (like table, views, store procedure and triggers).DROP – is used to delete objects from the database.ALTER-is used to alter the structure of the database.TRUNCATE–is used to remove all records from a table, including all spaces allocated for the records are removed.RENAME –is used to rename an object existing in the database.

Data Manipulation Language (DML) - These SQL commands are used for storing, retrieving, modifying, and deleting data. These Data Manipulation Language commands are: SELECT, INSERT, UPDATE, and DELETE.

Examples of DML commands:

SELECT – is used to retrieve data from the a database.INSERT – is used to insert data into a table.UPDATE – is used to update existing data within a table.DELETE – is used to delete records from a database table.

Transaction Control Language (TCL) - These SQL commands are used for managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT.

Examples in TCL commands:

COMMIT– commits a overall Transaction.ROLLBACK– rollbacks a transaction in case of any error like occurs.SAVEPOINT–is sets a savepoint within a transaction.

Data Control Language (DCL) - These SQL commands are used for providing security to database objects. These commands are GRANT and REVOKE.

Examples of DCL commands:

GRANT- It gives user’s access privileges to a database.REVOKE- Is withdraw user’s access privileges given by using of the GRANT command.

SQL Database Data Types

DATA TYPES represents the type of data an object is holding. Data Types are defined for columns of a table, local/global variables, input/output arguments of procedures etc..

Each database system (MS SQL Server, MYSQL, DB2, Oracle etc.) have its own long list of data types but several data types are common in most of them. This article will list down common data types across various database systems.

Views In SQL:

A VIEW is a virtual table, through which a selected portion of the data from one or more tables can be seen. Views do not contain data of their own. They are used to restrict access to the database or to hide data complexity. A view is stored as a SELECT statement in the database. DML operations on a view like INSERT, UPDATE, DELETE affects the data in the original table upon which the view is based.

Syntax to create a sql view is:

CREATE VIEW view_name AS SELECT column_list FROM table_name [WHERE condition];

Example:

CREATE VIEW view_product AS SELECT product_id, product_name FROM product;

Joins In SQL

SQL Joins are used to relate information in different tables. A Join condition is a part of the sql query that retrieves rows from two or more tables. A SQL Join condition is used in the SQL WHERE Clause of select, update, delete statements.

Types of SQL JOINs

(INNER) JOIN: It Returns records that have matching to all values in both tables

Syntax:

SELECT table1.column1,table1.column2,table2.column1,....FROM table1 INNER JOIN table2ON table1.matching_column = table2.matching_column;

LEFT (OUTER) JOIN: It Returns all records from the left table, and the matched records from the right table

Syntax:

SELECT table1.column1,table1.column2,table2.column1,....FROM table1 LEFT JOIN table2ON table1.matching_column = table2.matching_column;

RIGHT (OUTER) JOIN: It Return all records from the right table, and the matched records from the left table

Syntax:

SELECT table1.column1,table1.column2,table2.column1,....FROM table1 RIGHT JOIN table2ON table1.matching_column = table2.matching_column;

FULL (OUTER) JOIN: It Returns all records when there is a match in either left or right table

Syntax:

SELECT table1.column1,table1.column2,table2.column1,....FROM table1 FULL JOIN table2ON table1.matching_column = table2.matching_column;

SQL Syntax for joining two tables is:

SELECT col1, col2, col3...FROM table_name1, table_name2 WHERE table_name1.col2 = table_name2.col1;

SQL CREATE TABLE Statement: The CREATE TABLE Statement is used to create tables to store data. Integrity Constraints like primary key, unique key, foreign key can be defined for the columns while creating the table. The integrity constraints can be defined at column level or table level. The implementation and the syntax of the CREATE Statements differs for different RDBMS.

Syntax for the CREATE TABLE Statement is:

CREATE TABLE table_name (column_name1 datatype, column_name2 datatype,... column_nameN datatype );

For Example:

CREATE TABLE employee ( id number(5), name char(20), dept number(10), salary number(10), location char(10) );

SQL GRANT REVOKE Commands:

DCL commands are used to enforce database security in a multiple user database environments. Two types of DCL commands are the GRANT and REVOKE. It can be Only Database Administrator's or owner's of the database of the object can provide/remove privileges on a database object.

Syntax for GRANT command:

GRANT privilege_name ON object_name TO {user_name |PUBLIC |role_name} [WITH GRANT OPTION]

ConclusionHowever, flexibility is the most significant strength of this SQL server. The user can add new functionalities to the test scripts and framework. Being open source, you can easily modify and download anytime.Having sound knowledge of SQL server or databases professional having the same provides maximum testing benefits.Click Here: https://mindmajix.com/sql-server/

Hello, My name is miaavery, I am passionate about being updated on recent IT and business technology innovations. I was wondering if I could get an opportunity to post one of my written pieces on your site regarding any topic based on your niche.