1. Connect to SQL Server

sqlcmd -S localhost -U sa -P password

2. Create a Database

CREATE DATABASE mydatabase;

3. Use a Database

USE mydatabase;

4. Create a Table

CREATE TABLE mytable (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  age INT
);

5. Insert Data into a Table

INSERT INTO mytable (name, age) VALUES ('John Doe', 30);

6. Select Data from a Table

SELECT * FROM mytable;

7. Update Data in a Table

UPDATE mytable SET age = 31 WHERE name = 'John Doe';

8. Delete Data from a Table

DELETE FROM mytable WHERE name = 'John Doe';

9. Drop a Table

DROP TABLE mytable;

10. Drop a Database

DROP DATABASE mydatabase;

11. Show Databases

SELECT name FROM sys.databases;

12. Show Tables

SELECT name FROM sys.tables;

13. Describe a Table

EXEC sp_columns mytable;

14. Create an Index

CREATE INDEX idx_name ON mytable (name);

15. Drop an Index

DROP INDEX idx_name ON mytable;

16. Create a View

CREATE VIEW myview AS SELECT * FROM mytable;

17. Drop a View

DROP VIEW myview;

18. Create a Stored Procedure

CREATE PROCEDURE myproc
AS
BEGIN
  SELECT * FROM mytable;
END;

19. Call a Stored Procedure

EXEC myproc;

20. Drop a Stored Procedure

DROP PROCEDURE myproc;

21. Create a Function

CREATE FUNCTION myfunc (@x INT)
RETURNS INT
AS
BEGIN
  RETURN @x * 2;
END;

22. Call a Function

SELECT dbo.myfunc(5);

23. Drop a Function

DROP FUNCTION myfunc;

24. Create a Trigger

CREATE TRIGGER mytrigger
ON mytable
AFTER INSERT
AS
BEGIN
  PRINT 'Trigger fired!';
END;

25. Drop a Trigger

DROP TRIGGER mytrigger;

26. Create a User

CREATE USER myuser FOR LOGIN mylogin;

27. Grant Privileges to a User

GRANT SELECT ON mytable TO myuser;

28. Revoke Privileges from a User

REVOKE SELECT ON mytable FROM myuser;

29. Show User Privileges

SELECT * FROM sys.database_permissions;

30. Change User Password

ALTER LOGIN mylogin WITH PASSWORD = 'newpassword';

31. Drop a User

DROP USER myuser;

32. Show SQL Server Version

SELECT @@VERSION;

33. Show SQL Server Status

SELECT * FROM sys.dm_os_performance_counters;

34. Show SQL Server Variables

SELECT * FROM sys.configurations;

35. Set SQL Server Variable

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

36. Show SQL Server Logs
EXEC xp_readerrorlog;


37. Backup a Database

BACKUP DATABASE mydatabase TO DISK = 'C:\backup\mydatabase.bak';

38. Restore a Database

RESTORE DATABASE mydatabase FROM DISK = 'C:\backup\mydatabase.bak';

39. Import Data from CSV

BULK INSERT mytable FROM 'C:\data\data.csv' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n');

40. Export Data to CSV

EXEC xp_cmdshell 'bcp "SELECT * FROM mytable" queryout "C:\data\data.csv" -c -t, -T';

41. Use Transactions

BEGIN TRANSACTION;
UPDATE mytable SET age = 31 WHERE name = 'John Doe';
COMMIT;

42. Rollback a Transaction

ROLLBACK;

43. Create a Composite Key

ALTER TABLE mytable ADD CONSTRAINT PK_mytable PRIMARY KEY (name, age);

44. Create a Foreign Key

ALTER TABLE mytable ADD CONSTRAINT FK_user FOREIGN KEY (user_id) REFERENCES users(id);

45. Show Foreign Keys

SELECT * FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID('mytable');

46. Optimize a Table

ALTER INDEX ALL ON mytable REBUILD;

47. Analyze a Table

UPDATE STATISTICS mytable;

48. Check Table Status

DBCC CHECKTABLE('mytable');

49. Repair a Table

DBCC CHECKDB('mydatabase') WITH REPAIR_ALLOW_DATA_LOSS;

50. Show Table Size

EXEC sp_spaceused 'mytable';

51. Create a Temporary Table

CREATE TABLE #temp_table AS SELECT * FROM mytable;

52. Show Current Database

SELECT DB_NAME() AS CurrentDatabase;

53. Show Current User

SELECT USER_NAME() AS CurrentUser ;

54. Show Current Connection ID

SELECT @@SPID AS ConnectionID;

55. Set SQL Mode

EXEC sp_configure 'user options', 0; -- Example to set a specific option
RECONFIGURE;

56. Show SQL Modes

SELECT * FROM sys.dm_exec_sessions;

57. Create a Sequence

CREATE SEQUENCE my_sequence START WITH 1 INCREMENT BY 1;

