Cara menggunakan php sqlite parameterized query

Example

Return the number of products in the "Products" table:

SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;

Definition and Usage

The COUNT() function returns the number of records returned by a select query.

Note: NULL values are not counted.

Syntax

Parameter Values

Technical Details

The SELECT statement is used to make a simple query from a database or a complicated query against some criteria. A SELECT statement does not make any changes to the database.

A query or SELECT statement is a command which gives instructions to a database to produce certain information(s) from the table in its memory. The SELECT command starts with the keyword SELECT followed by a space and a list of comma separated columns. A * character can be used to select all the columns of a table. The table name comes after the FROM keyword and a white-space.

Syntax:

SELECT [ALL | DISTINCT] result [FROM table-list]
[WHERE expr]
[GROUP BY expr-list]
[HAVING expr]
[compound-op select]*
[ORDER BY sort-expr-list]
[LIMIT integer [(OFFSET|,) integer]]

Parameters:

ClauseOperation performInput ValueWHEREUsed to restrictExpression or conditionDISTINCTUsed to restrictList of columnsFROMJoinsList of tablesGROUP BYUsed to restrictList of columnsORDER BY List of columnsHAVINGUsed to restrictExpression or conditionLIMITUsed to restrictInteger valueOFFSETUsed to restrictInteger value

Here is the sample table employees.

Examples:

Retrieving all data:

If you want to retrieve all the rows from the locations table the following SQL can be used.

SELECT * FROM locations;

Relational Algebra Expression:

Relational Algebra Tree:

Here is the result.

sqlite> SELECT * FROM locations;
location_id  street_address  postal_code  city        state_province  country_id
-----------  --------------  -----------  ----------  --------------  ----------
1000         1297 Via Cola   989          Roma                        IT
1100         93091 Calle de  10934        Venice                      IT
1200         2017 Shinjuku-  1689         Tokyo       Tokyo Prefectu  JP
1300         9450 Kamiya-ch  6823         Hiroshima                   JP
1400         2014 Jabberwoc  26192        Southlake   Texas           US
1500         2011 Interiors  99236        South San   California      US
1600         2007 Zagora St  50090        South Brun  New Jersey      US
1700         2004 Charade R  98199        Seattle     Washington      US
1800         147 Spadina Av  M5V 2L7      Toronto     Ontario         CA
1900         6092 Boxwood S  YSW 9T2      Whitehorse  Yukon           CA
2000         40-5-12 Laogia  190518       Beijing                     CN
2100         1298 Vileparle  490231       Bombay      Maharashtra     IN
2200         12-98 Victoria  2901         Sydney      New South Wale  AU
2300         198 Clementi N  540198       Singapore                   SG
2400         8204 Arthur St               London                      UK
2500         Magdalen Centr  OX9 9ZB      Oxford      Oxford          UK
2600         9702 Chester R  9629850293   Stretford   Manchester      UK
2700         Schwanthalerst  80925        Munich      Bavaria         DE
2800         Rua Frei Canec  01307-002    Sao Paulo   Sao Paulo       BR
2900         20 Rue des Cor  1730         Geneva      Geneve          CH
3000         Murtenstrasse   3095         Bern        BE              CH
3100         Pieter Breughe  3029SK       Utrecht     Utrecht         NL
3200         Mariano Escobe  11932        Mexico Cit  Distrito Feder  MX

Here in the above example the * have been used to indicate ALL the records from the locations table.

if FROM clause not used:

An expression can only be executed:

sqlite> SELECT 6+15;
6+15
----------
21

or

sqlite> SELECT 5*15;
5*15
----------
75

or

sqlite> SELECT 5+2-3*4/6;
5+2-3*4/6
----------
5

Select specific columns:

The SELECT statement can be used to retrieve specific columns. The column names follow the SELECT word.

If you want to retrieve the columns street_address and city from the location table the following SQL can be used.

SELECT street_address, city FROM locations;

Relational Algebra Expression:

Relational Algebra Tree:

