Tuesday, June 26, 2012

jQuery questions and answers

What is jQuery ?
It's very simple but most valuable Question on jQuery means jQuery is a fast and concise JavaScript Library that simplifies HTML document traversing, animating, event handling, and Ajax interactions for rapid web development. jQuery is designed to change the way that you write JavaScript. Jquery is build library for javascript no need to write your own functions or script jquery all ready done for you

How you will use Jquery means requirement needed for using jquery
Nothing more need to do just olny download jquery library(.js file) from any of the jquery site Download jquery and just linked with your html pages like all other javascript file
like below :
< script src="jquery.js" language="javascript" type="text/javascript">

what the use of $ symbol in Jquery
$ Symbol is just replacement of jquery means at the place of $ you may use jquery hence $ symbol is used for indication that this line used for jquery

How do you select an item using css class or ID and get the value by use of jquery
If an element of html like < div> , < p> or any tag have ID MyId and class used MyClass then we select the element by below jquery code

$('#MyId') for ID and for classs $('.MyClass') and for value

var myValue = $('#MyId').val(); // get the value in var Myvalue by id
Or for set the value in selected item

$('#MyId').val("print me"); // set the value of a form input

How to get the server response from an AJAX request using Jquery?
When invoking functions that have asynchronous behavior We must provide a callback function to capture the desired result. This is especially important with AJAX in the browser because when a remote request is made, it is indeterminate when the response will be received.
Below an example of making an AJAX call and alerting the response (or error):
     url: 'pcdsEmpRecords.php',
     success: function(response) {
     error: function(xhr) {
        alert('Error!  Status = ' + xhr.status);

How do you update ajax response with id " resilts"
By using below code we can update div content where id 'results' with ajax response
 function updateStatus() {
            url: 'pcdsEmpRecords.php',
            success: function(response) {
             // update div id Results 

How do You disable or enable a form element?
There are two ways to disable or enable form elements.
Set the 'disabled' attribute to true or false:
 // Disable #pcds
 $('#pcds').attr('disabled', true);
 // Enable #pcds
 $('#pcds').attr('disabled', false);
Add or remove the 'disabled' attribute:
 // Disable #pcds
 $("#pcds").attr('disabled', 'disabled');
 // Enable #x

How do you check or uncheck a checkbox input or radio button?
There are two ways to check or uncheck a checkbox or radio button.
Set the 'checked' attribute to true or false.
 // Check #pcds
 $('#pcds').attr('checked', true);
 // Uncheck #pcds
 $('#pcds').attr('checked', false);
Add or remove the 'checked' attribute:
 // Check #pcds
 $("#pcds").attr('checked', 'checked');
 // Uncheck #pcds

How do you get the text value of a selected option?
Select elements typically have two values that you want to access. First there's the value to be sent to the server, which is easy:
 // => 1
The second is the text value of the select. For example, using the following select box:
If you wanted to get the string "Mr" if the first option was selected (instead of just "1"), you would do that in the following way:
 $("#mpcdsselect option:selected").text();
 // => "Mr"

JSON questions and answer

What is The JSON(JavaScript Object Notation) ?
JavaScript Object Notation(JSON) is a lightweight text-based open standard designed for human-readable data interchange. It is derived from the JavaScript programming language for representing simple data structures and associative arrays, called objects. And JSON is language-independent, with parsers available for virtually every programming language. Uses conventions that are familiar to programmers of the C-family of languages, including C, C++, C#, Java, JavaScript, Perl, Python,php
The JSON format is often used for serializing and transmitting structured data over a network connection. When third party data interchane(REST Services) then JSON may used there LIKE SHOP .It is primarily used to transmit data between a server and web application, serving as an alternative to XML.
 Who is the Father or creater of JSON ?
Douglas Crockford called as the Father of JSON

what the file extension of JSON
The JSON filename extension is .json.

Explain Json with php
Json is too much easy with php There is no installation needed to use these functions; they are part of the PHP core. nothing more need to know just only use { ,[ and create json format string and use three php function json_encode() to get JSON representation of a value, json_decode() for Decodes a JSON string, ¦json_last_error() to get the last error occurred in process.

write your desire string in below format and use php funtions :

"firstName": "Rohit",
"lastName": "Singh",
"age": 26,
"address": {
"streetAddress": "Mira Road Thane ",
"city": "Mumbai",
"state": "maharshtra",
"postalCode": "401107"
"phoneNumber": [
{ "type": "home", "number": "022 333-1234" },
{ "type": "fax", "number": "022 444-4567" }

$decodeString = json_decode($string);
echo 'First Name - '.$decode->{"firstName"};
echo 'Last Name - '.$decode->{"lastName"};
echo 'Address - '.$decode->{"address"}->{"streetAddress"};

Out put : Print below

First Name - Rohit
Last Name - Singh
Address - Mira Road Thane

Why Use JSON over XML
• Lighter and faster than XML as on-the-wire data format
• JSON objects are typed while XML data is typeless
> JSON types: string, number, array, boolean,
> XML data are all string
• Native data form for JavaScript code
> Data is readily accessible as JSON objects in your JavaScript
code vs. XML data needed to be parsed and assigned to variables through tedious DOM APIs
> Retrieving values is as easy as reading from an object property in your JavaScript code

Explain JSON Structures
• A collection of name/value pairs
> In various languages, this is realized as an object, record, struct, dictionary, hash table, keyed list, or associative array
• An ordered list of values
> In most languages, this is realized as an array, vector, list, or sequence
• These are universal data structures supported
• A JSON object is an unordered set of name/value pairs
• A JSON object begins with { (left brace) and ends with } (right brace)
• Each name is followed by : (colon) and the name/value pairs are separated by , (comma)

Compare JSON with JavaScript
• JSON is a subset of the object literal notation of JavaScript
> JSON can be used in the JavaScript language with no muss or fuss
Example: JSON Object
var myJSONObject = {"bindings": [ {"ircEvent": "PRIVMSG", "method": "newURI", "regex": "^http://.*"},
{"ircEvent": "PRIVMSG", "method": "deleteURI", "regex": "^delete.*"},
{"ircEvent": "PRIVMSG", "method": "randomURI", "regex": "^random.*"}
• In this example, a JSON JavaScript object is created
containing a single member "bindings", which contains
an array containing three objects, each containing
"ircEvent", "method", and "regex" members
• Members can be retrieved using dot or subscript
operators myJSONObject.bindings[0].method // "newURI"
Text to Object Conversion in
JavaScript code
var myObject = eval('(' + myJSONtext + ')');
• To convert a JSON text into an JSON object, use
the eval() function > eval() invokes the JavaScript compiler
> Since JSON is a proper subset of JavaScript, the compiler will
correctly parse the text and produce an object structure

what the Security and JSON Parser
Security and JSON Parser to understand by below examples
// Include http://www.json.org/json.js
var myObject = myJSONtext.parseJSON();
• eval() can compile and execute any JavaScript program, so there can be security issues (cross-site scripting)
> Use eval() when the source can be trusted
• When security is a concern - the source cannot be trusted -, it is better to use a JSON parser
> A JSON parser will only recognize JSON text and so is much safer
Object to Text Conversion
var myJSONText = myObject.toJSONString();
• You can convert JSON object into JSON text
• JSON does not support cyclic data structure
> Do not give cyclical structures to the JSON stringifier

Do you know JSON Tools for Java Developer
Ya some of JSON tool for java developer is
• Parser
> Parse JSON text files and convert these to a Java model
• Renderer
> Render a Java representation into text
• Serializer
> Serialize plain POJO clusters to a JSON representation
• Validator
> Validate the contents of a JSON file using a JSON schema
JSONObject Java Class
• A JSONObject is an unordered collection of name/value pairs
• The put methods adds a name/value pair to an object
• The texts produced by the toString methods strictly conform to the JSON syntax rules
myString = new JSONObject().put("JSON", "Hello, World!").toString();
// myString is {"JSON": "Hello, World"}

How to Generate or Send JSON Data at the Server Side
• Create JSONObject Java object
• Add name and value pairs using put method
• Convert it to String type using toString method and send it to the client with content-type as "text/xml" or "text/plain"
myString = new JSONObject().put("JSON", "Hello, World!").toString();
// myString is {"JSON": "Hello, World"}

How to Receive JSON Data at the Client Side
• JSON data is received as a string
• Calling eval() will generate JSON object in JavaScript code
> var JSONdata = eval(req.responseText);
• Once you have JSON object, you can use . notation to access its properties
> var name = JSONdata.name;
> var address = JSONdata.addresses[3];
> var streetname = JSONdata.addresses[3].street;

How to Generate/Send JSON Data at the Client Side
• Create JSON JavaScript object
• Use "POST" HTTP method in the open method of the XMLHttpRequest object
• Pass JSON JavaScript object in the send method of XMLHttpRequest object
var carAsJSON = JSON.stringify(car);
var url = "JSONExample?timeStamp=" + new Date().getTime(); createXMLHttpRequest();
xmlHttp.open("POST", url, true);
xmlHttp.onreadystatechange = handleStateChange;
xmlHttp.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");

How to Receive JSON Data at the Server Side
• Read the JSON data as a String type
• Create JSONObject Java object from the string String json = readJSONStringFromRequestBody(request);
//Use the JSON-Java binding library to create a JSON object in Java JSONObject jsonObject = null;
try {
jsonObject = new JSONObject(json);
catch(ParseException pe) {

What is JSON-RPC? What is JSON-RPC-Java?
• JSON-RPC is a simple remote procedure call protocol similar to XML-RPC although it uses the lightweight JSON format instead of XML
• JSON-RPC-Java is a Java implementation of the JSON-RPC protocol

Why JSON-RPC-Java?
• It allows you to transparently call server-side Java code from JavaScript with an included lightweight JSON-RPC JavaScript client
• It is designed to run in a Servlet container such as Tomcat and can be used with J2EE Application servers to allow calling of plain Java or EJB methods from within a JavaScript DHTML web application

Features of JSON-RPC-Java
• Dynamically call server-side Java methods from JavaScript DHTML web applications. No Page reloading.
• Asynchronous communications.
• Transparently maps Java objects to JavaScript objects.
• Lightweight protocol similar to XML-RPC although much faster.
• Leverages J2EE security model with session specific exporting of objects.
• Supports Internet Explorer, Mozilla, Firefox, Safari, Opera and Konqueror

Friday, June 22, 2012

Database (DBMS) interview questions and answers

What is database or database management systems (DBMS)? and - What’s the difference between file and database? Can files qualify as a database?
Database provides a systematic and organized way of storing, managing and retrieving from collection of logically related information.
Secondly the information has to be persistent, that means even after the application is closed the information should be persisted.
Finally it should provide an independent way of accessing data and should not be dependent on the application to access the information.
Main difference between a simple file and database that database has independent way (SQL) of accessing information while simple files do not File meets the storing, managing and retrieving part of a database but not the independent way of accessing data. Many experienced programmers think that the main difference is that file can not provide multi-user capabilities which a DBMS provides. But if we look at some old COBOL and C programs where file where the only means of storing data, we can see functionalities like locking, multi-user etc provided very efficiently. So it’s a matter of debate if some interviewers think this as a main difference between files and database accept it… going in to debate is probably loosing a job.

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’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
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.

What are CODD rules?
In 1969 Dr. E. F. Codd laid down some 12 rules which a DBMS should adhere in order to get the logo of a true RDBMS.
Rule 1: Information Rule.
"All information in a relational data base is represented explicitly at the logical level and in exactly one way - by values in tables."
Rule 2: Guaranteed access Rule.
"Each and every datum (atomic value) in a relational data base is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name."
In flat files we have to parse and know exact location of field values. But if a DBMS is truly RDBMS you can access the value by specifying the table name, field name, for instance Customers.Fields [‘Customer Name’].
Rule 3: Systematic treatment of null values.
"Null values (distinct from the empty character string or a string of blank characters and distinct from zero or any other number) are supported in fully relational DBMS for representing missing information and inapplicable information in a systematic way, independent of data type.".
Rule 4: Dynamic on-line catalog based on the relational model.
"The data base description is represented at the logical level in the same way as ordinary data, so that authorized users can apply the same relational language to its interrogation as they apply to the regular data. "The Data Dictionary is held within the RDBMS, thus there is no-need for off-line volumes to tell you the structure of the database.
Rule 5: Comprehensive data sub-language Rule.
"A relational system may support several languages and various modes of terminal use (for example, the fill-in-the-blanks mode). However, there must be at least one language whose statements are expressible, per some well-defined syntax, as character strings and that is comprehensive in supporting all the following items

Data Definition
View Definition
Data Manipulation (Interactive and by program).
Integrity Constraints
Transaction boundaries (Begin, commit and rollback)
Rule 6: .View updating Rule
"All views that are theoretically updatable are also updatable by the system."
Rule 7: High-level insert, update and delete.
"The capability of handling a base relation or a derived relation as a single operand applies not only to the retrieval of data but also to the insertion, update and deletion of data."
Rule 8: Physical data independence.
"Application programs and terminal activities remain logically unimpaired whenever any changes are made in either storage representations or access methods."
Rule 9: Logical data independence.
"Application programs and terminal activities remain logically unimpaired when information-preserving changes of any kind that theoretically permit un-impairment are made to the base tables."
Rule 10: Integrity independence.
"Integrity constraints specific to a particular relational data base must be definable in the relational data sub-language and storable in the catalog, not in the application programs." Rule 11: Distribution independence.
"A relational DBMS has distribution independence."
Rule 12: Non-subversion Rule.
"If a relational system has a low-level (single-record-at-a-time) language, that low level cannot be used to subvert or bypass the integrity Rules and constraints expressed in the higher level relational language (multiple-records-at-a-time)."

What are E-R diagrams?
E-R diagram also termed as Entity-Relationship diagram shows relationship between various tables in the database. .

How many types of relationship exist in database designing?
There are three major relationship models:-

7.What is normalization? What are different type of normalization?
There is set of rules that has been established to aid in the design of tables that are meant to be connected through relationships. This set of rules is known as Normalization.
Benefits of Normalizing your database include:
=>Avoiding repetitive entries
=>Reducing required storage space
=>Preventing the need to restructure existing tables to accommodate new data.
=>Increased speed and flexibility of queries, sorts, and summaries.

Following are the three normal forms :-
First Normal Form
For a table to be in first normal form, data must be broken up into the smallest un possible.In addition to breaking data up into the smallest meaningful values, tables first normal form should not contain repetitions groups of fields.
Second Normal form
The second normal form states that each field in a multiple field primary key table must be directly related to the entire primary key. Or in other words, each non-key field should be a fact about all the fields in the primary key.
Third normal form
A non-key field should not depend on other Non-key field.

What is denormalization ?
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 Fourth Normal Form and Fifth Normal Form ?
In fourth normal form it should not contain two or more independent multi-v about an entity and it should satisfy “Third Normal form”.
Fifth normal form deals with reconstructing information from smaller pieces of information. These smaller pieces of information can be maintained with less redundancy.

Have you heard about sixth normal form?
If we want relational system in conjunction with time we use sixth normal form. At this moment SQL Server does not supports it directly.

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

How do we select distinct values from a table?
DISTINCT keyword is used to return only distinct values. Below is syntax:- Column age and Table pcdsEmp

What is Like operator for and what are wild cards?
LIKE operator is used to match patterns. A "%" sign is used to define the pattern.
Below SQL statement will return all words with letter "S"
SELECT * FROM pcdsEmployee WHERE EmpName LIKE 'S%'
Below SQL statement will return all words which end with letter "S"
SELECT * FROM pcdsEmployee WHERE EmpName LIKE '%S'
Below SQL statement will return all words having letter "S" in between
SELECT * FROM pcdsEmployee WHERE EmpName LIKE '%S%'
"_" operator (we can read as “Underscore Operator”). “_” operator is the character defined at that point. In the below sample fired a query Select name from pcdsEmployee where name like '_s%' So all name where second letter is “s” is returned.

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 order by clause?
ORDER BY clause helps to sort the data in either ascending order to descending order.
Ascending order sort query
SELECT name,age FROM pcdsEmployee ORDER BY age ASC
Descending order sort query
SELECT name FROM pcdsEmployee ORDER BY age DESC

What is the SQL " IN " clause?
SQL IN operator is used to see if the value exists in a group of values. For instance the below SQL checks if the Name is either 'rohit' or 'Anuradha' SELECT * FROM pcdsEmployee WHERE name IN ('Rohit','Anuradha') Also you can specify a not clause with the same. SELECT * FROM pcdsEmployee WHERE age NOT IN (17,16)

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'

we have an employee salary table how do we find the second highest from it?
below Sql Query find the second highest salary
SELECT * FROM pcdsEmployeeSalary a WHERE (2=(SELECT COUNT(DISTINCT(b.salary)) FROM pcdsEmployeeSalary b WHERE b.salary>=a.salary))

What are different types of joins in SQL?
Inner join shows matches only when they exist in both tables. Example in the below SQL there are two tables Customers and Orders and the inner join in made on Customers.Customerid and Orders.Customerid. So this SQL will only give you result with customers who have orders. If the customer does not have order it will not display that record.
SELECT Customers.*, Orders.* FROM Customers INNER JOIN Orders ON Customers.CustomerID =Orders.CustomerID

Left join will display all records in left table of the SQL statement. In SQL below customers with or without orders will be displayed. Order data for customers without orders appears as NULL values. For example, you want to determine the amount ordered by each customer and you need to see who has not ordered anything as well. You can also see the LEFT OUTER JOIN as a mirror image of the RIGHT OUTER JOIN (Is covered in the next section) if you switch the side of each table.
SELECT Customers.*, Orders.* FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID =Orders.CustomerID

Right join will display all records in right table of the SQL statement. In SQL below all orders with or without matching customer records will be displayed. Customer data for orders without customers appears as NULL values. For example, you want to determine if there are any orders in the data with undefined CustomerID values (say, after a conversion or something like it). You can also see the RIGHT OUTER JOIN as a mirror image of the LEFT OUTER JOIN if you switch the side of each table.
SELECT Customers.*, Orders.* FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID =Orders.CustomerID

What is “CROSS JOIN”? or What 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.

How to select the first record in a given set of rows?
Select top 1 * from sales.salesperson

What is the default “-SORT ” order for a SQL?

What is a self-join?
If we want to join two instances of the same table we can use self-join.

What’s the difference between DELETE and TRUNCATE ?
Following are difference between them:
=>>DELETE TABLE syntax logs the deletes thus making the delete operations low. TRUNCATE table does not log any information but it logs information about deallocation of data page of the table. So TRUNCATE table is faster as compared to delete table.
=>>DELETE table can have criteria while TRUNCATE can not.
=>> TRUNCATE table can not have triggers.

What’s the difference between “UNION” and “UNION ALL” ?
UNION SQL syntax is used to select information from two tables. But it selects only distinct records from both the table. , while UNION ALL selects all records from both the tables.

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.

What is " Group by " clause?
“Group by” clause group similar data so that aggregate values can be derived.

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.

What is a Sub-Query?
A query nested inside a SELECT statement is known as a subquery and is an alternative to complex join statements. A subquery combines data from multiple tables and returns results that are inserted into the WHERE condition of the main query. A subquery is always enclosed within parentheses and returns a column. A subquery can also be referred to as an inner query and the main query as an outer query. JOIN gives better performance than a subquery when you have to check for the existence of records.
For example, to retrieve all EmployeeID and CustomerID records from the ORDERS table that have the EmployeeID greater than the average of the EmployeeID field, you can create a nested query, as shown:

What are Aggregate and Scalar Functions?
Aggregate and Scalar functions are in built function for counting and calculations.
Aggregate functions operate against a group of values but returns only one value.
AVG(column) :- Returns the average value of a column
COUNT(column) :- Returns the number of rows (without a NULL value) of a column
COUNT(*) :- Returns the number of selected rows
MAX(column) :- Returns the highest value of a column
MIN(column) :- Returns the lowest value of a column
Scalar functions operate against a single value and return value on basis of the single value.
UCASE(c) :- Converts a field to upper case
LCASE(c) :- Converts a field to lower case
MID(c,start[,end]) :- Extract characters from a text field
LEN(c) :- Returns the length of a text

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 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

What is SQl injection ?
It is a Form of attack on a database-driven Web site in which the attacker executes unauthorized SQL commands by taking advantage of insecure code on a system connected to the Internet, bypassing the firewall. SQL injection attacks are used to steal information from a database from which the data would normally not be available and/or to gain access to an organization’s host computers through the computer that is hosting the database.
SQL injection attacks typically are easy to avoid by ensuring that a system has strong input validation.
As name suggest we inject SQL which can be relatively dangerous for the database. Example this is a simple SQL
SELECT email, passwd, login_id, full_name
FROM members WHERE email = 'x'
Now somebody does not put “x” as the input but puts “x ; DROP TABLE members;”.
So the actual SQL which will execute is :-
SELECT email, passwd, login_id, full_name FROM members WHERE email = 'x' ; DROP TABLE members;
Think what will happen to your database.

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 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 are Fact tables and Dimension Tables ? What is Dimensional Modeling and Star Schema Design
When we design transactional database we always think in terms of normalizing design to its least form. But when it comes to designing for Data warehouse we think more in terms of denormalizing the database. Data warehousing databases are designed using Dimensional Modeling. Dimensional Modeling uses the existing relational database structure and builds on that.
There are two basic tables in dimensional modeling:-
Fact Tables.
Dimension Tables.

Fact tables are central tables in data warehousing. Fact tables have the actual aggregate values which will be needed in a business process. While dimension tables revolve around fact tables. They describe the attributes of the fact tables.

What is Snow Flake Schema design in database? What’s the difference between Star and Snow flake schema?
Star schema is good when you do not have big tables in data warehousing. But when tables start becoming really huge it is better to denormalize. When you denormalize star schema it is nothing but snow flake design. For instance below customeraddress table is been normalized and is a child table of Customer table. Same holds true for Salesperson table.

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.
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.
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.
After transformation you are ready to load the information in to your final data warehouse database.

What is Data mining ?
Data mining is a concept by which we can analyze the current data from different perspectives and summarize the information in more useful manner. It’s mostly used either to derive some valuable information from the existing data or to predict sales to increase customer market.
There are two basic aims of Data mining:-

Prediction: -
From the given data we can focus on how the customer or market will perform. For instance we are having a sale of 40000 $ per month in India, if the same product is to be sold with a discount how much sales can the company expect.
Summarization: -
To derive important information to analyze the current business scenario. For example a weekly sales report will give a picture to the top management how we are performing on a weekly basis?

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 are indexes? What are B-Trees?
Index makes your search faster. So defining indexes to your database will make your search faster.Most of the indexing fundamentals use “B-Tree” or “Balanced-Tree” principle. It’s not a principle that is something is created by SQL Server or ORACLE but is a mathematical derived fundamental.In order that “B-tree” fundamental work properly both of the sides should be balanced.

I have a table which has lot of inserts, is it a good database design to create indexes on that table?
Insert’s are slower on tables which have indexes, justify it?or Why do page splitting happen?
All indexing fundamentals in database use “B-tree” fundamental. Now whenever there is new data inserted or deleted the tree tries to become unbalance.
Creates a new page to balance the tree.
Shuffle and move the data to pages.
So if your table is having heavy inserts that means it’s transactional, then you can visualize the amount of splits it will be doing. This will not only increase insert time but will also upset the end-user who is sitting on the screen. So when you forecast that a table has lot of inserts it’s not a good idea to create indexes.

What are the two types of indexes and explain them in detail? or What’s the difference between clustered and non-clustered indexes?
There are basically two types of indexes:-
Clustered Indexes.
Non-Clustered Indexes.
In clustered index the non-leaf level actually points to the actual data.In Non-Clustered index the leaf nodes point to pointers (they are rowid’s) which then point to actual data.

Mysql interview questions and answers

how to do login in mysql with unix shell
By below method if password is pass and user name is root
# [mysql dir]/bin/mysql -h hostname -u root -p pass

how you will Create a database on the mysql server with unix shell
mysql> create database databasename;

how to list or view all databases from the mysql server.
mysql> show databases;

How Switch (select or use) to a database.
mysql> use databasename;

How To see all the tables from a database of mysql server.
mysql> show tables;

How to see table's field formats or description of table .
mysql> describe tablename;

How to delete a database from mysql server.
mysql> drop database databasename;

How we get Sum of column
mysql> SELECT SUM(*) FROM [table name];

How to delete a table
mysql> drop table tablename;

How you will Show all data from a table.
mysql> SELECT * FROM tablename;

How to returns the columns and column information pertaining to the designated table
mysql> show columns from tablename;

How to Show certain selected rows with the value "pcds"
mysql> SELECT * FROM tablename WHERE fieldname = "pcds";

How will Show all records containing the name "sonia" AND the phone number '9876543210'
mysql> SELECT * FROM tablename WHERE name = "sonia" AND phone_number = '9876543210';

How you will Show all records not containing the name "sonia" AND the phone number '9876543210' order by the phone_number field.
mysql> SELECT * FROM tablename WHERE name != "sonia" AND phone_number = '9876543210' order by phone_number;

How to Show all records starting with the letters 'sonia' AND the phone number '9876543210'
mysql> SELECT * FROM tablename WHERE name like "sonia%" AND phone_number = '9876543210';

How to show all records starting with the letters 'sonia' AND the phone number '9876543210' limit to records 1 through 5.
mysql> SELECT * FROM tablename WHERE name like "sonia%" AND phone_number = '9876543210' limit 1,5;

Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with r.
mysql> SELECT * FROM tablename WHERE rec RLIKE "^r";

How you will Show unique records.
mysql> SELECT DISTINCT columnname FROM tablename;

how we will Show selected records sorted in an ascending (asc) or descending (desc)
mysql> SELECT col1,col2 FROM tablename ORDER BY col2 DESC;

mysql> SELECT col1,col2 FROM tablename ORDER BY col2 ASC;

how to Return total number of rows.
mysql> SELECT COUNT(*) FROM tablename;

How to Join tables on common columns.
mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id

How to Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs.
# mysql -u root -p

mysql> use mysql;

mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));

mysql> flush privileges;

How to Change a users password from unix shell.
# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password 'new-password'

How to Change a users password from MySQL prompt. Login as root. Set the password. Update privs.
# mysql -u root -p

mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');

mysql> flush privileges;

How to Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.
# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("newrootpassword") where User='root';
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

How to Set a root password if there is on root password.
# mysqladmin -u root password newpassword

How to Update a root password.
# mysqladmin -u root -p oldpassword newpassword

How to allow the user "sonia" to connect to the server from localhost using the password "passwd". Login as root. Switch to the MySQL db. Give privs. Update privs.
# mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to sonia@localhost identified by 'passwd';
mysql> flush privileges;

How to give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.
# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');
mysql> flush privileges;
mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;

How To update info already in a table and Delete a row(s) from a table.
mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';
mysql> DELETE from [table name] where [field name] = 'whatever';

How to Update database permissions/privilages.
mysql> flush privileges;

How to Delete a column and Add a new column to database
mysql> alter table [table name] drop column [column name];
mysql> alter table [table name] add column [new column name] varchar (20);

Change column name and Make a unique column so we get no dupes.
mysql> alter table [table name] change [old column name] [new column name] varchar (50);
mysql> alter table [table name] add unique ([column name]);

How to make a column bigger and Delete unique from table.
mysql> alter table [table name] modify [column name] VARCHAR(3);
mysql> alter table [table name] drop index [colmn name];

How to Load a CSV file into a table
mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);

How to dump all databases for backup. Backup file is sql commands to recreate all db's.
# [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

How to dump one database for backup.
# [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql

How to dump a table from a database.
# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Restore database (or database table) from backup.
# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

How to Create Table show Example
mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));
How to search second maximum(second highest) salary value(integer)from table employee (field salary)in the manner so that mysql gets less load?
By below query we will get second maximum(second highest) salary value(integer)from table employee (field salary)in the manner so that mysql gets less load?
SELECT DISTINCT(salary) FROM employee order by salary desc limit 1 , 1 ;
(This way we will able to find out 3rd highest , 4th highest salary so on just need to change limit condtion like LIMIT 2,1 for 3rd highest and LIMIT 3,1 for 4th
some one may finding this way useing below query that taken more time as compare to above query SELECT salary FROM employee where salary < (select max(salary) from employe) order by salary DESC limit 1 ;

What Is 3-Tier(Multi-Tier) Architecture And Why Do You Need It?

Three-tier or multi-tier architecture are often used when describing how clients connect to servers. But what does it all mean and do you need to make any changes to your existing infrastructure?
Let me try to explain this in non-technical terms (or as close to it I can get).

1-Tier Architecture
We all know software packages like MS Access, MS Excel, QuickBooks and Peachtree just to name a few. They all have the same in common that they access files directly. This means that the file you want to work with must be accessible from a local or shared drive. This is the simplest of all the architectures but also the least secure. Since users have direct access to the files, they could accidentally move, modify or even worse delete the file by accident or on purpose.
There is also usually an issue when multiple users access the same file at the same time: In many cases only one can edit the file while others only have read-only access.
So 1-tier architecture is simple and cheap, but usually unsecured and data can easily be lost if you are not careful.

2-Tier Architecture
This architecture is also called Client-Server architecture because of the two components: The client that runs the application and the server that handles the database back-end. When the client starts it establishes a connection to the server and communicates as needed with the server while running the client. The client computer usually can’t see the database directly and can only access the data by starting the client. This means that the data on the server is much more secure. Now users are unable to change or delete data unless they have specific user rights to do so.
The client-server solution also allows multiple users to access the database at the same time as long as they are accessing data in different parts of the database. One other huge benefit is that the server is processing data that allows the client to work on the presentation and business logic only. This mean that the client and the server is sharing the workload and by scaling the server to be more powerful than the client, you are usually able to load many clients to the server allowing more users to work on the system at the same time.

3-Tier Architecture
This involves one more layer called the business logic tier, service tier or middle tier (layer). In the client-server solution the client was handling the business logic that makes the client “thick”. A thick client means that it requires heavy traffic with the server, thus making it difficult to use over slower network connections like Internet and Wireless (3G, Edge or Wi-Fi).
By introducing the middle layer, the client is only handling presentation logic. This means that only little communication is needed between the client and the middle tier making the client “thin” or “thinner”. An example of a thin client is an Internet browser that allows you to see and provide information fast and almost with no delay.
As more users access the system a three-tier solution is more scalable than the other solutions because you can add as many middle tiers (running on each own server) as needed to ensure good performance (N-tier or multiple-tier).
Security is also the best in the three-tier architecture because the middle layer protects the database tier.
There is one major drawback to the N-tier architecture and that is that the additional tiers increase the complexity and cost of the installation.
Take a look at the differences among these three.

Very simpleInexpensive
No server needed
Good securityMore scalable
Faster execution
Exceptional securityFastest execution
“Thin” client
Very scalable
Poor securityMulti user issues
More costlyMore complex
“Thick” client
Very costlyVery complex
Usually 1 (or a few)
50-2000 (+)

SQL Server 2008 R2 Setup, Configurations & Remote Accessing using Java


In this article, the configuration steps for remotely accessing SQL Server 2008 R2 using Java are described.

System Requirements

  • Operating System: Windows 7 (preferable) / Windows Server 2008 standard
  • Database System: SQL Server 2008 R2 (preferable) / SQL Server 2008
  • .NET Framework: Microsoft .NET Framework 3.5 SP1
  • Database Management Tool: SQL Server Management Studio

Configuration Steps

SQL Server 2008 R2 Installation

  1. In Instance Name page, please select the Default instance
  2. In Authentication Mode page, please select the Windows Authentication Mode

SQL Server 2008 to allow remote connections

  1. Click Start, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.
  2. Click SQL Server Services, make confirm SQL Server (SQLEXPRESS) and SQL Server Browser running. 
  3. If SQL Server Browser is stopped, then select its properties and point to Service tab, change the Start Mode Disabled to Automatic, click the apply button, then click start option using right mouse click over SQL Server Browser.
  4. Restart the SQL Server(SQLEXPRESS) using right mouse click over SQL Server(SQLEXPRESS).
  5. Click SQL Server Network Configuration, point to Protocols for SQLEXPRESS, point to TCP/IP, make sure TCP/IP status is Enabled.
  6. Open TCP/IP Properties form using right mouse click over TCP/IP, point to IP Address tab, point to TCP Port in Last section, change TCP Port to 1433, and click Apply button.

Create exceptions in Windows Firewall:

  1. Click Start, point to Control Panel, point to Windows Firewall Settings
  2. Click Change settings link, point to Exceptions tab
  3. Click Add port... button, do the following:
Name: 1433
Port number: 1433
Protocol: TCP
Click OK, and click apply.

Alternative process to create exceptions in Windows Firewall:

  1. Click Start, point to Administrative Tools, open Windows Firewall with Advanced Security.
  2. Click Inbound Rules, Click New Rule link at the top of right section.
  3. Select Port radio button, click next.
  4. Select TCP radio button, Enter port number in Specific local ports section such as:
Specific local ports: 1433
  1. Click next
  2. Select Allow the connection, click next button, again click next button
  3. Enter Name Ex. 1433
  4. Click Finish button

Import database using .bak extension file:

  1. In SQL Server Management Studio, Right-click the Databases folder, Click the Restore Database.
  2. Enter database name into To database section. Example: testDB
  3. Select the From device, and point the testDB.bak file location
  4. Click the OK button

To create a SQL Server login that uses SQL Server Authentication (SQL Server Management Studio):

  1. In SQL Server Management Studio, open Object Explorer and expand the folder of the server instance in which to create the new login.
  2. Right-click the Security folder, point to New, and then click Login.
  3. On the General page, enter a name for the new login in the Login name box. Example: test
  4. Select SQL Server Authentication
  5. Enter a password for the login. password example: test-1
  6. Select the Enforce password policy and Enforece password expiration options. In general, enforcing password policy is the more secure option.
  7. Confirm User must change password at next login is not selected.
  8. Select Default database as testDB.
  9. Click OK.

Add user in testDB database:

  1. In SQL Server Management Studio, open Databases folder, expand testDB
  2. Right-click the Security folder, point to New, and then click User
  3. Enter a User Name, enter login name which has been created. Example: test
  4. Select the db_datareader, db_datawriter (as you need) in Owned Schemas section
  5. Click OK

Access database using JAVA:

  1. Requirement: sqljdbc4.jar
  2. Add jar file in Java project
  3. Connection string
String conString =jdbc:sqlserver://\\SQLEXPRESS:1433;
databaseName=testDB;userName=test; passWord=test-2;";


What’s PHP ?
The PHP Hypertext Preprocessor is a programming language that allows web developers to create dynamic content that interacts with databases. PHP is basically used for developing web based software applications.

What Is a Session?
A session is a logical object created by the PHP engine to allow you to preserve data across subsequent HTTP requests. There is only one session object available to your PHP scripts at any time. Data saved to the session by a script can be retrieved by the same script or another script when requested from the same visitor. Sessions are commonly used to store temporary data to allow multiple PHP pages to offer a complete functional transaction for the same visitor.

What is meant by PEAR in php?
PEAR is the next revolution in PHP. This repository is bringing higher level programming to PHP. PEAR is a framework and distribution system for reusable PHP components. It eases installation by bringing an automated wizard, and packing the strength and experience of PHP users into a nicely organized OOP library. PEAR also provides a command-line interface that can be used to automatically install “packages”
PEAR is short for “PHP Extension and Application Repository” and is pronounced just like the fruit. The purpose of PEAR is to provide:
A structured library of open-sourced code for PHP users
A system for code distribution and package maintenance
A standard style for code written in PHP
The PHP Foundation Classes (PFC),
The PHP Extension Community Library (PECL),
A web site, mailing lists and download mirrors to support the PHP/PEAR community
PEAR is a community-driven project with the PEAR Group as the governing body. The project has been founded by Stig S. Bakken in 1999 and quite a lot of people have joined the project since then.

How can we know the number of days between two given dates using PHP?
Simple arithmetic:
$date1 = date(’Y-m-d’);
$date2 = ‘2006-07-01′;
$days = (strtotime() – strtotime()) / (60 * 60 * 24);
echo “Number of days since ‘2006-07-01′: $days”;

How can we repair a MySQL table?

The syntex for repairing a mysql table is:
REPAIR TABLE tablename
This command will repair the table specified.
If QUICK is given, MySQL will do a repair of only the index tree.
If EXTENDED is given, it will create index row by row.

What is the difference between $message and $$message?
Anwser 1:
$message is a simple variable whereas $$message is a reference variable. Example:
$user = ‘bob’
is equivalent to
$holder = ‘user’;
$$holder = ‘bob’;Anwser 2:
They are both variables. But $message is a variable with a fixed name. $$message is a variable who’s name is stored in $message. For example, if $message contains “var”, $$message is the same as $var.

What Is a Persistent Cookie?
A persistent cookie is a cookie which is stored in a cookie file permanently on the browser’s computer. By default, cookies are created as temporary cookies which stored only in the browser’s memory. When the browser is closed, temporary cookies will be erased. You should decide when to use temporary cookies and when to use persistent cookies based on their differences:
*Temporary cookies can not be used for tracking long-term information.
*Persistent cookies can be used for tracking long-term information.
*Temporary cookies are safer because no programs other than the browser can access them.
*Persistent cookies are less secure because users can open cookie files see the cookie values.

What does a special set of tags do in PHP?
The output is displayed directly to the browser.How do you define a constant?
Via define() directive, like define (”MYCONSTANT”, 100);

What are the differences between require and include, include_once?
Anwser 1:
require_once() and include_once() are both the functions to include and evaluate the specified file only once. If the specified file is included previous to the present call occurrence, it will not be done again.But require() and include() will do it as many times they are asked to do.
Anwser 2:
The include_once() statement includes and evaluates the specified file during the execution of the script. This is a behavior similar to the include() statement, with the only difference being that if the code from a file has already been included, it will not be included again. The major difference between include() and require() is that in failure include() produces a warning message whereas require() produces a fatal errors.
Anwser 3:
All three are used to an include file into the current page.
If the file is not present, require(), calls a fatal error, while in include() does not.
The include_once() statement includes and evaluates the specified file during the execution of the script. This is a behavior similar to the include() statement, with the only difference being that if the code from a file has already been included, it will not be included again. It des not call a fatal error if file not exists. require_once() does the same as include_once(), but it calls a fatal error if file not exists.
Anwser 4:
File will not be included more than once. If we want to include a file once only and further calling of the file will be ignored then we have to use the PHP function include_once(). This will prevent problems with function redefinitions, variable value reassignments, etc.

What is meant by urlencode and urldecode?
Anwser 1:
urlencode() returns the URL encoded version of the given string. URL coding converts special characters into % signs followed by two hex digits. For example: urlencode(”10.00%”) will return “10%2E00%25″. URL encoded strings are safe to be used as part of URLs.
urldecode() returns the URL decoded version of the given string.

Anwser 2:
string urlencode(str) – Returns the URL encoded version of the input string. String values to be used in URL query string need to be URL encoded. In the URL encoded version:
Alphanumeric characters are maintained as is.
Space characters are converted to “+” characters.
Other non-alphanumeric characters are converted “%” followed by two hex digits representing the converted character.
string urldecode(str) – Returns the original string of the input URL encoded string.For example:
$discount =”10.00%”;
$url = “http://domain.com/submit.php?disc=”.urlencode($discount);
echo $url;You will get “http://domain.com/submit.php?disc=10%2E00%25″.

How To Get the Uploaded File Information in the Receiving Script?
Once the Web server received the uploaded file, it will call the PHP script specified in the form action attribute to process them. This receiving PHP script can get the uploaded file information through the predefined array called $_FILES. Uploaded file information is organized in $_FILES as a two-dimensional array as:
$_FILES[$fieldName]['name'] – The Original file name on the browser system.
$_FILES[$fieldName]['type'] – The file type determined by the browser.
$_FILES[$fieldName]['size'] – The Number of bytes of the file content.
$_FILES[$fieldName]['tmp_name'] – The temporary filename of the file in which the uploaded file was stored on the server.
$_FILES[$fieldName]['error'] – The error code associated with this file upload.
The $fieldName is the name used in the .

What is the difference between mysql_fetch_object and mysql_fetch_array?
MySQL fetch object will collect first single matching record where mysql_fetch_array will collect all matching records from the table in an array

How can I execute a PHP script using command line?
Just run the PHP CLI (Command Line Interface) program and provide the PHP script file name as the command line argument. For example, “php myScript.php”, assuming “php” is the command to invoke the CLI program.
Be aware that if your PHP script was written for the Web CGI interface, it may not execute properly in command line environment.I am trying to assign a variable the value of 0123, but it keeps coming up with a different number, what’s the problem?
PHP Interpreter treats numbers beginning with 0 as octal. Look at the similar PHP interview questions for more numeric problems.Would I use print “$a dollars” or “{$a} dollars” to print out the amount of dollars in this example?
In this example it wouldn’t matter, since the variable is all by itself, but if you were to print something like “{$a},000,000 mln dollars”, then you definitely need to use the braces.

What are the different tables present in MySQL? Which type of table is generated when we are creating a table in the following syntax: create table employee(eno int(2),ename varchar(10))?
Total 5 types of tables we can create
1. MyISAM2. Heap
3. Merge
MyISAM is the default storage engine as of MySQL 3.23. When you fire the above create query MySQL will create a MyISAM table.

How To Create a Table?
If you want to create a table, you can run the CREATE TABLE statement as shown in the following sample script:
include “mysql_connection.php”;
$sql = “CREATE TABLE Tech_links (”
. “, url VARCHAR(80) NOT NULL”
. “, notes VARCHAR(1024)”
. “, counts INTEGER”
. “, time TIMESTAMP DEFAULT sysdate()”
. “)”;
if (mysql_query($sql, $con)) {
print(”Table Tech_links created.\n”);
} else {
print(”Table creation failed.\n”);
Remember that mysql_query() returns TRUE/FALSE on CREATE statements. If you run this script, you will get something like this:
Table Tech_links created.

How can we encrypt the username and password using PHP?
You can encrypt a password with the following Mysql>SET PASSWORD=PASSWORD(”Password”);
You can use the MySQL PASSWORD() function to encrypt username and password. For example,
INSERT into user (password, …) VALUES (PASSWORD($password”)), …);

How do you pass a variable by value?
Just like in C++, put an ampersand in front of it, like $a = &$b

What is the functionality of the functions STRSTR() and STRISTR()?
string strstr ( string haystack, string needle ) returns part of haystack string from the first occurrence of needle to the end of haystack. This function is case-sensitive.
stristr() is idential to strstr() except that it is case insensitive.

When are you supposed to use endif to end the conditional statement?
When the original if was followed by : and then the code block without braces.

How can we send mail using JavaScript?
No. There is no way to send emails directly using JavaScript.
But you can use JavaScript to execute a client side email program send the email using the “mailto” code. Here is an example:
function myfunction(form)
return true;

What is the functionality of the function strstr and stristr?
strstr() returns part of a given string from the first occurrence of a given substring to the end of the string. For example: strstr(”user@example.com”,”@”) will return “@example.com”.
stristr() is idential to strstr() except that it is case insensitive.

What is the difference between ereg_replace() and eregi_replace()?
egi_replace() function is identical to ereg_replace() except that it ignores case distinction when matching alphabetic characters.

How do I find out the number of parameters passed into function9. ?
func_num_args() function returns the number of parameters passed in.

What is the purpose of the following files having extensions: frm, myd, and myi? What these files contain?
In MySQL, the default table type is MyISAM.
Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type.
The ‘.frm’ file stores the table definition.
The data file has a ‘.MYD’ (MYData) extension.
The index file has a ‘.MYI’ (MYIndex) extension,

If the variable $a is equal to 5 and variable $b is equal to character a, what’s the value of $$b?
100, it’s a reference to existing variable.

How to Protect Special Characters in Query String?
If you want to include special characters like spaces in the query string, you need to protect them by applying the urlencode() translation function. The script below shows how to use urlencode():

Please click the links below”
.” to submit comments about TECHPreparation.com:

$comment = ‘I want to say: “It\’s a good site! :->”‘;
$comment = urlencode($comment);

.”It’s an excellent site!

$comment = ‘This visitor said: “It\’s an average site! :-(“‘;
$comment = urlencode($comment);

.”It’s an average site.


Are objects passed by value or by reference?
Everything is passed by value.

What are the differences between DROP a table and TRUNCATE a table?
DROP TABLE table_name – This will delete the table and its data.
TRUNCATE TABLE table_name – This will delete the data of the table, but not the table definition.

How do you call a constructor for a parent class?

Here are three basic types of runtime errors in PHP:
1. Notices: These are trivial, non-critical errors that PHP encounters while executing a script – for example, accessing a variable that has not yet been defined. By default, such errors are not displayed to the user at all – although you can change this default behavior.
2. Warnings: These are more serious errors – for example, attempting to include() a file which does not exist. By default, these errors are displayed to the user, but they do not result in script termination.
3. Fatal errors: These are critical errors – for example, instantiating an object of a non-existent class, or calling a non-existent function. These errors cause the immediate termination of the script, and PHP’s default behavior is to display them to the user when they take place.
Internally, these variations are represented by twelve different error types

What’s the special meaning of __sleep and __wakeup?
__sleep returns the array of all the variables than need to be saved, while __wakeup retrieves them.

How can we submit a form without a submit button?
If you don’t want to use the Submit button to submit a form, you can use normal hyper links to submit a form. But you need to use some JavaScript code in the URL of the link. For example:
Submit Me Why doesn’t the following code print the newline properly?
Because inside the single quotes the \n character is not interpreted as newline, just as a sequence of two characters – \ and n

Would you initialize your strings with single quotes or double quotes?
Since the data inside the single-quoted string is not parsed for variable substitution, it’s always a better idea speed-wise to initialize a string with single quotes, unless you specifically need variable substitution.How can we extract string ‘abc.com ‘ from a string http://info@abc.com using regular expression of php?
We can use the preg_match() function with “/.*@(.*)$/” as
the regular expression pattern. For example:
echo $data[1];

What are the differences between GET and POST methods in form submitting, give the case where we can use GET and we can use POST methods?
Anwser 1:
When we submit a form, which has the GET method it displays pair of name/value used in the form at the address bar of the browser preceded by url. Post method doesn’t display these values.
Anwser 2:
When you want to send short or small data, not containing ASCII characters, then you can use GET” Method. But for long data sending, say more then 100 character you can use POST method.
Once most important difference is when you are sending the form with GET method. You can see the output which you are sending in the address bar. Whereas if you send the form with POST” method then user can not see that information.
Anwser 3:
What are “GET” and “POST”?
GET and POST are methods used to send data to the server: With the GET method, the browser appends the data onto the URL. With the Post method, the data is sent as “standard input.”
Major Difference
In simple words, in POST method data is sent by standard input (nothing shown in URL when posting while in GET method data is sent through query string.
Ex: Assume we are logging in with username and password.
GET: we are submitting a form to login.php, when we do submit or similar action, values are sent through visible query string (notice ./login.php?username=…&password=… as URL when executing the script login.php) and is retrieved by login.php by $_GET['username'] and $_GET['password'].
POST: we are submitting a form to login.php, when we do submit or similar action, values are sent through invisible standard input (notice ./login.php) and is retrieved by login.php by $_POST['username'] and $_POST['password'].
POST is assumed more secure and we can send lot more data than that of GET method is limited (they say Internet Explorer can take care of maximum 2083 character as a query string).
Anwser 4:
In the get method the data made available to the action page ( where data is received ) by the URL so data can be seen in the address bar. Not advisable if you are sending login info like password etc. In the post method the data will be available as data blocks and not as query string in case of get method.
Anwser 5:
When we submit a form, which has the GET method it pass value in the form of query string (set of name/value pair) and display along with URL. With GET we can a small data submit from the form (a set of 255 character) whereas Post method doesn’t display value with URL. It passes value in the form of Object and we can submit large data from the form.
Anwser 6:
On the server side, the main difference between GET and POST is where the submitted is stored. The $_GET array stores data submitted by the GET method. The $_POST array stores data submitted by the POST method.
On the browser side, the difference is that data submitted by the GET method will be displayed in the browser’s address field. Data submitted by the POST method will not be displayed anywhere on the browser.
GET method is mostly used for submitting a small amount and less sensitive data. POST method is mostly used for submitting a large amount or sensitive data.

What is the difference between the functions unlink and unset?
unlink() is a function for file system handling. It will simply delete the file in context.
unset() is a function for variable management. It will make a variable undefined.

How come the code works, but doesn’t for two-dimensional array of mine?
Any time you have an array with more than one dimension, complex parsing syntax is required. print “Contents: {$arr[1][2]}” would’ve worked.How can we register the variables into a session?
$_SESSION['var'] = ‘value’;

What is the difference between characters 23 and \x23?
The first one is octal 23, the second is hex 23.

With a heredoc syntax, do I get variable substitution inside the heredoc contents?

How can we submit form without a submit button?
We can use a simple JavaScript code linked to an event trigger of any form field. In the JavaScript code, we can call the document.form.submit() function to submit the form. For example:

How can we create a database using PHP and mysql?
We can create MySQL database with the use of mysql_create_db($databaseName) to create a database.

How many ways we can retrieve the date in result set of mysql using php?
As individual objects so single record or as a set or arrays.

Can we use include (”abc.php”) two times in a php page “makeit.php”?

For printing out strings, there are echo, print and printf. Explain the differences.
echo is the most primitive of them, and just outputs the contents following the construct to the screen. print is also a construct (so parentheses are optional when calling it), but it returns TRUE on successful output and FALSE if it was unable to print out the string. However, you can pass multiple parameters to echo, like:

and it will output the string “Welcome to techpreparations!” print does not take multiple parameters. It is also generally argued that echo is faster, but usually the speed advantage is negligible, and might not be there for future versions of PHP. printf is a function, not a construct, and allows such advantages as formatted output, but it’s the slowest way to print out data out of echo, print and printf.

I am writing an application in PHP that outputs a printable version of driving directions. It contains some long sentences, and I am a neat freak, and would like to make sure that no line exceeds 50 characters. How do I accomplish that with PHP?
On large strings that need to be formatted according to some length specifications, use wordwrap() or chunk_split().

What’s the output of the ucwords function in this example?
print $formatted;
ucwords() makes every first letter of every word capital, but it does not lower-case anything else. To avoid this, and get a properly formatted string, it’s worth using strtolower() first.

What’s the difference between htmlentities() and htmlspecialchars()?
htmlspecialchars only takes care of <, >, single quote ‘, double quote ” and ampersand. htmlentities translates all occurrences of character sequences that have different meaning in HTML.

How can we extract string “abc.com” from a string “mailto:info@abc.com?subject=Feedback” using regular expression of PHP?
$text = “mailto:info@abc.com?subject=Feedback”;
preg_match(’|.*@([^?]*)|’, $text, $output);
echo $output[1];
Note that the second index of $output, $output[1], gives the match, not the first one, $output[0].So if md5() generates the most secure hash, why would you ever use the less secure crc32() and sha1()?
Crypto usage in PHP is simple, but that doesn’t mean it’s free. First off, depending on the data that you’re encrypting, you might have reasons to store a 32-bit value in the database instead of the 160-bit value to save on space. Second, the more secure the crypto is, the longer is the computation time to deliver the hash value. A high volume site might be significantly slowed down, if frequent md5() generation is required.

How can we destroy the session, how can we unset the variable of a session?
session_unregister() – Unregister a global variable from the current session
session_unset() – Free all session variables

What are the different functions in sorting an array?
Sorting functions in PHP:

How can we know the count/number of elements of an array?
2 ways:
a) sizeof($array) – This function is an alias of count()
b) count($urarray) – This function returns the number of elements in an array.
Interestingly if you just pass a simple var instead of an array, count() will return 1.

How many ways we can pass the variable through the navigation between the pages?
At least 3 ways:
1. Put the variable into session in the first page, and get it back from session in the next page.
2. Put the variable into cookie in the first page, and get it back from the cookie in the next page.
3. Put the variable into a hidden form field, and get it back from the form in the next page.

What is the maximum length of a table name, a database name, or a field name in MySQL?
Database name: 64 characters
Table name: 64 characters
Column name: 64 characters

How many values can the SET function of MySQL take?
MySQL SET function can take zero or more values, but at the maximum it can take 64 values

.What are the other commands to know the structure of a table using MySQL commands except EXPLAIN command?
DESCRIBE table_name;

How can we find the number of rows in a table using MySQL?
Use this for MySQL
SELECT COUNT(*) FROM table_name;

What’s the difference between md5(), crc32() and sha1() crypto on PHP?
The major difference is the length of the hash generated. CRC32 is, evidently, 32 bits, while sha1() returns a 128 bit value, and md5() returns a 160 bit value. This is important when avoiding collisions.

How can we find the number of rows in a result set using PHP?
Here is how can you find the number of rows in a result set in PHP:
$result = mysql_query($any_valid_sql, $database_link);
$num_rows = mysql_num_rows($result);
echo “$num_rows rows found”;

How many ways we can we find the current date using MySQL?

Give the syntax of GRANT commands?
The generic syntax for GRANT is as following
GRANT [rights] on [database] TO [username@hostname] IDENTIFIED BY [password]
Now rights can be:
a) ALL privilages
b) Combination of CREATE, DROP, SELECT, INSERT, UPDATE and DELETE etc.
We can grant rights on all databse by usingh *.* or some specific database by database.* or a specific table by database.table_name.Give the syntax of REVOKE commands?
The generic syntax for revoke is as following
REVOKE [rights] on [database] FROM [username@hostname]
Now rights can be:
a) ALL privileges
b) Combination of CREATE, DROP, SELECT, INSERT, UPDATE and DELETE etc.We can grant rights on all database by using *.* or some specific database by database.* or a specific table by database.table_name.

What is the difference between CHAR and VARCHAR data types?
CHAR is a fixed length data type. CHAR(n) will take n characters of storage even if you enter less than n characters to that column. For example, “Hello!” will be stored as “Hello! ” in CHAR(10) column.
VARCHAR is a variable length data type. VARCHAR(n) will take only the required storage for the actual number of characters entered to that column. For example, “Hello!” will be stored as “Hello!” in VARCHAR(10) column.

How can we encrypt and decrypt a data present in a mysql table using mysql?

Will comparison of string “10″ and integer 11 work in PHP?
Yes, internally PHP will cast everything to the integer type, so numbers 10 and 11 will be compared.

What is the functionality of MD5 function in PHP?
string md5(string)
It calculates the MD5 hash of a string. The hash is a 32-character hexadecimal number.

How can I load data from a text file into a table?
The MySQL provides a LOAD DATA INFILE command. You can load data from a file. Great tool but you need to make sure that:
a) Data must be delimited
b) Data fields must match table columns correctly

How can we know the number of days between two given dates using MySQL?
SELECT DATEDIFF(NOW(),’2006-07-01′);

How can we change the name of a column of a table?
This will change the name of column:
ALTER TABLE table_name CHANGE old_colm_name new_colm_name

How can we change the data type of a column of a table?
This will change the data type of a column:
ALTER TABLE table_name CHANGE colm_name same_colm_name [new data type]

What is the difference between GROUP BY and ORDER BY in SQL?
To sort a result, use an ORDER BY clause.
The most general way to satisfy a GROUP BY clause is to scan the whole table and create a new temporary table where all rows from each group are consecutive, and then use this temporary table to discover groups and apply aggregate functions (if any).
ORDER BY [col1],[col2],…[coln]; Tells DBMS according to what columns it should sort the result. If two rows will have the same value in col1 it will try to sort them according to col2 and so on.
GROUP BY [col1],[col2],…[coln]; Tells DBMS to group (aggregate) results with same value of column col1. You can use COUNT(col1), SUM(col1), AVG(col1) with it, if you want to count all items in group, sum all values or view average.

What is meant by MIME?
Answer 1:
MIME is Multipurpose Internet Mail Extensions is an Internet standard for the format of e-mail. However browsers also uses MIME standard to transmit files. MIME has a header which is added to a beginning of the data. When browser sees such header it shows the data as it would be a file (for example image)
Some examples of MIME types:
Answer 2:
Multipurpose Internet Mail Extensions.
WWW’s ability to recognize and handle files of different types is largely dependent on the use of the MIME (Multipurpose Internet Mail Extensions) standard. The standard provides for a system of registration of file types with information about the applications needed to process them. This information is incorporated into Web server and browser software, and enables the automatic recognition and display of registered file types. …How can we know that a session is started or not?
A session starts by session_start() function.
This session_start() is always declared in header portion. it always declares first. then we write session_register().What are the differences between mysql_fetch_array(), mysql_fetch_object(), mysql_fetch_row()?
Answer 1:
mysql_fetch_array() -> Fetch a result row as a combination of associative array and regular array.mysql_fetch_object() -> Fetch a result row as an object.
mysql_fetch_row() -> Fetch a result set as a regular array().
Answer 2:
The difference between mysql_fetch_row() and mysql_fetch_array() is that the first returns the results in a numeric array ($row[0], $row[1], etc.), while the latter returns a the results an array containing both numeric and associative keys ($row['name'], $row['email'], etc.). mysql_fetch_object() returns an object ($row->name, $row->email, etc.).

If we login more than one browser windows at the same time with same user and after that we close one window, then is the session is exist to other windows or not? And if yes then why? If no then why?
Session depends on browser. If browser is closed then session is lost. The session data will be deleted after session time out. If connection is lost and you recreate connection, then session will continue in the browser.

What are the MySQL database files stored in system ?
Data is stored in name.myd
Table structure is stored in name.frm
Index is stored in name.myi

What is the difference between PHP4 and PHP5?
PHP4 cannot support oops concepts and Zend engine 1 is used.
PHP5 supports oops concepts and Zend engine 2 is used.
Error supporting is increased in PHP5.
XML and SQLLite will is increased in PHP5.Can we use include(abc.PHP) two times in a PHP page makeit.PHP”?
Yes we can include that many times we want, but here are some things to make sure of:
(including abc.PHP, the file names are case-sensitive)
there shouldn’t be any duplicate function names, means there should not be functions or classes or variables with the same name in abc.PHP and makeit.php

What are the differences between mysql_fetch_array(), mysql_fetch_object(), mysql_fetch_row()?
mysql_fetch_array – Fetch a result row as an associative array and a numeric array.
mysql_fetch_object – Returns an object with properties that correspond to the fetched row and moves the internal data pointer ahead. Returns an object with properties that correspond to the fetched row, or FALSE if there are no more rows
mysql_fetch_row() – Fetches one row of data from the result associated with the specified result identifier. The row is returned as an array. Each result column is stored in an array offset, starting at offset 0.

What is meant by nl2br()?
nl2br() inserts a HTML tag
before all new line characters \n in a string.
echo nl2br(”god bless \n you”);
god bless


How can we encrypt and decrypt a data presented in a table using MySQL?
You can use functions: AES_ENCRYPT() and AES_DECRYPT() like:
AES_ENCRYPT(str, key_str)
AES_DECRYPT(crypt_str, key_str)

How can I retrieve values from one database server and store them in other database server using PHP?
For this purpose, you can first read the data from one server into session variables. Then connect to other server and simply insert the data into the database.Who is the father of PHP and what is the current version of PHP and MYSQL?
Rasmus Lerdorf.
PHP 5.1. Beta
MySQL 5.0

In how many ways we can retrieve data in the result set of MYSQL using PHP?
mysql_fetch_array – Fetch a result row as an associative array, a numeric array, or both
mysql_fetch_assoc – Fetch a result row as an associative arraymysql_fetch_object – Fetch a result row as an object
mysql_fetch_row —- Get a result row as an enumerated array

What are the functions for IMAP?
imap_body – Read the message body
imap_check – Check current mailbox
imap_delete – Mark a message for deletion from current mailbox
imap_mail – Send an email message

What are encryption functions in PHP?

What is the difference between htmlentities() and htmlspecialchars()?
htmlspecialchars() – Convert some special characters to HTML entities (Only the most widely used)
htmlentities() – Convert ALL special characters to HTML entities

What is the functionality of the function htmlentities?
htmlentities() – Convert all applicable characters to HTML entities
This function is identical to htmlspecialchars() in all ways, except with htmlentities(), all characters which have HTML character entity equivalents are translated into these entities.

How can we get the properties (size, type, width, height) of an image using php image functions?
To know the image size use getimagesize() function
To know the image width use imagesx() function
To know the image height use imagesy() function

How can we increase the execution time of a php script?
By the use of void set_time_limit(int seconds)
Set the number of seconds a script is allowed to run. If this is reached, the script returns a fatal error. The default limit is 30 seconds or, if it exists, the max_execution_time value defined in the php.ini. If seconds is set to zero, no time limit is imposed.
When called, set_time_limit() restarts the timeout counter from zero. In other words, if the timeout is the default 30 seconds, and 25 seconds into script execution a call such as set_time_limit(20) is made, the script will run for a total of 45 seconds before timing out.

Answer 1:
Create a full backup of your database: shell> mysqldump tab=/path/to/some/dir opt db_name
Or: shell> mysqlhotcopy db_name /path/to/some/dir
The full backup file is just a set of SQL statements, so restoring it is very easy:
shell> mysql “.”Executed”;
Answer 2:
To backup: BACKUP TABLE tbl_name TO /path/to/backup/directory
’ To restore: RESTORE TABLE tbl_name FROM /path/to/backup/directory
mysqldump: Dumping Table Structure and Data
Utility to dump a database or a collection of database for backup or for transferring the data to another SQL server (not necessarily a MySQL server). The dump will contain SQL statements to create the table and/or populate the table.
-t, no-create-info
Don’t write table creation information (the CREATE TABLE statement).
-d, no-data
Don’t write any row information for the table. This is very useful if you just want to get a dump of the structure for a table!

How to set cookies?
variable – name of the cookie variable
value – value of the cookie variable
time – expiry time
Example: setcookie(’Test’,$i,time()+3600);
Test – cookie variable name
$i – value of the variable ‘Test’
time()+3600 – denotes that the cookie will expire after an one hourHow to reset/destroy a cookie ?
Reset a cookie by specifying expire time in the past:
Example: setcookie(’Test’,$i,time()-3600); // already expired time
Reset a cookie by specifying its name only
Example: setcookie(’Test’);

What types of images that PHP supports ?
Using imagetypes() function to find out what types of images are supported in your PHP engine.imagetypes() – Returns the image types supported.
This function returns a bit-field corresponding to the image formats supported by the version of GD linked into PHP. The following bits are returned, IMG_GIF | IMG_JPG | IMG_PNG | IMG_WBMP | IMG_XPM.

Check if a variable is an integer in JAVASCRIPT ?
var myValue =9.8;
if(parseInt(myValue)== myValue)
alert(’Not an integer’);

Tools used for drawing ER diagrams.
Case Studio
Smart Draw

How can I know that a variable is a number or not using a JavaScript?
Answer 1:
bool is_numeric( mixed var)
Returns TRUE if var is a number or a numeric string, FALSE otherwise.
Answer 2:
Definition and Usage
The isNaN() function is used to check if a value is not a number.
Parameter Description
number Required. The value to be tested

How can we submit from without a submit button?
Trigger the JavaScript code on any event ( like onSelect of drop down list box, onfocus, etc ) document.myform.submit(); This will submit the form.

How many ways can we get the value of current session id?
session_id() returns the session id for the current session.

How can we destroy the cookie?
Set the cookie with a past expiration time.

What are the current versions of Apache, PHP, and MySQL?
PHP: PHP 5.2.5
MySQL: MySQL 5.1
Apache: Apache 2.1

What are the reasons for selecting LAMP (Linux, Apache, MySQL, Php) instead of combination of other software programs, servers and operating systems?
All of those are open source resource. Security of Linux is very more than windows. Apache is a better server that IIS both in functionality and security. Mysql is world most popular open source database. Php is more faster that asp or any other scripting language.

What are the features and advantages of OBJECT ORIENTED PROGRAMMING?
One of the main advantages of OO programming is its ease of modification; objects can easily be modified and added to a system there by reducing maintenance costs. OO programming is also considered to be better at modeling the real world than is procedural programming. It allows for more complicated and flexible interactions. OO systems are also easier for non-technical personnel to understand and easier for them to participate in the maintenance and enhancement of a system because it appeals to natural human cognition patterns. For some systems, an OO approach can speed development time since many objects are standard across systems and can be reused. Components that manage dates, shipping, shopping carts, etc. can be purchased and easily modified for a specific system.

How can we get second of the current time using date function?
$second = date(”s”);What is the use of friend function?
Friend functions
Sometimes a function is best shared among a number of different classes. Such functions can be declared either as member functions of one class or as global functions. In either case they can be set to be friends of other classes, by using a friend specifier in the class that is admitting them. Such functions can use all attributes of the class which names them as a friend, as if they were themselves members of that class. A friend declaration is essentially a prototype for a member function, but instead of requiring an implementation with the name of that class attached by the double colon syntax, a global function or member function of another class provides the match.class mylinkage
mylinkage * prev;
mylinkage * next;
friend void set_prev(mylinkage* L, mylinkage* N);
void set_next(mylinkage* L);
mylinkage * succ();
mylinkage * pred();
void mylinkage::set_next(mylinkage* L) { next = L; }
void set_prev(mylinkage * L, mylinkage * N ) { N->prev = L; }
Friends in other classes
It is possible to specify a member function of another class as a friend as follows:
class C
friend int B::f1();
class B
int f1();
It is also possible to specify all the functions in another class as friends, by specifying the entire class as a friend.
class A
friend class B;
Friend functions allow binary operators to be defined which combine private data in a pair of objects. This is particularly powerful when using the operator overloading features of C++. We will return to it when we look at overloading.

How can we get second of the current time using date function?
$second = date(”s”);

What is the maximum size of a file that can be uploaded using PHP and how can we change this?
You can change maximum size of a file set upload_max_filesize variable in php.ini file

How can I make a script that can be bilingual (supports English, German)?
You can change char set variable in above line in the script to support bi language.

What are the difference between abstract class and interface?
Abstract class: abstract classes are the class where one or more methods are abstract but not necessarily all method has to be abstract. Abstract methods are the methods, which are declare in its class but not define. The definition of those methods must be in its extending class.
Interface: Interfaces are one type of class where all the methods are abstract. That means all the methods only declared but not defined. All the methods must be define by its implemented class.What are the advantages of stored procedures, triggers, indexes?
A stored procedure is a set of SQL commands that can be compiled and stored in the server. Once this has been done, clients don’t need to keep re-issuing the entire query but can refer to the stored procedure. This provides better overall performance because the query has to be parsed only once, and less information needs to be sent between the server and the client. You can also raise the conceptual level by having libraries of functions in the server. However, stored procedures of course do increase the load on the database server system, as more of the work is done on the server side and less on the client (application) side. Triggers will also be implemented. A trigger is effectively a type of stored procedure, one that is invoked when a particular event occurs. For example, you can install a stored procedure that is triggered each time a record is deleted from a transaction table and that stored procedure automatically deletes the corresponding customer from a customer table when all his transactions are deleted. Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. If a table has 1,000 rows, this is at least 100 times faster than reading sequentially. If you need to access most of the rows, it is faster to read sequentially, because this minimizes disk seeks.

What is maximum size of a database in mysql?
If the operating system or filesystem places a limit on the number of files in a directory, MySQL is bound by that constraint. The efficiency of the operating system in handling large numbers of files in a directory can place a practical limit on the number of tables in a database. If the time required to open a file in the directory increases significantly as the number of files increases, database performance can be adversely affected.
The amount of available disk space limits the number of tables.
MySQL 3.22 had a 4GB (4 gigabyte) limit on table size. With the MyISAM storage engine in MySQL 3.23, the maximum table size was increased to 65536 terabytes (2567 – 1 bytes). With this larger allowed table size, the maximum effective table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits.
The InnoDB storage engine maintains InnoDB tables within a tablespace that can be created from several files. This allows a table to exceed the maximum individual file size. The tablespace can include raw disk partitions, which allows extremely large tables. The maximum tablespace size is 64TB.
The following table lists some examples of operating system file-size limits. This is only a rough guide and is not intended to be definitive. For the most up-to-date information, be sure to check the documentation specific to your operating system.
Operating System File-size Limit
Linux 2.2-Intel 32-bit 2GB (LFS: 4GB)
Linux 2.4+ (using ext3 filesystem) 4TB
Solaris 9/10 16TB
NetWare w/NSS filesystem 8TB
Win32 w/ FAT/FAT32 2GB/4GB
Win32 w/ NTFS 2TB (possibly larger)
MacOS X w/ HFS+ 2TB

Explain normalization concept?
The normalization process involves getting our data to conform to three progressive normal forms, and a higher level of normalization cannot be achieved until the previous levels have been achieved (there are actually five normal forms, but the last two are mainly academic and will not be discussed).
First Normal Form
The First Normal Form (or 1NF) involves removal of redundant data from horizontal rows. We want to ensure that there is no duplication of data in a given row, and that every column stores the least amount of information possible (making the field atomic).
Second Normal Form
Where the First Normal Form deals with redundancy of data across a horizontal row, Second Normal Form (or 2NF) deals with redundancy of data in vertical columns. As stated earlier, the normal forms are progressive, so to achieve Second Normal Form, your tables must already be in First Normal Form.
Third Normal Form
I have a confession to make; I do not often use Third Normal Form. In Third Normal Form we are looking for data in our tables that is not fully dependant on the primary key, but dependant on another value in the table

What’s the difference between accessing a class method via -> and via ::?
:: is allowed to access methods that can perform static operations, i.e. those, which do not require object initialization.

What are the advantages and disadvantages of CASCADE STYLE SHEETS?
External Style Sheets
Can control styles for multiple documents at once Classes can be created for use on multiple HTML element types in many documents Selector and grouping methods can be used to apply styles under complex contexts
An extra download is required to import style information for each document The rendering of the document may be delayed until the external style sheet is loaded Becomes slightly unwieldy for small quantities of style definitions
Embedded Style Sheets
Classes can be created for use on multiple tag types in the document Selector and grouping methods can be used to apply styles under complex contexts No additional downloads necessary to receive style information
This method can not control styles for multiple documents at once
Inline Styles
Useful for small quantities of style definitions Can override other style specification methods at the local level so only exceptions need to be listed in conjunction with other style methods
Does not distance style information from content (a main goal of SGML/HTML) Can not control styles for multiple documents at once Author can not create or control classes of elements to control multiple element types within the document Selector grouping methods can not be used to create complex element addressing scenarios

What type of inheritance that php supports?
In PHP an extended class is always dependent on a single base class, that is, multiple inheritance is not supported. Classes are extended using the keyword ‘extends’.

How can increase the performance of MySQL select query?
We can use LIMIT to stop MySql for further search in table after we have received our required no. of records, also we can use LEFT JOIN or RIGHT JOIN instead of full join in cases we have related data in two or more tables.

How can we change the name of a column of a table?
MySQL query to rename table: RENAME TABLE tbl_name TO new_tbl_nameALTER TABLE tableName CHANGE OldName newName.

When you want to show some part of a text displayed on an HTML page in red font color? What different possibilities are there to do this? What are the advantages/disadvantages of these methods?
There are 2 ways to show some part of a text in red:
1. Using HTML tag
2. Using HTML tag

When viewing an HTML page in a Browser, the Browser often keeps this page in its cache. What can be possible advantages/disadvantages of page caching? How can you prevent caching of a certain page (please give several alternate solutions)?
When you use the metatag in the header section at the beginning of an HTML Web page, the Web page may still be cached in the Temporary Internet Files folder.
A page that Internet Explorer is browsing is not cached until half of the 64 KB buffer is filled. Usually, metatags are inserted in the header section of an HTML document, which appears at the beginning of the document. When the HTML code is parsed, it is read from top to bottom. When the metatag is read, Internet Explorer looks for the existence of the page in cache at that exact moment. If it is there, it is removed. To properly prevent the Web page from appearing in the cache, place another header section at the end of the HTML document.

What are the different ways to login to a remote server? Explain the means, advantages and disadvantages?
There is at least 3 ways to logon to a remote server:
Use ssh or telnet if you concern with security
You can also use rlogin to logon to a remote server.Please give a regular expression (preferably Perl/PREG style), which can be used to identify the URL from within a HTML link tag.
Try this: /href=”([^"]*)”/iHow can I use the COM components in php?
The COM class provides a framework to integrate (D)COM components into your PHP scripts.
string COM::COM( string module_name [, string server_name [, int codepage]]) – COM class constructor.
module_name: name or class-id of the requested component.
server_name: name of the DCOM server from which the component should be fetched. If NULL, localhost is assumed. To allow DCOM com, allow_dcom has to be set to TRUE in php.ini.
codepage – specifies the codepage that is used to convert php-strings to unicode-strings and vice versa. Possible values are CP_ACP, CP_MACCP, CP_OEMCP, CP_SYMBOL, CP_THREAD_ACP, CP_UTF7 and CP_UTF8.
$word->Visible = 1; //open an empty document
$word->Documents->Add(); //do some weird stuff
$word->Selection->TypeText(”This is a test…”);
$word->Documents[1]->SaveAs(”Useless test.doc”); //closing word
$word->Quit(); //free the object
$word = null;

How many ways we can give the output to a browser?
HTML output
PHP, ASP, JSP, Servlet Function
Script Language output Function
Different Type of embedded Package to output to a browser

What is the default session time in php and how can I change it?
The default session time in php is until closing of browser

What changes I have to do in php.ini file for file uploading?
Make the following line uncomment like:
; Whether to allow HTTP file uploads.
file_uploads = On
; Temporary directory for HTTP uploaded files (will use system default if not
; specified).
upload_tmp_dir = C:\apache2triad\temp
; Maximum allowed size for uploaded files.
upload_max_filesize = 2MHow can I set a cron and how can I execute it in Unix, Linux, and windows?
Cron is very simply a Linux module that allows you to run commands at predetermined times or intervals. In Windows, it’s called Scheduled Tasks. The name Cron is in fact derived from the same word from which we get the word chronology, which means order of time.
The easiest way to use crontab is via the crontab command.# crontabThis command ‘edits’ the crontab. Upon employing this command, you will be able to enter the commands that you wish to run. My version of
Linux uses the text editor vi. You can find information on using vi here.
The syntax of this file is very important – if you get it wrong, your crontab will not function properly. The syntax of the file should be as follows:
minutes hours day_of_month month day_of_week command
All the variables, with the exception of the command itself, are numerical constants. In addition to an asterisk (*), which is a wildcard that allows any value, the ranges permitted for each field are as follows:
Minutes: 0-59
Hours: 0-23
Day_of_month: 1-31
Month: 1-12
Weekday: 0-6
We can also include multiple values for each entry, simply by separating each value with a comma.
command can be any shell command and, as we will see momentarily, can also be used to execute a Web document such as a PHP file.
So, if we want to run a script every Tuesday morning at 8:15 AM, our mycronjob file will contain the following content on a single line:
15 8 * * 2 /path/to/scriptname
This all seems simple enough, right? Not so fast! If you try to run a PHP script in this manner, nothing will happen (barring very special configurations that have PHP compiled as an executable, as opposed to an Apache module). The reason is that, in order for PHP to be parsed, it needs to be passed through Apache. In other words, the page needs to be called via a browser or other means of retrieving
Web content. For our purposes, I’ll assume that your server configuration includes wget, as is the case with most default configurations. To test your configuration, log in to shell. If you’re using an RPM-based system (e.g. Redhat or Mandrake), type the following:
# wget help
If you are greeted with a wget package identification, it is installed in your system.
You could execute the PHP by invoking wget on the URL to the page, like so:
# wget http://www.example.com/file.php
Now, let’s go back to the mailstock.php file we created in the first part of this article. We saved it in our document root, so it should be accessible via the Internet. Remember that we wanted it to run at 4PM Eastern time, and send you your precious closing bell report? Since I’m located in the Eastern timezone, we can go ahead and set up our crontab to use 4:00, but if you live elsewhere, you might have to compensate for the time difference when setting this value.
This is what my crontab will look like:
0 4 * * 1,2,3,4,5

Steps for the payment gateway processing?
An online payment gateway is the interface between your merchant account and your Web site. The online payment gateway allows you to immediately verify credit card transactions and authorize funds on a customer’s credit card directly from your Web site. It then passes the transaction off to your merchant bank for processing, commonly referred to as transaction batching

How many ways I can redirect a PHP page?
Here are the possible ways of php page redirection.
1. Using Java script:
‘; echo ‘window.location.href=”‘.$filename.’”;’; echo ”; echo ”; echo ”; echo ”; } } redirect(’http://maosjb.com’); ?>
2. Using php function: header(”Location:http://maosjb.com “);

List out different arguments in PHP header function?
void header ( string string [, bool replace [, int http_response_code]])

What type of headers have to be added in the mail function to attach a file?
$boundary = ‘–’ . md5( uniqid ( rand() ) );
$headers = “From: \”Me\”\n”;
$headers .= “MIME-Version: 1.0\n”;
$headers .= “Content-Type: multipart/mixed; boundary=\”$boundary\”";

What is the difference between Reply-to and Return-path in the headers of a mail function?
Reply-to: Reply-to is where to delivery the reply of the mail.
Return-path: Return path is when there is a mail delivery failure occurs then where to delivery the failure notification.How to store the uploaded file to the final location?
move_uploaded_file ( string filename, string destination)
This function checks to ensure that the file designated by filename is a valid upload file (meaning that it was uploaded via PHP’s HTTP POST upload mechanism). If the file is valid, it will be moved to the filename given by destination.
If filename is not a valid upload file, then no action will occur, and move_uploaded_file() will return FALSE.If filename is a valid upload file, but cannot be moved for some reason, no action will occur, and move_uploaded_file() will return FALSE. Additionally, a warning will be issued.

Explain about Type Juggling in php?
PHP does not require (or support) explicit type definition in variable declaration; a variable’s type is determined by the context in which that variable is used. That is to say, if you assign a string value to variable $var, $var becomes a string. If you then assign an integer value to $var, it becomes an integer.
An example of PHP’s automatic type conversion is the addition operator ‘+’. If any of the operands is a float, then all operands are evaluated as floats, and the result will be a float. Otherwise, the operands will be interpreted as integers, and the result will also be an integer. Note that this does NOT change the types of the operands themselves; the only change is in how the operands are evaluated.
$foo += 2; // $foo is now an integer (2)
$foo = $foo + 1.3; // $foo is now a float (3.3)
$foo = 5 + “10 Little Piggies”; // $foo is integer (15)
$foo = 5 + “10 Small Pigs”; // $foo is integer (15)
If the last two examples above seem odd, see String conversion to numbers.
If you wish to change the type of a variable, see settype().
If you would like to test any of the examples in this section, you can use the var_dump() function.
Note: The behavior of an automatic conversion to array is currently undefined.
Since PHP (for historical reasons) supports indexing into strings via offsets using the same syntax as array indexing, the example above leads to a problem: should $a become an array with its first element being “f”, or should “f” become the first character of the string $a? The current versions of PHP interpret the second assignment as a string offset identification, so $a becomes “f”, the result of this automatic conversion however should be considered undefined. PHP 4 introduced the new curly bracket syntax to access characters in string, use this syntax instead of the one presented above:

How can I embed a java programme in php file and what changes have to be done in php.ini file?
There are two possible ways to bridge PHP and Java: you can either integrate PHP into a Java Servlet environment, which is the more stable and efficient solution, or integrate Java support into PHP. The former is provided by a SAPI module that interfaces with the Servlet server, the latter by this Java extension.
The Java extension provides a simple and effective means for creating and invoking methods on Java objects from PHP. The JVM is created using JNI, and everything runs in-process.
Example Code:
getProperty(’java.version’) . ”; echo ‘Java vendor=’ . $system->getProperty(’java.vendor’) . ”; echo ‘OS=’ . $system->getProperty(’os.name’) . ‘ ‘ . $system->getProperty(’os.version’) . ‘ on ‘ . $system->getProperty(’os.arch’) . ‘ ‘; // java.util.Date example $formatter = new Java(’java.text.SimpleDateFormat’, “EEEE, MMMM dd, yyyy ‘at’ h:mm:ss a zzzz”); echo $formatter->format(new Java(’java.util.Date’)); ?>
The behaviour of these functions is affected by settings in php.ini.
Table 1. Java configuration options
Name Default Changeable

Explain the ternary conditional operator in PHP?
Expression preceding the ? is evaluated, if it’s true, then the expression preceding the : is executed, otherwise, the expression following : is executed.

What’s the difference between include and require?
It’s how they handle failures. If the file is not found by require(), it will cause a fatal error and halt the execution of the script. If the file is not found by include(), a warning will be issued, but execution will continue.

How many ways can we get the value of current session id?
session_id() returns the session id for the current session.

Q.1 What is joomla in php?

ANSWER: Joomla is an open source content management system.

Q.2 What is zend engine?

ANSWER: Zend engine is used internally by php as a compiler and runtime engine...

Q.3 Joomla! Features:

Completely database driven site engines
*News, products or services sections fully editable and manageable
*Topics sections can be added to by contributing authors
*Fully customizable layouts including left, center and right menu boxes
*Browser upload of images to your own library for use anywhere in the site
*Dynamic Forum/Poll/Voting booth for on-the-spot results
*Runs on Linux, FreeBSD, MacOSX server, Solaris and AIX

Q.4 What are some real world examples of what Joomla! can do?

Joomla is used all over the world to power Web sites of all shapes and sizes. For example:

* Corporate Web sites or portals
* Corporate intranets and extranets
* Online magazines, newspapers, and publications
* E-commerce and online reservations
* Government applications
* Small business Web sites
* Non-profit and organizational Web sites
* Community-based portals
* School and church Web sites
* Personal or family homepages

What is the difference between "echo" and "print" in PHP?
Latest Answer: echo is constructor and print is function.echo cannot take arguments but print can take arguments. ...

Which will execute faster POST or GET? Explain
Latest Answer: POST method using for the moving into another pages means we are using two pages. But GET methods using for the within the page (exp pagination & url calling)

How to prevent form hijacking in PHP?
Latest Answer: Few more coding practices can be done to avoid PP Form Hijacking User Input
Sanitization-Never trust web user submitted data. Follow good clieint side data validation
practices with regular expressions before submitting data to the server.Form Submision ...

What is the difference between session_register and $_session?
Latest Answer: session_register() is used for register one or more global variables with the

current session. While $_SESSION[] array is used for storing one or more variables with in the  current session array. session_register() depends upon register_global is enable

What are new features that are in added in PHP5?
Latest Answer: PHP5----Strong OOPS support 1) Data Encapsulation (public, private, protect visibility for member variables and methods) 2) Concepts like Abstraction, Inheritance, Reflections(reverse engineering APIs) 3) More Magic Functions like __construct, ...

While in case of download, How will you make a percentage bar showing current status of total 100%?
Latest Answer: Use Following Tips for showing Processing Percentage Bar 1. Use Image for displaying Percentage Bar.2. Change width of Image depending on Processing completed. ...

What are the different methods to optimize the MySQL query?
Latest Answer: It does not depend upon single entity. In order to optimize it you can look
into following things. - Make sure table has primary key. - If there is any field which is
used more frequently for searching then index it!. - The order of condition does ...

How will you connect to the Database using Joomla?
Latest Answer: What Vikas said is absolutely Correct. In Joomla there is no need to write the code for database connectivityIf we hosted the site local into web, we have to change the configuration.php fileIn that we have to mention the database username and password

How to call a C function within PHP?
Latest Answer: This is not strictly true.PHP has many native functions that have the same
name as C functions, and which act in similar ways, but they are not C functions, they are
PHP functions. ...

What is the purpose of ob_start() ?
Latest Answer: This function will redirect the output to a buffer. We can flush out the
buffer ob_end_flush(). ob_start must be called before any output is made. so the apt place is immediately after opening the php tag. ...

My SQL Interview Questions and Answers

What's MySQL ?
MySQL (pronounced "my ess cue el") is an open source relational database management system (RDBMS) that uses Structured Query Language (SQL), the most popular language for adding, accessing, and processing data in a database. Because it is open source, anyone can download MySQL and tailor it to their needs in accordance with the general public license. MySQL is noted mainly for its speed, reliability, and flexibility. ...

What is DDL, DML and DCL ?
If you look at the large variety of SQL commands, they can be divided into three large subgroups. Data Definition Language deals with database schemas and descriptions of how the data should reside in the database, therefore language statements like CREATE TABLE or ALTER TABLE belong to DDL. DML deals with data manipulation, and therefore includes most common SQL statements such SELECT, INSERT, etc. Data Control Language includes commands such as GRANT, and mostly concerns with rights, permissions and other controls of the database system.

How do you get the number of rows affected by query?
SELECT COUNT (user_id) FROM users would only return the number of user_id’s.

If the value in the column is repeatable, how do you find out the unique values?
Use DISTINCT in the query, such as SELECT DISTINCT user_firstname FROM users; You can also ask for a number of distinct values by saying SELECT COUNT (DISTINCT user_firstname) FROM users;

How do you return the a hundred books starting from 25th?
SELECT book_title FROM books LIMIT 25, 100. The first number in LIMIT is the offset, the second is the number.

You wrote a search engine that should retrieve 10 results at a time, but at the same time you’d like to know how many rows there’re total. How do you display that to the user?
SELECT SQL_CALC_FOUND_ROWS page_title FROM web_pages LIMIT 1,10; SELECT FOUND_ROWS(); The second query (not that COUNT() is never used) will tell you how many results there’re total, so you can display a phrase "Found 13,450,600 results, displaying 1-10". Note that FOUND_ROWS does not pay attention to the LIMITs you specified and always returns the total number of rows affected by query.

How would you write a query to select all teams that won either 2, 4, 6 or 8 games?
SELECT team_name FROM teams WHERE team_won IN (2, 4, 6, 8)

How would you select all the users, whose phone number is null?
SELECT user_name FROM users WHERE ISNULL(user_phonenumber);

What does this query mean: SELECT user_name, user_isp FROM users LEFT JOIN isps USING (user_id) ?
It’s equivalent to saying SELECT user_name, user_isp FROM users LEFT JOIN isps WHERE users.user_id=isps.user_id

How do you find out which auto increment was assigned on the last insert?

SELECT LAST_INSERT_ID() will return the last value assigned by the auto_increment function. Note that you don’t have to specify the table name.

What does –i-am-a-dummy flag to do when starting MySQL?
Makes the MySQL engine refuse UPDATE and DELETE commands where the WHERE clause is not present.

On executing the DELETE statement I keep getting the error about foreign key constraint failing. What do I do?
What it means is that so of the data that you’re trying to delete is still alive in another table. Like if you have a table for universities and a table for students, which contains the ID of the university they go to, running a delete on a university table will fail if the students table still contains people enrolled at that university. Proper way to do it would be to delete the offending data first, and then delete the university in question. Quick way would involve running SET foreign_key_checks=0 before the DELETE command, and setting the parameter back to 1 after the DELETE is done. If your foreign key was formulated with ON DELETE CASCADE, the data in dependent tables will be removed automatically.

When would you use ORDER BY in DELETE statement?
When you’re not deleting by row ID. Such as in DELETE FROM techpreparation_com_questions ORDER BY timestamp LIMIT 1. This will delete the most recently posted question in the table techpreparation_com_questions.

How can you see all indexes defined for a table?
SHOW INDEX FROM techpreparation_questions;

How would you change a column from VARCHAR(10) to VARCHAR(50)?
ALTER TABLE techpreparation_questions CHANGE techpreparation_content techpreparation_CONTENT VARCHAR(50).

How would you delete a column?
ALTER TABLE techpreparation_answers DROP answer_user_id.

My SQL Interview Questions and Answers

How would you change a table to InnoDB?
ALTER TABLE techpreparation_questions ENGINE innodb;

When you create a table, and then run SHOW CREATE TABLE on it, you occasionally get different results than what you typed in. What does MySQL modify in your newly created tables?
1. VARCHARs with length less than 4 become CHARs
2. CHARs with length more than 3 become VARCHARs.
3. NOT NULL gets added to the columns declared as PRIMARY KEYs
4. Default values such as NULL are specified for each column

How do I find out all databases starting with ‘tech’ to which I have access to?

How do you concatenate strings in MySQL?
CONCAT (string1, string2, string3)

How do you get a portion of a string?
SELECT SUBSTR(title, 1, 10) from techpreparation_questions;

What’s the difference between CHAR_LENGTH and LENGTH?
The first is, naturally, the character count. The second is byte count. For the Latin characters the numbers are the same, but they’re not the same for Unicode and other encodings.

How do you convert a string to UTF-8?

SELECT (techpreparation_question USING utf8);

What do % and _ mean inside LIKE statement?
% corresponds to 0 or more characters, _ is exactly one character.

What does + mean in REGEXP?
At least one character. Appendix G. Regular Expressions from MySQL manual is worth perusing before the interview.

How do you get the month from a timestamp?
SELECT MONTH(techpreparation_timestamp) from techpreparation_questions;

How do you offload the time/date handling to MySQL?
SELECT DATE_FORMAT(techpreparation_timestamp, ‘%Y-%m-%d’) from techpreparation_questions; A similar TIME_FORMAT function deals with time.

How do you add three minutes to a date?
ADDDATE(techpreparation_publication_date, INTERVAL 3 MINUTE)

What’s the difference between Unix timestamps and MySQL timestamps?
Internally Unix timestamps are stored as 32-bit integers, while MySQL timestamps are stored in a similar manner, but represented in readable YYYY-MM-DD HH:MM:SS format.

How do you convert between Unix timestamps and MySQL timestamps?
UNIX_TIMESTAMP converts from MySQL timestamp to Unix timestamp, FROM_UNIXTIME converts from Unix timestamp to MySQL timestamp.

What are ENUMs used for in MySQL?
You can limit the possible values that go into the table. CREATE TABLE months (month ENUM ‘January’, ‘February’, ‘March’,…); INSERT months VALUES (’April’);

How are ENUMs and SETs represented internally?
As unique integers representing the powers of two, due to storage optimizations.

How do you start and stop MySQL on Windows?
net start MySQL, net stop MySQL

How do you start MySQL on Linux?
/etc/init.d/mysql start

Explain the difference between mysql and mysql interfaces in PHP?
mysqli is the object-oriented version of mysql library functions.

What’s the default port for MySQL Server?

What does tee command do in MySQL?
tee followed by a filename turns on MySQL logging to a specified file. It can be stopped by command note.

Can you save your connection settings to a conf file?
Yes, and name it ~/.my.conf. You might want to change the permissions on the file to 600, so that it’s not readable by others.

How do you change a password for an existing user via mysqladmin?
mysqladmin -u root -p password "newpassword"

Use mysqldump to create a copy of the database?
mysqldump -h mysqlhost -u username -p mydatabasename > dbdump.sql

Have you ever used MySQL Administrator and MySQL Query Browser?
Describe the tasks you accomplished with these tools.

What are some good ideas regarding user security in MySQL?
There is no user without a password. There is no user without a user name. There is no user whose Host column contains % (which here indicates that the user can log in from anywhere in the network or the Internet). There are as few users as possible (in the ideal case only root) who have unrestricted access.

Explain the difference between MyISAM Static and MyISAM Dynamic. ?
In MyISAM static all the fields have fixed width. The Dynamic MyISAM table would include fields such as TEXT, BLOB, etc. to accommodate the data types with various lengths. MyISAM Static would be easier to restore in case of corruption, since even though you might lose some data, you know exactly where to look for the beginning of the next record.

What does myisamchk do?
It compressed the MyISAM tables, which reduces their disk usage.

Explain advantages of InnoDB over MyISAM?
Row-level locking, transactions, foreign key constraints and crash recovery.

Explain advantages of MyISAM over InnoDB?
Much more conservative approach to disk space management - each MyISAM table is stored in a separate file, which could be compressed then with myisamchk if needed. With InnoDB the tables are stored in tablespace, and not much further optimization is possible. All data except for TEXT and BLOB can occupy 8,000 bytes at most. No full text indexing is available for InnoDB. TRhe COUNT(*)s execute slower than in MyISAM due to tablespace complexity.


SQL Server 2008 Interview Questions and Answers.

What is RDBMS?
Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.

What is normalization?
Database normalization is a data design and organization process applied to data structures based on rules that help build relational databases. In relational database design, the process of organizing data to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

What are different normalization forms?

1NF: Eliminate Repeating Groups
Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.
2NF: Eliminate Redundant Data
If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3NF: Eliminate Columns Not Dependent On Key
If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key
BCNF: Boyce-Codd Normal Form
If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
4NF: Isolate Independent Multiple Relationships
No table may contain two or more 1:n or n:m relationships that are not directly related.
5NF: Isolate Semantically Related Multiple Relationships
There may be practical constrains on information that justify separating logically related many-to-many relationships.
ONF: Optimal Normal Form
A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF: Domain-Key Normal Form
A model free from all modification anomalies.

Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.

What is Stored Procedure?
A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
e.g. sp_helpdb, sp_renamedb, sp_depends etc.

What is Trigger?
A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS.Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; the DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.
Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.

What is View?
A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.

What is Index?
An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.

Clustered indexes define the physical sorting of a database table’s rows in the storage media. For this reason, each database table may have only one clustered index.
Non-clustered indexes are created outside of the database table and contain a sorted list of references to the table itself.

hat is the difference between clustered and a non-clustered index?
A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

What are the different index configurations a table can have?
A table can have one of the following index configurations:

No indexes
A clustered index
A clustered index and many nonclustered indexes
A nonclustered index
Many nonclustered indexes

What is cursors?
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.

In order to work with a cursor we need to perform some steps in the following order:

Declare cursor
Open cursor
Fetch row from the cursor
Process fetched row
Close cursor
Deallocate cursor

What is the use of DBCC commands?
DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.
E.g. DBCC CHECKDB – Ensures that tables in the db and the indexes are correctly linked.
DBCC CHECKALLOC – To check that all pages in a db are correctly allocated.
DBCC CHECKFILEGROUP – Checks all tables file group for any damage.

What is a Linked Server?
Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data.
Storped Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.

What is Collation?
Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

What are different type of Collation Sensitivity?
Case sensitivity
A and a, B and b, etc.

Accent sensitivity
a and á, o and ó, etc.

Kana Sensitivity
When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.

Width sensitivity
When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.

What’s the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

What is a NOLOCK?
Using the NOLOCK query optimiser hint is generally considered good practice in order to improve concurrency on a busy system. When the NOLOCK hint is included in a SELECT statement, no locks are taken when data is read. The result is a Dirty Read, which means that another process could be updating the data at the exact time you are reading it. There are no guarantees that your query will retrieve the most recent data. The advantage to performance is that your reading of data will not block updates from taking place, and updates will not block your reading of data. SELECT statements take Shared (Read) locks. This means that multiple SELECT statements are allowed simultaneous access, but other processes are blocked from modifying the data. The updates will queue until all the reads have completed, and reads requested after the update will wait for the updates to complete. The result to your system is delay(blocking).

What is difference between DELETE & TRUNCATE commands?
Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.
TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column.
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
TRUNCATE can not be Rolled back using logs.
TRUNCATE is DDL Command.
TRUNCATE Resets identity of the table.

DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
DELETE Can be used with or without a WHERE clause
DELETE Activates Triggers.
DELETE Can be Rolled back using logs.
DELETE is DML Command.
DELETE does not reset identity of the table.

Difference between Function and Stored Procedure?
UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
Inline UDF’s can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

When is the use of UPDATE_STATISTICS command?
This command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

What types of Joins are possible with Sql Server?
Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query. HAVING criteria is applied after the the grouping of rows has occurred.

What is sub-query? Explain properties of sub-query.
Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of parentheses. Sub-queries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword.

A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery SELECT statement if executed independently of the T-SQL statement, in which it is nested, will return a result set. Meaning a subquery SELECT statement can standalone and is not depended on the statement in which it is nested. A subquery SELECT statement can return any number of values, and can be found in, the column list of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL statement. A Subquery can also be used as a parameter to a function call. Basically a subquery can be used anywhere an expression can be used.

Properties of Sub-Query
A subquery must be enclosed in the parenthesis.
A subquery must be put in the right hand of the comparison operator, and
A subquery cannot contain a ORDER-BY clause.
A query can contain more than one sub-queries.

What are types of sub-queries?
Single-row subquery, where the subquery returns only one row.
Multiple-row subquery, where the subquery returns multiple rows,.and
Multiple column subquery, where the subquery returns multiple columns.

What is SQL Profiler?
SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performance by executing too slowly.

Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

What is User Defined Functions?
User-Defined Functions allow to define its own T-SQL functions that can accept 0 or more parameters and return a single scalar data value or a table data type.

What kind of User-Defined Functions can be created?
There are three types of User-Defined functions in SQL Server 2000 and they are Scalar, Inline Table-Valued and Multi-statement Table-valued.

Scalar User-Defined Function
A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value.

Inline Table-Value User-Defined Function
An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables.

Multi-statement Table-Value User-Defined Function
A Multi-Statement Table-Value user-defined function returns a table and is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a T-SQL select command or a group of them gives us the capability to in essence create a parameterized, non-updateable view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, It can be used in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets.

Which TCP/IP port does SQL Server run on? How can it be changed?
SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties –> Port number.both on client and the server.

What are the authentication modes in SQL Server? How can it be changed?
Windows mode and mixed mode (SQL & Windows).

To change authentication mode in SQL Server click Start, Programs, Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group. Select the server then from the Tools menu select SQL Server Configuration Properties, and choose the Security page.

Where are SQL server users names and passwords are stored in sql server?
They get stored in master db in the sysxlogins table.

Which command using Query Analyzer will give you the version of SQL server and operating system?
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'),
What is SQL server agent?
SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA). It is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the implementation of tasks for the DBA, with its full-function scheduling engine, which allows you to schedule your own jobs and scripts.

