# installs everything you need to use sqldf with SQLite
# including SQLite itself
install.packages("sqldf")
# shows built in data frames
data()
# load sqldf into workspace
library(sqldf)
sqldf("select * from iris limit 5")
sqldf("select count(*) from iris")
sqldf("select Species, count(*) from iris group by Species")
# create a data frame
DF <- data.frame(a = 1:5, b = letters[1:5])
sqldf("select * from DF")
sqldf("select avg(a) mean, variance(a) var from DF") # see example 15
SELECT *
FROM df1
WHERE product = "Toaster"
df1 = data.frame(CustomerId=c(1:6),Product=c(rep("Toaster",3),rep("Radio",3))) ;
df <- df1[df1$Product=="Toaster",];
select distinct x
from my_table;
> x <- list(a=c(1,2,3), b = c(2,3,4), c=c(4,5,6))
> xx <- unlist(x)
> xx
a1 a2 a3 b1 b2 b3 c1 c2 c3
1 2 3 2 3 4 4 5 6
> unique(xx)
[1] 1 2 3 4 5 6
select *
from product [left] [right] [outer] join countries
on (product.customer_id = countries.customer_id)
df1 = data.frame(CustomerId=c(1:6),Product=c(rep("Toaster",3),rep("Radio",3)))
df2 = data.frame(CustomerId=c(2,4,6),State=c(rep("Alabama",2),rep("Ohio",1)))
> df1
CustomerId Product
1 Toaster
2 Toaster
3 Toaster
4 Radio
5 Radio
6 Radio
> df2
CustomerId State
2 Alabama
4 Alabama
6 Ohio
#Outer join:
merge(x = df1, y = df2, by = "CustomerId", all = TRUE)
#Left outer:
merge(x = df1, y = df2, by = "CustomerId", all.x=TRUE)
#Right outer:
merge(x = df1, y = df2, by = "CustomerId", all.y=TRUE)
#Cross join:
merge(x = df1, y = df2, by = NULL)
CREATE TABLE my_table (
a varchar2(10 char),
b varchar2(10 char),
c number
);
SELECT a, b, mean(c)
FROM my_table
GROUP BY a, b
grouped_data <- aggregate(my_table, by=list(my_table$a, my_table$b, FUN=mean);
> mydf
A B
1 1 2
2 1 3
3 2 3
4 3 5
5 3 6
> aggregate(B ~ A, mydf, sum)
A B
1 1 5
2 2 3
3 3 11
> library(data.table)
> DT <- data.table(mydf)
> DT[, sum(B), by = A]
A V1
1: 1 5
2: 2 3
3: 3 11
> DF <- data.frame(A = c("1", "1", "2", "3", "3"), B = c(2, 3, 3, 5, 6))
> library(plyr)
> DF.sum <- ddply(DF, c("A"), summarize, B = sum(B))
> DF.sum
A B
1 1 5
2 2 3
3 3 11
SELECT empno, deptno,
COUNT(*) OVER (PARTITION BY
deptno) DEPT_COUNT
FROM emp
WHERE deptno IN (20, 30);
EMPNO DEPTNO DEPT_COUNT
---------- ---------- ----------
7369 20 5
7566 20 5
7788 20 5
7902 20 5
7876 20 5
7499 30 6
7900 30 6
7844 30 6
7698 30 6
7654 30 6
7521 30 6
11 rows selected.
SELECT empno, deptno, hiredate, ROW_NUMBER( ) OVER (PARTITION BY deptno ORDER BY hiredate NULLS LAST) SRLNO FROM emp WHERE deptno IN (10, 20) ORDER BY deptno, SRLNO; EMPNO DEPTNO HIREDATE SRLNO ------ ------- --------- ---------- 7782 10 09-JUN-81 1 7839 10 17-NOV-81 2 7934 10 23-JAN-82 3 7369 20 17-DEC-80 1 7566 20 02-APR-81 2 7902 20 03-DEC-81 3 7788 20 09-DEC-82 4 7876 20 12-JAN-83 5 8 rows selected.
SELECT empno, deptno, sal, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC NULLS LAST) RANK, DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC NULLS LAST) DENSE_RANK FROM emp WHERE deptno IN (10, 20) ORDER BY 2, RANK; EMPNO DEPTNO SAL RANK DENSE_RANK ------ ------- ----- ----- ---------- 7839 10 5000 1 1 7782 10 2450 2 2 7934 10 1300 3 3 7788 20 3000 1 1 7902 20 3000 1 1 7566 20 2975 3 2 7876 20 1100 4 3 7369 20 800 5 4 8 rows selected.
SELECT deptno, empno, sal,
LEAD(sal, 1, 0) OVER (PARTITION BY dept ORDER BY sal DESC NULLS LAST) NEXT_LOWER_SAL,
LAG(sal, 1, 0) OVER (PARTITION BY dept ORDER BY sal DESC NULLS LAST) PREV_HIGHER_SAL
FROM emp
WHERE deptno IN (10, 20)
ORDER BY deptno, sal DESC;
DEPTNO EMPNO SAL NEXT_LOWER_SAL PREV_HIGHER_SAL
------- ------ ----- -------------- ---------------
10 7839 5000 2450 0
10 7782 2450 1300 5000
10 7934 1300 0 2450
20 7788 3000 3000 0
20 7902 3000 2975 3000
20 7566 2975 1100 3000
20 7876 1100 800 2975
20 7369 800 0 1100
8 rows selected.
-- How many days after the first hire of each department were the next
-- employees hired?
SELECT empno, deptno, hiredate ? FIRST_VALUE(hiredate)
OVER (PARTITION BY deptno ORDER BY hiredate) DAY_GAP
FROM emp
WHERE deptno IN (20, 30)
ORDER BY deptno, DAY_GAP;
EMPNO DEPTNO DAY_GAP
---------- ---------- ----------
7369 20 0
7566 20 106
7902 20 351
7788 20 722
7876 20 756
7499 30 0
7521 30 2
7698 30 70
7844 30 200
7654 30 220
7900 30 286
11 rows selected.
public class myClass() {
...
public void myMethod() {
...
try {
//code to be executed causing exception
}
catch(Exception e) {
//log the exception
}
...
}
...
}
CREATE PROCEDURE myproc AS
BEGIN
...
DECLARE EXIT HANDLER FOR SQL_ERROR_CODE MY_SQL_ERROR_CODE
BEGIN
-- log the exception
END;
-- code to be executed causing exception
end;
CREATE TABLE MYTAB (I INTEGER PRIMARY KEY);
drop PROCEDURE myproc;
CREATE PROCEDURE myproc AS
BEGIN
declare myvar int;
DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 1299
BEGIN
write_debug_log('MARCO TEST',
'Handler of the NO_DATA_FOUND exception of the select below (after the following begin end block)',
'SQL_ERROR_CODE = ' || ::SQL_ERROR_CODE || '; SQL_ERROR_MESSAGE = ' || ::SQL_ERROR_MESSAGE);
END;
begin
declare my_test int;
--CASE GENERIC FOR ANY POSSIBLE EXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION
begin
write_debug_log('MARCO TEST',
'separate handler for division by 0',
'SQL_ERROR_CODE = ' || ::SQL_ERROR_CODE || '; SQL_ERROR_MESSAGE = ' || ::SQL_ERROR_MESSAGE);
end;
my_test := 1/0;
end;
SELECT I INTO myVar FROM MYTAB; --NO_DATA_FOUND exception
SELECT 'NeverReached_noContinueOnErrorSemantics' FROM DUMMY;
END;
--execute the code and raise the exceptions
call myproc;
--check the situation in the log table
select * from log
order by id desc;