Here is the result.

sqlite> SELECT street_address, city FROM locations;
street_address                                      city
--------------------------------------------------  ----------
1297 Via Cola di Rie                                Roma
93091 Calle della Testa                             Venice
2017 Shinjuku-ku                                    Tokyo
9450 Kamiya-cho                                     Hiroshima
2014 Jabberwocky Rd                                 Southlake
2011 Interiors Blvd                                 South San
2007 Zagora St                                      South Brun
2004 Charade Rd                                     Seattle
147 Spadina Ave                                     Toronto
6092 Boxwood St                                     Whitehorse
40-5-12 Laogianggen                                 Beijing
1298 Vileparle (E)                                  Bombay
12-98 Victoria Street                               Sydney
198 Clementi North                                  Singapore
8204 Arthur St                                      London
Magdalen Centre, The Oxford Science Park            Oxford
9702 Chester Road                                   Stretford
Schwanthalerstr. 7031                               Munich
Rua Frei Caneca 1360                                Sao Paulo
20 Rue des Corps-Saints                             Geneva
Murtenstrasse 921                                   Bern
Pieter Breughelstraat 837                           Utrecht
Mariano Escobedo 9991                               Mexico Cit

Select columns using alias:

In the FROM list with a select statement, one or more table names can be included separated by a comma. The tables using followed by the FROM clause have an optional alias name that can be used to mention individual column names in the result.Where the table name in full can be used to qualify columns of a table, the aliases are not used.

sqlite> SELECT regions.region_name FROM regions;
region_name
-------------------------
REGION_NAME
Europe
Americas
Asia
Middle East and Africa

or

sqlite> SELECT z.region_name FROM regions z;
region_name
-------------------------
REGION_NAME
Europe
Americas
Asia
Middle East and Africa

Here in the above, the two SELECT statements are identical, the latter uses a table alias z for regions.

Renaming column names :

Sometimes we use the column name as short form against a keyword for that column. So the return value for that column will appear in short from, and sometimes we required to display it in full name. It can be done by using the AS clause followed the column name or any expression with the select statement.

If you want to retrieve the region_name column from the regions table with the customize column head "Name of the Region" the following SQL can be used. If the customize heading contain more than one words then it will be within a quote unquote unless it can be used independently.

Here is the example.

SELECT region_name AS "Name of the Region" FROM regions;

Here is the result.

Name of the Region
-------------------------
REGION_NAME
Europe
Americas
Asia
Middle East and Africa

Here in the following example, the customize heading contain more than one words and it used without quote unquote, and this statement occurs an error.

sqlite> SELECT region_name AS Name of the Region FROM regions;
Error: near "of": syntax error

Limit Output:

When we want to retrieve a limited amount of data from a large database we can use the LIMIT clause to limit the data amount returned by the statement.

If we want to retrieve the first ten records from the locations table, the following SQL can be used.

SELECT * FROM locations;

Here is the result.

SELECT * FROM locations;

If we want to retrieve five records skipping the first four from the locations table, the following SQL can be used.

SELECT * FROM locations;

Here is the results.

SELECT * FROM locations;

Select using OFFSET clause:

The OFFSET clause after LIMIT specifies how many rows to skip at the beginning of the result set.

Here is the example.

SELECT * FROM locations;

Here is the result is same as the previous result.

SELECT * FROM locations;

WHERE clause

WHERE is a powerful filter A WHERE clause can let you take exactly the piece of the data you want. It sets the conditions for the SELECT, and the query will return only those rows that match the conditions. It provides you with a great degree of control over the conditions to impose restrictions on the dataset returned by a SELECT with which to include (or exclude) rows in (or from) the result. It is used both to limit the number of rows returned and to indicate a relationship used to join two tables together.

If you only wanted to see cities from Canada, the following SQL can be used.

SELECT * FROM locations;

Relational Algebra Expression:

Relational Algebra Tree:

Here is the result for the above statement.

SELECT * FROM locations;

Here some operators can be used with WHERE clause. Here are the operators below-

