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