آموزش برخی از دستورات SQL Server به همراه مثال ساده

1. CREATE DATABASE:
   CREATE DATABASE MyDatabase;

2. CREATE TABLE:
   CREATE TABLE Employees (
       EmployeeID INT PRIMARY KEY,
       FirstName VARCHAR(50),
       LastName VARCHAR(50),
       DepartmentID INT
   );

3. ALTER TABLE:
   ALTER TABLE Employees
   ADD Salary DECIMAL(10, 2);

4. DROP TABLE:
   DROP TABLE Employees;

5. INSERT INTO:
   INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID)
   VALUES (1, 'John', 'Doe', 101);

6. UPDATE:
   UPDATE Employees
   SET Salary = 50000
   WHERE EmployeeID = 1;

7. DELETE FROM:
   DELETE FROM Employees
   WHERE EmployeeID = 1;

8. SELECT:
   SELECT * FROM Employees;

9. DISTINCT:
   SELECT DISTINCT DepartmentID
   FROM Employees;

10. WHERE:
    SELECT * FROM Employees
    WHERE DepartmentID = 101;

11. ORDER BY:
    SELECT * FROM Employees
    ORDER BY LastName;

12. GROUP BY:
    SELECT DepartmentID, COUNT(*)
    FROM Employees
    GROUP BY DepartmentID;

13. HAVING:
    SELECT DepartmentID, COUNT(*)
    FROM Employees
    GROUP BY DepartmentID
    HAVING COUNT(*) > 5;

14. INNER JOIN:
    SELECT e.FirstName, e.LastName, d.DepartmentName
    FROM Employees e
    INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

15. LEFT JOIN:
    SELECT e.FirstName, e.LastName, d.DepartmentName
    FROM Employees e
    LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID;

16. RIGHT JOIN:
    SELECT e.FirstName, e.LastName, d.DepartmentName
    FROM Employees e
    RIGHT JOIN Departments d ON e.DepartmentID = d.DepartmentID;

17. FULL JOIN:
    SELECT e.FirstName, e.LastName, d.DepartmentName
    FROM Employees e
    FULL JOIN Departments d ON e.DepartmentID = d.DepartmentID;

18. UNION:
    SELECT FirstName, LastName
    FROM Employees
    UNION
    SELECT ManagerFirstName, ManagerLastName
    FROM Managers;

19. UNION ALL:
    SELECT FirstName, LastName
    FROM Employees
    UNION ALL
    SELECT ManagerFirstName, ManagerLastName
    FROM Managers;

20. EXISTS:
    SELECT *
    FROM Employees e
    WHERE EXISTS (SELECT * FROM Managers m WHERE e.EmployeeID = m.EmployeeID);

21. NOT EXISTS:
    SELECT *
    FROM Employees e
    WHERE NOT EXISTS (SELECT * FROM Managers m WHERE e.EmployeeID = m.EmployeeID);

22. IN:
    SELECT *
    FROM Employees
    WHERE DepartmentID IN (101, 102);

23. NOT IN:
    SELECT *
    FROM Employees
    WHERE DepartmentID NOT IN (101, 102);

24. BETWEEN:
    SELECT *
    FROM Orders
    WHERE OrderDate BETWEEN '2022-01-01' AND '2022-01-31';

25. LIKE:
    SELECT *
    FROM Products
    WHERE ProductName LIKE 'App%';

26. IS NULL:
    SELECT *
    FROM Customers
    WHERE Email IS NULL;

27. IS NOT NULL:
    SELECT *
    FROM Customers
    WHERE Email IS NOT NULL;

28. AVG():
    SELECT AVG(Salary)
    FROM Employees;

29. SUM():
    SELECT SUM(Salary)
    FROM Employees;

30. COUNT():
    SELECT COUNT(*)
    FROM Employees;

31. MAX():
    SELECT MAX(Salary)
    FROM Employees;

32. MIN():
    SELECT MIN(Salary)
    FROM Employees;

33. GROUP_CONCAT():
    SELECT DepartmentID, GROUP_CONCAT(LastName)
    FROM Employees
    GROUP BY DepartmentID;

