Netezza Common SQL commands

Nzsql commands 

ALTER DATABASE

 To change the default 8-bit character set in the emp database, enter:

system(admin)=> ALTER DATABASE emp set default character set

latin9;

 To rename the emp database, enter:

system(admin)=> ALTER DATABASE emp RENAME TO employees;

 To change the owner of the emp database, enter:

system(admin)=> ALTER DATABASE emp OWNER TO admin3;

ALTER GROUP

 To add the users Karl and John to the group staff, enter:

system(admin)=> ALTER GROUP staff ADD USER karl, john;

 To change the session idle time, enter:

system(admin)=> ALTER GROUP staff WITH SESSIONTIMEOUT 300;

 To remove the user Beth from the group workers, enter:

system(admin)=> ALTER GROUP workers DROP USER beth;

 To change the group’s maximum priority, enter:

system(admin)=> ALTER GROUP workers WITH MAXPRIORITY critical;

ALTER HISTORY CONFIGURATION

SYSTEM(ADMIN)=> ALTER HISTORY CONFIGURATION all_hist COLLECT QUERY;

ALTER SEQUENCE

 To change the maximum value, enter:

system(admin)=> ALTER SEQUENCE sequence1 MAXVALUE 1000;

ALTER SESSION

 To change the session from normal to critical, enter:

system(admin)=> ALTER SESSION SET PRIORITY TO critical;

ALTER SYNONYM

 To rename the synonym payroll to pr, enter:

system(admin)=> ALTER SYNONYM payroll RENAME TO pr;

 To change the owner of the synonym pr, enter:

system(admin)=> ALTER SYNONYM pr OWNER TO accounting;

ALTER TABLE

 To change a default, enter:

system(admin)=> ALTER TABLE distributors ALTER COLUMN address DROP

DEFAULT;

 To modify the length of a varchar column, enter:

system(admin)=> ALTER TABLE t3 MODIFY COLUMN (col1 VARCHAR(6));

 To rename the column address, enter:

system(admin)=> ALTER TABLE distributors RENAME COLUMN address TO

city;

 To rename the existing table distributors, enter:

system(admin)=> ALTER TABLE distributors RENAME TO suppliers;

 To change the table owner, enter:

system(admin)=> ALTER TABLE distributors OWNER TO carmen;

 To set privileges, enter:

system(admin)=> ALTER TABLE distributors SET PRIVILEGES TO carmen;

 To add a constraint, enter:

system(admin)=> ALTER TABLE distributors ADD CONSTRAINT empkey

PRIMARY KEY(col1) INITIALLY IMMEDIATE;

 To drop a constraint, enter:

system(admin)=> ALTER TABLE distributors DROP CONSTRAINT empkey

CASCADE;

ALTER USER

 To change a user password, enter:

system(admin)=> ALTER USER davide WITH PASSWORD 'hu8jmn3';

 To set the user’s password expiration, enter:

system(admin)=> ALTER USER manuel WITH VALID UNTIL 'Jan 31 2030';

 To set the user’s rowset limits, enter:

system(admin)=> ALTER USER mark WITH ROWSETLIMIT 10000;

ALTER VIEW

 To rename a view, enter:

system(admin)=> ALTER VIEW emp RENAME TO employees

 To change the owner of a view, enter:

system(admin)=> ALTER VIEW emp OWNER TO john

BEGIN

 To begin a user transaction, enter:

system(admin)=> BEGIN WORK;

COMMENT

 To add a comment to the table mytable, enter:

system(admin)=> COMMENT ON my_table IS 'This is my table.';

 To add comments to a database, sequence, view, and column, enter:

system(admin)=> COMMENT ON DATABASE my_database IS 'Development

Database';

system(admin)=> COMMENT ON SEQUENCE my_sequence IS 'Used to

generate primary keys';

system(admin)=> COMMENT ON VIEW my_view IS 'View of departmental

costs';

system(admin)=> COMMENT ON COLUMN my_table.my_field IS 'Employee ID

number';

COMMIT

 To make all changes permanent, enter:

COMMIT WORK;

 To use the COMMIT command within (at the end of) a transaction, enter:

system(admin)=> begin;

BEGIN

system(admin)=> insert into cities values ('Boston',

'Massachusetts');

INSERT 0 1

system(admin)=> insert into cities values ('Houston', 'Texas');

INSERT 0 1

system(admin)=> commit;

COMMIT

system(admin)=>

COPY

 To copy a table to standard output, using a vertical bar (|) as the field delimiter, enter:

