SQL Commands Lists with Examples [DDL, DQL, DML, DCL and TCL Commands]

Posted in /  

SQL Commands Lists with Examples [DDL, DQL, DML, DCL and TCL Commands]
vinaykhatri

Vinay Khatri
Last updated on December 3, 2024

    SQL is a very powerful and easy-to-use Database Management System, which helps the developer to create and manage a Database. There are many commands present in SQL. These commands are usually termed as queries. In tech-related interviews, you might face direct questions related to SQL, or they can ask you to write a query to perform an action on the database. Here in this article, we have provided the top SQL commands.

    Basic SQL Commands List

    Here is a manifest database on which we will perform various queries to have a better understanding of SQL commands.

    Table Name customers

    Id FirstName LastName Mobile
    1 Will Drove 7878787878
    2 David Kin 1212121212
    3 John Adams 2323232323
    4 Will Smith 3434343434
    5 James Robert 4545454545
    6 Mark Uzumaki 5656565656
    7 Sofi Ketchum 6767676767

    Table Name employee

    Id FirstName LastName Salary
    1 Will Drove 100000
    2 David Kin 90000
    3 John Adams 300000
    4 Will Smith 500000
    5 James Robert 20000
    6 Mark Uzumaki 400000
    7 Sofi Ketchum 210000

    1. Write a command to show all the tables present in the selected database

    SHOW TABLES;
    customers
    employee

    2. Write a Query to display the information about the columns of the Customers table.

    SHOW COLUMNS FROM customers;
    Field Type Null Key
    ID Int(11) No PRI
    FirstName varchar(20) No
    LastName Varchar(30) No
    Mobile int(20) NO

    3. Write a Query to display only the first name of all the customers

    SELECT FirstName FROM customers;
    FirstName
    Will
    David
    John
    Will
    James
    Mark
    Sofi

    4. Write a query to display the first and the last name of all the customers.

    SELECT FirstName, LastName
    FROM customers;
    FirstName LastName
    Will Drove
    David Kin
    John Adams
    Will Smith
    James Robert
    Mark Uzumaki
    Sofi Ketchum

    5. Write a Query to display the complete customer table.

    SELECT *FROM customers;

    6. Write a SQL command that eliminates the duplicate record and only unique ones.

    SELECT DISTINCT FirstName
    FROM customers;
    FirstName
    Will
    David
    John
    James
    Mark
    Sofi

    7. Write a query to display the first 2 records of the customer's table.

    SELECT *
    FROM customers LIMIT 2;
    Id FirstName LastName Mobile
    1 Will Drove 7878787878
    2 David Kin 1212121212

    8. Write a SQL query to sort the customer's tables by their first name;

    SELECT * FROM customer
    ORDER BY FirstName;
    Id FirstName LastName Mobile
    2 David Kin 1212121212
    5 James Robert 4545454545
    3 John Adams 2323232323
    6 Mark Uzumaki 5656565656
    7 Sofi Ketchum 6767676767
    1 Will Drove 7878787878
    4 Will Smith 3434343434

    9. Write a query to select the customer with id 3.

    SELECT * FROM customers
    WHERE ID=3;
    Id FirstName LastName Mobile
    3 John Adams 2323232323

    10. Write a query to select and display all those customers with an id greater than 3.

    SELECT * FROM customers
    WHERE ID > 3;

    11. Write a query to select those customers whose id number is between 2 to 4 (include):

    SELECT * FROM customers
    WHERE ID BETWEEN 2 AND 4;
    Id FirstName LastName Mobile
    2 David Kin 1212121212
    3 John Adams 2323232323
    4 Will Smith 3434343434

    12. Write a query to select those customers whose name is will.

    SELECT *FROM customers
    WHERE FirstName = 'Will';
    Id FirstName LastName Mobile
    1 Will Drove 7878787878
    4 Will Smith 3434343434

    13. Write a query to select those customers whose id number is greater than 1 and less than 4.

    SELECT *
    FROM customers
    WHERE Id > 1 AND Id <= 4;
    2 David Kin 1212121212
    3 John Adams 2323232323

    14. Write a query to join the FirstName and the LastName with ‘-‘.

    SELECT CONCAT(FirstName, '- ' , LastName) FROM customers;
    CONCAT(FirstName, '- ' , LastName)
    Will-Drove
    David-Kin
    John-Adams
    Will-Smith
    James-Robert
    Mark-Uzumaki
    Sofi-Ketchum

    15. Write a query to display all the LastName from the table customers in uppercase.

    SELECT UPPER(FirstName) AS FIRST
    FROM employees;
    FIRST
    WILL
    DAVID
    JOHN
    WILL
    JAMES
    MARK
    SOFI

    16. Write a query to display the average salary of all employees from Employee table.

    SELECT AVG(Salary) FROM employee;

    17. Write a query to select those employees whose First Name starts with J from.

    SELECT * FROM employee
    WHERE FirstName LIKE 'J%'; 

    18. Write a query to select those employees whose Last Name starts with h from.

    SELECT * FROM employee
    WHERE FirstName LIKE '%h';

    19. Write a query to select the minimum salary from the employee Table.

    SELECT MIN(Salary) AS SALARY FROM employee;

    20. Write a query to remove a row from the table employee where FirstName is will.

    DELETE FROM employee
    WHERE FirstName = ‘will’;

    21. Write a query to select those customers whose id is equal to employee id:

    SELECT *
    FROM customers, employee
    WHERE customers.ID=employee.ID
    ORDER BY customers.ID;

    22. Write a Query to create a View for Firstname and Salary of Employee table

    CREATE VIEW Employee_Sal AS
    SELECT FirstName, Salary
    FROM Employee;

    23. Write a Query to select those employees whose names contain 4 letters and the end letter is y

    Select * from employee where FirstName like '___h';

    24. Write a Query to select those employees whose salary is lies between 80000 to 500000.

    Select * from employee where Salary between 80000 and 500000;

    25. Write an SQL query to count an employee having the first name Will.

    SELECT COUNT(*) FROM employee
    Where FirstName= 'Will';

    26. Write an SQL query to show those records which Customer table has but not Employee

    SELECT * FROM Customers
    MINUS
    SELECT * FROM Employee;

    27. Write a Query that shows the current date

    SELECT CURDATE();

    28. Write a query that shows the current date as well as time/

    SELECT NOW();

    29. Write a Query to show the list of those employees having the same salary.

    SELECT DISTINCT e1.id, e1.FirstName, e1.salary
    FROM employee e1, employee e2
    WHERE e1.Salary = e2.Salary
    AND e1.id !=e2.id;

    30. Write a query to select the second highest salary from the employee table

    SELECT MAX(Salary) FROM employee
    WHERE Salary NOT IN (SELECT MAX(Salary) FROM employee);

    31. Write a Query to show all the common Information of Employee and Customers table

    (SELECT * FROM Employee)
    INTERSECT
    (SELECT * FROM Customers);

    32. Write a query to show only 50 % of the information of the entire customer table

    SELECT * FROM Customer WHERE LIMIT= (SELECT count(customers.id)/2)

    Conclusion

    Here we reach the end of the SQL commands. We have taken one sample database consisting of two tables, namely Customers and Employee. And we have performed various queries on these two tables to make you understand how SQL commands work.

    People are also reading:

    Leave a Comment on this Post

    0 Comments