kenics.net

Technical notes on perl, python, php, sql, cgi, c/c++, q/kdb+, unix/shell, revision control tools, data structures & algorithms, and their applications into web services and other various forms of software engineering.

sql notes

 
most examples referenced from these wonderful turotial sites: 
(ref)  https://www.w3schools.com/sql/default.asp 
(ref)  https://www.tutorialspoint.com/sql/sql-full-joins.htm 
 
################# 
###   intro   ### 
################# 
 
server: contains DBs 
DB: contains tables 
table: consists of rows/columns 
 
RDBMS: relational DB mgmt system. (e.g. Oracle, DB2, Subase, MySQL, informix, Teradata, Greenplumn, Vertica) 
       relation == table (of columns and rows) 
       rows aka records. each row is unique within a table 
       SQL is used to query/maintain RDB 
 
SQL: structured query language 
 
####################################### 
####     basic commands/syntax     #### 
####################################### 
 
note: you can use a semi-colon to write to write two SQL cmds in sequence. 
      sometimes people just put ; at the end of each line 
 
## 
##  comment 
## 
use two hyphens. 
e.g. 
--  here is a comment 
 
## 
##  select      (distinct, count, upper, lower) 
## 
 
SELECT column_name_foo,column_name_bar FROM table_name             # slecting columns by name 
SELECT column_name_foo,hoge=column_name_bar FROM table_name        # renaming column in the output 
SELECT * FROM table_name                                           # wild card 
 
SELECT DISTINCT column_a FROM table_a            # distinct is a keyword 
SELECT COUNT(column_a) FROM table_a              # count() is a function, thus requires parenthesis 
SELECT COUNT(distinct column_a) FROM table_a 
SELECT UPPER(family_name),LOWER(first_name) FROM customer_table    # upper(), lower() 
 
 
## 
##  convert(data_type(length), expression, style) 
## 
 
sometimes default format for a given data type is not convenient. 
e.g. 
"date" type shows  "mon dd yyyy hh:miAM/PM" format, which you may wanna convert to yyyymmdd format 
 
SELECT customerID, birthday = CONVERT(varchar(8), birthday, 112)  FROM customerTable      # style=112 means yyyymmdd 
SELECT customerID, birthday = CONVERT(varchar(10), birthday, 102) FROM customerTable      # style=102 means yyyy.mm.dd 
 
(comprehensive style list) https://www.w3schools.com/sql/func_sqlserver_convert.asp 
 
> SELECT CONVERT(int, 25.67);    # similarly, you can do normal casting like this. 
25 
 
 
## 
##  where     (and, or, not) 
## 
 
SELECT * FROM table_name WHERE column_a='foo' AND NOT column_b = 'bar' 
SELECT * FROM table_name WHERE column_a='foo' AND (column_b > 5 or column_c <> 'bar') 
SELECT * FROM table_name WHERE column_a BETWEEN value_a AND value_b 
SELECT * FROM table_name WHERE column_a IN ('foo','bar','ken')        # "in" really is a simplified way of representing multiple ORs 
SELECT * FROM table_name WHERE column_a LIKE '_foo%'                  # underscore '_' is any single char, and percent "%" is zero to any number of char 
 
 
note: of course, you can negate, e.g.  "not in",  "not like" 
note: "where 1=0" is a common way to force nothing gets matched 
 
operator  description 
---------------------------- 
=         equal 
<>        not equal        # note: some version of SQL accepts "!=" too 
>         greater than 
<         less than 
>=        gt or equal to 
<=        lt or equal to 
between   inclusive range 
like      regex 
in        equal to any value from a list 
 
note: to test for NULL, you need special operators, see below. 
 
## 
##  NULL 
## 
 
note: NULL is not zero, not whitespace. it is no value. 
note: to test for NULL, you need special operators. 
e.g. 
 
SELECT column_a FROM table_a WHERE column_a IS NULL          # "is null" operator 
SELECT column_a FROM table_a WHERE column_a IS NOT NULL      # "is not null" operator 
 
 
## 
##  select as input to another select statement 
## 
 
SELECT customer_name FROM table_customer WHERE customer_country IN (SELECT country FROM table_blacklist)    # selecting customer names who are from blacklist countries 
 
