Oracle Advanced SQL and PL/SQL Developer Certification Questions and Answers (Dumps and Practice Questions)
Question : Examine the create table statements for the stores and sales tables.
SQL> CREATE TABLE stores(store_id NUMBER(4) CONSTRAINT store_id_pk PRIMARY KEY, store_name VARCHAR2(12), store_address VARCHAR2(20), start_date DATE);
SQL> CREATE TABLE sales(sales_id NUMBER(4) CONSTRAINT sales_id_pk PRIMARY KEY, item_id NUMBER(4), quantity NUMBER(10), sales_date DATE, store_id NUMBER(4),
CONSTRAINT store_id_fk FOREIGN KEY(store_id) REFERENCES stores(store_id));
You executed the following statement:
SQL> DELETE from stores
WHERE store_id=900;
The statement fails due to the integrity constraint error:
ORA-02292: integrity constraint (HR.STORE_ID_FK) violated
Which three options ensure that the statement will execute successfully?
A. Disable the primary key in the STORES table.
B. Use CASCADE keyword with DELETE statement.
C. DELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from STORES table.
D. Disable the FOREIGN KEY in SALES table and then delete the rows.
E. Create the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE option.
1. A,C,D
2. C,D,E
3. B,C,E
4. C,D,E
5. A,C,E
Correct Answer : 1 Exp: The relationship between foreign key and parent key has implications for deletion of parent keys. For example, if a user attempts to delete the record for this department, then what happens to the records for employees in this department? When a parent key is modified, referential integrity constraints can specify the following actions to be performed on dependent rows in a child table:
No action on deletion or update
In the normal case, users cannot modify referenced key values if the results would violate referential integrity. For example, if employees.department_id is a foreign key to departments, and if employees belong to a particular department, then an attempt to delete the row for this department violates the constraint.
Cascading deletions: A deletion cascades (DELETE CASCADE) when rows containing referenced key values are deleted, causing all rows in child tables with dependent foreign key values to also be deleted. For example, the deletion of a row in departments causes rows for all employees in this department to be deleted.
Deletions that set null: A deletion sets null (DELETE SET NULL) when rows containing referenced key values are deleted, causing all rows in child tables with dependent foreign key values to set those values to null. For example, the deletion of a department row sets the department_id column value to null for employees in this department.
Question : You want to create a sales
table with the following column
specifications and data types:
SALESID: Number
STOREID: Number
ITEMID: Number
QTY: Number, should be set to 1 when no value is specified
SLSDATE: Date, should be set to current date when no value is specified
PAYMENT: Characters up to 30 characters, should be set to CASH when no value is specified
Which statement would create the table?
1. A
2. B
3. C
4. D
Correct Answer : 2
Explanation:
Question : Examine the data in the CUST_NAME column of the customers table.
You need to display customers' second names where the second name starts with "Mc" or "MC."
Which query gives the required output?
1. A
2. B
3. C
4. D
Correct Answer : 2
Explanation: INITCAP returns char, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric.
char can be of any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The return value is the same datatype as char. The database sets the case of the initial characters based on the binary mapping defined for the underlying character set. For linguistic-sensitive uppercase and lowercase, please refer to NLS_INITCAP.
This function does not support CLOB data directly. However, CLOBs can be passed in as arguments through implicit data conversion.
The following example capitalizes each word in the string:
SELECT INITCAP('the soap') "Capitals" FROM DUAL;
Capitals
---------
The Soap
Related Questions
Question : What is/are correct statement for the following query
SELECT p.product_id, p.product_name, categories.category_name
FROM products p
INNER JOIN categories
ON p.category_id = categories.category_id
ORDER BY p.product_name ASC, categories.category_name ASC;
1. You can not use Alias in Order by clause
2. You can not use Alias in JOIN clause
3. When you use Alias, use for all the tables
4. None of the above
Question : Which type of Join illustrated in the image. (Blue color is for selected data)
1. Right
2. Left
3. Inner
4. None of the above
Question : Select the equivalent statement of below query
SELECT *
FROM customers
WHERE customer_id NOT BETWEEN 3000 AND 3500;
1. SELECT *
FROM customers
WHERE customer_id < 3000
OR customer_id > 3500;
2. SELECT *
FROM customers
WHERE customer_id = < 3000
OR customer_id > 3500;
3. SELECT *
FROM customers
WHERE customer_id < 3000
OR customer_id >= 3500;
4. None of the above
Question : Select correct sql statement
1. INSERT INTO suppliers
(supplier_id supplier_name)
SELECT account_no, name
FROM customers
WHERE customer_id > 5000;
2. INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no || name
FROM customers
WHERE customer_id > 5000;
3. INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE customer_id > 5000;
4. All of the above
Question : What result is returned by the following statement?
SELECT COUNT(*) FROM DUAL;
1. 0
2. 1
3. Access Mostly Uused Products by 50000+ Subscribers
4. infinite
Question : Select the correct statement ...
1. Group functions may only be used when a GROUP BY clause is present.
2. Group functions can operate on multiple rows at a time.
3. Access Mostly Uused Products by 50000+ Subscribers
4. Group functions can execute multiple times within a single group.