SQL Structured Query Language What is SQL Server

SQL Structured Query Language

What is SQL Server? • SQL is a standard language for storing, manipulating and retrieving data in databases.

Database

What Can SQL do? • • • SQL can execute queries against a database SQL can retrieve data from a database SQL can insert records in a database SQL can update records in a database SQL can delete records from a database SQL can create new databases SQL can create new tables in a database SQL can create stored procedures in a database SQL can create views in a database SQL can set permissions on tables, procedures, and views

RDBMS • RDBMS stands for Relational Database Management System. • RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB 2, Oracle, My. SQL, and Microsoft Access. • The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows.

SQL Statements Most of the actions you need to perform on a database are done with SQL statements. The following SQL statement selects all the records in the "Customers" table: SELECT * FROM Customers;

DDL • DDL is short name of Data Definition Language, which deals with database schemas and descriptions, of how the data should reside in the database.

DDL Commands • CREATE - to create database and its objects like (table, index, views, store procedure, function, and triggers) • ALTER - alters the structure of the existing database • DROP - delete objects from the database • TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed • RENAME - rename an object

DML • DML is short name of Data Manipulation Language which deals with data manipulation and includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE etc, and it is used to store, modify, retrieve, delete and update data in database.

DML Commands • SELECT - retrieve data from a database • INSERT - insert data into a table • UPDATE - updates existing data within a table • DELETE - Delete all records from a database table

DCL is short name of Data Control Language which includes commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system.

DCL Commands • GRANT - allow users access privileges to database • REVOKE - withdraw users access privileges given by using the GRANT command

Lets start with DML

Most of the actions you need to perform on a database are done with SQL statements. The following SQL statement selects all the records in the "Student" table: • SELECT * FROM Student;

The SQL SELECT Statement • The SELECT statement is used to select data from a database. • The data returned is stored in a result table, called the result-set. SELECT Syntax SELECT column 1, column 2, . . . FROM table_name; SELECT Student. Name, City FROM Student;

The SQL WHERE Clause • The WHERE clause is used to filter records. • The WHERE clause is used to extract only those records that fulfill a specified condition. SELECT Syntax SELECT column 1, column 2, . . . FROM table_name; WHERE conditions SELECT * FROM Student WHERE Country='India';

The SQL AND, OR and NOT Operators • The WHERE clause can be combined with AND, OR, and NOT operators. • The AND and OR operators are used to filter records based on more than one condition: ▫ The AND operator displays a record if all the conditions separated by AND is TRUE. ▫ The OR operator displays a record if any of the conditions separated by ▫ OR is TRUE. SELECT Syntax SELECT column 1, column 2, . . . FROM table_name WHERE condition 1 AND condition 2 AND condition 3. . . ; SELECT * FROM Student WHERE Country='India' AND City='Chennai';

The SQL ORDER BY Keyword The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword. Syntax: SELECT column 1, column 2, . . . FROM table_name ORDER BY column 1, column 2, . . . ASC|DESC; Sample: SELECT * FROM Student ORDER BY Country ASC;