NOTE: if you think about this, SELECT * from (SELECT foo,bar FROM ...) feels ok because you are all dealing with tables. but the above example can feel somewhat weird because SELECT should return a table but you are treating the output as if a country column. But it's just SQL syntactic sugar. 
 
## 
##  order by     (asc, desc) 
## 
 
SELECT * FROM table_name WHERE column_a='foo' ORDER BY column_b       # sort by "ascnending" order by default 
                                                                      # i.e.  a -> z, smaller num to bigger num 
 
SELECT * FROM table_name ORDER BY column_b ASC    # same as default 
SELECT * FROM table_name ORDER BY column_b DESC   # descending order 
 
SELECT * FROM table_name ORDER BY column_b, column_c             # sort by column_b first, then column_c 
SELECT * FROM table_name ORDER BY column_b DESC, column_c ASC    # you can specify asc, desc for each column 
 
 
## 
##  update 
## 
 
UPDATE table_name SET column_a='foo' WHERE column_a='bar'                    # usually you want to specify condition 
UPDATE table_name SET column_a='foo', column_b='ken' WHERE column_a='bar'    # you can update multiple columns like this 
 
note: notice you may update multiple records. so make sure your condition captures only the records you want. 
 
 
## 
##  delete 
## 
-- delete records 
 
DELETE FROM table_a WHERE column_a = 'foo' 
 
note: just like update, really make sure your condition captures only what you wanna delete. 
 
DELETE FROM table_a      # delete all rows from the table_a (i.e. empty the table) 
DELETE * FROM table_a    # same 
 
 
## 
##  insert into     (values) 
## 
 
INSERT INTO table_name (col_1, col_2, col_3,,, col_N) VALUES (val_1, val_2, val_3,,, valN) 
INSERT INTO table_name (col_3, col_8) VALUES (val_foo, val_bar)                              # you can insert only some columns and the rest will have NULL 
 
note: "into" and "values" are keywords 
note: if you are inserting all columns, you can skip column names. (but make sure the order of values are aligned) 
e.g. 
 
INSERT INTO table_name VALUES (val_1, val_2, val_3,,, valN) 
 
 
## 
##  top (percent), limit 
## 
 
SELECT TOP 3 * FROM table_a                        # only top 3 items. equivalent to "head" in unix 
SELECT TOP 50 PERCENT column_a FROM table_a        # you can do percentage-based top too 
 
SELECT * FROM table_a WHERE column_a = 'foo' ORDER BY column_b DESC LIMIT 3    # in MySQL syntax, use "limit" instead of "top" 
 
## 
##  max(), min(), count(), avg(), sum() 
## 
 
SELECT MAX(customer_age) FROM customer_table    # get the oldest customer age 
SELECT MIN(customer_name) FROM cusotmer_table   # works on alphabet too, just treats A smallest -> Z biggest 
SELECT COUNT(customer_ID) FROM customer_table 
SELECT AGG(customer_age) FROM customer_table WHERE customer_age IS NOT NULL 
SELECT SUM(customer_budget) FROM customer_table WHERE customer_budget IS NOT NULL 
 
note: these are called "aggregate functions" 
 
 
## 
##  alias   (as) 
## 
 
SLECT customer_name AS name, customer_country AS country FROM customer_table   # "as" keyword 
 
-- a common use case is when you have multiple tables, columns (with possibly the same names) like below 
e.g. 
 
SELECT o.orderID, o.order_date, c.customer_name FROM customer_table AS c, order_table AS o WHERE c.customerID = o.customerID 
 
note: technically, you can write the above without alias as below. 
 
SELECT order_table.orderID, order_table.order_date, customer_table.customer_name FROM customer_table, order_table WHERE customer_table.customerID = order_table.customerID 
 
 
-- another common use case is when you want to create a new column by combining existing columns 
e.g. 
SELECT customer_name, country + post_code + city + street AS address FROM customer_table 
 
 
## 
##  group by 
## 
 
note: often used together with aggregate functions. # count(), max(), min(), sum(), avg() 
 
SELECT COUNT(movie_name) AS number_of_movie,movie_country FROM movie_table GROUP BY movie_country 
 
number_of_movie  movie_country 
------------------------ 
12               France 
9                Germany 
173              USA 
18               Japan 
290              India 
.. 
.. 
 
 
-- probably you want to sort. 
 
