SQL

SELECT 
    orderNumber,
    orderDate,
    customerName,
    orderLineNumber,
    productName,
    quantityOrdered,
    priceEach
FROM
    orders
INNER JOIN orderdetails 
    USING (orderNumber)
INNER JOIN products 
    USING (productCode)
INNER JOIN customers 
    USING (customerNumber)
ORDER BY 
    orderNumber, 
    orderLineNumber;

SELECT FirstName, LastName
 FROM CUSTOMER
 WHERE EXISTS
  (SELECT DISTINCT CustomerID
  FROM SALES
  WHERE SALES.CustomerID = CUSTOMER.CustomerID);

SELECT (DATE '2011-01-28', DATE '2011-02-01' + 1) OVERLAPS
       (DATE '2011-02-01', DATE '2011-02-01'    )

SELECT columns
FROM table-1 
INNER JOIN table-2
ON table-1.column = table-2.column;

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
FULL OUTER JOIN basket_b 
    ON fruit_a = fruit_b;
Code language: SQL (Structured Query Language) (sql)

do $$ 
declare
   counter    integer := 1;
   first_name varchar(50) := 'John';
   last_name  varchar(50) := 'Doe';
   payment    numeric(11,2) := 20.5;
begin 
   raise notice '% % % has been paid % USD', 
       counter, 
	   first_name, 
	   last_name, 
	   payment;
end $$;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

            

                
            

         
SELECT TO_CHAR(NOW() :: DATE, 'dd/mm/yyyy');
Code language: SQL (Structured Query Language) (sql)

SELECT age(timestamp1, timestamp2);

  -- Difference between Oct 02, 2011 and Jan 01, 2012 in years
  SELECT DATE_PART('year', '2012-01-01'::date) - DATE_PART('year', '2011-10-02'::date);
  -- Result: 1

SELECT name, continent, indep_year,
    CASE WHEN indep_year < 1900 THEN 'before 1900'
         WHEN indep_year <= 1930 THEN 'between 1900 and 1930'
         ELSE 'after 1930' END
         AS indep_year_group
FROM countries
ORDER BY indep_year_group;

SELECT 
   table_name, 
   column_name, 
   data_type 
FROM 
   information_schema.columns
WHERE 
   table_name = 'city';
Code language: SQL (Structured Query Language) (sql)

Select * from ( 
Select Id_pk, col1, col2...,coln from table1, ‘Old_table’ 
Union all 
Select Id_pk, col1, col2...,coln from table2, 'New_tbale' 
) cmpr order by Id_pk;

select * from table where random() < 0.01 limit 1000;

EXPLAIN SELECT * FROM tenk1 WHERE stringu1 < 'IAAAAA';

                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..483.00 rows=3077 width=244)
   Filter: (stringu1 < 'IAAAAA'::name)

EXPLAIN SELECT count(*) FROM items;

Aggregate  (cost=20834.00..20834.01 rows=1 width=0)
  ->  Seq Scan on items  (cost=0.00..18334.00 rows=1000000 width=0)

-- Example: Orders have a default total of 0 cents
alter table orders alter column total_cents set default 0;

-- Example: Items are available by default
alter table items alter column available set default true;

links:

https://www.postgresql.org/docs/9.5/ddl-alter.html

https://www.postgresql.org/message-id/4558AA88.3070102@brainscraps.com

-- Example: Orders have a default total of 0 cents
alter table orders alter column total_cents set default 0;

-- Example: Items are available by default
alter table items alter column available set default true;

links:

https://www.postgresql.org/docs/9.5/ddl-alter.html

https://www.postgresql.org/message-id/4558AA88.3070102@brainscraps.com

SELECT CompanyName, 
       ProductCount = (SELECT COUNT(P.id)
                         FROM [Product] P
                        WHERE P.SupplierId = S.Id)
  FROM Supplier S

SELECT Column(s) FROM table_name WHERE column IN (value1, value2, ... valueN);

 -- Azure Synapse Analytics, Parallel Data Warehouse 

DROP FUNCTION [IF EXISTS] [ schema_name. ] function_name
[;] 

select ord_num, agent_code, ord_date, ord_amount
from orders
where(agent_code, ord_amount) IN
(SELECT agent_code, MIN(ord_amount)
FROM orders 
GROUP BY agent_code);  

CREATE FUNCTION check_password(uname TEXT, pass TEXT)
RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
BEGIN
        SELECT  (pwd = $2) INTO passed
        FROM    pwds
        WHERE   username = $1;

        RETURN passed;
END;
$$  LANGUAGE plpgsql
    SECURITY DEFINER
    -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
    SET search_path = admin, pg_temp;

