sql Archives - An artist's personal website

Add a user and grant privilege in mysql

Posted on October 25, 2017March 22, 2018 by akash073

CREATE USER 'mcp'@'localhost' IDENTIFIED BY 'password';

1	CREATE USER 'mcp'@'localhost' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON * . * TO 'mcp'@'localhost';

1	GRANT ALL PRIVILEGES ON * . * TO 'mcp'@'localhost';

FLUSH PRIVILEGES;

1	FLUSH PRIVILEGES;

for more information https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql

mysql -u root -pabc

1	mysql -u root -pabc

create database #Export

mysqldump -u [uname] -p[pass] db_name > db_backup.sql

1	mysqldump -u [uname] -p[pass] db_name > db_backup.sql

#import

mysql -u username -p -h localhost DATA-BASE-NAME < data.sql

1	mysql -u username -p -h localhost DATA-BASE-NAME < data.sql

Indexing in Sql Server 2014

Posted on November 30, 2016September 7, 2017 by akash073

Indexing of a table in SQL server usually means a look up table #Clustered Indexing Details…

Backup to network drive in Sql Server 2014

Posted on November 21, 2016November 21, 2016 by akash073

Backup to drive is easy .. Just backup database [DbName] to DISK=’D:\\Backup\Dbname.bak’ or All database Details…

Different Query With Sql Server

Posted on November 20, 2016November 22, 2016 by akash073

#CASE OF SUM OF TWO DIFFERENT COLUMN

select sm.Model,sum(CASE when sm.WarrantyAvailable=1 then 1 else 0 end) Warranty,
sum(CASE when sm.WarrantyAvailable<>1 then 1 else 0 end) NonWarranty
from WSMS.dbo.Requisition r
left join WSMS.dbo.RequisitionDetail rd
on r.RequisitionID=rd.RequisitionID
inner join WSMS.dbo.ServiceMaster sm
on r.ServiceId=sm.ServiceID
where rd.ItemCode not in ('SPAR.MOBL.00000','SPAR.MOBL.99999')
and rd.ItemStatus='Used'
group by sm.Model

1

2

3

4

5

6

7

8

9

10

select sm.Model,sum(CASE when sm.WarrantyAvailable=1 then 1 else 0 end) Warranty,

sum(CASE when sm.WarrantyAvailable<>1 then 1 else 0 end) NonWarranty

from WSMS.dbo.Requisition r

left join WSMS.dbo.RequisitionDetail rd

on r.RequisitionID=rd.RequisitionID

inner join WSMS.dbo.ServiceMaster sm

on r.ServiceId=sm.ServiceID

where rd.ItemCode not in ('SPAR.MOBL.00000','SPAR.MOBL.99999')

and rd.ItemStatus='Used'

group by sm.Model

#ROW NUMBER OVER PARTITION BY

select DATEDIFF(day, ReleaseDate, GETDATE()) AGES
from (
select Model,ReleaseDate ,
Row_number() OVER( partition BY Model ORDER BY ReleaseDate) RowNumber
from tblCellPhoneDepriciationPrice  p 
) X where X.RowNumber=1

1

2

3

4

5

6

select DATEDIFF(day, ReleaseDate, GETDATE()) AGES

from (

select Model,ReleaseDate ,

Row_number() OVER( partition BY Model ORDER BY ReleaseDate) RowNumber

from tblCellPhoneDepriciationPrice p

) X where X.RowNumber=1

#update Details…