SELECT COUNT(movie_name) AS number_of_movie,movie_country FROM movie_table GROUP BY movie_country ORDER BY count(movie_name) DESC 
 
number_of_movie  movie_country 
------------------------ 
290              India 
173              USA 
18               Japan 
12               France 
9                Germany 
.. 
.. 
 
-- maybe you only want to know the top 1 
 
SELECT TOP 1 COUNT(movie_name) AS number_of_movie,movie_country FROM movie_table GROUP BY movie_country ORDER BY count(movie_name) DESC 
 
number_of_movie  movie_country 
------------------------ 
290              India 
 
 
-- alternatively 
 
SELECT MAX(foo),movie_country FROM (SELECT COUNT(movie_name) AS foo,movie_country FROM movie_table GROUP BY movie_country) 
 
MAX(foo)  movie_country 
----------------------- 
290       India 
 
 
## 
##  having 
## 
 
note: "where" cannot be used with aggregate functions. 
    - aggr func simply means func that aggregates multiple input to a single output, e.g. count(). max(), min(), sum() 
 
SELECT COUNT(movie_name), movie_country FROM movie_table GROUP BY movie_country WHERE COUNT(movie_name) > 5      # illegal 
SELECT COUNT(movie_name), movie_country FROM movie_table GROUP BY movie_country HAVING COUNT(movie_name) > 5     # correct 
 
-- and usually you want to sort somehow, like below 
 
SELECT COUNT(movie_name), movie_country FROM movie_table GROUP BY movie_country HAVING count(movie_name) > 5 ORDER BY COUNT(movie_name) DESC 
 
-- another use case, using inner join 
 
SELECT C.customer_name, COUNT(O.orderID) AS number_of_orders FROM customer_table AS C, order_table AS O WHERE C.customerID = O.customerID GROUP BY C.customer_name HAVING count(O.orderID) > 25 
 
 
## 
##  exists 
## 
 
it tests for the existence of any record in a subquery. 
"exists" operator returns true if the subquery returns one or more records. 
 
e.g. 
-- returns name,age of whoever is older than 18 
SELECT N.name, A.age FROM name_table AS N, age_table AS A WHERE N.id = A.id AND A.age > 18 
 
-- the above SQL can be written, using exists although it feels redundant. 
SELECT N.name, A.age FROM name_table AS N, age_table AS A WHERE EXISTS(SELECT N.name FROM name_table AS N, age_table AS A WHERE N.id = A.id AND A.age > 18) 
 
 
## 
##  any, all 
## 
 
https://www.w3schools.com/sql/sql_any_all.asp 
 
 
### 
###  union 
### 
 
union combines the output from two (or more) select statements. 
 
note: generally the expectation is 
- the same number of columns from the output of each select statement. 
- same(similar enough) data types 
- same order 
 
e.g 
 
SELECT name FROM customer_table_france 
UNION 
SELECT name FROM customer_table_germany 
 
NOTE: the result applies "distinct", so to allow duplicate, you use "union all" 
e.g. 
 
SELECT name FROM customer_table_france 
UNION ALL 
SELECT name FROM customer_table_germany 
 
 
 
##################### 
####    join     #### 
##################### 
 
joining records from two tables based on matching condition. there are 4 kinds. 
 
[1] (inner) join       : intersection (i.e. records matched in both tables) 
[2] left (outer) join  : entire left table | matched vals from right table 
[3] right (outer) join : entire right table | matched vals from left table 
[4] full (outer) join  : return all records when there is a match in either left or right table 
 
 
note: nice graphics here (ref) https://www.w3schools.com/sql/sql_join.asp 
 
 
### 
###  inner join 
### 
 
SELECT o.orderID, c.customer_name FROM order_table AS o INNER JOIN customer_table AS c ON c.customerID = o.customerID 
 
-- a more elaborate use case is below: captures all orders with customer and shipper info 
 
SELECT o.orderID, c.customer_name, s.shipper_name FROM ((order_table AS o INNER JOIN customer_table AS c ON o.customerID = o.customerID) INNER JOIN shipper_table AS s ON o.shipperID = s.shipperID) 
 
NOTE: basic inner join is easy to implement without using "join" keyword 
      the following two SQL statements are identical. 
 
