SQL Structured Query Language - PowerPoint PPT Presentation

1 / 18
About This Presentation
Title:

SQL Structured Query Language

Description:

SQL Structured Query Language Data Definition Language (DDL) Data Manipulation Language (DML) – PowerPoint PPT presentation

Number of Views:41
Avg rating:3.0/5.0
Slides: 19
Provided by: Noh49
Category:

less

Transcript and Presenter's Notes

Title: SQL Structured Query Language


1
SQLStructured Query Language
  • ??? ????????? Data Definition Language (DDL)
  • ??? ??????? ?? ???????? Data Manipulation
    Language (DML)
  • ??? ??????????? Data Control Language (DCL)

2
SELECT advanced statement
  • distinct (????)
  • select distinct (country) from customers
  • order by country

3
SELECT advanced statement
  • ?????? ?? ???? ?? ???? ????
  • Inner Join
  • select products.productid, categories.categoryname
    from products inner join categories
  • on Products.categoryid categories.categoryid

4
SELECT advanced statement
  • Outer Join
  • select companyname, customers.customerid,
    orderdate
  • from customers
  • LEFT OUTER JOIN orders
  • ON customers.customerid orders.customerid
  • ??? ?????? ? ????? ??????? ? ????? ???????
    (????) NULL

5
SELECT advanced statement
  • Outer Join
  • select companyname, customers.customerid,
    orderdate
  • from customers
  • RIGHT OUTER JOIN orders
  • ON customers.customerid orders.customerid
  • ???? ???????? ???????

6
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.titleb.title
  • Where a.employeeid lt b.employeeid

7
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.titleb.title
  • Where a.employeeid lt b.employeeid

8
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

9
SELECT advanced statement
  • Group Functions
  • ???? ?????????
  • select avg(unitprice), min(unitprice),
    max(unitprice), sum(unitprice)
  • From products

10
SELECT advanced statement
  • count function
  • ??????
  • select count(), count(1), count(employeeid),
    count(reportsto)
  • From employees

11
SELECT advanced statement
  • Group By function
  • select categoryid, avg(unitprice),
    count(productid)
  • From products
  • group by categoryid

12
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

13
SELECT advanced statement
  • Order by with group by
  • select categoryid, avg(unitprice),
    count(productid)
  • From products
  • group by categoryid
  • order by avg(unitprice) DESC

14
SELECT advanced statement
  • Group by and Having
  • select categoryid, avg(unitprice),
    count(productid)
  • From products
  • where categoryid gt 3
  • group by categoryid
  • having avg(unitprice) gt 25
  • order by avg(unitprice) ASC

15
SELECT advanced statement
  • Nested sub-Queries
  • Select productid, productname, unitprice
  • From products
  • where categoryid
  • (Select categoryid from products where productid
    62)

16
SELECT advanced statement
  • Using IN with Nested subqueries
  • select
  • From products
  • where Productid IN (Select Productid from
    products where Categoryid5)

17
SELECT advanced statement
  • Using Order by with nested subqueries
  • select
  • From products
  • where Productid IN (Select Productid from
    products where Categoryid5)
  • Order by Unitprice DESC

18
SELECT advanced statement
  • Using Variables with Queries
  • Declare _at_FirstNameVariable NVARCHAR(20),
    _at_RegionVariable NVARCHAR(30)
  • SET _at_FirstNameVariable N'Anne'
  • SET _at_RegionVariable N'WA'
  • Select
  • From Employees
  • where FirstName _at_FirstNameVariable
  • Or Region _at_RegionVariable
Write a Comment
User Comments (0)
About PowerShow.com