Client/Server Architecture

Other Client/Server
Architectures
SQL
S-Q-L or sequel
 SQL is both an ANSI and ISO
standard language based on the
relational model, designed for
querying and managing data in
an RDBMS
SQL used a declarative
programming paradigm not
imperative
SQL Categories
DDL: stands for Data Definition
Language, deals with object
definition and include statements
such as CREATE, ALTER, DROP.
DML: stands for Data Manipulation
Language DML allows you to query
and modify data and includes
statements such as SELECT,
INSERT, UPDATE, DELETE,
TRUNCATE, and MERGE.
SQL Categories(Cont.)
DCL: stands for data control
language, deals with permissions
and includes statements such as
GRANT and REVOKE
 T-SQL
 Microsoft’s and Sybase’s proprietary
extension to SQL.
 T-SQL expands on the SQL standard
to include:
1-Procedural Programming
2-Local Variables
3-various support functions for string
processing
4-changes to the UPDATE and DELETE
statements.
SQL Server History
year Release name Code name

SQL Server 1.0

1989 -
(16 bit)

SQL Server 1.1

1991 -
(16 bit)

1993 SQL Server 4.21 SQLNT

1995 SQL Server 6.0 SQL95

1996 SQL Server 6.5 Hydra
1998 SQL Server 7.0 Sphinx

SQL Server 7.0

1999 Palato mania
OLAP Tools

2000 SQL Server 2000 Shiloh

SQL Server 2000

2003 Liberty
64-bit Edition

2005 SQL Server 2005 Yukon

2008 SQL Server 2008 Katmai
2010 SQL Azure DB CloudDatabase

2010 SQL Server 2008 R2 Kilimanjaro (aka KJ)

2012 SQL Server 2012 Denali

2014 SQL Server 2014
SQL Server Editions
Description Edition
This is considered the Enterprise
premium edition of SQL
Server. This edition is all-
inclusive, meaning that it
contains all the features
available in every edition.
that supports a high
level of mission-critical
workloads, blazing-fast
performance.
This is a new edition to the Business Intelligence
SQL Server family. It is
focused on delivering all-
encompassing BI-focused
solutions.
it encompasses basic data Standard
management and BI
capabilities that are more in
SQL Server Editions

Description Edition
This is identical to the Developer
Enterprise edition, except that
it is only licensed for
development and test
systems. You cannot use it for
production purposes.
The Express version of SQL Express
Server is a great entry-level
product. It is perfect for
learning and building small
data-driven applications.
 SQL Server Components
Description Component
Responsible for storage and Database Engine
processing.

SSIS, SSAS, SSRS Business Intelligence

Database Engine:
The SQL Server Database Engine,
sometimes called the Relational
Engine, is the core of SQL Server.
It is the component that handles
all the relational database work.
SQL is a descriptive language,
meaning it describes only the
question to the engine; the
engine takes over from there.
SQL Server Management tools
Desc. Management tool
SQL Server Management SQL Server Management
Studio is an integrated Studio
environment to access,
configure, manage,
administer, and develop
components of SQL Server.
SQL Server Configuration SQL Server Configuration
Manager provides basic Manager
configuration management for
SQL Server services, server
protocols, client protocols, and
client aliases.
SQL Server Profiler provides a SQL Server Profiler
graphical user interface to
monitor an instance of the
Database Engine or Analysis
Services.
SQL Server Management tools

Desc. Management tool

SQL Server Data Tools (SSDT) SQL Server Data Tools
provides an IDE for building
solutions for the Business
Intelligence components:
Analysis Services, Reporting
Services, and Integration
Services.
Installs components for Connectivity Components
communication between
clients and servers, and
network libraries for DB-
Library, ODBC, and OLE DB.
SQL Server authentication
When SQL Server was installed,
one of the decisions made was
which of the following
authentication
methods to use:
■ Windows Authentication mode:
Windows authentication only
■ Mixed mode: Both Windows
authentication and SQL Server user
authentication
SQL server databases:
Master DB

Temp DB

Model

