RAKUB Assistant Database Administrator Written Exam: 2020 (E-Zone) Question Full Solution

 RAKUB Assistant Database Administrator Written Exam: 2020 (E-Zone) Question Full Solution

 

RAKUB

Assistant Database Administrator

Written Exam: 2020 (E-Zone)

 

 

1. Describe Database ACID properties.

 

Answer: ACID properties are used for maintaining the integrity of database during transaction processing. ACID stands for Atomicity, Consistency, Isolation and Durability.

 

Atomicity: This property states that a transaction must be treated as an atomic unit, that is, either all of its operations are executed or none. There must be no state in a database where a transaction is left partially completed. States should be defined either before the execution of the transaction or after the execution/abortion/failure of the transaction.

 

Consistency: The database must remain in a consistent state after any transaction. No transaction should have any adverse effect on the data residing in the database. If the database was in a consistent state before the execution of a transaction, it must remain consistent after the execution of the transaction as well.

 

Isolation: In a database system where more than one transaction is being executed simultaneously and in parallel, the property of isolation states that all the transactions will be carried out and executed as if it is the only transaction in the system. No transaction will affect the existence of any other transaction.

 

Durability: The database should be durable enough to hold all its latest updates system fails or restarts. If a transaction updates a chunk of data in a database and commits, then the database will hold the modified data. If a transaction commits but the system before the data could be written on to the disk, then that data will be updated springs back into action.

 

2. Define View, Materialized View. Difference between View and Materialized View and Usage of two.

 

Answer:

 

Views: A View is a virtual relation that acts as an actual relation. It is not a part of logical relational model of the database system. Tuples of the view are not stored in the database system and tuples of the view are generated every time the view is accessed. Query expression of the view is stored in the databases system.

 

Materialized Views: When the results of a view expression are stored in a database system, materialized view, however some database management system provides custom extensions materialized views. The process of keeping the materialized views updated is known asview maintenance.

 

 

Difference between View and Materialized View

 

Views	Materialized Views
Query expression are stored in the databases system and not the resulting tuples of the query expression.	Resulting tuples of the query expression are stored in the databases system.
It does not have any storage cost associated with it.	It does have a storage cost associated with it.
It does not have any updation cost associated with it.	It does have updation cost associated with it.
There is an SQL standard of defining a view.	There is no SQL standard for defining a materialized view.
Views are useful when the view is accessed infrequently	Materialized views are efficient when the view is accessed frequently as it saves the computation time by storing the results before hand.

 

Usage

 

Views: Views can be used everywhere were we can use the actual relation. Views can beused to create custom virtual relations according to the needs of a specific user. We can createas many views as we want in a databases system.

 

Materialized view:

 

Update the materialized view as soon as the relation on which it is defined is updated.

Update the materialized view every time the view is accessed.

Update the materialized view periodically.

 

3.  Difference between Primary key and Unique Key, Drop and Purge, Delete andTruncate.

 

Answer: Differences between primary key and unique key:

 

Primary Key	Unique Key
There can be one primary key in a table	There can be multiple unique keys in the table
It does not allow null columns.	It allows null columns.
Default Index is clustered	Default Index is no-clustered
The purpose of the primary key is to enforce entity integrity.	The purpose of unique key is to enforce unique data.
Primary key can be created using syntax:   CREATE TABLE Employee ( ID int PRIMARY KEY , Name varchar (255), City varchar (150) )	Unique key can be created using syntax:   CREATE TABLE Employee ( ID int UNIQUE. Name varchar (255) NOT NULL. City varchar (150) )
In the primary key, duplicate keys are not allowed.	In a unique key, if one or more key parts are null, then duplicate keys are allowed.

 

DROP is a Data Definition Language (DDL) command which removes the named elements of the schema like relations, domains or constraints and can also remove an entire schema using DROP command.

 

PURGE statement to remove a table or index from recycle bin and release all of the space associated with the object, or to remove the entire recycle bin, or to remove part of all of a dropped table space from the recycle bin.

 

Difference between DELETE and TRUNCATE command

 

Delete	Truncate
The DELETE command is used to delete specified rows (one or more). It is a DML (Data Manipulation Language) command.	While this command is used to delete all the rows from a table. While it is a DDL(Data Definition Language) command
There may be WHERE clause in DELETE command in order to filter the records.	While there may not be WHERE clause in TRUNCATE command.
In the DELETE command, a tuple is locked before removing it.	While in this command, data page is locked before removing the table data.
We can rollback the data even after using DELETE command	While in this command, we can't rollback.
DELETE command is slower than TRUNCATE command	While TRUNCATE command is faster than DELETE command.

 

