Home » Articole » RO » Calculatoare » Programare » SQL » SQL: Exemple de snippet

# SQL: Exemple de snippet

postat în: SQL

În acest articol sunt exemple SQL referitoare la diferite subiecte. Puteți folosi exemplele în funcție de nevoile dvs.

## CREATETABLE

### Data type

```--
-- Frequently used data types and simple constraints
CREATE TABLE t_standard (
-- column name   data type     default        nullable/constraint
id               DECIMAL                      PRIMARY KEY,  -- some prefer the name: 'sid'
col_1            VARCHAR(50)   DEFAULT 'n/a'  NOT NULL,     -- string with variable length. Oracle: 'VARCHAR2'
col_2            CHAR(10),                                  -- string with fixed length
col_3            DECIMAL(10,2) DEFAULT 0.0,                 -- 8 digits before and 2 after the decimal. Signed.
col_4            NUMERIC(10,2) DEFAULT 0.0,                 -- same as col_3
col_5            INTEGER,
col_6            BIGINT                                     -- Oracle: use 'NUMBER(n)', n up to 38
);

-- Data types with temporal aspects
CREATE TABLE t_temporal (
-- column name   data type     default  nullable/constraint
id               DECIMAL                PRIMARY KEY,
col_1            DATE,                                -- Oracle: contains day and time, seconds without decimal
col_2            TIME,                                -- Oracle: use 'DATE' and pick time-part
col_3            TIMESTAMP,                           -- Including decimal for seconds
col_4            TIMESTAMP WITH TIME ZONE,            -- MySql: no time zone
col_5            INTERVAL YEAR TO MONTH,
col_6            INTERVAL DAY TO SECOND
);

CREATE TABLE t_misc (
-- column name   data type     default  nullable/constraint
id               DECIMAL                PRIMARY KEY,
col_1            CLOB,                                -- very long string (MySql: LONGTEXT)
col_2            BLOB,                                -- binary, eg: Word document or mp3-stream
col_3            FLOAT(6),                            -- example: two-thirds (2/3).
col_4            REAL,
col_5            DOUBLE PRECISION,
col_6            BOOLEAN,                             -- Oracle: Not supported
col_7            XML                                  -- Oracle: 'XMLType'
);
```

### CONSTRAINT

```--
-- Denominate all constraints with an expressive name, eg.: abbreviations for
-- table name (unique across all tables in your schema), column name, constraint type, running number.
--
CREATE TABLE myExampleTable (
id               DECIMAL,
col_1            DECIMAL(1),   -- only 1 (signed) digit
col_2            VARCHAR(50),
col_3            VARCHAR(90),
CONSTRAINT example_pk           PRIMARY KEY (id),
CONSTRAINT example_uniq         UNIQUE (col_2),
CONSTRAINT example_fk           FOREIGN KEY (col_1) REFERENCES person(id),
CONSTRAINT example_col_1_nn     CHECK (col_1 IS NOT NULL),
CONSTRAINT example_col_1_check  CHECK (col_1 >=0 AND col_1 < 6),
CONSTRAINT example_col_2_nn     CHECK (col_2 IS NOT NULL),
CONSTRAINT example_check_1      CHECK (LENGTH(col_2) > 3),
CONSTRAINT example_check_2      CHECK (LENGTH(col_2) < LENGTH(col_3))
);
```

### FOREIGNKEY

```--
-- Reference to a different (or the same) table. This creates 1:m or n:m relationships.
CREATE TABLE t_hierarchie (
id               DECIMAL,
part_name        VARCHAR(50),
super_part_id    DECIMAL,      -- ID of the part which contains this part
CONSTRAINT hier_pk             PRIMARY KEY (id),
-- In this special case the foreign key refers to the same table
CONSTRAINT hier_fk             FOREIGN KEY (super_part_id) REFERENCES t_hierarchie(id)
);

-- -----------------------------------------------
-- n:m relationships
-- -----------------------------------------------
CREATE TABLE t1 (
id               DECIMAL,
name             VARCHAR(50),
-- ...
CONSTRAINT t1_pk               PRIMARY KEY (id)
);
CREATE TABLE t2 (
id               DECIMAL,
name             VARCHAR(50),
-- ...
CONSTRAINT t2_pk               PRIMARY KEY (id)
);
CREATE TABLE t1_t2 (
id               DECIMAL,
t1_id            DECIMAL,
t2_id            DECIMAL,
CONSTRAINT t1_t2_pk            PRIMARY KEY (id),       -- also this table should have its own Primary Key
CONSTRAINT t1_t2_unique        UNIQUE (t1_id, t2_id),  -- every link should occur only once
CONSTRAINT t1_t2_fk_1          FOREIGN KEY (t1_id) REFERENCES t1(id),
CONSTRAINT t1_t2_fk_2          FOREIGN KEY (t2_id) REFERENCES t2(id)
);

-- -----------------------------------------------------------------------------------
-- ON DELETE / ON UPDATE / DEFFERABLE
-- -----------------------------------------------------------------------------------
-- DELETE and UPDATE behaviour for child tables (see first example)
-- Oracle: Only DELETE [CASCADE | SET NULL] is possible. Default is NO ACTION, but this cannot be
--         specified explicit - just omit the phrase.
CONSTRAINT hier_fk             FOREIGN KEY (super_part_id) REFERENCES t_hierarchie(id)
ON DELETE CASCADE  -- or: NO ACTION (the default), RESTRICT, SET NULL, SET DEFAULT
ON UPDATE CASCADE  -- or: NO ACTION (the default), RESTRICT, SET NULL, SET DEFAULT

-- Initial stage: immediate vs. deferred, [not] deferrable
-- MySQL: DEFERABLE is not supported
CONSTRAINT t1_t2_fk_1          FOREIGN KEY (t1_id) REFERENCES t1(id)
INITIALLY IMMEDIATE DEFERRABLE

-- Change constraint characteristics at a later stage
SET CONSTRAINT hier_fk DEFERRED; -- or: IMMEDIATE
```

