"고급"SQL이란 무엇입니까?

"고급"SQL이란 무엇입니까?

---

"고급 SQL"이 요구되는 작업 설명을 살펴 봅니다. 나는 누구와 마찬가지로 기본적인 쿼리를 작성할 수 있고, 전문적인 환경에서 MySQL 데이터베이스로 작업 해 왔지만, 내가 고용된다면이 직업으로 무엇을 할 수 있을까? 고급 SQL의 예는 무엇이며 SQL 멍청한 규모에서 SQL 마스터에 이르기까지 어디에 있습니까?

---

예를 들어 강조하는 것이 가장 좋다고 생각합니다. 참조 자료가 거의 또는 전혀없이 다음 SQL 문을 빠르게 작성할 수 있다고 생각되면 아마도 고급 SQL 요구 사항을 충족 할 것입니다.

DECLARE @date DATETIME
SELECT @date = '10/31/09'

SELECT
      t1.EmpName,
      t1.Region,
      t1.TourStartDate,
      t1.TourEndDate,
      t1.FOrdDate,
      FOrdType  = MAX(CASE WHEN o.OrderDate = t1.FOrdDate THEN o.OrderType  ELSE NULL END),
      FOrdTotal = MAX(CASE WHEN o.OrderDate = t1.FOrdDate THEN o.OrderTotal ELSE NULL END),
      t1.LOrdDate,
      LOrdType  = MAX(CASE WHEN o.OrderDate = t1.LOrdDate THEN o.OrderType  ELSE NULL END),
      LOrdTotal = MAX(CASE WHEN o.OrderDate = t1.LOrdDate THEN o.OrderTotal ELSE NULL END)
  FROM 
      (--Derived table t1 returns the tourdates, and the order dates
      SELECT
            e.EmpId,
            e.EmpName,
            et.Region,
            et.TourStartDate,
            et.TourEndDate,
            FOrdDate = MIN(o.OrderDate),
            LOrdDate = MAX(o.OrderDate)
        FROM #Employees e INNER JOIN #EmpTours et
          ON e.EmpId = et.EmpId INNER JOIN #Orders o
          ON e.EmpId = o.EmpId
       WHERE et.TourStartDate <= @date
         AND (et.TourEndDate > = @date OR et.TourEndDate IS NULL)
         AND o.OrderDate BETWEEN et.TourStartDate AND @date
       GROUP BY e.EmpId,e.EmpName,et.Region,et.TourStartDate,et.TourEndDate
      ) t1 INNER JOIN #Orders o
    ON t1.EmpId = o.EmpId
   AND (t1.FOrdDate = o.OrderDate OR t1.LOrdDate = o.OrderDate)
 GROUP BY t1.EmpName,t1.Region,t1.TourStartDate,t1.TourEndDate,t1.FOrdDate,t1.LOrdDate

(쿼리 소스)

솔직히 말해서 이것은 비교적 간단한 쿼리입니다. 몇 가지 공통 키워드 (최대, 최소, 케이스)와 함께 내부 조인과 하위 쿼리 만 있습니다.

---

기초

---

1. SELECT테이블의 열
2. 집계 1 부 : COUNT, SUM, MAX/MIN
3. 집계 2 부 : DISTINCT, GROUP BY,HAVING

중급

---

4. JOINs, ANSI-89 및 ANSI-92 구문
5. UNION vs UNION ALL
6. NULL처리 : COALESCE& 네이티브 NULL 처리
7. 하위 쿼리 : IN, EXISTS및 인라인보기
8. 하위 쿼리 : 상관
9. WITH 구문 : 하위 쿼리 팩토링 / CTE
10. 견해

고급 주제

---

• 함수, 저장 프로 시저, 패키지
• 피벗 데이터 : CASE 및 PIVOT 구문
• 계층 적 쿼리
• 커서 : 암시 적 및 명시 적
• 트리거
• 동적 SQL
• 구체화 된 뷰
• 쿼리 최적화 : 인덱스
• 쿼리 최적화 : 계획 설명
• 쿼리 최적화 : 프로파일 링
• 데이터 모델링 : 일반 형식, 1 ~ 3
• 데이터 모델링 : 기본 및 외래 키
• 데이터 모델링 : 테이블 제약
• 데이터 모델링 : 링크 / 코롤 러리 테이블
• 전체 텍스트 검색
• XML
• 격리 수준
• 엔티티 관계 다이어그램 (ERD), 논리적 및 물리적
• Transactions: COMMIT, ROLLBACK, Error Handling

