SQL Some Basic Queries

Some basic my SQL server queries

Add

Alter table table name

Add column name datatype

Drop

Alter table table name

Drop column address

 Update

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

For example

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1; 

Delete

DELETE FROM table_name WHERE condition;

 Min

SELECT MIN(column_name)
FROM table_name
WHERE condition;

 Count

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

 Between

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

 

 

 Function also known aggregate function

1.      Count

2.      Max

3.      Min

4.      Avg

5.      Sum 

etc

Query:

Select max(column name) from (table name)

For example

create database joinclause

 

create table city

(

cid int not null,

cityname varchar(50) not null,

primary key (cid)

)

 

Select max(cid) from city

 

create table personal

(

id int not null,

name varchar(20),

age int,

gender varchar(20),

city int not null,

foreign key(city) references city (cid)

)

Select sum(age) from personal

 

Logical operator

For example =, >=, <=, ==  etc

Queries

Select * from table name

where column name =’value’ or column name =’value’

for example

create database joinclause

 

create table city

(

cid int not null,

cityname varchar(50) not null,

primary key (cid)

)

 

insert into city (cid,cityname)

values(1,'FSD')

insert into city (cid,cityname)

values(2,'MULTAN')

insert into city (cid,cityname)

values(3,'BWP')

insert into city (cid,cityname)

values(4,'CHISHTIAN')

Query

Select * from city

where cityname =’fsd’ or cityname =’bwp’

 

like query

select * from table name where column name like ‘any letter%’

for example

insert into city (cid,cityname)

values(1,'FSD')

insert into city (cid,cityname)

values(2,'MULTAN')

insert into city (cid,cityname)

values(3,'BWP')

insert into city (cid,cityname)

values(4,'CHISHTIAN')

Query

select * from city where cityname like ‘f%’

 

 ‘a%’ this use first letter

‘%a’ this use last letter

‘-a%’ not first letter but see next letter

‘a%n’ also seen first letter and last

 

Functions (in, not in)

Select * from table name where column name in(‘value’ ,’value’)

Select * from table name where column name not in(‘value’ ,’value’)

for example

insert into city (cid,cityname)

values(1,'FSD')

insert into city (cid,cityname)

values(2,'MULTAN')

insert into city (cid,cityname)

values(3,'BWP')

insert into city (cid,cityname)

values(4,'CHISHTIAN')

Query

Select * from city where cityname in(‘multan’ ,’chishtian’)

Select * from city where cityname not in(‘multan’ ,’chishtian’)

 

Join function

Inner join or join

For example

create database joinclause

 

create table city

(

cid int not null,

cityname varchar(50) not null,

primary key (cid)

)

 

create table personal

(

id int not null,

name varchar(20),

age int,

gender varchar(20),

city int not null,

foreign key(city) references city (cid)

)

select * from personal inner join city on personal.city=city.cid

and also use temporary name

select p.id, p.name, p.age, p.gender, c.cityname from

personal p inner join city c on p.city=c.cid

 

Queries

 left join

select * from personal left join city on personal.city=city.cid

 

Right join

select * from personal right join city on personal.city=city.cid

 

Full join

select * from personal full join city on personal.city=city.cid

 

 

Group by

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

 

Having by

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

 

IS NULL Syntax

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

 

IS NOT NULL Syntax

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;