## ALTERTABLE

În ceea ce privește coloanele.

```-- Add a column (plus some column constraints). Oracle: The key word 'COLUMN' is not allowed.
ALTER TABLE t1 ADD COLUMN col_1 VARCHAR(100) CHECK (LENGTH(col_1) > 5);

-- Change a columns characteristic. (Some implementations use different key words like 'MODIFY'.)
ALTER TABLE t1 ALTER COLUMN col_1 SET DATA TYPE NUMERIC;
ALTER TABLE t1 ALTER COLUMN col_1 SET SET DEFAULT -1;
ALTER TABLE t1 ALTER COLUMN col_1 SET NOT NULL;
ALTER TABLE t1 ALTER COLUMN col_1 DROP NOT NULL;

-- Drop a column. Oracle: The key word 'COLUMN' is mandatory.
ALTER TABLE t1 DROP COLUMN col_2;
```

Concerning complete table.

```--
ALTER TABLE t1 ADD CONSTRAINT t1_col_1_uniq UNIQUE (col_1);
ALTER TABLE t1 ADD CONSTRAINT t1_col_2_fk FOREIGN KEY (col_2) REFERENCES person (id);

-- Change definitions. Some implementations use different key words like 'MODIFY'.
ALTER TABLE t1 ALTER CONSTRAINT t1_col_1_unique UNIQUE (col_1);

-- Drop a constraint. You need to know its name. Not supported by MySQL, there is only a 'DROP FOREIGN KEY'.
ALTER TABLE t1 DROP CONSTRAINT t1_col_1_unique;
-- As an extension to the SQL standard, some implementations offer an ENABLE / DISABLE command for constraints.
```

## DROPTABLE

```--
-- All data and complete table structure inclusive indices are thrown away.
-- No column name. No WHERE clause. No trigger is fired. Considers Foreign Keys. Very fast.
DROP TABLE t1;
```

## SELECT

### Sintaxa de vază

```--
-- Overall structure: SELECT / FROM / WHERE / GROUP BY / HAVING / ORDER BY

-- constants, column values, operators, functions
SELECT 'ID: ', id, col_1 + col_2, sqrt(col_2)
FROM   t1
-- precedence within WHERE: functions, comparisions, NOT, AND, OR
WHERE  col_1 > 100
AND    NOT MOD(col_2, 10) = 0
OR     col_3 < col_1
ORDER  BY col_4 DESC, col_5; -- sort ascending (the default) or descending

-- number of rows, number of not-null-values
SELECT COUNT(*), COUNT(col_1) FROM t1;

-- predefined functions
SELECT COUNT(col_1), MAX(col_1), MIN(col_1), AVG(col_1), SUM(col_1) FROM t1;

-- UNIQUE values only
SELECT DISTINCT col_1 FROM t1;

-- In the next example col_1 many have duplicates. Only the combination of col_1 plus col_2 is unique.
SELECT DISTINCT col_1, col_2 FROM t1;
```

### CASE

```--
-- CASE expression with conditions on exactly ONE column
SELECT id,
CASE contact_type   -- ONE column name
WHEN 'fixed line' THEN 'Phone'
WHEN 'mobile'     THEN 'Phone'
ELSE                   'Not a telephone number'
END,
contact_value
FROM   contact;

-- CASE expression with conditions on ANY column
SELECT id,
CASE   -- NO column name
WHEN contact_type IN ('fixed line', 'mobile')  THEN 'Phone'
WHEN id = 4                                    THEN 'ICQ'
ELSE                                                'Something else'
END,
contact_value
FROM   contact;
```

### GROUPING

