DatabaseQueries.sql

//Create Database through Query :
Create DATABASE Customers_Database;

//Create a Table of Customer & its Attributes with Respective Data types & Primary Key through Query.
 Create Table Customers
(
CustomerID int,
CustomerName char(200),
ContactName char(200),
Adress char (500),
City char(200),
PostalCode char(200),
Country char(200),
PRIMARY KEY(CustomerID)
);

//Now Insert Data in this table through Queries.
insert into Customers values('87','Wartian Herkku','Pirkko Koshitalo','Torikatu 38','Oulu','90110','Finland');

//Update Contact Name of  Customer "Jane Banks" to "Pirko Kshtiako" through Query
update Customers set ContactName = 'Jane Banks' WHERE ContactName = 'Pirkko Koshitalo';

//Delete Record of Customer whose id is 90
delete Customers where CustomerID = '90';

//Add a column HomeAddress in Customers table using alter command.
ALTER TABLE Customer ADD HomeAddress varchar(500); 

//Display the current Date and Time.
select GetDate() as CurrentDateTime

//Use getdate() function to get system date then use datepart() function to extract month, year and date from it.
SELECT DATEPART (YYYY, GETDATE()) as SystemDate , DATEPART(mm,getdate()) as Datee, DATEPART(MONTH, GETDATE()) as Monthh, DATEPART(YEAR, GETDATE()) as Year ;

//Calculate the average of the UnitPrice from Products using DISTINCT.
Select Avg( Distinct UnitPrice) from Products

//Calculate the number of employees working in HumanResources.
Select COUNT (BusinessEntityID) from Person.BusinessEntity

//Calculate Absolute Value of 564.32.
Select ABS(564.32)

//Generate Random Number.
Select RAND

//Calculate Square Root of 6666.
Select SQRT(6666)

//Calculate Exponential Value of 10
Select EXP(10)

//Count Tangent of 12
Select TAN(12)

//Calculate Power of (2,2)
Select POWER(2,2)

//Calculate CosQ of 67'
Select COS(67)

//Calculate SinQ of 12'
Select Sin(12)

//Calculate Log of 12
Select Log(12)

//Calculate Square of 23
Select SQUARE(23)

//Get Order id, product id, unit price from Order Details.
select orderid , productid, unitprice from [Order Details]

//Count total number of employees.
select COUNT(EmployeeID) from Employees

//Get the most expensive product amount from Products.
select max (UnitPrice) from Products

//Multiply unit price to quantity in OrderDetails to get   OrderPrice.
select unitprice*Quantity as orderprice from [Order Details]

//Display all cities that employees belong to but don’t allow repetition.
select distinct City from Employees

//Display the complete name of all the employees.
select FirstName+LastName as EmplyeeName from Employees

//Display data of all employees those working as Sales Representative.
select * from Employees where Title = 'Sales Representative'

//Display the CustomerID of customers whose order is shipped to London and display the column name as CustomerID_London.
select CustomerID AS [CustomerIDLondon] from Customers
where City = 'London'

//Display Order Details where Quantity is greater than 2.
select * from [Order Details] where Quantity >2

//Display all the orders where unit price lies in the range of 10$ to 40$.
 select * from [OrderDetails] where UnitPrice Like '[10.00
40.00]%';

//Display all the orders in Order Details where Discount is empty.
select * from [Order Details]  where Discount = 0;

//In Orders, display the Ship Name that end with the alphabet a.
 select ShipName from Orders  where ShipName like '%a';

//In Customers, display the Company Name where Region is NULL.
select CompanyName from Customers where Region is Null

//Display the name of Employees whose name contain ‘an’.
select FirstName+LastName from Employees where FirstName+LastName like '[an]%'

//List titles whose price is greater than 20.
Select * from titles where price > 20;

//List all employees who were hired before ‘Margaret’.
SELECT  HireDate FROM Employees
WHERE  (HireDate >= '1993-05-03 00:00:00.000')

//Sort employees by hire date in ascending order.
SELECT * FROM Employees ORDER BY HireDate ASC

//Sort suppliers by postal code in descending order.
SELECT * FROM Suppliers ORDER BY PostalCode DESC

//List the name of all employees whose first name starts with the letter ‘A’.
select * from Employees where FirstName like 'A%';

//Display complete names of customers those who are either an Owner or living in
Seattle city.
select FirstName+LastName from Employees where Title = 'Owner' or City Like 'Seattle'