Exam 2 ISM 641 Flashcards by Rebecca Cottrell

Find the largest department
A) select deptno from emp group by deptno having count() is maximum;
B) select deptno from emp group by deptno having count() = (select max(count()) from emp group by
deptno);
C) select deptno from emp group by deptno having count() >= all (select max(count()) from emp group
by deptno);
D) select deptno from emp group by deptno having count() >= all (select count(*) from emp group by
deptno);

D) select deptno from emp group by deptno having count() >= all (select count() from emp group by
deptno);

How well did you know this?

Not at all

Perfectly

(check all that apply) Find all managers who has no supervisees
A) select ename from emp where (job, empno) not in (select job, mgr from emp);
B) select ename from emp where job = ‘MANAGER’ and empno not in (select mgr from emp where mgr is not null);
C) (select ename from emp where job = ‘MANAGER’) minus (select ename from emp where empno in (select mgr from emp));
D) (select ename from emp where job = ‘MANAGER’)) intersect (select ename from emp where empno in (select mgr from emp));

B) select ename from emp where job = ‘MANAGER’ and empno not in (select mgr from emp where mgr is not null);
C) (select ename from emp where job = ‘MANAGER’) minus (select ename from emp where empno in (select mgr from emp));

How well did you know this?

Not at all

Perfectly

Find the maximum salary in Dallas
A) select max(sal) from emp, dept where dept.deptno = emp.deptno;
B) select max(sal) from emp where deptno in (select deptno from dept where loc=’DALLAS’);
C) select sal from emp, dept where loc=’DALLAS’;
D) select max(sal) from emp where loc = ‘DALLAS’

B) select max(sal) from emp where deptno in (select deptno from dept where loc=’DALLAS’);

How well did you know this?

Not at all

Perfectly

(check all that apply) Find all the employees located in Dallas
A) select ename from dept where loc = ‘DALLAS’;
B) select ename from dept, emp where dept.depto = emp.deptno and loc = ‘DALLAS’;
C) select ename from dept, emp where loc = ‘DALLAS’;
D) select ename from emp where loc = ‘DALLAS’;
E) select ename from emp where deptno in (select deptno from dept where loc = ‘DALLAS’);

B) select ename from dept, emp where dept.depto = emp.deptno and loc = ‘DALLAS’;
E) select ename from emp where deptno in (select deptno from dept where loc = ‘DALLAS’);

How well did you know this?

Not at all

Perfectly

(check all that apply) Find all salesman who makes more than that all managers make
A) select salesman from emp where sal > all (select max(sal) from emp where job = ‘MANAGER’);
B) select ename from emp where sal > all (select sal from emp where mgr = empno);
C) select ename from emp where job = ‘SALESMAN’ and sal > (select max(sal) from emp where job = ‘MANAGER’);
D) select ename from emp where job = ‘SALESMAN’ and sal > all (select sal from emp where job =
‘MANAGER’);
E) none of the above

C) select ename from emp where job = ‘SALESMAN’ and sal > (select max(sal) from emp where job = ‘MANAGER’);
D) select ename from emp where job = ‘SALESMAN’ and sal > all (select sal from emp where job =
‘MANAGER’);

How well did you know this?

Not at all

Perfectly

Find those jobs who have the smallest total salary of all the jobs
A) select job from emp group by job having count(sal) <= all (select count(sal) from emp group by job);
B) select job from emp group by job having sum(sal) <= all (select sum(sal) from emp group by job);
C) select job from emp group by job having sum(sal) = (select min(sum(sal)) from emp group by job);
D) select job from emp where sum(sal) <= all (select sum(sal) from emp group by job);

B) select job from emp group by job having sum(sal) <= all (select sum(sal) from emp group by job);

How well did you know this?

Not at all

Perfectly

Based on the tables shown below, find the department that has the smallest average salary.
A) select deptno from emp group by deptno having min(sal) <= all (select avg(sal) from emp group by deptno);
B) select dname from emp group by deptno having avg(sal) <= (select avg(sal) from emp group by
deptno);
C) select deptno from emp group by deptno having avg(sal) = (select min(avg(sal)) from emp group by deptno);
D) select deptno from emp group by deptno having avg(sal) <= all (select avg(sal) from emp group by deptno);

D) select deptno from emp group by deptno having avg(sal) <= all (select avg(sal) from emp group by deptno);

How well did you know this?

Not at all

Perfectly

