HealthCare Domain Terminology

#Issuer : The companies providing the insurance. Eg Humana, Aetna etc.

#Standard for Electronic Rating for Form Filing (SERFF) : This is run by National Association of Insurance Commissioners (NAIC, USA). The Electronic Filing Submission's intent was to provide a cost-effective method for handling insurance policy rate and form filings between regulators and insurance companies.

#Service Area : The geographical area where the issuer is offering the insurance. It can be identified using Zip Code/County

#Maximum Out-of-Pocket (MOOP) : refers to the actual amount of money you will pay for your medical cost before an insurance plan pays 100% of your bill.

#Copay : is a payment (in dollars) defined in the insurance policy and paid by the insured person each time a medical service is accessed

#Co-insurance : is a percentage(%) payment after the deductible up to a certain limit. It must be paid before any policy benefit is payable by an insurance company

#Actuarial Value : The percentage of total average costs for covered benefits that a plan will cover. For example, if a plan has an actuarial value of 70%, on average, you would be responsible for 30% of the costs of all covered benefits. However, you could be responsible for a higher or lower percentage of the total costs of covered services for the year, depending on your actual health care needs and the terms of your insurance policy.

#Catastrophic Plans : A high-deductible health plan (HDHP) is a health insurance plan with lower premiums and higher deductibles than a traditional health plan

#Preferred Provider Organization (PPO) Plans : is a managed care organization of medical doctors, hospitals, and other health care providers who have covenanted with an insurer or a third-party administrator to provide health care at reduced rates to the insurer's or administrator's clients.

#Exclusive Provider Organization (EPO) Plans : is a network of individual medical care providers, or groups of medical care providers, who have entered into written agreements with an insurer to provide health insurance to subscribers. With an EPO, medical care providers enter a mutually beneficial relationship with an insurer.

Why Do You Like Testing?

#1. Do you like software testing because it is challenging?

It surely is. Software testing is about looking at product from different angles, with different perspectives and testing it with different expectations. It is not easy to develop the right mind set and to test the product with different aspects.

#2. Do you like Software testing because it is satisfactory?

It is very true. By testing the software and finding and tracking the bugs and by suggesting improvement ideas, you are contributing towards betterment of product, it surely is the most satisfactory job.

#3. Software testing is complex !

Don’t you believe it? Do you think, understanding product and testing the same while considering different factors like functionality, performance, security, GUI and many others, is an easy task? Along with that, now days, it has become more complex due to mobile applications. To cover the vast range of devices available and to check the application’s behavior in terms of response time and usability is a big challenge.

#4. Software testing is a process !

As software testing starts with understanding requirements and continues with documents preparation like test plan, test strategy, test cases, execution of test cases, preparation of test reports and test summary, a cycle of process is followed and it makes the task (=testing) more fruitful.

#5. Software testing is about improving the quality !

The ultimate purpose of software testing is not to find bugs but to make the product qualitative. As a tester, you are contributing in improvements of the product quality.

#6. Software testing is about finding defects in others’ work !

Critical attitude surely helps when it comes to software testing. By nature, if you like to find faults in others’ work, software testing is the job for you. But remember, the attitude should be limited to work and should not affect your relation with colleagues and personal life.

#7. Software testing is about understanding the customer !

Isn’t it correct? A good software tester is the one, who understands what customer wants, who studies market, who understands latest trends, who provides relevant information to the client, who interprets how important the product is for the customer and ultimately who can put on customer’s shoes and work on product. Software testing is really not about mechanically executing 50 test cases per day but to understand importance of test cases and tweaking them as per requirement and analyzing results to provide best results.

#8. Software testing is about building confidence in the product !

How do you help the developers and organization by doing software testing? By testing the software, you are finding bugs and analyzing product from different perspectives, which helps to make the product better and which helps in growing confidence about the product developed.

#9. Software testing is about learning fast and implementing new ideas !

Yes, software testing is the most interesting job because it throws challenges to you every day. You have to stretch your mind to understand something, to find out how it should work and how it should not, to study the general behavior, to improve the analysis power, to learn new tools and implementing the learning in real life. Rather than that, software testing is about generating ideas. This is the only field in IT, where you have to apply number of ideas to do your work. You have to look at bigger picture and you have to understand how badly end user can handle the product and have to imagine what could be end user’s expectations. Easy it is? Not at all.

#10. Software testing is about deciding the priority !

As a software tester, most of the time you experience to be pushed up to complete the task early. Most of the estimated time for the product is eaten by development and in fixing the defects found in initial rounds of testing. Ultimately you are left with almost no time and you own big responsibility of signing the product as “TESTED”. To handle these kinds of situations you have to understand priority and have to work and convey accordingly.

#11. Software testing is about analyzing data and providing results !

As I mentioned above, software testing is not limited to executing test cases. One has to understand the results, has to generate matrix and has to analyze product’s behavior accordingly.

#12. I have to like it as I do not see any other option !

I really hope no one would go for this option. Software testing is an ocean and no matter at which point you are sailing your boat, you are surely going to face the strong winds and splashing waves. But ultimately, my friend, who wants to sit on the sea shore and keep looking at boats? So, love your job as software testing as you are doing something best rather than just earning.

Jmeter Work Flow

Jmeter Work Flow is depicted in below image :



- Jmeter using sample HTTP requests hits target server.

- In respond to HTTP request made, server sends the response.

- Jmeter save responses using Listeners.

- All statistics collected are analyzed to create Performance Test Report.

What to include in Performance Test Report?

We have to keep in mind  that, performance report or any report can be send to any level of users have some useability or understanding issues . Mainly Performance reports are send to technical, non-technical, managerial, business development people etc.

So, we have to make the comments along with report in such a way so that every kind of person(mentioned before) have at least some understanding over the report.

-This will help the whole software team inside a company to understand the report which will make importance about the performance.
-This will help SQA team to set standards based on target clients.
-This will help management person to set up the timeline and milestones of the projects.
-This will help the support team to define their efforts and answers of feedback to client.
-This will help stake holders to know the actual stability of the product in production.

[As, a performance test normally performed after development , sometimes after beta]

Normally we used to keep a few listeners in the jmeter test plan for running as most of them takes higher resources(memory and process). It is best practice to use jmeter to run without listeners but to save results in CSV file. We will have mostly used two listeners Summary Report, Aggregate Report.  After getting the results from these two listeners, we need to save the results as CSV file and then we will process them in report and then we will make some comments.