operatordescription=Equal<>Not equal*>Greater than<Less than>=Greater than or equal<=Less than or equal

Here is a sample table agents.

SELECT * FROM locations;

If you only wanted to see agent_code, agent_name, and commission from the agents who gets the commission less than .15% , the following SQL can be used.

SELECT * FROM locations;

Here is the result for the above statement.

sqlite> SELECT * FROM locations;
location_id  street_address  postal_code  city        state_province  country_id
-----------  --------------  -----------  ----------  --------------  ----------
1000         1297 Via Cola   989          Roma                        IT
1100         93091 Calle de  10934        Venice                      IT
1200         2017 Shinjuku-  1689         Tokyo       Tokyo Prefectu  JP
1300         9450 Kamiya-ch  6823         Hiroshima                   JP
1400         2014 Jabberwoc  26192        Southlake   Texas           US
1500         2011 Interiors  99236        South San   California      US
1600         2007 Zagora St  50090        South Brun  New Jersey      US
1700         2004 Charade R  98199        Seattle     Washington      US
1800         147 Spadina Av  M5V 2L7      Toronto     Ontario         CA
1900         6092 Boxwood S  YSW 9T2      Whitehorse  Yukon           CA
2000         40-5-12 Laogia  190518       Beijing                     CN
2100         1298 Vileparle  490231       Bombay      Maharashtra     IN
2200         12-98 Victoria  2901         Sydney      New South Wale  AU
2300         198 Clementi N  540198       Singapore                   SG
2400         8204 Arthur St               London                      UK
2500         Magdalen Centr  OX9 9ZB      Oxford      Oxford          UK
2600         9702 Chester R  9629850293   Stretford   Manchester      UK
2700         Schwanthalerst  80925        Munich      Bavaria         DE
2800         Rua Frei Canec  01307-002    Sao Paulo   Sao Paulo       BR
2900         20 Rue des Cor  1730         Geneva      Geneve          CH
3000         Murtenstrasse   3095         Bern        BE              CH
3100         Pieter Breughe  3029SK       Utrecht     Utrecht         NL
3200         Mariano Escobe  11932        Mexico Cit  Distrito Feder  MX

If you only wanted to see agent_code, agent_name, and commission from the agents who gets the commission more than .12% , the following SQL can be used.

sqlite> SELECT * FROM locations;
location_id  street_address  postal_code  city        state_province  country_id
-----------  --------------  -----------  ----------  --------------  ----------
1000         1297 Via Cola   989          Roma                        IT
1100         93091 Calle de  10934        Venice                      IT
1200         2017 Shinjuku-  1689         Tokyo       Tokyo Prefectu  JP
1300         9450 Kamiya-ch  6823         Hiroshima                   JP
1400         2014 Jabberwoc  26192        Southlake   Texas           US
1500         2011 Interiors  99236        South San   California      US
1600         2007 Zagora St  50090        South Brun  New Jersey      US
1700         2004 Charade R  98199        Seattle     Washington      US
1800         147 Spadina Av  M5V 2L7      Toronto     Ontario         CA
1900         6092 Boxwood S  YSW 9T2      Whitehorse  Yukon           CA
2000         40-5-12 Laogia  190518       Beijing                     CN
2100         1298 Vileparle  490231       Bombay      Maharashtra     IN
2200         12-98 Victoria  2901         Sydney      New South Wale  AU
2300         198 Clementi N  540198       Singapore                   SG
2400         8204 Arthur St               London                      UK
2500         Magdalen Centr  OX9 9ZB      Oxford      Oxford          UK
2600         9702 Chester R  9629850293   Stretford   Manchester      UK
2700         Schwanthalerst  80925        Munich      Bavaria         DE
2800         Rua Frei Canec  01307-002    Sao Paulo   Sao Paulo       BR
2900         20 Rue des Cor  1730         Geneva      Geneve          CH
3000         Murtenstrasse   3095         Bern        BE              CH
3100         Pieter Breughe  3029SK       Utrecht     Utrecht         NL
3200         Mariano Escobe  11932        Mexico Cit  Distrito Feder  MX

