create database db_Test
use db_Test
create table person
(
id int primary key,
name varchar(50),
orderId int,
phoneno int
)
insert into person(id,name,orderId,phoneno)values(1,'John',101,123)
insert into person(id,name,orderId,phoneno)values(2,'Jonny',102,145)
insert into person(id,name,orderId,phoneno)values(3,'John',103,543)
insert into person(id,name,orderId,phoneno)values(4,'John',104,435)
insert into person(id,name,orderId,phoneno)values(5,'John',105,567)
insert into person(id,name,orderId,phoneno)values(6,'potter',106,709)
insert into person(id,name,orderId,phoneno)values(7,'Aaloo',107,509)
insert into person(id,name,orderId,phoneno)values(8,'kalu',108,980)
update person set name ='Hammer' where id =3
update person set name ='Kitty' where id =4
update person set name ='Humpy' where id =5
update OrderId set orderId=101 where orderId =1
create table Ordre
(
orderId int primary key,
Quantity int,
Items varchar(50)
)
insert into Ordre(orderId, Quantity, Items)values(101, 2, 'Jeans')
insert into Ordre(orderId, Quantity, Items)values(102, 3, 'Tshirt')
insert into Ordre(orderId, Quantity, Items)values(103, 4, 'Shampoo')
insert into Ordre(orderId, Quantity, Items)values(104, 1, 'Clock')
insert into Ordre(orderId, Quantity, Items)values(105, 1, 'Shop')
insert into Ordre(orderId, Quantity, Items)values(106, 2, 'colddrink')
select * from person
select * from Ordre
//select * from person right join Ordre on person.orderId = Ordre.orderId
//update OrderId set orderId=101 where orderId =1
//update Ordre set Quantity = Quantity + 1
// update person set orderId=9, phoneno=900
// delete from person where id=9
//select * from person order by name
//select * from person order by orderid desc
where clasue is used with select , update and delete
//update person set phoneno =667 where id = 3
//select * from person where orderid = 1
Aggregate function or group function = this function is used with integer
sum
avg
min
max
count = count particular column value cannot count null value
select sum(orderid) from person
select avg(orderid) from person
select min(orderid) from person
select max(orderid) from person
select count(orderid) from person
Numeric Function
ABS means absolute function
select ABS(-10)
select CEILING(78.5)
select FLOOR(78.9)
select SIGN(12), SIGN(-12), SIGN(0) return 1 when value is positive -1 when value
is negative and zero in case of 0
select SQUARE(9), SQRT(25), PI(), cos(30), sin(90)
select EXP(2)
///string function
select LEN("database") count database length
select name, LEN(name) from person
select upper('this is sql')
select name, Lower(name) from person
select RTRIM('this is ball ')
select LTRIM(' this is ball')
select SUBSTRING('microsoft', 6,9)
select REPLACE('microsoft', 'micro', 'major')
select REPLICATE('hi', 5)
//Boolean function
//group by
select depno from person group by depno
select sum(salary) from person Group By DeptNo
select deptno, sum(salary) as 'total salary ' from person Group By DeptNo
select deptno, min(salary) as 'total salary ' from person Group By DeptNo
select deptno, max(salary) as 'total salary ' from person Group By DeptNo
select deptno, avg(salary) as 'total salary ' from person Group By DeptNo
select deptno, round(avg(salary), 2) as 'Avg salary ' from person Group By DeptNo
// Having clause
Having clause define the condition that is then applied to groups of rows.
always used with select statement inside group by clause.
having is come after group by.
where clause is come before group by otherwise give error.
both of these two quary give same out put.
select deptno, sum(Salary) as 'Total salary' from person group by deptno Having =20
select deptno, sum(Salary) as 'Total salary' from person where deptno = 20 group by
deptno
//Top() clause
the Top clause specifies the first n rows of the query result that are to be
retrived.
this clause should always be used with the order by clause
return multiple rows.
select empsalary from person order by empsalary
select empsalary from person order by empsalary desc
select Top(3) empsalary from person order by empsalary desc
select Top(3) empsalary from person order by empsalary
//Alter(DDL)
alter table emp add salary decimal
alter table emp addphone varchar(10) null
//alter with costraints
alter table emp add projected_complted int not null default5 decimal not null
alter table emp add salary decimal not null
alter table emp add projectID integer null constraints pID_unique_key UNIQUE
//add more than one column
alter table emp add salary decimal default 15000, projectID integer null constraint
pID_unique_key UNIQUE
//Dropping acolumn or columns
- remove a column or multiple columns
alter table emp drop column salary
alter table emp drop column salary age
//Union and Intersection
CREATE TABLE TableA
(
ID INT,
Name VARCHAR(50),
Gender VARCHAR(10),
Department VARCHAR(50)
)
GO
INSERT INTO TableA VALUES(1, 'Pranaya', 'Male','IT')
INSERT INTO TableA VALUES(2, 'Priyanka', 'Female','IT')
INSERT INTO TableA VALUES(3, 'Preety', 'Female','HR')
INSERT INTO TableA VALUES(3, 'Preety', 'Female','HR')
GO
select * from TableA
CREATE TABLE TableB
(
ID INT,
Name VARCHAR(50),
Gender VARCHAR(10),
Department VARCHAR(50)
)
GO
INSERT INTO TableB VALUES(2, 'Priyanka', 'Female','IT')
INSERT INTO TableB VALUES(3, 'Preety', 'Female','HR')
INSERT INTO TableB VALUES(4, 'Anurag', 'Male','IT')
GO
SELECT ID, Name, Gender, Department FROM TableA
UNION ALL
SELECT ID, Name, Gender, Department FROM TableB
SELECT ID, Name, Gender, Department FROM TableA
UNION
SELECT ID, Name, Gender, Department FROM TableB
SELECT ID, Name, Gender, Department FROM TableA
Intersect
SELECT ID, Name, Gender, Department FROM TableB
//EXCEPT Operator:
The EXCEPT operator will return unique rows from the left query that aren’t present
in the right query’s results.
SELECT ID, Name, Gender, Department FROM TableA
EXCEPT
SELECT ID, Name, Gender, Department FROM TableB
select * from TableA
select * from TableB
//self join
use db_test
---self join
create table Emp
(
EmpId int primary key identity,
EmpName varchar (20),
ManagerId int
)
insert into Emp(EmpName,ManagerId)values('John','3')
insert into Emp(EmpName,ManagerId)values('Tom','1')
insert into Emp(EmpName,ManagerId)values('Harry','2')
insert into Emp(EmpName,ManagerId)values('Dew','3')
insert into Emp(EmpName,ManagerId)values('Panther','1')
select * from emp
select E.EmpName as Emp, M.EmpName as Manager
from Emp E join Emp M on E. ManagerId =M.EmpId
---function
create Function [dbo].[Sum]
(
@num1 int,
@num2 int
)
Returns int
As
begin
Declare @Result int
select @Result = @num1 +@num2
return @Result
End
create proc[dbo].[callFunction]
@FirstNum int,
@SecondNum int
As
Begin
select dbo.[sum](@FirstNum,@SecondNum)
End
[callFunction] 2,3
string filepath = "D:\textfile.txt";
if (File.Exists(filepath))
{
File.Delete(filepath);
Console.WriteLine("Existing Text File Deleted.");
}
File.WriteAllText(filepath, "Writing this text to a file.");
Console.WriteLine("Created a new text file and wrote the text in it.");
Console.WriteLine(File.ReadAllText(filepath));
