SQL Queries for Beginners

30 Most Common SQL Queries for Beginners

Big Data / By

Aspiring to learn SQL and build a successful career in data? Here we bring the most common SQL queries for beginners that will help you become a data pro.

Computers are machines, and we need a specialized language for communicating with them. Therefore, computer programming languages come with a wide range of instructions to address certain tasks. Programming languages such as SQL, Python, and Java help computers and systems process large and complex data efficiently.

SQL or Structured Query Language is undoubtedly one of the prominently popular programming languages, especially for its functionalities in access and modification of data in databases. The basic use of SQL for interfacing with databases is the most prominent reason for its popularity.

Check Now: SQL BASICS TRAINING COURSE

Therefore, the demand for learning about SQL has increased recently. One of the most fundamental aspects of learning about SQL is the query. Basically, a query in SQL is a request or question for data. SQL queries for beginners can help in familiarizing themselves with the SQL language.

Top 30 SQL Queries for Beginners

It is important to consider SQL queries as more than just SQL basics. SQL queries are your tools to use SQL for different tasks without having to go through complicated procedures. So, let us dive into the most frequently asked SQL queries in professional interviews for database-related job roles.

1. Displaying the Data from All Columns in a Table

One of the first SQL queries for beginners addresses the function of retrieving data from a table. The query has only one character followed by SELECT, i.e., *, pointing out to all columns in the table. As a result, there is no need for listing the names of all the columns. The additional component and most important one in this query are ‘FROM,’ followed by the name of the table in question for data retrieval. For example, if you want to obtain data from a table ‘stars,’ then the query would be,

SELECT *

FROM stars;

2. Creating Databases & Tables

This is also one of the most important SQL queries for beginners, and it is essential to begin working with SQL. The “CREATE” keyword can help you create tables as well as databases as tables within databases. Here is an example of an SQL query for creating a database, entering into it, and then create a table.

CREATE db_name;

/* Use the specified database for executing further queries */

USE db_name;

CREATE TABLE authors (author_name VARCHAR(60), author_email VARCHAR(70), author_pay int);

3. Filtering out Data

The ‘WHERE’ clause is one of the significant SQL queries for beginners for understanding the functionalities of SQL. It helps in filtering data on the basis of certain conditions. For example, in the case of ‘students’ databases, you can implement a condition such as age>=10. This example would aim to find out records that have a value of 10 or more in the ‘age’ column.

SELECT id, name, age

FROM students

WHERE age>=10;

4. Obtaining Data from Specific Columns

SQL also offers the opportunity for displaying data from specific columns of a table. You can achieve this by listing the columns after ‘SELECT.’ Take the example of a ‘students’ database. If you want to retrieve data regarding ‘id’ and ‘name,’ then you can use the following example query,

SELECT id, name

FROM students;

5. Filtering Data on the Basis of Conditions Joined with OR Operator

This is an advancement over the basic SQL queries for beginners and offers filtering of data on the basis of multiple conditions. If you want to implement two conditions and want one of the conditions to be fulfilled for filtering data, then you can join the conditions by using the ‘OR’ operator. In the following example query, let us find out records of students with age of 10 years or more and with the first name ‘John.’

SELECT id, name, age

FROM students

WHERE age >= 10 OR name = ‘John’;

6. Filtering Out Data by Joining Conditions with AND Operator

You can also find such SQL commands for beginners for in-depth data filtering. This SQL query can provide better data filtering with the use of more than one condition. The ‘AND’ operator does the trick here. So, improvising on the above-mentioned example query, let us find out records of students with the first name ‘John’ above the age of 10 years.

SELECT id, name, age

FROM students

WHERE age >= 10 AND name = ‘John’;

7. Obtain Non-repeated Records

You can use the ‘DISTINCT’ keyword after ‘SELECT’ for obtaining only a specific type of record, thereby avoiding any repeated records. In the following example query, let us obtain records from the columns of ‘name’ and ‘color’ from a table of ‘clothing.’

SELECT DISTINCT name, color

FROM clothing;

This query would help in verifying whether the values in these columns are the same for multiple records. In case of the same values in more than one record, the query will return only one of the records. Therefore, you can notice that SQL queries for beginners can simplify tasks in interacting with databases.

8. Sorting Data on the Basis of Multiple Columns

The SQL functionalities also help in sorting data in accordance to the requirements of multiple columns. Here is an example of sorting records according to the column ‘name’ in descending order followed by the column ‘id’ in ascending order. If the query identifies records with the same name, then it can sort all records in ascending order on basis of ‘id.’

SELECT id, name

FROM animal

ORDER BY name DESC, id;

9. Obtaining Data in a Specific Column without NULL

SQL queries for beginners also include functions for retrieving data only from rows without the “NULL” in a specific column. Just use ‘IS NOT NULL,’ and the job is done. Here is an example,

SELECT name, color

FROM clothing

WHERE color IS NOT NULL;

This example query showcases that the value in ‘color’ column could not be ‘NULL.’ It returns the records with value stored in the ‘color’ column.

Also Check – Google Cloud SQL Deep Dive Training Course

10. Sorting Data on the basis of One Column

The example SQL query for this objective would require placing the column name followed by ‘ORDER BY.’ The default sorting method in this query would be alphabetical. However, you can use ‘DESC,’ followed by the column name for displaying rows in descending order. The following example helps in sorting data into the ‘id’ and ‘name’ columns on the basis of ‘name’ column.

SELECT id, name

FROM animal

ORDER BY name;

11. Joining Values from Text Columns in One String

