SQL Server TSQL SELECT First Name Last Name

SQL Server آﻤﻮﺯﺵ

TSQL چﻨﺪیﻦ ﻧﻤﻮﻧﻪ ﺍﺯ ﺩﺳﺘﻮﺭﺍﺕ SELECT First. Name, Last. Name FROM dbo. Employees; SELECT * FROM dbo. Employees; ---------------SELECT Last. Name + ', ' + First. Name FROM dbo. Employees;

TSQL چﻨﺪیﻦ ﻧﻤﻮﻧﻪ ﺍﺯ ﺩﺳﺘﻮﺭﺍﺕ -- Aliasing column names SELECT Last. Name + ‘ , ‘ + First. Name AS [Full Name] FROM dbo. Employees;

TSQL چﻨﺪیﻦ ﻧﻤﻮﻧﻪ ﺍﺯ ﺩﺳﺘﻮﺭﺍﺕ -- This is deprecated: SELECT 'Full. Name' = Last. Name + ', ' + First. Name FROM dbo. Employees; --SELECT and SELECT DISTINCT SELECT Title FROM dbo. Employees; SELECT DISTINCT Title FROM dbo. Employees;

TSQL چﻨﺪیﻦ ﻧﻤﻮﻧﻪ ﺍﺯ ﺩﺳﺘﻮﺭﺍﺕ --WHERE SELECT Company. Name, City FROM dbo. Customers WHERE City = 'Paris';

TSQL چﻨﺪیﻦ ﻧﻤﻮﻧﻪ ﺍﺯ ﺩﺳﺘﻮﺭﺍﺕ --LIKE and wildcard characters SELECT Company. Name FROM dbo. Customers WHERE Company. Name LIKE 'S%'; SELECT Company. Name FROM dbo. Customers WHERE Company. Name LIKE '%S%';

TSQL چﻨﺪیﻦ ﻧﻤﻮﻧﻪ ﺍﺯ ﺩﺳﺘﻮﺭﺍﺕ --BETWEEN SELECT Last. Name, First. Name, Postal. Code FROM dbo. Employees WHERE Postal. Code BETWEEN '98103' AND '98999'; --Testing for Null SELECT Last. Name, First. Name, Region FROM dbo. Employees WHERE Region IS NULL;

TSQL چﻨﺪیﻦ ﻧﻤﻮﻧﻪ ﺍﺯ ﺩﺳﺘﻮﺭﺍﺕ --AND requires both conditions to be true SELECT Last. Name, City, Postal. Code FROM dbo. Employees WHERE City = 'Seattle' AND Postal. Code LIKE '9%'; --OR only requires one condition to be true SELECT Last. Name, City, Postal. Code FROM dbo. Employees WHERE City = 'Seattle' OR Postal. Code LIKE '9%';

TSQL چﻨﺪیﻦ ﻧﻤﻮﻧﻪ ﺍﺯ ﺩﺳﺘﻮﺭﺍﺕ --IN SELECT Customer. ID, Country FROM dbo. Customers WHERE Country IN ('France', 'Spain'); --IN with a subquery SELECT Customer. ID FROM dbo. Customers WHERE Customer. ID NOT IN(SELECT Customer. ID FROM dbo. Orders);

TSQL چﻨﺪیﻦ ﻧﻤﻮﻧﻪ ﺍﺯ ﺩﺳﺘﻮﺭﺍﺕ --ORDER BY SELECT Last. Name, City FROM dbo. Employees ORDER BY City;

TSQL چﻨﺪیﻦ ﻧﻤﻮﻧﻪ ﺍﺯ ﺩﺳﺘﻮﺭﺍﺕ --Sorting in descending order SELECT Last. Name, City FROM dbo. Employees ORDER BY City DESC; --Sorting on multiple columns SELECT Last. Name, City FROM dbo. Employees ORDER BY City DESC, Last. Name ASC;

TSQL چﻨﺪیﻦ ﻧﻤﻮﻧﻪ ﺍﺯ ﺩﺳﺘﻮﺭﺍﺕ --Using Grouping SELECT City FROM dbo. Employees GROUP BY City;