system(admin)=> COPY country TO stdout USING DELIMITERS '|';

 To copy data from a UNIX file into the table country, enter:

system(admin)=> COPY country FROM '/usr1/proj/bray/sql/country_

data';

 This is a sample of data suitable for copying into a table from stdin (so it has the termination

sequence on the last line):

AF AFGHANISTAN

AL ALBANIA

DZ ALGERIA

ZM ZAMBIA

ZW ZIMBABWE

\.

CREATE DATABASE

 To create a new database, enter:

system(admin)=> CREATE DATABASE customers;

CREATE GROUP

 To create an empty group, enter:

system(admin)=> CREATE GROUP staff;

 To create a group with members, enter:

system(admin)=> CREATE GROUP marketing WITH USER jonathan, david;

 To set the group’s maximum priority, enter:

system(admin)=> Create GROUP workers WITH MAXPRIORITY critical;

CREATE HISTORY CONFIGURATION

The following command creates a history configuration named all_hist which enables the

capture of all history information:

SYSTEM(ADMIN)=> CREATE HISTORY CONFIGURATION all_hist HISTTYPE QUERY

DATABASE histdb USER histusr PASSWORD histusrpw COLLECT PLAN,COLUMN

LOADINTERVAL 5 LOADMINTHRESHOLD 4 LOADMAXTHRESHOLD 20 VERSION 1;

The following command creates a history configuration named hist_mincollect which collects

the basic level of history data (login failure, session creation, and termination, and the

startup of the alcapp process):

SYSTEM(ADMIN)=> CREATE HISTORY CONFIGURATION hist_mincollect HISTTYPE

QUERY DATABASE histdb USER histusr PASSWORD histusrpw COLLECT

LOADINTERVAL 5 LOADMINTHRESHOLD 4 LOADMAXTHRESHOLD 20 VERSION 1;

The following command creates a history configuration named hist_queryonly which collects

query and plan details and the basic level of information:

SYSTEM(ADMIN)=> CREATE HISTORY CONFIGURATION hist_mincollect HISTTYPE

QUERY DATABASE "query db" USER histusr PASSWORD histusrpw COLLECT

QUERY,PLAN LOADINTERVAL 5 LOADMINTHRESHOLD 4 LOADMAXTHRESHOLD 20

VERSION 1;

The following command creates a history configuration named hist_disabled that disables

history collection:

SYSTEM(ADMIN)=> CREATE HISTORY CONFIGURATION hist_disabled HISTTYPE

NONE;

CREATE MATERIALIZED VIEW

 To create a materialized view, enter:

system(admin)=> CREATE MATERIALIZED VIEW kinds AS SELECT t1 FROM

emp ORDER BY name;

CREATE SEQUENCE

 To create a sequence as an integer with a starting value of 11, increment of 2, minvalue

of 1, and maxvalue of 100, enter:

CREATE SEQUENCE sequence1 As integer START WITH 11 INCREMENT BY 2

MINVALUE 1 MAXVALUE 100 NO CYCLE;

CREATE SYNONYM

 To create a synonym pr for the table payroll, enter:

CREATE SYNONYM pr FOR payroll;

CREATE TABLE

 To create a table, enter:

system(admin)=>

CREATE TABLE name (

code CHARACTER(5) CONSTRAINT firstkey PRIMARY KEY,

title CHARACTER VARYING(40) NOT NULL,

did DECIMAL(3) NOT NULL,

date_prod DATE,

kind CHAR(10),

len INTERVAL HOUR TO MINUTE

);

 To define a primary key table constraint for the table films, you can define primary key

table constraints on one or more columns of the table:

system(admin)=>

CREATE TABLE name (

code CHAR(5),

title VARCHAR(40),

did DECIMAL(3),

date_prod DATE,

kind VARCHAR(10),

len INTERVAL HOUR TO MINUTE,

CONSTRAINT code_title PRIMARY KEY(code,title)

);

 To define a primary key constraint for the table distributors, enter:

system(admin)=> CREATE TABLE distributors (

did DECIMAL(3),

name CHAR VARYING(40),

PRIMARY KEY(did)

);

system(admin)=> CREATE TABLE distributors (

did DECIMAL(3) PRIMARY KEY,

name VARCHAR(40)

);

Note: The two examples are equivalent — the first uses the table constraint syntax, the

second uses the column constraint notation.

 To define two not null column constraints on the table distributors, one of which is

explicitly given a name, enter:

system(admin)=> CREATE TABLE distributors (

did DECIMAL(3) CONSTRAINT no_null NOT NULL,

name VARCHAR(40) NOT NULL

);

CREATE TABLE AS

 To show an example of the CREATE TABLE AS command, consider the following table,

named cows, enter:

system(admin)=> CREATE TABLE cow2 AS SELECT * FROM cows;

cnumber | cname | cbreed | ckind

---------+--------+-------------+-------

3 | Cindy | Ayrshire | milk

8 | Muffin | Guernsey | milk

2 | Martha | Brown Swiss | milk

7 | Joe | Angus | beef

5 | Gretel | Highland | beef

1 | Betsy | Holstein | milk

6 | Bob | Angus | beef

4 | Mindy | Hereford | beef

9 | Milda | Jersey | milk

(9 rows)

 To use the CREATE TABLE AS command to create a new table from two columns of the

table cows, enter:

system(admin)=> CREATE TABLE cow2 AS SELECT cname, cbreed FROM cows;

The result is the new table, named cows2:

system(admin)=> SELECT * FROM cows2;

cname | cbreed

--------+-------------

Cindy | Ayrshire

Muffin | Guernsey

Mindy | Hereford

Milda | Jersey

Betsy | Holstein

Gretel | Highland

Martha | Brown Swiss

Joe | Angus

Bob | Angus

(9 rows)

CREATE USER

 To create a user with a password, enter:

system(admin)=> CREATE USER davide WITH PASSWORD 'jw8s0F4';

 To create a user with a password whose user account expires on January 1, 2003,

enter:

system(admin)=> CREATE USER miriam WITH PASSWORD 'jw8s0F4' VALID

UNTIL 'Jan 1 2003';

CREATE VIEW

 To create a view consisting of all comedy films, enter:

system(admin)=> CREATE VIEW kinds AS

SELECT *

FROM films

WHERE kind = 'Comedy';

 To display the view, enter:

system(admin)=> SELECT * FROM kinds;

code | title | did | date_prod | kind | len

------+---------------------------+-----+------------+---------+--

C_701 | There's a Girl in my Soup | 107 | 1970-06-11 | Comedy |

01:36:00

UA502 | Bananas | 105 | 1971-07-13 | Comedy |

01:22:00

(2 rows)

 To re-create the view while retaining the permissions of the original view, enter:

system(admin)=> CREATE OR REPLACE VIEW kinds AS

SELECT *

FROM films

WHERE kind = 'Action';

DELETE

 To remove all but musicals from the table films, enter:

system(admin)=> DELETE FROM films WHERE kind <> 'Musical';

 To display the remaining rows, enter:

system(admin)=> SELECT * FROM films;

code| title| did| date_prod| kind| len

----+------|-----|----------+-----+------

UA501| West Side Story| 105| 1961-01-03| Musical| 02:32:00

TC901| The King and I| 109| 1956-08-11| Musical| 02:13:00

WD101| Bed Knobs and Broomsticks| 111| | Musical | 01:57:00

(3 rows)

-----+---------------------------|------|----------+-----------

(3 rows)

 To clear the table films, enter:

system(admin)=> DELETE FROM films;

 To display the valid, but blank, table, enter:

system(admin)=> SELECT * FROM films;

code| title| did| date_prod| kind| len

----+------|-----|----------+-----+------

(0 rows)

DROP DATABASE

 To delete the database emp, enter:

system(admin)=> DROP DATABASE emp;

DROP GROUP

 To drop the group staff, enter:

system(admin)=> DROP GROUP staff;

DROP HISTORY CONFIGURATION

SYSTEM(ADMIN)=> DROP HISTORY CONFIGURATION basic_hist;

DROP SEQUENCE

 To drop sequence1, enter:

system(admin)=> DROP SEQUENCE sequence1;

DROP SESSION

 To drop session ID 17044:

system(admin)=> DROP SESSION 17044;

DROP SESSION

DROP SYNONYM

 To drop synonym pr, enter:

system(admin)=> DROP SYNONYM pr;

DROP TABLE

 To drop (delete) two tables, films and distributors, enter:

system(admin)=> DROP TABLE films, distributors;

DROP USER

 To drop the user account jonathan, enter:

SYSTEM(ADMIN)=> DROP USER jonathan;

DROP USER

 If you drop a user that owns objects:

SYSTEM(ADMIN)=> DROP USER jonathan;

ERROR: DROP USER: user "jonathan" owns objects, cannot be removed

