In the real-world scenario where we have to select only columns from a given table, the columns selected can be single or multiple as per requirement. For Example: Write a query that gives the names of EMPLOYEE in an organization, so here we have to pick out only the name column from that particular EMPLOYEE table. Similarly, another example of multiple columns can be: Write a query which gives the names and salaries of all employees working in an organization. So here we have to select 2 columns of name and salary.
The examples above make us understand that the selection of columns is very important while learning SQL. First, we will learn how to select a single column from a table then we will move toward multiple columns.
How to Create a Database in SQL?
First, we create our database to execute the selected queries
Step 1: Creating the database
Use the below SQL query to create a database called geeks:
CREATE DATABASE geeks;
Step 2: Using the database
USE geeks;
Step 3: Table Creation
CREATE TABLE Employee
(
EmpID int,
FirstName varchar(255),
LastName varchar(255),
Salary INT
);
Step 4: Adding data to the table
INSERT INTO Employee VALUES(1, 'john' , 'ryther', 10000);
INSERT INTO Employee VALUES(2, 'Alex' , 'Hamilton', 20000);
INSERT INTO Employee VALUES(3, 'Sze' , 'Chauhan' , 10000);
INSERT INTO Employee VALUES(4,'Shiv', 'Chauhan', 50000);
Output:
Employee Table
Different Cases in SQL to Fetch Desire Output
Now, we are going to discuss different cases in SQL to fetch the desired output as per the query mentioned.
Case 1: Selecting a Single Column in SQL
In SQL, selecting any column is one of the easiest things as you have to type only the SELECT command and after that, the column name and the output will be the desired column.
Syntax:
SELECT (Column Name)
FROM (Table Name);
To make it more clear let’s take a general example of the EMPLOYEE table, which we have created above. Now, we have to select the column First_Name from the given table.
Query:
SELECT FirstName
FROM EMPLOYEE;
Output:
output
Case 2: Selecting a Single Column Based on Conditions
Now, we see how we have to fetch out the first name of those employees whose salary is 10,000. From the table, it is quite clear that our desired output should be: John and Sze. To solve such queries we have to simply put a WHERE clause in our code along with the condition, as shown in the below query:
Query:
SELECT FirstName
FROM EMPLOYEE
WHERE Salary=10000;
Output:
output
Case 3: Selecting Multiple Columns in SQL
Selecting multiple columns in SQL with or without any condition is as simple as selecting a single column and not only simple but also the same as that. Taking our previous example further, this time we have to write a query to get a first name as well as last name from the employee table which means that we have to fetch 2 columns, the solution is very simple, we have to write all those columns names with SELECT clause which we want to fetch (In our case its first name and last name) and then table name. The syntax for which is shown below
Syntax:
SELECT column1,column2,column3…FROM table name;
In our example, the code will be as follows
Query:
SELECT FirstName,LastName
FROM Employee;
Output:
output
Case 4: Selecting Multiple Columns with Conditions
When we have to select multiple columns along with some condition, we put a WHERE clause and write our condition inside that clause. It is not mandatory to choose the WHERE clause there can be multiple options to put conditions depending on the query asked but most conditions are satisfied with the WHERE clause. As per the above example, this time we are going to put multiple conditions.
Example:
Write a query in SQL to select a first name and last name of an employee who has either salary of 10000 or has the last name of Chauhan.
For this query, there are three things
- Selecting columns first name and last name i.e. SELECT First_Name, Last_Name.
- From the employee table i.e. FROM Employee
-
Now, conditions are the tricky part as there are two conditions, let’s deal one by one
- Salary must be 10000 i.e. Salary=10000
- the Last name should be Chauhan i.e. Last_name=’chauhan’
And, hence our query has been solved, now we have to just put the above things in proper format as shown
Query:
SELECT FirstName, LastName
FROM Employee
WHERE Salary=10000 OR LastName='Chauhan';
Output:
output
We can match from the table that the employees which have a salary of 10000 are john and sze and the employees who have the last name Chauhan are sze and shiv which is as per our desired output.