Demo-IDD-Chapter2-Part2.rar

-- DEMO IDD CHAPTER 2 - PART 2 USE AdventureWorks -- USE IN & NOT IN (tim kiem theo 1 tap dieu kien) -- Tim nhung nhan vien co vi tri la Recruiter & Stocker) --C1: SELECT * FROM HumanResources.Employee WHERE Title = 'Recruiter' OR Title = 'Stocker' --C2: using IN SELECT * FROM HumanResources.Employee WHERE Title NOT IN ('Recruiter','Stocker') /* Su dung ky tu dai dien de tim kiem - Wildcard character - % dai dien cho bat cu tu nao - _ dai dien cho 1 ky tu nao - [abc] dai dien cho 1 ky tu bat ky trong chuoi abc - [^abc] dai dien cho 1 ky tu bat ky khong co trong chuoi abc */ -- Tim kiem cac phong ban co ten bat dau bang chu Pro SELECT * FROM HumanResources.Department WHERE Name LIKE 'Pro%' SELECT * FROM HumanResources.Department WHERE Name LIKE 'Marketin_' -- Tim ra cac phong ban co ten bat dau bang chu M,P,T SELECT * FROM HumanResources.Department WHERE Name LIKE '[MPT]%' -- TOP - tim kiem theo vi tri -- Tim ra 3 nhan vien co thoi gian nghi om nhieu nhat trong nam 1998 SELECT TOP 3 * FROM HumanResources.Employee WHERE HireDate >='1998/1/1' AND HireDate <='1998/12/31' ORDER BY SickLeaveHours DESC -- Tim kiem loai bo gia tri trung lap SELECT DISTINCT Title FROM HumanResources.Employee -- Su dung Function (ham`) SELECT function_name -- chuyen chu viet hoa SELECT upper('hoang ngoc anh') -- chuyen chu thuong SELECT lower('HOANG ANH DUNG') -- dem so luong ky tu SELECT len('Nguyen Hoang Tung') -- Y/c: Tim ra cac phong ban co ten bat dau bang chu P -- Y/c: ten phong ban phai viet hoa SELECT DepartmentID, 'Name'=upper(Name) FROM HumanResources.Department WHERE Name Like 'P%' --Ham lay ngay thang getdate() SELECT getdate() -- Ham cong ngay thang dateadd(date part, number, date) -- VD: Cong them 10 ngay vao HireDate cua toan bo nhan vien SELECT 'Hire Date'=dateadd(dd,10,HireDate) FROM HumanResources.Employee -- Cong them 1 nam vao HireDate cua toan bo nhan vien SELECT 'Hire Date'=dateadd(yyyy,1,HireDate) FROM HumanResources.Employee -- ham tru ngay thang datediff(datepart, date1, date2) -- VD: tinh tham nien lam viec cua toan bo nhan vien SELECT 'Tham Nien'=datediff(yyyy,HireDate,getdate()) FROM HumanResources.Employee -- Tinh tuoi cua toan bo nhan vien nu, sap xep theo giam dan SELECT 'Tuoi'=datediff(yyyy,BirthDate,getdate()) FROM HumanResources.Employee WHERE Gender = 'F' ORDER BY Tuoi DESC