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


Use wildcard in sql statement

Wildcard in SQL Select Statement: Wildcard ^[] indicate the single character not in the bracket. Example ^[ABCDEF] it indicates not A, B, C, D, E, F.

Wildcard sign is used with SQL Select Where Statement.

Syntax:
   Select */COLUMN_NAME From TABLE_NAME Where COLUMN_NAME Like [^ABCD] 


Description:
             Select: SQL Keyword.
             * | COLUMN_NMME: All or specific column name.
             From: SQL Keyword.
             TABLE_NAME: Name of table.
             Where: SQL keyword.
            [^ABCD]: Indicates not in.



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



Now, SQL Wildcard sign example

Select ItemCode,ItemName,ManufactureCompany from ItemDetails Where ManufactureCompany like'[Si]%'
Result:
ItemCode ItemName ManufactureCompany
5 Apple Mobile Apple
6 Apple Computer Apple
7 Apple Macbook Apple
8 Dell Computer DELL
9 Dell Laptop DELL
10 Apple Laptop Apple
11 Apple Headphone Apple
14 Ascer Computer Ascer
16 Borother Printer Brother
17 Ascer Printer Ascer
18 Brother Photocopy Brother
19 Brother Multi Printer Brother


Select ItemCode,ItemName,ManufactureCompany from ItemDetails Where ManufactureCompany like'[ADS]%'
Result:
ItemCode ItemName ManufactureCompany
4 iPhone iPone
16 Borother Printer Brother
18 Brother Photocopy Brother
19 Brother Multi Printer Brother


Select ItemCode,ItemName,ProductCountry from ItemDetails Where ProductCountry like'[K]%'
Result:
ItemCode ItemName ProductCountry
4 iPhone United Stat
5 Apple Mobile United Stat
6 Apple Computer United Stat
7 Apple Macbook United Stat
8 Dell Computer United Stat
9 Dell Laptop United Stat
10 Apple Laptop United Stat
11 Apple Headphone United Stat
12 Sony Camera Japan
13 Sony Headphone Japan
14 Ascer Computer China
16 Borother Printer China
17 Ascer Printer China
18 Brother Photocopy China
19 Brother Multi Printer India



Select ItemCode,ItemName,ProductCountry from ItemDetails Where ProductCountry like'[IC]%'
Result:
ItemCode ItemName ProductCountry
1 Samsung Mobile Korea
2 Samsung Pro Korea
3 Samsung Headphone Korea
4 iPhone United Stat
5 Apple Mobile United Stat
6 Apple Computer United Stat
7 Apple Macbook United Stat
8 Dell Computer United Stat
9 Dell Laptop United Stat
10 Apple Laptop United Stat
11 Apple Headphone United Stat
12 Sony Camera Japan
13 Sony Headphone Japan



Select ItemCode,ItemName,ItemName from ItemDetails Where ItemName like'[AS]%'
Result:
ItemCode ItemName ItemName2
4 iPhone iPhone
8 Dell Computer Dell Computer
9 Dell Laptop Dell Laptop
16 Borother Printer Borother Printer
18 Brother Photocopy Brother Photocopy
19 Brother Multi Printer Brother Multi Printer



Select ItemCode,ItemName,ItemCategory from ItemDetails Where ItemCategory like'[LP]%'
Result:
ItemCode ItemName ItemCategory
1 Samsung Mobile Mobile
2 Samsung Pro Mobile
3 Samsung Headphone Headphone
4 iPhone Mobile
5 Apple Mobile Mobile
6 Apple Computer Computer
7 Apple Macbook Macbook
8 Dell Computer Computer
11 Apple Headphone Headphone
12 Sony Camera Camera
13 Sony Headphone Headphone
14 Ascer Computer Computer



Select ItemCode,ItemName,ItemCategory from ItemDetails Where ItemCategory like'[HC]%'
Result:
ItemCode ItemName ItemCategory
1 Samsung Mobile Mobile
2 Samsung Pro Mobile
4 iPhone Mobile
5 Apple Mobile Mobile
7 Apple Macbook Macbook
9 Dell Laptop Laptop
10 Apple Laptop Laptop
16 Borother Printer Printer
17 Ascer Printer Printer
18 Brother Photocopy Printer
19 Brother Multi Printer Photocopy




SQL server not like wildcard example, in video 




SQL server not like wildcard example in Image
wildcard not like in SQL




[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