100 SQL Commands for Mastering SQL

Database

100 SQL Commands for Mastering SQL

by Aditya Pratap BhuyanLeave a Comment on 100 SQL Commands for Mastering SQL
100 SQL Commands

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

  1. Create Table: 
    CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);
  2. Drop Table: 
    DROP TABLE users;
  3. Alter Table: 
    ALTER TABLE users ADD COLUMN age INT;
  4. Rename Table: 
    ALTER TABLE users RENAME TO customers;
  5. Add Column: 
    ALTER TABLE users ADD COLUMN address VARCHAR(255);
  6. Drop Column: 
    ALTER TABLE users DROP COLUMN address;
  7. Modify Column: 
    ALTER TABLE users MODIFY COLUMN name VARCHAR(150);

Basic Data Operations

  1. Insert Data: 
    INSERT INTO users (id, name, email) VALUES (1, 'John Doe', 'john@example.com');
  2. Update Data: 
    UPDATE users SET email = 'john.doe@example.com' WHERE id = 1;
  3. Delete Data: 
    DELETE FROM users WHERE id = 1;
  4. Select Data: 
    SELECT * FROM users;
  5. Select Specific Columns: 
    SELECT name, email FROM users;

Advanced Data Retrieval

  1. Where Clause: 
    SELECT * FROM users WHERE age > 30;
  2. Order By: 
    SELECT * FROM users ORDER BY name ASC;
  3. Group By: 
    SELECT COUNT(*), age FROM users GROUP BY age;
  4. Having Clause: 
    SELECT age, COUNT(*) FROM users GROUP BY age HAVING COUNT(*) > 1;
  5. Limit Clause: 
    SELECT * FROM users LIMIT 10;
  6. Distinct: 
    SELECT DISTINCT age FROM users;

Joins

  1. Inner Join: 
    SELECT users.name, orders.order_id 
FROM users 
INNER JOIN orders ON users.id = orders.user_id;
  2. Left Join: 
    SELECT users.name, orders.order_id 
FROM users 
LEFT JOIN orders ON users.id = orders.user_id;
  3. Right Join: 
    SELECT users.name, orders.order_id 
FROM users 
RIGHT JOIN orders ON users.id = orders.user_id;
  4. Full Join: 
    SELECT users.name, orders.order_id 
FROM users 
FULL OUTER JOIN orders ON users.id = orders.user_id;

Indexes and Constraints

  1. Create Index: 
    CREATE INDEX idx_users_name ON users(name);
  2. Drop Index: 
    DROP INDEX idx_users_name;
  3. Unique Constraint: 
    ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);
  4. Foreign Key Constraint: 
    ALTER TABLE orders ADD CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users(id);

Aggregate Functions

  1. Count: 
    SELECT COUNT(*) FROM users;
  2. Sum: 
    SELECT SUM(age) FROM users;
  3. Average: 
    SELECT AVG(age) FROM users;
  4. Max: 
    SELECT MAX(age) FROM users;
  5. Min: 
    SELECT MIN(age) FROM users;

