Transcript SQL Structured Query Language

SQL
Structured Query Language
Data Definition Language (DDL) ‫قسم التعريفات‬
Data Manipulation Language (DML) ‫قسم التعامل مع البيانات‬
Data Control Language (DCL) ‫قسم االستعالمات‬
1
SELECT advanced statement
distinct (‫)فريد‬

select distinct (country) from customers
order by country
2
SELECT advanced statement
‫بيانات من أكثر من جدول واحد‬
Inner Join

select products.productid, categories.categoryname
from products inner join categories
on Products.categoryid = categories.categoryid
3
SELECT advanced statement

Outer Join
select companyname, customers.customerid,
orderdate
from customers
LEFT OUTER JOIN orders
ON customers.customerid = orders.customerid
NULL )‫ تاريخ الطلبية (فارغ‬
‫اسم الشركة و تاريخ الطلبية‬
4
SELECT advanced statement

Outer Join
select companyname,
customers.customerid, orderdate
from customers
RIGHT OUTER JOIN orders
ON customers.customerid =
orders.customerid
‫جميع الطلبيات للشركات‬
5
SELECT advanced statement
Joining a table to itself
!!!‫ربط الجدول مع نفسه‬
Inner Join

select a.employeeid, a.lastname as name, a.title as
Title,b.employeeid, b.lastname as name, b.title as
Title
From Employees as a
INNER JOIN employees as b
on a.title=b.title
Where a.employeeid < b.employeeid
6
SELECT advanced statement
Joining a table to itself
!!!‫ربط الجدول مع نفسه‬
Inner Join

select a.employeeid, left(a.lastname,10) as name,
left(a.title, 10) as Title,
b.employeeid, b.lastname as name, b.title as Title
From Employees as a
INNER JOIN employees as b
on a.title=b.title
Where a.employeeid < b.employeeid
7
SELECT advanced statement
Joining a table to itself
!!!‫ربط الجدول مع نفسه‬
Inner Join

select a.employeeid, a.lastname as name, a.title as
Title,b.employeeid, b.lastname as 'Manager name',
b.title as Title
From Employees as a
INNER JOIN employees as b
on a.reportsto = b.employeeid
8
SELECT advanced statement
Group Functions
‫دوال المجموعات‬

select avg(unitprice), min(unitprice),
max(unitprice), sum(unitprice)
From products
9
SELECT advanced statement
count function
‫العداد‬

select count(*), count(1), count(employeeid),
count(reportsto)
From employees
10
SELECT advanced statement
Group By function

select categoryid, avg(unitprice),
count(productid)
From products
group by categoryid
11
SELECT advanced statement


select categoryid, avg(unitprice),
count(productid)
From products
group by categoryid
select categoryid, avg(unitprice),
count(productid), productid
From products
group by categoryid
12
SELECT advanced statement
Order by with group by

select categoryid, avg(unitprice),
count(productid)
From products
group by categoryid
order by avg(unitprice) DESC
13
SELECT advanced statement
Group by and Having

select categoryid, avg(unitprice),
count(productid)
From products
where categoryid > 3
group by categoryid
having avg(unitprice) > 25
order by avg(unitprice) ASC
14
SELECT advanced statement
Nested sub-Queries

Select productid, productname, unitprice
From products
where categoryid =
(Select categoryid from products where
productid = 62)
15
SELECT advanced statement
Using IN with Nested subqueries

select *
From products
where Productid IN (Select Productid from
products where Categoryid=5)
16
SELECT advanced statement
Using Order by with nested subqueries

select *
From products
where Productid IN (Select Productid from
products where Categoryid=5)
Order by Unitprice DESC
17
SELECT advanced statement
Using Variables with Queries

Declare @FirstNameVariable NVARCHAR(20),
@RegionVariable NVARCHAR(30)
SET @FirstNameVariable = N'Anne'
SET @RegionVariable = N'WA'

Select *
From Employees
where FirstName = @FirstNameVariable
Or Region = @RegionVariable
18