10 Representative Queries Of Sql Direct Command
Friday, July 13, 2018
Add Comment
The Select ascendance inward SQL is i of the most powerful as well as heavily used commands. This is I guess the starting fourth dimension ascendance anyone larn inward SQL fifty-fifty earlier CREATE which is used to create a tabular array inward SQL. SELECT is used inward SQL to fetch records from database tables as well as yous tin sack do a lot many things using Select. For example, yous tin sack select all records, yous tin sack select few records based on the status specified inward WHERE clause, select all columns using the wild carte du jour (*) or entirely selecting a few columns yesteryear explicitly declaring them inward a query.
In this SELECT SQL ascendance tutorial, nosotros volition run into simply about examples of select command or Select Statement as well as volition write SQL queries to demonstrate the result. We volition utilization next tabular array as well as information for our SQL inquiry examples, i tabular array stand upwards for Stocks listed inward diverse marketplace as well as simply about other tabular array contains Details of marketplace e.g. Country. MySQL is my favorite RDBMS as well as bully for learning role yous tin sack download MySQL as well as start working on it. My proffer is to utilization ascendance business interface for writing queries instead of using GUI e.g. SQL Developer or MySQL inquiry tool. Command business is best for learning as well as existent fun of writing SQL inquiry is entirely on the ascendance prompt.
mysql> select * from STOCK;
+---------+-------------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+---------+-------------------------+--------------------+
| 6758.T | Sony | T |
| GOOG.O | Google Inc | O |
| GS.N | Goldman Sachs Group Inc | N |
| INFY.BO | InfoSys | BO |
| VOD.L | Vodafone Group PLC | L |
+---------+-------------------------+--------------------+
5 rows inward laid upwards (0.00 sec)
mysql> select * from MARKET;
+------+-------------------------+---------------+
| RIC | NAME | COUNTRY |
+------+-------------------------+---------------+
| T | Tokyo Stock Exchange | Japan |
| O | NASDAQ | U.S. |
| N | New York Stock Exchange | U.S. |
| BO | Mumbai Stock Exchange | India |
+------+-------------------------+---------------+
4 rows inward laid upwards (0.00 sec)
SQL SELECT ascendance inquiry examples
hither are simply about of my favorite select clause examples which explore dissimilar ways i tin sack utilization the select ascendance for reporting role as well as display results.
1) Finding how many rows inward tables
mysql> select count(*) from STOCK;
+----------+
| count(*) |
+----------+
| five |
+----------+
2) Finding all records from tables; nosotros are using wildcard start * for getting all columns.
mysql> select * from STOCK;
+---------+-------------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+---------+-------------------------+--------------------+
| 6758.T | Sony | T |
| GOOG.O | Google Inc | O |
| GS.N | Goldman Sachs Group Inc | N |
| INFY.BO | InfoSys | BO |
| VOD.L | Vodafone Group PLC | L |
+---------+-------------------------+--------------------+
5 rows inward laid upwards (0.00 sec)
3. Selecting few records based on simply about status from tables inward SQL
mysql> select * from STOCK where RIC='GOOG.O';
+--------+------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+--------+------------+--------------------+
| GOOG.O | Google Inc | O |
+--------+------------+--------------------+
4. How to select few columns instead of all columns?
Instead of using start wild-card simply give the bring upwards of interested columns to SELECT clause.
mysql> select COMPANY from STOCK where RIC='GOOG.O';
+------------+
| COMPANY |
+------------+
| Google Inc |
+------------+
5. Select distinct (unique) records from Columns
The distinct keyword is used to present entirely unique records it volition non present whatever duplicate values.
mysql> select distinct LISTED_ON_EXCHANGE from Stock;
+--------------------+
| LISTED_ON_EXCHANGE |
+--------------------+
| T |
| O |
| N |
| BO |
| L |
+--------------------+
6. Selecting value with status based on less than, greater than (>, <, >=, <=) etc.
mysql> select * from Stock where RIC > 'I';
+---------+--------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+---------+--------------------+--------------------+
| INFY.BO | InfoSys | BO |
| VOD.L | Vodafone Group PLC | L |
+---------+--------------------+--------------------+
7. Combining status using logical operator AND & OR
AND as well as OR Can last effectively used to combine ii atmospheric condition on WHERE clause as well as gives yous a lot of flexibility to write SQL query.
mysql> select * from Stock where RIC <'I' AND RIC > 'G';
+--------+-------------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+--------+-------------------------+--------------------+
| GOOG.O | Google Inc | O |
| GS.N | Goldman Sachs Group Inc | N |
+--------+-------------------------+--------------------+
You tin sack position whatever number of AND, OR atmospheric condition on WHERE Clause, sometimes things transcend away quite slow when yous combine AND, OR inward SQL.
8. How to detect records which are non zero using keyword NULL as well as IS NULL
NULL is real tricky inward SQL; NULL way anything which doesn't lead maintain value. NULL is non "null" which volition last treated equally text.To demonstrate this nosotros volition insert a Stock which is non listed on whatever Market yet.
mysql> select * from STOCK;
+---------+-------------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+---------+-------------------------+--------------------+
| 6758.T | Sony | T |
| GOOG.O | Google Inc | O |
| GS.N | Goldman Sachs Group Inc | N |
| INDIGO | INDIGO Airlines | NULL |
| INFY.BO | InfoSys | BO |
| VOD.L | Vodafone Group PLC | L |
+---------+-------------------------+--------------------+
6 rows inward laid upwards (0.00 sec)
You See at that topographic point is entirely i row who has LISTED_ON_EXCHANGE null, nosotros volition right away run into count using NULL as well as IS NULL which volition verify this result.
mysql> select count(*) from STOCK where LISTED_ON_EXCHANGE IS NULL;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row inward laid upwards (0.00 sec)
mysql> select count(*) from STOCK where LISTED_ON_EXCHANGE IS NOT NULL;
+----------+
| count(*) |
+----------+
| five |
+----------+
1 row inward laid upwards (0.00 sec)
mysql> select count(*) from STOCK;
+----------+
| count(*) |
+----------+
| six |
+----------+
1 row inward laid upwards (0.00 sec)
9. SELECT Statement using BETWEEN as well as NOT BETWEEN
As the bring upwards advise BETWEEN is used to acquire information betwixt ranges.
mysql> select * from Stock where RIC BETWEEN 'G' AND 'I';
+--------+-------------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+--------+-------------------------+--------------------+
| GOOG.O | Google Inc | O |
| GS.N | Goldman Sachs Group Inc | N |
+--------+-------------------------+--------------------+
10. Pattern matching inward SQL queries using LIKE as well as NOT LIKE
LIKE is a designing matching operator as well as used to detect records which are non an exact check but in all probability match.
mysql> select * from Stock where RIC LIKE 'V%';
+-------+--------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+-------+--------------------+--------------------+
| VOD.L | Vodafone Group PLC | L |
+-------+--------------------+--------------------+
NOT LIKE is opposit of LIKE as well as display records which are non in all probability match.
mysql> select * from Stock where RIC NOT LIKE 'V%';
+---------+-------------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+---------+-------------------------+--------------------+
| 6758.T | Sony | T |
| GOOG.O | Google Inc | O |
| GS.N | Goldman Sachs Group Inc | N |
| INDIGO | INDIGO Airlines | NULL |
| INFY.BO | InfoSys | BO |
+---------+-------------------------+--------------------+
11. IN as well as NOT IN
IN is simply about other useful SQL operator nosotros tin sack utilization with SELECT. it provides a laid upwards of values which tin sack last used inward WHERE clause.
mysql> select * from Stock where RIC inward ('GS.N' , 'INFY.BO');
+---------+-------------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+---------+-------------------------+--------------------+
| GS.N | Goldman Sachs Group Inc | N |
| INFY.BO | InfoSys | BO |
+---------+-------------------------+--------------------+
12. Sorting ResultSet inward SQL using ORDER BY, ASC, DESC
Order yesteryear is used to kind records inward the termination laid upwards returned yesteryear SELECT clause. By default, it listing inward Ascending gild but nosotros tin sack utilization either ascending or descending using specifier ASC as well as DESC.
mysql> select * from Stock gild yesteryear COMPANY;
+---------+-------------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+---------+-------------------------+--------------------+
| GS.N | Goldman Sachs Group Inc | N |
| GOOG.O | Google Inc | O |
| INDIGO | INDIGO Airlines | NULL |
| INFY.BO | InfoSys | BO |
| 6758.T | Sony | T |
| VOD.L | Vodafone Group PLC | L |
+---------+-------------------------+--------------------+
14. Selecting information from multiple tables yesteryear using JOIN inward SQL
Join inward SQL is a powerful concept which allows yous to select information from multiple tables. You tin sack generate a written report where information is accumulated from dissimilar tables based on atmospheric condition specified inward Join statement.
Suppose yous involve to “display a listing of Records as well as Name of Market where they are listed”. Here the bring upwards of Stock inward the STOCK tabular array field the bring upwards of central inward the MARKET table. We involve to bring together both of them to display this report.
mysql> select s.RIC, m.NAME from Stock s, Market 1000 where s.LISTED_ON_EXCHANGE=m.RIC;
+---------+-------------------------+
| RIC | NAME |
+---------+-------------------------+
| 6758.T | Tokyo Stock Exchange |
| GOOG.O | NASDAQ |
| GS.N | New York Stock Exchange |
| INFY.BO | Mumbai Stock Exchange |
+---------+-------------------------+
Above method is called implicit Join an d This inquiry tin sack also last written yesteryear using explicit bring together manner which uses ON clause to bring together tables.
mysql> select s.RIC, m.NAME from Stock s INNER JOIN Market ON 1000 I s.LISTED_ON_EXCHANGE=m.RIC;
15. Calling component on SELECT clause e.g. displaying electrical flow date
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2011-10-13 10:25:47 |
+---------------------+
16. Doing calculation using SELECT CLAUSE
You tin sack perform simply about basic calculation using SELECT clause e.g addition, subtraction, multiplication, segmentation etc.
mysql> select 1+2;
+-----+
| 1+2 |
+-----+
| iii |
+-----+
17. SELECT information from i row till simply about other row similar Paging
If yous are thinking to implement paging as well as getting information from specified row yous tin sack do this easily inward Mysql yesteryear using LIMIT clause.
mysql> select * from Stock gild yesteryear COMPANY LIMIT 0,2;
+--------+-------------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+--------+-------------------------+--------------------+
| GS.N | Goldman Sachs Group Inc | N |
| GOOG.O | Google Inc | O |
+--------+-------------------------+--------------------+
Here starting fourth dimension parameter '0' says start from the starting fourth dimension tape as well as '2' says to acquire 2 records only.
18. Selecting information from the termination of simply about other inquiry yesteryear using derived table.
Sometimes information needed to create concluding SELECT termination comes from simply about other inquiry as well as human activity equally a tabular array for the outer SELECT statement. This tabular array also called Derived table
mysql> select RIC from (select s.RIC, m.NAME from Stock s, Market 1000 where s.LISTED_ON_EXCHANGE=m.RIC) t where RIC > 'G'
+---------+
| RIC |
+---------+
| GOOG.O |
| GS.N |
| INFY.BO |
+---------+
Some Important indicate most SELECT ascendance inward SQL:
So Far nosotros lead maintain seen dissimilar examples of a SELECT clause inward SQL which volition enable yous to lead maintain sum wages of SELECT field writing SQL queries. Here I lead maintain listed simply about of import points which yous should consider field writing SQL inquiry non simply SELECT but with whatever other keyword also.
1) Most oft nosotros utilization SELECT Operator with WHERE Clause, endeavour to utilization column which has the index on WHERE clause. Using a non-index column on WHERE clause tin sack dull your inquiry drastically as well as number would last to a greater extent than visible when your tabular array information increases. an instance with 1 Million records inquiry without index was taking 80-second field later index it simply took .3 second, whopping 260% increment inward speed.
2) If yous don't involve all columns, don’t utilization the * wild card. SELECT inquiry with few columns is slightly faster than all columns.
3) If yous are retrieving information from a large table, do a count (*) depository fiscal establishment check earlier firing actual select query, this volition give yous en justice of how many records yous are most to acquire as well as how much fourth dimension it could take.
4) You can innovate novel columns inward the termination laid upwards of SELECT Query yesteryear using keyword "AS" as shown inward below example. Very useful for displaying calculated value e.g. average or percentage.
5) Always use IS NULL or NULL for including or excluding values which could last null. Don’t utilization 'null' that volition last treated equally text.
6) While writing SQL query, non simply SELECT, its proficient practise to write a keyword inward the pocket-size instance and TABLES as well as COLUMNS inward capital. So that they volition stand upwards out from the whole inquiry as well as makes the inquiry to a greater extent than readable.
That's all on SQL Select ascendance examples, I tried to encompass a proficient number of select ascendance instance to supply an overview what SELECT disputation tin sack do. If yous know whatever proficient select instance inward SQL delight share.
Further Learning
Difference betwixt truncate as well as delete inward SQL
0 Response to "10 Representative Queries Of Sql Direct Command"
Post a Comment