Subqueries

  1. Subquery in SELECT: 
    SELECT name, (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count 
FROM users;
  2. Subquery in WHERE: 
    SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

Transactions

  1. Start Transaction: 
    START TRANSACTION;
  2. Commit Transaction: 
    COMMIT;
  3. Rollback Transaction: 
    ROLLBACK;

Views

  1. Create View: 
    CREATE VIEW user_orders AS 
SELECT users.name, orders.order_id 
FROM users 
JOIN orders ON users.id = orders.user_id;
  2. Drop View: 
    DROP VIEW user_orders;

Advanced SQL Commands

  1. 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;
  2. Union: 
    SELECT name FROM users 
UNION 
SELECT name FROM customers;
  3. Union All: 
    SELECT name FROM users 
UNION ALL 
SELECT name FROM customers;

JSON Functions (for databases supporting JSON)

  1. Insert JSON Data: 
    INSERT INTO user_data (data) VALUES ('{"name": "John", "age": 30}');
  2. Select JSON Data: 
    SELECT data->>'name' AS name FROM user_data;
  3. Update JSON Data: 
    UPDATE user_data SET data = jsonb_set(data, '{age}', '31') WHERE id = 1;

Window Functions

  1. Row Number: 
    SELECT name, age, ROW_NUMBER() OVER (PARTITION BY age ORDER BY name) AS row_num 
FROM users;
  2. Rank: 
    SELECT name, age, RANK() OVER (ORDER BY age DESC) AS rank 
FROM users;
  3. Dense Rank: 
    SELECT name, age, DENSE_RANK() OVER (ORDER BY age DESC) AS dense_rank 
FROM users;

String Functions

  1. Concatenate: 
    SELECT CONCAT(first_name, ' ', last_name) AS full_name 
FROM users;
  2. Substring: 
    SELECT SUBSTRING(name, 1, 3) AS short_name 
FROM users;
  3. Length: 
    SELECT LENGTH(name) AS name_length 
FROM users;
  4. Upper: 
    SELECT UPPER(name) AS uppercase_name 
FROM users;
  5. Lower: 
    SELECT LOWER(name) AS lowercase_name 
FROM users;

Date and Time Functions

  1. Current Date: 
    SELECT CURRENT_DATE;
  2. Current Time: 
    SELECT CURRENT_TIME;
  3. Current Timestamp: 
    SELECT CURRENT_TIMESTAMP;
  4. Extract Year: 
    SELECT EXTRACT(YEAR FROM birth_date) AS birth_year 
FROM users;
  5. Date Add: 
    SELECT birth_date + INTERVAL '1 year' AS next_birth_date 
FROM users;

User and Role Management

  1. Create User: 
    CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';
  2. Drop User: 
    DROP USER 'new_user'@'localhost';
  3. Grant Privileges: 
    GRANT ALL PRIVILEGES ON my_database.* TO 'new_user'@'localhost';
  4. Revoke Privileges: 
    REVOKE ALL PRIVILEGES ON my_database.* FROM 'new_user'@'localhost';
  5. Create Role: 
    CREATE ROLE manager;
  6. Grant Role to User: 
    GRANT manager TO 'new_user'@'localhost';

Backup and Restore

  1. Backup Database: 
    BACKUP DATABASE my_database TO DISK = 'backup_file.bak';
  2. Restore Database: 
    RESTORE DATABASE my_database FROM DISK = 'backup_file.bak';

Analytical Functions

  1. Cume Dist: 
    SELECT name, age, CUME_DIST() OVER (ORDER BY age) AS cume_dist 
FROM users;
  2. Lag: 
    SELECT name, age, LAG(age, 1) OVER (ORDER BY age) AS previous_age 
FROM users;
  3. Lead: 
    SELECT name, age, LEAD(age, 1) OVER (ORDER BY age) AS next_age 
FROM users;
  4. Ntile: 
    SELECT name, age, NTILE(4) OVER (ORDER BY age) AS quartile 
FROM users;

Data Integrity and Validation

  1. Check Constraint: 
    ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 0);
  2. Default Value: 
    ALTER TABLE users ALTER COLUMN age SET DEFAULT 0;
  3. Not Null Constraint: 
    ALTER TABLE users ALTER COLUMN name SET NOT NULL;

Security

  1. Create Procedure: 
    CREATE PROCEDURE my_procedure()
BEGIN
    SELECT * FROM users;
END;
  2. Drop Procedure: 
    DROP PROCEDURE my_procedure;
  3. Create Function: 
    CREATE FUNCTION get_user_email(user_id INT) RETURNS VARCHAR(100)
BEGIN
    RETURN (SELECT email FROM users WHERE id = user_id);
END;
  4. Drop Function: 
    DROP FUNCTION get_user_email;

Data Export and Import

  1. Export to CSV: 
    SELECT * INTO OUTFILE '/tmp/users.csv' 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
FROM users;
  2. Import from CSV: 
    LOAD DATA INFILE '/tmp/users.csv' 
INTO TABLE users 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

Error Handling

  1. Try-Catch: 
    BEGIN TRY
    -- SQL statements
END TRY
BEGIN CATCH
    -- Handle error
