Oracle Foundation Test Questions and Answers!
Table structure :
create table tbEmp -- Staff table
(
eID number(7) primarykey, -- staff number
eName varchar(20) not null, -- staff name
eSex varchar(2) not null - the gender of the staff member
check(esex in('male',' women')),
eAge smallint not nullcheck(eage>=18), -- Age of staff
eAddr varchar2(50) not null, -- staff address
eTel varchar2(30) not null, -- Staff phone number
eEmail varchar2(30) - staff email
)
Create Sequence
create sequence tbEmp_seq
minvalue 1
maxvalue 9999999
start with 1
increment by 1
cache 100;
Basic data
insert into tbemp (eid,ename,esex,eage,eaddr,etel)
values(tbEmp_seq.Nextval,' Feifei Chen','male',22,' Tongde District, Guangzhou City, Guangdong Province6 ridgepole (old)808 size','020-3434123');
insert into tbemp(eid,ename,esex,eage,eaddr,etel)
values(tbEmp_seq.Nextval,' Du Juanjuan (name)',' women',23,' Shang She Village, Guangzhou City, Guangdong Province1 ridgepole (old)2 size','020-34345233');
insert into tbemp (eid,ename,esex,eage,eaddr,etel)
values(tbEmp_seq.Nextval,' Liu Yidao','male',25,' City Plaza, Guangzhou, Guangdong Province606 size','020-12344321');
insert into tbemp(eid,ename,esex,eage,eaddr,etel)
values(tbEmp_seq.Nextval,' Zhang Xiaojie','male',27,' Shenzhen, Guangdong Province4221 plants','0755-9876543');
insert into tbemp (eid,ename,esex,eage,eaddr,etel)
values(tbEmp_seq.Nextval,' a house built as a concubine women',' women',23,' Green Road, Kobashi, Hebei87 size','0689-5656564');
select * from tbemp;
Product Information Sheet
create table tbProd
(
pID number(7) primary key, --item number
pType varchar(20) notnull, -- commodity type
pMark varchar(20) not null, --brand of the item
pSpec varchar(20) - the product specification
)
Create Sequence
create sequence tbProd_seq
minvalue 1
maxvalue 9999999
start with 1
increment by 1
cache 100;
Basic data
insert into tbprodvalues(tbProd_seq.Nextval,' TV sets',' Skyworth (brand)','48 Inch Plasma TV');
insert into tbprodvalues(tbProd_seq.Nextval,' TV sets',' Skyworth (brand)','56 Digital TV sets in inches');
insert into tbprodvalues(tbProd_seq.Nextval,' TV sets',' Skyworth (brand)','32 Inch Rear Projection');
insert into tbprodvalues(tbProd_seq.Nextval,' TV sets',' panda','48 Inch Plasma TV');
insert into tbprodvalues(tbProd_seq.Nextval,' TV sets',' panda','56 Digital TV sets in inches');
insert into tbprod values(tbProd_seq.Nextval,' TV sets','TCL','32 Inch Rear Projection');
insert into tbprodvalues(tbProd_seq.Nextval,' notebooks',' Acer',' Pentium Dual-Core T6666');
insert into tbprodvalues(tbProd_seq.Nextval,' notebooks',' Acer',' Core2 dual core (computing) E7500 ');
insert into tbprodvalues(tbProd_seq.Nextval,' notebooks',' associate (cognitively)',' Pentium Dual-Core T6666');
insert into tbprodvalues(tbProd_seq.Nextval,' notebooks',' associate (cognitively)',' Core2 dual core (computing) E7500 ');
Sales table
create table tbSales
(
srNO number(7) primarykey, -- record number
eID number(7) not nullreferences tbemp, --staff number
pID number(7) not nullreferences tbprod, --item number
pQty number(7) not null check(pqty>0), -- Number of sales
pAmount number(7) notnull check(pamount>0), -- Sales value
sDate date -- time of sale
)
Create Sequence
create sequence tbSales_seq
minvalue 1
maxvalue 9999999
start with 1
increment by 1
cache 100;
Basic data
insert into tbsalesvalues(tbSales_seq.Nextval,1,1,10,21000,to_date('2010-3-12','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,1,2,5,20000,to_date('2010-3-12','yyyy-mm-dd'));
insert into tbsales values(tbSales_seq.Nextval,1,4,12,23500,to_date('2010-3-14','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,1,5,4,16500,to_date('2010-3-14','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,2,3,3,31000,to_date('2010-3-11','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,2,6,4,40000,to_date('2010-3-13','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,3,7,5,40000,to_date('2010-3-13','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,3,8,3,36000,to_date('2010-3-14','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,4,9,6,41500,to_date('2010-3-12','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,4,10,5,50000,to_date('2010-3-14','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,1,1,10,21000,to_date('2010-4-12','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,1,2,5,20000,to_date('2010-4-12','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,1,4,12,23500,to_date('2010-4-14','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,1,5,4,16500,to_date('2010-4-14','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,2,3,3,31000,to_date('2010-4-11','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,2,6,4,40000,to_date('2010-4-13','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,3,7,5,40000,to_date('2010-4-13','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,3,8,3,36000,to_date('2010-4-14','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,4,9,6,41500,to_date('2010-4-12','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,4,10,5,50000,to_date('2010-4-14','yyyy-mm-dd'));
II. Single Table Search (2 marks per question, 10 marks in total)
1. Check the details of all female staff
Select * from tbemp where tbemp .esex=’ women’;
2. Search for the name and sex of employees between 24 and 26 years of age
Selectename name and surname,esex name and surname from tbemp where eage<=26 andeage>=24;
Select ename name,esex name from tbempwhere eage between 24 and 26;
3. Search for the name, phone number and address of a female employee living in Guangzhou
Select ename name,etel phone,eaddr address from tbemp where eaddr like '%Guangzhou%';
4. Inquire about Chen Feifei, a house built as a concubine women, Jie Zhang's phone number, address
Select etel phone,eaddr address from tbemp where ename in ('Chen Fei Fei',' Ah Fang female',' Zhang Xiao Jie');
Select etel phone,eaddr address from tbemp where ename='Chen Feifei' or ename=' Ah Fang female' or ename= 'Zhang Xiaojie';
5. Search for the name, gender and age of employees in Guangzhou and Shenzhen
Select ename name,esex gender , eage age from tbemp where eaddr like '%Guangzhou%' or
eaddr like '%Shenzhen%';
III. Multi-table query (4 marks per question, 20 marks in total)
1. Sales of all staff, all products, including: staff name, product category, product brand, product specification, sales quantity, sales value, sales time, sorted by sales time from highest to lowest
SelectE.ENAME,P.PTYPE,P.PMARK,P.PSPEC,S.PQTY,S.PAMOUNT,P.SDATE
from tbemp e, tbprod p,tbsales s
where E.EID=S.EID AND S.PID=P.PID
ORDER BY S.SDATE DESC;
2. Notebook sales for April, including: item number, item brand, item size, quantity sold, value sold, sorted by quantity sold from lowest to highest
SELECT P.PID,P.PMARK,P.SPEC,S.PQTY,S.PAMOUNT
FROM TBPROD P, TBSALES S
WHERE S.PID=P.PID AND P.PTYPE='Notebook' AND S.SDATE LIKE '%-4%'
ORDER BY S.PQTY;
---- determine the month of the date type, available function extract
----WHERE EXTRACT(MONTH FROM S.SDATE ) = ’4’
3. Fei-Fei Chen's sales for March, including: item number, sales quantity, sales value
SELECT P.PID,S.PQTY,S.PAMOUNT
FROM TBPROD P, TBSALES S
WHERE S.PID=P.PID AND
S.EID IN (SELECT E.EID FROM TBEMP E WHERE E.ENAME='Chen Feifei')
AND EXTRACT(MONTH FROM S.SDATE)=’3’;
4. Sales of Skyworth 56-inch digital TV sets, including: sales quantity, sales value, sales time
SELECT S.PQTY,S.PAMOUNT,S.SDATE FROM TBSALES S , TBPROD P
WHERE S.PID=P.PID
AND P.PMRK=' Genworth'
AND P.PSPEC=' 56-inch digital TV set';
5. Guangzhou male staff sales in April, including: staff name, product category, product brand, product specifications, sales quantity, sales value
SELECT E.ENAME,P.PTYPE, P.PMARK, P.PSPEC, S.PQTY, S.PAMOUNT
FROM TBEMP E,TBSALES S,TBPROD P
WHERE E.EID=S.EID AND S.PID=P.PID
AND E.EADDR LIKE '%Guangzhou%'
AND E.ESEX=' male'
AND EXTRACT(MONTH FROM S.SDATE)=’4’;
IV. Total (5 marks per question, 30 marks in total)
1. How many staff members are there in total?
SELECT COUNT(EID) FROM TBEMP;
2. How many times has a Skyworth TV been sold? // How many total sales have been recorded
SELECT COUNT(S.SRNO) FROM TBSALES S, TBPROD P
WHERE S.PID=P.PID
AND P.PTYPE=' TV'
AND P.PMARK=' Genworth' ;
3. Age of the youngest female staff member
SELECT MIN(EAGE) FROM TBEMP
WHERE ESEX=' female';
4. Highest single sale value of notebooks per sale
SELECT MAX(S.PAMOUNT) FROM TBSALESS,TBPROD P
WHERE S.PID=P.PID
AND P.PTYPE=' notebook';
5. Average selling price of TCL TV sets
---Average sales price = total sales price/total number of sales
SELECT SUM(S.PAMOUNT)/SUM(S.PQTY) FROM TBSALES S, TBPROD P
WHERE S.PID=P.PID
AND P.PTYPE=' TV'
AND P.PMARK=’ TCL’;
6. Lenovo notebook sales totals for April
SELECT SUM(S.PAMOUNT) FROM TBSALES S, TBPROD P
WHERE S.PID=P.PID
AND P.PTYPE=' notebook'
AND P.PMARK=' associate'
AND EXTRACT(MONTH FROM S.SDATE)=’4’;
V. Breakdown and total (5 marks per question, total 35 marks)
1. The total number of products sold by type and brand, and the total number of products sold below 20 are not counted.
SELECT SUM(S.PQTY) FROM TBSALES S,TBPROD P
WHERE S.PID=P.PID
GROUP BY P.PTYPE,P.PMARK
HAVING SUM(S.PQTY)>=20;
2. Average selling price of notebooks by brand and specification, displayed in descending order of selling price
SELECT SUM(S.PAMOUNT)/SUM(S.PQTY) FROM TBSALES S, TBPROD P
WHERE S.PID=P.PID
AND P.PTYPE=' notebook'
GROUP BY P.PMARK , P.PSPEC
ORDER BY SUM(S.PAMOUNT)/SUM(S.PQTY) DESC;
3. The total sales of male employees in March are counted by name, and those with sales less than 80,000 are not counted.
SELECT SUM(S.PAMOUNT) FROM TBEMP E,TBSALES S
WHERE E.EID=S.EID
AND E.ESEX=' male'
AND EXTRACT(MONTH FROM S.SDATE)=’3’
GROUP BY E.ENAME
HAVING SUM(S.PAMOUNT)>=80000;
4. Find the name of the employee with the highest gross sales
SELECT G.NAME FROM (
SELECT E.ENAME NAME, SUM(S.PAMOUNT) TOTAL
FROM TBEMP E, TBSALES S
WHERE E.EID=S.EID
GROUP BY E.ENAME)G
WHERE G.TOTAL IN
(SELECT MAX(H.TOTAL) FROM
(SELECT E.ENAME NAME, SUM(S.PAMOUNT) TOTAL
FROMTBEMP E, TBSALES S
WHERE E.EID=S.EID
GROUP BY E.ENAME)H
);
---- Method 2
SELECT E.ENAME FROM TBEMP E, TBSALES S
WHERE E.EID=S.EID
GROUP BY E.ENAME
HAVING SUM(S.PAMOUNT)>=ALL(
SELECT SUM(S.PAMOUNT) FROM TBSALES S
GROUP BY E.EID
);
5. Count the total number of sales completed by each employee by name
SELECT COUNT( S.SRNO) FROM TBEMP E, TBSALES S
WHERE E.EID=S.EID(+)
GROUP BY E.ENAME;
6. Identify the name of the employee with the lowest number of sales transactions and the number of transactions he completed
SELECT E.ENAME, COUNT( S.SRNO) FROM TBEMP E, TBSALES S
WHERE E.EID=S.EID(+)
GROUP BY E.ENAME
HAVING COUNT(S.SRNO)<=ALL(
SELECT COUNT( S.SRNO) FROM TBEMP E, TBSALES S
WHERE E.EID=S.EID(+)
GROUP BY E.ENAME
) ;
7. Maximum and minimum age of staff by gender
SELECT MAX(EAGE), MIN(EAGE) FROM TBEMP
GROUP BY ESEX ;
Recommended>>
1、BSON and mongoDB data types
2、MIT researcher develops secure platform that doesnt leak data
3、Handling of Characters Strings and Text of String Type
4、CIKM2018 From Big Data to Big Information and Big Knowledge Tutorial with PPT download
5、Data Science How to build a data science team in an internet company