Basic SQL Queries

July 25, 2020December 8, 2023 | by Achyut Kendre | No comments

How to create table using SQL?

To create any thing in database SQL provides you create query or command. To create table in database we need to use Create Table query as follows –

create table tablename ( fieldname datatype([size]), fieldname datatype([size]) ....)

where field name is name of column from table and data type allow you to specify what type of data you want to store in that field or column. SQL server support lot of data types few following common data types –

Numeric Types : allow you to store the numbers in table column or field. few are number types are – tiny int, small int, int, big int, decimal, numeric etc.

Char / String: in this data types we have char, varchar, nchar, nvarchar, varchar(max), nvarchar(max) where char, nchar are fixed length characters difference is char is for ascii where as nchar is for unicode varchar and nvarchar for variable length characters. where as varchar(max) and nvarchar(max) is used if you do not know how many characters you want to store.

Date & Time : to store date and time we need to use data types date, datetime, time.

Similar to this we have data types like money, binary , xml , image etc.

Now if you want to create a table for customer to store customer id, customer name, email id , mobile no, credit limit as follows –

Create Table CustomerTbl(CustomerID bigint,CustomerName varchar(100), Address varchar(max),EmailID varchar(40),MobileNo varchar(40), CreditLimit decimal(10,2))

It will create table as follows –

How to Insert data in Table using SQL?

To add new data to table sql provides you insert command, you can insert it as follows –

Insert into TableName [(list of fields)] values(List of values seperated by comma)

Here list of fields will be optional if you want to insert all the values in correct sequence as the fields in table.

If you want to insert a record in customer table with all field values you can do it as follows –

insert into CustomerTbl values(1,'Ganesh','Pune','ganesh@hotmail.com','89898989',12000)

If you want to insert only name, email id and mobile no into customer table in this case you need to specify the list of fields or columns as follows –

insert into CustomerTbl (CustomerName,EmailID) values('Suresh','Suresh@gmail.com')

How to fetch data from table?

To fetch data from sql server table we can use the select query , we can use the select query as follows –

select */list of fields from tablename where condition order by fieldname group by fieldname having condition

Simplest select query is –

select * from table name

It will select all the rows and columns from table.

Following query will select all the rows / columns from customer table.

select * from customertbl

If you want to select specific columns or fields from table, then you can use following query –

select fieldname, fieldname, fieldname ... from tablename

To select the cusotmername, mobile no and credit limit we need to use the following query.

select customername,mobileno,creditlimit from customertbl

How to filter rows in select query or command?

To select specific rows from database table using select query we need to use the where clause as follows –

select * /field name, field name .. from customertbl where condition

You can create the conditions using relational operators like >, < , >=, <= , !=, =. You can also use the logical operators and, or and not to combine the conditions.

You can also use the operators like for string pattern, between and , in and not in .

e.g. find all the customers from customer table having credit limit > 11000

Select * from CustomerTbl where creditlimit > 11000

e.g. find customers having credit limit >11000 and address pune.

select * from CustomerTbl where Address='Pune' or Address='Mumbai'

Like operator is used to compare string pattern where you can use operator % for any number of characters and _ for single character.

Find all the customers whose name start with R.

select * from CustomerTbl where customername like 'R%'

Find all customers whose name ends with sh.

select * from CustomerTbl where customername like '%sh'

How to use between and Operator?

Find all the customers whose credit limit is between 11000 and 14000 using between and operator.

select * from CustomerTbl where creditlimit>=11000 and creditlimit<=14000

How to use In and Not In Operators?

In operator and not in operator is used to compare single field value with set of values. Find all the customers from pune, mumbai and delhi as follows –

select * from customertbl where address in ('Pune','Mumbai','Delhi')

Find all customers those are not from city pune, mumbai as follows –

select * from customertbl where address not in ('Pune','Mumbai')

How to Order Data In ASCENDING / DESCENDING order using SQL?

SQL provides you order by clause with asc and desc to sort the data in ascending or descending order with single field or multiple field. Syntax for the same is as follows –

select * from tablename order by fieldname asc/desc, fieldname asc/desc ...

To sort customer table data in ascending order of name as follows –

select * from CustomerTbl order by customername asc

To sort customer table data with multiple fields you can do it as follows –

select * from CustomerTbl order by customername asc,creditlimit desc

How to use Group by clause in SQL?

Group by clause in SQL can be used to group the data from table using some field or set of fields. Once you group the data you can use the summary functions to generate the summary of data as follows –

sum(fieldname) :- it will create the sum of the data from a field name passed as parameter.
max(fieldname):- it will find the maximum value from the field name passed as parameter.
min(fieldname):- it will find the minimum value from the field name passed as parameter.
count(fieldname/*):- it will count no of records for a field name passed as parameter.
avg(fieldname):- it will get the avg of the values from the field name passed as parameter.

Let’s group the customer by address and find out the total number of customers, sum of credit limit, minimum credit limit and maximum credit limit.

select address,min(creditlimit) minimu, max(creditLimit) maximum, sum(creditlimit) as TotalCreditLimit,count(*) as CountOfCustomers from customertbl group by address

Use of having clause in group by, having is used to apply some condition in group by . Let’s execute above query only for city pune and mumbai.

select address,min(creditlimit) minimu, max(creditLimit) maximum, sum(creditlimit) as TotalCreditLimit,count(*) as CountOfCustomers from customertbl group by address having address in ('Pune','Mumbai')

Update and Delete Query

Update query will help you to update the existing data single record or multiple records. It has following syntax –

Upate tablename set fieldname=newvalue,fieldname=newvalue,fieldname=newvalue ... where condition.

Suppose we want to update address and email id of the customer whose id is –

update customertbl set address='Pune', mobileno='898989' where customerid=4

Delete command is used to delete the data from table, it has following syntax –

Delete from tablename where condition.

To delete all the records where address is pune we can use the following query.

delete from customertbl where address='Pune'