LEARNING PHP AND MYSQL (2015)

Queries

· Query is the most commonly used and one of the key operations in SQL.

· It is declared using SELECT statement.

· It provides user with various methods to describe and show his/her desired data.

· An ‘* ‘is used to show that the query should return all columns of the particular that is queried.

· It is a request information method used in the SQL therefore it is a key factor and mostly the back bone of the SQL.

MySQL query window

Various clauses and keywords used in SQL are as follows.

· FROM clause- This shows from where or which table to retrieve data from.

· Where clause- This includes a predicate for comparison this helps in restricting of the row that is been returned by the query.

· Group by clause- Used to project rows that are identical or have common value.

· Having clause- It is used in filtration of row where it can be combined with Group by clause.

· Order by clause- It sorts or arranges the data in a form it’s needed to be arranged.

Example

SELECT Book title AS TIM,

COUNT (*) AS Authors

FROM Book

JOIN Book_TIM

ON Book . isbn = Book_author . isbn

GROUP BY Book . title

Output

Sub-Queries

These are nested queries. A query that is in another query and this is embedded within the where clause. Sub-Queries are usually used to return data which will be further used in the main query.

Rules of Sub- queries

· It has to be enclosed within parenthesis.

· It can have only one column in SELECT clause.

· It is not allowed to use ODER BY query whereas instead of that GROUP BY is used.

· It cannot have immediate enclosure in a set function.

Use of sub-queries

· It is used to join one table with other tables operation.

· This also provides faster remedies. This provides a hierarchical execution.

· Sub query can use values from query outside, these are correlated sub query.

Sub-Query

Example

SELECT isbn,

Name,

Price,

FROM Book

WHERE price < (SELECT AVG ( PRICE ) FROM Book)

ORDER BY title;

3VL

3VL stands for three-valued logic to SQL. There are 3 fragments of 3VL which are AND, OR and NOT. There tables which are followed in 3VL.