10 Illustration Queries Of Sql Choose Command
The Select dominance inwards SQL is ane of the most powerful together with heavily used commands. This is I guess the kickoff dominance anyone acquire inwards SQL fifty-fifty earlier CREATE which is used to create a tabular array inwards SQL. SELECT is used inwards SQL to fetch records from database tables together with y'all tin do a lot many things using Select. For example, y'all tin select all records, y'all tin select few records based on the status specified inwards WHERE clause, select all columns using the wild carte (*) or exclusively selecting a few columns past times explicitly declaring them inwards a query.
In this SELECT SQL dominance tutorial, nosotros volition run into or then examples of select command or Select Statement together with volition write SQL queries to demonstrate the result. We volition utilization next tabular array together with information for our SQL inquiry examples, ane tabular array stand upwardly for Stocks listed inwards diverse marketplace position together with or then other tabular array contains Details of marketplace position e.g. Country. MySQL is my favorite RDBMS together with cracking for learning role y'all tin download MySQL together with start working on it. My proposition is to utilization dominance trouble interface for writing queries instead of using GUI e.g. SQL Developer or MySQL inquiry tool. Command trouble is best for learning together with existent fun of writing SQL inquiry is exclusively on the dominance 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 inwards ready (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 inwards ready (0.00 sec)
SQL SELECT dominance inquiry examples
hither are or then of my favorite select clause examples which explore dissimilar ways ane tin utilization the select dominance for reporting role together with display results.
1) Finding how many rows inwards tables
mysql> select count(*) from STOCK;
+----------+
| count(*) |
+----------+
| v |
+----------+
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 inwards ready (0.00 sec)
3. Selecting few records based on or then status from tables inwards 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 merely give the lift 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 demo exclusively unique records it volition non demo 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 together with OR Can hold upwardly effectively used to combine 2 atmospheric condition on WHERE clause together with gives y'all 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 pose whatever publish of AND, OR atmospheric condition on WHERE Clause, sometimes things move past times away quite slow when y'all combine AND, OR inwards SQL.
8. How to detect records which are non zero using keyword NULL together with IS NULL
NULL is real tricky inwards SQL; NULL agency anything which doesn't conduct keep value. NULL is non "null" which volition hold upwardly 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 inwards ready (0.00 sec)
You See at that topographic point is exclusively ane row who has LISTED_ON_EXCHANGE null, nosotros volition straight off run into count using NULL together with IS NULL which volition verify this result.
mysql> select count(*) from STOCK where LISTED_ON_EXCHANGE IS NULL;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row inwards ready (0.00 sec)
mysql> select count(*) from STOCK where LISTED_ON_EXCHANGE IS NOT NULL;
+----------+
| count(*) |
+----------+
| v |
+----------+
1 row inwards ready (0.00 sec)
mysql> select count(*) from STOCK;
+----------+
| count(*) |
+----------+
| vi |
+----------+
1 row inwards ready (0.00 sec)
9. SELECT Statement using BETWEEN together with NOT BETWEEN
As the lift 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 inwards SQL queries using LIKE together with NOT LIKE
LIKE is a blueprint matching operator together with used to detect records which are non an exact gibe 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 together with 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 together with NOT IN
IN is or then other useful SQL operator nosotros tin utilization amongst SELECT. it provides a ready of values which tin hold upwardly used inwards WHERE clause.
mysql> select * from Stock where RIC inwards ('GS.N' , 'INFY.BO');
+---------+-------------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+---------+-------------------------+--------------------+
| GS.N | Goldman Sachs Group Inc | N |
| INFY.BO | InfoSys | BO |
+---------+-------------------------+--------------------+
12. Sorting ResultSet inwards SQL using ORDER BY, ASC, DESC
Order past times is used to variety records inwards the termination ready returned past times SELECT clause. By default, it listing inwards Ascending social club but nosotros tin utilization either ascending or descending using specifier ASC together with DESC.
mysql> select * from Stock social club past times 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 past times using JOIN inwards SQL
Join inwards SQL is a powerful concept which allows y'all to select information from multiple tables. You tin generate a study where information is accumulated from dissimilar tables based on atmospheric condition specified inwards Join statement.
Suppose y'all require to “display a listing of Records together with Name of Market where they are listed”. Here the lift of Stock inwards the STOCK tabular array spell the lift of central inwards the MARKET table. We require 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 also hold upwardly written past times using explicit bring together way 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 perform or then basic calculation using SELECT clause e.g addition, subtraction, multiplication, partitioning etc.
mysql> select 1+2;
+-----+
| 1+2 |
+-----+
| iii |
+-----+
17. SELECT information from ane row till or then other row similar Paging
If y'all are thinking to implement paging together with getting information from specified row y'all tin do this easily inwards Mysql past times using LIMIT clause.
mysql> select * from Stock social club past times COMPANY LIMIT 0,2;
+--------+-------------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+--------+-------------------------+--------------------+
| GS.N | Goldman Sachs Group Inc | N |
| GOOG.O | Google Inc | O |
+--------+-------------------------+--------------------+
Here kickoff parameter '0' says start from the kickoff tape together with '2' says to acquire 2 records only.
18. Selecting information from the termination of or then other inquiry past times using derived table.
Sometimes information needed to create concluding SELECT termination comes from or then other inquiry together with human activeness 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 quest close SELECT dominance inwards SQL:
So Far nosotros conduct keep seen dissimilar examples of a SELECT clause inwards SQL which volition enable y'all to conduct keep sum wages of SELECT spell writing SQL queries. Here I conduct keep listed or then of import points which y'all should consider spell writing SQL inquiry non merely SELECT but with whatever other keyword also.
1) Most frequently 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 boring your inquiry drastically together with upshot would hold upwardly 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 spell afterwards index it merely took .3 second, whopping 260% increment inwards speed.
2) If y'all don't require all columns, don’t utilization the * wild card. SELECT inquiry with few columns is slightly faster than all columns.
3) If y'all are retrieving information from a large table, do a count (*) banking concern check earlier firing actual select query, this volition give y'all en guess of how many records y'all are close to acquire together with how much fourth dimension it could take.
4) You can innovate novel columns inwards the termination ready of SELECT Query past times using keyword "AS" as shown inwards 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 hold upwardly null. Don’t utilization 'null' that volition hold upwardly treated equally text.
6) While writing SQL query, non merely SELECT, its practiced do to write a keyword inwards the pocket-size instance and TABLES together with COLUMNS inwards capital. So that they volition stand upwardly out from the whole inquiry together with makes the inquiry to a greater extent than readable.
That's all on SQL Select dominance examples, I tried to encompass a practiced publish of select dominance instance to render an overview what SELECT contestation tin do. If y'all know whatever practiced select instance inwards SQL delight share.
Further Learning
Difference betwixt truncate together with delete inwards SQL
0 Response to "10 Illustration Queries Of Sql Choose Command"
Post a Comment