Database Archives - scmGalaxy

Limited Time Offer!

For Less Than the Cost of a Starbucks Coffee, Access All DevOpsSchool Videos on YouTube Unlimitedly.
Master DevOps, SRE, DevSecOps Skills!

Enroll Now

DBMS Interview Q&A Part- 2

by Chandan KumarMay 11, 2021May 11, 2021Database

Q1. Can you create a table without using create command ?

A. Yes, we can create table with the help of SELECT INTO statement. It
copies content of one table to another table. However, there should be
at least one table from where we can copy content.
Example : Copying all columns : select * into new_table from old_table where
condition
Copying specific column : select col1,col2 into new_table from old_table
where condition
Creating new empty table : select * into new_table from old_table where 1
= 0

Q2. What is Denormalization ?

A. It is the reverse process of Normalization. It is the process of trying to
improve the readability of the database by grouping data. Denormalization
is also used for speeding up the performance.

Q3. What are Joins ?

A. Join clause are used to combine rows from two or more tables,
depending upon the columns between them.

Q4. What are the different types of Joins ?

A. Different types of Joins are :

INNER JOIN : It returns all records that are common in both tables.
LEFT OUTER JOIN : It returns all records from the left table, and matched records from right table
RIGHT OUTER JOIN : It returns all records from the right table, and matched records from left table.
FULL OUTER JOIN : It returns all records when there is a match in either left or right table.

Q5. Explain Transaction ?

A. Transaction refers to the collection of multiple statements, that are
responsible for transferring a database from one consistent state to another
consistent state.

Q6. Explain the role of views in database ?

A.View refers to the virtual table. We can create view using create view
statement.

CREATE VIEW as Select col1
FROM table1
where CONDITION;

Q7. Explain Trigger ?

A. Triggers are defined as special kind of stored programs, which are
automatically executed whenever a specific operation occurs in the
database server.

Q8. What are Locks ?

A. Locking is the mechanism to protect data integrity and ensure data
consistency during transactions. Locks are the most common cause of
blocked processes. Stronger the Isolation level, more the chances of
blocking.

Q9. Explain different types of Locks ?

A. Locks are broadly characterized into following types :

Shared Locks : These locks are acquired by readers during read
operations. In other words, these locks exist when two transactions are
granted read access. Data updation is not allowed until shared lock is
released.
Exclusive Locks : In exclusive lock, data items can be both read as well
as written by the transaction. In Exclusive lock, multiple transactions do not
modify the same data simultaneously.

Q10. What is Super Key ?

A. An attribute or set of attributes that uniqueness in database is refered to
as Super key. It is the superset of Candidate key.

Q11. What is Candidate Key ?

A. A minimal set of attribute/attributes that can be used to uniquely identify
a single row in a given relation is refered to as Candidate key.

Q12. Explain Primary Key ?

A. DB Designer selects one of the candidate key as primary key for a
relation for the purpose of identification of a tuple uniquely. It is identified
during table creation.

Q13. What is Composite Key ?

A. If a primary key has more than one attribute, then it is referred to as
Composite key.

Q14. Explain Foreign Key ?

A. A set of attribute/attributes that is used to establish and enforce a link
between data in two or more relations.

Q15. Can a table have more than one primary key ?

A. No.

Q16. Can We Have NULL Value in Primary Key?

A. No.

Q17. What are cursors ?

A. A cursor is a temporary work area created in system memory when a
SQL statement is executed. A cursor can hold more than one row, but can
process only one row at a time.

Q18.What are the differences between Hash join, Merge join and Nested loops?

Hash join	Merge join	Nested loops
The hash join is used when you have to join large tables.	Merge join is used when projections of the joined tables are sorted on the join columns.	The nested loop consists of an outer loop and an inner loop.

Q19. What do you understand by Proactive, Retroactive and Simultaneous Update ?

Proactive Update: These updates are applied to the database before it becomes effective in the real-world environment.
Retroactive Update: These retroactive updates are applied to a database after it becomes effective in the real-world environment.
Simultaneous Update: These updates are applied to the database at the same instance of time as it becomes effective in a real-world environment.

