Transcript What is SQL?

SQL SERVER DATABASES
Lecturer:
Eng. Mohamed Adam Isak
PH.D Researcher in CS.
M.Sc. and B.Sc. of Information Technology Engineering
Senior Lecturer in University of Somalia, Mogadishu university and Dar-al-Hikma university
E-mail: [email protected].
Tell:0615648915
WWW.ENGMAISAK.BLOGSPOT.COM
February –July 2016
LECTURE 1.
BASIC CONCEPT OF SQL SERVER
Outlines
3





Why SQL Server?
How do I know if my hardware meets the requirements?
Can I just confirm that I have the right operating system?
What can I do with SQL Server?
Installing SQL Server on a Windows platform




Where to install SQL Server physically
How SQL Server runs on a machine
How security is implemented
Logon IDs for SQL Server, especially the sa (system administrator)
logon
Eng.Mohamed Adam Isak, Ph.D. Researcher
Overview
4




SQL stands for Structured Query Language
SQL lets you access and manipulate databases
SQL is an ANSI (American National Standards
Institute) standard.
SQL Server is used by:
Database Administrator (DBA)
 Programmers
 SQL Literate End-users
 Application Programs

Eng.Mohamed Adam Isak, Ph.D. Researcher
Why SQL Server?
5




SQL Server faces competition from other databases, not only from
other Microsoft products such as Microsoft Access and Microsoft
Visual FoxPro, but also from competitors such as Oracle, Sybase,
DB2, and Informix, to name a few.
Microsoft Access is found on a large number of PCs. The fact that it
is packaged with some editions of Office and has been around for a
number of years in different versions of Office has helped make this
database ubiquitous; in fact, a great number of people actually do
use the software. Unfortunately, it does have its limitations when it
comes to scalability, speed, and flexibility, but for many small, inhouse systems, these areas of concern are not an issue, as such
systems do not require major database functionality.
Oracle isn’t as user friendly from a developer’s point of view.
Then there is Sybase. Yes, i t is very much like SQL Server with one
major exception: it has no GUI front end.
Eng.Mohamed Adam Isak, Ph.D. Researcher
Why SQL Server?
6





Each database has its own SQL syntax, although they all will have the same
basic SQL syntax, known as the ANSI-92 standard. This means that the
syntax for retrieving data, and so on, is the same from one database to
another.
However, each database has its own special syntax to maintain it, and
trying to use a feature from this SQL syntax in one database may not work,
or may work differently, in another.
So SQL Server seems to be the best choice in the database marketplace,
and in many scenarios it is.
It can be small enough for a handful of users, or large enough for the
largest corporations. It doesn’t need to cost as much as Oracle or Sybase,
but it does have the ability to scale up and deal with terabytes of data
without many concerns.
As you will see, it is easy to install, as it comes as one complete package for
most of its functionality, with a simple install to be performed for the
remaining areas if required
Eng.Mohamed Adam Isak, Ph.D. Researcher
Evolution of SQL Server (1/2)
7

Eng.Mohamed Adam Isak, Ph.D. Researcher
SQL Server has evolved
over the years into the
product it is today.
Evolution of SQL Server (2/2)
8
Eng.Mohamed Adam Isak, Ph.D. Researcher
Hardware Requirements
9

CPU


Memory


The minimum recommended CPU that SQL Server will run on
is a 1GHz processor for the 32-bit edition and a 1.6GHz
for the 64-bit version, or a compatible processor, or similar
processing power; however, 2GHz is recommended
Microsoft recommends 1GB or above
Hard Disk Space

For SQL Server alone, ignoring any data files that you are
then going to add on top, you will need over 1GB of space.
Eng.Mohamed Adam Isak, Ph.D. Researcher
Operating System Requirements
10




You will find that SQL Server 2008 will run on Windows
Vista Home Basic Edition and above, as well as
Windows XP.
SQL Server 2012 will run on Windows 7 Service Pack
1 and above, as well as Vista Service Pack 2.
From the server side, it will work on Windows Server
2003 with Service Pack 2 and Windows Server 2008.
It will also work on the 64-bit operating systems for
Windows XP Professional, as well as the 64-bit editions
of Windows Server 2003 and 2008.
So there is plenty of scope for running SQL Server on
many operating systems.
Eng.Mohamed Adam Isak, Ph.D. Researcher
Installation
11


