Introduction to SQL

Question 1

Given the following tables:
YEAR_2006
EMPID NAME
---------------------------------
1 Jagger, Mick
2 Richards, Keith
3 Wood, Ronnie
4 Watts, Charlie
5 Jones, Darryl
6 Leavell, Chuck
YEAR_1962
EMPID NAME
-------------------------------
1 Jagger, Mick
2 Richards, Keith
3 Jones, Brian
4 Wyman, Bill
5 Chapman, Tony
6 Stewart, Ian
If the following SQL statement is executed, how many rows will be returned?
SELECT name FROM year_2006
UNION
SELECT name FROM year_1962

Accepted Answer

10

Answer

0

Answer

6

Answer

12

Question 2

Given the following table definition:
SALES
---------------------------------------------
INVOICE_NO CHAR(20) NOT NULL
SALES_DATE DATE
SALES_PERSON CHAR(20)
REGION CHAR(20)
SALES INTEGER
If the following SELECT statement is executed, which of the following describes the order of the rows
in the result data set produced?
SELECT * FROM sales

Accepted Answer

The rows are not sorted in any particular order.

Answer

The rows are sorted by INVOICE_NO in ascending order

Answer

The rows are sorted by INVOICE_NO in descending order.

Answer

The rows are ordered based on when they were inserted into the table.

Question 3

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?

Accepted Answer

INSERT INTO current_employees (name, salary) VALUES (SELECT name,
salary FROM past_employees WHERE empid = 20)

Answer

INSERT INTO current_employees (empid) VALUES (10)

Answer

INSERT INTO current_employees VALUES (10, 'JAGGER', 85000.00)

Answer

INSERT INTO current_employees SELECT empid, name, salary FROM
past_employees WHERE empid = 20

Question 4

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?

Accepted Answer

DELETE FROM sales WHERE YEAR(sales_date) = 1995

Answer

DELETE * FROM sales WHERE YEAR(sales_date) = 1995

Answer

DROP * FROM sales WHERE YEAR(sales_date) = 1995

Answer

DROP FROM sales WHERE YEAR(sales_date) = 1995

Question 5

Given the following two tables:
NAMES POINTS
------------------------ ------------------------
NAME NUMBER NAME POINTS
------------- ------- ------------- -------
Wayne Gretzky 99 Wayne Gretzky 244
Jaromir Jagr 68 Jaromir Jagr 168
Bobby Orr 4 Bobby Orr 129
Bobby Hull 23 Brett Hull 121
Brett Hull 16 Mario Lemieux 189
Mario Lemieux 66 Joe Sakic 94
Mark Messier 11
Which of the following statements will display the player name, number, and points for all players that
have scored points?

Accepted Answer

SELECT p.name, n.number, p.points FROM names n
RIGHT OUTER JOIN points p ON n.name = p.name

Answer

SELECT p.name, n.number, p.points FROM names n
INNER JOIN points p ON n.name = p.name

Answer

SELECT p.name, n.number, p.points FROM names n
LEFT OUTER JOIN points p ON n.name = p.name

Answer

SELECT p.name, n.number, p.points FROM names n
FULL OUTER JOIN points p ON n.name = p.name

Question 6

Given the following two tables:
NAMES POINTS
------------------------ ------------------------
NAME NUMBER NAME POINTS
------------- ------- ------------- -------
Wayne Gretzky 99 Wayne Gretzky 244
Jaromir Jagr 68 Bobby Orr 129
Bobby Orr 4 Brett Hull 121
Bobby Hull 23 Mario Lemieux 189
Mario Lemieux 66 Joe Sakic 94
How many rows would be returned using the following statement?
SELECT name FROM names, points

Accepted Answer

25

Answer

0

Answer

5

Answer

10

Question 7

If the following SQL statements are executed:
CREATE TABLE make (makeid SMALLINT NOT NULL PRIMARY KEY,
make VARCHAR(25));
CREATE TABLE model (modelid SMALLINT,
model VARCHAR(25),
makeid SMALLINT,
CONSTRAINT const1 FOREIGN KEY (makeid)
REFERENCES make(makeid) ON DELETE RESTRICT);
And each table created is populated as follows:
MAKE MODEL
MAKEID MAKE MODELID MODEL MAKEID
------ --------- ------- ------- --------
1 Ford 1 Mustang 1
2 Chevrolet 2 Escort 1
3 Toyota 3 Malibu 2
4 Camry 3
If the following SQL statement is executed:
DELETE FROM make WHERE makeid = 1
What is the total number of rows that will be deleted?