Can a stored procedure call itself or recursive stored procedure? How many level SP nesting possible?
Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.

What is @@ERROR?
The @@ERROR automatic variable returns the error code of the last Transact-SQL statement. If there was no error, @@ERROR returns zero. Because @@ERROR is reset after each Transact-SQL statement, it must be saved to a variable if it is needed to process it further after checking it.

What is Raiseerror?
Stored procedures report errors to client applications via the RAISERROR command. RAISERROR doesn’t change the flow of a procedure; it merely displays an error message, sets the @@ERROR automatic variable, and optionally writes the message to the SQL Server error log and the NT application event log.

What is log shipping?
Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports log shipping. In log shipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db can be used this as the Disaster Recovery plan. The key feature of log shipping is that is will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval.

What is the difference between a local and a global variable?
A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.

A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection are closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

What command do we use to rename a db?
sp_renamedb ‘oldname’ , ‘newname’
If someone is using db it will not accept sp_renmaedb. In that case first bring db to single user using sp_dboptions. Use sp_renamedb to rename database. Use sp_dboptions to bring database to multi user mode.

What is sp_configure commands and set commands?
Use sp_configure to display or change server-level settings. To change database-level settings, use ALTER DATABASE. To change settings that affect only the current user session, use the SET statement.

What are the different types of replication? Explain.
The SQL Server 2000-supported replication types are as follows:

  • Transactional
  • Snapshot
  • Merge

Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. Snapshot replication is best used as a method for replicating data that changes infrequently or where the most up-to-date values (low latency) are not a requirement. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.