create function get_film_count(len_from int, len_to int)
returns int
language plpgsql
as
$$
declare
   film_count integer;
begin
   select count(*) 
   into film_count
   from film
   where length between len_from and len_to;
   
   return film_count;
end;
$$;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

 array_multisort(array_column($array, 'datetime'), SORT_DESC, $array);

SQL> with t1(col) as(
  2    select 1    from dual union all
  3    select 2    from dual union all
  4    select null from dual union all
  5    select 3    from dual
  6  )
  7  select *
  8    from t1
  9  order by col asc nulls last
  10  ;

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

SELECT DISTINCT ON (location) location, time, report
    FROM weather_reports
    ORDER BY location, time DESC;

ORDER BY array_position(ARRAY['f', 'p', 'i', 'a']::varchar[], x_field)

select now(),
       extract(epoch from now()),                                          -- correct
       extract(epoch from now() at time zone 'utc'),                       -- incorrect
       extract(epoch from now() at time zone 'utc' at time zone 'utc');    -- correct

          now                  |    date_part     |    date_part     |    date_part
-------------------------------+------------------+------------------+------------------
 2014-10-14 10:19:23.726908+02 | 1413274763.72691 | 1413267563.72691 | 1413274763.72691
(1 row)


https://www.epochconverter.com/

SELECT agent_code,COUNT(agent_code),MAX(ord_amount) 
FROM orders 
GROUP BY agent_code 
HAVING MAX(ord_amount) IN(500,800,2000);

SELECT version();

CREATE TABLE [New Table] AS TABLE [Old Table] WITH NO DATA;

$words = preg_replace('/[0-9]+/', '', $words);

SELECT
    column_name,
    data_type
FROM
    information_schema.columns
WHERE
    table_name = 'table_name';

UPDATE users SET fullname = REPLACE(fullname, ' ', '') WHERE fullname ~* ' ';

SELECT
    column_name,
    data_type
FROM
    information_schema.columns
WHERE
    table_name = 'table_name';

-- Lastly set your new PRIMARY KEY
ALTER TABLE <table_name> ADD PRIMARY KEY (id);

DROP TABLE products CASCADE;

COPY persons(first_name, last_name, dob, email)
FROM 'C:\sampledb\persons.csv'
DELIMITER ','
CSV HEADER;
Code language: SQL (Structured Query Language) (sql)

 $ psql -h host_name -U user_name -d database_name -c 
 "\\copy table_name FROM 'file_path' WITH CSV HEADER DELIMITER ','"

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
COMMENT ON SCHEMA public IS 'standard public schema';

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

CASE
     WHEN condition_1  THEN result_1
     WHEN condition_2  THEN result_2
     ...
     ELSE  result_n
END

 OpenAI

SELECT LEFT(first_name, 1) initial,
    COUNT(*)
FROM customer
GROUP BY initial
ORDER BY initial;

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

-- Schema: public

-- DROP SCHEMA public;

CREATE SCHEMA public
  AUTHORIZATION postgres;

GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
COMMENT ON SCHEMA public
  IS 'standard public schema';

Syntax:
CREATE TABLE new_table AS 
TABLE existing_table 
WITH NO DATA;

expression BETWEEN value-1 AND value-2;

DELETE FROM myrecords WHERE record ~ '^[^0-9]+$';

CREATE TABLE product(
    id SERIAL PRIMARY KEY,
    name VARCHAR NOT NULL,
    price NUMERIC(10,2),
    net_price NUMERIC(10,2),
    segment_id INT NOT NULL,
    FOREIGN KEY(segment_id) REFERENCES product_segment(id)
);


INSERT INTO 
    product (name, price, segment_id) 
VALUES 
    ('diam', 804.89, 1),
    ('vestibulum aliquet', 228.55, 3),
    ('lacinia erat', 366.45, 2),
    ('scelerisque quam turpis', 145.33, 3),
    ('justo lacinia', 551.77, 2),
    ('ultrices mattis odio', 261.58, 3),
    ('hendrerit', 519.62, 2),
    ('in hac habitasse', 843.31, 1),
    ('orci eget orci', 254.18, 3),
    ('pellentesque', 427.78, 2),
    ('sit amet nunc', 936.29, 1),
    ('sed vestibulum', 910.34, 1),
    ('turpis eget', 208.33, 3),
    ('cursus vestibulum', 985.45, 1),
    ('orci nullam', 841.26, 1),
    ('est quam pharetra', 896.38, 1),
    ('posuere', 575.74, 2),
    ('ligula', 530.64, 2),
    ('convallis', 892.43, 1),
    ('nulla elit ac', 161.71, 3);
Code language: SQL (Structured Query Language) (sql)