Learn How to Use the Project and Selection Operations in SQL

[XiNFiNiTY]

Standard Query Language (SQL) is a mathematically based language that is used to query databases. There are several different types of database management systems in existence; SQL is used with the relational database management system.

The relational database management system (or relational model) deals with the mathematical concept of a relationship and is physically represented as a table.

These tables are represented by rows and columns, where the rows contain records and the columns contain attributes.

Two special types of operations can be carried out on the rows and columns in a table---project and selection.

Project Operation

The project SQL operation allows users of the relational model to retrieve column-specific data from a table. This data is then used to create a new table that is dedicated to the information that the user would like to see.

So, if you had a relational model consisting of nine different columns but you only need the name and the date of birth for each individual in the table, you would use a project operation to retrieve this data.

Project Operation Structure

Select column_name from table_name

The project operation has a pretty straightforward structure, consisting of exactly four parts.

The Select keyword, which should always begin with a capital letter.

The column name/s, if there is more than one each should be separated from the other with a comma.

The from keyword, which is all lower case.

The table name.

Using the Project Operation on a Table

Imagine a furniture store that has a relational database management system.

In this database, a customer table that stores all the data we have on each customer. In the customer table are nine fields:

CustomerID

FirstName

LastName

DOB

PhoneNumber

Email

CustomerAddress

City

Country

Customer Table Example

RELATED:How To Create A Table In SQL

One day the customer relations officer comes up with a brilliant idea that is aimed at improving customer relationship.

The idea is to get the software developer to create a simple automated program that will email each customer on their birthday.

So now you need exactly four fields of data from our customer table: FirstName and LastName, to personalize the email; DOB, to know the date to schedule the email on; and Email.

Using the Project Operation Example

Select FirstName, LastName, DOB, Email from Customer

The code above will effectively generate a new table that can be used to create a simple program. The table that was generated can be seen below.

Customers Birthday Table Example

In this instance, the project operation proves to be very useful because of two reasons.

It protects the privacy of the customers and provides the information that is needed. The customers trust the store with their information, and by providing only the data that is essential for a specific member of staff to carry out their duties, that trust is protected.

The Similarities Between the Project and Selection Operation

The selection operation targets records (rows), or specific entities in a relational database. The structure of a selection operation is very similar to that of the project operation; in fact, there is one specific operation that can be used as a project or a select operation because it returns the same result in either case.

This operation is known as a select all query and what it does is produce all the data that is in a table.

Select All Example

Select * from table_name

If you were to use the query above as a project operation you would say that you are selecting all the attributes (columns) in a relational database.

However, if you were to use the example above as a selection operation then you would be selecting all the records (rows) in a relational database.

The point is that regardless of the operation type, you will always get the same result.

Using Select All on Customers Table

Select * from Customers

The code above will simply regenerate the original Customers table, which can be seen under the “customer table example” above.

The Selection Operation

What makes an average selection operation different from a project operation is the “where” property. The “where” property makes it possible for the selection operation to target records, that meet a specific criterion.

RELATED:The Most Important SQL Commands Any Programmer Should Know

Selection Operation Structure Example

Select * from table_name where column_name = value

Using the Selection Operation

Our furniture store has branches all over the country, and all of these branches are connected to the main database. From this database, the managing director was able to see that a branch in a specific city is not performing as well as the others.

After some brainstorming, the decision was made to create a “bring a friend” initiative. The idea here is for customers from the poorly performing branch to be emailed a coupon, and if they brought a friend who purchased an item that coupon can be used with a 10% discount off their next purchase.

The database administrator would now need to generate a new table that contains only customers from the target city.

Selecting All Customers From Kingston Example

Select * from Customers where City='Kingston';

The example above would generate the following table.

Using the Project and Selection Operations Together

The table created above using the selection operation got the job done; this gives you a record of all customers that are in Kingston city.

The only problem is that you have now thrown the customers' privacy right out the door. The staff member that is going to be emailing these coupon codes to our Kingston customers does not need access to their full address, phone number, or customer ID.

Using the project and selection operation together solves this little problem.

Using the Project and Selection Operation Example

Select FirstName, LastName, Email from Customers where City='Kingston';

The query above will generate the following table.

As you can see from the table above only the information that is necessary to carry out this particular task is available.

Now You Can Use the Project and Selection Operations

Using the basic structure of a relational database management system gives you can use the project and selection operations separately and together.

This is just one of the many ways to interrogate database tables.

[Silent Watcher]

Avoid unnecessary posts such as 'Thank you', 'Welcome', etc. Such posts will be deleted and user will be warned if it happens again. If caught spamming, the following actions are applicable -

• First time - Warning
• Second time - 5000 Points will be deducted
• Third time - Ban for 7 days
• Fourth time - Permanent Ban

If the post helped you, reward the user by reacting to the post like this -