Accepted Answer

0

Answer

1

Answer

2

Answer

3

Question 8

Which of the following allows you to modify records in a table?

Accepted Answer

UPDATE

Answer

SELECT

Answer

INSERT

Answer

DELETE

Question 9

Which of the following is TRUE about SQL?

Accepted Answer

DML is used to access data

Answer

DDL is used to control access to database and database objects

Answer

DCL is used to group DML statements into transactions

Answer

TCL is used to define properties of data objects

Question 10

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?

Accepted Answer

The statement will only succeed if the data retrieved by the subquery does not contain multiple
records.

Answer

The statement will fail because an UPDATE statement cannot contain a subquery.

Answer

The statement will succeed; if the data retrieved by the subquery contains multiple records, only the
first record will be used to perform the update.

Answer

The statement will only succeed if every record in the EMPLOYEES table has a null value in the
WORKDEPT column.

Question 11

Given the following table:
TAB1 RESULT
COL1 COL2 COL1 COL2
----- ----- ----- -----
A 10 A 10
B 20 B 20
C 30 C 30
A 10 D 40
D 40
C 30
Which of the following statements will produce the desired results?

Accepted Answer

SELECT DISTINCT * FROM tab1

Answer

SELECT UNIQUE * FROM tab1

Answer

SELECT UNIQUE(*) FROM tab1

Answer

SELECT DISTINCT(*) FROM tab1

Question 12

Given the following table definition:
SALES
---------------------------------------
INVOICE_NO CHAR(20) NOT NULL
SALES_DATE DATE
SALES_PERSON VARCHAR(25)
REGION CHAR(20)
SALES_AMT DECIMAL(9,2)
Which of the following queries will return SALES information, sorted by SALES_PERSON, from A to Z,
and SALES_DATE, from most recent to earliest?

Accepted Answer

SELECT invoice_no, sales_person, sales_date, sales_amt FROM sales ORDER BY sales_person,
sales_date DESC

Answer

SELECT invoice_no, sales_person, sales_date, sales_amt FROM sales SORT BY sales_person,
sales_date DESC

Answer

SELECT invoice_no, sales_person, sales_date, sales_amt FROM sales SORT BY sales_person
DESC, sales_date

Answer

SELECT invoice_no, sales_person, sales_date, sales_amt FROM sales ORDER BY sales_person
DESC, sales_date

Question 13

Given the following two tables:
TAB1 TAB2
---------------------- -------------------
COL_1 COL_2 COL_A COL_B
----- ----- ----- -----
A 10 A 21
B 12 C 23
C 14 D 25
Assuming the following results are desired:
COL_1 COL_2 COL_A COL_B
A 10 A 21
B 12 - -
C 14 C 23
- - D 25

Accepted Answer

SELECT * FROM tab1 FULL OUTER JOIN tab2 ON col_1 = col_a

Answer

SELECT * FROM tab1 INNER JOIN tab2 ON col_1 = col_a

Answer

SELECT * FROM tab1 LEFT OUTER JOIN tab2 ON col_1 = col_a

Answer

SELECT * FROM tab1 RIGHT OUTER JOIN tab2 ON col_1 = col_a

Question 14

If table TAB1 is created using the following statement:
CREATE TABLE tab1 (col1 INTEGER NOT NULL,
col2 CHAR(5),
CONSTRAINT cst1 CHECK (col1 in (1, 2, 3)))
Which of the following statements will successfully insert a record into table TAB1?

Accepted Answer

INSERT INTO tab1 VALUES (ABS(2), 'abc')

Answer

INSERT INTO tab1 VALUES (0, 'abc')

Answer

INSERT INTO tab1 VALUES (NULL, 'abc')

Answer

INSERT INTO tab1 VALUES (DEFAULT, 'abc')

Introduction to SQL

Create a free account to finish this quiz

Questions in this quiz (14)

Information

Feedback

What do you think of the new look?