Transactional replication, an initial snapshot of data is applied at Subscribers, and then when data modifications are made at the Publisher, the individual transactions are captured and propagated to Subscribers.

Merge replication is the process of distributing data from Publisher to Subscribers, allowing the Publisher and Subscribers to make updates while connected or disconnected, and then merging the updates between sites when they are connected.

What are the OS services that the SQL Server installation adds?
MS SQL SERVER SERVICE, SQL AGENT SERVICE, DTC (Distribution transac co-ordinator)

What are three SQL keywords used to change or set someone’s permissions?

What does it mean to have quoted_identifier on? What are the implications of having it off?
When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.

What is the STUFF function and how does it differ from the REPLACE function?
STUFF function to overwrite existing characters. Using this syntax, STUFF(string_expression, start, length, replacement_characters), string_expression is the string that will have characters substituted, start is the starting position, length is the number of characters in the string that are substituted, and replacement_characters are the new characters interjected into the string.
REPLACE function to replace existing characters of all occurance. Using this syntax REPLACE(string_expression, search_string, replacement_string), where every incidence of search_string found in the string_expression will be replaced with replacement_string.

Using query analyzer, name 3 ways to get an accurate count of the number of records in a table?
FROM table1
FROM table1
FROM sysindexes
WHERE id = OBJECT_ID(table1)
AND indid <>
How to rebuild Master Database?
Shutdown Microsoft SQL Server 2000, and then run Rebuildm.exe. This is located in the Program Files\Microsoft SQL Server\80\Tools\Binn directory.
In the Rebuild Master dialog box, click Browse.
In the Browse for Folder dialog box, select the \Data folder on the SQL Server 2000 compact disc or in the shared network directory from which SQL Server 2000 was installed, and then click OK.
Click Settings. In the Collation Settings dialog box, verify or change settings used for the master database and all other databases.
Initially, the default collation settings are shown, but these may not match the collation selected during setup. You can select the same settings used during setup or select new collation settings. When done, click OK.
In the Rebuild Master dialog box, click Rebuild to start the process.
The Rebuild Master utility reinstalls the master database.
To continue, you may need to stop a server that is running.
Source: http://msdn2.microsoft.com/en-us/library/aa197950(SQL.80).aspx