Q20.What do you understand by Data Independence?

A. When you say an application has data independence, it implies that the application is independent of the storage structure and data access strategies of data.

DBMS Interview Q&A Part- 1

by Chandan KumarMay 10, 2021May 10, 2021DatabaseLeave a Comment

Q. What is Data ?

A. Data refers to raw facts and figures that can be recorded.

Q. What is Database ?

A. Database refers to the collection of interrelated and coherent data.

Q. Explain DBMS ?

A. DBMS stands for Database Management System. It is a software
package designed to define, manipulate, retrieve and manage data in
database.

Q. Why DBMS ?

A. To make information easy to access and protected, we use database
management systems. DBMS is important because it manages the data
efficiently and allow users to perform multiple tasks on it with the ease.

Q. What is a database system?

A. The collection of database and DBMS software together is known as a
database system.

Q. What do you mean by Data Modelling ?

A. Data Modelling is the set of conceptual tools for describing data
relationship, data semantics, and consistency constraints. Different data
models are : Network model, Relational model, Object Oriented model, ER
model, and more.

Q. Explain RDBMS ?

A. RDBMS stands for Relational Database Management System. It
arranges information into allied rows and columns. RDMS is an information
management system which is oriented on a data model. RDBMS Example
systems are SQL Server, Oracle, MySQL, MariaDB and SQLite.

Q. Explain Abstraction of Data, with reference to DBMS ?

A. Data Abstraction refers to the process of hiding background details from
user.

Q. Explain the 3 L’s of Data Abstraction ?

A. It refers to three levels of abstraction. They are :

Physical Level : It is lowest level of abstraction. It describes how data is
actually stored. It also describes complex data structure in detail.
Logical Level : It describes what data get stored in the database and what are the relationships among them.
View Level : It is the highest level of data abstraction that only describes a part of database indirectly

Q. What is Database Schema ?

A. Schema refers to the overall structure of database without data values.

Q. What do you mean by transparent DBMS?

A. The transparent DBMS is a type of DBMS which keeps its physical
structure hidden from users.

Q. Explain ER Model ?

A. This model is based on the perception of real world that consists of
collection of basic entities and relationship among these objects. It is the
graphical representation of the database.

Q. What do you understand by Data Independency ?

A. It refers to the capacity to change data at one level without affecting next
higher level is called Data Independence. It is of two types : Physical DI,
Logical DI.
Physical DI : It indicates that physical storage of device could be changed
without affecting conceptual view.
Logical DI : It indicates that conceptual schema can be changed without
affecting existing external schema.

Q. What is a Database Language ?

A. Database Language is a medium by which we can interact with the
database system through some set of commands. These commands are
structured.

Q. What is a Tuple ?

A. A single row of a table, which contains a single record for that relation is
called a tuple.

Q. Explain degree and Cardinality ?

A. Degree is the total number of attributes in a relation or table and
cardinality is total number of tuples/rows in a relation/table.

Q. What is a relation in DBMS ?

A. A database relation refers to an individual table in a relational database.
A table is a relation because it stores the relation between data in its
column-row format.

Q. What is the role of DML Compiler ?

A. It translates DML statements in a query language into low-level
instructions that the query evaluation engine can easily understand.

Q. Explain me the role of using clause for queries ?

A. Clause enables you to specify conditions that filters the results as per
the requirement. Some of the most commonly used clauses are : having,
where etc.

Q. What is a Query ?

A. Query is a statement that is used for the extraction of data from
database.
For example – select * from table1 is a query

Q. What is Subquery ?

A. Subquery is a query within query.
For example – select * from students where marks = ( select max(marks)
from students);

Q. Explain BCNF ?

A. BCNF is Boyce-Codd Normal Form. It is considered to be the advanced
version of 3 NF. Hence it is also refered to as 3.5 NF. A relation is said to
be in BCNF, if it satisfies following rules :

It is in 3NF.
For every functional dependency P->Q, P should be the super key of the table.

Q. What are Stored Procedures ?