Here is the result for the above statement.

sqlite> SELECT * FROM locations;
location_id  street_address  postal_code  city        state_province  country_id
-----------  --------------  -----------  ----------  --------------  ----------
1000         1297 Via Cola   989          Roma                        IT
1100         93091 Calle de  10934        Venice                      IT
1200         2017 Shinjuku-  1689         Tokyo       Tokyo Prefectu  JP
1300         9450 Kamiya-ch  6823         Hiroshima                   JP
1400         2014 Jabberwoc  26192        Southlake   Texas           US
1500         2011 Interiors  99236        South San   California      US
1600         2007 Zagora St  50090        South Brun  New Jersey      US
1700         2004 Charade R  98199        Seattle     Washington      US
1800         147 Spadina Av  M5V 2L7      Toronto     Ontario         CA
1900         6092 Boxwood S  YSW 9T2      Whitehorse  Yukon           CA
2000         40-5-12 Laogia  190518       Beijing                     CN
2100         1298 Vileparle  490231       Bombay      Maharashtra     IN
2200         12-98 Victoria  2901         Sydney      New South Wale  AU
2300         198 Clementi N  540198       Singapore                   SG
2400         8204 Arthur St               London                      UK
2500         Magdalen Centr  OX9 9ZB      Oxford      Oxford          UK
2600         9702 Chester R  9629850293   Stretford   Manchester      UK
2700         Schwanthalerst  80925        Munich      Bavaria         DE
2800         Rua Frei Canec  01307-002    Sao Paulo   Sao Paulo       BR
2900         20 Rue des Cor  1730         Geneva      Geneve          CH
3000         Murtenstrasse   3095         Bern        BE              CH
3100         Pieter Breughe  3029SK       Utrecht     Utrecht         NL
3200         Mariano Escobe  11932        Mexico Cit  Distrito Feder  MX

If you only want to see cities from Canada, the following SQL can be used.

sqlite> SELECT * FROM locations;
location_id  street_address  postal_code  city        state_province  country_id
-----------  --------------  -----------  ----------  --------------  ----------
1000         1297 Via Cola   989          Roma                        IT
1100         93091 Calle de  10934        Venice                      IT
1200         2017 Shinjuku-  1689         Tokyo       Tokyo Prefectu  JP
1300         9450 Kamiya-ch  6823         Hiroshima                   JP
1400         2014 Jabberwoc  26192        Southlake   Texas           US
1500         2011 Interiors  99236        South San   California      US
1600         2007 Zagora St  50090        South Brun  New Jersey      US
1700         2004 Charade R  98199        Seattle     Washington      US
1800         147 Spadina Av  M5V 2L7      Toronto     Ontario         CA
1900         6092 Boxwood S  YSW 9T2      Whitehorse  Yukon           CA
2000         40-5-12 Laogia  190518       Beijing                     CN
2100         1298 Vileparle  490231       Bombay      Maharashtra     IN
2200         12-98 Victoria  2901         Sydney      New South Wale  AU
2300         198 Clementi N  540198       Singapore                   SG
2400         8204 Arthur St               London                      UK
2500         Magdalen Centr  OX9 9ZB      Oxford      Oxford          UK
2600         9702 Chester R  9629850293   Stretford   Manchester      UK
2700         Schwanthalerst  80925        Munich      Bavaria         DE
2800         Rua Frei Canec  01307-002    Sao Paulo   Sao Paulo       BR
2900         20 Rue des Cor  1730         Geneva      Geneve          CH
3000         Murtenstrasse   3095         Bern        BE              CH
3100         Pieter Breughe  3029SK       Utrecht     Utrecht         NL
3200         Mariano Escobe  11932        Mexico Cit  Distrito Feder  MX

Relational Algebra Expression:

Relational Algebra Tree:

Here is the result for the above statement.

SELECT * FROM locations;

