Can you explain the SELECT INTO Statement
SELECT INTO statement is used mostly to create backups. The below SQL backsup the Employee table in to the EmployeeBackUp table. One point to be noted is that the structure of pcdsEmployeeBackup and pcdsEmployee table should be same. SELECT * INTO pcdsEmployeeBackup FROM pcdsEmployee
What is SQL
SQL stands for Structured Query Language.SQL is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating database systems. SQL statements are used to retrieve and update data in a database.
What is de-normalization
Denormalization is the process of putting one fact in numerous places (its vice-versa of normalization).Only one valid reason exists for denormalizing a relational design – to enhance performance.The sacrifice to performance is that you increase redundancy in database.
Can you explain Insert, Update and Delete query
Insert statement is used to insert new rows in to table. Update to update existing data in the table. Delete statement to delete a record from the table. Below code snippet for Insert, Update and Delete :-
- INSERT INTO pcdsEmployee SET name=’rohit’,age=’24’;
- UPDATE pcdsEmployee SET age=’25’ where name=’rohit’;
- DELETE FROM pcdsEmployee WHERE name = ‘sonia’;
What is a View
View is a virtual table which is created on the basis of the result set returned by the select statement.
CREATE VIEW [MyView] AS SELECT * from pcdsEmployee where LastName = ‘singh’
In order to query the view
SELECT * FROM [MyView]
What is “CROSS JOIN”? Orwhat is Cartesian product
CROSS JOIN” or “CARTESIAN PRODUCT” combines all rows from both tables. Number of rows will be product of the number of rows in each table. In real life scenario I can not imagine where we will want to use a Cartesian product. But there are scenarios where we would like permutation and combination probably Cartesian would be the easiest way to achieve it.
What is ETL process in Data warehousing? What are the different stages in “Data warehousing
ETL (Extraction, Transformation and Loading) are different stages in Data warehousing. Like when we do software development we follow different stages like requirement gathering, designing, coding and testing. In the similar fashion we have for data warehousing.
Extraction:-
In this process we extract data from the source. In actual scenarios data source can be in many forms EXCEL, ACCESS, Delimited text, CSV (Comma Separated Files) etc. So extraction process handle’s the complexity of understanding the data source and loading it in a structure of data warehouse.
Transformation:-
This process can also be called as cleaning up process. It’s not necessary that after the extraction process data is clean and valid. For instance all the financial figures have NULL values but you want it to be ZERO for better analysis. So you can have some kind of stored procedure which runs through all extracted records and sets the value to zero.
Loading:-
After transformation you are ready to load the information in to your final data warehouse database.
What are cursors and what are the situations you will use them
SQL statements are good for set at a time operation. So it is good at handling set of data. But there are scenarios where we want to update row depending on certain criteria. we will loop through all rows and update data accordingly. There’s where cursors come in to picture.
How will you represent a hierarchical structure shown below in a relational database? or How will you store a tree data structure into DB tables
The hierarchical data is an example of the composite design pattern. The entity relationship diagrams (aka ERdiagram) are used to represent logical and physical relationships between the database tables. The diagram below shows how the table can be designed to store tree data by maintaining the adjacency information via superior_emp_id.
As you can see the “superior_emp_id” is a foreign key that points to the emp_id in the same table. So, Peter has null as he has no superiors. John and Amanda points to Peter who is their manager or superior and so on.
The above table can be created using SQL DDL (Data Definition Language) as shown below.
CREATE TABLE employee
(
emp_id NUMBER (4) CONSTRAINT emp_pk PRIMARY KEY,
emp_name VARCHAR2 (40) NOT NULL,
title VARCHAR2 (40),
dept_id NUMBER (2) NOT NULL,
superior_emp_id NUMBER (4) CONSTRAINT emp_fk REFERENCES employee(emp_id)
CONSTRAINT emp_pk
PRIMARY KEY NONCLUSTERED (emp_id)
)
This can be represented as an object model to map relational data as shown below
public class Employee
{
private Long id;
private String name;
private String title;
private Employee superior;
private Set subordinates;
//getters and setters are omitted
}
What’s difference between DBMS and RDBMS
DBMS provides a systematic and organized way of storing, managing and retrieving from collection of logically related information. RDBMS also provides what DBMS provides but above that it provides relationship integrity. So in short we can say:
RDBMS = DBMS + REFERENTIAL INTEGRITY
These relations are defined by using “Foreign Keys” in any RDBMS.Many DBMS companies claimed there DBMS product was a RDBMS compliant, but according to industry rules and regulations if the DBMS fulfills the twelve CODD rules it’s truly a RDBMS. Almost all DBMS (SQL SERVER, ORACLE etc) fulfills all the twelve CODD rules and are considered as truly RDBMS.