What is Advanced SQL

Question

Looking at a job descriptions where  advanced SQL  is a requirement   I can write basic queries as well as anyone  and have worked with MySQL databases in a professional setting  but what would I be getting into with these jobs  if I were to be hired   What are examples of advanced SQL and where am I along the scale of SQL noob to SQL master

User · Answer

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

User · Answer

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

User · Answer

Some  Advanced  features    recursive queries windowing ranking functions pivot and unpivot performance tuning

User · Answer

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

User · Answer

I think it s best highlighted with an example   If you feel you could write the following SQL statement quickly with little no reference material  then I d guess that you probably meet their Advanced SQL requirement   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  lt    date          AND  et TourEndDate  gt     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    source of query   And to be honest  that s a relatively simple query - just some inner joins and a subquery  along with a few common keywords  max  min  case

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Basics     SELECTing columns from a table Aggregates Part 1  COUNT  SUM  MAX MIN Aggregates Part 2  DISTINCT  GROUP BY  HAVING   Intermediate     JOINs  ANSI-89 and ANSI-92 syntax UNION vs UNION ALL NULL handling  COALESCE  amp  Native NULL handling Subqueries  IN  EXISTS  and inline views Subqueries  Correlated WITH syntax  Subquery Factoring CTE Views   Advanced Topics     Functions  Stored Procedures  Packages Pivoting data  CASE  amp  PIVOT syntax Hierarchical Queries Cursors  Implicit and Explicit Triggers Dynamic SQL Materialized Views Query Optimization  Indexes Query Optimization  Explain Plans Query Optimization  Profiling Data Modelling  Normal Forms  1 through 3 Data Modelling  Primary  amp  Foreign Keys Data Modelling  Table Constraints Data Modelling  Link Corrollary Tables Full Text Searching XML  Isolation Levels Entity Relationship Diagrams  ERDs   Logical and Physical Transactions  COMMIT  ROLLBACK  Error Handling

[sql] What is "Advanced" SQL?

Examples related to sql

Examples related to database