```--
SELECT product_group, count(*) AS cnt
FROM   sales
WHERE  region = 'west'  -- additional restrictions are possible but not necessary
GROUP  BY product_group -- 'product_group' is the criterion which creates groups
HAVING COUNT(*) > 1000  -- restriction to groups with more than 1000 sales per group
ORDER  BY cnt;

-- Attention: in the next example, col_2 is not part of the GROUP BY criterion. Therefore it cannot be displayed.
SELECT col_1, col_2
FROM   t1
GROUP  BY col_1;

-- We must accumulate all col_2-values of each group to ONE value, eg:
SELECT col_1, sum(col_2), min(col_2)
FROM   t1
GROUP  BY col_1;
```

### JOIN

```--
-- Inner join: Only persons together with their contacts.
-- Ignores all persons without contacts and all contacts without persons
SELECT *
FROM   person p
JOIN   contact c ON p.id = c.person_id;

-- Left outer join: ALL persons. Ignores contacts without persons
SELECT *
FROM   person p
LEFT   JOIN contact c ON p.id = c.person_id;

-- Right outer join: ALL contacts. Ignores persons without contacts
SELECT *
FROM   person p
RIGHT  JOIN contact c ON p.id = c.person_id;

-- Full outer join: ALL persons. ALL contacts.
SELECT *
FROM   person p
FULL   JOIN contact c ON p.id = c.person_id;

-- Carthesian product (missing ON keyword): be carefull!
SELECT COUNT(*)
FROM   person p
JOIN   contact c;
```

### Subquery

```--
-- Subquery within SELECT clause
SELECT id,
lastname,
weight,
(SELECT avg(weight) FROM person)    -- the subquery
FROM   person;

-- Subquery within WHERE clause
SELECT id,
lastname,
weight
FROM   person
WHERE  weight < (SELECT avg(weight) FROM person) -- the subquery
;

-- CORRELATED subquery within SELECT clause
SELECT id,
(SELECT status_name FROM status st WHERE st.id = sa.state)
FROM   sales sa;

-- CORRELATED subquery retrieving the highest version within each booking_number
SELECT *
FROM   booking b
WHERE  version =
(SELECT MAX(version) FROM booking sq WHERE sq.booking_number = b.booking_number)
;
```

### Setare operații

```--
-- UNION
SELECT firstname  -- first SELECT command
FROM   person
UNION           -- push both intermediate results together to one result
SELECT lastname   -- second SELECT command
FROM   person;

-- Default behaviour is: 'UNION DISTINCT'. 'UNION ALL' must be explicitly specified, if duplicate values shall be removed.

-- INTERSECT: resulting values must be in BOTH intermediate results
SELECT firstname FROM person
INTERSECT
SELECT lastname FROM person;

-- EXCEPT: resulting values must be in the first but not in the second intermediate result
SELECT firstname FROM person
EXCEPT   -- Oracle uses 'MINUS'. MySQL does not support EXCEPT.
SELECT lastname FROM person;
```

### ROLLUP/CUBE

```-- Additional sum per group and sub-group
SELECT SUM(col_x), ...
FROM   ...
GROUP  BY ROLLUP (producer, model); -- the MySQL syntax is: GROUP BY producer, model WITH ROLLUP

-- Additional sum per EVERY combination of the grouping columns
SELECT SUM(col_x), ...
FROM   ...
GROUP  BY CUBE (producer, model); -- not supported by MySQL
```

### Funcții analitice

```-- The frames boundaries
SELECT id,
emp_name,
dep_name,
FIRST_VALUE(id) OVER (PARTITION BY dep_name ORDER BY id) AS frame_first_row,
LAST_VALUE(id)  OVER (PARTITION BY dep_name ORDER BY id) AS frame_last_row,
COUNT(*)        OVER (PARTITION BY dep_name ORDER BY id) AS frame_count,
LAG(id)         OVER (PARTITION BY dep_name ORDER BY id) AS prev_row,
LEAD(id)        OVER (PARTITION BY dep_name ORDER BY id) AS next_row
FROM   employee;

-- The moving average
SELECT id, dep_name, salary,
AVG(salary)     OVER (PARTITION BY dep_name ORDER BY salary
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS sum_over_1or2or3_rows
FROM employee;
```

### Recursiuni

```-- The 'with clause' consists of three parts:
-- First: arbitrary name of an intermediate table and its columns
WITH intermediate_table (id, firstname, lastname) AS
(
-- Second: starting row (or rows)
SELECT id, firstname, lastname
FROM   family_tree
WHERE  firstname = 'Karl'
AND    lastname  = 'Miller'
UNION ALL
-- Third: Definition of the rule for querying the next level. In most cases this is done with a join operation.
SELECT f.id, f.firstname, f.lastname
FROM   intermediate_table i
JOIN   family_tree f ON f.father_id = i.id
)

-- After the 'with clause': depth first / breadth first
-- SEARCH BREADTH FIRST BY firstname SET sequence_number (default behaviour)
-- SEARCH DEPTH FIRST BY firstname SET sequence_number

-- The final SELECT
SELECT * FROM intermediate_table;

-- Hints: Oracle supports the syntax of the SQL standard since version 11.2. .
-- MySQL does not support recursions at all and recommend procedural workarounds.
```