DROP VIEW

 To remove the view kinds, enter:

system(admin)=> DROP VIEW kinds;

EXPLAIN

 To display a query plan for a simple query on a table with a single int4 column and 128

rows, enter:

system(admin)=> EXPLAIN SELECT * FROM foo;

NOTICE: QUERY PLAN:

Seq Scan on foo (cost=0.00..2.28 rows=128 width=4)

EXPLAIN

 To display a query plan for a join between the emp and grp tables, enter:

system(admin)=> EXPLAIN SELECT emp.* FROM emp,grp WHERE

emp.id=grp.grp_id;

NOTICE: QUERY PLAN:

Hash Join (cost=0.01..0.04 rows=10000 width=28)

-> Seq Scan on emp (cost=0.00..0.01 rows=1000 width=28)

-> Hash (cost=0.01..0.01 rows=1000 width=4)

-> Seq Scan on grp (cost=0.00..0.01 rows=1000 width=4)

EXPLAIN

 To display a query plan for TPHC Query 3, enter:

system(admin)=> EXPLAIN SELECT

l_orderkey,

sum(l_extendedprice * (1 - l_discount)) as revenue,

o_orderdate,

o_shippriority

FROM

customer,

orders,

lineitem

WHERE

c_mktsegment = 'BUILDING'

and c_custkey = o_custkey

and l_orderkey = o_orderkey

and o_orderdate < date '1995-03-15'

and l_shipdate > date '1995-03-15'

GROUP BY

l_orderkey,

o_orderdate,

o_shippriority

ORDER BY

revenue desc,

o_orderdate

LIMIT 10;

 To display the distribution from a SELECT statement in a CTAS command, enter:

dev(admin)=> EXPLAIN DISTRIBUTION SELECT COUNT(*),grp FROM emp

GROUP BY grp;

NOTICE: Result-set distribution (for "CREATE TABLE AS")

Distributed on hash: "grp"

EXPLAIN

GENERATE EXPRESS STATISTICS

 To generate statistics on the table cows, enter:

system(admin)=> GENERATE EXPRESS STATISTICS ON cows;

GENERATE EXPRESS STATISTICS

GENERATE STATISTICS

 To generate statistics on the table cows, column cnumber, enter:

system(admin)=> GENERATE STATISTICS ON cows (cnumber);

GENERATE STATISTICS

 To generate statistics on all the tables in database mydb, enter:

mydb(admin)=> GENERATE STATISTICS;

GENERATE STATISTICS

GRANT

 To grant the insert privilege to all users on the user table films, enter:

system(admin)=> GRANT INSERT ON films TO PUBLIC;

The table

GROOM TABLE

 To migrate data for a versioned table, enter:

system(admin)=> GROOM TABLE <table_name> VERSIONS;

 To reclaim deleted records in a table (equivalent to nzreclaim -records), enter:

system(admin)=> GROOM TABLE <table_name> RECORDS ALL;

 To identify data pages containing only deleted records and to reclaim extents that are

empty as a result (an extension of nzreclaim -startEndBlocks), enter:

system(admin)=> GROOM TABLE <table_name> PAGES ALL;

 To organize data not already organized in a clustered base table, enter:

system(admin)=> GROOM TABLE <table_name> RECORDS READY;

INSERT

 To insert a single row into the table films, enter:

system(admin)=> INSERT INTO films VALUES