Microsoft offers a 120-day trial version at
http://www.microsoft.com/ sql/evaluation/trial/
To connect to the server after installation process
completion:
Eng.Mohamed Adam Isak, Ph.D. Researcher
The sa Login
12






The sa login is a default login that has full administration rights for
SQL Server.
If you had selected mixed mode authentication during the
installation process, you would have seen that you would be forced
to include a password for this account.
This is because the sa user ID is such a powerful login.
It also exists in every SQL Server installation; therefore, any hacker
knows that this user ID exists and so will try to connect to the server
using it.
It is essential to set up a strong password on the sa account.
A good password is one that mixes numbers and letters, but doesn’t
include letters than can be made into numbers and numbers into
letters. For example, pa55word is just as easy to guess as password
Eng.Mohamed Adam Isak, Ph.D. Researcher
What Can SQL do?
13










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
Eng.Mohamed Adam Isak, Ph.D. Researcher
Using SQL in Your Web Site
14

To build a web site that shows some data from a
database, you will need the following:
 An
RDBMS database program (i.e. MS Access, SQL
Server, MySQL)
 A server-side scripting language, like PHP or ASP
 HTML / CSS
Eng.Mohamed Adam Isak, Ph.D. Researcher
Tasks of a SQL Server DBA
15










Install and configure SQL Server
Plan and create databases
Backup the databases
Restore databases when required
Setup and manage users for SQL Servers
Manage Security for SQL Server users
Setup / Manage task, alerts, operators
Setup / Manage replication environment
Performance Tune SQL Server
Troubleshoot SQL Server related problems
Eng.Mohamed Adam Isak, Ph.D. Researcher
Eng.Mohamed Adam Isak, Ph.D. Researcher
16
OUR EXAMPLES WILL BE BASED ON:
THE FOLLOWING TWO TABLES:
Database Tables
17


A database most often contains one or more tables.
Each table is identified by a name (e.g.
“EmployeeTable" or “DepartmentTable").
Tables contain records (rows) with data and
attributes (columns).
Eng.Mohamed Adam Isak, Ph.D. Researcher
Employee Table
18
Eng.Mohamed Adam Isak, Ph.D. Researcher
SQL DML and DDL
19

SQL can be divided into two parts: The Data
Manipulation Language (DML) and the Data
Definition Language (DDL).
Eng.Mohamed Adam Isak, Ph.D. Researcher
DML part of SQL
20




SELECT - extracts data from a database
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts new data into a database
Eng.Mohamed Adam Isak, Ph.D. Researcher
The DDL part of SQL
21

The DDL part of SQL permits database tables to be
created or deleted. It also defines indexes (keys),
specify links between tables, and impose constraints
between tables. The most important DDL statements
in SQL are:
Eng.Mohamed Adam Isak, Ph.D. Researcher
DDL
22







CREATE DATABASE -
creates a new database
ALTER DATABASE - modifies a database
CREATE TABLE - creates a new table
ALTER TABLE - modifies a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index
Eng.Mohamed Adam Isak, Ph.D. Researcher
The SQL SELECT Statement
23



The SELECT statement is used to select data from a
database.
The result is stored in a result table, called the
result-set.
SQL SELECT Syntax
 SELECT
column_name(s)
FROM table_name
 SELECT * FROM table_name

Note: SQL is not case sensitive. SELECT is the same
as select
Eng.Mohamed Adam Isak, Ph.D. Researcher
The SQL SELECT Statement-Example
24

The asterisk (*) is a quick way of selecting all
columns.
Eng.Mohamed Adam Isak, Ph.D. Researcher
THE DISTINCT SYNTAX
25


In a table, some of the columns may contain
duplicate values. This is not a problem; however,
sometimes you will want to list only the different
(distinct) values in a table.
The DISTINCT keyword can be used to return only
distinct (different) values.
Eng.Mohamed Adam Isak, Ph.D. Researcher
THE DISTINCT SYNTAX
26
Eng.Mohamed Adam Isak, Ph.D. Researcher
The WHERE Clause
27

The WHERE clause is used to extract only those
records that fulfill a specified criterion.
Eng.Mohamed Adam Isak, Ph.D. Researcher
28
Start up your own project !!!!!!!!
Select your project from the list you will see after
you click here or
Visit
www.EngMaisak.Blogspot.com
Eng.Mohamed Adam Isak, Ph.D. Researcher