## INSERT

```--
-- fix list of values/rows
INSERT INTO t1 (id, col_1, col_2) VALUES (6, 46, 'abc');
INSERT INTO t1 (id, col_1, col_2) VALUES (7, 47, 'abc7'),
(8, 48, 'abc8'),
(9, 49, 'abc9');
COMMIT;

-- subselect: leads to 0, 1 or more new rows
INSERT INTO t1 (id, col_1, col_2)
SELECT id, col_x, col_y
FROM   t2
WHERE  col_y > 100;
COMMIT;

-- dynamic values
INSERT INTO t1 (id, col_1, col_2) VALUES (16, CURRENT_DATE, 'abc');
COMMIT;

INSERT INTO t1 (id, col_1, col_2)
SELECT id,
CASE
WHEN col_x < 40 THEN col_x + 10
ELSE                 col_x +  5
END,
col_y
FROM   t2
WHERE  col_y > 100;
COMMIT;
```

## UPDATE

```--
-- basic syntax
UPDATE t1
SET col_1 = 'Jimmy Walker',
col_2 = 4711
WHERE id = 5;

-- raise value of col_2 by factor 2; no WHERE ==> all rows!
UPDATE t1 SET col_2 = col_2 * 2;

-- non-correlated subquery leads to one single evaluation of the subquery
UPDATE t1 SET col_2 = (SELECT max(id) FROM t1);

-- correlated subquery leads to one evaluation of subquery for EVERY affected row of outer query
UPDATE t1 SET col_2 = (SELECT col_2 FROM t2 where t1.id = t2.id);

-- Subquery in WHERE clause
UPDATE article
SET col_1 = 'topseller'
WHERE id IN
(SELECT article_id
FROM   sales
GROUP BY article_id
HAVING COUNT(*) > 1000
);
```

## MERGE

```--
-- INSERT / UPDATE depending on any criterion, in this case: the two columns 'id'
MERGE INTO hobby_shadow                     t   -- the target table
USING (SELECT id, hobbyname, remark
FROM   hobby
WHERE  id < 8)                 s   -- the source
ON    (t.id = s.id)                       -- the 'match criterion'
WHEN MATCHED THEN
UPDATE SET remark = concat(s.remark, ' Merge / Update')
WHEN NOT MATCHED THEN
INSERT (id, hobbyname, remark) VALUES (s.id, s.hobbyname, concat(s.remark, ' Merge / Insert'))
;

-- Independent from the number of affected rows there is only ONE round trip between client and DBMS
```

## DELETE

```--
-- Basic syntax
DELETE FROM t1 WHERE id = 5; -- no column name behind 'DELETE' key word because the complete row will be deleted

-- no hit is OK
DELETE FROM t1 WHERE id != id;

-- subquery
DELETE FROM person_hobby
WHERE person_id IN
(SELECT id
FROM   person
WHERE  lastname = 'Goldstein'
);
```

## TRUNCATE

```--
-- TRUNCATE deletes ALL rows (WHERE clause is not possible). The table structure remains.
-- No trigger actions will be fired. Foreign Keys are considered. Much faster than DELETE.
TRUNCATE TABLE t1;
```

##### Telelucru (Telework)

Telelucrul, ca un nou mod de a lucra prin efectuarea unei activităţi (forme de muncă) flexibile în timp şi la distanţă, utilizând tehnologia informaţională şi comunicaţiile avansate, se concretizează în teleactivităţi şi teleservicii. În ultimii ani, s-au dezvoltat rapid noi … Citeşte mai mult

Nu a fost votat \$0,00
##### Ghid WordPress pentru începători

WordPress combină simplitatea pentru utilizatorii şi editori cu complexitate suportului software pentru dezvoltatori. Acest lucru îl face mai flexibil, fiind în acelaşi timp uşor de utilizat. Simplitatea sa face posibilă instalarea şi publicarea online rapid. Nimic nu ar trebui să … Citeşte mai mult

Nu a fost votat \$2,99
##### Facebook pentru afaceri şi utilizatori

Ediția a doua (revăzută și îmbunătățită) Un ghid pentru dezvoltarea profilului propriu al utilizatorilor Facebook, şi pentru promovarea imaginii, produselor şi serviciilor firmei prin paginile de Facebook. Include volumele Ghid Facebook pentru utilizatori și Ghid pagini Facebook – Campanii de … Citeşte mai mult

Nu a fost votat \$4,99\$7,18