4. Query to find out even number from given table.

 

Answer:

 

I am passing a simple query where I am searching for specific rows where OrderID is an evennumber.

 

SELECT *

FROM Orders

WHERE mod (OrderID, 2)=0;

 

5.  What are the roles of Database Engineer?

 

Answer: Some database Engineer responsibilities:

 

Installing and upgrading the database server and application tool.

Allocating system storage and planning storage requirements for the database system.

Modifying the database structure, as necessary, from information given byapplication developers.

Enrolling users and maintaining system security.

 Ensuring compliance with database vendor license agreement.

Controlling and monitoring user access to the database.

Monitoring and optimizing the performance of the database.

Planning for backup and recovery of database information.

Maintaining archived data.

Backing up and restoring databases.

Contacting database vendor for technical support.

Generating various reports by querying from database as per need.

Managing and monitoring data replication.

 

6. How to copy from Parent table to Child Table with 1 column dividing into 3different columns?

Name

a

b

c

d

e

f

 

 

 

 

 

 

 

 

 

Answer:

 

WITH MyTable AS (

SELECT 'a' AS Name UNION ALL

SELECT 'b' UNION ALL

SELECT 'C' UNION ALL

SELECT 'd' UNION ALL

SELECT 'e' UNION ALL

SELECT 'f'

),

cte AS(

SELECT Name, ROW_NUMBER () OVER (ORDER BY Name)

FROM MyTable

)

SELECT

MAX (CASE WHEN FLOOR (rn/2) = 0 THEN Name END) AS Namel,

MAX (CASE WHEN FLOOR (rn/2) = 1 THEN Name END) AS Namel,

MAX (CASE WHEN FLOOR (rn/2) = 2 THEN Name END) AS Name3

FROM cte

GROUP BY

rn % 2

ORDER BY

rn % 2;

  

 

My Result Table

  

Name	Name2	Name 3
a	c	e
b	d	f

 

 

7. What is Indexing? Write down the usages of Indexing.

 

Answer:

 

Indexing is a data structure technique which allows to quickly retrieving records from a database file. An Index is a small table having only two columns. The first column comprises copy of the primary or candidate key of a table. Its second column contains a set of pointers for holding the address of the disk block where that specific key value stored. Two main types of indexing methods are:

 

Primary Indexing

Secondary Indexing

Usages of Indexing

 

It helps to reduce the total number of I/O operations needed to retrieve that data, so don't need to access a row in the database from an index structure.

Indexing also helps to reduce table space as don't need to link to a row in a table, as there is no need to store the ROWID in the Index.

Offers Faster search and retrieval of data to users.

 

 

8. What is a constraint? Why use constraint? Difference between table level Constraint and column level Constraint.

 

Answer: Constraints enable to database system to enforce the integrity of the database automatically, without needing to create triggers, rule or defaults. Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted. Constraints can be column level or table level.

 

Difference between table level Constraint and column level Constraint:

✓ Column level constraint is declared at the time of creating a table but table level constraint is created after table is created.

✓ Composite primary key must be declared at table level.

✓ All the constraints can be created at table level but for table level NOT NULL is no allowed.

 

 

9. Describe what are the ways for no data loss?

 

Answer:

Always back up our data

Prevention is the best protection. Create a structured backup strategy and consistently back up our files. Our backup plan should include the different levels of data maintain in our company and the schedule for their backups. Some data is so critical that it may require back it up every week. Also, be sure periodically test our backups to make sure that our data is being backed up properly.

 

Diversify our backups

Always want more than one backup system. The general rule is 3-2-1. I should have 3backups of anything that's very important. They should be backed up in at least two different formats, such as in the cloud and on a hard drive. There should always be an off-site backup in the event that there is damage to our physical office.

 

Encrypt sensitive data

Data is not always encrypted even when it's on a backup tape. Invest in a backup system or service that automatically encrypts all backups to ensure that no matter who gets their handson our data, they cannot access it.

 

Address data security

Mobile devices are powerful business tools but they can leave our data vulnerable. Ensure our devices can be wiped if they are lost with a remote device management system and use hardwares or mobile device systems that encrypt data.

 

Use anti-virus and email security

Email threats such as hacking, phishing or ransomeware can be some of the biggest threats to data. Use a thorough anti-virus and security system to prevent malicious emails from getting through to our company. Also be certain to offer continuing education to our team about the importance of email security.

 

