Database Management System - SQL beginner Training

Database Management System
Beginner Training
Practices in 2017
Prepared by: Moutasm Tamimi
Using SQL language
Microsoft SQL Server Management Studio
Versions (2008-2010-2012-2014)

Speaker Information
 Moutasm tamimi
Independent consultant , IT Researcher , CEO at ITG7
Instructor of: Project Development.
DBMS.
.NET applications.
Digital marketing.
Email: tamimi@itg7.com
LinkedIn: click here.

Introduction
Database: is an organized collection of data It is the collection
of schemas, tables, queries, reports, views, and other objects.
SQL: is a standard language for accessing and manipulating databases.
What is SQL?
•SQL stands for Structured Query Language
•SQL lets you access and manipulate databases
•SQL is an ANSI (American National Standards Institute)
standard

The four main categories of SQL statements
are as follows:
1. DDL (Data Definition Language)
2. DML (Data Manipulation Language)
3. DCL (Data Control Language)
4. TCL (Transaction Control Language)

Open Microsoft SQL Server
Server name
Input (.) dot as default
server login

Creating a database
 We need to use Master database for creating a database
 By default the size of a database is 1 MB
 A database consists of
Master Data File (.mdf)
Primary Log File (.ldf)

Data Types in SQL
 Data types in
Microsoft SQL server
2012

Create Tables in SQL
PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi
Product
Attribute names
Table name
Tuples or rows
Column name

Tables Explained
 The schema of a table is the table name and its
attributes:
Product(PName, Price, Category, Manfacturer)
 A key is an attribute whose values are unique;
we underline a key

Identity Property
 Identity has
A seed
An increment
 Seed is the initial value
 Increment is the value by which we need to skip to fetch the
nextvalue
 Identity(1,2) will generate sequence numbers 1,3,5,7…

Primary key constraints and Identity Property

Create a New Database Diagram
A database schema of a database system is its structure
described in a formal language supported by the database
management system (DBMS).

Create Database diagram
Blue box: primary key
Yellow box: foreign key

How To add records from the database on
a table

Stored procedures
 Stored procedures provide improved performance because
fewer calls need to be sent to the database.
 How to creare New Quey in Microsoft SQL server

Store the code of Stored Procedures:

Open the code of the Stored procedures

Insert statements
 Inserting data to all columns
 Insert into tablename(col1,col2) values(v1,v2)
 Insert into tablename values(v1,v2)
 Inserting data to selected columns
 Insert into tablename(col1) values (v1)
 Insert into tablename(col2) values (v2)

Insert statements in Microsoft SQL Server

Update statement
Update table tablename
Set colname=value
- This updates all rows with colname set to value
Update table tablename
Set colname=value
Where <<condition>>
- This updates selected rows with colname as value only if the row satisfies the
condition

Update statement statements in Microsoft
SQL Server

Delete statements
Delete from table1;
Deletes all rows in table1
Delete from table1 where <<condition>>
Deletes few rows from table1 if they satisfy the condition

Delete statement statements in Microsoft
SQL Server

SQL Select Query
Basic form:
SELECT <attributes>
FROM <one or more relations>
WHERE <conditions>

Simple SQL Query
SELECT *
FROM Product
WHERE category=‘Gadgets’
Product
Powergizmo $29.99 Gadgets GizmoWorks“selection”

Simple SQL Query
SELECT PName, Price, Manufacturer
FROM Product
WHERE Price > 100
Product
PName Price Manufacturer
SingleTouch $149.99 Canon
MultiTouch $203.99 Hitachi
“selection” and
“projection”

Notation
Answer(PName, Price, Manfacturer)
Input Schema
Output Schema
SELECT PName, Price, Manufacturer
FROM Product
WHERE Price > 100

Details
 Case insensitive:
 Same: SELECT Select select
 Same: Product product
 Different: ‘Seattle’ ‘seattle’
 Constants:
 ‘abc’ - yes
 “abc” - no

The LIKE operator
 s LIKE p: pattern matching on strings
 p may contain two special symbols:
 % = any sequence of characters
 _ = any single character
SELECT *
FROM Products
WHERE PName LIKE ‘%gizmo%’

Eliminating Duplicates
SELECT DISTINCT category
FROM Product
Compare to:
SELECT category
FROM Product
Category
Gadgets
Gadgets
Photography
Household
Category
Gadgets
Photography
Household

Ordering the Results
SELECT pname, price, manufacturer
FROM Product
WHERE category=‘gizmo’ AND price > 50
ORDER BY price, pname
Ties are broken by the second attribute on the ORDER BY list, etc.
Ordering is ascending, unless you specify the DESC keyword.

SELECT Category
FROM Product
ORDER BY PName
?
FROM Product
ORDER BY category
FROM Product
ORDER BY PName
?
?

Keys and Foreign Keys
Product
Company
CName StockPrice Country
GizmoWorks 25 USA
Canon 65 Japan
Hitachi 15 Japan
Key
Foreign
key

Joins
Product (pname, price, category, manufacturer)
Company (cname, stockPrice, country)
Find all products under $200 manufactured in Japan;
return their names and prices.
SELECT PName, Price
FROM Product, Company
WHERE Manufacturer=CName AND Country=‘Japan’
AND Price <= 200
Join
between Product
and Company

Joins
 Cross Join
 Cartesian product. Simply merges two tables.
 Inner Join
 Cross join with a condition. Used to find matching records in the two tables
 Outer Join
 Used to find un matched rows in the two tables
 Self Join
 Joining a table with itself

Joins
Product
Company
Cname StockPrice Country
GizmoWorks 25 USA
Canon 65 Japan
Hitachi 15 Japan
PName Price
SingleTouch $149.99
SELECT PName, Price
FROM Product, Company
WHERE Manufacturer=CName AND Country=‘Japan’
AND Price <= 200

Database Management System - SQL beginner Training

More Related Content

What's hot

Viewers also liked

Similar to Database Management System - SQL beginner Training

More from Moutasm Tamimi

Recently uploaded

Database Management System - SQL beginner Training