MS_SQL CHAPTER ONE

Presenter:
Eng: Abdiwahab Osman Siad
 MS-SQL

• SQL (Structured Query Language) is used to perform operations on

the records stored in the database, such as updating records, inserting
records, deleting records, creating and modifying database tables,
views, etc.
Cont..

• SQL is not a database system, but it is a query language.

• Suppose you want to perform the queries of SQL language on the

stored data in the database. You are required to install any database
management system in your systems, for example, Oracle, MySQL,
MongoDB, PostgreSQL, SQL Server, DB2, etc.
 What is SQL?

• SQL is a short-form of the structured query language, and it is pronounced as S-

Q-L or sometimes as See-Quell.

• This database language is mainly designed for maintaining the data in relational
database management systems. It is a special tool used by data professionals for
handling structured data (data which is stored in the form of tables). It is also
designed for stream processing in RDSMS.
 Cont..

• You can easily create and manipulate the database, access and modify the
table rows and columns, etc. This query language became the standard of
ANSI in the year of 1986 and ISO in the year of 1987.

• If you want to get a job in the field of data science, then it is the most
important query language to learn. Big enterprises like Facebook,
Instagram, and LinkedIn, use SQL for storing the data in the back-end.
 Why SQL?

• Nowadays, SQL is widely used in data science and analytics.

Following are the reasons which explain why it is widely used:

• The basic use of SQL for data professionals and SQL users is to insert,
update, and delete the data from the relational database.

• SQL allows the data professionals and users to retrieve the data from
the relational database management systems.

• It also helps them to describe the structured data.

Cont..

• It allows SQL users to create, drop, and manipulate the database and its tables.

• It also helps in creating the view, stored procedure, and functions in the relational
database.

• It allows you to define the data and modify that stored data in the relational
database.

• It also allows SQL users to set the permissions or constraints on table columns,
views, and stored procedures.
 History of SQL

• "A Relational Model of Data for Large Shared Data Banks" was a paper which
was published by the great computer scientist "E.F. Codd" in 1970.

• The IBM researchers Raymond Boyce and Donald Chamberlin originally

developed the SEQUEL (Structured English Query Language) after learning from
the paper given by E.F. Codd. They both developed the SQL at the San Jose
Research laboratory of IBM Corporation in 1970.
Cont..

• At the end of the 1970s, relational software Inc. developed their own
first SQL using the concepts of E.F. Codd, Raymond Boyce, and Donald
Chamberlin. This SQL was totally based on RDBMS. Relational
Software Inc., which is now known as Oracle Corporation, introduced
the Oracle V2 in June 1979, which is the first implementation of SQL
language. This Oracle V2 version operates on VAX computers.
 Some SQL Commands

• The SQL commands help in creating and managing the database. The
most common SQL commands which are highly used are mentioned
below:
Cont..

1.CREATE command

2.UPDATE command

3.DELETE command

4.SELECT command

5.DROP command

6.INSERT command
 CREATE Command

• This command helps in creating the new database, new table, table
view, and other objects of the database.
 UPDATE Command

• This command helps in updating or changing the stored data in the

database.
 DELETE Command

• This command helps in removing or erasing the saved records from

the database tables. It erases single or multiple tuples from the tables
of the database.
 SELECT Command

• This command helps in accessing the single or multiple rows from one
or multiple tables of the database. We can also use this command with
the WHERE clause.
 DROP Command

• This command helps in deleting the entire table, table view, and other
objects from the database.
 INSERT Command

• This command helps in inserting the data or records into the database
tables. We can easily insert the records in single as well as multiple
rows of the table.
 Advantages of SQL

• SQL provides various advantages which make it more popular in the

field of data science. It is a perfect query language which allows data
professionals and users to communicate with the database. Following
are the best advantages or benefits of Structured Query Language:
 1. No programming needed

• SQL does not require a large number of coding lines for managing the
database systems. We can easily access and maintain the database by
using simple SQL syntactical rules. These simple rules make the SQL
user-friendly.
 2. High-Speed Query Processing

• A large amount of data is accessed quickly and efficiently from the

database by using SQL queries. Insertion, deletion, and updation
operations on data are also performed in less time.
 3. Standardized Language

• SQL follows the long-established standards of ISO and ANSI, which

offer a uniform platform across the globe to all its users.
 4. Portability

• The structured query language can be easily used in desktop

computers, laptops, tablets, and even smartphones. It can also be used
with other applications according to the user's requirements.
 Disadvantages of SQL

With the advantages of SQL, it also has some disadvantages, which are as follows:

1. Cost

The operation cost of some SQL versions is high. That's why some programmers
cannot use the Structured Query Language.

2. Interface is Complex

Another big disadvantage is that the interface of Structured query language is

difficult, which makes it difficult for SQL users to use and manage it.
 SQL Syntax

• When you want to do some operations on the data in the database,

then you must have to write the query in the predefined syntax of
SQL.

• The syntax of the structured query language is a unique set of rules

and guidelines, which is not case-sensitive. Its Syntax is defined and
maintained by the ISO and ANSI standards.
Following are some most important points about the SQL
syntax which are to remember:

• You can write the keywords of SQL in both uppercase and lowercase,
but writing the SQL keywords in uppercase improves the readability
of the SQL query.

• SQL statements or syntax are dependent on text lines. We can place a

single SQL statement on one or multiple text lines.
 SQL Statement

• SQL