A. Stored Procedure refers to the set of Structured Query Language(SQL)
statements stored in a relational database management system as a group.
It can further be reused and shared by multiple programs. It provides a
layer of security between a user interface and database.

Tagged : dbms / Interview / Questions

SQL Queries

by Joydeep MApril 5, 2021DatabaseLeave a Comment

👉 To give a table, or a column in a table, a temporary name we can use Alias:

Syntax: SELECT "column_name" AS "column_Alias" FROM "table_name";

For example we have a table with column_name as “SNo” , “Country” and “Capital” and we want to update it as “Serial Number” , “State” and “Country_Capital then we will run the following command:

👉 To count the number of rows in the table:

Syntax: SELECT COUNT(column_name) AS (alias_name) FROM table_name;

For example we want to count number of orders placed by a particular customer from customer_table:

This will show the count of numbers of order places by the customer id “CG-1234′ and the products ordered.

👉 To add the values in a columns we use Sum command:

Syntax: SELECT SUM (column_name) FROM table_name;

For example we want to add the profit amount from the sales table:

👉 To find out an average for a column in the table:

Syntax: SELECT AVG (column_name) FROM table_name;

For example we need to find the average age of the customers from customer table:

👉To find the minimum and the maximum value in a table:

Minimum

SELECT MIN (column_name) FROM table_name;

For example we want to find the minimum order sale made for a product from sales table:

Maximum

SELECT MAX (column_name) FROM table_name;

For example we want to find the maximum order sale made for a product from sales table:

👉 To groups rows that have the same values into summary rows, like “find the number of customers in each region” we use GROUP BY statement. The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

Syntax: SELECT "column_name","function-type" (column_name) FROM table_name Group By "column-name";

For example we need to find the number orders places in each region in the table:

👉 To make conditions for aggregate functions we use HAVING clause. The difference between Having clause and Where clause is that we cannot make conditions in aggregate functions whereas we can make conditions in aggregate functions using Having clause.

Syntax: SELECT column_name, AGGREGATE FUNCTION column_name FROM tables GROUP BY (column1) HAVING condition;

For example we have some set of customers in four regions and we want to see in which region the count of customers is more than 200. In this scenario we will use the HAVING clause and set a condition of count more than 200.

This command will show the count of customers in the regions where it is more than 200.

👉 To go through a condition and return a value when the first condition is met (like an if-then-else statement) “CASE” statement is used. So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

Syntax: SELECT * CASE WHEN condition THEN result ELSE result END;

Tagged : alias / Case / count / having / maximum / minimum / sql / Update

Alter Table in MySql

by Joydeep MApril 3, 2021DatabaseLeave a Comment

In this tutorial we will see the commands which we can use to make changes in our table (as per April 2k21).

To add a column in an existing table:

Syntax: ALTER TABLE table_name ADD column_name DATATYPE;

To delete a column in an existing table:

Syntax: ALTER TABLE table_name DROP column_name;

To change the data type of a column in an existing table:

Syntax: ALTER TABLE table_name MODIFY COLUMN column_name NEW DATA TYPE;

To rename a column in an existing table:

Syntax: ALTER TABLE table_name CHANGE column_name NEW_COLUMN_NAME;

To update the column as NOT NULL Constraint:

Syntax: ALTER TABLE table_name MODIFY column_name DATATYPE NOT NULL;

To Sort the data in the Table:

Syntax: SELECT "column_name" FROM "table-name" [WHERE "condition"] ORDER BY "column_name" [ASC/DESC];

Tagged : alter table / Change / database / modify / query / where

MySql queries:

by Joydeep MApril 2, 2021DatabaseLeave a Comment

Inserting data into a existing table:

Syntax without column names specified:

INSERT INTO table_name VALUES ('value1','value2');

Example:

In the example we have inserted the data in a customer_table where we have 4 columns with name cust_id, first_name, last_name, age and we have inserted the data in the columns with this command.

Syntax without column names specified:

INSERT INTO table_name ('column1','column2') VALUES ('value1','value2');

Example:

In this example we have inserted the data in three columns excluding the last_name column, by doing this it will store the information in all the columns and will show NULL in the last_name.

