In this article, let's discuss the 15 most important SQL queries for beginners including these. How do you get the data from the table? How do you create or drop the table? How do you display the different records? Do you want to? and You can easily do all of this by writing a simple SQL query.
1. How to get det data from all columns of a table
This is a very simple query to display all data of a table. Note that there is only one character after SELECT: "*" (which specifies all columns) in this query.
So there is no need to list the column names. Of course, don't forget the FROM and the name of the table you want to get data from. In this example, we are retrieving data from the HUMUN in the table.
SELECT *
FROM human;
2. Get data from certain columns of a table
The above query will display all data for HUMAN. If you only want to retrieve data from specific columns, list them after SELECT. In this example, we are retrieving data from the id and name columns.
SELECT id, name
FROM human;
3. Filtering data using WHERE clause
In addition to getting data from certain columns, we can also filter data using WHERE. See the below example, There is one condition age >= 30. So it means that we are looking for a human list that has an age equal to 30 or more.
SELECT id, name, age
FROM human
WHERE age>=30;
4. Getting data using multiple conditions joined by AND operator
If you want to filter data by multiple conditions like name = 'Rajesh' and age >= 30 then you can use below one
SELECT id, name, age
FROM human
WHERE age >= 30 AND name = 'Rajesh';
5. Getting data using multiple conditions joined by OR operator
If you want to filter data by multiple conditions but at least one condition met
SELECT id, name, age
FROM human
WHERE age >= 30 OR name = 'Rajesh';
It means getting those record where has name = 'Rajesh' or has age >= 30
6. Retrieve non-repeated records using DISTINCT
If you want to display only unique records means non-repeated records that time you can use DISTINCT after SELECT to get only one of each type of record.
Suppose we have HUMAN table like the one below
Name | Age
Rajesh | 30
Rajesh | 30
Rajesh | 25
Rajesh | 30
To see the above records there is a total of 4 records and 3 are the same out of 4. So if we will use DISTINCT then display only two records.
SELECT DISTINCT name, age
FROM human;
Output:
Name | Age
Rajesh | 30
Rajesh | 25
7. Getting records without NULL from the certain column(s)
If you want to get only records that have a NOT NULL value, for that, you can use IS NOT NULL in the condition see the below example.
Suppose records in DB like the ones below
Name | Age
Rajesh | 28
Subhash | NULL
Vishal | 25
Rajesh | NULL
SELECT name, age
FROM human
WHERE age IS NOT NULL;
Output
Name | Age
Rajesh | 28
Vishal | 25
8. Database records sorting according to the column
Whenever the user wants to display records sorted by name, age, salary, or any other column that time we can use ORDER BY query.
Name | Age
Rajesh | 28
Subhash | 35
Vishal | 25
Ajay | 40
SELECT name,age
FROM human
ORDER BY name;
Output
Name | Age
Ajay | 28
Rajesh | 35
Subhash | 25
Vishal | 40
Or if want to display in descending order then just put only DESC after ORDER BY query
SELECT name,age
FROM human
ORDER BY name DESC;
You can sort more than columns in a single query
9. Finding matching records with some pattern (LIKE)
You can use LIKE for finding some records with the pattern, See the below records and query
Name | Age
Ajay | 28
Rajesh | 35
Subhash | 25
Vishal | 40
SELECT name,age
FROM human
WHERE name LIKE '%j%';
Output
Name | Age
Ajay | 28
Rajesh | 35
We have only records that have the character "j".
Let's see another one
SELECT name,age
FROM human
WHERE name LIKE '%sh%';
Output
Name | Age
Rajesh | 35
Subhash | 25
Vishal | 40
So there are 3 records that have "sh" character in the name.
You can use different types of queries like names starting with "Ra%" or end with "%sh" or whatever you want.
10. Join the values from text word columns into one string(CONCAT)
In SQL server there is one function CONCAT to join the string into one.
Name | Age
Rajesh | 28
Subhash | 30
Vishal | 25
Mahesh | 29
Select CONCAT(name, ' - ', age) Detail from human
Output
Detail
Rajesh - 28
Subhash - 30
Vishal - 25
Mahesh - 29
11. Mathematical operators
In SQL queries we can use any mathematical operators like (=,-,*,/,% etc...),
Suppose we have a Products table and we need to display Prices after the discount, See the below query
Select ActualPrice, Discount, (ActualPrice - Discount)UserPrice from PRODUCTS
ActualPrice Discount UserPrice
300 50 250
450 100 350
12. Join data from multiple tables
JOIN is the most important query in SQL,
You can join tables using JOIN queries such as INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, and CROSS JOIN. This example merges data from the Customer and City tables. The INNER JOIN must be specified after FROM and the name of the first table, Customer. After the INNER JOIN, put the name of the second table, city. Records with data from both tables are matched by ON in the join condition. A record in the city table matches a record in the customer table if the id column of the customer table and the customer_id column of the city table have the same value.
SELECT cust.last_name, c.name
FROM customer cust
INNER JOIN city c
ON cust.id = c.customer_id;
13. Insert data into a table
Need to specify both the columns and fields value to be inserted
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
Suppose we have a CUSTOMER table and need to add some data to the table then we have to write query like the below
INSERT INTO CUSTOMER(CustomerName, Address, City, Country)
VALUES ('Rajesh','12,Mondeal Height','Ahmedabad','India');
14. Update table column value
Name | Age
Rajesh | 28
Subhash | 30
Vishal | 25
Mahesh | 29
UPDATE human
SET name = 'Rajesh Gami'
WHERE name = 'Rajesh';
Output
Name | Age
Rajesh Gami | 28
Subhash | 30
Vishal | 25
Mahesh | 29
15. CREATE table
Syntax
CREATE TABLE table_name (
column_1 datatype,
column_2 datatype,
column_3 datatype,
....
);
Let's add a HUMAN table
CREATE TABLE Human(
Id int,
Name varchar(255),
Address varchar(255),
Age int
);
Free Books
Here are two free eBooks downloads (PDF) on SQL queries for beginners:
Here are some resources to learn SQL programming. SQL Programming on C# Corner