WHERE with AND and OR

You can using AND and OR with WHERE clause for filter the rows from the tables.

Here is the sample table employees:

If you want to see the employee_id, first_name, last_name, job_id, manager_id and departments who belongs to the department_id 80, the following SQL can be used.

sqlite> SELECT * FROM locations;
location_id  street_address  postal_code  city        state_province  country_id
-----------  --------------  -----------  ----------  --------------  ----------
1000         1297 Via Cola   989          Roma                        IT
1100         93091 Calle de  10934        Venice                      IT
1200         2017 Shinjuku-  1689         Tokyo       Tokyo Prefectu  JP
1300         9450 Kamiya-ch  6823         Hiroshima                   JP
1400         2014 Jabberwoc  26192        Southlake   Texas           US
1500         2011 Interiors  99236        South San   California      US
1600         2007 Zagora St  50090        South Brun  New Jersey      US
1700         2004 Charade R  98199        Seattle     Washington      US
1800         147 Spadina Av  M5V 2L7      Toronto     Ontario         CA
1900         6092 Boxwood S  YSW 9T2      Whitehorse  Yukon           CA
2000         40-5-12 Laogia  190518       Beijing                     CN
2100         1298 Vileparle  490231       Bombay      Maharashtra     IN
2200         12-98 Victoria  2901         Sydney      New South Wale  AU
2300         198 Clementi N  540198       Singapore                   SG
2400         8204 Arthur St               London                      UK
2500         Magdalen Centr  OX9 9ZB      Oxford      Oxford          UK
2600         9702 Chester R  9629850293   Stretford   Manchester      UK
2700         Schwanthalerst  80925        Munich      Bavaria         DE
2800         Rua Frei Canec  01307-002    Sao Paulo   Sao Paulo       BR
2900         20 Rue des Cor  1730         Geneva      Geneve          CH
3000         Murtenstrasse   3095         Bern        BE              CH
3100         Pieter Breughe  3029SK       Utrecht     Utrecht         NL
3200         Mariano Escobe  11932        Mexico Cit  Distrito Feder  MX

Relational Algebra Expression:

Relational Algebra Tree:

Here is the result.

Sample Output:

sqlite> SELECT * FROM locations;
location_id  street_address  postal_code  city        state_province  country_id
-----------  --------------  -----------  ----------  --------------  ----------
1000         1297 Via Cola   989          Roma                        IT
1100         93091 Calle de  10934        Venice                      IT
1200         2017 Shinjuku-  1689         Tokyo       Tokyo Prefectu  JP
1300         9450 Kamiya-ch  6823         Hiroshima                   JP
1400         2014 Jabberwoc  26192        Southlake   Texas           US
1500         2011 Interiors  99236        South San   California      US
1600         2007 Zagora St  50090        South Brun  New Jersey      US
1700         2004 Charade R  98199        Seattle     Washington      US
1800         147 Spadina Av  M5V 2L7      Toronto     Ontario         CA
1900         6092 Boxwood S  YSW 9T2      Whitehorse  Yukon           CA
2000         40-5-12 Laogia  190518       Beijing                     CN
2100         1298 Vileparle  490231       Bombay      Maharashtra     IN
2200         12-98 Victoria  2901         Sydney      New South Wale  AU
2300         198 Clementi N  540198       Singapore                   SG
2400         8204 Arthur St               London                      UK
2500         Magdalen Centr  OX9 9ZB      Oxford      Oxford          UK
2600         9702 Chester R  9629850293   Stretford   Manchester      UK
2700         Schwanthalerst  80925        Munich      Bavaria         DE
2800         Rua Frei Canec  01307-002    Sao Paulo   Sao Paulo       BR
2900         20 Rue des Cor  1730         Geneva      Geneve          CH
3000         Murtenstrasse   3095         Bern        BE              CH
3100         Pieter Breughe  3029SK       Utrecht     Utrecht         NL
3200         Mariano Escobe  11932        Mexico Cit  Distrito Feder  MX

