SQL

Learning Simple Queries in SQL

updated on

In this article on Learning Simple Queries in SQL, I will explain how to perform simple selection and projection queries on a database table in SQL.

To begin with, let us create a table Course in the Institute database that we have created earlier. The following create command creates the Course table.

create table Course(course_id integer primary key, course_name varchar(10), duration integer, credits integer);

As a result of the execution of the above command, we get the following structure of the Course table.

Create Table Command in SQL
Create Table Command in SQL

After that, let us insert certain rows using the following insert commands.

insert into Course(course_id, course_name, duration, credits) values(17, 'BBA', 3, 100)
insert into Course(course_id, course_name, duration, credits) values(20, 'BCA', 3, 100)
insert into Course(course_id, course_name, duration, credits) values(39, 'MBA', 2, 100)
insert into Course(course_id, course_name, duration, credits) values(44, 'MCA', 2, 100)
insert into Course(course_id, course_name, duration, credits) values(324, 'BTECH', 4, 100)
insert into Course(course_id, course_name, duration, credits) values(234, 'LLB', 5, 100)
insert into Course(course_id, course_name, duration, credits) values(28, 'M.Sc.', 2, 100)

The following figure shows the Course table after inserting rows.

Course Table
Course Table

Using Projection and Selection for Learning Simple Queries in SQL

The following section provides the examples of selection and projection queries in SQL.

Projection Queries

Basically, in SQL projection allows us to specify which of the columns we want to retrieve as a result of the query. In other words, the projection query returns the data from chosen columns. The following queries demonstrate the examples of projection.

  1. Display data from all columns.
SELECT * FROM Course

Output

Selecting All Columns
Selecting All Columns

2. Display specific columns

SELECT course_name, duration FROM Course

Output

Selecting course_name and duration
Selecting course_name and duration

Selection Queries

Likewise, the selection operation allows us to retrieve data from specific rows on the basis of certain condition. In other words, in selection queries we specify the condition in where clause of the select statement. The following queries demonstrate the examples of selection operation.

  1. Display the courses with duration of more than three years.
SELECT * FROM Course where duration > 3

Output

Courses with more than 3 years duration
Courses with more than 3 years duration

2. Display courses where the duration falls in the range of three and four years,

SELECT * FROM Course where duration between 3 and 4

Output

Between Operator in SQL
Between Operator in SQL

3. Display the courses where duration is either two or three or five years.

SELECT * FROM Course where duration in (2, 3, 5)

Output

In Operator in SQL
In Operator in SQL

4. Display the courses where course name starts with ‘B’ character.

SELECT * FROM Course where course_name LIKE 'B%'

Output

Like Operator in SQL
Like Operator in SQL

4. Display the courses where course name starts with ‘B’ character and duration is more than three years.

SELECT * FROM Course where course_name LIKE 'B%' and duration > 3

Output

Compound Condition in Where Clause
Compound Condition in Where Clause

Further Reading

SQL Practice Exercise

Data Definition Language Commands in SQL

You may also like...
SQL

SQL Practice Exercise
SQL

Data Definition Language Commands in SQL
SQL

SQL Queries – Basic to Advanced