SELECT A.name, B.time FROM name_table AS A, time_table as B WHERE A.id = B.id 
SELECT A.name, B.time FROM name_table AS A INNER JOIN time_table AS B ON A.id = B.id 
 
 
### 
###  left join 
### 
 
note: if no match, then you simply get the left table. 
 
SELECT c.customer_name, o.orderID FROM customer_table AS c LEFT JOIN order_table AS o ON c.customerID = o.customerID 
 
 
### 
###  right join 
### 
 
-- simply s/left/right/ 
-- if no match, you get the right table. 
 
SELECT o.orderID, c.customer_name FROM order_table AS o RIGHT JOIN customer_table AS c ON o.customerID = c.customerID 
 
 
### 
###  full join 
### 
 
note: it's essentially both left and right join. 
note: this can potentially return a huge table. 
 
good example (ref) https://www.tutorialspoint.com/sql/sql-full-joins.htm 
 
SELECT c.customer_name, o.orderID FROM customer_table AS c FULL JOIN order_table AS o ON c.customerID = o.customerID 
 
===> you get everything from both tables, and matching records too. 
     e.g. obviously you get customer_name who had no order, with orderID NULL 
          you may get multiple rows of the same customer_name, with diff orderIDs 
          you may get orderIDs with no customer_name, and so on 
 
 
### 
###  self join 
### 
 
just a regular inner join done within a single table. 
e.g. 
 
SELECT DISTINCT A.customer_name FROM customer_table AS A, customer_table AS B WHERE A.customerID <> B.customerID AND A.customer_city = B.customer_city 
 
 
 
### 
###  why venn diagram visualization is bad for explaining left/right/inner/outer joins 
### 
 
venn diagram is good for illustrating concept/intuition. 
but for specific examples like below, it can be misleading. 
 
to set up an example scenario, let's imagine a public DB with 3 columns: 
(1) date 
(2) name 
(3) ranking 
 
=> this can be the world pro tennis player ranking table. the ranking among players dont change very frequently, but they do change on occasion as players participate in various tournaments and earn points. 
 
now imagine you want to build a local DB, containing the same data, but because the ranking doesnt change everyday, it is efficient to keep the following "span" structure, like below. 
 
e.g. 
 
q)show t1:([] name:`smith`ford`smith`smith`smith; ranking:11 9 13 11 4; startDate:2018.01.03 2017.12.17 2018.02.12 2018.03.02 2018.10.02; endDate:2018.02.11 2018.04.30 2018.03.01 2018.10.01 2018.10.15) 
name  ranking startDate  endDate 
----------------------------------- 
smith 11      2018.01.03 2018.02.11 
ford  9       2017.12.17 2018.04.30 
smith 13      2018.02.12 2018.03.01 
smith 11      2018.03.02 2018.10.01 
smith 4       2018.10.02 2018.10.15 
 
==> now suppose today's data comes in and you want to merge. 
 
q)show t2:([] date:2018.10.16; name:`smith`ford`osaka; ranking:11 8 9) 
date       name  ranking 
------------------------ 
2018.10.16 smith 11 
2018.10.16 ford  8 
2018.10.16 osaka 9 
 
==> how do we merge? 
 
one idea is to left-join t1 & t2, like below, so if today's data is the same as yesterday (by comparing date & endDate), then we just extend the existing span. 
 
  case (0) - extending the existing span 
 
q)show t3:t1 lj `name`ranking xkey t2 
name  ranking startDate  endDate    date 
---------------------------------------------- 
smith 11      2018.01.03 2018.02.11 2018.10.16   // extend ? - no 
ford  9       2017.12.17 2018.04.30 
smith 13      2018.02.12 2018.03.01 
smith 11      2018.03.02 2018.10.01 2018.10.16   // extend ? - no 
smith 4       2018.10.02 2018.10.15 
 
==> operation wise, it should look like this. 
 
q)show t3:delete date from update endDate:date from t3 where endDate = date-1 
name  ranking startDate  endDate 
----------------------------------- 
smith 11      2018.01.03 2018.02.11 
ford  9       2017.12.17 2018.04.30 
smith 13      2018.02.12 2018.03.01 
smith 11      2018.03.02 2018.10.01 
smith 4       2018.10.02 2018.10.15 
 