---

The rest of the job opening listing could provide context to provide a better guess at what "Advanced SQL" may encompass.

I disagree with comments and responses indicating that understanding JOIN and aggregate queries are "advanced" skills; many employers would consider this rather basic, I'm afraid. Here's a rough guess as what "Advanced" can mean.

There's been an "awful" lot of new stuff in the RDBMS domain, in the last few years!

The "Advanced SQL" requirement probably hints at knowledge and possibly proficiency in several of the new concepts such as:

• CTEs (Common Table Expressions)
• UDFs (User Defined Functions)
• Fulltext search extensions/integration
• performance tuning with new partitionning schemes, filtered indexes, sparse columns...)
• new data types (ex: GIS/spatial or hierarchical)
• XML support / integration
• LINQ
• and a few more... (BTW the above list is somewhat MSSQL-centric, but similar evolution is observed in most other DBMS platforms).

While keeping abreast of the pro (and cons) of the new features is an important task for any "advanced SQL" practitioner, the old "advanced fundamentals" are probably also considered part of the "advanced":

• triggers and stored procedures at large
• Cursors (when to use, how to avoid ...)
• design expertise: defining tables, what to index, type of indexes
• performance tuning expertise in general
• query optimization (reading query plans, knowing what's intrinsically slow etc.)
• Procedural SQL
• ...

Note: the above focuses on skills associated with programming/lead role. "Advanced SQL" could also refer to experience with administrative roles (Replication, backups, hardware layout, user management...). Come to think about it, a serious programmer should be somewhat familiar with such practices as well.

Edit: LuckyLindy posted a comment which I found quite insightful. It suggests that "Advanced" may effectively have a different purpose than implying a fair-to-expert level in most of the categories listed above...
I repeat this comment here to give it more visibility.

I think a lot of companies post Advanced SQL because they are tired of getting someone who says "I'm a SQL expert" and has trouble putting together a 3 table outer join. I post similar stuff in job postings and my expectation is simply that a candidate will not need to constantly come to me for help writing SQL. (comment by LuckyLindy)

---

I would expect:

• stored procedure creation and usage
• joins (inner and outer) and how to correctly use GROUP BY
• performance evaluation/tuning
• knowledge of efficient (and inefficient) ways of doing things in queries (understanding how certain things can affect performance, e.g. using functions in WHERE clauses)
• dynamic SQL and knowledge of cursors (and IMO the few times they should be used)
• understanding of schema design, indexing, and referential integrity

---

Check out SQL For Smarties. I thought I was pretty good with SQL too, until I read that book... Goes into tons of depth, talks about things I've not seen elsewhere (I.E. difference between 3'rd and 4'th normal form, Boyce Codd Normal Form, etc)...

---

Some "Advanced" features

• recursive queries
• windowing/ranking functions
• pivot and unpivot
• performance tuning

---

SELECT ... HAVING ... is a good start. Not many developers seem to understand how to use it.

---

I suppose subqueries and PIVOT would qualify, as well as multiple joins, unions and the like.

---

Performance tuning, creating indices, stored procedures, etc.

"Advanced" means something different to everyone. I'd imagine this type of thing means something different to every job-poster.

---

When you see them spelled out in requirements they tend to include:

• Views
• Stored Procedures
• User Defined Functions
• Triggers
• sometimes Cursors

Inner and outer joins are a must but i rarely ever see it mentioned in requirements. And it's surprising how many so-called db professionals cannot get their head around a simple outer join.

---

At my previous job, we had a technical test which all candidates were asked to sit. 10ish questions, took about an hour. In all honesty though, 90% of failures could be screened out because they couldn't write an INNER JOIN statement. Not even an outer.

I'd consider that a prerequisite for any job description involving SQL and would leave well alone until that was mastered. From there though, talk to them - any further info on what they're actually looking for will, worst case scenario, be a useful list of things to learn as part of your professional development.

참고URL : https://stackoverflow.com/questions/2054130/what-is-advanced-sql