Trust the professionals

Data is too valuable to be left alone. Most businesses don't plan to lose data but that doesn't mean they are immune. No one wants a disaster but still should be prepared. Remember that the best defense is a good offense.

 

 

 

10. Design and Queries from HR schema.

 

 

(vi)  Display details of jobs where the minimum salary is greater than 10000.

 

SELECT * FROM JOBS WHERE MIN_SALARY  > 10000

 

(vii) Display the first name and join date of the employees who joined between 2002and 2005.

 

SELECT FIRST_NAME, HIRE_DATE  FROM  EMPLOYEES

WHERE  TO_CHAR (HIRE_DATE, 'YYYY')  BETWEEN  2002  AND  2005

ORDER  BY  HIRE_DATE

 

(viii) Display first name and join date of the employees who is either IT Programmer or Sales Man.

 

SELECT FIRST_NAME,  HIRE_DATE

FROM  EMPLOYEES  WHERE JOB_ID IN ('IT_PROG', 'SA_MAN')

 

(ix) Display first name, salary, commission pet, and hire date for employees with salary less than 10000.

 

SELECT FIRST_NAME, SALARY, COMMISSION_PCT, HIRE_DATE FROM EMPLOYEES WHERE  SALARY  < 10000

 

(x) Display job Title, the difference between minimum and maximum salaries for jobswith max salary in the range 10000 to 20000.

 

SELECT JOB_TITLE, MAX_SALARY, MAX_SALARY-MIN_SALARY DIFFERENCE FROM JOBS WHERE MAX _SALARY  BETWEEN  10000 AND 20000

 

(xi) Display first name, salary, and round the salary to thousands.

 

SELECT  FIRST_NAME, SALARY,  ROUND (SALARY, -3)  FROM  EMPLOYEES

 

(xii) Display employees where the first name or  last name starts with S.

 

SELECT FIRST_NAME, LAST NAME FROM EMPLOYEES WHERE FIRST_NAME LIKE 'S%' OR  LAST_NAME  LIKE  'S%'

 

(xiii) Display details of the employees where commission percentage is null and salaryin the range 5000 to 10000 and department is 30.

 

SELECT * FROM EMPLOYEES WHERE COMMISSION_PCTSALARY IS NULL AND BETWEEN  5000  AND 10000 AND DEPARTMENT_ID = 30

 

(xiv) Display first name and date of first salary of the employees.

 

FIRST_NAME, HIRE_DATE, LAST_DAY (HIRE_DATE) +1 FROM EMPLOYEES

 

 

(xv) Display first name and last name after converting the first letter of each name toupper case and the rest to lower case.

 

SELECT INITCAP (FIRST_NAME), INITCAP (LAST_NAME) FROM  EMPLOYEES

 

 

ID	NAME	EMAIL
1	John	asd@asd.com
2	Sam	asd@asd.com
3	Tom	asd@asd.com
4	Bob	asd@asd.com
5	Tom	asd@asd.com

 

11. Query for retrieving UNCOMMON Name from Name column of two given tables.

 

Answer:

 

SELECT * FROM  Tab2 T2

WHERE name NOT EXISTS (SELECT * FROM Tab1 T1

WHERE  T1.colname  T2.colname)

 

12. How to find duplicate data in database? Explain DDL and DML.

 

Answer:

 

SELECT name, COUNT (email) 1

FROM users

GROUP BY email

HAVING COUNT (email) > 1

 

DDL

DDL is short name of Data Definition Language, which deals with database schemas and descriptions, of how the data should reside in the database.

 

CREATE-to create database and its objects like (table, index, views, store procedure, function and triggers).

ALTER- alters the structure of the existing database.

DROP-delete object from the database

TRUNCATE- remove all records from a table: also, all spaces allocated for the records are removed.

COMMENT – add comments to the data dictionary.

 

 

 

 

DML

DML is short name of Data Manipulation Language which deals with the data manipulation and includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE, etc. and is used to store, modify, retrieve, delete and update data in database.

 

SELECT-  retrieve data from one or more tables.

INSERT- insert data into a table.

UPDATE-  updates existing data within a table.

DELETE-  delete all records from a table.

MERGE- UPSERT  operation (insert or update)

CALL – call a PS/SQL or a Java subprogram.

EXPLAIN PLAN – interpretation of the data access path.

      LOCK TABLE-  concurrency control