SQL Server
SQL / PostgreSQL
Basics:
- Data types:
- INT : whole number
- VARCHAR(number_characters)
- DATE : yyy-mm-dd
- BLOB: binary large object to store large data
- TIMESTAMP: yyyy-mm-dd hh:mm-ss
- Compare Arrays
1. Equality operators (=, < >) do an exact element-by-element comparison
select
array[1, 2, 3] = array[1, 2, 4] as compare1, -- array are equal
array[1, 2, 3] < > = array[1, 2, 4] as compare2; --array are not equal
- Compare1 is false.
- Comapre2 is True.
2. Ordering operators (>, <, >=, <=) also compare each element in an array in order. Result are based on the first different pair of element, not the size of the array.
select
array[1, 2, 5] >= array[1, 2, 4] as comapre1, -- True
array[1, 2, 5] <= array[1, 2, 4, 5] as compare2; -- False
3. Containment operators (@>, <@) called "bird Operators". An array is said to be contained in another array if each of its unique elements is also present in the other array.
select array['a', 'b', 'c'] @> array['a', 'b', 'b', 'a'] as contains; -- contains True
select array[1, 1, 4] <@ array[4, 3, 2, 1] as is_contained_by; -- is_contained_by True
4. Overlap Operator (&&) called Overlapping array to check if two arrays:
select
array[1, 2] && array[2, 3] as overlap1, -- True
array[1, 2] && array[3, 4] as overlap; -- False
- Concatenate Strings
1. Concatenate strings (||) operator offers 2 ways concatenate
select 'Join these' || 'string with a number' || 23;
==> result: Joint these strings with a number 23
select first_name || ' ' || last_name as customer_name from customer limit 2;
==> customer_name
MARY SMITH
PATRICIA JHNSON
2. One disadvantage of using the || operator is a null value in any of the columns being joined together will result in a null value.
select 'Null with ||' || ' will make ' || everything disappear' || null;
==> result:
3. Using concat() will transform the nulls into empty strings when concatenating:
select concat('Concat() handles', null, 'nulls better', null);
==> result: concat
Concat() handles nulls nulls beter
4. concat() accept multiple parameters, separated by comas.
- Convert The Case of a Strings
1. Function are lower(), upper(), initcap()
select lower('Turn THis to LOWER'); -- ==> result: turn this to lower
select upper('Turn to upper'); -- ==> result: TURN TO UPPER
select
first_name,
last_name,
initcap(concat(first_naem, ' ', last_name)) as name
from customer limit 5;
==> result:
- Create an Array
An array is a single data object that holds multiple values.
- INSERT data:
=> INSERT INTO table_name VALUES (v1, v2, ....);
- UPDATE data:
=> UPDATE table_name
SET name = ' '
WHERE id = 5;
- DELETE data
=> DELETE from table_name WHERE id = 10;
Database Management in PopSQL
- Create table
Example: table name: users, have info: id, name, date
CREATE TABLE users(
id INT PRIMARY KEY,
name VARCHAR(40),
date DATE
);
- Create temporary table
Example: table name: active_users, info: id, name, active
CREATE TABLE #active_users(
id INT PRIMARY KEY,
name VARCHAR(40),
active bit default 1
);
==> When we want to use table (by some info):
SELECT id, name INTO #active_users FROM users WHERE active = 1;
- DROP table:
DROP TABLE table_name;
- Rename table
1. ALTER TABLE old_table_name RENAME new_table_name;
or
2. RENAME TABLE old_table_name TO new_table_name;
- Duplicate table
1. Copy entire table structure along with particular data set:
SELECT * INTO users_1 FROM users WHERE domain = 1;
2. Copy only particular columns into new table along with particular data set:
SELECT id, username, password INTO users_1 FROM users WHERE domain = 1;
3. Copy only particular columns from mre tables into new table along with particular data set:
SELECT
u.username,
s.start_date as session_start_date,
s.end_date as session_end_date
INTO users_sessions_1_rpt
FROM sessions s
LEFT JION user_sessions us ON s.id = us.session_id
LEFT JOIN users u ON us.user_id = u.id
WHERE u.domain = 1;
4. Copy only table structure, No data copying:
SELECT * INTO users_1 FROM users WHERE 1 = 0;
- ADD a Column
1. Adding a brand_id smallint column:
ALTER TABLE products
ADD brand_id smallint; -- or ADD brand_id smallint default 1;
2. Adding a string (VARCHAR) column with a not null contraint:
ALTER TABLE products
ADD
brand_id smallint default 1,
description VARCHAR(100) NOT NULL;
DROP a Column
Comments
Post a Comment