What is the basic functions for master, msdb, model, tempdb databases?
The Master database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care.
The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.
The tempdb holds temporary objects such as global and local temporary tables and stored procedures.
The model is essentially a template database used in the creation of any new user database created in the instance.

What are primary keys and foreign keys?
Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A table can have only one Primary key.
Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables.

What is data integrity? Explain constraints?
Data integrity is an important feature in SQL Server. When used properly, it ensures that data is accurate, correct, and valid. It also acts as a trap for otherwise undetectable bugs within applications.

A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.

A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

What are the properties of the Relational tables?
Relational tables have six properties:

  • Values are atomic.
  • Column values are of the same kind.
  • Each row is unique.
  • The sequence of columns is insignificant.
  • The sequence of rows is insignificant.
  • Each column must have a unique name.

What is De-normalization?
De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

How to get @@error and @@rowcount at the same time?
If @@Rowcount is checked after Error checking statement then it will have 0 as the value of @@Recordcount as it would have been reset.
And if @@Recordcount is checked before the error-checking statement then @@Error would get reset. To get @@error and @@rowcount at the same time do both in same statement and store them in local variable. SELECT @RC = @@ROWCOUNT, @ER = @@ERROR

What is Identity?
Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers, the value of this cannot be controled. Identity/GUID columns do not need to be indexed.