('UA502','Bananas',105,'1971-07-13','Comedy',INTERVAL '82

minute');

 In this sample, the last column len is omitted, and therefore the last column assumes

the default value of null:

system(admin)=> INSERT INTO films (code, title, did, date_prod,

kind)

VALUES ('T_601', 'Yojimbo', 106, DATE '1961-06-16', 'Drama');

 To insert a single row into the table distributors, enter:

system(admin)=> INSERT INTO distributors (name) VALUES ('British

Lion');

Note: Because only the column name is specified, the omitted column is assigned its

default value.

 To insert several rows into the table films from the table tmp, enter:

system(admin)=> INSERT INTO films SELECT * FROM tmp;

RESET

 To set DateStyle to its default value, enter:

system(admin)=> RESET DateStyle;

 To set Geqo to its default value, enter:

system(admin)=> RESET geqo;

REVOKE

 To revoke the insert privilege for the group public on the table films, enter:

system(admin)=> REVOKE INSERT ON films FROM PUBLIC;

ROLLBACK

 To abort all changes, enter:

system(admin)=> ROLLBACK WORK;

SELECT

 To join the table films with the table distributors, enter:

system(admin)=> SELECT f.title, f.did, d.name, f.date_prod, f.kind

FROM distributors d, films f WHERE f.did = d.did;

title |did|name|date_prod|kind

--------------------------+-----+------------------+-----------+-------

The Third Man|101|British Lion|1949-12-23|Drama

The African Queen|101|British Lion|1951-08-11|Romantic

Une Femme est une Femme|102|Jean Luc Godard|1961-03-12|Romantic

Vertigo|103|Paramount|1958-11-14|Action

Becket |103|Paramount|1964-02-03|Drama

48 Hours|103|Paramount|1982-10-22|Action

War and Peace|104|Mosfilm|1967-02-12|Drama

West Side Story|105|United Artists|1961-01-03|Musical

Bananas|105|United Artists|1971-07-13|Comedy

Yojimbo|106|Toho|1961-06-16|Drama

There’s a Girl in my Soup|107|Columbia|1970-06-11|Comedy

Taxi Driver|107|Columbia|1975-05-15|Action

Absence of Malice|107|Columbia|1981-11-15|Action

Storia di una donna|108|Westward|1970-08-15|Romantic

The King and I|109|20th Century Fox|1956-08-11|Musical

Das Boot|110|Bavaria Atelier|1981-11-11|Drama

Bed Knobs and Broomsticks|111|Walt Disney||Musical

(17 rows)

 To sum the column len of all films and group the results by kind, enter:

system(admin)=> SELECT kind, SUM(len) AS total FROM films GROUP BY

kind;

kind|total

----+-------

Action|07:34

Comedy|02:58

Drama|14:28

Musical|06:42

Romantic|04:38

(5 rows)

 To sum the column len of all films, group the results by kind, and show those group

totals that are less than 5 hours, enter:

system(admin)=> SELECT kind, SUM(len) AS total FROM films GROUP BY

kind HAVING SUM(len) < INTERVAL '5 HOUR';

kind| total

----+------

Comedy| 02:58

Romantic| 04:38

(2 rows)

 To sort the individual results according to the contents of the second column (name),

use either example:

system(admin)=> SELECT * FROM distributors ORDER BY name;

system(admin)=> SELECT * FROM distributors ORDER BY 2;

did| name

---|---------------------

109| 20th Century Fox

110| Bavaria Atelier

101| British Lion

107| Columbia

102| Jean Luc Godard

113| Luso Films

104| Mosfilm

103| Paramount

106| Toho

105| United Artists

111| Walt Disney

112| Warner Bros.

108| Westward

(13 rows)

 To show how to obtain the union of the tables distributors and actors, restricting the

results to those that begin with letter W in each table, enter:

system(admin)=> SELECT distributors.name

FROM distributors

WHERE distributors.name LIKE 'W%';

UNION

SELECT actors.name

FROM actors

WHERE actors.name LIKE 'W%';

Walt Disney

Walter Matthau

Warner Bros.

Warren Beatty

Westward

Woody Allen

Note: Because only distinct rows are wanted, the all keyword is omitted.

SET

 To set the style of date to the European convention, enter:

SET DATESTYLE TO European;

 To set the threshold to 20 percent, enter:

SET SYSTEM DEFAULT MATERIALIZE THRESHOLD 20;

SET AUTHENTICATION

The following provides sample usage.

SET AUTHENTICATION ldap base 'dc=netezza,dc=com' server

'ldapserver.netezza.com' port '389' version '3' binddn 'ldapreader'

scope 'base' ssl 'off' attrname 'cn' namecase 'lowercase';

SET CONNECTION

The following provides sample usage.

SET CONNECTION HOST DATABASE 'ALL' IPADDR '192.168.0.0' IPMASK

'255.255.255.0';

SET HISTORY CONFIGURATION

SYSTEM(ADMIN)=> SET HISTORY CONFIGURATION all_hist;

SET SESSION

 To set a session to read-only, enter:

system(admin)=> SET SESSION READ ONLY;

SET SYSTEM DEFAULT

 To set the system default timeout to five hours (300 minutes), enter:

system(admin)=> SET SYSTEM DEFAULT SESSIONTIMEOUT TO 300;

SET TRANSACTION

 To set the transaction isolation level command, enter:

system(admin)=> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET VARIABLE

SHOW

 To show the current DateStyle setting, enter:

system(admin)=> SHOW DateStyle;

NOTICE: DateStyle is ISO with US (NonEuropean) conventions

SHOW AUTHENTICATION

 If you have the proper access to show authentication settings, you can view all but the

password. A sample follows.

system (admin) => SHOW AUTHENTICATION ALL;

AUTH_OPTION | AUTH_VALUE

--------------------------------------------------

AUTHENTICATION METHOD | LDAP

AUTHMTHD LDAP ATTRNAME | cn

AUTHMTHD LDAP BASE | dc=example, dc=org

AUTHMTHD LDAP BINDDN | ldapreader

AUTHMTHD LDAP NAMECASE | lowercase

AUTHMTHD LDAP PORT | 389

AUTHMTHD LDAP SCOPE | BASE

AUTHMTHD LDAP SERVER | myldap.netezza.com

AUTHMTHD LDAP SSL | ON

AUTHMTHD LDAP VERSION | 3

 If you do not have the Manage System privilege to show authentication settings, you

can view the type of authentication. A sample follows.

system (admin) => SHOW AUTHENTICATION;

AUTH_OPTION | AUTH_VALUE

--------------------------------------------------

AUTHENTICATION METHOD | LDAP

SHOW CONNECTION

Sample usage with output follows.

SYSTEM(ADMIN)=> SHOW CONNECTION;

CONNID | CONNTYPE | CONNDB | CONNIPADDR | CONNIPMASK | CONNAUTH

--------+-----------+--------+-------------+-----------------+--------

1 | local | all | | | trust

2 | host | all | 127.0.0.1 | 255.255.255.255 | md5

3 | host | all | 0.0.0.0 | 0.0.0.0 | md5

4 | hostnossl | ALL | 192.168.1.2 | 255.255.255.255 | md5

(4 rows)

SHOW HISTORY CONFIGURATION

SYSTEM(ADMIN)=> SHOW HISTORY CONFIGURATION plan_hist;

SHOW PLANFILE

Sample usage with a short exerpt of the output.

TESTER(USER1)=> SHOW PLANFILE;

SHOW SESSION

 The following command shows information about the current session:

SHOW SESSION;

 The following command shows information about all sessions:

SHOW SESSION ALL;

 The following command shows verbose information about the current session:

SHOW SESSION VERBOSE;

SHOW SYSTEM DEFAULT

 To show the query timeout, enter:

system(admin)=> SHOW SYSTEM DEFAULT QUERYTIMEOUT;

NOTICE: QUERYTIMEOUT is UNLIMITED

TRUNCATE

 To truncate the table bigtable, enter:

system(admin)=> TRUNCATE TABLE bigtable;

UPDATE

 To change the word Drama to Dramatic within the column kind, enter:

system(admin)=> UPDATE films SET kind = 'Dramatic' WHERE kind =

'Drama';

system(admin)=> SELECT * FROM films WHERE kind = 'Dramatic' OR kind

kind = 'Drama';

code| title| did| date_prod| kind| len

----+------+----+----------+------+------

BL101| The Third Man| 101| 1949-12-23| Dramatic| 01:44

P_302| Becket| 103| 1964-02-03| Dramatic| 02:28

M_401| War and Peace| 104| 1967-02-12| Dramatic| 05:57

T_601| Yojimbo| 106| 1961-06-16| Dramatic| 01:50

WITH Clause

 To use the WITH Clause when inserting:

system(admin)=> INSERT INTO emp_copy WITH employee AS (select * from

emp) SELECT * FROM employee;

 To use the WITH Clause when updating:

system(admin)=> UPDATE emp_copy SET grp = 'gone' WHERE id =

(WITH employee AS (select * from emp) SELECT id FROM employee WHERE id

= 1);

 To use the WITH Clause when deleting:

system(admin)=> DELETE FROM emp_copy WHERE id IN

(WITH employee AS (SELECT * FROM emp_copy where grp = 'gone')

SELECT id FROM employee);

Types of Joins

SELECT * FROM cows_one CROSS JOIN cows_two;

SELECT * FROM cows_one INNER JOIN cows_two ON cows_one.cnumber = cows_

two.cnumber;

SELECT * FROM cows_one LEFT OUTER JOIN cows_two ON cows_one.cnumber =

cows_two.cnumber;

SELECT * FROM cows_one RIGHT OUTER JOIN cows_two ON cows_one.cnumber =

cows_two.cnumber;

SELECT * FROM cows_one FULL OUTER JOIN cows_two ON cows_one.cnumber =

cows_two.cnumber;

Credits:

Pankaj Chahar