10 Illustration Queries Of Sql Choose Command
The Select ascendancy inward SQL is i of the most powerful in addition to heavily used commands. This is I guess the commencement ascendancy 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 in addition to 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 inward WHERE clause, select all columns using the wild carte du jour (*) or alone selecting a few columns past times explicitly declaring them inward a query.
In this SELECT SQL ascendancy tutorial, nosotros volition run into to a greater extent than or less examples of select command or Select Statement in addition to volition write SQL queries to demonstrate the result. We volition work next tabular array in addition to information for our SQL inquiry examples, i tabular array stand upwards for Stocks listed inward diverse marketplace in addition to to a greater extent than or less other tabular array contains Details of marketplace e.g. Country. MySQL is my favorite RDBMS in addition to nifty for learning work y'all tin download MySQL in addition to start working on it. My proffer is to work ascendancy delineate of piece of work interface for writing queries instead of using GUI e.g. SQL Developer or MySQL inquiry tool. Command delineate of piece of work is best for learning in addition to existent fun of writing SQL inquiry is alone on the ascendancy 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 develop (0.00 sec)
mysql> select * from MARKET;
+------+-------------------------+---------------+
| RIC | NAME | COUNTRY |
+------+-------------------------+---------------+
| T | Tokyo Stock Exchange | Japan |
| O | NASDAQ | the States |
| N | New York Stock Exchange | the States |
| BO | Mumbai Stock Exchange | India |
+------+-------------------------+---------------+
4 rows inward develop (0.00 sec)
SQL SELECT ascendancy inquiry examples
hither are to a greater extent than or less of my favorite select clause examples which explore unlike ways i tin work the select ascendancy for reporting work in addition to display results.
1) Finding how many rows inward 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 inward develop (0.00 sec)
3. Selecting few records based on to a greater extent than or less 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 laissez passer on the elevate 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 alone 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 in addition to OR Can move effectively used to combine ii atmospheric condition on WHERE clause in addition to 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 lay whatever lay out of AND, OR atmospheric condition on WHERE Clause, sometimes things drib dead quite tardily when y'all combine AND, OR inward SQL.
8. How to notice records which are non zero using keyword NULL in addition to IS NULL
NULL is rattling tricky inward SQL; NULL agency anything which doesn't get got value. NULL is non "null" which volition move treated every bit 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 develop (0.00 sec)
You See at that spot is alone i row who has LISTED_ON_EXCHANGE null, nosotros volition straightaway run into 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 inward develop (0.00 sec)
mysql> select count(*) from STOCK where LISTED_ON_EXCHANGE IS NOT NULL;
+----------+
| count(*) |
+----------+
| v |
+----------+
1 row inward develop (0.00 sec)
mysql> select count(*) from STOCK;
+----------+
| count(*) |
+----------+
| vi |
+----------+
1 row inward develop (0.00 sec)
9. SELECT Statement using BETWEEN in addition to NOT BETWEEN
As the elevate 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 inward SQL queries using LIKE in addition to NOT LIKE
LIKE is a blueprint matching operator in addition to used to notice records which are non an exact jibe but probably 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 probably 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 to a greater extent than or less other useful SQL operator nosotros tin work amongst SELECT. it provides a develop of values which tin move 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 past times is used to form records inward the termination develop returned past times SELECT clause. By default, it listing inward Ascending social club but nosotros tin work either ascending or descending using specifier ASC in addition to 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 inward SQL
Join inward 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 unlike tables based on atmospheric condition specified inward Join statement.
Suppose y'all require to “display a listing of Records in addition to Name of Market where they are listed”. Here the elevate of Stock inward the STOCK tabular array piece the elevate of central inward 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 inquiry tin also move written past times 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 one thousand I s.LISTED_ON_EXCHANGE=m.RIC;
15. Calling business office on SELECT clause e.g. displaying electrical current date
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2011-10-13 10:25:47 |
+---------------------+
16. Doing calculation using SELECT CLAUSE
You tin perform to a greater extent than or less basic calculation using SELECT clause e.g addition, subtraction, multiplication, partitioning etc.
mysql> select 1+2;
+-----+
| 1+2 |
+-----+
| three |
+-----+
17. SELECT information from i row till to a greater extent than or less other row similar Paging
If y'all are thinking to implement paging in addition to getting information from specified row y'all tin do this easily inward 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 commencement parameter '0' says start from the commencement tape in addition to '2' says to acquire 2 records only.
18. Selecting information from the termination of to a greater extent than or less other inquiry past times using derived table.
Sometimes information needed to create in conclusion SELECT termination comes from to a greater extent than or less other inquiry 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 signal nearly SELECT ascendancy inward SQL:
So Far nosotros get got seen unlike examples of a SELECT clause inward SQL which volition enable y'all to accept amount wages of SELECT piece writing SQL queries. Here I get got listed to a greater extent than or less of import points which y'all should consider piece writing SQL inquiry non simply SELECT but with whatever other keyword also.
1) Most oftentimes nosotros work SELECT Operator with WHERE Clause, endeavour to work column which has the index on WHERE clause. Using a non-index column on WHERE clause tin deadening your inquiry drastically in addition to trial would move 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 piece afterwards index it simply took .3 second, whopping 260% increment inward speed.
2) If y'all don't require all columns, don’t work 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 laissez passer on y'all en gauge of how many records y'all are nearly to acquire in addition to how much fourth dimension it could take.
4) You can innovate novel columns inward the termination develop of SELECT Query past times 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 move null. Don’t work 'null' that volition move treated every bit text.
6) While writing SQL query, non simply SELECT, its proficient do to write a keyword inward the small-scale instance and TABLES in addition to COLUMNS inward capital. So that they volition stand upwards out from the whole inquiry in addition to makes the inquiry to a greater extent than readable.
That's all on SQL Select ascendancy examples, I tried to embrace a proficient lay out of select ascendancy instance to supply an overview what SELECT contention tin do. If y'all know whatever proficient select instance inward SQL delight share.
Further Learning
Difference betwixt truncate in addition to delete inward SQL
Belum ada Komentar untuk "10 Illustration Queries Of Sql Choose Command"
Posting Komentar