What is a Scheduled Jobs or What is a Scheduled Tasks?
Scheduled tasks let user automate processes that run on regular or predictable cycles. User can schedule administrative tasks, such as cube processing, to run during times of slow business activity. User can also determine the order in which tasks run by creating job steps within a SQL Server Agent job. E.g. Back up database, Update Stats of Tables. Job steps give user control over flow of execution. If one job fails, user can configure SQL Server Agent to continue to run the remaining tasks or to stop execution.

What is a table called, if it does not have neither Cluster nor Non-cluster Index? What is it used for?
Unindexed table or Heap. Microsoft Press Books and Book On Line (BOL) refers it as Heap.
A heap is a table that does not have a clustered index and, therefore, the pages are not linked by pointers. The IAM pages are the only structures that link the pages in a table together.
Unindexed tables are good for fast storing of data. Many times it is better to drop all indexes from table and than do bulk of inserts and to restore those indexes after that.

What is BCP? When does it used?
BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination.

How do you load large data to the SQL server database?
BulkCopy is a tool used to copy huge amount of data from tables. BULK INSERT command helps to Imports a data file into a database table or view in a user-specified format.

Can we rewrite subqueries into simple select statements or with joins?
Subqueries can often be re-written to use a standard outer join, resulting in faster performance. As we may know, an outer join uses the plus sign (+) operator to tell the database to return all non-matching rows with NULL values. Hence we combine the outer join with a NULL test in the WHERE clause to reproduce the result set without using a sub-query.