Based on the following table, which is the correct SQL statement to find the employees who work
in the same department as SMITH?
A) select ename from emp where job = (select ename from emp where ename = ‘SMITH’);
B) select ename from emp where deptno = (select deptno from emp where ename = ‘SMITH’);
C) select ename from emp where dname = (select dname from emp where ename = ‘SMITH’);
D) select ename from emp where deptno = (select * from emp where ename = ‘SMITH’);

B) select ename from emp where deptno = (select deptno from emp where ename = ‘SMITH’);

How well did you know this?

Not at all

Perfectly

Based on the following table, which is the correct SQL statement to find the employees who was hired in the same year as TURNER?
A) select ename from emp where hiredate = (select hiredate from emp where ename = ‘TURNER’);
B) select ename from emp where hiredate in (select * from emp where ename = ‘TURNER’);
C) select ename from emp where to_char(hiredate,’YYYY’) in (select to_char(hiredate, ‘YYYY’) from emp where ename = ‘TURNER’);
D) select ename from emp where * = (select * from emp where ename = ‘TURNER’);

C) select ename from emp where to_char(hiredate,’YYYY’) in (select to_char(hiredate, ‘YYYY’) from emp where ename = ‘TURNER’);

How well did you know this?

Not at all

Perfectly

Find all employees who have a job as salesman.
A) select all salesman from emp;
B) select empno, ename from emp where job = ‘salesman’;
C) select Count() from salesman;
D) select Count() from emp where job = ‘salesman’;

B) select empno, ename from emp where job = ‘salesman’;

How well did you know this?

Not at all

Perfectly

Find all managers who make more than 2000 dollars
A) select ename from emp where job = ‘MANAGER’ or sal > 2000;
B) select manager from emp where sal > 2000;
C) select ename from emp where job = ‘MANAGER’ and sal > 2000;
D) select ename from emp where job = MANAGER and sal > 2000;

C) select ename from emp where job = ‘MANAGER’ and sal > 2000;

How well did you know this?

Not at all

Perfectly

Find the location for Research Department
A) select loc from dept, emp where dname=’RESEARCH’;
B) select loc from dept where dname = ‘RESEARCH’;
C) select loc from dept where deptno in (select deptno from emp where loc=’DALLAS’);
D) select loc from dept inner join emp on dept.deptno = emp.deptno;

B) select loc from dept where dname = ‘RESEARCH’;

How well did you know this?

Not at all

Perfectly

Find employees whose name has a letter S in it
A) select ename from emp where ename like ‘S%’;
B) select ename from emp where ename like ‘S*’;
C) select ename from emp where ename = ‘%S%’;
D) select ename from emp where ename like ‘%S%’;
E) select ename from emp where ename in ‘%S%’;

D) select ename from emp where ename like ‘%S%’;

How well did you know this?

Not at all

Perfectly

To add a value to an empty cell of an existing record, you would use
A) Insert statement
B) Select statement
C) Update statement
D) Delete statement

C) Update statement

How well did you know this?

Not at all

Perfectly

What can the statement “select constraint_name from user_constraints where table_name = ‘ACCOUNTS’” do?
A) find all the primary keys on Account table
B) find all the foreign keys on Account table
C) find all domain validation rules on Account table
D) All of the above

D) All of the above

How well did you know this?

Not at all

Perfectly

Find those employees whose name starts with letter K
A) select * from emp where ename like ‘K’;
B) select * from emp where ename like ‘%K%’;
C) select * from emp where ename like ‘K*’;
D) select * from emp where ename like ‘K%’;

D) select * from emp where ename like ‘K%’;

How well did you know this?

Not at all

Perfectly

Find the department located in BOSTON in the following table: DEPT(DEPTNO, DNAME, LOC)
A) select department from DEPT where loc= BOSTON;
B) select deptno from DEPT where location = ‘BOSTON’;
C) select dname from dept where loc=BOSTON;
D) select * from DEPT where loc=’BOSTON’;

D) select * from DEPT where loc=’BOSTON’;

How well did you know this?

Not at all

Perfectly

Finds all salesmen who are making less than 1500 dollars
A) select salesman from emp where sal < 1500;
B) select * from emp where sal < 1500;
C) select ename from salesman where sal < 1500;
D) select ename from emp where job = ‘SALESMAN’ and sal < 1500;

D) select ename from emp where job = ‘SALESMAN’ and sal < 1500;

How well did you know this?

Not at all

Perfectly

The second referential integrity rule states that
A) each table must have a primary key
B) the data for a foreign key column must exist in the corresponding primary key colmun
C) recursive relationship is always optional to optional
D) columns with frequent changes in value should not be made a primary key

