SQL – Buzzword in the Database World
SQL is part of the vastly growing database technologies of present businesses. As data in businesses is growing at an unbelievably fast rate, understanding SQL, relational databases, and data management is vital for survival in today’s world. The benefit of SQL is that programmers and administrators need to learn one single language, and with minor adjustments, can apply it to a wide variety of database platforms, applications, and products. SQL is the standard language used to communicate with a relational database. It is important to learn and understand SQL and how data structures are used to store information in an organization. You use SQL to convey your needs to the database. You can request specific information from within a database by writing a query in SQL. The SELECT statement is used to retrieve records from one or more tables in a SQL database.
SELECT Statement and its Significance
SELECT statement represents Data Query Language (DQL) in SQL. A table is populated by using the INSERT statement. After this the SELECT statement is used in conjunction with the FROM clause to extract data from the database in an organized, readable manner. The SELECT keyword in a query is followed by a list of columns which you want to display as the result of a query output.
The SELECT statement retrieves or extracts information from tables in the database. Using the SELECT statement, you can choose the rows in a table that you want returned from a query by specifying values to match the rows that you want to display. In its simplest form a SELECT statement must contain the following:
- A SELECT clause: This specifies the columns which contain the values to be matched against.
- A FROM clause which specifies the TABLE containing the columns listed in the SELECT clause.
The SELECT statement has many optional clauses:
- WHERE specifies which rows to retrieve.
- GROUP BY groups rows sharing a property so that an aggregate function can be applied to each group.
- HAVING selects among the groups defined by the GROUP BY clause.
- ORDER BY specifies an order in which to return the rows.
- AS provides an alias which can be used to temporarily rename tables or columns.
Syntax for a SELECT Statement:
SELECT [column 1, column 2, ...] FROM source_table;
If you want to display all the columns of a table in the output, then use the symbol ‘*’ after SELECT.
Combination of SQL Select and Insert Statements
The INSERT statement with a nested SELECT statement helps a table to be quickly populated with one or more rows from the result set of the SELECT statement.
Multiple records can be inserted by using a SELECT statement after an SQL INSERT statement:
INSERT INTO table (column1, column2, ... ) SELECT expression1, expression2, ... FROM source_tables WHERE conditions;
Explanation of the parameters or arguments used in the above syntax:
- table is the table to insert the records into.
- column1, column2 are the columns in the table to insert values.
- expression1, expression2 are the values to assign to the columns in the table. So column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and so on.
- source_tables is the source table when inserting data from another table.
- conditions are conditions that must be met for the records to be inserted.
SQL SELECT INTO Statement
The SELECT INTO statement selects data from one table and inserts it into a new table.
If we want to copy all columns of a table into a new table, then we use ‘*’ after the SELECT statement.
SELECT * INTO newtable [IN externaldb] FROM table1;
Example of SQL SELECT INTO Statement:
Here is an SQL SELECT INTO example to create a copy of the entire table of Vendors:
SELECT * INTO Vendorcopy2014 FROM Vendors;
If we want to copy specific columns names into the new table then we enter:
SELECT [column 1, column 3, ….] INTO newtable FROM table1;
Here a new table will be created with the column-names and types as defined in the SELECT statement.
Example: To copy only the columns bearing Vendor Name and Country Name in the new table.
SELECT VendorName, CountryName INTO Vendorcopy2014 FROM Vendors;
SELECT DISTINCT Statement
The DISTINCT clause is used to remove duplicates from the result set of a SELECT statement.
Syntax for the SQL DISTINCT clause is:
SELECT DISTINCT expressions FROM tables WHERE conditions;
Explanation of Parameters or Arguments
- Expressions are the columns or calculations that you wish to retrieve.
- Tables are the tables that you wish to retrieve records from. At least one table must be listed with the FROM clause.
- Conditions are conditions that must be met for the records to be selected.
If you do not want to remove duplicate data, or are sure that there will be no duplicates in the result set, then use ALL keyword instead of DISTINCT. But as ALL is the default keyword, so we usually do not include ALL in our queries.
If you want to explore the effects of different clauses of SELECT statement, then SQL Queries 101 will help you to understand how to write basic SQL queries and other code statements. As you hone your knowledge and skills to become a successful database administrator and developer, you need to start writing customized SQL code to ensure you meet the user requirements in the most efficient manner. If you want to explore Oracle SQL and Oracle PL/SQL Programming then taking a peek into the course such as Introduction to Oracle SQL, or Oracle PL/SQL Tutorial – A Comprehensive Training Course may be a good idea.