Can SQL Servers linked to other servers like Oracle?
SQL Server can be lined to any server provided it has OLE-DB provider from Microsoft to allow a link. E.g. Oracle has a OLE-DB provider for oracle that Microsoft provides to add it as linked server to SQL Server group.

How to know which index a table is using?
SELECT table_name,index_name FROM user_constraints

How to copy the tables, schema and views from one SQL server to another?
Microsoft SQL Server 2000 Data Transformation Services (DTS) is a set of graphical tools and programmable objects that lets user extract, transform, and consolidate data from disparate sources into single or multiple destinations.

What is Self Join?
This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company have a hierarchal reporting structure whereby one member of staff reports to another.

What is Cross Join?
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.

Which virtual table does a trigger use?
Inserted and Deleted.

List few advantages of Stored Procedure.

  • Stored procedure can reduced network traffic and latency, boosting application performance.
  • Stored procedure execution plans can be reused, staying cached in SQL Server’s memory, reducing server overhead.
  • Stored procedures help promote code reuse.
  • Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
  • Stored procedures provide better security to your data.

What is DataWarehousing?

  • Subject-oriented, meaning that the data in the database is organized so that all the data elements relating to the same real-world event or object are linked together;
  • Time-variant, meaning that the changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time;
  • Non-volatile, meaning that data in the database is never over-written or deleted, once committed, the data is static, read-only, but retained for future reporting;
  • Integrated, meaning that the database contains data from most or all of an organization’s operational applications, and that this data is made consistent.