msdb
Select statement
The purpose of a SELECT
statement is to query tables, and
return a result.
SELECT [DISTINCT][TOP (n)] *, columns, or
expressions
[FROM data source(s)]
[into newtable]
[JOIN data source
ON condition](may include multiple joins)
[WHERE conditions]
[GROUP BY columns]
[HAVING conditions]
[ORDER BY Columns];
]Table Name[
Ifthe name of a database object,
such as a table or column name,
conflicts with a SQL reserved
keyword, you can let SQL know
that it’s the name of an object by
placing it inside square brackets.
Note that the square brackets are
specific to SQL Server and not
part of the ANSI SQL standard.
Selecting All Columns

Example For Retrieving All

Columns
Of Table
Select *
From Employees
Selecting Specific Columns

Example For Retrieving Specific

Columns Of Table

Select Employeeid,Salary,Address
From Employees
Column aliases
Renames Column Heading
Useful With Calculation
Follows Column Name (Optional
As Keyword Between CN and
Alias)
Require Double quotation If it
contains
FROM Employees spaces Or Special
SELECT Employeeid,Salary as “Employee Salary”

Characters Or Case Sensitive

SELECT Employeeid,salary
FROM Employees
Concatenation Operator

Links Columns or Character

Strings To Other Columns
Represented By (+)
Creates Resultant column That is
Character Expression

Select EmployeeName + address

From Employees
Literal Column Strings

Specify Own quotation Mark

delimiter
Choose delimiter
Increase Readability
Select EmployeeName + ‘ The Address is ’ +
address
From Employees

Select EmployeeName + ‘ it’’s address: ’

From Employees
Select Distinct
eliminates duplicate rows from
the result set of the query.

SELECTDISTINCT Salary
From Employee
Using Arithmetic
Operators
SELECT Employeeid, Salary +
3000
From Employee
Operators Precedence

SELECT
Employeeid,300+Salary*10 From
Employee

SELECT
Employeeid,10*(Salary+3000) From
Employee
Defining Null Value

A null is value that is

unavailable,unknown,unassigned
A null is not zero or blank space

Select
Employeeid , MobileNo
From Employee
Null Values In
Arithmatic Expressions

SelectEmployeeid ,Salary * 10
From Employee
Where Conditions
The WHERE conditions filter the
output of the FROM clause and
restrict the rows that will be
returned in the result set. The
conditions can refer to the data
within the tables, expressions,
built-in SQL Server scalar
functions, or user-defined
functions.
Comparison operator
Using the between search
condition
greater than or equal to the first
value, and less than or equal to
the second value.
SELECT EmployeeName , salary
FROM Employees
WHERE salary > 2000

SELECT EmployeeName , salary

FROM Employees
WHERE salary between 2000 and 4000
Comparing with a list
The WHERE condition can
compare the test value against
the values in a list using IN. IN
operator can also be mixed with a
NOT to reverse the condition.
Like operator
The LIKE search condition uses
wildcards to search for patterns
within a string.
Using NULL Condition
Select
Employeename,MobileNo
From Employee
Where Mobileno is NULL
AND Condition
Returns True If Both Conditions
are True

Select
Employeename,MobileNo,salary
From Employee
Where salary >3000
And employeename like ‘ad%’
OR Condition
Returns True If One Condition is
True

Select
Employeename,MobileNo,salary
From Employee
Where salary >3000
OR employeename like ‘ad%’
NOT Condition
Returns True If Both Conditions
are False

Select
Employeename,MobileNo,salary
From Employee
Where salary not in
(2000,3000,4000)
Ordering the Result Set
SQL Server usually returns the data
in the order of the primary key
(because that’s probably the
clustered index), but there’s no
logical guarantee of that order.
The only correct way to sort the
results is with an ORDER BY clause.
Sort order can be specified as ASC
(ascending) or DESC (descending)
for each column.
Top
 TOP() predicate tells SQL Server to return only a few rows
(either a fixed number or a percentage) based upon the
options specified.

 TOP() works hand-in-hand with ORDER BY. It’s the ORDER BY

clause that determines which rows are first. If the SELECT
statement does not have an ORDER BY clause, then the
TOP() predicate still works by returning an unordered
sampling of the result set.
Selecting a random row
Using the TOP(1) predicate will
return a single row, and sorting
the result set by newid()
randomizes the sort. Together
they will return a random row
each time the query is executed.
Example:
 select top(1)* from Instructor order by newid();