58. Get Next Value from a Sequence

SELECT NEXT VALUE FOR my_sequence;

59. Drop a Sequence

DROP SEQUENCE my_sequence;

60. Create a User-Defined Variable

DECLARE @myvar INT = 10;

61. Use a User-Defined Variable

SELECT @myvar;

62. Create a Stored Function

CREATE FUNCTION dbo.add_numbers(@a INT, @b INT)
RETURNS INT
AS
BEGIN
  RETURN @a + @b;
END;

63. Call a Stored Function

SELECT dbo.add_numbers(5, 10);

64. Create a Cursor

DECLARE my_cursor CURSOR FOR SELECT name FROM mytable;

65. Open a Cursor

OPEN my_cursor;

66. Fetch from a Cursor

FETCH NEXT FROM my_cursor INTO @name;

67. Close a Cursor

CLOSE my_cursor;

68. Create a View with Join

CREATE VIEW myview AS SELECT a.name, b.age FROM mytable a JOIN other_table b ON a.id = b.user_id;

69. Show View Definition

EXEC sp_helptext 'myview';

70. Refresh a Materialized View

CREATE OR ALTER VIEW myview AS SELECT * FROM mytable;

71. Create a Composite Index

CREATE INDEX idx_name_age ON mytable (name, age);

72. Show Indexes

SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('mytable');

73. Use LIMIT with OFFSET

SELECT * FROM mytable ORDER BY id OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY;

74. Use GROUP BY

SELECT age, COUNT(*) FROM mytable GROUP BY age;

75. Use HAVING Clause

SELECT age, COUNT(*) FROM mytable GROUP BY age HAVING COUNT(*) > 1;

76. Use UNION

SELECT name FROM mytable UNION SELECT name FROM other_table;

77. Use CASE Statement

SELECT name, 
       CASE 
           WHEN age < 18 THEN 'Minor' 
           ELSE 'Adult' 
       END AS age_group 
FROM mytable;

78. Use COALESCE Function

SELECT COALESCE(name, 'Unknown') FROM mytable;

79. Use IIF Function

SELECT IIF(age > 18, 'Adult', 'Minor') AS age_group FROM mytable;

80. Use CONCAT Function

SELECT CONCAT(name, ' is ', age, ' years old') AS description FROM mytable;

81. Use SUBSTRING Function

SELECT SUBSTRING(name, 1, 3) AS short_name FROM mytable;

82. Use LEN Function

SELECT LEN(name) AS name_length FROM mytable;

83. Use LTRIM and RTRIM Functions

SELECT LTRIM(RTRIM(name)) AS trimmed_name FROM mytable;

84. Use UPPER and LOWER Functions

SELECT UPPER(name) AS upper_name, LOWER(name) AS lower_name FROM mytable;

85. Use GETDATE Function

SELECT GETDATE() AS current_datetime;

86. Use FORMAT Function

SELECT FORMAT(GETDATE(), 'yyyy-MM-dd') AS formatted_date;

87. Use DATEDIFF Function

SELECT DATEDIFF(DAY, '2023-01-01', GETDATE()) AS days_difference;

88. Use DATEADD Function

SELECT DATEADD(DAY, 7, GETDATE()) AS next_week;

89. Use EOMONTH Function

SELECT EOMONTH(GETDATE()) AS end_of_month;

90. Use STRING_AGG Function

SELECT STRING_AGG(name, ', ') AS all_names FROM mytable;

91. Use JSON Functions

SELECT JSON_QUERY('{"name": "' + name + '", "age": ' + CAST(age AS VARCHAR) + '}') AS json_data FROM mytable;

92. Use PATINDEX for Pattern Matching

SELECT * FROM mytable WHERE PATINDEX('%J%', name) > 0;

93. Use EXPLAIN for Query Analysis

SET SHOWPLAN_TEXT ON;
SELECT * FROM mytable WHERE age > 30;
SET SHOWPLAN_TEXT OFF;

94. Use sp_help for Table Structure

EXEC sp_help 'mytable';

95. Use ALTER TABLE to Modify Structure

ALTER TABLE mytable ADD email VARCHAR(255);

96. Use ALTER TABLE to Rename a Column

EXEC sp_rename 'mytable.name', 'full_name', 'COLUMN';

97. Use ALTER TABLE to Drop a Column

ALTER TABLE mytable DROP COLUMN email;

98. Use CREATE TABLE AS

SELECT * INTO new_table FROM mytable WHERE age > 30;

99. Use INSERT IGNORE

INSERT INTO mytable (name, age) VALUES ('John Doe', 30);

100. Use MERGE Statement

MERGE INTO mytable AS target
USING (SELECT 'John Doe' AS name, 30 AS age) AS source
ON target.name = source.name
WHEN MATCHED THEN
    UPDATE SET target.age = source.age
WHEN NOT MATCHED THEN
    INSERT (name, age) VALUES (source.name, source.age);