34. CASE WHEN:
    SELECT FirstName,
           CASE WHEN Salary > 50000 THEN 'High'
                WHEN Salary > 30000 THEN 'Medium'
                ELSE 'Low'
           END AS SalaryLevel
    FROM Employees;

35. DATEPART():
    SELECT DATEPART(YEAR, OrderDate) AS OrderYear,
           DATEPART(MONTH, OrderDate) AS OrderMonth,
           COUNT(*) AS TotalOrders
    FROM Orders
    GROUP BY DATEPART(YEAR, OrderDate), DATEPART(MONTH, OrderDate);

36. DATEDIFF():
    SELECT DATEDIFF(DAY, OrderDate, ShippedDate) AS DaysToShip
    FROM Orders;

37. DATEADD():
    SELECT DATEADD(MONTH, 3, HireDate) AS ProbationEndDate
    FROM Employees;

38. UPPER():
    SELECT UPPER(LastName)
    FROM Employees;

39. LOWER():
    SELECT LOWER(LastName)
    FROM Employees;

40. LEFT():
    SELECT LEFT(FirstName, 3)
    FROM Employees;

41. RIGHT():
   SELECT RIGHT(LastName, 3)
   FROM Employees;

42. LEN():
   SELECT LEN(FirstName) AS FirstNameLength
   FROM Employees;

43. ROUND():
   SELECT ROUND(Salary, 2) AS RoundedSalary
   FROM Employees;

44. CAST():
   SELECT CAST(Salary AS INT) AS RoundedSalary
   FROM Employees;

45. CONVERT():
   SELECT CONVERT(VARCHAR(10), HireDate, 101) AS FormattedHireDate
   FROM Employees;

46. COALESCE():
   SELECT COALESCE(ManagerFirstName, 'No Manager') AS ManagerName
   FROM Employees;

47. NULLIF():
   SELECT NULLIF(ManagerFirstName, '') AS ManagerName
   FROM Employees;

48. TOP():
   SELECT TOP 10 *
   FROM Products;

49. ROW_NUMBER():
   SELECT ROW_NUMBER() OVER (ORDER BY LastName) AS RowNumber,
          FirstName,
          LastName
   FROM Employees;

50. RANK():
   SELECT RANK() OVER (ORDER BY Salary DESC) AS Rank,
          FirstName,
          LastName,
          Salary
   FROM Employees;

51. DENSE_RANK():
   SELECT DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank,
          FirstName,
          LastName,
          Salary
   FROM Employees;

52. NTILE():
   SELECT NTILE(4) OVER (ORDER BY Salary DESC) AS Quartile,
          FirstName,
          LastName,
          Salary
   FROM Employees;

53. CTE (Common Table Expression):
   WITH EmployeeCTE AS (
       SELECT EmployeeID, FirstName, LastName, Salary,
              ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNumber
       FROM Employees
   )
   SELECT EmployeeID, FirstName, LastName, Salary
   FROM EmployeeCTE
   WHERE RowNumber <= 10;

54. INDEXES:
   CREATE INDEX IX_DepartmentID ON Employees(DepartmentID);

55. TRIGGERS:
   CREATE TRIGGER trgAfterInsertEmployee
   ON Employees
   AFTER INSERT
   AS
   BEGIN
       -- Trigger logic here
   END;

56. STORED PROCEDURES:
   CREATE PROCEDURE spGetEmployeeByID @EmployeeID INT
   AS
   BEGIN
       -- Procedure logic here
   END;

57. VIEWS:
   CREATE VIEW vwHighSalaryEmployees AS
       SELECT FirstName, LastName, Salary
       FROM Employees
       WHERE Salary > 50000;

58. TRANSACTIONS:
   BEGIN TRANSACTION;
   -- SQL statements here

59. JOINS with USING clause:
     SELECT e.FirstName, e.LastName, d.DepartmentName
     FROM Employees e
     JOIN Departments d USING (DepartmentID);

60. GRANT PERMISSIONS:
     GRANT SELECT ON Employees TO user1;

سارتر چه بر سر جایزۀ نوبل آورد...