Here in the another example, if you want to see the employee_id, first_name, last_name, job_id, manager_id and departments who belongs to the department_id 80, and must be under the manager whose ID is 100, the following SQL can be used.

sqlite> SELECT * FROM locations;
location_id  street_address  postal_code  city        state_province  country_id
-----------  --------------  -----------  ----------  --------------  ----------
1000         1297 Via Cola   989          Roma                        IT
1100         93091 Calle de  10934        Venice                      IT
1200         2017 Shinjuku-  1689         Tokyo       Tokyo Prefectu  JP
1300         9450 Kamiya-ch  6823         Hiroshima                   JP
1400         2014 Jabberwoc  26192        Southlake   Texas           US
1500         2011 Interiors  99236        South San   California      US
1600         2007 Zagora St  50090        South Brun  New Jersey      US
1700         2004 Charade R  98199        Seattle     Washington      US
1800         147 Spadina Av  M5V 2L7      Toronto     Ontario         CA
1900         6092 Boxwood S  YSW 9T2      Whitehorse  Yukon           CA
2000         40-5-12 Laogia  190518       Beijing                     CN
2100         1298 Vileparle  490231       Bombay      Maharashtra     IN
2200         12-98 Victoria  2901         Sydney      New South Wale  AU
2300         198 Clementi N  540198       Singapore                   SG
2400         8204 Arthur St               London                      UK
2500         Magdalen Centr  OX9 9ZB      Oxford      Oxford          UK
2600         9702 Chester R  9629850293   Stretford   Manchester      UK
2700         Schwanthalerst  80925        Munich      Bavaria         DE
2800         Rua Frei Canec  01307-002    Sao Paulo   Sao Paulo       BR
2900         20 Rue des Cor  1730         Geneva      Geneve          CH
3000         Murtenstrasse   3095         Bern        BE              CH
3100         Pieter Breughe  3029SK       Utrecht     Utrecht         NL
3200         Mariano Escobe  11932        Mexico Cit  Distrito Feder  MX

Relational Algebra Expression:

Relational Algebra Tree:

Here is the result.

sqlite> SELECT * FROM locations;
location_id  street_address  postal_code  city        state_province  country_id
-----------  --------------  -----------  ----------  --------------  ----------
1000         1297 Via Cola   989          Roma                        IT
1100         93091 Calle de  10934        Venice                      IT
1200         2017 Shinjuku-  1689         Tokyo       Tokyo Prefectu  JP
1300         9450 Kamiya-ch  6823         Hiroshima                   JP
1400         2014 Jabberwoc  26192        Southlake   Texas           US
1500         2011 Interiors  99236        South San   California      US
1600         2007 Zagora St  50090        South Brun  New Jersey      US
1700         2004 Charade R  98199        Seattle     Washington      US
1800         147 Spadina Av  M5V 2L7      Toronto     Ontario         CA
1900         6092 Boxwood S  YSW 9T2      Whitehorse  Yukon           CA
2000         40-5-12 Laogia  190518       Beijing                     CN
2100         1298 Vileparle  490231       Bombay      Maharashtra     IN
2200         12-98 Victoria  2901         Sydney      New South Wale  AU
2300         198 Clementi N  540198       Singapore                   SG
2400         8204 Arthur St               London                      UK
2500         Magdalen Centr  OX9 9ZB      Oxford      Oxford          UK
2600         9702 Chester R  9629850293   Stretford   Manchester      UK
2700         Schwanthalerst  80925        Munich      Bavaria         DE
2800         Rua Frei Canec  01307-002    Sao Paulo   Sao Paulo       BR
2900         20 Rue des Cor  1730         Geneva      Geneve          CH
3000         Murtenstrasse   3095         Bern        BE              CH
3100         Pieter Breughe  3029SK       Utrecht     Utrecht         NL
3200         Mariano Escobe  11932        Mexico Cit  Distrito Feder  MX

Now, lets find those employees, whose job ID may be CLERK or may be in the department which ID is 80 and must be under the manager who is the owner of the ID 147.

