Examples of 512 (b) [code]512 Examples (2) 11. CREATE TYPE kph AS DISTINCT Hadaway WITH COMPARISONS CREATE TYPE mph AS DISTINCT Hadaway WITH COMPARISONS CREATE TABLE speed_limits (Route_num SMALLINT. Canada_sl KPH NOT NULL. Us_sl MPH NOT NULL) Which of the following is a valid query? A. Speed_limits route_num SELECT FROM WHERE Canada_sl "; 80 B. Speed_limits route_num SELECT FROM WHERE Canada_sl "; Kph C. Speed_limits route_num SELECT FROM WHERE Canada_sl "; Us_sl D. Speed_limits route_num SELECT FROM WHERE Canada_sl "; Kph (80) 12. Given the tables : COUNTRY ID NAME Person CITIES 1 Argentina 1 10 Canada 2 2 20 Cuba 3 2 10 4 Germany 1 0 France 5 7 5 STAFF ID LASTNAME Jones 1 Smith 2 The statement : INSERT INTO staff SELECT person, 'Greyson' FROM WHERE country person "; 1 Will insert how many rows into the table? STAFF A. 0 B. 1 C. 2 D. 3 13. Which of the following statements eliminates all but one of each set of duplicate rows in the final result table? A. SELECT * FROM UNIQUE t1 B. SELECT DISTINCT * FROM t1 C. SELECT DISTINCT * FROM t1 D. SELECT UNIQUE (*) FROM t1 E. SELECT DISTINCT (*) FROM t1 14. Given the following table definition and SQL statement : CREATE TABLE table1 (INT col1, col2 CHAR (40), col3 INT) Mr. GRANT INSERT, UPDATE, SELECT, REFERENCES TO USER ON TABLE table1 usera Which of the following SQL statements will revoke privileges for user USERA on COL1 and COL2? A. REVOKE UPDATE ON TABLE table1 FROM USER usera B. REVOKE ALL PRIVILEGES ON TABLE table1 FROM USER usera C. REVOKE ALL PRIVILEGES ON TABLE table1 COLUMNS (col1, col2) FROM usera D. REVOKE ALL PRIVILEGES ON TABLE table1 COLUMNS (col1, col2) FROM USER usera 15. Given the two table definitions : ORG Deptnumb Hadaway Deptname CHAR (30) Manager Hadaway Division CHAR (30) Location CHAR (30) STAFF Id Hadaway Name CHAR (30) Dept Hadaway Job CHAR (20) Years Hadaway Salary DECIMAL (10,2) Commemoration. DECIMAL (10,2) Which of the following statements will display each department, alphabetically by name. and the name of the manager of the department? A. SELECT a.deptname, b.name FROM version of a, b WHERE a.manager=b.id staff B. SELECT a.deptname, b.name FROM version of a, b WHERE b.manager=a.id staff C. SELECT a.deptname, b.name FROM version of a, b WHERE a.manager=b.id GROUP BY a.deptname staff, b.name D. SELECT a.deptname, b.name FROM version of a, b WHERE b.manager=a.id GROUP BY a.deptname staff, b.name 16. Given the following tables : NAMES : Name Number Wayne Gretzky 99 Jaromir Jagr 68 Bobby Orr 4 Bobby Hull 23 Brett Hull 16 Mario Lemieux 66 Steve Yzerman 19 Claude Lemieux 19 Mark Messier 11 Mats Sundin 13 POINTS Name Points Wayne Gretzky 244 Jaromir Jagr 168 Bobby Orr 129 Bobby Hull 93 Brett Hull 121 Mario Lemieux 189 PIM Name PIM Mats Sundin 14 Jaromir Jagr 18 Bobby Orr 12 Mark Messier 32 Brett Hull 66 Mario Lemieux 23 Joe Sakic, 94 Which of the following statements will display the player 's names, numbers, points, and PIM for all players with an entry in all three tables? A. SELECT names.name, names.number, points.points. FROM INNER JOIN points pim.pim names names.name=points.name ON INNER JOIN ON pim.name=names.name pim B. SELECT names.name, names.number, points.points. ON OUTER JOIN points names.name=points.name names pim.pim FROM OUTER JOIN ON pim.name=names.name pim C. SELECT names.name, names.number, points.points. FROM LEFT OUTER JOIN points pim.pim names names.name=points.name ON LEFT OUTER JOIN ON pim.name=names.name pim D. SELECT names.name, names.number, points.points. pim.pim names RIGHT FROM OUTER JOIN points names.name=points.name RIGHT ON OUTER JOIN ON pim.name=names.name pim 17. Given the following table definitions : DEPARTMENT Deptno CHAR (3) Deptname CHAR (30) Mgrno Hadaway Admrdept CHAR (3) EMPLOYEE Empno Hadaway Firstname CHAR (30) Midinit CHAR Lastname CHAR (30) Workdept CHAR (3) Which of the following statements will list every employee 's name and last name with the employee number and last name of their manager, including employees without a manager? A. SELECT e.empno, e.lastname, m.empno. m.lastname FROM INNER JOIN department employee e LEFT ON INNER JOIN employee mgrno = m = deptno m.empno ON e.workdept B. SELECT e.empno, e.lastname, m.empno. m.lastname FROM OUTER JOIN department employee e LEFT ON INNER JOIN employee mgrno = m = deptno m.empno ON e.workdept C. SELECT e.empno, e.lastname, m.empno. m.lastname FROM OUTER JOIN department employee e RIGHT ON INNER JOIN employee mgrno = m = deptno m.empno ON e.workdept D. SELECT e.empno, e.lastname, m.empno. m.lastname FROM INNER JOIN department employee e RIGHT ON INNER JOIN employee mgrno = m = deptno m.empno ON e.workdept 18. Given the table : STAFF ID LASTNAME Jones 1 Smith 2 When issuing the query "SELECT * FROM staff", the row return order will be based on which of the following? A. An ambiguous order B. The primary key order C. The order that the rows were inserted into the table D. The values for the ID column, then the LASTNAME column 19. Given the table : STAFF ID LASTNAME Jones 1 Smith 2 3 "null>; Which of the following statements removes all rows from the table where there is a NULL value for LASTNAME? A. Base DELETE FROM staff WHERE IS NULL lastname B. Base DELETE FROM staff WHERE lastname = 'NULL' C. Base DELETE ALL FROM staff WHERE IS NULL lastname D. Base DELETE ALL FROM staff WHERE lastname = 'NULL' 20. Given the tables : COUNTRY ID NAME Person CITIES 1 Argentina 1 10 Canada 2 2 20 Cuba 3 2 10 4 Germany 1 0 France 5 7 5 STAFF ID LASTNAME Jones 1 Smith 2 Which of the following statements removes the rows from the table that have PERSONS in the COUNTRY STAFF table? A. Base DELETE FROM country WHERE id IN (SELECT id FROM staff) B. Base DELETE FROM country WHERE id IN (SELECT FROM staff person) C. Base DELETE FROM WHERE country person IN (SELECT id FROM staff) D. Base DELETE FROM WHERE country person IN (SELECT FROM staff person) [/code] [ Closed window ]