B) the data for a foreign key column must exist in the corresponding primary key colmun

How well did you know this?

Not at all

Perfectly

Give 5% salary increase to each employee who makes the smallest salary in his or her department
A) update emp set sal = sal * 1.05 where sal = (select min(sal) from emp group by deptno);
B) update emp b set sal = sal * 1.05 where sal = (select min(a.sal) from emp group by a.deptno having a.deptno = b.deptno);
C) update emp b set sal = sal * 1.05 where sal = (select min(a.sal) from emp a where a.deptno = b.deptno)
D) update emp a set sal = sal * 1.05 where a.deptno = b.deptno;

Study These Flashcards

C) update emp b set sal = sal * 1.05 where sal = (select min(a.sal) from emp a where a.deptno = b.deptno)

For all the employees supervised by KING, give them a salary increase, which is equal to the 2%
of the company’s average salary
Code A:

<pre>
update emp
set sal = Sal +
(select 0.02 * avg(sal) from emp)
where mgr in
(select empno from emp where ename = 'KING');
</pre>

Code B:

<pre>
update emp
set sal =
(select avg(1.02*sal) from emp)
where mgr in
(select empno from emp);
</pre>

Code C:

<pre>
update emp a, emp b
set a.sal =
(select avg(1.02*a.sal) from emp)
where a.mgr in
(select empno from emp b where b.ename = 'KING');
</pre>

A) A, B, and C
B) A and B
C) B and C
D) A and C
E) A only

Study These Flashcards

E) A only

Function decode(comm, null, 0, comm) is the same as
A) NVL(comm, 0)
B) decode(comm, comm, comm, 0)
C) update emp set comm = 0 where comm is null;
D) update comm = 0 when comm is null

Study These Flashcards

A) NVL(comm, 0)

Base on the tables shown in the figure below, give 2% salary increase to those who have the longest employment in their corresponding department.
Code A.

<pre>
update emp a
set a.sal = 1.02 * a.sal
where a.hiredate = (select min(b.hiredate)
from emp b
where b.deptno = a.deptno);
</pre>

Code B:

<pre>
update emp
set sal = 1.02 * sal
where (deptno, hiredate) in (select deptno, min(hiredate)
from emp
group by deptno);
</pre>

Code c:

<pre>
update emp
set sal = 1.02 * sal
where empno in
(select empno from emp a
where a.hiredate = (select min(b.hiredate) from emp b
where b.deptno = a.deptno)));
</pre>

A) A, B, and C
B) A and B
C) B and C
D) A and C
E) C only

Study These Flashcards

A) A, B, and C

Find the top salary earner hired each year.
A) select ename from emp where sal in (select max(sal) from emp group by hiredate);
B) select ename from emp where (hiredate, sal) in (select hiredate, max(sal) from emp group by hiredate);
C) select ename, to_char(hiredate, ‘month’) from emp where (to_char(hiredate, ‘month’), sal) in (select to_char(hiredate, ‘month’), max(sal) from emp group by to_char(hiredate, ‘month’));
D) select ename, to_char(hiredate, ‘YYYY’) from emp where (to_char(hiredate, ‘YYYY’), sal) in (select to_char(hiredate, ‘YYYY’), max(sal) from emp group by to_char(hiredate, ‘YYYY’));

Study These Flashcards

D) select ename, to_char(hiredate, ‘YYYY’) from emp where (to_char(hiredate, ‘YYYY’), sal) in (select to_char(hiredate, ‘YYYY’), max(sal) from emp group by to_char(hiredate, ‘YYYY’));

(select all that apply) Find jobs whose minimum salary is less than 2000 and maximum salary is over 4000 A) (select job from emp group by job having min(sal) < 2000) union (select job from emp group by job having max(sal) > 4000); B) select job from emp group by job having min(sal) < 2000 and max(sal) > 4000; C) (select job from emp group by job having min(sal) < 2000) minus (select job from emp group by job having max(sal) > 4000); D) (select job from emp group by job having min(sal) < 2000) intersect (select job from emp group by job having max(sal) > 4000); E) select job from emp group by job having min(sal) < 2000 and having max(sal) > 4000;

B) select job from emp group by job having min(sal) < 2000 and max(sal) > 4000; D) (select job from emp group by job having min(sal) < 2000) intersect (select job from emp group by job having max(sal) > 4000);