Here is the statement.

sqlite> SELECT * FROM locations;
location_id  street_address  postal_code  city        state_province  country_id
-----------  --------------  -----------  ----------  --------------  ----------
1000         1297 Via Cola   989          Roma                        IT
1100         93091 Calle de  10934        Venice                      IT
1200         2017 Shinjuku-  1689         Tokyo       Tokyo Prefectu  JP
1300         9450 Kamiya-ch  6823         Hiroshima                   JP
1400         2014 Jabberwoc  26192        Southlake   Texas           US
1500         2011 Interiors  99236        South San   California      US
1600         2007 Zagora St  50090        South Brun  New Jersey      US
1700         2004 Charade R  98199        Seattle     Washington      US
1800         147 Spadina Av  M5V 2L7      Toronto     Ontario         CA
1900         6092 Boxwood S  YSW 9T2      Whitehorse  Yukon           CA
2000         40-5-12 Laogia  190518       Beijing                     CN
2100         1298 Vileparle  490231       Bombay      Maharashtra     IN
2200         12-98 Victoria  2901         Sydney      New South Wale  AU
2300         198 Clementi N  540198       Singapore                   SG
2400         8204 Arthur St               London                      UK
2500         Magdalen Centr  OX9 9ZB      Oxford      Oxford          UK
2600         9702 Chester R  9629850293   Stretford   Manchester      UK
2700         Schwanthalerst  80925        Munich      Bavaria         DE
2800         Rua Frei Canec  01307-002    Sao Paulo   Sao Paulo       BR
2900         20 Rue des Cor  1730         Geneva      Geneve          CH
3000         Murtenstrasse   3095         Bern        BE              CH
3100         Pieter Breughe  3029SK       Utrecht     Utrecht         NL
3200         Mariano Escobe  11932        Mexico Cit  Distrito Feder  MX

Relational Algebra Expression:

Relational Algebra Tree:

Here is the result of the above statement.

sqlite> SELECT 6+15;
6+15
----------
21

or

sqlite> SELECT 5*15;
5*15
----------
75

or

sqlite> SELECT 5+2-3*4/6;
5+2-3*4/6
----------
5

The anove result shows that, the 'SA_REP' job_id also appear along with 'SH_CLERK', because the implimentation of OR clause and manager_id 147 appear for only those departments that have the ID 80.

The AND operator has a higher precedence than the OR operator, which means it gets evaluated first. So, in effect, the query really looks like the below:

sqlite> SELECT 6+15;
6+15
----------
21

or

sqlite> SELECT 5*15;
5*15
----------
75

or

sqlite> SELECT 5+2-3*4/6;
5+2-3*4/6
----------
5

Relational Algebra Expression:

Relational Algebra Tree:

Which selects all employees for 'SH_CLERK' and only those employees for department no 80 who have under the manager whose id is 147. The parentheses can be used to clarify the query and actually get the high rollers we wanted.

DISTINCT

The DISTINCT keyword is used to select only unique items from the result set or it can be said that, it eliminates the duplicates rows from the result of the query.

If you want to get the unique department_id and manager_id combination , the following SQL can be used.

sqlite> SELECT 6+15;
6+15
----------
21

or

sqlite> SELECT 5*15;
5*15
----------
75

or

sqlite> SELECT 5+2-3*4/6;
5+2-3*4/6
----------
5

Relational Algebra Expression:

Relational Algebra Tree:

Here is the result.

sqlite> SELECT 6+15;
6+15
----------
21

or

sqlite> SELECT 5*15;
5*15
----------
75

or

sqlite> SELECT 5+2-3*4/6;
5+2-3*4/6
----------
5

The above result shows only the unique combination of department_id and manager_id have appeared. Now, if we add the job_id column with above query, look what will happen.

sqlite> SELECT 6+15;
6+15
----------
21

or

sqlite> SELECT 5*15;
5*15
----------
75

or

