10 Event Queries Of Sql Conduct Command
The Select ascendence inwards SQL is i of the most powerful in addition to heavily used commands. This is I guess the commencement ascendence anyone larn 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 in addition to you lot tin do a lot many things using Select. For example, you lot tin select all records, you lot tin select few records based on the status specified inwards WHERE clause, select all columns using the wild bill of fare (*) or exclusively selecting a few columns past times explicitly declaring them inwards a query.
In this SELECT SQL ascendence tutorial, nosotros volition encounter closed to examples of select command or Select Statement in addition to volition write SQL queries to demonstrate the result. We volition utilisation next tabular array in addition to information for our SQL interrogation examples, i tabular array stand upwards for Stocks listed inwards diverse marketplace in addition to closed to other tabular array contains Details of marketplace e.g. Country. MySQL is my favorite RDBMS in addition to smashing for learning role you lot tin download MySQL in addition to start working on it. My proposition is to utilisation ascendence trouble interface for writing queries instead of using GUI e.g. SQL Developer or MySQL interrogation tool. Command trouble is best for learning in addition to existent fun of writing SQL interrogation is exclusively on the ascendence 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 laid upwards (0.00 sec)
mysql> select * from MARKET;
+------+-------------------------+---------------+
| RIC | NAME | COUNTRY |
+------+-------------------------+---------------+
| T | Tokyo Stock Exchange | Japan |
| O | NASDAQ | U.S.A. |
| N | New York Stock Exchange | U.S.A. |
| BO | Mumbai Stock Exchange | India |
+------+-------------------------+---------------+
4 rows inwards laid upwards (0.00 sec)
SQL SELECT ascendence interrogation examples
hither are closed to of my favorite select clause examples which explore unlike ways i tin utilisation the select ascendence for reporting role in addition to display results.
1) Finding how many rows inwards 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 inwards laid upwards (0.00 sec)
3. Selecting few records based on closed to 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 simply give the refer 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 exhibit exclusively unique records it volition non exhibit whatsoever 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 in addition to OR Can hold upwards effectively used to combine 2 weather condition on WHERE clause in addition to gives you lot 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 seat whatsoever seat out of AND, OR weather condition on WHERE Clause, sometimes things choke quite slow when you lot combine AND, OR inwards SQL.
8. How to detect records which are non zero using keyword NULL in addition to IS NULL
NULL is really tricky inwards SQL; NULL agency anything which doesn't create got value. NULL is non "null" which volition hold upwards treated every bit text.To demonstrate this nosotros volition insert a Stock which is non listed on whatsoever 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 laid upwards (0.00 sec)
You See at that spot is exclusively i row who has LISTED_ON_EXCHANGE null, nosotros volition at i time encounter count using NULL in addition to IS NULL which volition verify this result.
mysql> select count(*) from STOCK where LISTED_ON_EXCHANGE IS NULL;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row inwards laid upwards (0.00 sec)
mysql> select count(*) from STOCK where LISTED_ON_EXCHANGE IS NOT NULL;
+----------+
| count(*) |
+----------+
| five |
+----------+
1 row inwards laid upwards (0.00 sec)
mysql> select count(*) from STOCK;
+----------+
| count(*) |
+----------+
| six |
+----------+
1 row inwards laid upwards (0.00 sec)
9. SELECT Statement using BETWEEN in addition to NOT BETWEEN
As the refer propose 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 in addition to NOT LIKE
LIKE is a pattern matching operator in addition to used to detect records which are non an exact check but in all likelihood 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 in addition to display records which are non in all likelihood 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 in addition to NOT IN
IN is closed to other useful SQL operator nosotros tin utilisation with SELECT. it provides a laid upwards of values which tin hold upwards 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 form records inwards the outcome laid upwards returned past times SELECT clause. By default, it listing inwards Ascending lodge but nosotros tin utilisation either ascending or descending using specifier ASC in addition to DESC.
mysql> select * from Stock lodge 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 you lot to select information from multiple tables. You tin generate a study where information is accumulated from unlike tables based on weather condition specified inwards Join statement.
Suppose you lot require to “display a listing of Records in addition to Name of Market where they are listed”. Here the refer of Stock inwards the STOCK tabular array spell the refer of telephone substitution 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 one thousand 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 interrogation tin also hold upwards 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 one thousand I s.LISTED_ON_EXCHANGE=m.RIC;
15. Calling business office 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 closed to basic calculation using SELECT clause e.g addition, subtraction, multiplication, partition etc.
mysql> select 1+2;
+-----+
| 1+2 |
+-----+
| three |
+-----+
17. SELECT information from i row till closed to other row similar Paging
If you lot are thinking to implement paging in addition to getting information from specified row you lot tin do this easily inwards Mysql past times using LIMIT clause.
mysql> select * from Stock lodge past times COMPANY LIMIT 0,2;
+--------+-------------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+--------+-------------------------+--------------------+
| GS.N | Goldman Sachs Group Inc | N |
| GOOG.O | Google Inc | O |
+--------+-------------------------+--------------------+
Here commencement parameter '0' says start from the commencement tape in addition to '2' says to acquire 2 records only.
18. Selecting information from the outcome of closed to other interrogation past times using derived table.
Sometimes information needed to gain terminal SELECT outcome comes from closed to other interrogation in addition to deed every bit 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 one thousand where s.LISTED_ON_EXCHANGE=m.RIC) t where RIC > 'G'
+---------+
| RIC |
+---------+
| GOOG.O |
| GS.N |
| INFY.BO |
+---------+
Some Important betoken virtually SELECT ascendence inwards SQL:
So Far nosotros create got seen unlike examples of a SELECT clause inwards SQL which volition enable you lot to accept amount wages of SELECT spell writing SQL queries. Here I create got listed closed to of import points which you lot should consider spell writing SQL interrogation non simply SELECT but with whatsoever other keyword also.
1) Most ofttimes nosotros utilisation SELECT Operator with WHERE Clause, endeavour to utilisation column which has the index on WHERE clause. Using a non-index column on WHERE clause tin tiresome your interrogation drastically in addition to trial would hold upwards to a greater extent than visible when your tabular array information increases. an instance with 1 Million records interrogation without index was taking 80-second spell later index it simply took .3 second, whopping 260% increment inwards speed.
2) If you lot don't require all columns, don’t utilisation the * wild card. SELECT interrogation with few columns is slightly faster than all columns.
3) If you lot are retrieving information from a large table, do a count (*) banking concern check earlier firing actual select query, this volition give you lot en guess of how many records you lot are virtually to acquire in addition to how much fourth dimension it could take.
4) You can innovate novel columns inwards the outcome laid upwards 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 upwards null. Don’t utilisation 'null' that volition hold upwards treated every bit text.
6) While writing SQL query, non simply SELECT, its expert exercise to write a keyword inwards the pocket-size instance and TABLES in addition to COLUMNS inwards capital. So that they volition stand upwards out from the whole interrogation in addition to makes the interrogation to a greater extent than readable.
That's all on SQL Select ascendence examples, I tried to encompass a expert seat out of select ascendence instance to render an overview what SELECT disceptation tin do. If you lot know whatsoever expert select instance inwards SQL delight share.
Further Learning
Difference betwixt truncate in addition to delete inwards SQL
Komentar
Posting Komentar