Sql Tutorial Class


SQL Lesson Topic
1.Sql Query Classes
2.Create a database
3.Drop database
4.Database Rename

5.Data Type
6.Create table sql
7.Insert into table
8.Select Statement
7.1.Primary key
7.2.UNIQUE Key
7.3.FOREIGN Key
7.4.Check Constraint
7.5.Default SQL
7.6.Delete Statement
7.7.Truncate Table
7.8.Update Stat.
7.9.DROP Table

9.Custom column Heading
10.Sql Select Where
11.Operators
12.Greater than Equalto
13.Greater than
14.Less than
15.Less than equalto
16.SQL SUM()
17.SQL MAX()
18.SQL MIN()
19.SQL AVG()
20.SQL COUNT()
21.Like Wildcard(%)
22.Like Wildcard(_)
23.Like Wildcard []
24.Like wildcard [^]
25.AND Operator
26.OR Operator
27.NOT Operator
28.IS NULL
29.IS NOT NULL
30.IN KeywordL
31.NOT IN Keyword
32.Between
33.Not Between
34.Distinct keyword
35.SELECT INTO
36.Insert into Select
37.ORDER BY ASC
38.ORDER BY DESC
39.SQL TOP 10
40.HAVING Clause
41.UNION Clause
42.Date Format


How to use not operator in sql query

Not operator in SQL Server: "NOT operator" exclude the condition's value.

 Example: NOT Country="Korea" --display countries except Korea.

NOT Logical Operator:
Syntax:
         Select [*|COLUMN_NAME,...] From [TABLE_NAME] Where [COLUMN_NAME]=['VALUE'] NOT [COLUMN_NAME]=['VALUE']

Description:
             Select: SQL Keyword.
             * | COLUMN_NMME: All or specific column name.
             From: SQL Keyword.
             TABLE_NAME: Name of table.
             Where: SQL keyword.
            [NOT]: Logical operator.
Here is a Table ItemDetails


ItemCodeItemNameManufactureCompanyItemSerialNoItemCategoryItemQuantityItemPriceProductCountry
1Samsung MobileSamsungS12345Mobile500240Korea
2Samsung ProSamsungSP1234Mobile100300Korea
3Samsung HeadphoneSamsungSH2345Headphone5050Korea
4iPhoneiPoneiP12345Mobile1000500United Stat
5Apple MobileAppleA12345Mobile200300United Stat
6Apple ComputerAppleA12343Computer200400United Stat
7Apple MacbookAppleAM2345Macbook50600United Stat
8Dell ComputerDELLD02345Computer200240United Stat
9Dell LaptopDELLDL2345Laptop30240United Stat
10Apple LaptopAppleAL2345Laptop500100United Stat
11Apple HeadphoneAppleAH2345Headphone5020United Stat
12Sony CameraSonySN2345Camera200240Japan
13Sony HeadphoneSonySNH345Headphone50050Japan
14Ascer ComputerAscerAC2345Computer100600China
16Borother PrinterBrotherB12345Printer200200China
17Ascer PrinterAscerAP2345Printer200150China
18Brother PhotocopyBrotherBP2345Printer20150China
19Brother Multi PrinterBrotherBM243PhotocopyNULLNULLIndia


SQL "NOT" logical operator example:

 Now,

Select ItemCode,ItemName,ManufactureCompany,ItemPrice from ItemDetails Where NOT ManufactureCompany ='Samsung'
Result:
ItemCode ItemName ManufactureCompany ItemPrice
4 iPhone iPone 500
5 Apple Mobile Apple 300
6 Apple Computer Apple 400
7 Apple Macbook Apple 600
8 Dell Computer DELL 240
9 Dell Laptop DELL 240
10 Apple Laptop Apple 100
11 Apple Headphone Apple 20
12 Sony Camera Sony 240
13 Sony Headphone Sony 50
14 Ascer Computer Ascer 600
16 Borother Printer Brother 200
17 Ascer Printer Ascer 150
18 Brother Photocopy Brother 150
19 Brother Multi Printer Brother NULL







Select ItemCode,ItemName,ProductCountry,ProductCountry from ItemDetails Where NOT ItemCategory='Mobile' 
Result:

ItemCode ItemName ProductCountry ProductCountry2
3 Samsung Headphone Korea Korea
6 Apple Computer United Stat United Stat
7 Apple Macbook United Stat United Stat
8 Dell Computer United Stat United Stat
9 Dell Laptop United Stat United Stat
10 Apple Laptop United Stat United Stat
11 Apple Headphone United Stat United Stat
12 Sony Camera Japan Japan
13 Sony Headphone Japan Japan
14 Ascer Computer China China
16 Borother Printer China China
17 Ascer Printer China China
18 Brother Photocopy China China
19 Brother Multi Printer India India




