Using Select Statements
Select
- Select is used to extract a subset of data from a table
- general form
select [column/s] [by columns] from table_name where [condition/s] - select statement is executed in order:
- from table_name
- where [condition/s]
- [by columns]
-
select [columns]
-
example table:
q)sales:([]fruit:10?`apple`banana`orange;grocer:10?`ram`krishna`laxman;price:10?10;qty:10?100) / sales table is defined q)sales fruit grocer price qty ------------------------ orange krishna 6 12 apple laxman 6 10 apple krishna 1 1 orange laxman 8 90 apple krishna 5 73 banana ram 4 90 apple ram 9 43 apple krishna 2 90 apple laxman 7 84 orange krishna 0 63 select from table_name- in select if no columns are queried - it will select all columns from the table
- in its basic format -
fromis only required condition, all other(by, where, select) are optional select column/s from table_name- select columns separated by
, - even if single column is asked return type is table
select column/s from table_name where condition/sselect column/s by column/s from table_name- grouping data by column/s
- we can apply aggregation to grouped data -
select aggregate_function column/s by column/s from table_name -
if we don't query any select columns then all the columns are grouped by
bycolumn/s andlastaggregate_function is applied by default -
select[+/- n] from table_name - returns n rows from start
- if n < 0; returns n rows from end of table
-
same can be applied to result set after the select is performed
n sublist select from table_name -
select[m n] from table_name - returns
nrows starting from indexm -
same can be applied to result set after select is performed
m n sublist select from table_name -
select[ column/s] from table_name <means ascending order>means descending order- same can be done using functions
xascandxdescto sort the result table into ascending or descending order respectively -column/s xasc/xdesc select from table_nameq)`grocer xasc select from sales fruit grocer price qty ------------------------ orange krishna 6 12 apple krishna 1 1 apple krishna 5 73 apple krishna 2 90 orange krishna 0 63 apple laxman 6 10 orange laxman 8 90 apple laxman 7 84 banana ram 4 90 apple ram 9 43 q)`grocer xdesc select from sales fruit grocer price qty ------------------------ banana ram 4 90 apple ram 9 43 apple laxman 6 10 orange laxman 8 90 apple laxman 7 84 orange krishna 6 12 apple krishna 1 1 apple krishna 5 73 apple krishna 2 90 orange krishna 0 63 - all above points can also be combined
Exec
- general form :
exec column/s by column/s from table_name where condition/s - can return data with type different than table, whereas select always returns data with type=table(98h)
-
exec can only be applied to in-memory tables
-
exec column/s from table_name exec column/s!column/s from table_name- return type is dictionary
- keys are column/s before
!and values are ones after it exec column/s!column/s by column/s from table_name
Update
- update allows us to add/modify columns from table
- general form:
update column_name/s: aggr column_name/s by column/s from table_name where condition/s update new_column_name:aggr[column1;column2] from table_nameq)update notional:price*qty from sales fruit grocer price qty notional --------------------------------- orange krishna 6 12 72 apple laxman 6 10 60 apple krishna 1 1 1 orange laxman 8 90 720 apple krishna 5 73 365 banana ram 4 90 360 apple ram 9 43 387 apple krishna 2 90 180 apple laxman 7 84 588 orange krishna 0 63 0- update by default won't make changes to table, we can make changes into table by referring to table_name in update query as symbol -
update x:y from ``table_nameq)update notional:price*qty from sales fruit grocer price qty notional --------------------------------- orange krishna 6 12 72 apple laxman 6 10 60 apple krishna 1 1 1 orange laxman 8 90 720 apple krishna 5 73 365 banana ram 4 90 360 apple ram 9 43 387 apple krishna 2 90 180 apple laxman 7 84 588 orange krishna 0 63 0 q)sales fruit grocer price qty ------------------------ orange krishna 6 12 apple laxman 6 10 apple krishna 1 1 orange laxman 8 90 apple krishna 5 73 banana ram 4 90 apple ram 9 43 apple krishna 2 90 apple laxman 7 84 orange krishna 0 63 q)update notional:price*qty from `sales `sales q)sales fruit grocer price qty notional --------------------------------- orange krishna 6 12 72 apple laxman 6 10 60 apple krishna 1 1 1 orange laxman 8 90 720 apple krishna 5 73 365 banana ram 4 90 360 apple ram 9 43 387 apple krishna 2 90 180 apple laxman 7 84 588 orange krishna 0 63 0
Delete
- used to delete columns from table
- general form is same as update/select
delete column/s from table_namedelete from table_name where condition/s- we need to provide table_name as symbol to make changes permanent
q)delete notional from sales
fruit grocer qty
------------------
orange krishna 12
apple laxman 10
apple krishna 1
orange laxman 90
apple krishna 73
banana ram 90
apple ram 43
apple krishna 90
apple laxman 84
orange krishna 63
q)sales
fruit grocer qty notional
---------------------------
orange krishna 12 72
apple laxman 10 60
apple krishna 1 1
orange laxman 90 720
apple krishna 73 365
banana ram 90 360
apple ram 43 387
apple krishna 90 180
apple laxman 84 588
orange krishna 63 0
q)delete notional from `sales
`sales
q)sales
fruit grocer qty
------------------
orange krishna 12
apple laxman 10
apple krishna 1
orange laxman 90
apple krishna 73
banana ram 90
apple ram 43
apple krishna 90
apple laxman 84
orange krishna 63
delete to remove entities from our workspace
* we can also delete everything from current workspace using delete
Insert
- general form
table_name_as_symbol insert (values;as;column;types) - returned is the index number where rows are added into the table
- more than 1 record can be added with values as list of lists
- we can also insert entire table, provided schema matches
- same can be done using
,:adverbq)`cars insert cars 3 4 5 q)cars brand model purchasedate ---------------------------- bmw z4 2020.01.01 tata nexon 2020.12.01 mahindra xuv500 2020.10.02 bmw z4 2020.01.01 tata nexon 2020.12.01 mahindra xuv500 2020.10.02 q)cars,:cars q)cars brand model purchasedate ---------------------------- bmw z4 2020.01.01 tata nexon 2020.12.01 mahindra xuv500 2020.10.02 bmw z4 2020.01.01 tata nexon 2020.12.01 mahindra xuv500 2020.10.02 bmw z4 2020.01.01 tata nexon 2020.12.01 mahindra xuv500 2020.10.02 bmw z4 2020.01.01 tata nexon 2020.12.01 mahindra xuv500 2020.10.02
Upsert
- can be used for both - keyed and unkeyed tables
- it will modify a row if it already exists and add if it doesn't
- in order to add more than 1 row, we need to upsert a table
q)`fruitprice upsert (`apple`orange;150 30;30 50) 'length [0] `fruitprice upsert (`apple`orange;150 30;30 50) ^ q)`fruitprice upsert ([fruit:`apple`orange];price: 150 30;quantity: 30 50) `fruitprice q)fruitprice fruit | price quantity ------| -------------- apple | 150 30 banana| 20 400 orange| 30 50 - upsert will work if order of columns are different or column values are missing
q)fruitprice fruit | price quantity ------| -------------- apple | 150 30 banana| 20 400 orange| 30 50 q)`fruitprice upsert (fruit:`apple;price: 200 ;quantity: 20) `fruitprice q)fruitprice fruit | price quantity ------| -------------- apple | 200 20 banana| 20 400 orange| 30 50 q)`fruitprice upsert ([]price:175 40;fruit:`apple`kiwi) `fruitprice q)fruitprice fruit | price quantity ------| -------------- apple | 175 20 banana| 20 400 orange| 30 50 kiwi | 40