What is OLTP(OnLine Transaction Processing)?
In OLTP – online transaction processing systems relational database design use the discipline of data modeling and generally follow the Codd rules of data normalization in order to ensure absolute data integrity. Using these rules complex information is broken down into its most simple structures (a table) where all of the individual atomic level elements relate to each other and satisfy the normalization rules.

How do SQL server 2000 and XML linked? Can XML be used to access data?
You can execute SQL queries against existing relational databases to return results as XML rather than standard rowsets. These queries can be executed directly or from within stored procedures. To retrieve XML results, use the FOR XML clause of the SELECT statement and specify an XML mode of RAW, AUTO, or EXPLICIT.

OPENXML is a Transact-SQL keyword that provides a relational/rowset view over an in-memory XML document. OPENXML is a rowset provider similar to a table or a view. OPENXML provides a way to access XML data within the Transact-SQL context by transferring data from an XML document into the relational tables. Thus, OPENXML allows you to manage an XML document and its interaction with the relational environment.

What is an execution plan? When would you use it? How would you view the execution plan?
An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called “Show Execution Plan” (located on the Query drop-down menu). If this option is turned on it will display query execution plan in separate window when query is ran again.

Database Basics

A database is a structured collection of data that is stored in a computer system. Data is organized in one or more tables within a database.

Each table consists of lists of information or data in its columns and rows. Columns are named and each must have its datatype defined. Rows are typically numbered according to the primary key column.

Computer languages, such as SQL, allow the user to get information from the database.

Questions you may have include:

  • What is a database table?
  • What are properties of columns and rows?
  • How can the user get information from the database?

This lesson will answer those questions. There is a mini-quiz near the end of the lesson.

Database and tables

A database is a collection of data stored in an organized manner in a computer. Some database applications are Microsoft Access and MySQL databases.

You typically give your database some sort of identifying name, as well as its file name.


A database consists of one or more tables.

A table is structured file that stores a specific type of data.

For example, consider the "auto_supply" database that an auto repair shop may have on their computer or website. This database consists of the following tables:

  • customer_information
  • parts_information
  • auto_brands

Thus, the customer_information is a listing of one type of data, separate from the parts_information list.

A typical table is:

parts_information table

















A database table consists of columns and rows, similar to tables you may see in a document or like a spreadsheet grid. In fact, a spreadsheet is a special form of a database.

Columns and rows

A database table consists of columns and rows.


Each column is given a distinct name. Columns are also called fields.

In the parts_information table, the names of the fields are id, part, part_no and cost.

Data types

A specific data type (or datatype) must be defined for each column. Simply put, the id is an integer, part is a text datatype, part_no is a number and cost is a number.

There is a whole list of datatypes available for a database table. By identifying the datatype, you allow for better control and manipulation of the data.


Every table much have at least one key column, which will identify the items in the row. The key may be a part number or a primary key integer.

The id column is called a primary key. It identifies each row in the table with a distinct integer. A null value is not allowed in a primary key field, and the numbers are listed in order.

The part_no column is a key column, since it can be used to identify the part. For example, there may be several available fenders


A row indicates a record of specific data. For example, in the parts_information table, row id 2 is a record of the cost ($750) of a fender.

Database language

Although a database is used to store data and information in an organized manner, you all need to have a way to access the information you want. The Structured Query Language (SQL) is a common computer language used to obtain specific information from the database.

With SQL, you can search the records (rows) in a database table for a specific identifying number or word. You can list and sort information, as well as add new records, edit existing ones or completely delete a record.

UNIX / Linux Command Summary

Used to check the accessibility of files

Access(pathname, access_mode)
Char* pathname;
int access-mode;
The access modes are.
04 read
02 write
01 execute (search)
00 checks existence of a file

& operator
execute a command as a background process.

prints the specified string in large letters. Each argument may be upto 10 characters long.

is used to break out of a loop. It does not exit from the program.

Produces a calender of the current month as standard output. The month (1-12) and year (1-9999) must be specified in full numeric format.

Cal [[ month] year]

Displays contents of the calendar file

case operator
The case operator is used to validate multiple conditions.

Case $string in

Pattern 1)
Command list;;
Command list;;

Pattern 3)
Command list;;

(for concatenate) command is used to display the contents of a file. Used without arguments it takes input from standard input is used to terminate input.

cat [filename(s)]
cat > [filename]
Data can be appended to a file using >>

