Video : 19 SSMS SQl Sever mangement studio
SQL Db
CREATE DATADASE PRODUCTION ---------- For Data Based
CREATE TABLE Orders
(
OrderID INT,
Customer VARCHAR(10),
Gender VARCHAR(2).
BirthDate DATE,
Product VARCHAR(15),
Category VARCHAR(20),
State VARCHAR(20),
Region VARCHAR(10),
Sales INT
)
How to Insert data into Table?
INSERT INTO Orders VALUES(1,'lohi', Mº 1970-01-15,'Laptop', 'Electronics",
'California', 'West',300):
INSERT INTO Orders VALUES,2, 'Lohan',1970-01-15, Jeans','Clothing, Newyork,
Cut',400);
INSERT INTO Orders VALUES(3, 'Linda',P, 1935-11-24, 'Laptop', 'Clectronics',
Newyork, 'East', 300);
INSERT INTO Orders VALUES(4, 'Unda', F, 1985-11-24 Jeans, Clothing, "California',
'West',250);
INSERT INTO Orders VALUES(5, Null, Null, Null, Null, 'Electronics',
'Newyork', 'East',500);
How to Query Table?
SELECT * FROM Orders
How to Drop table ?
Drop TABLE Orders
=======================================================================
Video 20 Data Base Operations
Delete TableName where orderid = 5 its delete the data based on the filter
condition
Trancat Table TableName It will delete entire data not table structure
Alter Table TableName ADD columeName Varchar(100) It will add new colume in the
exiting table
Update TableName set cloumeName 'USE' it will update the entire colume in the table
Upadte TableName set ColumeName = 'M' where ColumeNmae = 5
Aggretions function:
---------------------
SELECT SUM(ColumeName) FROM TableName
SELECT SUM(ColumeName) AS Totalsales FROM TableName For cloume name (OR) SELECT
SUM(ColumeName) AS "Total Sales" FROM TableName (OR) SELECT SUM(ColumeName) AS
[Total sales] FROM TableName
SELECT AVG (CloumeName) AVG_Sales FROM TableName---- Avarage of sales
SELECT MIN (CloumeName) Min_Sales FROM TableName---- Minimum of sales
SELECT MAX (CloumeName) Max_Sales FROM TableName---- Maximun of sales
SELECT COUNT (CloumeName) FROM TableName---- For count of cloums
SELECT COUNTDESTINCT (CloumeName) FROM TableName---- For Count of cloume (Remove
the duplicate )
SELECT DESTINCT ColumeName from TableName
SELECT *, REPLACE (ColumeName, 'M','MALE') from TableName
SELECT *, REPLACE (ColumeName, 'M','MALE') from TableName WHERE ID = 5 ----------
It replace the Particular Value
SELECT *, REPECATE (Product, 2) FROM TableName --- IT just repated cloume (Write
text muliple time)
SELECT CONCAT (CITY, Country) FROM TABLENAME -- It concataned values
(HYDERABADINDIA)
SELECT CONCAT (City, CONCAT (' ', country)) FROM TABLENAME-----------------
(HYDERABAD INDIA) if want space write this formula
SELECT REVERSE (COLUMENAME) FROM TABLENAME ---- Reverse the Text
----------------------------------------------------------------------------------
Filter Function:
------------------------------------------------
I want filter the sales 300 to 500
SELETE * from TableName WHERE ColumeName BETWEEN 300 to 500 --- Give the result in
300 to 500 values
ABove case revarse
SELETE * from TableName WHERE ColumeName NOTBETWEEN 300 to 500