Question

RSQLite (using R studio)

1. Make you have imported the database tables into your database (I've copied and pasted these at the bottom). Write and submit the following RSQLite queries.

2. Retrieve the names of all employees.

3. Retrieve the names of all distinct employee names.

4. Retrieve the names of all employees whose name begins with the letter ‘B’.

5. Retrieve the names and NI numbers (NI_NO) of all employees.

6. Retrieve details of employees who are over 31 years of age.

7. Retrieve details of employees who are between 31 years of age and 65 years of age.

8. Retrieve the average employee age, the age of the youngest employee and the age of the oldest employee.

9. Retrieve the names of all employees and the names of the departments in which they work.

10. Retrieve details of employees who share the phone extension 123.

EMPLOYEE.txt
EMP_N0,NI_NO,NAME,AGE,DEPT_NO
E1,123,SMITH,21,D1
E2,159,SMITH,31,D1
E3,5432,BROWN,65,D2
E5,7654,GREEN,52,D3

PRODUCT.txt
PROD_NO,NAME,COLOR
p1,PANTS,BLUE
p2,PANTS,KHAKI
p3,SOCKS,GREEN
p4,SOCKS,WHITE
p5,SHIRTS,WHITE

SALES ORDER LINE.txt
ORDER_NO,PROD_NO,QUANTITY
01,p1,10
02,p1,10
02,p4,20
09,p1,05
010,p1,05

INVOICES.txt
ORDER_NO,PROD_NO,QUANTITY,PRICE
01,p1,100,2.99
01,p2,20000,2.99
01,p6,20,14.98
02,p2,1000,14.98
02,p6,10000,14.98

SALES ORDER.txt
ORDER_NO,DATE,CUST_NO
01,11/11/17,C1
02,7/9/17,C3
09,8/16/17,C6
010,10/12/17/,C6

CUSTOMER.txt
CUST_NO,NAME,ADDRESS
C1,ALEX,State
C2,BOB,Hollister
C3,CAROL,Ocean
C6,JUAN,Phelps

DEPARTMENT.txt
DEPT_NO,NAME,MANAGER
D1,Accounts,E1
D2,Stores,E2
D3,Sales,null

Answer 1

R-code

> EMP_NO=c("E1","E2","E3","E5")
> EMP_NO
[1] "E1" "E2" "E3" "E5"
> NI_NO=c(123,159,5432,7654)
> NI_NO
[1] 123 159 5432 7654
> NAME=c("SMITH","SMITH","BROWN","GREEN")
> NAME
[1] "SMITH" "SMITH" "BROWN" "GREEN"
> AGE=c(21,31,65,52)
> AGE
[1] 21 31 65 52
> DEPT_NO=c("D1","D1","D2","D3")
> DEPT_NO
[1] "D1" "D1" "D2" "D3"

1. Make you have imported the database tables into your database

> D=data.frame(EMP_NO,NI_NO,NAME,AGE,DEPT_NO)
> D
EMP_NO NI_NO NAME AGE DEPT_NO
1 E1 123 SMITH 21 D1
2 E2 159 SMITH 31 D1
3 E3 5432 BROWN 65 D2
4 E5 7654 GREEN 52 D3

> data=as.matrix(data.frame(EMP_NO,NI_NO,NAME,AGE,DEPT_NO))
> data
EMP_NO NI_NO NAME AGE DEPT_NO
[1,] "E1" " 123" "SMITH" "21" "D1"
[2,] "E2" " 159" "SMITH" "31" "D1"
[3,] "E3" "5432" "BROWN" "65" "D2"
[4,] "E5" "7654" "GREEN" "52" "D3"

2. Retrieve the names of all employees.

> Names=data[,3]
> Names
[1] "SMITH" "SMITH" "BROWN" "GREEN"

3. Retrieve the names of all distinct employee names.

> dist.Names=Names[c(1,3,4)]
> dist.Names
[1] "SMITH" "BROWN" "GREEN"

4. Retrieve the names of all employees whose name begins with the letter ‘B’.

> B_Names=Names[3]
> B_Names
[1] "BROWN"

5. Retrieve the names and NI numbers (NI_NO) of all employees.

> d=data[,c(3,2)]
> d
NAME NI_NO
[1,] "SMITH" " 123"
[2,] "SMITH" " 159"
[3,] "BROWN" "5432"
[4,] "GREEN" "7654"

6. Retrieve details of employees who are over 31 years of age.

> det=data[3:4,]
> det
EMP_NO NI_NO NAME AGE DEPT_NO
[1,] "E3" "5432" "BROWN" "65" "D2"
[2,] "E5" "7654" "GREEN" "52" "D3"   

7. Retrieve details of employees who are between 31 years of age and 65 years of age.

> detail=data[2:4,]
> detail
EMP_NO NI_NO NAME AGE DEPT_NO
[1,] "E2" " 159" "SMITH" "31" "D1"
[2,] "E3" "5432" "BROWN" "65" "D2"
[3,] "E5" "7654" "GREEN" "52" "D3"

8. Retrieve the average employee age, the age of the youngest employee and the age of the oldest employee.

> Ave.age=mean(D[,4])
> Ave.age
[1] 42.25

> young_Employee=max(D[,4])
> young_Employee
[1] 65
> Old_Employee=min(D[,4])
> Old_Employee
[1] 21

9. Retrieve the names of all employees and the names of the departments in which they work.

> NandD=data[,c(3,5)]
> NandD
NAME DEPT_NO
[1,] "SMITH" "D1"
[2,] "SMITH" "D1"
[3,] "BROWN" "D2"
[4,] "GREEN" "D3"

10. Retrieve details of employees who share the phone extension 123.

> phone_ext=data[1,]
> phone_ext
EMP_NO NI_NO NAME AGE DEPT_NO
"E1" " 123" "SMITH" "21" "D1"

____________________________________________________________________________________________

R-code

EMP_NO=c("E1","E2","E3","E5")
EMP_NO
NI_NO=c(123,159,5432,7654)
NI_NO
NAME=c("SMITH","SMITH","BROWN","GREEN")
NAME
AGE=c(21,31,65,52)
AGE
DEPT_NO=c("D1","D1","D2","D3")
DEPT_NO
D=data.frame(EMP_NO,NI_NO,NAME,AGE,DEPT_NO)
D
data=as.matrix(data.frame(EMP_NO,NI_NO,NAME,AGE,DEPT_NO))
data
Names=data[,3]
Names
dist.Names=Names[c(1,3,4)]
dist.Names
B_Names=Names[3]
B_Names
d=data[,c(3,2)]
d
det=data[3:4,]
det
detail=data[2:4,]
detail
Ave.age=mean(D[,4])
Ave.age
young_Employee=max(D[,4])
young_Employee
Old_Employee=min(D[,4])
Old_Employee
NandD=data[,c(3,5)]
NandD
phone_ext=data[1,]
phone_ext