What is Data Warehousing
Data Warehousing is a process in which the data is stored and accessed from central location and is meant to support some strategic decisions. Data Warehousing is not a requirement for Data mining. But just makes your Data mining process more efficient.
Data warehouse is a collection of integrated, subject-oriented databases designed to support the decision-support functions (DSF), where each unit of data is relevant to some moment in time.
What is the default “-SORT” order for a SQL
ASCENDING
Compare Data mining and Data Warehousing
Data Warehousing” is technical process where we are making our data centralized while “Data mining” is more of business activity which will analyze how good your business is doing or predict how it will do in the future coming times using the current data. As said before “Data Warehousing” is not a need for “Data mining”. It’s good if you are doing “Data mining” on a “Data Warehouse” rather than on an actual production database. “Data Warehousing” is essential when we want to consolidate data from different sources, so it’s like a cleaner and matured data which sits in between the various data sources and brings then in to one format. “Data Warehouses” are normally physical entities which are meant to improve accuracy of “Data mining” process. For example you have 10 companies sending data in different format, so you create one physical database for consolidating all the data from different company sources, while “Data mining” can be a physical model or logical model. You can create a database in “Data mining” which gives you reports of net sales for this year for all companies. This need not be a physical database as such but a simple query.
What is the difference between “HAVING” and “WHERE” clause
“HAVING” clause is used to specify filtering criteria for “GROUP BY”, while “WHERE” clause applies on normal SQL.
Is there any other way to to store tree structure in a relational database
Yes, it can be done using the “modified preorder tree traversal” as described below.As shown in the previous diagram above, each node is marked with a left and right numbers using a modified preorder traversalas shown above. This can be represented in a database table as shown below.
Can you explain the between clause
Below SQL selects employees born between ’01/01/1975′ AND ’01/01/1978′ as per mysql
SELECT * FROM pcdsEmployee WHERE DOB BETWEEN ‘1975-01-01’ AND ‘2011-09-28’
What are E-R diagrams
E-R
diagram also termed as Entity-Relationship diagram shows relationship between various tables in the database. .
What are DML and DDL statements
DML stands for Data Manipulation Statements. They update data values in table. Below are the most important DDL statements:-
- SELECT – gets data from a database table
- UPDATE – updates data in a table
- DELETE – deletes data from a database table
- INSERT INTO – inserts new data into a database table
DDL stands for Data definition Language. They change structure of the database objects like table, index etc. Most important DDL statements are as shown below:-
- CREATE TABLE – creates a new table in the database.
- ALTER TABLE – changes table structure in database.
- DROP TABLE – deletes a table from database
- CREATE INDEX – creates an index
- DROP INDEX – deletes an index
What are Data Marts
Data Marts are smaller section of Data Warehouses. They help data warehouses collect data. For example your company has lot of branches which are spanned across the globe. Head-office of the company decides to collect data from all these branches for anticipating market. So to achieve this IT department can setup data mart in all branch offices and a central data warehouse where all data will finally reside.
What is a self-join
If we want to join two instances of the same table we can use self-join.