END CATCH;

Miscellaneous

  1. Rename Column: 
    ALTER TABLE users RENAME COLUMN name TO full_name;
  2. Case Conversion: 
    SELECT UPPER(full_name) AS upper_name FROM users;
  3. Remove Duplicates: 
    DELETE FROM users 
WHERE id NOT IN (SELECT MIN(id) 
                 FROM users 
                 GROUP BY name, email);
  4. IF Statement: 
    IF (SELECT COUNT(*) FROM users) > 100
BEGIN
    PRINT 'More than 100 users';
END;
  5. Convert Data Type: 
    SELECT CONVERT(VARCHAR, age) AS age_str FROM users;
  6. Coalesce: 
    SELECT COALESCE(middle_name, 'N/A') AS middle_name FROM users;
  7. Is Null: 
    SELECT * FROM users WHERE middle_name IS NULL;
  8. Is Not Null: 
    SELECT * FROM users WHERE middle_name IS NOT NULL;

XML Functions (for databases supporting XML)

  1. Insert XML Data: 
    INSERT INTO user_data (data) VALUES ('<user><name>John</name><age>30</age></user>');
  2. Select XML Data: 
    SELECT data.value('(/user/name)[1]', 'VARCHAR(100)') AS name FROM user_data;
  3. Update XML Data: 
    UPDATE user_data 
SET data.modify('replace value of (/user/age)[1] with 31') 
WHERE id = 1;

Full-Text Search

  1. Create Full-Text Index: 
    CREATE FULLTEXT INDEX ft_idx ON users(name, email);
  2. Full-Text Search: 
    SELECT * FROM users WHERE MATCH(name, email) AGAINST('search_term');

Temporary Tables

  1. Create Temporary Table: 
    CREATE TEMPORARY TABLE temp_users (
    id INT,
    name VARCHAR(100)
);
  2. Drop Temporary Table: 
    DROP TEMPORARY TABLE temp_users;

Sequences

  1. Create Sequence: 
    CREATE SEQUENCE user_seq START WITH 1 INCREMENT BY 1;
  2. Next Value of Sequence: 
    SELECT NEXT VALUE FOR user_seq;
  3. 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.

facebookShare on Facebook
TwitterPost on X
FollowFollow us
PinterestSave
Aditya: Cloud Native Specialist, Consultant, and Architect Aditya is a seasoned professional in the realm of cloud computing, specializing as a cloud native specialist, consultant, architect, SRE specialist, cloud engineer, and developer. With over two decades of experience in the IT sector, Aditya has established themselves as a proficient Java developer, J2EE architect, scrum master, and instructor. His career spans various roles across software development, architecture, and cloud technology, contributing significantly to the evolution of modern IT landscapes. Based in Bangalore, India, Aditya has cultivated a deep expertise in guiding clients through transformative journeys from legacy systems to contemporary microservices architectures. He has successfully led initiatives on prominent cloud computing platforms such as AWS, Google Cloud Platform (GCP), Microsoft Azure, and VMware Tanzu. Additionally, Aditya possesses a strong command over orchestration systems like Docker Swarm and Kubernetes, pivotal in orchestrating scalable and efficient cloud-native solutions. Aditya's professional journey is underscored by a passion for cloud technologies and a commitment to delivering high-impact solutions. He has authored numerous articles and insights on Cloud Native and Cloud computing, contributing thought leadership to the industry. His writings reflect a deep understanding of cloud architecture, best practices, and emerging trends shaping the future of IT infrastructure. Beyond his technical acumen, Aditya places a strong emphasis on personal well-being, regularly engaging in yoga and meditation to maintain physical and mental fitness. This holistic approach not only supports his professional endeavors but also enriches his leadership and mentorship roles within the IT community. Aditya's career is defined by a relentless pursuit of excellence in cloud-native transformation, backed by extensive hands-on experience and a continuous quest for knowledge. His insights into cloud architecture, coupled with a pragmatic approach to solving complex challenges, make them a trusted advisor and a sought-after consultant in the field of cloud computing and software architecture.
Website https://cloudnativeblogs.in

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts

Back To Top