Preparing Reports

From Summary Report, Aggregate Report, we will get these attributes

Throughput : (Request/second, sometimes it is shown in request/min or request/hour but when you save the CSV, it is always in request/second)
What is this? It indicates how many request/second is gotten by jmeter. It means, the more throughput your web pages have, it will be more responsive and faster. It includes any intervals between samples.
Some time, we might get a higher throughput because of cache server serving the same data again and again. To overcome this, try to avoid static data while requesting.
This is an ideal candidate for reporting.
Throughput = (Number of requests) / (total time).

Average : (Millisecond) It indicates the average time needed for one request among the samples jmeter determined. Ex- Suppose we are testing 100 user load for a log in request, and among the 100 users, jmeter listener see the results among 86 users and gave a average time. In here average time means total time needed by 86 sample and divided by 86. (per sample time).
This is not ideal candidate for reporting as , most of the time starting and ending thread may need some extra time, so the average time may not represent the actual average time.

Sample: (numbers) : It represents the number of sample requests determined by a jmeter listener. During execution, it is normal to have determination among less number than thread numbers. Ex- I am testing 100 users but, a listener could record or measure results among 86 samples among that 100. So, it will define the number of threads(samples) under measurements.
As, it don’t represents any state of the wep pages, so we can avoid this to include in the report. If it is asked , how many samples were used for measurement, then it will be mention in the report.

Min : (Millisecond)  The shortest time needed for a sample among the same named samples. It can be ignored in the report.

Max : (Millisecond) The longest time needed for a sample among the same named samples. It is one of the ideal candidate for report.

Std.dev: (Millisecond) The standard deviation of sample elapsed time. Jmeter calculates the population of standard deviation( same as STDEVP function in spreadsheet) not from the sample standard deviation. It means, it calculates among results shown in the summary report data, not from the sample time.
Depend on client, it can be mentioned in the report. Usually it is not mentioned.

Error : (%) Percent of requests with errors.
It means, if there are 100 samples, and among then 10 samples took more time than it should(time can be mentioned in the sampler) or not responding or getting false (of http 200) .
This is an ideal candidate for reporting as it represents errors.
Sometimes, we may get 0 because of non responsive sites or exception in apache / java socket exception. We should be aware of the log before mention this in the report.

Bandwidth :(kb/sec) The throughput calculated in Kilobytes per second
Normally, it is mentioned beside throughput in reporting. It is optional; it just shows additional visibility with throughput.

Size : (avg. byte) Average size of the sample response (shows in byte) . It can be mentioned in the report but not mandatory. It is useful when refactoring the solutions, showing which are heavy requests.

Median: (Millisecond): IT represents time in the middle set of results. That means, 50% of sample took less time that this and other 50 took more time than this. The Median is the same as the 50 th Percentile
This may be mentioned in the report. It shows a overall average for requests.

90%Line : (Millisecond) It represents the time needed by 90% of the samples. In other words, 90% of all samples took not more than this time. And the other 10% took at least this time. It is same as 90th Percentile.
It is an ideal candidate for reporting as it represents the max time needed for most of pages(90%).

So, we get the measurements. Now, reporting. In this section, we will see different representation of reports.

1. Compare graph: This is comparison among all get and post requests based on the measurement under single test run. So, it is side by side comparison of requests under single measurement. Ex- In log in test, if we compare log in page load and log in request side by side, it will show which will have more throughput(a measurement) or need less average time(another measurement).
2. Progressive graph: This is comparison among progressive test run on a get / post request based on a measurement. Progressive test run means increasing / decreasing the number of user/time for test. For example, if we do testing with same settings under 50, 100, 150, 200 … number of users. And when we will compare (let’s say Log in Request) response time for 50, 100, 150, 200 users, then it will show progressive graph for log in request under response time. Same for time driven approach, like running same test under constant number of user for 30min, 1hr, 2 hr, 4 hr, 8hr etc.
3. Mixture (Ultimate) Graph: This is among the all get and post requests based on the measurement under progressive test run. It is basically a mixture graph of the previous two.

Tips:

- Change the unit to make graph more understandable to user. Ex- make millisecond to second or req/sec to req/min. This is important to have more visibility over graphs.
-Change unit to have a good size graph. Some time graphs became small for using small unit, if we change the unit, it will be more visible.
- Change the label of the request. This is must when we use recording. For better understanding over page/request , change the label so that every one can understand. Ex- Change the page name to Log in page instead on domai\login.html.
-Define the problems in the graph. (you can see the standards mention below to identify problems with in the graph report)

So, when we have the reports, we may comment based on the reports like following.

A. Using Compare graph :

1.Which request is talking the most time of all. According to this we can apply refactoring, implement caching, identify bottlenecks.
2.Which page size is bigger, so let’s restructure or re engineer the page. (like optimization).
3.We can identify the ajax/js time dependencies.
4.We can also show which pages have high error rates
5.We can define max throughput of a page/requests and define which need to improve.

B. Using Progressive graph :

1.We can show which page/requests are failing/generating error at increment over user/time.
2.We can show which page/requests are taking more time at increment over user/time.
3.We can define maximum user/time supported by the application.
4.We can also find the server’s breaking point.

If we have a chance to compare results among multiple servers. We can comment on

1.Which server requires less time(performs better) on which page/requests
2.Which server need to improve (performs poor) in which page/requests
3.Which server has bottlenecks
4.Which sever is busy most of the time(using server agent)

So, now we know the comments for a test on a web application. But there are other things we should mention in the comment. These are fully depends on clients. I am adding some from my previous projects.

1.    Server configuration & bandwidth where test were performed
2.    Server configuration & bandwidth on which the tested application hosted
3.    Jmeter setting and configuration( jmeter property, test thread configuration, ramp-ups , delay time, plug in configuration, etc)
4.    Test Scenario settings
5.    Notes : on dependencies, blocking issues, known issues. Etc.
6.    Suggestions :  Based on what we get with measurement points.
7.    Good areas : Based on what we get with measurement points.
8.    Bad Areas : Based on what we get with measurement points.

Note : It is best to set standards before starting the test. This is one of the best practices. So, when can make standards. It should be at the beginning or before test plan approved. First, we should find what are the type of requests are there, then set the standard.

