Given the following two tables:
NAMES NAME NUMBER
Wayne Gretzky 99 Jaromir Jagr 68 Bobby Orr 4 Bobby Hull 23 Brett Hull 16 Mario Lemieux 66 Mark Messier 11 POINTS NAME POINTS
Wayne Gretzky 244 Jaromir Jagr 168 Bobby Orr 129 Brett Hull 121 Mario Lemieux 189 Joe Sakic 94
Which of the following statements will display the player name, number, and points for all players that have scored points?
A. SELECT p.name,n.number, p.points FROM names n INNER JOIN points p ON n.name =
B. name
C. SELECT p.name,n.number, p.points FROM names n LEFT OUTER JOIN points p ON n.name = p.name
D. SELECT p.name,n.number, p.points FROM names n RIGHT OUTER JOIN points p ON n.name = p.name
E. SELECT p.name,n.number, p.points FROM names n FULL OUTER JOIN points p ON n.name = p.name
Given the following table definitions:
TABLE1
ID INT NAME CHAR(30) PERSON INT CITIES INT TABLE2
ID INT
LASTNAME CHAR(30)
Which of the following statements will remove all rows in table TABLE1 that have matching PERSONs in table
TABLE2?
A. DELETE FROM table1 WHERE id IN (SELECT id FROM table2)
B. DELETE FROM table1 WHERE id IN (SELECT person FROM table2)
C. DELETE FROM table1 WHERE person IN (SELECT id FROM table2)
D. DELETE FROM table1 WHERE person IN (SELECT person FROM table2)
Given the following data:
TAB1
C1 C2
200 abc 250 abc 150 def 300 ghi 175 def
If the following query is executed: WITH subset (col1, col2) AS (SELECT c1, c2 FROM tab1 WHERE c1 > 150) SELECT col2, SUM(col1) AS col1_sum FROM subset GROUP BY col2 ORDER BY col2
Which of the following result data sets will be produced?
A. COL2 COL1_SUM
abc 200
abc 250
def 175
ghi 300
4 record(s) selected.
B. COL2 COL1_SUM
abc 450
def 175
ghi 300
3 record(s) selected.
C. COL2 COL1_SUM
abc 450
def 325
ghi 300
3 record(s) selected.
D. COL2 COL1_SUM
abc 450
abc 450
def 175
def 175
ghi 300
5 record(s) selected.
The following SQL statement:
DELETE FROM tab1 WHERE CURRENT OF csr1 WITH RR Is used to perform which type of delete operation?
A. Positioned
B. Searched
C. Embedded
D. Dynamic
Given the following table definition:
EMPLOYESS
EMP ID INTEGER
NAME CHAR(20)
DEPT CHAR(10)
SALARY DECIMAL (10, 2)
COMMISSION DECIMAL (8, 2)
Assuming the DEPT column contains the values 'ADMIN', 'PRODUCTION', and 'SALES', which of the following
statements will produce a result data set in which all ADMIN department employees are grouped together, all
PRODUCTION department employees are grouped together, and all SALES department employees are
grouped together?
A. SELECT name, dept FROM employees ORDER BY dept
B. SELECT name, dept FROM employees GROUP BY dept
C. SELECT name, dept FROM employees GROUP BY ROLLUP (dept)
D. SELECT name, dept FROM employees GROUP BY CUBE (dept)
Given the following table definition:
SALES
SALES_DATE DATE
SALES_PERSON CHAR(20)
REGION CHAR(20)
SALES INTEGER
Which of the following SQL statements will remove all rows that had a SALES_DATE in the year 1995?
A. DELETE * FROM sales WHEREYEAR(sales_date) = 1995
B. DELETE FROM sales WHEREYEAR(sales_date) = 1995
C. DROP * FROM sales WHEREYEAR(sales_date) = 1995
D. DROP FROM sales WHEREYEAR(sales_date) = 1995
Given the following table:
CURRENT_EMPLOYEES
EMPID INTEGER NOT NULL
NAME CHAR(20)
SALARY DECIMAL(10,2)
PAST_EMPLOYEES
EMPID INTEGER NOT NULL
NAME CHAR(20)
SALARY DECIMAL(10,2)
Assuming both tables contain data, which of the following statements will NOT successfully add data to table
CURRENT_EMPLOYEES?
A. INSERT INTOcurrent_employees (empid) VALUES (10)
B. INSERT INTOcurrent_employees VALUES (10, 'JAGGER', 85000.00)
C. INSERT INTOcurrent_employees SELECT empid, name, salary FROM past_employees WHERE empid = 20
D. INSERT INTOcurrent_employees (name, salary) VALUES (SELECT name, salary FROM past_employees WHERE empid = 20)
Given the following UPDATE statement:
UPDATE employees SET workdept =
(SELECT deptno FROM department WHERE deptno = 'A01') WHERE workdept IS NULL
Which of the following describes the result if this statement is executed?
A. The statement will fail because an UPDATE statement cannot contain asubquery.
B. The statement will only succeed if the data retrieved by thesubquery does not contain multiple records.
C. The statement will succeed; if the data retrieved by thesubquery contains multiple records, only the first record will be used to perform the update.
D. The statement will only succeed if every record in the EMPLOYEES table has a null value in the WORKDEPT column.
Given the following table:
EMPLOYEE
EMPID NAME INSTRUMENT
1 Jagger, Mick 01
2 Richards, Keith 02
3 Wood, Ronnie 02
4 Watts, Charlie 03
5 Jones, Darryl 04
6 Leavell, Chuck 05
If the following query is executed:
SELECT name,
CASE WHEN instrument = '01' THEN 'HARMONICA'
WHEN instrument = '02' THEN 'GUITAR'
WHEN instrument = '03' THEN 'DRUMS'
ELSE 'UNKNOWN'
END AS instrument
FROM employee
What will be the results?
A. NAME INSTRUMENT
Jagger, Mick HARMONICA
Richards, Keith GUITAR
Wood, Ronnie GUITAR
Watts, Charlie DRUMS
Jones, Darryl ERROR
Leavell, Chuck ERROR
B. NAME INSTRUMENT
Jagger, Mick HARMONICA
Richards, Keith GUITAR
Wood, Ronnie GUITAR
Watts, Charlie DRUMS
Jones, Darryl 04
Leavell, Chuck 05
C. NAME INSTRUMENT
Jagger, Mick HARMONICA
Richards, Keith GUITAR
Wood, Ronnie GUITAR
Watts, Charlie DRUMS
Jones, Darryl UNKNOWN
Leavell, Chuck UNKNOWN
D. NAME INSTRUMENT
Jagger, Mick HARMONICA
Richards, Keith GUITAR
Wood, Ronnie GUITAR
Watts, Charlie DRUMS
Jones, Darryl -
Leavell, Chuck
Given the following table definitions:
DEPARTMENT
DEPTNO CHAR(3) DEPTNAME CHAR(30) MGRNO INTEGER ADMRDEPT CHAR(3)
EMPLOYEE
EMPNO INTEGER FIRSTNAME CHAR(30) MIDINIT CHAR LASTNAME CHAR(30) WORKDEPT CHAR(3)
Which of the following statements will list every employee number and last name, along with the employee number and last name of their manager, including employees that have not been assigned to a manager?
A. SELECTe.empno, e.lastname, m.empno, m.lastname FROM employee e LEFT INNER JOIN department INNER JOIN employee m ON mgrno=m.empno ON e.workdept=deptno
B. SELECTe.empno, e.lastname, m.empno, m.lastname FROM employee e LEFT OUTER JOIN department INNER JOIN employee m ON mgrno=m.empno ON e.workdept=deptno
C. SELECTe.empno, e.lastname, m.empno, m.lastname FROM employee e RIGHT OUTER JOIN department INNER JOIN employee m ON mgrno=m.empno ON e.workdept=deptno
D. SELECTe.empno, e.lastname, m.empno, m.lastname FROM employee e RIGHT INNER JOIN department INNER JOIN employee m ON mgrno=m.empno ON e.workdept=deptno
Nowadays, the certification exams become more and more important and required by more and more enterprises when applying for a job. But how to prepare for the exam effectively? How to prepare for the exam in a short time with less efforts? How to get a ideal result and how to find the most reliable resources? Here on Vcedump.com, you will find all the answers. Vcedump.com provide not only IBM exam questions, answers and explanations but also complete assistance on your exam preparation and certification application. If you are confused on your C2090-730 exam preparations and IBM certification application, do not hesitate to visit our Vcedump.com to find your solutions here.