1. Which of the following are data models? Select one or more: Conceptual Network Relational Hierarchical
2. Which of the following are DML statements?
Select one or more: INSERT CREATE TRUNCATE SELECT
3. Which of the following are considered as constraints?
Select one or more: Unique NOT NULL NULL Check 4. Database is a shared collection of logically unrelated data, designed to meet the needs of an organization. State True or False. Select one: TRUE FALSE
5. Which of the following represents the degree of the relation?
Select one: No of rows No of Attributes Domain Cardinality
RDBMS CONCEPTS: Post-Quiz
1. 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. Select one: DBA Application Programmer End User Network Administrator
2. SQL is a case sensitive language and the data stored inside
the table are case in-sensitive. Say true or false? True False 3. Which of the following is not a valid relational database? MySQL Sybase Oracle Unify
4. Terms of Relational
_______ refers to the range of values that can
be stored for an attribute Answer 1 Domain
_______ refers to number of columns in the Answer 2
table Degree
________ refers to number of rows in the Answer 3
table cardinality
Answer 4 ______ refers to the columns of the table Attribute
Answer 5 ______ refers to the rows of the table Tuples
5. Choose Correct Option
Answer 1 Data is represented in the form of a table - relational
Answer 2 Data is represented in the form of a tree - hierarchical
Data is represented in the form of a graph Answer 3
- netw ork Data Definition Language: Pre-Quiz 1. A relational database consists of a collection of Select one: Records Keys Tables Fields
2. In a relational database a referential integrity constraint can be
5. Which of the following is not modification of the database
Select one: Deletion Updating Insertion Sorting
Data Definition Language: Post-Quiz
1. 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? Select one: column level foreign key constraint column level check constraint table level check constraint table level foreign key constraint
2. Which of the following options is not correct?
Select one: alter table emp drop column column_name; alter table emp modify name varchar(30); alter table emp add column_name varchar(25); alter emp drop column_name; 3. Choose the correct option
Answer 1 cardholderphoneno unique
creditcardtype should be sliver or platinum Answer 2
only check
Answer 3 cardholdername not null
Answer 4 creditcardno primary key
Answer 5 validitydate not null
4. An emp table contains fields employ name, desig and salary.
How do you drop column salary? Select one: alter table emp delete salary; delete from emp where column = salary; alter table emp delete column salary; alter table emp drop column salary;
5. A table consists of ______ primary keys.
Select one: 2 4 1 Any number Data Manipulation Language: Pre-Quiz 1. To remove a relation from SQL database, we use ___________ command. Delete Truncate All the options Drop
2. Numeric datatype is used to store_______
Whole number Rational number Both whole and natural numbers. Natural number
3. Which of the below is DDL Query?
Truncate table table_name; All the options Delete * from table_name; Drop table table_name;
4. What is the Default format for ‘Date’ datatype?
NONE DD-MON-YYYY YYYY-MM-DD MM-YYYY-DD 5. Which of the below is a reference option for deleting rows from a table?
Cascade Set Null All the options Restrict
Data Manipulation Language: Post-Quiz
1.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?
INSERT INTO student (stud_id, address, name, dob)
VALUES (101,'100 Main Street','Smith','1994-02-01'); INSERT INTO student VALUES (101,'Smith'); INSERT INTO student VALUES (101,'100 Main Street','17- JUN- 99','Smith'); INSERT INTO student (stud_id, address, dob) VALUES (101,'100 Main Street','17-JUN-99'); 2. State True or False. COMMIT ends the current transaction by making all pending data changes permanent. FALSE TRUE
3. 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.
TRUE FALSE
4. Which among the following tags belong to DML?
DELETE UPDATE ALTER CREATE
5. 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. Value for phoneno is missing. The column order should not be changed. The statement will get executed successfully empid value should be given without single quotes.
SQL Select statement: Pre-Quiz
1. 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?
This task cannot be accomplished.
DROP TABLE employee; TRUNCATE TABLE employee; DELETE FROM employee;
2. Which statement is true when a DROP TABLE command is
executed on a table? : Only a DBA can execute the DROP TABLE command. Any pending transactions on the table are rolled back. The table structure and its deleted data cannot be rolled back and restored once the DROP TABLE command is executed. The DROP TABLE command can be executed on a table on which there are pending transactions. 3. Which statements are true regarding constraints? Select one or more: A columns with the UNIQUE constraint can contain NULL values. A foreign key cannot contain NULL values. A constraint is enforced only for the INSERT operation on a table. A constraint can be disabled even if the constraint column contains data.
4. The SQL statements executed in a user session as follows:
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?
Both the DELETE statements and the UPDATE statement are
rolled back. Only the DELETE statements are rolled back. The rollback generates an error. Only the seconds DELETE statement is rolled back. 5. Match the following Like Answer 1 Match a character pattern
Between......and Answer 2 Range of values
In(set) Answer 3 Match any of the list vaues
Isnull Answer 4 checks w hether a value is null or not.
SQL Select statement: Post-Quiz
1. Select the suitable option for retrieving all the employees whose salary range is between 40000 and 100000? Select one or more: select name,salary from employee where salary between 40000 100000; select name,salary from employee where salary>=40000 and salary<=100000; select name,salary from employee where salary is between 40000 and 100000; select name,salary from employee where salary between 40000 and 100000; select name,salary from employee where salary lies between 40000 and 100000;
2. Which statement about SQL is true?
Select one: Null values are displayed first in ascending sequences. Date values are displayed in descending order by default. You cannot specify a column alias in an ORDER BY clause. You cannot sort query results by a column that is not included the SELECT list. The results are sorted by the first column in the SELECT list if the ORDER BY clause is not provided.
3. How to retrieve department_id column without any duplication
from employee relation? Select one: select all department_id from employee; select department_id from employee; select department_id distinct from employee; select distinct department_id from employee;
4. Select the suitable option for retrieving all the employees who have a manager? Select one: select empname, manager_id from employee where manager_id is NOT NULL; select empname, manager_id from employee where manager_id NULL; select empname, manager_id from employee where manager_id is NULL; select empname, manager_id from employee where manager_id is NULL VALUE;
5. Select the suitable option for retrieving all the employees
whose last name is "kumar"? Select one: select * from employee where empname like 'kumar'; select * from employee where empname like '%kumar%'; select * from employee where empname like 'kumar%'; select * from employee where empname like '%kumar';
Function-Scalar & Aggregate: Pre-Quiz
1. Which statement is true regarding the default behavior of the ORDER BY clause?
Only those columns that are specified in the SELECT list can be used in the ORDER BY clause NULL values are not considered at all by the sort operation Numeric values are displayed from the maximum to the minimum value if they have decimal positions In a character sort, the values are case-sensitive
2. 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? SELECT NULLIF(cust_credit_limit*.15,'Not Available')"NEW CREDIT" FROM customers; All the options SELECT TO_CHAR(NVL(cust_credit_limit*.15,'Not Available')) "NEW CREDIT" FROM customers;
3. 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?
: Change the column in the WHERE condition. Change the operator in the WHERE condition. Add a second condition to the WHERE condition. Create an outer join.
4. Select the suitable option for fetching the output of the following query. select substr("Oracle World",1,6) from dual;
racle W racle Wo racle Oracle
5. 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? You need to give the arithmetic expression that involves the salary increment in the UPDATE clause of the SELECT statement. You need to add a column to the EMP table. You need to give the arithmetic expression that involves the salary increment in the DISPLAY clause of the SELECT statement. You need to give the arithmetic expression that involves the salary increment in the SET clause of the UPDATE statement.
Function-Scalar & Aggregate: Post-Quiz
1. Single row functions can be nested to any level. State true or False.
TRUE FALSE
2. All columns in the SELECT list that are not in group functions must be in the GROUP-BY clause. State True or False.
FALSE TRUE
3. 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?
SELECT ordid,TO_CHAR(shipdate, 'Not Shipped'), TO_CHAR(total,'Not Available') FROM order; SELECT ordid, IFNULL(shipdate, 'Not Shipped') as SHIPDATE,Total FROM order; SELECT ordid, shipdate "Not Shipped", total "Not Available" FROM order; SELECT ordid, IFNULL(shipdate, 'Not Shipped') SHIPDATE, IFNULL(total,'Not Available')TOTAL FROM order; FROM order;
4. What will be the output for the below query: select ceil(5.3) from dual; None of the options 6 5 5.0 5. Group functions can be used in the where clause. State True or False. Select one: TRUE FALSE
Joins & SubQuery : Pre-Quiz
1. The COMMISSION column shows the monthly commission earned by the employee.
Which tasks would require sub queries or joins in order to be
performed in a single step?
Listing the employees who earn the same amount of
commission as employee 3 Finding the number of employees who earn a commission that is higher than the average commission of the company
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
Listing the employees who do not earn commission and who
are working for department 20 in descending order of the employee ID
2. Which SQL statement produces an error?
Select one:
SELECT * FROM emp_dept_vu;
SELECT job_id, SUM(salary)
FROM emp_dept_vu WHERE department_id IN (10,20) GROUP BY job_id HAVING SUM(salary) > 20000;
SELECT department_id, SUM(salary)
FROM emp_dept_vu GROUP BY department_id;
None of the statements produce an error; all are valid.
SELECT department_id, job_id, AVG(salary) FROM emp_dept_vu GROUP BY department_id, job_id;
3. What statement would display the age of Customers with the
alias name as AGE? Select one: select (Birth_date/365) AGE from customer; select round((curdate()-Birth_date)/365) AGE from customer; select round((birth_date)-Birth_date)/365) from customer; select (start_date-curdate())/365) AGE from customer
4. Which statement would display the highest credit limit available
in each income level in each city in the Customers table? Select one:
FROM customers GROUP BY cust_city, cust_income_level,cust_credit_limit; SELECT cust_city, cust_income_level,MAX(cust_credit_limit) FROM customers GROUP BY cust_city, cust_income_level;
5. To create a report displaying employee last names, department
names, and locations. Which query should you use to create an equi-join?
FROM employees e, departments d WHERE e.department_id =d.department_id; Joins & SubQuery : Post-Quiz 1. In which cases would you use an outer join? Select one: The tables being joined have only unmatched data. The tables being joined have NOT NULL columns. Only when the tables have a primary key/foreign key relationship. The tables being joined have both matched and unmatched data. The tables being joined have only matched data.
2. In which two cases would you use the USING clause? (Choose two) Select one or more: You want to create a nonequijoin. The tables to be joined have columns of the same name and different data types. The tables to be joined have columns with the same name and compatible data types. The tables to be joined have multiple NULL columns.
3. Which operator is NOT appropriate in the join condition of a
non-equi join SELECT statement? Select one: IN operator LIKE operator greater than or equal to operator equal operator BETWEEN x AND y operator 4. The NOT operator can be used with ______ operators. Select one or more: IN ALL ANY >
5. 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?
Select one: Use a subquery in the WHERE clause to compare the average salary value. Move the function to the SELECT clause and add a GROUP BY clause. Move the function to the SELECT clause and add a GROUP BY clause and a HAVING clause. Change the function in the WHERE clause.
DCL & database Objects : Pre-Quiz
1.The _______ join is based on all columns in the two tables that have the same data type. Left Outer Cross Natural Full Outer
2. Joining a table to itself is called as ______.
Non Equi Join Outer Join Equi Join Self Join
3. _____ is used to retrieve records that do not meet the join
condition Non Equi Join Equi Join Self Join Outer Join
4. The _______ join produces the cross product of two tables.
Cross Equi Outer Self
5. Equijoin is called as ______.
Equal Join Simple Join Self Join Outer Join
DCL & database Objects : Post-Quiz
1. Which SQL statement grants a privilege to all the database users? grant select on department to ALL; grant select on department to PRIVATE; grant select on department to ALL USERS; grant select on department to PUBLIC;
2. An owner can give specific privileges on the owner's objects to
others. State True or False. True False
3. The owner has all the privileges on the object. State true or False. True False
4. Which SQL statement would you use to remove a view called
EMP_DEPT_VU from your schema? REMOVE emp_dept_vu; DROP emp_dept_vu; DELETE VIEW emp_dept_vu; REMOVE VIEW emp_dept_vu; DELETE emp_dept_vu; DROP VIEW emp_dept_vu;
5. CREATE INDEX emp_dept_id_idx
ON employee(dept_id);
Which of the following statements are true with respect to the
above index? May reduce the amount of disk I/O for INSERT statements. Increase the chance of full table scans. Override the unique index created when the FK relationship was defined. Store an index in the EMPLOYEE table. May reduce the amount of disk I/O for SELECT statements.