Let’s say out testing web application have following type page/request
1.    Page Load Get
2.    Ajax
3.    JS
4.    Page Post with 10 parameters

So,  what will be the standard. Actually , this part is fully depend on

-The robustness of the application
-Client target and standards
-Mostly used standards in similar type application over the world.
-Development time line.

Ideally, I used to provide 2000ms for Page Load Get. 3000 for a Ajax / JS request, 500ms for one parameter for a Page Post request. This is the data I set with my project experiences and it will vary project to project.

Linux Interview Questions

Write command to list all the links from a directory?

ls -lrt | grep "^l"

Create a read-only file in your home directory?

touch file
chmod 400 file

How will you find which operating system your system is running on in UNIX?

By using command "uname -a" in UNIX

How do you copy file from one host to other?

Using "scp" command

How do you find which process is taking how much CPU?

Using "top" command

How do you check how much space left in current drive ?

By using "df" command

Arrays

Coming Soon ...

Strings and Numbers

Coming Soon ...

Flow Control: Looping with for

Coming Soon ...

Positional Parameters

Coming Soon ...

Flow Control: Branching with case

Coming Soon ...

Troubleshooting

Coming Soon ...

Flow Control: Looping with while and until

Coming Soon ...

Reading Keyboard Input

Coming Soon ...

Flow Control: Branching with if

Coming Soon ...

Top-Down Design

Coming Soon ...

Writing Your First Script

Coming Soon ...

Printing

Coming Soon ...

Formatting Output

Coming Soon ...

Text Processing

Coming Soon ...

Java : Regular Expressions

Coming Soon ...

Archiving and Backup

Coming Soon ...

Searching for Files

Coming Soon ...

Networking

Coming Soon ...

Storage Media

Coming Soon ...

Package Management

Coming Soon ...

Customizing the Prompt

Coming Soon ...

A Gentle Introduction to vi

Coming Soon ...

The Environment

Coming Soon ...

Processes

Coming Soon ...

Permissions

Coming Soon ...

Advanced Keyboard Tricks

Coming Soon ...

Redirection

Coming Soon ...

Working with Commands

Coming Soon ...

Manipulating Files and Directories

Coming Soon ...

Exploring the System

Coming Soon ...

What Is the Shell?

Coming Soon ...

Performance Testing Terms

Performance testing is a vast area of automation and during lifecycle of performance testing we will come across to various terms on regular basis. I am trying list out some of them so we can move comfortably with next level of knowledge sharing. These are more how of introduction kind. Hope it will help you a get basic picture of these terms:

1. Response Time: A time duration when a client gets a response from server for any request or we can define it as a amount of time from the moment that a user sends a request until the time that the application indicates that the request has completed.

2. Throughput: Throughput is  the number of transactions per second an application can handle, or the amount of transactions produced over time during a test

3.  Pacing: Time Difference between two iterations. (Can be calculated, i will share the details later)

4.      Iteration: One run of test is known as iteration.

5. Transaction: One or multiple activity can be termed as transaction

for example login into application can be one transaction.

6. Parametrization/ Data pool: A method to replace a static value with other values picked external data source like excel or DB or notepad is known as Parameterization.

(I will share more details in other post since it is important sectionJ)

7. Correlation: A Method to capture dynamic values from server response and replacing with a variable is known as correlation.

(Most imp topic in scripting part, i will detailed it later)

8.     Response: Reaction sent by server on request received from client can known as Response.

9.      Request: Event sent to Server from client side is known as Request.

10.   Cookies: Cookies is a small piece of data sent from a website and stored in a user's web browser while the user is browsing that website.

11.    Header: Headers are name/value pairs that appear in both request and response messages.

12.   Monitoring: A process to keep watch on all activities of server while testing.

13.   Performance Tuning: A process to improve system performance known as Performance Tuning.

14. Thick Client: A Software which installed on local system ( it can work offline too). In short desktop application.

15.   Thin Client: A Software which is browser based does not required full application installation on local system. It requires connectivity.

16.   Smart Client: Smart clients are Internet-connected devices that allow a user's local applications to interact with server-based applications through the use of Web services. It requires connectivity in occasional mode not continuous. e.g. whatsapp

17.  Simultaneous users: Performing same action one by one is simultaneous mode user.

18.   Concurrent users: Performing same action by multiple users is concurrent mode.

( i will share detailed post for above both concepts)

19.   Vuser: A Virtual user which simulate a real user action. Technically, Vuser is software service which simulates a real user process to generate load on server.

20.  Ramp up: A time duration in which all users of load testing will initialized and ready to run or a duration in which users increase in defined manner to reach on peak load point.

21.   Ramp down: A time duration in which all users of load testing will exit and or a duration in which users decrease in defined manner to stop point of test.

22.  Peak  Load: A point where  all users have started performing actions on application.

23.  Think Time: a time duration which users take between performing two actions.

24.  Performance Counters: Parameters of server (DB, application or any) which effect performance known as Performance counters.

Purpose of this post is make you familiar with Performance testing terms which will keep coming time to time .

SQL - NULLs

The SQL NULL is the term used to represent a missing value. A NULL value in a table is a value in a field that appears to be blank.

A field with a NULL value is a field with no value. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces.
Syntax:

The basic syntax of NULL while creating a table:

SQL> CREATE TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),     
   PRIMARY KEY (ID)
);

Here, NOT NULL signifies that column should always accept an explicit value of the given data type. There are two columns where we did not use NOT NULL, which means these columns could be NULL.

A field with a NULL value is one that has been left blank during record creation.
Example:

The NULL value can cause problems when selecting data, however, because when comparing an unknown value to any other value, the result is always unknown and not included in the final results.

You must use the IS NULL or IS NOT NULL operators in order to check for a NULL value.

Consider the following table, CUSTOMERS having the following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |          |
|  7 | Muffy    |  24 | Indore    |          |
+----+----------+-----+-----------+----------+

Now, following is the usage of IS NOT NULL operator:

SQL> SELECT  ID, NAME, AGE, ADDRESS, SALARY
     FROM CUSTOMERS
     WHERE SALARY IS NOT NULL;

This would produce the following result:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
+----+----------+-----+-----------+----------+

Now, following is the usage of IS NULL operator:

SQL> SELECT  ID, NAME, AGE, ADDRESS, SALARY
     FROM CUSTOMERS
     WHERE SALARY IS NULL;

This would produce the following result:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  6 | Komal    |  22 | MP        |          |
|  7 | Muffy    |  24 | Indore    |          |
+----+----------+-----+-----------+----------+

SQL - IN

The SQL IN condition is used to help reduce the need for multiple OR conditions in a SELECT, INSERT, UPDATE, or DELETE statement.
Syntax

The syntax for the SQL IN condition is:

expression IN (value1, value2, .... value_n);

Parameters or Arguments

expression is a value to test.

value1, value2..., or value_n are the values to test against expression.
Note

    The SQL IN condition will return the records where expression is value1, value2..., or value_n.
    The SQL IN condition is also called the SQL IN operator.

Example - With Character

Let's look at an IN condition example using character values.

The following is a SQL SELECT statement that uses the IN condition to compare character values:

SELECT *
FROM suppliers
WHERE supplier_name IN ('IBM', 'Hewlett Packard', 'Microsoft');

This SQL IN condition example would return all rows where the supplier_name is either IBM, Hewlett Packard, or Microsoft. Because the * is used in the select, all fields from the suppliers table would appear in the result set.

This IN condition example is equivalent to the following SQL statement:

SELECT *
FROM suppliers
WHERE supplier_name = 'IBM'
OR supplier_name = 'Hewlett Packard'
OR supplier_name = 'Microsoft';

As you can see, using the SQL IN condition makes the statement easier to read and more efficient.
Example - With Numeric

Next, let's look at an IN condition example using numeric values.

For example:

SELECT *
FROM orders
WHERE order_id IN (10000, 10001, 10003, 10005);

This SQL IN condition example would return all orders where the order_id is either 10000, 10001, 10003, or 10005.

This IN condition example is equivalent to the following SQL statement:

SELECT *
FROM orders
WHERE order_id = 10000
OR order_id = 10001
OR order_id = 10003
OR order_id = 10005;

Example - Using NOT operator

Finally, let's look at an IN condition example using the NOT operator.

For example:

SELECT *
FROM suppliers
WHERE supplier_name NOT IN ( 'IBM', 'Hewlett Packard', 'Microsoft');

This SQL IN condition example would return all rows where the supplier_name is neither IBM, Hewlett Packard, or Microsoft. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.

SQL - Sorting Results

The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some database sorts query results in ascending order by default.

Syntax:
The basic syntax of ORDER BY clause which would be used to sort result in ascending or descending order is as follows:

SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
You can use more than one column in the ORDER BY clause. Make sure whatever column you are using to sort, that column should be in column-list.

Example:
Consider the CUSTOMERS table having the following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+
Following is an example, which would sort the result in ascending order by NAME and SALARY:

SQL> SELECT * FROM CUSTOMERS
     ORDER BY NAME, SALARY;
This would produce the following result:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
+----+----------+-----+-----------+----------+
Following is an example, which would sort the result in descending order by NAME:

SQL> SELECT * FROM CUSTOMERS
     ORDER BY NAME DESC;
This would produce the following result:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
+----+----------+-----+-----------+----------+
To fetch the rows with own preferred order, the SELECT query would as follows:

SQL> SELECT * FROM CUSTOMERS
    ORDER BY (CASE ADDRESS
    WHEN 'DELHI'      THEN 1
    WHEN 'BHOPAL'      THEN 2
    WHEN 'KOTA'      THEN 3
    WHEN 'AHMADABAD' THEN 4
    WHEN 'MP'     THEN 5
    ELSE 100 END) ASC, ADDRESS DESC;
This would produce the following result:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
+----+----------+-----+-----------+----------+

This will sort customers by ADDRESS in your ownoOrder of preference first and in a natural order for the remaining addresses. Also remaining Addresses will be sorted in the reverse alpha order.

SQL - Distinct Keyword

The SQL DISTINCT keyword is used in conjunction with SELECT statement to eliminate all the duplicate records and fetching only unique records.

There may be a situation when you have multiple duplicate records in a table. While fetching such records, it makes more sense to fetch only unique records instead of fetching duplicate records.
Syntax:

The basic syntax of DISTINCT keyword to eliminate duplicate records is as follows:

SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]

Example:

Consider the CUSTOMERS table having the following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

First, let us see how the following SELECT query returns duplicate salary records:

SQL> SELECT SALARY FROM CUSTOMERS
     ORDER BY SALARY;

This would produce the following result where salary 2000 is coming twice which is a duplicate record from the original table.

+----------+
| SALARY   |
+----------+
|  1500.00 |
|  2000.00 |
|  2000.00 |
|  4500.00 |
|  6500.00 |
|  8500.00 |
| 10000.00 |
+----------+

Now, let us use DISTINCT keyword with the above SELECT query and see the result:

SQL> SELECT DISTINCT SALARY FROM CUSTOMERS
     ORDER BY SALARY;

This would produce the following result where we do not have any duplicate entry:

+----------+
| SALARY   |
+----------+
|  1500.00 |
|  2000.00 |
|  4500.00 |
|  6500.00 |
|  8500.00 |
| 10000.00 |
+----------+

SQL - Group By

The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups.

The GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.
Syntax:

The basic syntax of GROUP BY clause is given below. The GROUP BY clause must follow the conditions in the WHERE clause and must precede the ORDER BY clause if one is used.

SELECT column1, column2
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2

Example:

Consider the CUSTOMERS table is having the following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

If you want to know the total amount of salary on each customer, then GROUP BY query would be as follows:

SQL> SELECT NAME, SUM(SALARY) FROM CUSTOMERS
     GROUP BY NAME;

This would produce the following result:

+----------+-------------+
| NAME     | SUM(SALARY) |
+----------+-------------+
| Chaitali |     6500.00 |
| Hardik   |     8500.00 |
| kaushik  |     2000.00 |
| Khilan   |     1500.00 |
| Komal    |     4500.00 |
| Muffy    |    10000.00 |
| Ramesh   |     2000.00 |
+----------+-------------+

Now, let us have following table where CUSTOMERS table has the following records with duplicate names:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Ramesh   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | kaushik  |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Now again, if you want to know the total amount of salary on each customer, then GROUP BY query would be as follows:

SQL> SELECT NAME, SUM(SALARY) FROM CUSTOMERS
     GROUP BY NAME;