• statements tell the database what operation you want to perform on the
structured data and what information you would like to access from
the database.

• The statements of SQL are very simple and easy to use

and understand. They are like plain English but with a
particular syntax.
Simple Example of SQL statement:

• SELECT "column_name" FROM "table_name";

• Each SQL statement begins with any of the SQL keywords

and ends with the semicolon (;). The semicolon is used in the
SQL for separating the multiple Sql statements which are
going to execute in the same call.
 Most Important SQL Commands and Statements
1.Select Statement
2.Update Statement
3.Delete Statement 12.Distinct Clause
4.Create Table Statement 13.Commit Statement
5.Alter Table Statement 14.Rollback Statement
6.Drop Table Statement 15.Create Index Statement
7.Create Database Statement
16.Drop Index Statement
8.Drop Database Statement
9.Insert Into Statement 17.Use Statement
10.Truncate Table Statement
11.Describe Statement
Let's discuss each statement in short one by one with
syntax and one example:

• 1. SELECT Statement

• This SQL statement reads the data from the SQL database and shows it as
the output to the database user.

• Syntax of SELECT Statement:

1.SELECT column_name1, column_name2, .
…, column_nameN
2. [ FROM table_name ]
3. [ WHERE condition ]
4. [ ORDER BY order_column_name1 [ ASC | DESC ], .... ];
 Example of SELECT Statement:
1. SELECT Emp_ID, First_Name, Last_Name, Salary, City

2. FROM Employee_details

3. WHERE Salary = 100000

4. ORDER BY Last_Name

5. This example shows the Emp_ID, First_Name, Last_Name, Salary, and City of those
employees from the Employee_details table whose Salary is 100000. The output shows
all the specified details according to the ascending alphabetical order of Last_Name.
 3. UPDATE Statement

• This SQL statement changes or modifies the stored data in the SQL
database.

• Syntax of UPDATE Statement:

1.UPDATE table_name
2.SET column_name1 = new_value_1, column_name2 =
new_value_2, ...., column_nameN = new_value_N
3.[ WHERE CONDITION ];
Example of UPDATE Statement:

1.UPDATE Employee_details

2.SET Salary = 100000

3.WHERE Emp_ID = 10;

• This example changes the Salary of those employees of

the Employee_details table whose Emp_ID is 10 in the table.
 3. DELETE Statement

• This SQL statement deletes the stored data from the SQL database.

• Syntax of DELETE Statement:

1.DELETE FROM table_name

2.[ WHERE CONDITION ];

 Example of DELETE Statement:

1.DELETE FROM Employee_details

2.WHERE First_Name = 'Sumit';

• This example deletes the record of those employees from

the Employee_details table whose First_Name is Sumit in the table.
4. CREATE TABLE Statement
• This SQL statement creates the new table in the SQL
database.
Syntax of CREATE TABLE Statement:

1. CREATE TABLE table_name

column_name1 data_type [column1 constraint(s)],

column_name2 data_type [column2 constraint(s)],

column_nameN data_type [columnN constraint(s)],

PRIMARY KEY(one or more col)

);
 Example of CREATE TABLE Statement:
CREATE TABLE Employee_details(
Emp_Id NUMBER(4) NOT NULL,
First_name VARCHAR(30),
Last_name VARCHAR(30),
Salary Money,
City VARCHAR(30),
PRIMARY KEY (Emp_Id)
);
Cont..

• This example creates the table Employee_details with five columns or

fields in the SQL database. The fields in the table are Emp_Id,
First_Name, Last_Name, Salary, and City. The Emp_Id column in
the table acts as a primary key, which means that the Emp_Id column
cannot contain duplicate values and null values.
 5. ALTER TABLE Statement

• This SQL statement adds, deletes, and modifies the columns of the table in the
SQL database.

• Syntax of ALTER TABLE Statement:

• ALTER TABLE table_name ADD column_name datatype[(size)];

• The above SQL alter statement adds the column with its
datatype in the existing database table.
Cont…

• ALTER TABLE table_name MODIFY column_name column_datatyp

e[(size)];

• The above 'SQL alter statement' renames the old column name to the
new column name of the existing database table.

• ALTER TABLE table_name DROP COLUMN column_name;

• The above SQL alter statement deletes the column of the existing
database table.
Example of ALTER TABLE
Statement:
1.ALTER TABLE Employee_details ADD Designation VARCHAR(1
8);

• This example adds the new field whose name is Designation with
size 18 in the Employee_details table of the SQL database.
INSERT INTO Statement

• This SQL statement inserts the data or records in the existing table of
the SQL database. This statement can easily insert single and multiple
records in a single query statement.
Syntax of insert a single record:
INSERT INTO table_name
(
column_name1,
column_name2, .…,
column_nameN
)
VALUES
(value_1,
value_2, ..…,
value_N
);
Example of insert a single record:
INSERT INTO Employee_details
(
Emp_ID,
First_name,
Last_name,
Salary,
City
)
VALUES
(101,
Akhil,
Sharma,
40000,
Bangalore
);
Cont..

• This example inserts 101 in the first column, Akhil in the second
column, Sharma in the third column, 40000 in the fourth column,
and Bangalore in the last column of the table Employee_details.

• Syntax of inserting a multiple records in a single query:

Cont..

1.INSERT INTO table_name

2.( column_name1, column_name2, .…, column_nameN)

3.VALUES (value_1, value_2, ..…, value_N), (value_1, value_2, ..

…, value_N),….;