Complete Subject Question Bank
Tom has designed a payroll software for XYZ technology.The sofware will store the salary details into the database and later he can retreive the same for future references. Tom falls under which category of user.
Tom writes application code that stores and retrieves data — he is an Application Programmer.
In SQL is a case sensitive language and the data stored inside the table are case in-sensitive. Say true or false?
SQL keywords are case-insensitive; data values are case-sensitive by default in most RDBMS.
Which of the following is not a valid relational database?
XML databases are hierarchical/document stores, not relational databases.
Database is a shared collection of logically unrelated data, designed to meet the needs of an organization. State True or False.
A database is a collection of logically RELATED data — the statement is false.
Which of the following represents the degree of the relation?
The degree of a relation is its number of attributes.
A table consists of ______ primary keys.
Each table has exactly one primary key (which may be composite).
We need to ensure that the amount withdrawn should be less then the credit card limit amount, to ensure this integrity what type constraint will be used?
A CHECK constraint enforces domain integrity rules like withdrawal limits.
Which of the following options is not correct?
The correct syntax is ALTER TABLE emp DROP COLUMN salary; — missing the COLUMN keyword.
An emp table contains fields employ name, desig and salary. How do you drop column salary?
The correct DDL is: ALTER TABLE emp DROP COLUMN salary;
Which of the following is not modification of the database
XML databases are hierarchical/document stores, not relational databases.
In a relational database a referential integrity constraint can be done using
Foreign Keys enforce referential integrity between tables.
___________ removes data from the table, but structure remains the same.
TRUNCATE removes all rows but preserves the table structure; it is a DDL command.
A relational database consists of a collection of
A relational database organises data into tables (relations).
Column header is referred as
A column header in a relational table is called an attribute.
Consider the below table structure: Column Name DataType Constraint Empname Varchar(20) Not Null EmpId int(10) PK Phoneno bigint(10) Not Null insert into employee(empid,empname)values('123','John'); When we issue the above insert command and if the statement fails, what would be the reason.
phoneno has a NOT NULL constraint so it must be provided. empid is numeric so no quotes needed — that part is actually correct; the real issue is only the missing phoneno value.
Examine the structure of the STUDENT table: Column Name DataType Constraint Stud_id int(3) PK Name Varchar(20) Not Null Address Varchar(30) DOB Date Which statement inserts a new row into the STUDENT table?
Providing all column values in the correct order satisfies the table constraints.
State True or False. COMMIT ends the current transaction by making all pending data changes permanent.
COMMIT permanently saves all changes made in the current transaction.
Merge is not supported by MySQL. The other possible way of doing the work of merge statement, is by using ON DUPLICATE KEY UPDATE in the insert statement. State TRUE or FALSE.
MySQL uses INSERT ... ON DUPLICATE KEY UPDATE as an alternative to MERGE.
Which of the below is a reference option for deleting rows from a table?
ON DELETE CASCADE automatically deletes child rows when the parent row is deleted.
To remove a relation from SQL database, we use ___________ command.
DROP TABLE removes the entire table structure and data from the database.
What is the Default format for Date datatype?
ISO standard is YYYY-MM-DD; Oracle default is DD-MON-RR.
Numeric datatype is used to store_______
NUMERIC/NUMBER stores integer and decimal numeric values.
Which statement about SQL is true?
In most RDBMS (Oracle, MySQL, PostgreSQL), NULL values appear last in ASC order (NULLS LAST by default).
Select the suitable option for retrieving all the employees who have a manager?
Use IS NOT NULL to filter rows where manager_id has a value.
Select the suitable option for retrieving all the employees whose last name is "kumar"?
Exact match uses = operator.
How to retrieve department_id column without any duplication from employee relation?
DISTINCT removes duplicates from the result set.
Which statement is true when a DROP TABLE command is executed on a table?
DROP TABLE is DDL — it auto-commits, rolls back pending transactions, and is irreversible without a backup.
Which statements are true regarding constraints? Select one or more: A constraint is enforced only for the INSERT operation on a table. A foreign key cannot contain NULL values. A columns with the UNIQUE constraint can contain NULL values. A constraint can be disabled even if the constraint column contains data.
UNIQUE columns can contain NULL; constraints can be disabled while data exists.
You need to remove all the data from the employee table while leaving the table definition intact. You want to be able to undo this operation. How would you accomplish this task?
DELETE is DML and can be rolled back; TRUNCATE cannot be rolled back.
The SQL statements executed in a user session as follows: create table product(pid int(10),pname varchar(10)); Insert into product values(1,'pendrive'); Insert into product values(2,'harddisk'); savepoint a; update product set pid=20 where pid=1; savepoint b; delete from product where pid=2; commit; delete from product where pid=10; Which statements describe the consequence of issuing the ROLLBACK TO SAVE POINT a command in the session?
ROLLBACK TO SAVEPOINT sp1 undoes changes after the savepoint, so only p3-eraser is removed. p1 and p2 remain.
All columns in the SELECT list that are not in group functions must be in the GROUP-BY clause. State True or False.
SQL requires non-aggregated SELECT columns to appear in GROUP BY.
Group functions can be used in the where clause. State True or False.
Group functions cannot be used in WHERE; use HAVING instead.
Single row functions can be nested to any level. State true or False.
Single-row functions can be nested to any depth in SQL.
What will be the output for the below query: select ceil(5.3) from dual;
CEIL rounds up: CEIL(5.3) = 6.
We need to create a report to display the order id, ship date and order total of your ORDER table. If the order has not been shipped, your report must display 'Not Shipped'. If the total is not available, your report must display 'Not Available'. In the ORDER table, the SHIPDATE column has a datatype of DATE. The TOTAL column has a datatype of INT. Which statement do you use to create this report?
IFNULL(expr, replacement) returns the replacement when expr is NULL. Both columns need IFNULL applied.
To generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed. Which SQL statement would produce the required result?
NVL with TO_CHAR conversion is correct Oracle syntax: NVL(TO_CHAR(expr), 'Not Available').
To display the names of employees that are not assigned to a department. Evaluate this SQL statement: SELECT last_name, first_name FROM employee WHERE dept_id = NULL; Which change should you make to achieve the desired result?
NULL comparisons require IS NULL / IS NOT NULL. Using = NULL always evaluates to UNKNOWN.
Which statement is true regarding the default behavior of the ORDER BY clause?
The default ORDER BY sort order is ascending (ASC).
ABC company wants to give each employee a $100 salary increment. You need to evaluate the results from the EMP table prior to the actual modification. If you do not want to store the results in the database, which statement is valid?
To preview salary increments, use arithmetic in the SELECT clause: SELECT salary + 100 FROM employee;
Select the suitable option for fetching the output of the following query. select substr("Oracle World",1,6) from dual;
SUBSTR('Oracle World', 1, 6) starts at position 1 and takes 6 characters = 'Oracle'.
Which operator is NOT appropriate in the join condition of a non-equi join SELECT statement?
Non-equi joins use non-equality operators; = is for equi-joins.
In which cases would you use an outer join?
Outer joins return matched and unmatched rows from one or both tables.
To display the names of employees who earns more than the average salary of all employees. SELECT last_name, first_name FROMemployee WHEREsalary > AVG(salary); Which change should you make to achieve the desired results?
A correlated subquery in WHERE is needed to compare each salary to the average.
To create a report displaying employee last names, department names, and locations. Which query should you use to create an equi- join?
Refer to Database Management — 07 Joins & Subquery study materials.
Which statement would display the highest credit limit available in each income level in each city in the Customers table?
GROUP BY both city and income_level with MAX gives the required result.
Which SQL statement produces an error?
The HAVING clause is incomplete — no value after '>', causing a syntax error.
The COMMISSION column shows the monthly commission earned by the employee. Emp_Id Dept_Id Commission 1 10 500 2 20 1000 3 10 4 10 600 5 30 800 6 30 200 7 10 8 20 300 Which tasks would require sub queries or joins in order to be performed in a single step? Select one or more: Listing the employees who earn the same amount of commission as employee 3 Listing the employees whose annual commission is more than 6000 Listing the departments whose average commission is more that 600 Listing the employees who do not earn commission and who are working for department 20 in descending order of the employee ID Finding the total commission earned by the employees in department 10 Finding the number of employees who earn a commission that is higher than the average commission of the company
Tasks requiring subqueries: finding employees matching employee 3's commission (correlated subquery) and finding employees above average commission (scalar subquery).
What statement would display the age of Customers with the alias name as AGE?
Age is calculated from the date of birth using date arithmetic, aliased as AGE.
Which SQL statement would you use to remove a view called EMP_DEPT_VU from your schema?
DROP VIEW is the correct SQL syntax to remove a view.
CREATE INDEX emp_dept_id_idx ON employee(dept_id); Which of the following statements are true with respect to the above index?
An index on dept_id speeds up SELECT queries filtering by that column by reducing disk I/O.
An owner can give specific privileges on the owner's objects to others. State True or False.
Object owners can GRANT specific privileges to other users.
The owner has all the privileges on the object. State true or False.
An object owner automatically has all privileges on that object.
Which SQL statement grants a privilege to all the database users?
GRANT ... TO PUBLIC gives the privilege to all current and future users.
Equijoin is called as ______.
An equijoin uses the = operator and is also called an inner/simple join.
_____ is used to retrieve records that do not meet the join condition
Outer joins return rows that do not satisfy the join condition (unmatched rows).
Joining a table to itself is called as ______.
A self join joins a table to itself using aliases.
The _______ join is based on all columns in the two tables that have the same data type.
NATURAL JOIN automatically joins on all columns with matching names and data types.
The _______ join produces the cross product of two tables.
A CROSS JOIN produces the Cartesian product of two tables.
Tom has designed a payroll software for XYZ technology.The sofware will store the salary details into the database and later he can retreive the same for future references. Tom falls under which category of user.
Tom writes application code that stores and retrieves data — he is an Application Programmer.
Which of the following is not a valid relational database?
XML databases are hierarchical/document stores, not relational databases.
SQL is a case sensitive language and the data stored inside the table are case in-sensitive. Say true or false?
SQL keywords are case-insensitive; data values are case-sensitive by default.
Which of the following represents the degree of the relation?
The degree of a relation is its number of attributes.
Database is a shared collection of logically unrelated data, designed to meet the needs of an organization. State True or False.
A database is a collection of logically RELATED data — the statement is false.
We need to ensure that the amount withdrawn should be less then the credit card limit amount, to ensure this integrity what type constraint will be used?
A CHECK constraint enforces domain integrity rules like withdrawal limits.
A table consists of ______ primary keys.
Each table has exactly one primary key (which may be composite).
An emp table contains fields employ name, desig and salary. How do you drop column salary?
The correct DDL is: ALTER TABLE emp DROP COLUMN salary;
Which of the following options is not correct?
The correct syntax is ALTER TABLE emp DROP COLUMN salary; — missing the COLUMN keyword.
Which of the following is not modification of the database
Sorting (ORDER BY) is a retrieval operation, not a data modification.
A relational database consists of a collection of
A relational database organises data into tables (relations).
Column header is referred as
A column header in a relational table is called an attribute.
___________ removes data from the table, but structure remains the same.
TRUNCATE removes all rows but preserves the table structure; it is a DDL command.
In a relational database a referential integrity constraint can be done using
Foreign Keys enforce referential integrity between tables.
Merge is not supported by MySQL. The other possible way of doing the work of merge statement, is by using ON DUPLICATE KEY UPDATE in the insert statement. State TRUE or FALSE.
MySQL uses INSERT ... ON DUPLICATE KEY UPDATE as an alternative to MERGE.
Consider the below table structure: Column Name DataType Constraint Empname Varchar(20) Not Null EmpId int(10) PK Phoneno bigint(10) Not Null insert into employee(empid,empname)values('123','John'); When we issue the above insert command and if the statement fails, what would be the reason.
phoneno has a NOT NULL constraint so it must be provided. The statement fails because phoneno is missing.
Examine the structure of the STUDENT table: Column Name DataType Constraint Stud_id int(3) PK Name Varchar(20) Not Null Address Varchar(30) DOB Date Which statement inserts a new row into the STUDENT table?
Providing all column values in the correct order satisfies the table constraints.
State True or False. COMMIT ends the current transaction by making all pending data changes permanent.
COMMIT permanently saves all changes made in the current transaction.
Numeric datatype is used to store_______
NUMERIC/NUMBER stores integer and decimal numeric values.
Which of the below is a reference option for deleting rows from a table?
ON DELETE CASCADE automatically deletes child rows when the parent row is deleted.
To remove a relation from SQL database, we use ___________ command.
DROP TABLE removes the entire table structure and data from the database.
What is the Default format for Date datatype?
ISO standard is YYYY-MM-DD; Oracle default is DD-MON-RR.
How to retrieve department_id column without any duplication from employee relation?
DISTINCT removes duplicates from the result set.
Select the suitable option for retrieving all the employees who have a manager?
Use IS NOT NULL to filter rows where manager_id has a value.
Select the suitable option for retrieving all the employees whose last name is "kumar"?
Exact match uses = operator.
Which statement about SQL is true?
In most RDBMS (Oracle, MySQL, PostgreSQL), NULL values appear last in ASC order (NULLS LAST by default).
Which statement is true when a DROP TABLE command is executed on a table?
DROP TABLE is DDL — it auto-commits and cannot be rolled back.
Which statements are true regarding constraints? Select one or more: Af i k t t i NULL l A foreign key cannot contain NULL values. A constraint is enforced only for the INSERT operation on a table. A columns with the UNIQUE constraint can contain NULL values. A constraint can be disabled even if the constraint column contains data.
UNIQUE columns can contain NULL; constraints can be disabled while data exists.
The SQL statements executed in a user session as follows: create table product(pid int(10),pname varchar(10)); Insert into product values(1,'pendrive'); Insert into product values(2,'harddisk'); savepoint a; update product set pid=20 where pid=1; savepoint b; delete from product where pid=2; commit; delete from product where pid=10; Which statements describe the consequence of issuing the ROLLBACK TO SAVE POINT a command in the session?
ROLLBACK TO SAVEPOINT sp1 undoes changes after the savepoint, so only p3-eraser is removed. p1 and p2 remain.
You need to remove all the data from the employee table while leaving the table definition intact. You want to be able to undo this operation. How would you accomplish this task?
DELETE is DML and can be rolled back; TRUNCATE cannot.
What will be the output for the below query: select ceil(5.3) from dual;
CEIL rounds up: CEIL(5.3) = 6.
Single row functions can be nested to any level. State true or False.
Single-row functions can be nested to any depth in SQL.
All columns in the SELECT list that are not in group functions must be in the GROUP-BY clause. State True or False.
SQL requires non-aggregated SELECT columns to appear in GROUP BY.
Group functions can be used in the where clause. State True or False.
Group functions cannot be used in WHERE; use HAVING instead.
We need to create a report to display the order id, ship date and order total of your ORDER table. If the order has not been shipped, your report must display 'Not Shipped'. If the total is not available, your report must display 'Not Available'. In the ORDER table, the SHIPDATE column has a datatype of DATE. The TOTAL column has a datatype of INT. Which statement do you use to create this report?
IFNULL(expr, replacement) returns the replacement when expr is NULL. Both columns need IFNULL applied.
Select the suitable option for fetching the output of the following query. select substr("Oracle World",1,6) from dual;
SUBSTR('Oracle World', 1, 6) starts at position 1 and takes 6 characters = 'Oracle'.
To display the names of employees that are not assigned to a department. Evaluate this SQL statement: SELECT last_name, first_name FROM employee WHERE dept_id = NULL; Which change should you make to achieve the desired result?
NULL comparisons require IS NULL / IS NOT NULL. Using = NULL always evaluates to UNKNOWN.
ABC company wants to give each employee a $100 salary increment. You need to evaluate the results from the EMP table prior to the actual modification. If you do not want to store the results in the database, which statement is valid?
The correct clause to calculate salary + 100 for display is SELECT, not DISPLAY.
To generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed. Which SQL statement would produce the required result?
NVL with TO_CHAR conversion is correct Oracle syntax: NVL(TO_CHAR(expr), 'Not Available').
Which statement is true regarding the default behavior of the ORDER BY clause?
The default ORDER BY sort order is ascending (ASC).
To display the names of employees who earns more than the average salary of all employees. SELECT last_name, first_name FROMemployee WHEREsalary > AVG(salary); Which change should you make to achieve the desired results?
A correlated subquery in WHERE is needed to compare each salary to the average.
In which cases would you use an outer join? Selectone: Seec o e: The tables being joined have only unmatched data. Only when the tables have a primary key/foreign key relationship. The tables being joined have only matched data. The tables being joined have both matched and unmatched data. The tables being joined have NOT NULL columns.
Outer joins return matched and unmatched rows from one or both tables.
Which operator is NOT appropriate in the join condition of a non-equi join SELECT statement?
Non-equi joins use non-equality operators; = is for equi-joins.
Which statement would display the highest credit limit available in each income level in each city in the Customers table?
GROUP BY both city and income_level with MAX gives the required result.
What statement would display the age of Customers with the alias name as AGE?
Age is calculated from the date of birth using date arithmetic, aliased as AGE.
Which SQL statement produces an error?
The HAVING clause is incomplete — missing the value after >.
The COMMISSION column shows the monthly commission earned by the employee. Emp_Id Dept_Id Commission 1 10 500 2 20 1000 3 10 4 10 600 5 30 800 6 30 200 7 10 8 20 300 Which tasks would require sub queries or joins in order to be performed in a single step? Select one or more: Listing the employees who earn the same amount of commission as employee 3 Finding the total commission earned by the employees in department 10 Listing the departments whose average commission is more that 600 Listing the employees whose annual commission is more than 6000 Finding the number of employees who earn a commission that is higher than the average commission of the company Listing the employees who do not earn commission and who are working for department 20 in descending order of the employee ID
Tasks requiring subqueries: finding employees matching employee 3's commission (correlated subquery) and finding employees above average commission (scalar subquery).
To create a report displaying employee last names, department names, and locations. Which query should you use to create an equi-join?
Refer to Database Management — 7. Joins & Subquery study materials.
An owner can give specific privileges on the owner's objects to others. State True or False.
Object owners can GRANT specific privileges to other users.
Which SQL statement grants a privilege to all the database users?
GRANT ... TO PUBLIC gives the privilege to all current and future users.
CREATE INDEX emp_dept_id_idx ON employee(dept_id); Which of the following statements are true with respect to the above index?
An index on dept_id speeds up SELECT queries filtering by that column.
Which SQL statement would you use to remove a view called EMP_DEPT_VU from your schema?
DROP VIEW is the correct SQL syntax to remove a view.
The owner has all the privileges on the object. State true or False.
An object owner automatically has all privileges on that object.
Joining a table to itself is called as ______.
A self join joins a table to itself using aliases.
Equijoin is called as ______.
An equijoin uses the = operator and is also called an inner/simple join.
The _______ join is based on all columns in the two tables that have the same data type.
NATURAL JOIN automatically joins on all columns with matching names and data types.
The _______ join produces the cross product of two tables.
A CROSS JOIN produces the Cartesian product of two tables.
_____ is used to retrieve records that do not meet the join condition
Outer joins return rows that do not satisfy the join condition (unmatched rows).
Which command is used to change a table's behavior?
ALTER TABLE is the DDL command used to change a table's structure/behavior.
We need to ensure that the amount withdrawn should be less then the credit card limit amount, to ensure this integrity what type constraint will be used?
A CHECK constraint enforces business rules such as keeping withdrawal below the credit limit.
An emp table contains fields employ name, desig and salary. How do you drop column salary?
The correct DDL syntax is: ALTER TABLE emp DROP COLUMN salary;
How would you add a foreign key constraint on the dept_no column in the EMP table, referring to the id column in the DEPT table?
ALTER TABLE EMP ADD CONSTRAINT fk_dept FOREIGN KEY (dept_no) REFERENCES dept(dept_no);
A table consists of ______ primary keys.
Each table can have exactly one primary key (which may be composite).
__________ command is used to delete the records and _________ command is used to delete the db objects?
DELETE removes records (DML); TRUNCATE removes all rows and resets table (DDL).
Which of the following options is not correct?
The correct syntax requires the COLUMN keyword: ALTER TABLE emp DROP COLUMN salary; — DROP column_name; is invalid.
Which of the following is not modification of the database?
ORDER BY (sorting) is a retrieval operation, not a data modification.
Which of the following data models supports many-to-many relationships?
Both relational and network data models support many-to-many relationships.
Which of the following is not a part of RDBMS?
Polymorphism is an OOP concept, not part of RDBMS.
Which of these are standard and best practises for SQL?
Best practice mandates every table has a primary key to uniquely identify rows.
Which are auto-commit statements?
DROP and TRUNCATE are DDL statements that auto-commit; they cannot be rolled back.
Which of the update statement produces the following error in the employee table? ORA-02291: integrity constraint (SYS_C23) violated - parent key not found.
Setting a foreign key value to a non-existent department ID causes an integrity constraint violation.
Which of the following is not a DML statement?
DROP and ALTER are DDL statements, not DML. DML includes INSERT, UPDATE, DELETE, SELECT.
Which SQL statement needs both insert and update privilege on the target table and select privilege on the source table?
MERGE (upsert) requires both INSERT and UPDATE privileges on the target table.
On delete restrict is used to restrict the deletion of child records. State True or False.
Refer to Database Management — Data Manipulation Language study materials.
Delete statement supports up to two conditions only. State True or False
Refer to Database Management — Data Manipulation Language study materials.
Which statement inserts a new row into the STUDENT table?
Providing all required column values in the correct positional order is valid.
insert into employee(empid,empname)values(123,'John'); When we issue the above insert command and if the statement fails, what would be the reason.
phoneno has a NOT NULL constraint so it must be provided; omitting it causes an error.
DELETE FROM dept WHERE dept_id = 901; The above delete statement throws an integrity constraint error because a child record was found. What could we do to make the statement execute?
An integrity constraint error occurs when a parent record has child records. Delete children first or use ON DELETE CASCADE.
Which query is used to delete employee records whose age is greater than 55?
DELETE with a WHERE clause filters which rows are removed.
By using _____ datatype we can store numeric and character values as an input .
VARCHAR2, VARCHAR, and CHAR are character datatypes that can store both numeric and character values as text.
To permanently remove all the data from the STUDENT table, and you need the table structure in the future. Which single command performs this?
TRUNCATE permanently removes all rows but keeps the table structure intact; it cannot be rolled back.
Which statement would you use to add a primary key constraint to the patient table using the id_number column, immediately enabling the constraint?
The correct syntax is ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY(column);
Composite key is used in one to one relationship. State true or false.
Refer to Database Management — Data Manipulation Language study materials.
Which CREATE TABLE statement is valid?
The statement correctly defines columns, a DEFAULT, NOT NULL, and a composite PRIMARY KEY constraint.
Cascade constraints are used only in drop statements. State True or False.
Refer to Database Management — Data Manipulation Language study materials.
select substr("Oracle World",1,6) from dual;
SUBSTR('Oracle World', 1, 6) starts at position 1 and returns 6 characters = 'Oracle'.
ABC company wants to give each employee a $100 salary increment. You need to evaluate the results from the EMP table prior to the actual modification. If you do not want to store the results in the database, which statement is valid?
The correct clause to calculate salary + 100 for display is SELECT, not DISPLAY.
To display the names of all promos done after January 1, 2001 starting with the latest promo. Which query would give the required result? (Choose all that apply.)
The query filters by date, uses a column alias 'START DATE', and orders descending.
Which statement is true regarding the default behavior of the ORDER BY clause?
Character sort in ORDER BY is case-sensitive by default in most RDBMS.
To calculate the number of days from 1st Jan 2007 till date: Dates are stored in the default format of dd-mm-rr. Which SQL statements would give the required output?
In Oracle, subtracting two dates gives the number of days. TO_DATE converts the string to a date.
To generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed. Which SQL statement would produce the required result?
NVL(TO_CHAR(expr), 'Not Available') handles NULL: converts the number to string first so both arguments are the same type.
The CUSTOMERS table has these columns: CUSTOMER_ID NUMBER(4) NOT NULL; CUSTOMER_NAME VARCHAR2(100) NOT NULL; CUSTOMER_ADDRESS VARCHAR2(150); CUSTOMER_PHONE VARCHAR2(20); You need to produce output that states "Dear Customer customer_name, ". The customer_name data values come from the CUSTOMER_NAME column in the CUSTOMERS table. Which statement produces this output?
The || operator concatenates strings in Oracle SQL.
Generate a list of all customer last names with their credit limits from the CUSTOMERS table. Customers who do not have a credit limit should appear last in the list. kindly note that customers who do not have credit card will have NULL against credit limit.Which query would achieve the required result?
ORDER BY cust_credit_limit sorts ascending by default, generating the required list.
To display the names of employees that are not assigned to a department. Evaluate this SQL statement: SELECT last_name, first_name FROM employee WHERE dept_id = NULL; .Which change should you make to achieve the desired result?
NULL comparisons require IS NULL / IS NOT NULL. Using = NULL always evaluates to UNKNOWN.
To update the CUST_CREDIT_LIMIT column to NULL for all the customers, where CUST_INCOME_LEVEL has NULL in the CUSTOMERS table. Which SQL statement will accomplish the task?
IS NULL is required for NULL comparison; SET cust_credit_limit = NULL explicitly sets the column to NULL.
Tom has designed a payroll software for XYZ technology.The software will store the salary details into the database and later he can retrieve the same for future references. Tom falls under which category of user.
Tom writes application code that stores and retrieves data — he is an Application Programmer.
Which statement is used to manipulate data without affecting the data in the table?
SELECT is a read-only DML statement that retrieves data without modifying it.
Which operator is equivalent to the ‘or’ operator?
The IN operator is equivalent to multiple OR conditions: col IN (a, b) = col = a OR col = b.
How to retrieve department_id column without any duplication from employee relation?
DISTINCT removes duplicate values from the result set.
Select the suitable option for retrieving all the employees whose name ends with "kumar"?
The % wildcard at the start matches any prefix; '%kumar' matches names ending with 'kumar'.
Select the suitable option for retrieving all the employees whose salary range is between 40000 and 100000?
Using >= and <= correctly captures the inclusive range 40000–100000.
Which statement about SQL is true?
NULL values appear last in ascending order (NULLS LAST is the default in most RDBMS).
Select the suitable option for retrieving all the employees who have a manager?
IS NOT NULL correctly filters rows where manager_id has a value.
Which statement is true regarding distinct keywords?
DISTINCT eliminates duplicate rows from the query result.
You need to remove all the data from the employee table while leaving the table definition intact. You want to be able to undo this operation. How would you accomplish this task?
DELETE is DML and can be rolled back; TRUNCATE cannot be rolled back.
Which option is used to delete data from both parent and child tables?
ON DELETE CASCADE automatically deletes child records when the parent is deleted.
How do we represent comments in oracle?
Oracle SQL supports /* */ for block comments and -- for single-line comments.
Which query will change the cust_address to 'Panama' for those who are from ‘Los Angeles’?
UPDATE with SET and WHERE clause changes the specified rows.
Which statement is used to update multiple rows in a single query?
A single UPDATE with a WHERE clause can update multiple rows simultaneously without extra keywords.
A DML statement is used to modify the structure of the database. State True or false.
Refer to Database Management — Select Statement study materials.
Which statements are true regarding constraints?
UNIQUE constraints allow NULL values (a NULL is not considered equal to another NULL).
Which query will delete all the data in the Employee table that belongs to the employee id 1207?
DELETE with a WHERE clause removes specific rows matching the condition.
Create table subjects(Sub_id number(2) primary key,Subj_name varchar(50)); Insert into subjects values (null,’Oracle’); What will be the output of the given query?
Primary key columns cannot be NULL; omitting Sub_id causes ORA-01400.
Which statement is true when a DROP TABLE command is executed on a table?
DROP TABLE is DDL — it auto-commits and is irreversible without a backup.
Based on our question bank analysis, master these concepts to score high in Database Management.
"Focus on understanding the logic behind pseudocode loops and selection statements, as they form the bulk of technical assessments."