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