Some of the available options are :
Cat [-options] filename(S)
-s silent about files that
cannot be accessed
-v enables display of non printinging characters (except tabs, new lines, form-

-t when used with –v, it causes tabs to be printed as ^I’s
-e when used with –v, it causes $ to be printed at the end of each line
The –t and –e options are ignored if the –v options is not specified.

Used to change directories

Changes the group that owns a file.
Chgrp [grou –id] [filename]

Allows file permissions to be changed for each user. File permissions can be changed only by the owner (s).
Chmod [+/-][rwx] [ugo] [filename]

Used to change the owner of a file.
The command takes a file(s) as source files and the login id of another user as the target.
Chown [user-id] [filename]

The cmp command compares two files (text or binary) byte-by-byte and displays the first occurrence where the files differ.
Cmp [filename1] [filename2] -1 gives a long listing

The comm command compares two sorted files and displays the instances that are common. The display is separated into 3 columns.
Comm. filename1 filename2
first displays what occurs in first files but not in the second
second displays what occurs in second file but not in first
third displays what is common in both files

continue statement
The rest of the commands in the loop are ignored. It moves out of the loop and moves on the next cycle.

The cp (copy) command is used to copy a file.
Cp [filename1] [filename2]

cpio(copy input/output)
Utility program used to take backups.
Cpio operates in three modes:
-o output
-i input
-p pass

the system call creates a new file or prepares to rewrite an existing file. The file pointer is set to the beginning of file.
int creat(path, mode)

char *path;
int mode;

used to cut out parts of a file. It takes filenames as command line arguments or input from standard input. The command can cut columns as well as fields in a file. It however does not delete the selected parts of the file.
Cut [-ef] [column/fie,d] filename
Cut-d “:” –f1,2,3 filename
Where –d indicates a delimiter specified within “:”

used to find the number of free blocks available for all the mounted file systems.
#/etc/df [filesystem]

the diff command compares text files. It gives an index of all the lines that differ in the two files along with the line numbers. It also displays what needs to be changed.
Diff filename1 filename2

The echo command echoes arguments on the command line.
echo [arguments]

Displays the permanent environment variables associated with a user’s login id

exit command
Used to stop the execution of a shell script.

expr command
Expr (command) command is used for numeric computation.
The operators + (add), -(subtract), *(multiplu), /(divide), (remainder) are allowed. Calculation are performed in order of normal numeric precedence.

The find command searches through directories for files that match the specified criteria. It can take full pathnames and relative pathnames on the command line.
To display the output on screen the –print option must be specified

for operator
The for operator may be used in looping constructs where there is repetitive execution of a section of the shell program.
For var in vall val2 val3 val4;

Do commnds; done

Used to check the file system and repair damaged files. The command takes a device name as an argument
# /etc/fsck /dev/file-system-to-be-checked.

grave operator
Used to store the standard the output of a command in an enviroment variable. (‘)

The grep (global regular expression and print) command can be used as a filter to search for strings in files. The pattern may be either a fixed character string or a regular expression.
Grep “string” filename(s)

User’s home directory

if operator
The if operator allows conditional operator

If expression; then commands; fi
if … then…else… fi
$ if; then

efile; then


used to stop background processes

used to link files. A duplicate of a file is created with another name

displays user’s login name

Lists the files in the current directory

Some of the available options are:
-l gives a long listing
-a displays all file{including hidden files

used to print data on the line printer.
Lp [options] filename(s)

The mesg command controls messages received on a terminal.
-n does not allow messages to be displayed on screen
-y allows messages to be displayed on screen

used to create directories

The more command is used to dispay data one screenful at a time.
More [filename]

Mv (move) moves a file from one directory to another or simply changes filenames. The command takes filename and pathnames as source names and a filename or exiting directory as target names.
mv [source-file] [target-file]

The news command allows a user to read news items published by the system administrator.

Displays the contents of a file with line numbers

Changes the password

The paste command joins lines from two files and displays the output. It can take a number of filenames as command line arguments.
paste file1 file2

The directories that the system searches to find commands

Used to display data one page (screenful) at a time. The command can take a number of filenames as arguments.
Pg [option] [filename] [filename2]…..

Operator (1) takes the output of one commands as input of another command.

Gives information about all the active processes.

The system prompt

(print working directory) displays the current directory.

The rm (remove) command is used to delete files from a directory. A number of files may be deleted simultaneously. A file(s) once deleted cannot be retrieved.
rm [filename 1] [filename 2]…

sift command
Using shift $1becomes the source string and other arguments are shifted. $2 is shifted to $1,$3to $2 and so on.

The sleep command is used to suspend the execution of a shell script for the specified time. This is usually in seconds.

Sort is a utility program that can be used to sort text files in numeric or alphabetical order
Sort [filename]

Used to split large file into smaller files
Split-n filename
Split can take a second filename on the command line.

Used to switch to superuser or any other user.

Used to copy data in buffers to files

Used to run a UNIX command from within a C program

The tail command may be used to view the end of a file.
Tail [filename]

Used to save and restore files to tapes or other removable media.
Tar [function[modifier]] [filename(s)]

output that is being redirected to a file can also be viewed on standard output.

test command
It compares strings and numeric values.
The test command has two forms : test command itself If test ${variable} = value then
Do commands else do commands

The test commands also uses special operators [ ]. These are operators following the of are interpreted by the shell as different from wildcard characters.
Of [ -f ${variable} ]

Do commands
[ -d ${variable} ]

do commands

do commands

many different tests are possible for files. Comparing numbers, character strings, values of environment variables.

Used to display the execution time of a program or a command. Time is reported in seconds.
Time filename values

The tr command is used to translate characters.
tr [-option] [string1 [string2]]

Displays the terminal pathname

Used to specify default permissions while creating files.

The uniq command is used to display the uniq(ue) lines in a sorted file.
Sort filename uniq

The operator executes the commands within a loop as long as the test condition is false.

Used to send a message to all users logged in.
# /etc/wall message

the command halts the execution of a script until all child processes, executed as background processes, are completed.

The wc command can be used to count the number of lines, words and characters in a fine.
wc [filename(s)]
The available options are:
wc –[options] [filename]
while operator
the while operator repeatedly performs an operation until the test condition proves false.

$ while
Ø do

Ø done

displays information about all the users currently logged onto the system. The user name, terminal number and the date and time that each user logged onto the system.
The syntax of the who command is who [options]

The write command allows inter-user communication. A user can send messages by addressing the other user’s terminal or login id.
write user-name [terminal number]



apropos whatis

Show commands pertinent to string. See also threadsafe

which command

Show full path name of command

time command

See how long a command takes

time cat

Start stopwatch. Ctrl-d to stop. See also sw

dir navigation

cd -

Go to previous directory


Go to $HOME directory

(cd dir && command)

Go to dir, execute command and return to current dir

pushd .

Put current dir on stack so you can popd back to it

file searching

alias l='ls -l --color=auto'

quick dir listing

ls -lrt

List files by date. See also newest and find_mm_yyyy

ls /usr/bin | pr -T9 -W$COLUMNS

Print in 9 columns to width of terminal

find -name '*.[ch]' | xargs grep -E 'expr'

Search 'expr' in this dir and below. See also findrepo

find -type f -print0 | xargs -r0 grep -F 'example'

Search all regular files for 'example' in this dir and below

find -maxdepth 1 -type f | xargs grep -F 'example'

Search all regular files for 'example' in this dir

find -maxdepth 1 -type d | while read dir; do echo $dir; echo cmd2; done

Process each item with multiple commands (in while loop)

find -type f ! -perm -444

Find files not readable by all (useful for web site)

find -type d ! -perm -111

Find dirs not accessible by all (useful for web site)

locate -r 'file[^/]*\.txt'

Search cached index for names. This re is like glob *file*.txt

look reference

Quickly search (sorted) dictionary for prefix

grep --color reference /usr/share/dict/words

Highlight occurances of regular expression in dictionary

archives and compression

gpg -c file

Encrypt file

gpg file.gpg

Decrypt file

tar -c dir/ | bzip2 > dir.tar.bz2

Make compressed archive of dir/

bzip2 -dc dir.tar.bz2 | tar -x

Extract archive (use gzip instead of bzip2 for tar.gz files)

tar -c dir/ | gzip | gpg -c | ssh user@remote 'dd of=dir.tar.gz.gpg'

Make encrypted archive of dir/ on remote machine

find dir/ -name '*.txt' | tar -c --files-from=- | bzip2 > dir_txt.tar.bz2

Make archive of subset of dir/ and below

find dir/ -name '*.txt' | xargs cp -a --target-directory=dir_txt/ --parents

Make copy of subset of dir/ and below

( tar -c /dir/to/copy ) | ( cd /where/to/ && tar -x -p )

Copy (with permissions) copy/ dir to /where/to/ dir

( cd /dir/to/copy && tar -c . ) | ( cd /where/to/ && tar -x -p )

Copy (with permissions) contents of copy/ dir to /where/to/

( tar -c /dir/to/copy ) | ssh -C user@remote 'cd /where/to/ && tar -x -p'

Copy (with permissions) copy/ dir to remote:/where/to/ dir

dd bs=1M if=/dev/sda | gzip | ssh user@remote 'dd of=sda.gz'

Backup harddisk to remote machine

rsync (Network efficient file copier: Use the --dry-run option for testing)

rsync -P rsync://rsync.server.com/path/to/file file

Only get diffs. Do multiple times for troublesome downloads

rsync --bwlimit=1000 fromfile tofile

Locally copy with rate limit. It's like nice for I/O

rsync -az -e ssh --delete ~/public_html/ remote.com:'~/public_html'

Mirror web site (using compression and encryption)

rsync -auz -e ssh remote:/dir/ . && rsync -auz -e ssh . remote:/dir/

Synchronize current directory with remote one

ssh (Secure SHell)

ssh $USER@$HOST command

Run command on $HOST as $USER (default command=shell)

ssh -f -Y $USER@$HOSTNAME xeyes

Run GUI command on $HOSTNAME as $USER

scp -p -r $USER@$HOST: file dir/

Copy with permissions to $USER's home directory on $HOST

ssh -g -L 8080:localhost:80 root@$HOST

Forward connections to $HOSTNAME:8080 out to $HOST:80

ssh -R 1434:imap:143 root@$HOST

Forward connections from $HOST:1434 in to imap:143

wget (multi purpose download tool)

(cd dir/ && wget -nd -pHEKk http://www.pixelbeat.org/cmdline.html)

Store local browsable version of a page to the current dir

wget -c http://www.example.com/large.file

Continue downloading a partially downloaded file

wget -r -nd -np -l1 -A '*.jpg' http://www.example.com/dir/

Download a set of files to the current directory

wget ftp://remote/file[1-9].iso/

FTP supports globbing directly

wget -q -O- http://www.pixelbeat.org/timeline.html | grep 'a href' | head

Process output directly

echo 'wget url' | at 01:00

Download url at 1AM to current dir

wget --limit-rate=20k url

Do a low priority download (limit to 20KB/s in this case)

wget -nv --spider --force-html -i bookmarks.html

Check links in a file

wget --mirror http://www.example.com/

Efficiently update a local copy of a site (handy from cron)

networking (Note ifconfig, route, mii-tool, nslookup commands are obsolete)

ethtool eth0

Show status of ethernet interface eth0

ethtool --change eth0 autoneg off speed 100 duplex full

Manually set ethernet interface speed

iwconfig eth1

Show status of wireless interface eth1

iwconfig eth1 rate 1Mb/s fixed

Manually set wireless interface speed

iwlist scan

List wireless networks in range

ip link show

List network interfaces

ip link set dev eth0 name wan

Rename interface eth0 to wan

ip link set dev eth0 up

Bring interface eth0 up (or down)

ip addr show

List addresses for interfaces

ip addr add brd + dev eth0

Add (or del) ip and mask (

ip route show

List routing table

ip route add default via

Set default gateway to

host pixelbeat.org

Lookup DNS ip address for name or vice versa

hostname -i

Lookup local ip address (equivalent to host `hostname`)

whois pixelbeat.org

Lookup whois info for hostname or ip address

netstat -tupl

List internet services on a system

netstat -tup

List active connections to/from system

windows networking (Note samba is the package that provides all this windows specific networking support)


Find windows machines. See also findsmb

nmblookup -A

Find the windows (netbios) name associated with ip address

smbclient -L windows_box

List shares on windows machine or samba server

mount -t smbfs -o fmask=666,guest //windows_box/share /mnt/share

Mount a windows share

echo 'message' | smbclient -M windows_box

Send popup to windows machine (off by default in XP sp2)

text manipulation (Note sed uses stdin and stdout. Newer versions support inplace editing with the -i option)

sed 's/string1/string2/g'

Replace string1 with string2

sed 's/\(.*\)1/\12/g'

Modify anystring1 to anystring2

sed '/ *#/d; /^ *$/d'

Remove comments and blank lines

sed ':a; /\\$/N; s/\\\n//; ta'

Concatenate lines with trailing \

sed 's/[ \t]*$//'

Remove trailing spaces from lines

sed 's/\([`"$\]\)/\\\1/g'

Escape shell metacharacters active within double quotes

seq 10 | sed "s/^/ /; s/ *\(.\{7,\}\)/\1/"

Right align numbers

sed -n '1000{p;q}'

Print 1000th line

sed -n '10,20p;20q'

Print lines 10 to 20

sed -n 's/.*\(.*\)<\/title>.*/\1/ip;T;q'

Extract title from HTML web page

sed -i 42d ~/.ssh/known_hosts

Delete a particular line

sort -t. -k1,1n -k2,2n -k3,3n -k4,4n

Sort IPV4 ip addresses

echo 'Test' | tr '[:lower:]' '[:upper:]'

Case conversion

tr -dc '[:print:]' < /dev/urandom

Filter non printable characters

history | wc -l

Count lines

set operations (Note you can export LANG=C for speed. Also these assume no duplicate lines within a file)

sort file1 file2 | uniq

Union of unsorted files

sort file1 file2 | uniq -d

Intersection of unsorted files

sort file1 file1 file2 | uniq -u

Difference of unsorted files

sort file1 file2 | uniq -u

Symmetric Difference of unsorted files

join -t'\0' -a1 -a2 file1 file2

Union of sorted files

join -t'\0' file1 file2

Intersection of sorted files

join -t'\0' -v2 file1 file2

Difference of sorted files

join -t'\0' -v1 -v2 file1 file2

Symmetric Difference of sorted files


echo '(1 + sqrt(5))/2' | bc -l

Quick math (Calculate φ). See also bc

echo 'pad=20; min=64; (100*10^6)/((pad+min)*8)' | bc

More complex (int) e.g. This shows max FastE packet rate

echo 'pad=20; min=64; print (100E6)/((pad+min)*8)' | python

Python handles scientific notation

echo 'pad=20; plot [64:1518] (100*10**6)/((pad+x)*8)' | gnuplot -persist

Plot FastE packet rate vs packet size

echo 'obase=16; ibase=10; 64206' | bc

Base conversion (decimal to hexadecimal)

echo $((0x2dec))

Base conversion (hex to dec) ((shell arithmetic expansion))

units -t '100m/9.58s' 'miles/hour'

Unit conversion (metric to imperial)

units -t '500GB' 'GiB'

Unit conversion (SI to IEC prefixes)

units -t '1 googol'

Definition lookup

seq 100 | (tr '\n' +; echo 0) | bc

Add a column of numbers. See also add and funcpy


cal -3

Display a calendar

cal 9 1752

Display a calendar for a particular month year

date -d fri

What date is it this friday. See also day

[ $(date -d "tomorrow" +%d) = "01" ] || exit

exit a script unless it's the last day of the month

date --date='25 Dec' +%A

What day does xmas fall on, this year

date --date='@2147483647'

Convert seconds since the epoch (1970-01-01 UTC) to date

TZ='America/Los_Angeles' date

What time is it on west coast of US (use tzselect to find TZ)

date --date='TZ="America/Los_Angeles" 09:00 next Fri'

What's the local time for 9AM next Friday on west coast US


printf "%'d\n" 1234

Print number with thousands grouping appropriate to locale

BLOCK_SIZE=\'1 ls -l

Use locale thousands grouping in ls. See also l

echo "I live in `locale territory`"

Extract info from locale database

LANG=en_IE.utf8 locale int_prefix

Lookup locale info for specific country. See also ccodes

locale | cut -d= -f1 | xargs locale -kc | less

List fields available in locale database

recode (Obsoletes iconv, dos2unix, unix2dos)

recode -l | less

Show available conversions (aliases on each line)

recode windows-1252.. file_to_change.txt

Windows "ansi" to local charset (auto does CRLF conversion)

recode utf-8/CRLF.. file_to_change.txt

Windows utf8 to local charset

recode iso-8859-15..utf8 file_to_change.txt

Latin9 (western europe) to utf8

recode ../b64 <> file.b64

Base64 encode

recode /qp.. <> file.txt

Quoted printable decode

recode ..HTML <> file.html

Text to HTML

recode -lf windows-1252 | grep euro

Lookup table of characters

echo -n 0x80 | recode latin-9/x1..dump

Show what a code represents in latin-9 charmap

echo -n 0x20AC | recode ucs-2/x2..latin-9/x

Show latin-9 encoding

echo -n 0x20AC | recode ucs-2/x2..utf-8/x

Show utf-8 encoding


gzip < /dev/cdrom > cdrom.iso.gz

Save copy of data cdrom

mkisofs -V LABEL -r dir | gzip > cdrom.iso.gz

Create cdrom image from contents of dir

mount -o loop cdrom.iso /mnt/dir

Mount the cdrom image at /mnt/dir (read only)

cdrecord -v dev=/dev/cdrom blank=fast

Clear a CDRW

gzip -dc cdrom.iso.gz | cdrecord -v dev=/dev/cdrom -

Burn cdrom image (use dev=ATAPI -scanbus to confirm dev)

cdparanoia -B

Rip audio tracks from CD to wav files in current dir

cdrecord -v dev=/dev/cdrom -audio -pad *.wav

Make audio CD from all wavs in current dir (see also cdrdao)

oggenc --tracknum='track' track.cdda.wav -o 'track.ogg'

Make ogg file from wav file

disk space (See also FSlint)

ls -lSr

Show files by size, biggest last

du -s * | sort -k1,1rn | head

Show top disk users in current dir. See also dutop

df -h

Show free space on mounted filesystems

df -i

Show free inodes on mounted filesystems

fdisk -l

Show disks partitions sizes and types (run as root)

rpm -q -a --qf '%10{SIZE}\t%{NAME}\n' | sort -k1,1n

List all packages by installed size (Bytes) on rpm distros

dpkg-query -W -f='${Installed-Size;10}\t${Package}\n' | sort -k1,1n

List all packages by installed size (KBytes) on deb distros

dd bs=1 seek=2TB if=/dev/null of=ext3.test

Create a large test file (taking no space). See also truncate

> file

truncate data of file or create an empty file


tail -f /var/log/messages

Monitor messages in a log file

strace -c ls >/dev/null

Summarise/profile system calls made by command

strace -f -e open ls >/dev/null

List system calls made by command

ltrace -f -e getenv ls >/dev/null

List library calls made by command

lsof -p $$

List paths that process id has open

lsof ~

List processes that have specified path open

tcpdump not port 22

Show network traffic except ssh. See also tcpdump_not_me

ps -e -o pid,args --forest

List processes in a hierarchy

ps -e -o pcpu,cpu,nice,state,cputime,args --sort pcpu | sed '/^ 0.0 /d'

List processes by % cpu usage

ps -e -orss=,args= | sort -b -k1,1n | pr -TW$COLUMNS

List processes by mem (KB) usage. See also ps_mem.py

ps -C firefox-bin -L -o pid,tid,pcpu,state

List all threads for a particular process

ps -p 1,2

List info for particular process IDs

last reboot

Show system reboot history

free -m

Show amount of (remaining) RAM (-m displays in MB)

watch -n.1 'cat /proc/interrupts'

Watch changeable data continuously

system information (see also sysinfo) ('#' means root access is required)

uname -a

Show kernel version and system architecture

head -n1 /etc/issue

Show name and version of distribution

cat /proc/partitions

Show all partitions registered on the system

grep MemTotal /proc/meminfo

Show RAM total seen by the system

grep "model name" /proc/cpuinfo

Show CPU(s) info

lspci -tv

Show PCI info

lsusb -tv

Show USB info

mount | column -t

List mounted filesystems on the system (and align output)

grep -F capacity: /proc/acpi/battery/BAT0/info

Show state of cells in laptop battery


dmidecode -q | less

Display SMBIOS/DMI information


smartctl -A /dev/sda | grep Power_On_Hours

How long has this disk (system) been powered on in total


hdparm -i /dev/sda

Show info about disk sda


hdparm -tT /dev/sda

Do a read speed test on disk sda


badblocks -s /dev/sda

Test for unreadable blocks on disk sda

interactive (see also linux keyboard shortcuts)


Line editor used by bash, python, bc, gnuplot, ...


Virtual terminals with detach capability, ...


Powerful file manager that can browse rpm, tar, ftp, ssh, ...


Interactive/scriptable graphing


Web browser

xdg-open .

open a file or url with the registered desktop application

DOS Command

UNIX or Bash Command



ls -l (or use ls -lF)(-a all files)
(df -k Space remaining on filesystem)

List directory contents

DIR *.* /o-d
DIR *.* /v /os
DIR /s
DIR /aa

ls -tr
ls -ls
ls -R
ls -a

List directory contents by reverse time of modification/creation.
List files and size
List directory/sub-directory contents recursively.
List hidden files.


ls -R

List directory recursivly



Change directory



Make a new directory



Create a file or directory link



Remove a directory



Display directory location


rm -iv

Remove a file

DELTREE (Win 95...)

rm -R

Remove all directories and files below given directory


cp -piv

Copy a file


cp -R

Copy all file of directory recursivly


mv -iv

Rename/move a file



Dump contents of a file to users screen



Pipe output a single page at a time



Online manuals



Clear screen


exit 0

Exit a shell



Look for a word in files given in command line



Compare two files and show differences. Also see comm, cmp, mgdiff and tkdiff.



Compare two files and show differences. Also see comm, cmp, mgdiff and tkdiff.


set and env

List all environment variables

SET variable=value
echo %variable%


export variable=value
echo $variable

Set environment variables
Show environment variables

ECHO text

echo text

Echo text to screen

SET variable

setenv (for C shell) or export VAR=val (for Korn shell. Also VAR=val)

Set environment variables


echo $PATH

Display search path for executables.
Set PATH environment variable.


export PS1='\h(\u)\W> '

Set user command prompt.



Show date. (also set date - DOS only)



List command history


alias NAME=command

Set command alias



Trap ctrl-break / Trap signals.



Sort data alphabetically/numerically



Line mode editor

EDIT filename.txt

pico, gnp, vi, xedit, xemacs,dtpad

Edit a file. The Linux editor which looks most like DOS edit is probably Pico. (Not a recomendation!)

BACKUP files A:\

tar -cvf /dev/fd0 files
mdir, mcopy
doswrite -a file (AIX only)

Save files to floppy.
See Using DOS floppies

RESTORE A:\ files

tar -xvf /dev/fd0 files
mdir, mcopy
dosread -a file (AIX only)

Read files from floppy.
See Using DOS floppies

ATTRIB [+r|-r] [+a|-a] [+s|-s] [path\file] /s


Change file permissions. DOS: +:set to -:remove r:Read only a:Archive s:System /s:recursively

ATTRIB +h or -h

mv file .file

Change file to a hidden file - rename file with prefix "."



Print a file


source script (cshrc)
. script (bash)
sh script

Execute script from within batch shell.



Show free memory on system


ps -aux

List executable name, process ID number and memory usage of active processes



Show system info (Command borrowed from AIX)



Check and repair hard drive file system


du -s

Disk usage.



Tool to partition a hard drive.

SUBST V: C:\directory\path


Mount a drive letter to a folder/directory on your hard drive.


See: fdformat and mformat for floppies

Format drive file system.
For floppy drive use see YoLinux Tutorial Using DOS Floppies with Linux


uname -a
echo $SHELL
cat /etc/issue

Operating system/shell version


tar and zip

Compress and uncompress files/directories. Use tar to create compilation of a directory before compressing. Linux also has compress, gzip



Print host name of computer



Send packets to a network host



Show routes and router hops to given network destination.

WINIPCFG (Win 95...)


Display/configure network interface

NBTSTAT (Netbios info: -n, -c)
NBTSTAT -A IP-address

nslookup host-name
host host-name

Print DNS info for host.

NBTSTAT -a hostname

nmblookup -A hostname

lookup NetBIOS names.


route -n

Print routing table.


chkconfig --list |grep on

List services.

NET STARTservice-name
NET STOPservice-name

service service-name start
service service-name stop

Start/stop service/daemon.



Show mounted shares/filesystems.


smbclient -M MS-Windows-host-name

Send pop-up to a MS/Windows PC
Send message to another Unix/Linux user. See YoLinux tutorial



Start X-Windows.


shutdown -r now

Reboot system.

To find out how to do something on UNIX simply type "man -k subject_matter". This will do a key word search for all commands dealing with the subject matter. Then use the appropriate command. Online manuals are available on all the commands by typing "man command_name".

MS/Windows info:

  • http://www.mcmcse.com/
  • http://www.techtutorials.net/

Shell Descriptor/Operators:

DOS Descriptor/Operator

UNIX or Bash Descriptor/Operator




Directory path delimiter



Current directory



Parent directory



End of file/close shell



Interrupt/process break



file name wild card



Single character wild card



Variable prefix

%1 %2 %3

$1 $2 $3

First, second and third shell command line arguments.



Command line option flag prefix






stdout redirection



stdout redirection overwrite

stdin redirection

Shell Script Operators:

DOS Operator

UNIX or Bash Operator



set +v

Set verbose mode off. -v: Echo each line of shell script as it is executed.



command line argument prefix. DOS: %1 Bash: $1 for firs argument.



Comment. (Not processed.)



string "equal to" comparison



string "not equal to" comparison



negative of test expression



case/switch statement


IF EXIST C:\filename

IF NOT EXIST C:\filename

if [[ test-resulting-in-bool ]];
elif ...;

if [ -e /dir/filename ];

if [ ! -e /dir/filename ];


If file exists

If file does not exist.


goto ABC


FOR ... IN ... DO

FOR %%fff IN (C:\dir\*.*)
DO echo %%fff

for ffiillee in lliisstt;
do ...;

for (( expr1; expr2; expr3; ))
do ...;

For loop



exit status/return code



sleep for specified interval

Bash shell aliases for DOS users:

The following are bash shell aliases which can be added to the system profile or the user's personal profile ($HOME/.bashrc) to correct and help DOS users on Linux.

alias dir="echo 'Use the command: ls -lF'"
alias tree="echo 'Use the command: ls -R'"
alias del="echo 'Use the command: rm -iv'"
alias move="echo 'Use the command: mv -iv'"
alias rename="echo 'Use the command: mv -iv'"
alias copy="echo 'Use the command: cp -piv'"
alias type="echo 'Use the command: cat'"
alias cls="echo 'Use the command: clear'"
alias mem="echo 'Use the command: free'"
alias ver="echo 'Use the command: uname -a'"
alias A:="echo 'Use the command: mdir a:'"
alias a:="A:"
alias C:="echo 'No C drive in Linux. Go to your home directory with the command: cd'"
alias c:="C:"


DOS Device

Linux Device




Send into nothingness



stdin from console



First printer device



Firsst serial port

Equivalent Linux and MS/Windows GUI Applications:

MS/Windows Command

Linux Command




Command Text Terminal


nautilus --no-desktop

File browser

c:\Program Files\Internet Explorer\iexplore


Web browser

C:\Program Files\Windows NT\Accessories\wordpad


Text editor

C:\Program Files\Microsoft Office\Office10\winword


MS/Office and Open Office suites (ooffice)

C:\Program Files\Adobe\Acrobat 7.0\Reader\acrord32


Adobe PDF viewer



Graphics and painting program

C:\Program Files\WinZip\winzip32


File compress / decompress / pack / unpack



Process and system load monitor

For more information check ::