Find employees who are making the minimum salary in their corresponding department A) select ename from emp where sal in (select min(sal) from emp group by deptno); B) select ename, deptno from emp where (deptno, sal) in (select deptno, min(sal) from emp group by deptno); C) select min(sal) from emp group by deptno; D) (select ename from emp where sal in (select min(sal) from emp group by emp) intersect (select ename from emp where deptno in (select deptno from emp group by deptno);

B) select ename, deptno from emp where (deptno, sal) in (select deptno, min(sal) from emp group by deptno);

What is wrong with the following to find the total salary?

 select sum(sal) from ( (select sal from emp where comm is null)
 union
 (select sal from emp where comm is not null) )

A) there is no need to use union B) after from must be a table, not a subquery C) the union will remove duplicate salaries D) nothing wrong

C) the union will remove duplicate salaries

(select all that apply) Find the department located in Dallas and also have over two employees. A) select dname from dept, emp where dept.deptno = emp.deptno and loc='DALLAS' group by dname having count(*) > 2; B) select dname from dept, emp where dept.deptno = emp.deptno and loc='DALLAS' group by deptn having count(*) > 2; C) (select deptno from emp group by deptno having count(*) > 2) intersect (select deptno from dept where loc='DALLAS'); D) (select * from emp group by deptno having count(*) > 2) intersect (select * from dept where loc='DALLAS');

A) select dname from dept, emp where dept.deptno = emp.deptno and loc='DALLAS' group by dname having count(*) > 2; C) (select deptno from emp group by deptno having count(*) > 2) intersect (select deptno from dept where loc='DALLAS');

Based on the following tables, what is the correct statement to find employees who makes less than the average in there corresponding job titles? A) select ename from emp where (job, sal) < (select job, avg(sal) from emp group by job); B) select a.ename from emp a where a.sal < (select avg(b.sal) from emp b where b.deptno = a.deptno); C) select ename from emp a where a.sal < (select avg(b.sal) from emp b where b.job = a.job); D) select ename from emp where (deptno, sal) < (select deptno, avg(sal) from emp group by deptno)n j4mnqa4

C) select ename from emp a where a.sal < (select avg(b.sal) from emp b where b.job = a.job);

A tuple variable in SQL stands for A) a record B) a field C) a table D) none of the above

A) a record

(check all that apply) Delete the employee who has the longest employment in the company A) delete from emp where (hiredate, deptno) in (select max(hiredate), deptno from emp group by deptno); B) delete from emp where hiredate in (select max(hiredate) from emp; C) delete from emp where hiredate in (select min(hiredate) from emp); D) delete from emp s where s.hiredate in (select min(t.hiredate) from emp t where t.deptno = s.deptno); E) delete from emp s where s.hiredate = (select min(t.hiredate) from emp t);

C) delete from emp where hiredate in (select min(hiredate) from emp); E) delete from emp s where s.hiredate = (select min(t.hiredate) from emp t);

(check all that apply) Find the employee in each department who makes the highest salary in their departments A) select ename from emp where sal in (select max(sal) from emp group by deptno); B) select a.ename from emp a where a.sal = (select max(b.sal) from emp b where b.deptno = a.deptno); C) select ename, max(sal) from emp group by depnto; D) select ename from emp where (deptno, sal) in (select deptno, max(sal) from emp group by deptno); E) select ename from (select ename from emp order by sal desc) where rownum < 2;

B) select a.ename from emp a where a.sal = (select max(b.sal) from emp b where b.deptno = a.deptno); D) select ename from emp where (deptno, sal) in (select deptno, max(sal) from emp group by deptno);

Soundex() function is used to search for records based on the sound of A) entire cell value B) individual words in a cell C) entire record D) column name

A) entire cell value

To select a random number of records in Oracle database, you will use which of the following to randomize the order first: A) dbms_random.value B) Random() function C) Rank() function D) Rownum

A) dbms_random.value

Find one department at random based on the following tables: A) select * from dept order by dbms_random.value; B) select * from (select * from dept order by dbms_random.value) where rownum < 2; C) select deptno from emp where rownum< 2; D) select deptno from (select deptno from emp) where rownum< 2;

B) select * from (select * from dept order by dbms_random.value) where rownum < 2;

To find a value in one table, but not in the other table, which operation do you use? A) Interset B) Union C) Minus D) Join

C) Minus

What does the following SQL query do? with t(n) as ( select seq.nextval as n from dual union all select n + 1 as n from t where n < 1000 ) select n from t; A) get a list of integers from the number generated by Sequence seq to 1000 B) get a list of integers from 1 to 1000 C) create a new table called t with n as column name D) nothing, the query is invalid