for those records from today that dont fall into case (0), we need to create a new span. there are two cases to this. 
-  case (1): data is brand new combination of `name`ranking, so we just create a new span with startDate:date, endDate:date 
-  case (2): this combination of data was seen before, but for an older span, so we create a new separate span. (again, with startDate:date, endDate:date) 
 
===> case (1) is easy to detect by right-join t1 to t2, and by checking if any existing startDate-endDate span for today's data. 
===> case (2) is also possible to detect if any existing record for today's data by right-join t1 to t2, and if any endDate = date-1 
 
e.g.  right-join t1 to t2 
 
q)t2 lj `name`ranking xkey t1 
date       name  ranking startDate  endDate       // lets call this table result A 
---------------------------------------------- 
2018.10.16 smith 11      2018.01.03 2018.02.11    // case (2) ? - yes looks like it. but we need to see all matching records :-( 
2018.10.16 ford  8                                // case (1) 
2018.10.16 osaka 9                                // case (1) 
 
===> and yes, we may end up creating a new span AND extending the existing span for `name=`smith, `ranking=11 record. 
     what happened ? 
     did you expect to see the below table instead ? (so that we can do a proper comparison of endDate & date for every relevant matching record) 
 
q)t4 
name  ranking startDate  endDate    date          // lets call this table result B 
---------------------------------------------- 
smith 11      2018.01.03 2018.02.11 2018.10.16    // case (2) ? - yes 
smith 11      2018.03.02 2018.10.01 2018.10.16    // case (2) ? - yes 
ford  8                             2018.10.16    // case (1) 
osaka 9                             2018.10.16    // case (1) 
 
===> we really need result B. 
 
     this is where venn-diagram confuses you. it looks like right-join gives you result B, but in reality gives you result A. 
     how do we get result B ? 
     it's outer-join with a bit of filter. 
 
e.g  outer-join 
 
q)select from oj[t1;t2;`name`ranking] where not null date 
name  ranking startDate  endDate    date 
----------------------------------------------    // yay !  this is result B 
smith 11      2018.01.03 2018.02.11 2018.10.16 
smith 11      2018.03.02 2018.10.01 2018.10.16 
ford  8                             2018.10.16 
osaka 9                             2018.10.16 
 
 
===> the rest is only FYI, illustrating how we can complete our scenario. 
 
e.g. checking case (2) 
 
q)show t4:update maxED:max endDate by name,ranking from t4 where not null endDate 
name  ranking startDate  endDate    date       maxED 
--------------------------------------------------------- 
smith 11      2018.01.03 2018.02.11 2018.10.16 2018.10.01 
smith 11      2018.03.02 2018.10.01 2018.10.16 2018.10.01 
ford  8                             2018.10.16 
osaka 9                             2018.10.16 
 
q)update startDate:date,endDate:date from t4 where not null maxED, maxED <> date-1 
name  ranking startDate  endDate    date       maxED 
--------------------------------------------------------- 
smith 11      2018.10.16 2018.10.16 2018.10.16 2018.10.01   // successfully updated span for case (2) 
smith 11      2018.10.16 2018.10.16 2018.10.16 2018.10.01   // successfully updated span for case (2) 
ford  8                             2018.10.16 
osaka 9                             2018.10.16 
 
the rest is trivial for updating case (1) 
 
q)show t4:distinct delete maxED,date from update startDate:date,endDate:date from t4 where null startDate 
name  ranking startDate  endDate 
----------------------------------- 
smith 11      2018.10.16 2018.10.16 
ford  8       2018.10.16 2018.10.16 
osaka 9       2018.10.16 2018.10.16 
 
 
==> finally we can merge them all. i.e. existing records plus case 0,1,2 
 
q)distinct t3,t4 
name  ranking startDate  endDate 
----------------------------------- 
smith 11      2018.01.03 2018.02.11  // potential case (0) but endDate was not date-1 
ford  9       2017.12.17 2018.04.30 
smith 13      2018.02.12 2018.03.01 
smith 11      2018.03.02 2018.10.01  // potential case (0) but endDate was not date-1 
smith 4       2018.10.02 2018.10.15 
smith 11      2018.10.16 2018.10.16  // new - case (2) 
ford  8       2018.10.16 2018.10.16  // new - case (1) 
osaka 9       2018.10.16 2018.10.16  // new - case (1) 
 
 
NOTE: a simpler alternative is to just add today's data with startDate:date, endDate:date, then run a spanMerge function that take care of converting case (2) into case (0) 
 
 
 
################################## 
####     table operations     #### 
################################## 
 
## 
##  select into 
## 
 
-- for creating a new table out of an existing ones. 
e.g. 
 
SELECT * INTO customer_table_bkup FROM customer_table                     # taking a backup 
SELECT * INTO customer_table_bkup IN 'backup.mdp' FROM customer_table     # taking a backup in other DB named 'backup.mdp' 
 
SELECT id,name,address INTO customer_contact_table FROM customer_table    # creating a subset table by specifying columns 
SELECT * INTO customer_france_table FROM customer_table WHERE country='france'   # by specifying conditions 
 
-- you can combine tables into one 
SELECT C.name, O.orderID INTO customer_order_table FROM customer_table AS C, order_table AS O WHERE C.id = O.customerID 
 
-- you can create an empty table, inheriting all the schema (header) 
SELECT * INTO customer_new_table HWERE customer_table WHERE 1=0 
 
 
## 
##  insert into select 
## 
 
-- often what you wanna do is create a temp table (probably using select into) and do some manipulation, and also create another backup table, then delete some records in the original table, and insert the new records. 
-- insert into select lets you add records to an existing table 
 
SELECT * INTO french_customer_table_backup FROM customer_table WHERE country='france'    # backup 
SELECT * INTO french_customer_table_tmp FROM customer_table WHERE country='france'       # tmp 
-- do some manipulation on the tmp table 
DELETE FROM customer_table WHERE country='france' 
INSERT INTO customer_table SELECT * FROM french_customer_table_tmp 
INSERT INTO customer_table SELECT * FROM french_customer_table_tmp WHERE age > 25   # if you only insert back age>25 people 
 
-- or maybe you have ticker records with some start_date, end_date spanning, and you may simply split somespan, etc 
-- so you don't really delete anything, but probably just update the existing record with some span, then insert into select" another record with other span, etc 
 
 
## 
##  temporary table 
## 
 
-- you can specify a hash prefix "#" to a table name, then it gets created in DB called "tempdb" 
-- and your temp table gets deleted after your session ends. 
SELECT * INTO #france_customer_table_tmp FROM customer_table WHERE country = 'france' 
 
 
## 
##  create table 
## 
 
-- often "select into" lets you do it quicker. 
-- but if you are in the mood to create from scratch. 
-- note "primary key" is a very important notion 
 
CREATE TABLE customer_table(             # parenthesis 
   customerID int primary key,           # "primary key" denotes a unique identifier 
   customer_name varchar(255) not null,  # "not null" let you specify it cannot be NULL 
   customer_register_date date, 
   customer_country varchar(20) not null,  # varchar(n) = variable length of max n 
   customer_country char(3) not null,      # char(n) = char of length precisely n 
   customer_disability_discount boolean, 
   customer_budget float(8),    # 8 digits total, not decimal length 
); 
 
 
note: you can specify a combination of column names as a unique key 
note: you'll often hear the term DDL (data definition language) 
 
 
## 
##  <datatype>            (ref) https://www.w3schools.com/sql/sql_datatypes.asp 
## 
 
char(size)          # fixed length 
varchar(size)       # variable length 
 
int 
float(size)       # size = 4 or 8 bytes 
 
date 
 
time 
datetime 
 
 
## 
##  create index (on a table) 
## 
 
 
CREATE UNIQUE CLUSTERED INDEX idx_foo ON <table_name> (col_name1, col_name2, col_name3, ...); 
 
note: 
- 'INDEX' tells SQL to index records by those specified columns, making it fast for searches/queries by users. (apparently B-tree is used) 
-- but it also means, updating a table with indexes takes longer than a table without index, because of the overhead cost of updating index. 
-- so use index for tables where query frequency is far greater than update. 
- you can create multiple indexes, thought it's not common. 
- 'UNIQUE' and 'CLUSTERED' are optional. 
-- 'UNIQUE' tells values for the indexed columns must be unique. 
-- so index can be either clustered or non-clustered. 
--- clustered means rows are sorted by indexed column values. (as such, there can be only one clustered index in a table). also, once you cluster index in a table, that table is called clsutered table. 
--- non-clustered means rows are not sorted. (default, and the reason why SQL is slow) 
 
note: to drop index, 
- DROP INDEX table_name.index_name 
 

#  using index 

 
-- MySQL 
SELECT date,ticker FROM some_table USE INDEX(idx_1, idx_2) WHERE date >= '20180509'      # you may see WITH instead of USE 
-- Sybase 
SELECT date,ticker FROM some_table INDEX(idx_1 MRU) WHERE date >= '20180509'    # MRU or LRU, both are optional 
 
NOTE: it looks like you can only specify one index in Sybase, in other words, you must have exactly an index that indexes a combination of columns you want. so you may have something like below. 
idx_1  -- indexed on updated_timestamp column 
idx_2  -- indexed on ticker_name column 
idx_3  -- indexed on a combo of updated_timestamp and ticker_name columns 
 
 
## 
##  alter table 
## 
-- to add/delete/modify columns in an existing table. 
 
ALTER TABLE table_name ADD col_name <datatype> 
ALTER TABLE table_name DROP col_name 
ALTER TABLE table_name ALTER COLUMN col_name <datatype>      # modify existing column datatype 
 
 
## 
##  drop table 
## 
 
-- "drop table" 
DROP TABLE customer_table_tmp      # obviously always be careful, as this delets an entire table completely. 
 
 
## 
##  create database 
## 
 
CREATE DATABASE fooDB     # "create database" operator 
USE fooDB                 # "use" lets you switch to the DB 
 
## 
##  drop databse 
## 
 
DROP DATABASE fooDB      # "drop database" operator 
                         # be careful, as it completely delets DB 
 
 
## 
##  AT ISOLATION 
## 
 
SELECT * FROM table_name WHERE ticker = @tickerName AT ISOLATION 0 
 
isolation level 
  0   # read uncommitted (i.e. just read whatever, including uncommitted changes. we are ok to get dirty data) 
  1   # read commited (i.e. wait for locks to be released) 
  2   # hold shared locks until transaction completes 
  3   # hold shared locks until transaction completes 
 
 
##################################### 
####    stored procedure (SQ)    #### 
##################################### 
 
sp_help                          # show a list of SPs 
sp_help <table_name|SP_name>     # shows schema, definition 
sp_helpuser <username>           # user info, entitlement, etc 
sp_depends <table_name>          # shows you what SP depends on the table 
sp_helptext <SP_name>            # shows SP definition 
sp_helpindex <table_name>        # list indices of a table 
sp_tables                        # list tables in a DB 
 
note: stored procedures you can define with DDL, and usually stored in .sql files. 
 
e.g. 
------------------//  sampleSP.sql 
USE <db_name>                               # you put actual DB name here 
GO 
CREATE PROCEDURE getNames @city nvarchar(20)         # notice the syntax, the input parameters are denoted with "@" 
AS 
SELECT * FROM <table_name> WHERE city = @city        # you put an actual table_name like "customer" etc 
GO 
------------------- 
then you invoke the SP as:  "EXEC getNames @city = 'NewYork'" 
 
now you can get more elaborate. (use multiple input params, set a default value for each param, and use "like" instead of "=", etc) 
 
e.g. 
----------------// 
USE <db_name> 
GO 
CREATE PROCEDURE getCustomers @city nvarchar(20) = NULL, @addr nvarchar(55) = NULL 
AS 
SELECT * FROM <table_name> WHERE city = ISNULL(@city,city) 
AND addr LIKE '%' + ISNULL(@addr,addr) + '%' 
GO 
---------------- 
 
then you can invoke any number of ways. 
 
"EXEC getCustomers @city = 'NewYork', @addr = 'Broadway'"       # notice a comma separator 
"EXEC getCustomers @addr = 'Broadway'"                          # because we have a default value handling in this case, we don't have to specify every param. 
 
 
(good ref) https://www.mssqltips.com/sqlservertutorial/162/how-to-create-a-sql-server-stored-procedure-with-parameters/ 
 
 
######################### 
####       misc      #### 
######################### 
 
### 
###  how to list all tables 
### 
 
USE db_name 
SELECT * FROM sys.tables 
 
sp_tables 
 

  1. 2016-10-02 15:57:24 |
  2. Category : misc
  3. Page View:

Google Ads