AspiringCoders

Quick Guide to 27+ SQL Commands and Queries

Last updated on

Structured Query Language abbreviated as SQL, is used to access and manipulate a database.

To get a data job in 2021, you need to have a strong grasp of SQL.

I know, you know this very well. Right?

It becomes easy for learners to have the complete list of SQL commands and queries in one place i.e. we can refer to it whenever required.

If you’re on the way to learning SQL and searching for a complete list for SQL commands and queries, here it is.

List of SQL Commands and Queries

1. Selecting a column

  • Most commonly used command to access the required data.
  • Syntax used: SELECT column_name FROM table_name;
Select age from customers;

2.Selecting Multiple Columns

  • Used to access multiple columns in the table.
  • Syntax used: SELECT column1, column2,… FROM table_name;
Select name, city, address from customers;

3. Selecting All Columns

  • Used to select all the columns at a time in the table.
  • Syntax used: SELECT * FROM table_name;
Select * from customers;

4. The DISTINCT Keyword

  • Used in conjuction with SELECT to eliminate all the duplicates and return only unique values.
  • Syntax used: SELECT DISTINCT column1, column2 FROM table_name;
Select DISTINCT city FROM customers;

5. The LIMIT Keyword

  • Used to retrive the limited subsets of records
  • Syntax used: SELECT column list FROM table_name LIMIT [number of records];
SELECT ID, FirstName, City FROM customers LIMIT 5;

6. The OFFSET Keyword

  • Used to pick up set of records from particular offset
  • Syntax used: SELECT column list FROM table_name OFFSET [starts from] LIMIT [number of records];
SELECT ID, FirstName, LastName, City FROM customers OFFSET 3 LIMIT 4;

7. Fully Qualified Name

  • You can provide table name prior to the column name by separating them with a dot.
  • Syntax used: SELECT table_name.column_name FROM table_name;
SELECT customers.City FROM customers;

8. Order By

  • Used with SELECT to sort the returned data.
  • Syntax used: SELECT * FROM table_name ORDER BY column_name;
SELECT * FROM customers ORDER BY FirstName;

9. Sorting Multiple Columns

  • Used to sort multiple columns at a time.
  • Syntax used: SELECT * FROM table_name ORDER BY column_name1, column_name2;
SELECT * FROM customers ORDER BY LastName, Age;

10. The WHERE Statement

  • Used to ertract only those records that fulfill a specified criterion.
  • Syntax used: SELECT column_list FROM table_name WHERE condition;
SELECT * FROM customers WHERE ID=7;

11. The BETWEEN Operator

  • Used to selects values within a range.(first value must be lower bound and second value must be upper bound)
  • Syntax used: SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;
SELECT * FROM customers WHERE ID BETWEEN 121 AND 131;

12. AND Operator

  • Used when you want to select rows that satisfy all the given conditions, use the logical operator AND.
  • Syntax used: SELECT column_name1, column_name2 FROM customers WHERE condition1 AND condition2;
SELECT ID, FirstName, Age FROM customers WHERE Age>=30 AND Age<=40;

13. OR Operator

  • Used when you want to select rows that satisfy any one of the given conditions.
  • Syntax used: SELECT * FROM table_name WHERE condition1 OR condition2;
SELECT * FROM customers WHERE City='Delhi' OR City='Bangalore';

14. Combining AND & OR

  • The SQL AND and OR conditions may be combined to test multiple conditions in a query.
  • Syntax used: SELECT * FROM table_name WHERE condition1 AND (condition2 OR condition3);
SELECT * FROM customers WHERE City='New York' AND (Age=67 OR Age=80);

15. The IN Operator

  • Use IN instead of multiple OR conditions.
  • Syntax used: SELECT * FROM table_name WHERE column_name IN (value1, value2);
SELECT * FROM customers WHERE City IN ('New York', 'London');

16. The NOT IN Operator

  • Allows to exclude a list of specific values from the result set.
  • Syntax used: SELECT * FROM table_name WHERE column_name NOT IN (value1, value2);
SELECT * FROM customers WHERE City NOT IN ('New York', 'London', 'Washington');

17. The CONCAT Function

  • Used to concatenate two or more text values and returns the concatenating string.
  • Syntax used: SELECT CONCAT(column1, ‘,’ , column2) FROM table_name;
SELECT CONCAT(FirstName, ',' , City) FROM customers;

18. The AS Keyword

  • used to show the concatenation results in a new column.
  • Syntax used: SELECT CONCAT(column1, column2) AS new_column_name FROM table_name;
SELECT CONCAT(FirstName, ',' , City) AS new new_column FROM customers;

19. The UPPER Function

  • Used to convert all letters in the specified string to uppercase.
  • Syntax used: SELECT column1, UPPER(column2) AS new_column FROM table_name;
SELECT FirstName, UPPER(LastName) AS LastName FROM employee;

20. SQRT Function

  • Returns the square root of the given value in the argument.
  • Syntax used: SELECT column1, SQRT(column1) FROM table_name;
SELECT Salary, SQRT(Salary) FROM employee;

21. AVG Function

  • Returns the average of the given value in the numeric column.
  • Syntax used: SELECT AVG(column1) FROM table_name;
SELECT AVG(Salary) FROM employee;

22. The SUM Function

  • Calculate the sum for a numeric column.
  • Syntax used: SELECT SUM(column1) FROM table_name;
SELECT SUM(Salary) FROM employee;

23. DESC and ASC Keywords

  • Used to arrange the specific column in desending or asending order.
  • Syntax used: SELECT column_name FROM table_name ORDER BY column_name DESC/ASC;
SELECT FirstNmae, Salary FROM employee WHERE Salary>3000 ORDER BY Salary DESC;

24. The LIKE Operator

  • Used to specify the search condition within your WHERE clause
  • Syntax Used: SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;
SELECT * FROM employees WHERE FirstName LIKE 'A%';

25. The MIN Function

  • Used to return the minimun value of an expression in a SELECT statement.
  • Syntax used: SELECT MIN(column_name) AS column_name FROM table_name;
SELECT MIN(Salary) AS Salary FROM employees;

26. INNER JOIN

  • It returns rows there is a match between the tables.
  • Syntax used: SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;
SELECT students.name, universities.name FROM students INNER JOIN universities ON students.ID=universities.ID;

27. LEFT JOIN

  • Returns all the rows from left table, even there is no match in the right table.
  • Syntax used: SELECT table1.column1, table2.column2.. FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name;
SELECT customers.Name, items.Name FROM customers LEFT JOIN items ON customers.ID=items.Seller_id;

28. RIGHT JOIN

  • Retuns all rows from the right table even there is no matches in the left table.
  • Syntax used: SELECT table1.column1, table2.column2… FROM table1 RIGHT OUTER JOIN table2 ON table1.column_name=table2.column_name;
SELECT customers.Name, items.Name FROM customers RIGHT JOIN items ON customers.ID=items.Seller_id;

Closing Words!

I hope, you’ll find it informative!

Here, you can find all the basic SQL commands and queries that are always propitious for database management.

Even though, as a developer, you need these basic queries for creating, storing or manipulating data in different languages like Java, C++, or Python etc.

Keep Coding!👨‍💻

Sakshi K

I merge my love for coding and writing, crafting tech stories that empower and enlighten readers.

Comments

Leave a Reply

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