This would produce the following result:

+---------+-------------+
| NAME    | SUM(SALARY) |
+---------+-------------+
| Hardik  |     8500.00 |
| kaushik |     8500.00 |
| Komal   |     4500.00 |
| Muffy   |    10000.00 |
| Ramesh  |     3500.00 |
+---------+-------------+

SQL - Order By

The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some database sorts query results in ascending order by default.
Syntax:

The basic syntax of ORDER BY clause which would be used to sort result in ascending or descending order is as follows:

SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

You can use more than one column in the ORDER BY clause. Make sure whatever column you are using to sort, that column should be in column-list.
Example:

Consider the CUSTOMERS table having the following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Following is an example, which would sort the result in ascending order by NAME and SALARY:

SQL> SELECT * FROM CUSTOMERS
     ORDER BY NAME, SALARY;

This would produce the following result:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
+----+----------+-----+-----------+----------+

Following is an example, which would sort the result in descending order by NAME:

SQL> SELECT * FROM CUSTOMERS
     ORDER BY NAME DESC;

This would produce the following result:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
+----+----------+-----+-----------+----------+

To fetch the rows with own preferred order, the SELECT query would as follows:

SQL> SELECT * FROM CUSTOMERS
    ORDER BY (CASE ADDRESS
    WHEN 'DELHI'      THEN 1
    WHEN 'BHOPAL'      THEN 2
    WHEN 'KOTA'      THEN 3
    WHEN 'AHMADABAD' THEN 4
    WHEN 'MP'     THEN 5
    ELSE 100 END) ASC, ADDRESS DESC;

This would produce the following result:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
+----+----------+-----+-----------+----------+

This will sort customers by ADDRESS in your ownoOrder of preference first and in a natural order for the remaining addresses. Also remaining Addresses will be sorted in the reverse alpha order.

SQL - Top Clause

The SQL TOP clause is used to fetch a TOP N number or X percent records from a table.

Note: All the databases do not support TOP clause. For example MySQL supports LIMIT clause to fetch limited number of records and Oracle uses ROWNUM to fetch limited number of records.
Syntax:

The basic syntax of TOP clause with SELECT statement would be as follows:

SELECT TOP number|percent column_name(s)
FROM table_name
WHERE [condition]

Example:

Consider the CUSTOMERS table having the following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Following is an example on SQL server, which would fetch top 3 records from CUSTOMERS table:

SQL> SELECT TOP 3 * FROM CUSTOMERS;

This would produce the following result:

+----+---------+-----+-----------+---------+
| ID | NAME    | AGE | ADDRESS   | SALARY  |
+----+---------+-----+-----------+---------+
|  1 | Ramesh  |  32 | Ahmedabad | 2000.00 |
|  2 | Khilan  |  25 | Delhi     | 1500.00 |
|  3 | kaushik |  23 | Kota      | 2000.00 |
+----+---------+-----+-----------+---------+

If you are using MySQL server, then here is an equivalent example:

SQL> SELECT * FROM CUSTOMERS
LIMIT 3;

This would produce the following result:

+----+---------+-----+-----------+---------+
| ID | NAME    | AGE | ADDRESS   | SALARY  |
+----+---------+-----+-----------+---------+
|  1 | Ramesh  |  32 | Ahmedabad | 2000.00 |
|  2 | Khilan  |  25 | Delhi     | 1500.00 |
|  3 | kaushik |  23 | Kota      | 2000.00 |
+----+---------+-----+-----------+---------+

If you are using Oracle server, then here is an equivalent example:

SQL> SELECT * FROM CUSTOMERS
WHERE ROWNUM <= 3;

This would produce the following result:

+----+---------+-----+-----------+---------+
| ID | NAME    | AGE | ADDRESS   | SALARY  |
+----+---------+-----+-----------+---------+
|  1 | Ramesh  |  32 | Ahmedabad | 2000.00 |
|  2 | Khilan  |  25 | Delhi     | 1500.00 |
|  3 | kaushik |  23 | Kota      | 2000.00 |
+----+---------+-----+-----------+---------+

SQL - Like Clause

The SQL LIKE clause is used to compare a value to similar values using wildcard operators. There are two wildcards used in conjunction with the LIKE operator:

    The percent sign (%)

    The underscore (_)

The percent sign represents zero, one, or multiple characters. The underscore represents a single number or character. The symbols can be used in combinations.
Syntax:

The basic syntax of % and _ is as follows:

SELECT FROM table_name
WHERE column LIKE 'XXXX%'

or

SELECT FROM table_name
WHERE column LIKE '%XXXX%'

or

SELECT FROM table_name
WHERE column LIKE 'XXXX_'

or

SELECT FROM table_name
WHERE column LIKE '_XXXX'

or

SELECT FROM table_name
WHERE column LIKE '_XXXX_'

You can combine N number of conditions using AND or OR operators. Here, XXXX could be any numeric or string value.
Example:

Here are number of examples showing WHERE part having different LIKE clause with '%' and '_' operators:
Statement    Description
WHERE SALARY LIKE '200%'    Finds any values that start with 200
WHERE SALARY LIKE '%200%'    Finds any values that have 200 in any position
WHERE SALARY LIKE '_00%'    Finds any values that have 00 in the second and third positions
WHERE SALARY LIKE '2_%_%'    Finds any values that start with 2 and are at least 3 characters in length
WHERE SALARY LIKE '%2'    Finds any values that end with 2
WHERE SALARY LIKE '_2%3'    Finds any values that have a 2 in the second position and end with a 3
WHERE SALARY LIKE '2___3'    Finds any values in a five-digit number that start with 2 and end with 3

Let us take a real example, consider the CUSTOMERS table having the following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Following is an example, which would display all the records from CUSTOMERS table where SALARY starts with 200:

SQL> SELECT * FROM CUSTOMERS
WHERE SALARY LIKE '200%';

This would produce the following result:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
+----+----------+-----+-----------+----------+

SQL - Delete Query

The SQL DELETE Query is used to delete the existing records from a table.

You can use WHERE clause with DELETE query to delete selected rows, otherwise all the records would be deleted.
Syntax:

The basic syntax of DELETE query with WHERE clause is as follows:

DELETE FROM table_name
WHERE [condition];

You can combine N number of conditions using AND or OR operators.
Example:

Consider the CUSTOMERS table having the following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Following is an example, which would DELETE a customer, whose ID is 6:

SQL> DELETE FROM CUSTOMERS
WHERE ID = 6;

Now, CUSTOMERS table would have the following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

If you want to DELETE all the records from CUSTOMERS table, you do not need to use WHERE clause and DELETE query would be as follows:

SQL> DELETE FROM CUSTOMERS;

Now, CUSTOMERS table would not have any record.

SQL - AND & OR Clause

The SQL AND and OR operators are used to combine multiple conditions to narrow data in an SQL statement. These two operators are called conjunctive operators.

These operators provide a means to make multiple comparisons with different operators in the same SQL statement.
The AND Operator:

The AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause.
Syntax:

The basic syntax of AND operator with WHERE clause is as follows:

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];

You can combine N number of conditions using AND operator. For an action to be taken by the SQL statement, whether it be a transaction or query, all conditions separated by the AND must be TRUE.
Example:

Consider the CUSTOMERS table having the following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Following is an example, which would fetch ID, Name and Salary fields from the CUSTOMERS table where salary is greater than 2000 AND age is less tan 25 years:

SQL> SELECT ID, NAME, SALARY
FROM CUSTOMERS
WHERE SALARY > 2000 AND age < 25;

This would produce the following result:

+----+-------+----------+
| ID | NAME  | SALARY   |
+----+-------+----------+
|  6 | Komal |  4500.00 |
|  7 | Muffy | 10000.00 |
+----+-------+----------+

The OR Operator:

The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause.
Syntax:

The basic syntax of OR operator with WHERE clause is as follows:

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]

You can combine N number of conditions using OR operator. For an action to be taken by the SQL statement, whether it be a transaction or query, only any ONE of the conditions separated by the OR must be TRUE.
Example:

Consider the CUSTOMERS table having the following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Following is an example, which would fetch ID, Name and Salary fields from the CUSTOMERS table where salary is greater than 2000 OR age is less tan 25 years:

SQL> SELECT ID, NAME, SALARY
FROM CUSTOMERS
WHERE SALARY > 2000 OR age < 25;

This would produce the following result:

+----+----------+----------+
| ID | NAME     | SALARY   |
+----+----------+----------+
|  3 | kaushik  |  2000.00 |
|  4 | Chaitali |  6500.00 |
|  5 | Hardik   |  8500.00 |
|  6 | Komal    |  4500.00 |
|  7 | Muffy    | 10000.00 |
+----+----------+----------+

SQL - Where Clause

The SQL WHERE clause is used to specify a condition while fetching the data from single table or joining with multiple tables.

If the given condition is satisfied then only it returns specific value from the table. You would use WHERE clause to filter the records and fetching only necessary records.

The WHERE clause is not only used in SELECT statement, but it is also used in UPDATE, DELETE statement, etc., which we would examine in subsequent chapters.
Syntax:

The basic syntax of SELECT statement with WHERE clause is as follows:

SELECT column1, column2, columnN
FROM table_name
WHERE [condition]

You can specify a condition using comparison or logical operators like >, <, =, LIKE, NOT, etc. Below examples would make this concept clear.
Example:

Consider the CUSTOMERS table having the following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Following is an example which would fetch ID, Name and Salary fields from the CUSTOMERS table where salary is greater than 2000:

SQL> SELECT ID, NAME, SALARY
FROM CUSTOMERS
WHERE SALARY > 2000;

This would produce the following result:

+----+----------+----------+
| ID | NAME     | SALARY   |
+----+----------+----------+
|  4 | Chaitali |  6500.00 |
|  5 | Hardik   |  8500.00 |
|  6 | Komal    |  4500.00 |
|  7 | Muffy    | 10000.00 |
+----+----------+----------+

Following is an example, which would fetch ID, Name and Salary fields from the CUSTOMERS table for a customer with name Hardik. Here, it is important to note that all the strings should be given inside single quotes ('') where as numeric values should be given without any quote as in above example:

SQL> SELECT ID, NAME, SALARY
FROM CUSTOMERS
WHERE NAME = 'Hardik';

This would produce the following result:

+----+----------+----------+
| ID | NAME     | SALARY   |
+----+----------+----------+
|  5 | Hardik   |  8500.00 |
+----+----------+----------+

SQL - Select Query

SQL SELECT statement is used to fetch the data from a database table which returns data in the form of result table. These result tables are called result-sets.
Syntax:

The basic syntax of SELECT statement is as follows:

SELECT column1, column2, columnN FROM table_name;

Here, column1, column2...are the fields of a table whose values you want to fetch. If you want to fetch all the fields available in the field, then you can use the following syntax:

SELECT * FROM table_name;

Example:

Consider the CUSTOMERS table having the following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Following is an example, which would fetch ID, Name and Salary fields of the customers available in CUSTOMERS table:

SQL> SELECT ID, NAME, SALARY FROM CUSTOMERS;

This would produce the following result:

+----+----------+----------+
| ID | NAME     | SALARY   |
+----+----------+----------+
|  1 | Ramesh   |  2000.00 |
|  2 | Khilan   |  1500.00 |
|  3 | kaushik  |  2000.00 |
|  4 | Chaitali |  6500.00 |
|  5 | Hardik   |  8500.00 |
|  6 | Komal    |  4500.00 |
|  7 | Muffy    | 10000.00 |
+----+----------+----------+

If you want to fetch all the fields of CUSTOMERS table, then use the following query:

SQL> SELECT * FROM CUSTOMERS;

This would produce the following result:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

SQL - Insert Query

The SQL INSERT INTO Statement is used to add new rows of data to a table in the database.
Syntax:

There are two basic syntaxes of INSERT INTO statement as follows:

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)] 
VALUES (value1, value2, value3,...valueN);

Here, column1, column2,...columnN are the names of the columns in the table into which you want to insert data.

You may not need to specify the column(s) name in the SQL query if you are adding values for all the columns of the table. But make sure the order of the values is in the same order as the columns in the table. The SQL INSERT INTO syntax would be as follows:

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

Example:

Following statements would create six records in CUSTOMERS table:

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );


INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

You can create a record in CUSTOMERS table using second syntax as follows:

INSERT INTO CUSTOMERS
VALUES (7, 'Muffy', 24, 'Indore', 10000.00 );

All the above statements would produce the following records in CUSTOMERS table:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Populate one table using another table:

You can populate data into a table through select statement over another table provided another table has a set of fields, which are required to populate first table. Here is the syntax:

INSERT INTO first_table_name [(column1, column2, ... columnN)]
   SELECT column1, column2, ...columnN
   FROM second_table_name
   [WHERE condition];

SQL - Drop Table

The SQL DROP TABLE statement is used to remove a table definition and all data, indexes, triggers, constraints, and permission specifications for that table.

NOTE: You have to be careful while using this command because once a table is deleted then all the information available in the table would also be lost forever.
Syntax:

Basic syntax of DROP TABLE statement is as follows:

DROP TABLE table_name;

Example:

Let us first verify CUSTOMERS table and then we would delete it from the database:

SQL> DESC CUSTOMERS;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| ID      | int(11)       | NO   | PRI |         |       |
| NAME    | varchar(20)   | NO   |     |         |       |
| AGE     | int(11)       | NO   |     |         |       |
| ADDRESS | char(25)      | YES  |     | NULL    |       |
| SALARY  | decimal(18,2) | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

This means CUSTOMERS table is available in the database, so let us drop it as follows:

SQL> DROP TABLE CUSTOMERS;
Query OK, 0 rows affected (0.01 sec)

Now, if you would try DESC command, then you would get error as follows:

SQL> DESC CUSTOMERS;
ERROR 1146 (42S02): Table 'TEST.CUSTOMERS' doesn't exist

Here, TEST is database name which we are using for our examples.

SQL - Create Table

Creating a basic table involves naming the table and defining its columns and each column's data type.

The SQL CREATE TABLE statement is used to create a new table.
Syntax:

Basic syntax of CREATE TABLE statement is as follows:

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

CREATE TABLE is the keyword telling the database system what you want to do. In this case, you want to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement.

Then in brackets comes the list defining each column in the table and what sort of data type it is. The syntax becomes clearer with an example below.

A copy of an existing table can be created using a combination of the CREATE TABLE statement and the SELECT statement. You can check complete details at Create Table Using another Table.
Example:

Following is an example, which creates a CUSTOMERS table with ID as primary key and NOT NULL are the constraints showing that these fields can not be NULL while creating records in this table:

SQL> CREATE TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),     
   PRIMARY KEY (ID)
);

You can verify if your table has been created successfully by looking at the message displayed by the SQL server, otherwise you can use DESC command as follows:

SQL> DESC CUSTOMERS;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| ID      | int(11)       | NO   | PRI |         |       |
| NAME    | varchar(20)   | NO   |     |         |       |
| AGE     | int(11)       | NO   |     |         |       |
| ADDRESS | char(25)      | YES  |     | NULL    |       |
| SALARY  | decimal(18,2) | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

Now, you have CUSTOMERS table available in your database which you can use to store required information related to customers.

SQL - Select Database

When you have multiple databases in your SQL Schema, then before starting your operation, you would need to select a database where all the operations would be performed.

The SQL USE statement is used to select any existing database in SQL schema.

Syntax:

Basic syntax of USE statement is as follows:

USE DatabaseName;

Always database name should be unique within the RDBMS.
Example:

You can check available databases as follows:

SQL> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Chuck |
| Mary |
| mysql              |
| orig               |
| test               |
+--------------------+
6 rows in set (0.00 sec)

Now, if you want to work with Chuck database, then you can execute the following SQL command and start working with Chuck database:

SQL - Drop Database

The SQL DROP DATABASE statement is used to drop an existing database in SQL schema.

Syntax:

Basic syntax of DROP DATABASE statement is as follows:

DROP DATABASE DatabaseName;

Always database name should be unique within the RDBMS.
Example:

If you want to delete an existing database <testDB>, then DROP DATABASE statement would be as follows:

SQL> DROP DATABASE testDB;

NOTE: Be careful before using this operation because by deleting an existing database would result in loss of complete information stored in the database.

Make sure you have admin privilege before dropping any database. Once a database is dropped, you can check it in the list of databases as follows:

SQL> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Chuck             |
| Mary              |
| mysql             |
| orig                |
| test                |
+--------------------+
6 rows in set (0.00 sec)

SQL - Create Database

The SQL CREATE DATABASE statement is used to create new SQL database.

Syntax:

Basic syntax of CREATE DATABASE statement is as follows:

CREATE DATABASE DatabaseName;

Always database name should be unique within the RDBMS.
Example:

If you want to create new database <testDB>, then CREATE DATABASE statement would be as follows:

SQL> CREATE DATABASE testDB;

Make sure you have admin privilege before creating any database. Once a database is created, you can check it in the list of databases as follows:

SQL> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Chuck             |
| Mary     |
| mysql              |
| orig               |
| test               |
| testDB             |
+--------------------+
7 rows in set (0.00 sec)

SQL - Expressions

An expression is a combination of one or more values, operators, and SQL functions that evaluate to a value.

SQL EXPRESSIONs are like formulas and they are written in query language. You can also use them to query the database for specific set of data.

Syntax:

Consider the basic syntax of the SELECT statement as follows:

SELECT column1, column2, columnN
FROM table_name
WHERE [CONDITION|EXPRESSION];

There are different types of SQL expressions, which are mentioned below:

SQL - Boolean Expressions:

SQL Boolean Expressions fetch the data on the basis of matching single value. Following is the syntax:

SELECT column1, column2, columnN
FROM table_name
WHERE SINGLE VALUE MATCHTING EXPRESSION;

Consider the CUSTOMERS table having the following records:

SQL> SELECT * FROM CUSTOMERS;
+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+
7 rows in set (0.00 sec)

Here is simple example showing usage of SQL Boolean Expressions:

SQL> SELECT * FROM CUSTOMERS WHERE SALARY = 10000;
+----+-------+-----+---------+----------+
| ID | NAME  | AGE | ADDRESS | SALARY   |
+----+-------+-----+---------+----------+
|  7 | Muffy |  24 | Indore  | 10000.00 |
+----+-------+-----+---------+----------+
1 row in set (0.00 sec)

SQL - Numeric Expression:

This expression is used to perform any mathematical operation in any query. Following is the syntax:

SELECT numerical_expression as  OPERATION_NAME
[FROM table_name
WHERE CONDITION] ;

Here numerical_expression is used for mathematical expression or any formula. Following is a simple examples showing usage of SQL Numeric Expressions:

SQL> SELECT (15 + 6) AS ADDITION
+----------+
| ADDITION |
+----------+
|       21 |
+----------+
1 row in set (0.00 sec)

There are several built-in functions like avg(), sum(), count(), etc., to perform what is known as aggregate data calculations against a table or a specific table column.

SQL> SELECT COUNT(*) AS "RECORDS" FROM CUSTOMERS;
+---------+
| RECORDS |
+---------+
|       7 |
+---------+
1 row in set (0.00 sec)

SQL - Date Expressions:

Date Expressions return current system date and time values:

SQL>  SELECT CURRENT_TIMESTAMP;
+---------------------+
| Current_Timestamp   |
+---------------------+
| 2009-11-12 06:40:23 |
+---------------------+
1 row in set (0.00 sec)

Another date expression is as follows:

SQL>  SELECT  GETDATE();;
+-------------------------+
| GETDATE                 |
+-------------------------+
| 2009-10-22 12:07:18.140 |
+-------------------------+
1 row in set (0.00 sec)

SQL - Operators

An operator is a reserved word or a character used primarily in an SQL statement's WHERE clause to perform operation(s), such as comparisons and arithmetic operations.

Operators are used to specify conditions in an SQL statement and to serve as conjunctions for multiple conditions in a statement.
  • Arithmetic operators
  • Comparison operators
  • Logical operators
  • Operators used to negate conditions

SQL Arithmetic Operators:

Assume variable a holds 10 and variable b holds 20, then:

OperatorDescriptionExample
+Addition - Adds values on either side of the operatora + b will give 30
-Subtraction - Subtracts right hand operand from left hand operanda - b will give -10
*Multiplication - Multiplies values on either side of the operatora * b will give 200
/Division - Divides left hand operand by right hand operandb / a will give 2
%Modulus - Divides left hand operand by right hand operand and returns remainderb % a will give 0

SQL Comparison Operators:

Assume variable a holds 10 and variable b holds 20, then:

OperatorDescriptionExample
=Checks if the values of two operands are equal or not, if yes then condition becomes true.(a = b) is not true.
!=Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.(a != b) is true.
<>Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.(a <> b) is true.
>Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true.(a > b) is not true.
<Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true.(a < b) is true.
>=Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true.(a >= b) is not true.
<=Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true.(a <= b) is true.
!<Checks if the value of left operand is not less than the value of right operand, if yes then condition becomes true.(a !< b) is false.
!>Checks if the value of left operand is not greater than the value of right operand, if yes then condition becomes true.(a !> b) is true.

SQL Logical Operators:

Here is a list of all the logical operators available in SQL.

OperatorDescription
ALLThe ALL operator is used to compare a value to all values in another value set.
ANDThe AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause.
ANYThe ANY operator is used to compare a value to any applicable value in the list according to the condition.
BETWEENThe BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value.
EXISTSThe EXISTS operator is used to search for the presence of a row in a specified table that meets certain criteria.
INThe IN operator is used to compare a value to a list of literal values that have been specified.
LIKEThe LIKE operator is used to compare a value to similar values using wildcard operators.
NOTThe NOT operator reverses the meaning of the logical operator with which it is used. Eg: NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator.
ORThe OR operator is used to combine multiple conditions in an SQL statement's WHERE clause.
IS NULLThe NULL operator is used to compare a value with a NULL value.
UNIQUEThe UNIQUE operator searches every row of a specified table for uniqueness (no duplicates).

SQL - Data Types

SQL data type is an attribute that specifies type of data of any object. Each column, variable and expression has related data type in SQL.

You would use these data types while creating your tables. You would choose a particular data type for a table column based on your requirement.

SQL Server offers six categories of data types for your use:

Exact Numeric Data Types:

DATA TYPEFROMTO
bigint-9,223,372,036,854,775,8089,223,372,036,854,775,807
int-2,147,483,6482,147,483,647
smallint-32,76832,767
tinyint0255
bit01
decimal-10^38 +110^38 -1
numeric-10^38 +110^38 -1
money-922,337,203,685,477.5808+922,337,203,685,477.5807
smallmoney-214,748.3648+214,748.3647

Approximate Numeric Data Types:

DATA TYPEFROMTO
float-1.79E + 3081.79E + 308
real-3.40E + 383.40E + 38

Date and Time Data Types:

DATA TYPEFROMTO
datetimeJan 1, 1753Dec 31, 9999
smalldatetimeJan 1, 1900Jun 6, 2079
dateStores a date like June 30, 1991
timeStores a time of day like 12:30 P.M.


Note: Here, datetime has 3.33 milliseconds accuracy where as smalldatetime has 1 minute accuracy.

Character Strings Data Types:

DATA TYPEFROMTO
charcharMaximum length of 8,000 characters.( Fixed length non-Unicode characters)
varcharvarcharMaximum of 8,000 characters.(Variable-length non-Unicode data).
varchar(max)varchar(max)Maximum length of 231characters, Variable-length non-Unicode data (SQL Server 2005 only).
texttextVariable-length non-Unicode data with a maximum length of 2,147,483,647 characters.

Unicode Character Strings Data Types:

DATA TYPEDescription
ncharMaximum length of 4,000 characters.( Fixed length Unicode)
nvarcharMaximum length of 4,000 characters.(Variable length Unicode)
nvarchar(max)Maximum length of 231characters (SQL Server 2005 only).( Variable length Unicode)
ntextMaximum length of 1,073,741,823 characters. ( Variable length Unicode )

Binary Data Types:

DATA TYPEDescription
binaryMaximum length of 8,000 bytes(Fixed-length binary data )
varbinaryMaximum length of 8,000 bytes.(Variable length binary data)
varbinary(max)Maximum length of 231 bytes (SQL Server 2005 only). ( Variable length Binary data)
imageMaximum length of 2,147,483,647 bytes. ( Variable length Binary Data)

Misc Data Types:

DATA TYPEDescription
sql_variantStores values of various SQL Server-supported data types, except text, ntext, and timestamp.
timestampStores a database-wide unique number that gets updated every time a row gets updated
uniqueidentifierStores a globally unique identifier (GUID)
xmlStores XML data. You can store xml instances in a column or a variable (SQL Server 2005 only).
cursorReference to a cursor object
tableStores a result set for later processing