Select ItemCode,ItemName,ProductCountry,ItemQuantity from ItemDetails Where NOT ItemQuantity='300'
Result:
ItemCode ItemName ProductCountry ItemQuantity
1 Samsung Mobile Korea 500
2 Samsung Pro Korea 100
3 Samsung Headphone Korea 50
4 iPhone United Stat 1000
5 Apple Mobile United Stat 200
6 Apple Computer United Stat 200
7 Apple Macbook United Stat 50
8 Dell Computer United Stat 200
9 Dell Laptop United Stat 30
10 Apple Laptop United Stat 500
11 Apple Headphone United Stat 50
12 Sony Camera Japan 200
13 Sony Headphone Japan 500
14 Ascer Computer China 100
16 Borother Printer China 200
17 Ascer Printer China 200
18 Brother Photocopy China 20





NOT Operator in SQL Server
Video





Not Operator in SQL Server
Image
NOT operator in SQL Server





[Note: We have created and inserted values into sql table in previous topic. For more details Click here.
OR
Execute the follow command in your sql query window for executing above sql select where statement]
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Create Table ItemDetails
(
ItemCode int,
ItemName varchar(30),
ManufactureCompany varchar(50),
ExpiredDate date,
ItemSearialNo varchar (30),
ItemCategory varchar(30),
ItemQuantity int,
ItemPrice float,
ProductCountry varchar(30),
ReceivedDate date,
ItemImage image
)

Insert into ItemDetails values('1','Samsung Mobile','Samsung','12-10-2024','12-01-2050','S12345','Mobile','500','240','Korea','12-10-2025')
Insert into ItemDetails values('2','Samsung Pro','Samsung','12-10-2024','12-01-2050','SP1234','Mobile','100','300','Korea','12-10-2025')
Insert into ItemDetails values('3','Samsung Headphone','Samsung','12-10-2023','12-01-2053','SH2345','Headphone','50','50','Korea','12-10-2025')
Insert into ItemDetails values('4','iPhone','iPone','12-01-2024','12-01-2049','iP12345','Mobile','1000','500','United Stat','12-10-2025')
Insert into ItemDetails values('5','Apple Mobile','Apple','12-11-2024','12-01-2049','A12345','Mobile','200','300','United Stat','12-10-2025')
Insert into ItemDetails values('6','Apple Computer','Apple','12-02-2024','12-01-2050','A12343','Computer','200','400','United Stat','12-10-2025')
Insert into ItemDetails values('7','Apple Macbook','Apple','12-11-2024','12-01-2050','AM2345','Macbook','50','600','United Stat','12-10-2025')
Insert into ItemDetails values('8','Dell Computer','DELL','12-09-2024','12-01-2050','D02345','Computer','200','240','United Stat','12-10-2025')
Insert into ItemDetails values('9','Dell Laptop','DELL','12-09-2024','12-01-2050','DL2345','Laptop','30','240','United Stat','12-10-2025')
Insert into ItemDetails values('10','Apple Laptop','Apple','12-08-2024','12-01-2050','AL2345','Laptop','500','100','United Stat','12-10-2025')
Insert into ItemDetails values('11','Apple Headphone','Apple','12-12-2024','12-01-2050','AH2345','Headphone','50','20','United Stat','12-10-2025')
Insert into ItemDetails values('12','Sony Camera','Sony','12-11-2024','12-11-2050','SN2345','Camera','200','240','Japan','12-11-2025')
Insert into ItemDetails values('13','Sony Headphone','Sony','12-09-2024','12-01-2050','SNH345','Headphone','500','50','Japan','12-11-2025')
Insert into ItemDetails values('14','Ascer Computer','Ascer','12-09-2024','12-07-2050','AC2345','Computer','100','600','China','12-10-2025')
Insert into ItemDetails values('16','Borother Printer','Brother','12-09-2024','12-01-2050','B12345','Printer','200','200','China','12-10-2025')
Insert into ItemDetails values('19','Brother Multi Printer','Brother',GETDATE(),'2050-10-10','BM243','Photocopy',null,null,'India',GETDATE())

Insert into ItemDetails (ItemCode,ItemName,ManufactureCompany,ProductDate,ExpiredDate,ItemSearialNo,ItemCategory,ItemQuantity,ItemPrice,ProductCountry,ReceivedDate) values('17','Ascer Printer','Ascer','12-09-2024','12-07-2050','AP2345','Printer','200','150','China','12-10-2025')
Insert into ItemDetails (ItemCode,ItemName,ManufactureCompany,ProductDate,ExpiredDate,ItemSearialNo,ItemCategory,ItemQuantity,ItemPrice,ProductCountry,ReceivedDate) values('18','Brother Photocopy','Brother','12-08-2024','12-07-2050','BP2345','Printer','20','150','China','12-10-2025')
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Labels:

No comments:

Post a Comment

Amazon Today's Deals

Amazon Best Sellers

DMC