TSQL چﻨﺪیﻦ ﻧﻤﻮﻧﻪ ﺍﺯ ﺩﺳﺘﻮﺭﺍﺕ --A Cartesian Product SELECT Product. Name, Category. Name FROM dbo. Products, dbo. Categories;

کﻨیﻢ ﻣی ﺍﺳﺘﻔﺎﺩﻩ Professor ﻭ Student ﺟﺪﻭﻝ ﺩﻭ ﺍﺯ آﻤﻮﺯﺵ ﺍیﻦ ﺩﺭ • ﻣﺎ . Student ﺟﺪﻭﻝ • Professor • ﺟﺪﻭﻝ • • SELECT Students. Name , Students. Family, Students. Code, Professors. Name, Professors. Family, Professors. Code FROM Students INNER JOIN Professors ON Students. Professor_ID=Professors. ID

Left Join • ﺍﺯ ﺳﻄﺮﻫﺎی ﻭﻟی ﺷﻮﺩ ﻣی ﺩﺍﺩﻩ ﻧﻤﺎیﺶ ﺩﺍﺭﺩ ﻗﺮﺍﺭ Join چپ ﺩﺭ کﻪ ﺟﺪﻭﻟی ﺳﻄﺮﻫﺎی ﺗﻤﺎﻡ • . ﺷﻮﻧﺪ ﻧﻤی ﺩﺍﺩﻩ ﻧﻤﺎیﺶ ﻧﺪﺍﺭﺩ ( چپ )ﺳﻤﺖ ﺍﻭﻟی ﺟﺪﻭﻝ ﺍﺯ ﻣﺘﻨﺎﻇﺮی کﻪ ﺩﻭﻡ ﺟﺪﻭﻝ : ﺩﺳﺘﻮﺭ ﺑﺪﻧﻪ • • • SELECT column_name(s) FROM table 1 LEFT JOIN table 2 ON table 1. column_name=table 2. column_name; ------------SELECT Students. Name , Students. Family, Students. Code, Professors. Name, Professors. Family, Professors. Code FROM Students left JOIN Professors ON Students. Professor_ID=Professors. ID •

Right Join • ﻭﻟی ﺷﻮﺩ ﻣی ﺩﺍﺩﻩ ﻧﻤﺎیﺶ ﺩﺍﺭﺩ ﻗﺮﺍﺭ Join ﺭﺍﺳﺖ ﺩﺭ کﻪ ﺟﺪﻭﻟی ﺳﻄﺮﻫﺎی ﺗﻤﺎﻡ ﻧﺪﺍﺭﺩ ( ﺭﺍﺳﺖ ﺩﻭﻣی)ﺳﻤﺖ ﺟﺪﻭﻝ ﺍﺯ ﻣﺘﻨﺎﻇﺮی کﻪ ﺍﻭﻝ ﺟﺪﻭﻝ ﺍﺯ ﺳﻄﺮﻫﺎی . ﺷﻮﻧﺪ ﻧﻤی ﺩﺍﺩﻩ ﻧﻤﺎیﺶ : ﺩﺳﺘﻮﺭ • ﺑﺪﻧﻪ • • • SELECT column_name(s) FROM table 1 RIGHT JOIN table 2 ON table 1. column_name=table 2. column_name; --------------------------------------- • • • SELECT Students. Name , Students. Family, Students. Code, Professors. Name, Professors. Family, Professors. Code FROM Students right JOIN Professors ON Students. Professor_ID=Professors. ID

right join ﻧﺘیﺠﻪ

Full Join . ﺗﻤﺎﻡ ﺳﻄﺮﻫﺎی ﻫﺮ ﺩﻭ ﺟﺪﻭﻝ ﺩﺭ ﺧﺮﻭﺟی ﻧﻤﺎیﺶ ﺩﺍﺩﻩ ﻣی ﺷﻮﻧﺪ : ﺑﺪﻧﻪ ﺩﺳﺘﻮﺭ SELECT column_name(s) • FROM table 1 FULL OUTER JOIN table 2 ON table 1. column_name=table 2. column_name; -----------------------------------------SELECT Students. Name , Students. Family, Students. Code, Professors. Name, Professors. Family, Professors. Code FROM Students full JOIN Professors ON Students. Professor_ID=Professors. ID

Full Join ﻧﺘیﺠﻪ