A) get a list of integers from the number generated by Sequence seq to 1000

(select all that apply) In a hierarchical recursive query, with clause is used to build a subquery. What is that subquery different from subqueries used in other clauses such as where or from clause? A) Subquery in with clause uses union while others do not B) subquery in with clause uses union all while others do not C) Subquery in with clause can be self called by the subquery while others cannot D) There is no difference

B) subquery in with clause uses union all while others do not C) Subquery in with clause can be self called by the subquery while others cannot

Which of the following is the correct criteria to search for a word in a text column based on the sound of the word? A) contains(myTextColumn, '?suport') > 0 B) contains(myTextColumn, '$suport') > 0 C) contains(myTextColumn, '!suport') > 0 D) contains(myTextColumn, 'suport') > 0

C) contains(myTextColumn, '!suport') > 0

What is CAP Theorem? A) No distributed systems can be scalable as well as consistent B) MongoDB cannot be scalable C) A distributed system can meet only two of the following: consistency, availability, and errors tolerance D) A relational database cannot meet the requirements of big data

C) A distributed system can meet only two of the following: consistency, availability, and errors tolerance

What is isolation property for a distributed system? A) All transactions must be done sequentially one by one B) All transactions should lead to the same result whether they are performed concurrently or sequentially C) All transactions can be performed concurrently D) All transactions must all succeed or fail as a whole

B) All transactions should lead to the same result whether they are performed concurrently or sequentially

Using JSON, each document is a list of key-value pairs. How do you enter a order data value like 2009-12-23? A) OrderDate: new Date() B) "OrderDate": ISODate("2009-12-23") C) Order Date: new Date(2009, 12, 23) D) OrderDate: ISODate(2009, 12, 23)

B) "OrderDate": ISODate("2009-12-23")

How do you specify the value for for _id field of a document? A) _id: 12345 B) "_id":ObjectId("12345") C) _id:ObjectID(12345) D) _id:"12345"

B) "_id":ObjectId("12345")

How does MongoDB store a large document such as a photo or video file which is over 200MB? A) use GridFS B) use replication C) use sharding D) cannot be stored because the file is over 16 MB limit

A) use GridFS

The following command will grant roles to scott. Which database does the user have readWrite permission?

 db.grantRolesToUser(
   "scott",
   [ "readWrite" , { role: "read", db: "reporting" } ]
 )

A) reporting B) the current database the user is in C) myDB D) admin E) not specified

B) the current database the user is in

What is the command to insert the employee with eid 100, name "Scott" and hire date as 12/21/2019 into the collection employees? A) insert into employees (eid, name, hiredate) values (100, 'Scott', '21-Dec-2019') B) db.insert({eid:100, name:"Soctt", hiredate:ISODate("2019-12-21")}) C) db.save({eid:100, name:"Soctt", hiredate:ISODate("2019-12-21")}) D) db.employees.insert({eid:100, name:"Scott", hiredate:ISODate("2019-12-21")})

D) db.employees.insert({eid:100, name:"Scott", hiredate:ISODate("2019-12-21")})

(1 point) The command db.students.find({same:"Lisa"}) will find all student documents with sname = "Lisa". How do you find the first document in the list of the found documents? A) db.students.findOne({same:"Lisa"}) B) db.students.find({same:"Lisa"}).limit(1) C) db.students.find({same:"Lisa"}).sort(_id:1).limit(1) D) all fo the above E) noen of the above

D) all fo the above

How do you get the values of _id field for all the documents in students collection? A) db.students.find()._id B) db.students.findOne()._id C) db.student.find().forEach(function(doc){printjson(doc._id)}) D) db.student.find().forEach(function(doc){printjson(doc._id.getTimestamp()})

C) db.student.find().forEach(function(doc){printjson(doc._id)})

(check all that apply) How do you show sname and bdate field vales for all the documents with sname = "Lisa"? A) select sname, bdate from students where sname='Lisa'; B) db.student.find({same:"Lisa"}).forEach(function(doc){printjson([doc.sname, doc.bdate[)}); C) db.students.find( { sname "Lisa" }, { sname: 1, bdate: 1, _id: 0 } ) D) db.students.find( { sname "Lisa" }, { sname: 1, bdate: 1 }

B) db.student.find({same:"Lisa"}).forEach(function(doc){printjson([doc.sname, doc.bdate[)}); C) db.students.find( { sname "Lisa" }, { sname: 1, bdate: 1, _id: 0 } )

Exam 2 ISM 641 Flashcards

(49 cards)