Inserting data in multiple rows:

To ‘SELECT’ a statement in a table:

Select column_name from table_name;

Following command will show the data stored in the column_name column in the database.

To use ‘WHERE’ command in a table:

Select column_name from table_name Where (column_name = condition);

For example:

We have a table with Customers name , age and email given and we need to see only the data of customer whose age is greater than 25.

Select * from customer_table where age>25;

This command will show the data of the customers whose age is greater than 25.

To use logical operators in the query:

Select column_name from table_name Where (column_name = condition) AND (column_name = condition) OR (column_name = condition);

To update data in the table:

UPDATE table_name SET column_name='abc' WHERE condition(column);

For example to update the last name of a customer:

UPDATE customer_table SET Last_name='John' WHERE cust_id=5

Tagged : database / insert / mysql / queries

Creating a Table using MySql queries:

by Joydeep MApril 1, 2021April 1, 2021DatabaseLeave a Comment

In this we will learn how we can create tables in the database using sql queries. As we know it becomes quite complicated to make the tables manually in the database. To ease the process we can follow the steps to make tables using my sql queries.

Syntax:

CREATE TABLE table_name (
    column1 column1 datatype column1 constraint,
    column2 column2 datatype column2 constraint,
    column3 column3 datatype column3 constraint,
   ....
);

Constraints:

NOT NULL CONSTRAINT – Ensures that a column cannot have a null value.
DEFAULT CONSTRAINT – Provides a default value for a column when none is specified.
UNIQUE CONSTRAINT – Ensures that all value in a column are different.
CHECK CONSTRAINT – Make sure all values in a column satisfy certian criteria.
PRIMARY_KEY CONSTRAINT – Used to uniquely identify a row in a table.
FOREIGN_KEY CONSTRAINT – Used to ensure referential integrity of the data.

Keys:

A primary key is used to uniquely identify each row in a table.
A primary key can consists of one or more columns on a table.
When multiple columns are used as primary key, it is called as Composite key.
A foreign key is a column (or columns) that references a column (most often primary key) of other table.
The purpose of foreign key is to referential integrity of the data.

As shown in the above picture, Cust_ID is the foreign key for order table whereas it is primary key for in the Customer table that means the value of Cust_ID will not change in either of the table.

For example we will create a table named as customer_table inside the ‘test’ database:

Tagged : Databases / mysql / query

Limited Time Offer!

1. What is DataOps?

Answer: B

2. What is the main goal of DataOps?

Answer: D

3. What is a data pipeline?

Answer: A

Answer: A

5. What is a data warehouse?

Answer: B

6. What is version control?

Answer: A

7. What is ETL?

Answer: B

8. What is the purpose of data profiling?

Answer: C

Answer: A

10. What is data lineage?

Answer: A

11. What is a data dictionary?

Answer: C

12. What is a schema?

Answer: A

13. What is the difference between a primary key and a foreign key?

Answer: A

14. What is a data model?

Answer: B

15. What is a data mart?

Answer: A

16. What is a data pipeline framework?

Answer: A

17. What is a data catalog?

Answer: C

18. What is data integration?

Answer: A

19. What is metadata management?

Answer: A

20. What is data mining?

Answer: A

21. What is data augmentation?

Answer: A

22. What is data curation?

Answer: A

23. What is a data governance framework?

Answer: A

24. What is data lineage tracking?

Answer: A

25. What is data visualization?

Answer: A

26. What is a data warehouse schema?

Answer: C

27. What is data quality?

Answer: A

28. What is data preprocessing?

Answer: A

Answer: A

30. What is data replication?

Answer: A

31. What is a data profile?

Answer: A

32. What is data transformation?

Answer: A

33. What is a data lake architecture?

Answer: A

34. What is a data pipeline architecture?

Answer: A

35. What is data governance maturity?

Answer: A

36. What is data standardization?

Answer: A

37. What is data parallelization?

Answer: A

38. What is data scalability?

Answer: A

39. What is data latency?

Answer: A

40. What is data lineage analysis?

Answer: A

41. What is data governance certification?

Answer: A