100 SQL Commands for Mastering SQL

Database Creation and Management

1. Create Database:

   CREATE DATABASE my_database;

2. Drop Database:

   DROP DATABASE my_database;

3. Select Database:

   USE my_database;

Table Creation and Management

4. Create Table:

   CREATE TABLE users (
       id INT PRIMARY KEY,
       name VARCHAR(100),
       email VARCHAR(100)
   );

5. Drop Table:

   DROP TABLE users;

6. Alter Table:

   ALTER TABLE users ADD COLUMN age INT;

7. Rename Table:

   ALTER TABLE users RENAME TO customers;

8. Add Column:

   ALTER TABLE users ADD COLUMN address VARCHAR(255);

9. Drop Column:

   ALTER TABLE users DROP COLUMN address;

10. Modify Column:

    ALTER TABLE users MODIFY COLUMN name VARCHAR(150);

Basic Data Operations

11. Insert Data:

    INSERT INTO users (id, name, email) VALUES (1, 'John Doe', 'john@example.com');

12. Update Data:

    UPDATE users SET email = 'john.doe@example.com' WHERE id = 1;

13. Delete Data:

    DELETE FROM users WHERE id = 1;

14. Select Data:

    SELECT * FROM users;

15. Select Specific Columns:

    SELECT name, email FROM users;

Advanced Data Retrieval

16. Where Clause:

    SELECT * FROM users WHERE age > 30;

17. Order By:

    SELECT * FROM users ORDER BY name ASC;

18. Group By:

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

19. Having Clause:

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

20. Limit Clause:

    SELECT * FROM users LIMIT 10;

21. Distinct:

    SELECT DISTINCT age FROM users;

Joins

22. Inner Join:

    SELECT users.name, orders.order_id 
    FROM users 
    INNER JOIN orders ON users.id = orders.user_id;

23. Left Join:

    SELECT users.name, orders.order_id 
    FROM users 
    LEFT JOIN orders ON users.id = orders.user_id;

24. Right Join:

    SELECT users.name, orders.order_id 
    FROM users 
    RIGHT JOIN orders ON users.id = orders.user_id;

25. Full Join:

    SELECT users.name, orders.order_id 
    FROM users 
    FULL OUTER JOIN orders ON users.id = orders.user_id;

Indexes and Constraints

26. Create Index:

    CREATE INDEX idx_users_name ON users(name);

27. Drop Index:

    DROP INDEX idx_users_name;

28. Unique Constraint:

    ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

29. Foreign Key Constraint:

    ALTER TABLE orders ADD CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users(id);

Aggregate Functions

30. Count:

    SELECT COUNT(*) FROM users;

31. Sum:

    SELECT SUM(age) FROM users;

32. Average:

    SELECT AVG(age) FROM users;

33. Max:

    SELECT MAX(age) FROM users;

34. Min:

    SELECT MIN(age) FROM users;

Subqueries

35. Subquery in SELECT:

    SELECT name, (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count 
    FROM users;

36. Subquery in WHERE:

    SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

Transactions

37. Start Transaction:

    START TRANSACTION;

38. Commit Transaction:

    COMMIT;

39. Rollback Transaction:

    ROLLBACK;

Views

40. Create View:

    CREATE VIEW user_orders AS 
    SELECT users.name, orders.order_id 
    FROM users 
    JOIN orders ON users.id = orders.user_id;

41. Drop View:

    DROP VIEW user_orders;

Advanced SQL Commands

42. Case Statement:

    SELECT name, 
           CASE 
               WHEN age < 18 THEN 'Minor'
               WHEN age BETWEEN 18 AND 65 THEN 'Adult'
               ELSE 'Senior'
           END AS age_group
    FROM users;

43. Union:

    SELECT name FROM users 
    UNION 
    SELECT name FROM customers;

44. Union All:

    SELECT name FROM users 
    UNION ALL 
    SELECT name FROM customers;

JSON Functions (for databases supporting JSON)

45. Insert JSON Data:

    INSERT INTO user_data (data) VALUES ('{"name": "John", "age": 30}');

46. Select JSON Data:

    SELECT data->>'name' AS name FROM user_data;

47. Update JSON Data:

    UPDATE user_data SET data = jsonb_set(data, '{age}', '31') WHERE id = 1;

Window Functions

48. Row Number:

    SELECT name, age, ROW_NUMBER() OVER (PARTITION BY age ORDER BY name) AS row_num 
    FROM users;

49. Rank:

    SELECT name, age, RANK() OVER (ORDER BY age DESC) AS rank 
    FROM users;

50. Dense Rank:

    SELECT name, age, DENSE_RANK() OVER (ORDER BY age DESC) AS dense_rank 
    FROM users;

String Functions

51. Concatenate:

    SELECT CONCAT(first_name, ' ', last_name) AS full_name 
    FROM users;

52. Substring:

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

53. Length:

    SELECT LENGTH(name) AS name_length 
    FROM users;

54. Upper:

    SELECT UPPER(name) AS uppercase_name 
    FROM users;

55. Lower:

    SELECT LOWER(name) AS lowercase_name 
    FROM users;

Date and Time Functions

56. Current Date:

    SELECT CURRENT_DATE;

57. Current Time:

    SELECT CURRENT_TIME;

58. Current Timestamp:

    SELECT CURRENT_TIMESTAMP;

59. Extract Year:

    SELECT EXTRACT(YEAR FROM birth_date) AS birth_year 
    FROM users;

60. Date Add:

    SELECT birth_date + INTERVAL '1 year' AS next_birth_date 
    FROM users;

User and Role Management

61. Create User:

    CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';

62. Drop User:

    DROP USER 'new_user'@'localhost';

63. Grant Privileges:

    GRANT ALL PRIVILEGES ON my_database.* TO 'new_user'@'localhost';

64. Revoke Privileges:

    REVOKE ALL PRIVILEGES ON my_database.* FROM 'new_user'@'localhost';

65. Create Role:

    CREATE ROLE manager;

66. Grant Role to User:

    GRANT manager TO 'new_user'@'localhost';

Backup and Restore

67. Backup Database:

    BACKUP DATABASE my_database TO DISK = 'backup_file.bak';

68. Restore Database:

    RESTORE DATABASE my_database FROM DISK = 'backup_file.bak';

Analytical Functions

69. Cume Dist:

    SELECT name, age, CUME_DIST() OVER (ORDER BY age) AS cume_dist 
    FROM users;

70. Lag:

    SELECT name, age, LAG(age, 1) OVER (ORDER BY age) AS previous_age 
    FROM users;

71. Lead:

    SELECT name, age, LEAD(age, 1) OVER (ORDER BY age) AS next_age 
    FROM users;

72. Ntile:

    SELECT name, age, NTILE(4) OVER (ORDER BY age) AS quartile 
    FROM users;

Data Integrity and Validation

73. Check Constraint:

    ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 0);