sqlite> SELECT 5+2-3*4/6;
5+2-3*4/6
----------
5

Relational Algebra Expression:

Relational Algebra Tree:

Here is the output.

sqlite> SELECT 6+15;
6+15
----------
21

or

sqlite> SELECT 5*15;
5*15
----------
75

or

sqlite> SELECT 5+2-3*4/6;
5+2-3*4/6
----------
5

Here in the above result, the unique combination made by three columns and a naturally number of rows become changed, and here more rows appear than before.

ORDER BY

The ORDER BY clause is used to sort the returned data set. The ORDER BY clause is followed by the column on which we do the sorting. The default direction for ORDER BY is ascending; results are ordered from smallest amount to greatest. To specify the direction of the ORDER BY, use the DESC or ASC keyword:

If you want to sort the result according to salary for those employees which department no. is below 50, the following SQL can be used.

sqlite> SELECT 6+15;
6+15
----------
21

or

sqlite> SELECT 5*15;
5*15
----------
75

or

sqlite> SELECT 5+2-3*4/6;
5+2-3*4/6
----------
5

Relational Algebra Expression:

Relational Algebra Tree:

Here is the result.

sqlite> SELECT 6+15;
6+15
----------
21

or

sqlite> SELECT 5*15;
5*15
----------
75

or

sqlite> SELECT 5+2-3*4/6;
5+2-3*4/6
----------
5

If you want to sort the result according to salary in descending order for those employees which department no. is below 50 , the following SQL can be used.

sqlite> SELECT 6+15;
6+15
----------
21

or

sqlite> SELECT 5*15;
5*15
----------
75

or

sqlite> SELECT 5+2-3*4/6;
5+2-3*4/6
----------
5

Relational Algebra Expression:

Relational Algebra Tree:

Here is the result.

sqlite> SELECT 6+15;
6+15
----------
21

or

sqlite> SELECT 5*15;
5*15
----------
75

or

sqlite> SELECT 5+2-3*4/6;
5+2-3*4/6
----------
5

The result of the query can be ordered by more than one column. Rows with the same value for the first column of the ORDER BY are further ordered by the additional column(s):

Here is the example.

If you want to order the result firstly by department_id column in ascending order and after that, which departments are same they will be ordered themselves descendingly according to the salary, the following SQL can be used.

SELECT street_address, city FROM locations;

Relational Algebra Expression:

Relational Algebra Tree:

Here is the result.

SELECT street_address, city FROM locations;

It seems to the above result that, depertment_id 20 and 30 have more than one rows, and they have been arranged descendingly according to the salary, that is the greatest salary will come first.

ORDER BY with DISTINCT

If you want to get the unique department_id, manager_id and job_id combination by an order according to department ascendingly, the following SQL can be used.

SELECT street_address, city FROM locations;

Relational Algebra Expression:

Relational Algebra Tree:

Here is the output.

SELECT street_address, city FROM locations;

GROUP BY

The GROUP BY clause is used to combine database records with identical values into a single record. It is often used with the aggregation functions.

The GROUP BY clause is used to aggregate data into a single row where the value of one or more specified columns is repeated. It is used to combine database records with identical values into a single record. It is often used with the aggregation functions. The GROUP BY clause takes a list of expressions, usually column names from the result, and aggregates data for each expression. The job of the GROUP BY clause is to compute aggregates over an entire result, and can also split that result into groups of rows with specified values, and compute aggregates on each group, in a single step.

The GROUP BY clause sits in between the WHERE clause and the SELECT clause. It gets the output from WHERE and splits it into groups of rows that share a common value (or values) for a specific column (or columns). These groups are then passed to the SELECT clause.

The grouping process performed by two steps. First, the GROUP BY expression arranged the table rows into different groups. Once the groups are defined, the SELECT header defines how those groups are flattened down into a single row. The resulting table will have one row for each group.

If you want to find the total salary required for a month, which is on or above one lacs, for the designation that holding the ID is below 80, by an order of largest to smallest salary the following SQL can be used.