73 Matching Annotations
  1. Jul 2021
    1. The most important part of this query is the with block. It's a powerful resource, but for this example, you can think of it as a "way to store a variable" that is the path of the contact you need to update, which will be dynamic depending on the record.
    2. It just builds the path as '{1, value}', but we need to convert to text[] because that’s the type expected on the jsonb_path function.
    1. In addition to SQLAlchemy core queries, you can also perform raw SQL queries

      Instead of SQLAlchemy core query:

      query = notes.insert()
      values = [
          {"text": "example2", "completed": False},
          {"text": "example3", "completed": True},
      await database.execute_many(query=query, values=values)

      One can write a raw SQL query:

      query = "INSERT INTO notes(text, completed) VALUES (:text, :completed)"
      values = [
          {"text": "example2", "completed": False},
          {"text": "example3", "completed": True},
      await database.execute_many(query=query, values=values)


      The same goes with fetching in SQLAlchemy:

      query = notes.select()
      rows = await database.fetch_all(query=query)

      And doing the same with raw SQL:

      query = "SELECT * FROM notes WHERE completed = :completed"
      rows = await database.fetch_all(query=query, values={"completed": True})
  2. Jun 2021
    1. HAVING avg(score) < 0.75 * (SELECT avg(score) FROM performance_reviews)
    2. When you are dealing with an aggregate of aggregates, it needs to be accomplished in two steps. This can be done using a subquery as the FROM clause, essentially giving us a temporary table to then select from, allowing us to find the average of those averages.
    1. SELECT * FROM ( -- build virtual table of all hours between -- a date range SELECT start_ts, start_ts + interval '1 hour' AS end_ts FROM generate_series( '2017-03-01'::date, '2017-03-03'::timestamp - interval '1 hour', interval '1 hour' ) AS t(start_ts) ) AS cal LEFT JOIN ( -- build virtual table of uptimes SELECT * FROM ( VALUES ('2017-03-01 01:15:00-06'::timestamp, '2017-03-01 02:15:00-06'::timestamp), ('2017-03-01 08:00:00-06', '2017-03-01 20:00:00-06'), ('2017-03-02 19:00:00-06', null) ) AS t(start_ts, end_ts) ) AS uptime ON cal.end_ts > uptime.start_ts AND cal.start_ts <= coalesce(uptime.end_ts, current_timestamp)
  3. Apr 2021
  4. Mar 2021
    1. This is not a problem if your DBMS supports SQL recursion: lots of data can be generated with a single query. The WITH RECURSIVE clause comes to the rescue.

      WITH RECURSIVE can help you quickly generate a series of random data.

  5. Feb 2021
  6. Oct 2020
  7. Sep 2020
    1. The Realm is a new database module that is improving the way databases are used and also supports relationships between objects. If you are part of the SQL development world, then you must be familiar with the Realm.
  8. Jul 2020
    1. [:returning] (Postgres-only) An array of attributes that should be returned for all successfully inserted records. For databases that support INSERT ... RETURNING, this will default to returning the primary keys of the successfully inserted records. Pass returning: %w[ id name ] to return the id and name of every successfully inserted record or pass returning: false to omit the clause.
  9. Jun 2020
    1. NoSQL databases typically perform better and are easier to scale due to the nature of their data access and storage
  10. May 2020
    1. WHERE clause introduces a condition on individual rows; HAVING clause introduces a condition on aggregations, i.e. results of selection where a single result, such as count, average, min, max, or sum, has been produced from multiple rows. Your query calls for a second kind of condition (i.e. a condition on an aggregation) hence HAVING works correctly. As a rule of thumb, use WHERE before GROUP BY and HAVING after GROUP BY. It is a rather primitive rule, but it is useful in more than 90% of the cases.

      Difference between WHERE and HAVING in SQL:

      • WHERE is used for individual rows
      • HAVING is used for aggregations (result of a selection), such as: COUNT, AVERAGE, MIN, MAX or SUM
      • Use WHERE before GROUP BY and HAVING after GROUP BY (works in 90% of the cases)
    1. Which database technology to choose

      Which database to choose (advice from an Amazon employee):

      • SQL - ad hoc queries and/or support of ACID and transactions
      • NoSQL - otherwise. NoSQL is getting better with transactions and PostgreSQL is getting better with availability, scalability, durability
  11. Apr 2020
    1. 1) Redash and Falcon focus on people that want to do visualizations on top of SQL2) Superset, Tableau and PowerBI focus on people that want to do visualizations with a UI3) Metabase and SeekTable focus on people that want to do quick analysis (they are the closest to an Excel replacement)

      Comparison of data analysis tools:

      1) Redash & Falcon - SQL focus

      2) Superset, Tableau & PowerBI - UI workflow

      3) Metabase & SeekTable - Excel like experience

    1. Joins are not expensive. Who said it to you? As basically the whole concept of relational databases revolve around joins (from a practical point of view), these product are very good at joining. The normal way of thinking is starting with properly normalized structures and going into fancy denormalizations and similar stuff when the performance really needs it on the reading side. JSON(B) and hstore (and EAV) are good for data with unknown structure.
  12. Mar 2020
    1. Another nice SQL script paired with CRON jobs was the one that reminded people of carts that was left for more than 48 hours. Select from cart where state is not empty and last date is more than or equal to 48hrs.... Set this as a CRON that fires at 2AM everyday, period with less activity and traffic. People wake up to emails reminding them about their abandoned carts. Then sit watch magic happens. No AI/ML needed here. Just good 'ol SQL + Bash.

      Another example of using SQL + CRON job + Bash to remind customers of cart that was left (again no ML needed here)

    2. I will write a query like select from order table where last shop date is 3 or greater months. When we get this information, we will send a nice "we miss you, come back and here's X Naira voucher" email. The conversation rate for this one was always greater than 50%.

      Sometimes SQL is much more than enough (you don't need ML)

    1. ACID stands for Atomicity (an operation either succeeds completely or fails, it does not leave partial data), Consistency (once an application performs an operation the results of that operation are visible to it in every subsequent operation), Isolation (an incomplete operation by one user does not cause unexpected side effects for other users), and Durability (once an operation is complete it will be preserved even in the face of machine or system failure).

      ACID definition

  13. Jan 2020
    1. The SELECT part should not contain any columns not referenced in GROUP BY clause, unless it is wrapped with an aggregate function.
    1. Which to use? ANY is a later, more versatile addition, it can be combined with any binary operator returning a boolean value. IN burns down to a special case of ANY. In fact, its second form is rewritten internally: IN is rewritten with = ANY NOT IN is rewritten with <> ALL
    1. Single quotes return text strings.  Double quotes return (if you can really think of them as “returning” anything) identifiers, but with the case preserved.
  14. Dec 2019
  15. unix4lyfe.org unix4lyfe.org
    1. if you care at all about storing timestamps in MySQL, store them as integers and use the UNIX_TIMESTAMP() and FROM_UNIXTIME() functions.

      MySQL does not store offset

  16. Oct 2019
    1. Database engines in practice don’t actually run queries by joining, and then filtering, and then grouping, because they implement a bunch of optimizations reorder things to make the query run faster as long as reordering things won’t change the results of the query

      SQL queries are run by database engines in different order than we write them down

    2. SELECT isn’t the first thing, it’s like the 5th thing!

      Order of SQL queries:

      1. FROM/JOIN and all the ON conditions
      2. WHERE
      3. GROUP BY
      4. HAVING
      5. SELECT (including window functions)
      6. ORDER BY
      7. LIMIT SQL queries happen in this order* 1.
  17. Mar 2019
    1. DBA Por Acaso: RDS, MySQL e Tuning para Iniciantes

      Outro assunto que não é explicitamente coberto por nossos tópicos, mas que é base importante para o administrador de sistemas na nuvem - e aqui coberto em um nível introdutório, para não assustar ninguém. E procura por Cloud em https://wiki.lpi.org/wiki/DevOps_Tools_Engineer_Objectives_V1 para ver como esse assunto é importante!

  18. Dec 2018
    1. SELECT sj.name , sja.* FROM msdb.dbo.sysjobactivity AS sja INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id WHERE sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL

      View current running jobs SQL

  19. Nov 2018
    1. n the Advanced Security Settings dialog box, make sure that SELF is listed under Permission entries. If SELF is not listed, click Add, and then add SELF.Under Permission entries, click SELF, and then click Edit.In the Permission Entry dialog box, click the Properties tab.On the Properties tab, click This object only in the Apply onto list, and then click to select the check boxes for the following permissions under Permissions:Read servicePrincipalNameWrite servicePrincipalName

      Permissions needed for AD account to write SPN name

    2. rant delegation permission to the SQL Server service account domain user account.

      Computer and SQL service accounts need to be grated delegation permissions in AD users and computers

    1. The client and server computers must be part of the same Windows domain, or in trusted domains. A Service Principal Name (SPN) must be registered with Active Directory, which assumes the role of the Key Distribution Center in a Windows domain. The SPN, after it is registered, maps to the Windows account that started the SQL Server instance service. If the SPN registration has not been performed or fails, the Windows security layer cannot determine the account associated with the SPN, and Kerberos authentication will not be used.

      2 main criteria for linked servers to pass through AD credentials

      1. be on the same domain
      2. have an SPN registered for the AD account running the SQL service
  20. Oct 2018
    1. with recursive rnd_move(move) as ( select *, random() rnd from generate_series(1, 9) move ), winning_positions(a, b, c) as ( values (1, 2, 3), (4, 5, 6), (7, 8, 9), -- rows (1, 4, 7), (2, 5, 8), (3, 6, 9), -- cols (1, 5, 9), (3, 5, 7) -- diagonals ), game as ( select 'O' as who_next, ARRAY['.', '.', '.', '.', '.', '.', '.', '.', '.'] as board union ( select case when who_next = 'X' then 'O' else 'X' end as who_next, board[:move-1] || who_next || board[move+1:] from game, rnd_move where board[move] = '.' order by rnd limit 1 ) ), game_with_winner as ( select *, lag(a is not null) over () as finished, lag(who_next) over () as who from game left join winning_positions on board[a] != '.' and board[a] = board[b] and board[a] = board[c] ) select array_to_string(board[1:3] || chr(10) || board[4:6] || chr(10) || board[7:9] || chr(10), '') board, case when a is not null then who || ' wins' end as winner from game_with_winner where not finished;
  21. May 2018
    1. hi there learn MSBI in 20 min with handwritten explanation on each and every topics on the Course with real time examples


    2. hi there we have came up with the newly launched Procedural Query in PL sql with Oracle 12 c so please check this out For detailed Description on the Pl Sql from the scratch to advance level:-


  22. Apr 2018
    1. sqlite> .mode column sqlite> .headers on

      At the start of your session,these will format your sqlite3 output so it is clearer, and add columns headers.

  23. Nov 2017
    1. select top 1 * from newsletters where IsActive = 1 order by PublishDate desc

      This doesn't require a full table scan or a join operation. That's just COOL

  24. Sep 2017
    1. PostgreSQL連接Python


  25. May 2017
    1. REQUIRED fields are no longer supported in Standard SQL. If you're using Standard SQL (as opposed to Legacy SQL), they recommend you change all your REQUIRED fields to NULLABLE.
  26. Apr 2017
    1. significant.

      So, there is a performance hit for using + or +=, but not enough for any reasonable string literal in code. Concatenating a long paragraph, use StringBuilder.

  27. Aug 2016
    1. Todo o dado (valor atómico) pode ser acedido logicamente (e unicamente) usando o nome da tabela, o valor da chave primária da linha e o nome da coluna


    2. Um banco de dados relacional é um banco de dados que modela os dados de uma forma que eles sejam percebidos pelo usuário como tabelas, ou mais formalmente relações.

      tabelas = relações

    1. chaves estrangeiras

      A chave estrangeira ocorre quando um atributo de uma relação for chave primária em outra relação. https://pt.wikipedia.org/wiki/Chave_estrangeira

    2. chaves candidatas

      identificador único que garante que nenhuma tupla será duplicada; isto faz com que seja criado um relacionamento em algo denominado multiconjunto, porque viola a definição básica de um conjunto. Uma chave pode ser composta, isto é, pode ser formada por vários atributos. https://pt.wikipedia.org/wiki/Chave_candidata

    3. Uma relação é um conjunto desordenado de tuplas.

      A relação determina o modo como cada registro de cada tabela se associa a registros de outras tabelas.

    4. Na construção da tabela identificam-se os dados da entidade. A atribuição de valores a uma entidade constrói um registro da tabela.


    5. conjunto de pares ordenados de domínio e nome que serve como um cabeçalho para uma relação.


    6. Os blocos básicos do modelo relacional são o domínio, ou tipo de dado.

      todos os dados são representados como relações matemáticas

    7. Uma relação é similar ao conceito de tabela e uma tupla é similar ao conceito de linha.

      Uma tupla é um conjunto de atributos que são ordenados em pares de domínio e valor.

    1. Num banco de dados relacional, quando um registro aponta para o outro, dependente deste, há de se fazer regras para que o registro "pai" não possa ser excluído se ele tiver "filhos" (as suas dependências).

      Integridade referencial

  28. Jan 2016
  29. Feb 2015
    1. 2.3.2. Mapping native queries You can also map a native query (ie a plain SQL query). To achieve that, you need to describe the SQL resultset structure using @SqlResultSetMapping (or @SqlResultSetMappings if you plan to define several resulset mappings). Like @NamedQuery, a @SqlResultSetMapping can be defined at class level or in a JPA XML file. However its scope is global to the application.

      JPA SQL native query den join ile birden fazla nesne elde etme

    1. @SqlResultSetMappings( { @SqlResultSetMapping(name = "ProfessorWithAddress", entities = { @EntityResult(entityClass = Professor.class), @EntityResult(entityClass = Address.class) }) })

      jpa birden fazla tabloyu direk sınıf ile eşleme join table mapping

    1. Use @FieldResult in the SqlResultSetMapping, to link each entity property to its column alias

      birden fazla tablonun birleşimini sınıflara eşleştirirken oluşan hatanın giderilmesi