SQL Commands

By | March 16, 2022
SQL Commands

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

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;

Vamware
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();

Vamware

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:

Author: Vinay Singh

I am a Full Stack Developer with a Bachelor's Degree in Computer Science, who also loves to write technical articles that can help fellow developers.

Leave a Reply

Your email address will not be published.