Jul 1, 2020

MySQL Practice Example: Part-1 | MySQL Assignment Help

Querying data Using MySQL “SELECT”

“SELECT” statement is used to read data from one or more table.

Syntax:

SELECT select_list
 FROM table_name;

Below the “Employee” table which has four data columns

It contains the following records

Selecting Single column(first name) from Employee table

Query:

SELECT LastName
 FROM Emoloyee;

Selecting multiple columns(first name, last name)

Query:

SELECT LastName, FirstName
 FROM Emoloyee;

Selecting all data(using *) from table

Query:

SELECT *
 FROM Emoloyee;

Sorting data Using MySQL “ORDER BY” clause

When you use the SELECT statement to query data from a table, the result set is not sorted. It means that the rows in the result set can be in any order.

Syntax:

SELECT
  select_list
 FROM
  table_name
 ORDER BY
  column1 [ASC|DESC],
  column2 [ASC|DESC],
  ...

You use ASC to sort the result set in ascending order and DESC to sort the result set in descending order

Example:

SELECT
  FirstName,LastName
 FROM
  Employee
 ORDER BY
  FirstName;

By default(If not use any ASC and DESC ) it sorted in ascending order

Sort In Descending order

Example:

SELECT
  FirstName,LastName
 FROM
  Employee
 ORDER BY
  FirstName DESC;

Sort In Ascending order

Example:

SELECT
  FirstName,LastName
 FROM
  Employee
 ORDER BY
  FirstName ASC;

Using MySQL ORDER BY to sort a result set by an expression

Let suppose new database table “order”

Syntax:

SELECT
  orderNumber,
  Order_Line,
  quantityOrdered * price
 FROM
  Order
 ORDER BY
  quantityOrdered * price DESC;

Filtering Data Using MySQL “WHERE, AND, OR, IN, etc” clause

"Where” Clause:

The WHERE clause allows you to specify a search condition for the rows returned by a query.

Syntax:

SELECT
  select_list
 FROM
  table_name
 WHERE
  search_condition;

Let we working with the Employee table which is given below

It contains the following records

Query:

SELECT
  LastName,
  FirstName
 
 FROM
  employees
 WHERE
  JobTitle = 'Sales';
 

Using MySQL WHERE clause with AND operator

Query:

SELECT
  LastName,
  FirstName
 
 FROM
  employees
 WHERE
  JobTitle = 'Sales' AND employeeNumber = 1001;

Using MySQL WHERE clause with OR operator

Query:

SELECT
  LastName,
  FirstName
 
 FROM
  employees
 WHERE
  JobTitle = 'Sales' OR employeeNumber = 1001;

Using MySQL WHERE with BETWEEN operator example

Query:

SELECT
  LastName,
  FirstName
 
 FROM
  employees
 WHERE
  employeeNumber BETWEEN 1001 AND 1003;
 

Using MySQL WHERE with the LIKE operator example

Query:

SELECT
  LastName,
  FirstName
 
 FROM
  employees
 WHERE
  LastName LIKE ‘%tz’;

Using MySQL WHERE clause with the IN operator example

Query:


 SELECT
  LastName,
  FirstName
 
 FROM
  employees
 WHERE
 employeeNumber IN (1001, 1002, 1003);

Using MySQL WHERE clause with comparison operators

Query(not(<>))

SELECT
  LastName,
  FirstName
 
 FROM
  employees
 WHERE
  JobTitle <> ‘Sales’;

If you are a student or database developer, administrator or someone with a basic understanding of the features of MySQL Hire us and Get your projects done by MySQL expert or learn

If you have project or assignment files, You can send at contact@codersarts.com directly

    0