74. Default Value:

    ALTER TABLE users ALTER COLUMN age SET DEFAULT 0;

75. Not Null Constraint:

    ALTER TABLE users ALTER COLUMN name SET NOT NULL;

Security

76. Create Procedure:

    CREATE PROCEDURE my_procedure()
    BEGIN
        SELECT * FROM users;
    END;

77. Drop Procedure:

    DROP PROCEDURE my_procedure;

78. Create Function:

    CREATE FUNCTION get_user_email(user_id INT) RETURNS VARCHAR(100)
    BEGIN
        RETURN (SELECT email FROM users WHERE id = user_id);
    END;

79. Drop Function:

    DROP FUNCTION get_user_email;

Data Export and Import

80. Export to CSV:

    SELECT * INTO OUTFILE '/tmp/users.csv' 
    FIELDS TERMINATED BY ',' 
    OPTIONALLY ENCLOSED BY '"' 
    LINES TERMINATED BY '\n' 
    FROM users;

81. Import from CSV:

    LOAD DATA INFILE '/tmp/users.csv' 
    INTO TABLE users 
    FIELDS TERMINATED BY ',' 
    OPTIONALLY ENCLOSED BY '"' 
    LINES TERMINATED BY '\n';

Error Handling

82. Try-Catch:

    BEGIN TRY
        -- SQL statements
    END TRY
    BEGIN CATCH
        -- Handle error
    END CATCH;

Miscellaneous

83. Rename Column:

    ALTER TABLE users RENAME COLUMN name TO full_name;

84. Case Conversion:

    SELECT UPPER(full_name) AS upper_name FROM users;

85. Remove Duplicates:

    DELETE FROM users 
    WHERE id NOT IN (SELECT MIN(id) 
                     FROM users 
                     GROUP BY name, email);

86. IF Statement:

    IF (SELECT COUNT(*) FROM users) > 100
    BEGIN
        PRINT 'More than 100 users';
    END;

87. Convert Data Type:

    SELECT CONVERT(VARCHAR, age) AS age_str FROM users;

88. Coalesce:

    SELECT COALESCE(middle_name, 'N/A') AS middle_name FROM users;

89. Is Null:

    SELECT * FROM users WHERE middle_name IS NULL;

90. Is Not Null:

    SELECT * FROM users WHERE middle_name IS NOT NULL;

XML Functions (for databases supporting XML)

91. Insert XML Data:

    INSERT INTO user_data (data) VALUES ('<user><name>John</name><age>30</age></user>');

92. Select XML Data:

    SELECT data.value('(/user/name)[1]', 'VARCHAR(100)') AS name FROM user_data;

93. Update XML Data:

    UPDATE user_data 
    SET data.modify('replace value of (/user/age)[1] with 31') 
    WHERE id = 1;

Full-Text Search

94. Create Full-Text Index:

    CREATE FULLTEXT INDEX ft_idx ON users(name, email);

95. Full-Text Search:

    SELECT * FROM users WHERE MATCH(name, email) AGAINST('search_term');

Temporary Tables

96. Create Temporary Table:

    CREATE TEMPORARY TABLE temp_users (
        id INT,
        name VARCHAR(100)
    );

97. Drop Temporary Table:

    DROP TEMPORARY TABLE temp_users;

Sequences

98. Create Sequence:

    CREATE SEQUENCE user_seq START WITH 1 INCREMENT BY 1;

99. Next Value of Sequence:

    SELECT NEXT VALUE FOR user_seq;

100. Drop Sequence:

     DROP SEQUENCE user_seq;

Mastering these 100 SQL commands will give you a strong foundation in SQL, enabling you to manage and manipulate databases effectively.

Share on Facebook

Post on X

Follow us

Save