The SQL INSERT INTO Statement • The INSERT INTO statement is used to insert new records in a table. Syntax: INSERT INTO table_name (column 1, column 2, column 3, . . . ) VALUES (value 1, value 2, value 3, . . . ); Example: INSERT INTO Student (Student. Name, Contact. Name, Address, City, Postal. Code, Country) VALUES ('Vijay', Vijaya kumar Suresh', 'OMR', 'Chennai', '600097', 'India');

What is a NULL Value? A field with a NULL value is a field with no value. If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value. Syntax: SELECT column_names FROM table_name WHERE column_name IS NULL;

The SQL UPDATE Statement The UPDATE statement is used to modify the existing records in a table. UPDATE Syntax: UPDATE table_name SET column 1 = value 1, column 2 = value 2, . . . WHERE condition; Example: UPDATE Student SET Contact. Name = 'Vijaya kumar Suresh', City= 'Chennai' WHERE Customer. ID = 1;

The SQL DELETE Statement The DELETE statement is used to delete existing records in a table. DELETE Syntax DELETE FROM table_name WHERE condition; Sample: DELETE FROM Student WHERE Student. Name='Jai Ganesh';

The SQL SELECT TOP Clause • The SELECT TOP clause is used to specify the number of records to return. • The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact on performance.

Aggregate functions: • The MIN() function returns the smallest value of the selected column. • The MAX() function returns the largest value of the selected column. MIN() Syntax SELECT MIN(column_name) FROM table_name WHERE condition; Example SELECT MAX(Price) AS Largest. Price FROM Products;

The SQL COUNT(), AVG() and SUM() Functions • The COUNT() function returns the number of rows that matches a specified criteria. • The AVG() function returns the average value of a numeric column. • The SUM() function returns the total sum of a numeric column. COUNT() Syntax SELECT COUNT(column_name) FROM table_name WHERE condition;

SQL JOIN • A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Different Types of SQL JOINs

• (INNER) JOIN: Returns records that have matching values in both tables.

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

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

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

INNER JOIN Syntax SELECT column_name(s) FROM table 1 INNER JOIN table 2 ON table 1. column_name = table 2. column_name ; Example SELECT Departments. Department. ID, Student. Name FROM Departments INNER JOIN Student ON Departments. Department. ID = Student. ID;

LEFT JOIN Syntax SELECT column_name(s) FROM table 1 LEFT JOIN table 2 ON table 1. column_name = table 2. column_name; Example SELECT Student. Name, Departments. Department. ID FROM Student LEFT JOIN Departments ON Student. ID = Departments. Student. ID ORDER BY Student. Name;

RIGHT JOIN Syntax SELECT column_name(s) FROM table 1 RIGHT JOIN table 2 ON table 1. column_name = table 2. column _name; Example SELECT Departments. Department. ID, Employees. Last. Name, Employees. First. Name FROM Departments RIGHT JOIN Employees ON Departments. Employee. ID = Employees. Employee. ID ORDER BY Departments. Department. ID;

SQL Self JOIN Example The following SQL statement matches Student that are from the same city: Example: SELECT A. Student. Name AS Student. Name 1, B. Student. Name AS Student. Name 2, A. City FROM Student A, Student B WHERE A. Student. ID <> B. Student. ID AND A. City = B. City ORDER BY A. City;

The SQL GROUP BY Statement The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns. Example SELECT COUNT(Student. ID), Country FROM Student GROUP BY Country;

The SQL HAVING Clause • The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. Example SELECT COUNT(Student. ID), Country FROM Student GROUP BY Country HAVING COUNT(Student. ID) > 5;

DDL • Lets start with a DDL Commands

The SQL CREATE DATABASE Statement The CREATE DATABASE statement is used to create a new SQL database. Syntax: CREATE DATABASE databasename;

The SQL DROP DATABASE Statement The DROP DATABASE statement is used to drop an existing SQL database. Syntax: DROP DATABASE databasename;

The SQL CREATE TABLE Statement The CREATE TABLE statement is used to create a new table in a database. Syntax: CREATE TABLE table_name ( column 1 datatype, column 2 datatype, column 3 datatype, . . );

The SQL DROP TABLE Statement The DROP TABLE statement is used to drop an existing table in a database. Syntax DROP TABLE table_name;

SQL TRUNCATE TABLE The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself. Syntax TRUNCATE TABLE table_name;

SQL ALTER TABLE Statement • The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. • The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

SQL Constraints • • NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK DEFAULT INDEX

SQL NOT NULL Constraint • Example CREATE TABLE Persons ( ID int NOT NULL, Last. Name varchar(255) NOT NULL, First. Name varchar(255) NOT NULL, Age int );

SQL UNIQUE Constraint CREATE TABLE Persons ( ID int NOT NULL UNIQUE, Last. Name varchar(255) NOT NULL, First. Name varchar(255), Age int );

SQL PRIMARY KEY on CREATE TABLE • The following SQL creates a PRIMARY KEY on the "ID" column when the "Persons" table is created: • CREATE TABLE Persons ( ID int NOT NULL PRIMARY KEY, Last. Name varchar(255) NOT NULL, First. Name varchar(255), Age int ); • CREATE TABLE Persons ( ID int NOT NULL, Last. Name varchar(255) NOT NULL, First. Name varchar(255), Age int, CONSTRAINT PK_Person PRIMARY KEY (ID, Last. Name) );

SQL FOREIGN KEY Constraint • A FOREIGN KEY is a key used to link two tables together. • A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. • The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.

SQL CHECK Constraint • The CHECK constraint is used to limit the value range that can be placed in a column. • If you define a CHECK constraint on a single column it allows only certain values for this column. • If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

SQL CHECK on CREATE TABLE Example: CREATE TABLE Persons ( ID int NOT NULL, Last. Name varchar(255) NOT NULL, First. Name varchar(255), Age int CHECK (Age>=18) );

SQL DEFAULT Constraint The following SQL sets a DEFAULT value for the "City" column when the "Persons" table is created: CREATE TABLE Persons ( ID int NOT NULL, Last. Name varchar(255) NOT NULL, First. Name varchar(255), Age int, City varchar(255) DEFAULT 'Sandnes' ); CREATE TABLE Departments ( ID int NOT NULL, Order. Number int NOT NULL, Order. Date date DEFAULT GETDATE() );

SQL IDENTITY Column • Its used for auto incrementing a column in a table • CREATE TABLE Persons ( ID int IDENTITY(1, 1) PRIMARY KEY, Last. Name varchar(255) NOT NULL, First. Name varchar(255), Age int );

• A subquery is a SQL query nested inside a larger query. • A subquery may occur in : ▫ - A SELECT clause ▫ - A FROM clause ▫ - A WHERE clause Example: SELECT Country FROM Student WHERE State IN (SELECT State FROM Customer WHERE Country = ‘India’)

Stored Procedure: • The MS SQL Server Stored procedure is used to save time to write code again and again by storing the same in database and also get the required output by passing parameters. Syntax: Create procedure <procedure_Name> As Begin <SQL Statement> End Go

SQL CREATE VIEW Statement • In SQL, a view is a virtual table based on the result-set of an SQL statement. • A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. • You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table. CREATE VIEW Syntax: CREATE VIEW view_name AS SELECT column 1, column 2, . . . FROM table_name WHERE condition;

Sample for VIEW Statement Sample: CREATE VIEW [Current Product List] AS SELECT Product. ID, Product. Name FROM Products WHERE Discontinued = No; Then, we can query the view as follows: SELECT * FROM [Current Product List];

TRIGGERS • Triggers are stored programs, which are automatically executed or fired when some events occur.

DCL Commands

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

REVOKE: • The REVOKE command removes user access rights or privileges to the database objects.