NEW
Font size
WorksheetsISYS6892003
Total questions: 40
Worksheet time: 21mins
What is a subquery?
A type of join
A query within another query
A view definition
A sequence generator
A synonym
Which query finds names that end with 'son'?
SELECT * FROM employees WHERE name LIKE '%son%';
SELECT * FROM employees WHERE name LIKE 'son%';
SELECT * FROM employees WHERE name REGEXP_LIKE(name, 'son$');
SELECT * FROM employees WHERE name LIKE '%son';
SELECT * FROM employees WHERE name IN 'son';
Which of the following best defines a view?
A virtual table based on a query
A physical copy of a table
A stored procedure
A trigger function
A synonym for a column
Which query performs an inner join between orders and customers on customer_id?
SELECT * FROM orders JOIN customers ON orders.id = customers.id;
SELECT * FROM orders FULL JOIN customers;
SELECT * FROM orders NATURAL JOIN customers;
SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id;
SELECT * FROM orders, customers WHERE orders.id = customers.id;
What does the UPPER function do in SQL?
Converts all characters to lowercase
Capitalizes the first letter of each word
Removes spaces from a string
Converts all characters to uppercase
Replaces NULL values
Which query creates a sequence starting at 100 and incrementing by 10?
CREATE SYNONYM seq_id FOR 100 INCREMENT 10;
CREATE SEQUENCE seq_id START WITH 100 INCREMENT BY 10;
SEQUENCE seq_id BEGIN 100 STEP 10;
CREATE SEQUENCE seq_id VALUES 100 TO 1000;
START SEQUENCE seq_id AT 100 BY 10;
Which group function returns the highest value in a column?
MIN
AVG
SUM
MAX
COUNT
Which query calculates the average salary per department?
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;
SELECT AVG(salary) FROM employees;
SELECT department_id, AVG(salary) FROM employees;
SELECT department_id, salary FROM employees GROUP BY salary;
SELECT AVG(salary) GROUP BY department_id FROM employees;
Which function is used to search a string with a pattern?
SUBSTR
REGEXP_LIKE
TO_CHAR
UPPER
NVL
Which query returns employees who earn more than the average salary?
SELECT * FROM employees WHERE salary = AVG(salary);
SELECT AVG(salary) FROM employees WHERE salary > 5000;
SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
SELECT salary FROM employees HAVING salary > AVG(salary);
SELECT * FROM employees JOIN (SELECT AVG(salary) FROM employees);
What is a sequence used for in Oracle SQL?
To store temporary data
To create joins
To sort records
To generate unique numeric values
To define foreign keys
Which query creates a view showing employees with salary over 10000?
VIEW high_salary_employees AS SELECT * FROM employees WHERE salary > 10000;
CREATE VIEW FROM employees WHERE salary > 10000;
CREATE VIEW high_salary_employees AS SELECT * FROM employees WHERE salary > 10000;
SELECT * INTO high_salary_employees FROM employees WHERE salary > 10000;
CREATE VIEW high_salary_employees WHERE salary > 10000;
Which type of join returns only matching rows from both tables?
CROSS JOIN
FULL OUTER JOIN
RIGHT JOIN
LEFT JOIN
INNER JOIN
Which query returns the uppercase version of all employee names?
SELECT LOWER(name) FROM employees;
SELECT UPPER(name) FROM employees;
SELECT INITCAP(name) FROM employees;
SELECT REPLACE(name, 'a', 'A') FROM employees;
SELECT SUBSTR(name, 1) FROM employees;
Which of the following is used to replace NULL values in Oracle SQL?
NVL
SUBSTR
COALESCE
INITCAP
REPLACE
Which query returns employees whose name starts with 'J'?
SELECT * FROM employees WHERE name LIKE '%J';
SELECT * FROM employees WHERE name = 'J%';
SELECT * FROM employees WHERE name CONTAINS 'J';
SELECT * FROM employees WHERE name STARTS WITH 'J';
SELECT * FROM employees WHERE name REGEXP_LIKE(name, '^J');
What is the purpose of a synonym in Oracle?
To define user privileges
To create an alias for a database object
To create foreign keys
To define views
To create triggers
Which function returns the number of rows in a table?
SUM()
MAX()
AVG()
LENGTH(*)
COUNT()
Which query creates a synonym for the employees table?
CREATE ALIAS emp AS employees;
DEFINE SYNONYM employees AS emp;
CREATE SYNONYM emp FOR employees;
SET SYNONYM emp TO employees;
RENAME employees TO emp;
Which of the following is not a group function?
LENGTH
MAX
MIN
AVG
SUM
Which query selects departments having more than 10 employees?
SELECT department_id FROM employees WHERE COUNT(*) > 10;
SELECT department_id GROUP BY COUNT(*) > 10;
SELECT department_id HAVING COUNT(*) > 10;
SELECT department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 10;
SELECT department_id FROM employees COUNT(*) > 10;
What is the purpose of the INITCAP function in SQL?
Converts text to uppercase
Capitalizes the first letter of each word in a string
Converts text to lowercase
Returns the number of characters
Trims white space
Which query creates a view of employees in department 10?
CREATE VIEW employees WHERE department_id = 10;
CREATE VIEW dept10_employees AS employees IN department 10;
VIEW dept10_employees FROM employees WHERE department_id = 10;
CREATE VIEW dept10_employees AS SELECT * FROM employees WHERE department_id = 10;
CREATE VIEW dept10_employees FROM employees WHERE department_id = 10;
Which query shows all employees who work in the same department as 'John'?
SELECT * FROM employees WHERE name = 'John' AND department_id IS NOT NULL;
SELECT FROM employees WHERE EXISTS (SELECT FROM employees WHERE name = 'John');
SELECT * FROM employees WHERE department_id = (SELECT department_id FROM employees WHERE name = 'John');
SELECT * FROM employees JOIN departments ON employees.department_id = departments.id WHERE name = 'John';
SELECT * FROM employees WHERE department_id IN (name = 'John');
Which function returns the number of characters in a string?
LENGTH
COUNT
CHAR
MAX
WIDTH
Which query joins the orders and customers tables using a natural join?
SELECT * FROM orders NATURAL JOIN customers;
SELECT * FROM orders INNER JOIN customers ON orders.id = customers.id;
SELECT * FROM orders FULL JOIN customers ON orders.customer_id = customers.customer_id;
SELECT * FROM orders, customers WHERE orders.customer_id = customers.customer_id;
SELECT * FROM orders LEFT OUTER JOIN customers;
Which of the following is the correct use of the COALESCE function?
SELECT COALESCE('Hello', NULL, NULL) AS greeting;
SELECT COALESCE(NULL, NULL, 'Hello') FROM dual;
SELECT COALESCE FROM table_name;
SELECT * FROM COALESCE('x', 'y');
COALESCE('a', 'b') SELECT * FROM dual;
Which query counts the number of employees in each department?
SELECT COUNT(department_id) FROM employees;
SELECT COUNT(*) GROUP BY department_id FROM employees;
SELECT department_id, COUNT(*) FROM employees;
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;
SELECT COUNT(*) FROM employees WHERE department_id IS NOT NULL;
Which of the following statements about views is TRUE?
Views can insert directly into sequences
Views always contain indexes
Views cannot be queried
Views do not store data physically
Views are faster than tables
Which function combines first and last name with a space in between?
SELECT CONCAT(first_name, last_name) FROM employees;
SELECT first_name || ' ' || last_name FROM employees;
SELECT first_name + last_name FROM employees;
SELECT CONCAT_WS(' ', first_name, last_name) FROM employees;
SELECT first_name & ' ' & last_name FROM employees;
What does the following subquery return?
SELECT * FROM employees WHERE salary > (SELECT MAX(salary) FROM employees WHERE department_id = 30);
All employees in department 30
Employees with the highest salary
Employees earning less than average
Employees earning more than the highest salary in department 30
Employees with NULL salary
Which query returns all employees not in department 50?
SELECT * FROM employees WHERE department_id != 50;
SELECT * FROM employees WHERE department_id = 50;
SELECT * FROM employees WHERE department_id NOT IN 50;
SELECT * FROM employees EXCEPT department_id = 50;
SELECT * FROM employees WHERE department_id <> 50;
Which clause is used with GROUP BY to filter grouped rows?
WHERE
HAVING
ORDER BY
SELECT
GROUP FILTER
Which query creates a sequence that increments by 1 and starts at 1?
CREATE SEQUENCE seq1 BEGIN 1 STEP 1;
CREATE SEQUENCE seq1 START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE seq1 VALUES (1, 1);
SEQUENCE seq1 START = 1 INCREMENT = 1;
CREATE NEW SEQUENCE seq1 STARTING AT 1;
What does the TO_CHAR function do in Oracle SQL?
Converts text to date format
Changes the case of a character
Converts a number or date to a string
Replaces characters in a string
Trims white space
Which query shows the highest salary in each department?
SELECT department_id, MAX(salary) FROM employees GROUP BY department_id;
SELECT department_id, salary FROM employees WHERE salary = MAX(salary);
SELECT MAX(salary) FROM employees;
SELECT department_id FROM employees GROUP BY MAX(salary);
SELECT * FROM employees GROUP BY department_id MAX(salary);
What is the result of this query?
SELECT NVL(commission_pct, 0) FROM employees;
Deletes NULL commission values
Shows only non-NULL commissions
Filters rows with 0 commission
Replaces salary with 0
Replaces NULL commission values with 0
What does the DISTINCT keyword do?
Sorts the data
Converts NULL to a value
Filters based on condition
Removes duplicate rows from the result
Reverses the result order
What does this query do?
SELECT * FROM employees WHERE REGEXP_LIKE(email, '^[a-zA-Z0-9._%+-]+@oracle.com$');
Validates phone numbers
Filters emails ending with @oracle.com using regular expressions
Finds employees without email addresses
Changes email domain to oracle.com
Converts email to lowercase
Which query lists all departments that have no employees?
SELECT FROM departments WHERE NOT EXISTS (SELECT FROM employees);
SELECT * FROM departments WHERE COUNT(employees) = 0;
SELECT department_id FROM employees GROUP BY department_id HAVING COUNT(*) = 0;
SELECT * FROM departments WHERE department_id = 0;
SELECT * FROM departments WHERE department_id NOT IN (SELECT DISTINCT department_id FROM employees);