‘CONCAT’ function serves the role of joining strings. The following example query returns strings in the ‘name’ and ‘category’ columns, joined as a single column of strings. The single-column has a space between the value in the ‘name’ column and the ‘category’ column.

SELECT CONCAT (name, ‘,’ category)

FROM tab;

12. Identify Values Aligning with Specific Pattern

SQL queries for beginners can help in obtaining data from tables that align with specific pattern by using ‘LIKE’. The following example query helps in retrieving records from ‘name’ and ‘id’ columns that contain a string containing the character ‘b’ in the ‘name’ column.

SELECT id, name

FROM animal

WHERE name LIKE ‘%b%’;

13. Mathematical Operators

SQL queries for beginners can also include the use of mathematical operators. Mathematical operators such as “+,” “*,” “/” and “-” help in performing arithmetic operations on data in the table. The following example query subtracts the ‘discount’ from ‘price’ of products.

SELECT price – discount

FROM product;

14. Using Aliases of Tables and Columns

Aliases for tables and column names can help in joining tables. The following example joins two tables ‘city’ and ‘customer.’

SELECT c.last_name AS lname, t.name AS city

FROM customer AS c

INNER JOIN city AS t

  ON c.id = t.customer_id;

15. Determining Average of Values in a Column

AVG can help in calculating the average of values in a column. Here is an example query of the same,

SELECT AVG (price)

FROM product;

16. Counting Number of Rows

This is also one of the basic SQL queries for beginners using ‘COUNT.’The following example query returns the number of values from ‘id’ column stored in the ‘product’ table,

SELECT COUNT (id)

FROM product;

17. Determine Sum of Values in Columns

SUM command can help in calculating values of a column as in the following example query,

SELECT SUM (price)

FROM product;

18. Find Intersection of Data Sets

INTERSECT can help in returning the values of the intersection of two data sets. Here is the example query for retrieving last names listed in both tables.

SELECT last_name FROM customer

INTERSECT

SELECT last_name FROM employee;

19. Addition of Data from Various Tables

UNION ALL operators can help in joining records from various tables only if the records belong to similar data types. The following example returns all rows with last names from ‘customer’ table and ‘employee’ table.

SELECT last_name FROM customer

UNION ALL

SELECT last_name FROM employee;

You can use UNION rather than UNION ALL for selecting all last names without repeated values.

Also Check: NoSQL vs SQL – Which One is Better?

20. Identifying Maximum Value in a Column

The basic command for beginners in SQL is ‘MAX’ that helps in finding maximum value in a column. Here is an example of the SQL query for finding maximum price of products.

SELECT MAX (price)

FROM product;

21. Identifying Minimum Value in a Column

The ‘MIN’ command can help in finding minimum value in a column. Here is an example query to find minimum price from products

SELECT MIN (price)

FROM product;

22. Removal of Data from Tables

You can use the following example query to remove all data from a table,

DELETE FROM table1;

23. Inserting Data into Table

New records can be added to a table through the ‘INSERT INTO’ command. The following example query helps in inserting 15 into ‘id,’ bottle into ‘name,’ and kitchen supplies into ‘category’ of the ‘product’ table.

INSERT INTO product(id, name, category)

VALUES (15, 'bottle', 'kitchen supplies');

24. Updating Columns

Here is an example query for modifying all values in the ‘company’ column to ‘XYZ.’

UPDATE product SET company = ‘XYZ’;

25. Updating Column on the basis of Record Filtering

The following example query helps in specifying records for modification.

UPDATE product

SET name = ‘bottle’

WHERE id = 15;

26. Deleting Tables

The ‘DROP TABLE’ command can help in deleting a table. Here is an example query for deleting ‘table1’,

DROP TABLE table1;

27. Database backup

The following SQL query can help in creating a script for database backup whenever required,

BACKUP DATABASE db_name

TO DISK = "/home/databases/db_name-backup.db";

28. Renaming Tables

The following example query can help in renaming a table on the basis of SQL version and privileges,

sp_RENAME authors authors_renamed;

29. Changing String Case

SQL queries can use UPPER() and LOWER() functions for displaying the output of a table in uppercase or lowercase. For example,

SELECT UPPER(author_name) FROM authors;

SELECT LOWER(author_name) FROM authors;

30. Verifying that Values are Numeric

If you want to check that a specific value is numeric, then you can use the ISNUMERIC function. For example,

SELECT ISNUMERIC (34);

SQL is the old option, well tried and tested by everyone for data analysis while many new options have also come. Check Hive vs Pig vs SQL to know which one is best for you.

Conclusion

The above-mentioned entries are the most common SQL queries for beginners as well as professionals. As a matter of fact, they serve as important additions among SQL tips and tricks for beginners. The queries help in performing simple tasks with tables such as creating tables and databases, updating columns, retrieving data, and deleting tables.

The next important step for learners would be to dive deeper into the world of SQL with the SQL Basics training course and discover more. In addition, you would have to get your hands dirty if you want to gain expertise in SQL. So the faster you get hands-on experience in using SQL queries, the better it is for your potential career opportunities!

About Aditi Malhotra

Aditi Malhotra is the Content Marketing Manager at Whizlabs. Having a Master in Journalism and Mass Communication, she helps businesses stop playing around with Content Marketing and start seeing tangible ROI. A writer by day and a reader by night, she is a fine blend of both reality and fantasy. Apart from her professional commitments, she is also endearing to publish a book authored by her very soon.

Related Posts

Leave a Comment

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

Scroll to Top