What is “Advanced” SQL?

Basics

  1. SELECTing columns from a table
  2. Aggregates Part 1: COUNT, SUM, MAX/MIN
  3. Aggregates Part 2: DISTINCT, GROUP BY, HAVING

Intermediate

  1. JOINs, ANSI-89 and ANSI-92 syntax
  2. UNION vs UNION ALL
  3. NULL handling: COALESCE & Native NULL handling
  4. Subqueries: IN, EXISTS, and inline views
  5. Subqueries: Correlated
  6. WITH syntax: Subquery Factoring/CTE
  7. Views

Advanced Topics

  • Functions, Stored Procedures, Packages
  • Pivoting data: CASE & 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 & 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

Related Posts:

  1. Oracle SQL query for Date format
  2. WPDB Insert or if exists Update
  3. Where are image paths to product category images stored in WordPress database
  4. Sharing database for collaborative development
  5. Display data from a non wordpress database on a page template
  6. Organizing WP Pages based on old website database
  7. Inserting Post Meta From SQL
  8. Bulk Update of Custom Post Type Post Status through SQL
  9. How do I add indexes to WordPress Database?
  10. Moving an entire wordpress site from one domain to a new domain and database
  11. Change SQL get_results to search for posts with custom term in custom taxonomy
  12. Block search SQL from happening
  13. wpdb query on custom table not working [closed]
  14. How to execute mulitple statement SQL queries using $wpdb->query?
  15. Delete duplicate rows from wordpress database where a column is duplicate in phpmyadmin
  16. Query WordPress database by registered date and role
  17. Using table lock with wp_insert_post?
  18. Scheduling posts in database
  19. Size article thumbnails and retrieve them directly from database
  20. How to query the WordPress database to get posts of a certain custom post type, taxonomy and field?
  21. wpdb_prepare with multiple or condition
  22. Uppercase to sentence case for post titles
  23. SQL Error wp_commentmeta – incorrect table definition
  24. WordPress and MySQL: how to transfer Meta_key and Meta_Value from one post_id to another
  25. How do I get the posts within a certain year/date(with sql query)?
  26. Export / import WordPress database
  27. WordPress is not creating table in database after activating plugin,
  28. Bulk-create posts from SQL database
  29. Difficulty importing my live site to local
  30. WordPress Database Huge wp_usermeta Table
  31. Create table from array with prepare
  32. Setup private content for specific users with daily updates [closed]
  33. Which data is written and stored in the database when read-only (non-posting, non-commenting) users visit the site?
  34. Insert Query not working in the form
  35. WPDB SQL query with prepare() returning variable, not db value
  36. Problems with moving my WordPress site to another domain/server
  37. Why this query is not showing any result on wordpresss home page?
  38. Is it possible to have more than 1 MySQL database in my WordPress blog?
  39. pull data from a remote wordpress database
  40. How to migrate a database from a server to another
  41. Clear Terms from Taxonomy for Specific Post IDs?
  42. Is my way to change WordPress server is correct?
  43. Why does changing Database Table Prefixes result in loss of content?
  44. Options on how to Display member specific data from a MS SQL database
  45. Delete/Replace words from X to Y in post content
  46. SQL query to set posts in bulk based on the post content
  47. How to delete a particular row in a database table
  48. Delete all posts that do NOT have a specific tag
  49. Location of WP default mySQL tables creation file?
  50. $wpdb->get_var – What value is returned when zero records are found?
  51. How to display data from db in select list [closed]
  52. WordPress SQL – How to Check for Category?
  53. Getting value from database table depending on field value
  54. custom tables in wordpress Database? (can i just create them with sql?)
  55. Accidently changed the GUID
  56. DB prefix not updating
  57. Deleting empty meta key values
  58. Sql query to get all metadata for a post
  59. I wanted to Know that how can I give ingore to 0000-00-00 in my config file
  60. Job and Employee Performance Tracking with Product Safety
  61. SQL database import #1046 error
  62. How do I loop/iterate through posts to edit all img tags?
  63. Help posting values to DB on submit using $wpdb->query
  64. What’s the easiest way to connect external database and display data on wordpress?
  65. Is there a way for two deferent themes to consume two different DB on a same WP instance?
  66. How to set posts to draft in bulk based on the content of the post
  67. How to create index (sql) to a meta_key?
  68. 2 $wpdb queries causing error Table ‘wp_postmeta’ is specified twice, both as a target for ‘UPDATE’
  69. Delete all users meta that named: user_avatar
  70. Correct and secure way to access a custom SQL database in a custom PHP template file
  71. How to get specific table by current user login
  72. The MySQL Connection could not be established., before it was joomla but i have change it into worpress
  73. How to retrieve user data based on role using SQL?
  74. $wpdb not working
  75. WordPress character set issue
  76. Link wordpress with a SQl + MongoDB + .NET
  77. How to add data from wordpress tables to new database tables you have created
  78. Getting deleted users in database
  79. Install WordPress with SQL database
  80. Migrated to Namecheap, now Trouble with Database & wp-config [closed]
  81. export individual posts to text files or a single csv file
  82. SQL query : Select a thumbnail for a media
  83. why you don’t Implement PDO for access every thing to database
  84. SQL Query to get post_id from wp_posts and and meta_key(s) from wp_postmeta
  85. SQL query to rewrite all media URLs to end with .webp
  86. Weird WP -Cli Error Connection Refused
  87. TypeError: tuple indices must be integers, not str
  88. The MySQL alternatives: Do Percona Server and MariaDB work well with WordPress, and do they make WordPress go better?
  89. WordPress database error with latest WP – “WP_termmeta doesn’t exist”
  90. Cloning and syncing a WordPress website
  91. Filter WordPress posts by between parameter
  92. Should I remove transients from the wp_options table?
  93. How to migrate a website based on a custom database to wordpress?
  94. After database migration, theme mods don’t show [closed]
  95. Website displays old version of page
  96. Displaying table data on a page
  97. Database for JQuery Autocomplete field
  98. How do you build a database-centric site in WP?
  99. WP Multisite Users Not Linking
  100. Query a meta key using an array of values where the database value is a string

Leave a Comment